|
Solver.com
From Frontline Systems, developers of the Excel Solver.
|
|
|
|
|
Purchasing/Transportation Model |
|
|
|
|
|
| A
cake mix manufacturer has 4 different plants that all require a certain
amount of sugar. There are |
|
|
| 5 different companies where the sugar can
be bought. Where should the company buy the |
|
|
| sugar
and how much should it buy, to minimize cost of sugar and shipping? |
|
|
|
|
|
|
|
|
|
|
|
| |
Company 1 |
Company 2 |
Company 3 |
Company 4 |
Company 5 |
|
|
| Sugar prices (per ton) |
$40 |
$49 |
$47 |
$45 |
$44 |
|
|
| |
|
|
|
|
|
|
|
| Cost of shipping
from companies to plants (per ton) |
|
|
|
|
| |
Company 1 |
Company 2 |
Company 3 |
Company 4 |
Company 5 |
|
|
| Plant 1 |
$8 |
$4 |
$5 |
$4 |
$3 |
|
|
| Plant 2 |
$7 |
$6 |
$3 |
$2 |
$4 |
|
|
| Plant 3 |
$7 |
$3 |
$7 |
$5 |
$2 |
|
|
| Plant 4 |
$8 |
$2 |
$5 |
$6 |
$7 |
|
|
|
|
|
|
|
|
|
|
| Amounts of sugar to buy (tons) |
|
|
|
|
|
|
| |
Company 1 |
Company 2 |
Company 3 |
Company 4 |
Company 5 |
Total |
Demand |
| Plant 1 |
0 |
0 |
0 |
0 |
0 |
0 |
420 |
| Plant 2 |
0 |
0 |
0 |
0 |
0 |
0 |
360 |
| Plant 3 |
0 |
0 |
0 |
0 |
0 |
0 |
400 |
| Plant 4 |
0 |
0 |
0 |
0 |
0 |
0 |
375 |
| Total |
0 |
0 |
0 |
0 |
0 |
|
|
| Available
supply |
350 |
250 |
200 |
300 |
500 |
|
|
| |
|
|
|
|
|
|
|
| Cost
of sugar |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cost of shipping |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| |
|
|
|
|
Total cost |
$0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Problem |
|
|
|
|
|
|
|
| A
cake-mix manufacturer has 4 different plants throughout the country. It can
buy sugar from 5 different companies. |
| The
cost of the sugar and the transportation costs from each company to each
plant are known. Where should the |
| company buy
sugar and how much should it buy, to meet the demand and minimize cost? |
|
|
| |
|
|
|
|
|
|
|
| Solution |
|
|
|
|
|
|
|
| 1) The variables are the amounts of sugar to be bought from
each company for each plant. On
worksheet Purchase |
| these are
given the name Amounts_to_buy. |
|
|
|
|
|
| 2) The
constraints are simple and straightforward: |
|
|
|
|
|
| |
Amounts_to_buy >= 0 via the Assume Non-Negative option |
|
|
| |
Total_amounts_to_buy >= Demand |
|
|
|
|
| |
Total_sold <= Supply |
|
|
|
|
|
| 3) The
objective is to minimize cost. This
is defined as Total_cost on the worksheet. |
|
|
|
| |
|
|
|
|
|
|
|
| Remarks |
|
|
|
|
|
|
|
| Even
though this model is very simple, it is one of the most used models in the
industry. It routinely saves many |
|
| companies
thousands or even millions of dollars a year. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
