Re-create the S&OP spreadsheet used in Table 10.10 and Example 10.11. (You do not have to build in the optimization model using the Solver function.) While your formatting may differ, your answers should be the same. Your spreadsheet should generate new results any time any of the planning, sales forecast, or hiring/layoff values are changed. To test your spreadsheet, change the planning values to match the following:

If your spreadsheet works correctly, the new total cost for a level production plan should be $5,769,100, and for a chase production plan, it should be $5,755,600.

Labor hrs. per unit: 24 150 100 100 Worker hrs. per mont 4 5 Beginning & ending workforce Beginning & ending invent Production cost per unit Hiring cost off cost: Holding cost per unit per month 475.00 $400.00 $300.00 $3.00 9 10