In: Operations Management
You have three plants that produce a certain type of boats. The capacity for next month is 38 in San Diego, 45 in Santa
Ana, and 58 in San Jose. Production cost per boat is $1,065 in San Diego, $1,005 in Santa Ana, and $975 in San Jose.
Demand for next month is 42 in Newport Beach, 33 in Long Beach, 14 in Ventura, 10 in San Luis Obispo, and 22 in San
Francisco. The shipping costs per boat are summarized in the following table:
Shipping | Cost | to: | |||
From | NB | LB | VEN | SLO | SF |
SD | $200 | $220 | $280 | $350 | $400 |
SA | $125 | $125 | $280 | $350 | $400 |
SJ | $390 | $365 | $300 | $250 | $100 |
Develop a production and shipping schedule that minimizes the total cost of production and shipping while satisfying all
the demand.
We tabulate the given data as shown below:
The
above table considers transportation cost as well as production
cost at each location.
We solve this problem in Excel using Excel Solver as shown below:
Here Total Demand = 42 + 33 + 14 + 10 + 22 = 121
Total Supply = 38 + 45 + 58 = 141
Total Demand < Total Supply. Hence, there will be "<=" sign in Excel Solver for Supply Constraint
We get solution as:
The above solution in the form of formulas along with Excel Solver extract is shown below for better understanding and reference:
The no. of boats to be supplied and transported from each location to each destination is shown as highlighted in purple in the Variables tables above.
----------------------------------------------------------------------------------------------------------------------------------------------------
In case of any doubt, please ask through the comment section before Upvote/downvote.