In: Operations Management
A paint company has four manufacturing plants (W, X, Y, and Z) that require 30,000, 20,000, 10,000, and 20,000 paint cans, respectively. Three paint can suppliers (A, B, and C) have indicated their willingness to supply up to 40,000, 20,000, and 30,000 cans per month, respectively. The total cost (shipping plus price) of delivering 100 cans from each supplier to each manufacturing plant is as below.
Manufacturing plant | ||||
supplier | W | X | Y | Z |
A | $54 | $48 | $50 | $46 |
B | 52 | 50 | 54 | 48 |
C | 46 | 48 | 50 | 52 |
Currently, supplier A is shipping 20,000 cans to plant X and 20,000 cans to Z. Supplier B is shipping 30,000 cans to W, and supplier C is shipping 10,000 cans to Z. Does the present delivery arrangement minimize the cost to the paint company? If not, find a plan that does minimize total costs.
Based on the given data, we tabulate the same as shown below:
Total Demand = 30000 + 20000 + 10000 + 20000 = 80000
Total Supply = 40000 + 20000 + 30000 = 90000
Since Total Supply > Total Demand, this is an unbalanced problem. Hence, there will be "<=" sign for Supply constraints in Solver
We solve the given problem in Excel using Excel Solver as shown below:
This is the least cost shipping schedule as shown in blue in middle table.
The cost for shipping schedule given in the question, we get the total cost as:
The total cost shown in above table in orange is more than total cost we found in the optimum solution as shown in blue.
-----------------------------------------------------------------------------------------------------------------------
In case of any doubt, please ask through the comment section before Upvote/downvote.
If you liked the answer, please give an upvote. This will be quite encouraging for me, thank-you!!!