Kumquats Unlimited makes large batches of kumquat paste for use in the food industry. These batches are made on automated production lines. Kumquats Unlimited has the capability to start up or shut down lines at the beginning of each month, but at a cost. If a line is up, management has determined that it's best to keep the line busy, even if the resulting batches must be put in inventory.
Management has created the following Excel spreadsheet, which uses the Solver function to find the lowest-cost solution to the S&OP problem. Re-create this spreadsheet, including the Solver optimization model (using Example 10.11 as a guide). Your formatting does not have to be the same, but your answers should be. Your spreadsheet should allow the user to make changes only to the planning values, the sales forecast, and the number of production line start-ups and shutdowns. All other values should be calculated. Be sure that Solver does not let inventory drop below zero at the end of any month or end June with less inventory than was available at the beginning of January. To test your spreadsheet, modify the spreadsheet so that each batch requires 32 hours of production line time. The new optimal cost should be $16,215,000.

Transcribed Image Text:

A B D Sales & Operations Planning Spreadsheet for Kumquats Unlimited (with Solver optimization) 1 Production cost per batch: $ Line hours per batch: Production line hours per month: Production costs: $ Line start-up costs: $ Line shutckwn costs: $ Inventory holding costs: $ 2,400 125,000 75,000 165,000 16 6. 320 hours Cost to start up a line $ 25,000 Cost to shut down a line $ Inventory holding cost: $ Beginning and ending lines Beginning and ending inventory 8 6,000 per batch, per month production lines batches 6. 300 Grand total: $14,964,000 10 55 11 100 12 Sales Actual (in production Production lines) Actual Production Line Production Line Ending Inventory 100 200 Sales Sales Month Lines Production Start-ups Forecast (in line hours) Shutdowns 13 14 55 55.00 15 January 1,000 1,200 1,200 1,000 50 0. 16,000 19,200 19,200 16,000 12,800 1,100 1,100 1,100 1,000 16 February 60 55.00 100 17 March 60 55.00 18 Аpril May June 50 S0.00 19 800 40 43.00 860 7. 20 800 12,800 40 43.00 860 120 21 55 22 Total 6000 6,020 12.5 480 23 Average = 50 838 032