14. (***) (Microsoft Excel problem). 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 lowestcost solution to the S&OP problem. Re-create this spreadsheet, including the Solver optimization model

(use Example 13.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:

1 2 3 4 5 A B Sales & Operations Planning Spreadsheet for Kumquats Unlimited (with Solver optimization) Production cost per batch: $ Line hours per batch: E F H 2,400 16 6 Production line hours per month: $ 320 hours Production costs: $ Line start-up costs: $ 125,000 Line shutdown costs: $ 75,000 7 8 9 10 11 Cost to start up a line: $ Cost to shut down a line: $ Inventory holding cost: $ Beginning and ending lines: Beginning and ending inventory: 25,000 Inventory holding costs: $ 165,000 6,000 300 55 per batch, per month production lines Grand total: $14,765,000 100 batches 12 Sales 13 Month Forecast Sales (in line hours) Sales (in production Actual Production. lines) Lines Actual Production Production Line Production Line Ending Start-ups Shutdowns Inventory 14 55 100 15 January 1,000 16,000 50 55.00 1,100 0 0 200 16 February 1,200 19,200 60 55.00 1,100 0 0 100 17 March 1,200 19,200 60 55.00 1,100 0 0 0 18 April 1,000 16,000 50 50.00 1,000 0 5 0 19 May 800 12,800 40 42.50 850 0 7 50 20 June 800 12,800 40 42.50 850 0 0 100 21 48 5 0 100 22 Total = 6000 6,000 5 12.5 550 23. Average = 50