P421 Material Supply Problem (Adapted from a sample problem published by Frontline Systems Inc.) Suppose you are the production vice president for a cake mix manufacturer. You oversee 4 different cake mix producing plants, and each plant requires a certain amount of sugar next month, measured in tons. There are 5 different companies where the sugar can be bought. Each of the 4 plants can purchase from any of the 5 sugar companies. A plant also can purchase from more than one sugar company. But each plant must purchase at least the amount of sugar shown in the "Sugar Required" table below. Each of the 5 sugar companies has a maximum amount of available sugar, as shown in the "Available Supply" table below. The 4 production plants can purchase any amount from zero to the maximum from each sugar company. Your job now is to decide how much sugar each plant should purchase from each supplier company, in such a way as to minimize the total cost. In making your decision, you must take into account two types of cost. One is the price of the sugar, which is different from each supplier company. Each company's price per ton is shown in the "Sugar Prices" table below. Your second consideration is the shipping cost. The cost per ton to ship sugar from each company to each plant is shown in the "Shipping Costs" table below. How much sugar should each plant purchase from each of the sugar companies, in order to minimize the total cost of sugar and shipping, subject to sugar requirements and availability? Excel Solver Solution Set up an Excel model to show all calculations and results. Your spreadsheet should be well organized and easy to follow. Try to keep similar data in the same column. 1. State the decision to be made and identify the Decision Cells. 2. Set up needed calculated values with cell references to Decision Cells. 3. Show the formula to be maximized or minimized. In this case: Minimize total cost. Total cost will include: Cost of purchasing sugar for each plant at the prices shown plus Cost of shipping that many tons of sugar to each plant from each supplying company. Show the necessary calculations, using cell references to the Decision Cells where Solver will enter the values for the solution. 4. Subject to the constraints. Write out each constraint in words . Then set up a table of the constraints using cell references to Decision Cells and the calculated values. 5. Complete the Solver dialog box and solve for lowest total cost. DATA Sugar Required (tons) Plant 1 420 Plant 2 360 Plant 3 400 Plant 4 375 Sugar Available (tons) Company 1 Company 2 350 250 Company 3 200 Company 4 300 Company 5 500 Sugar prices (per ton) Company 1 Company 2 $40 $49 Company 3 $47 Company 4 $45 Company 5 $44 Shipping Costs from supplier company to plant (per ton) Company 1 Company 2 Company 3 Company 4 Plant 1 $8 $4 $5 $4 Plant 2 $7 $6 $3 $2 Plant 3 $7 $3 $7 $5 Plant 4 $8 $2 $5 $6 Company 5 $3 $4 $2 $7 Solver Model Decision Cells Calculated Values Minimize: Total Cost ($) Total Cost 0 Subject to: Constraints