Hello. Can you show an Excel Spreadsheet with formulas on this problem:

A planner has developed an aggregate forecast for demand (in cases) for the next six months.

Month: May Jun Jul Aug Sep Oct

Forecast 4,100 4,700 5,600 7,200 6,400 5100

Regular production cost $10 per case

Regular production capacity 5,000 cases

Overtime production cost $16 per case

Subcontracting cost $20 per case

Holding cost $1 per case per month

Beginning inventory 0

I've created a spread sheet and I'm not sure if its correct. I get confused with Inventory.

1 Period 2 Forecast 1 4100 3 5600 5 6400 6 5100 Total 33100 7200 2 4700 Output 5000 5000 5000 5000 800 4 Regular Production 5 Overtime Units 6 Subcontract 7 Output/Forcast 5000 700 700 5000 50 50 30000 1550 1550 800 -600 900 -600 300 Inventory 0 9 Beginning 10 Inventory Balance 11 Average 12 Backorder 900 900 0 -600 -300 300! 150 0 0 -600 -300 0 0 0 0 450 Cost 14 Output 15 Production Cost 16 Overtime Cost 17 SubContract 18 AVG Balance Inventory 19 Holding Cost 20 Total $ 50,000.00 $ 50,000.00 $ 50,000.00 $ - $ $ - 9000 1500 -3000 900 150 -300 $ 59,000.00 $ 51,500.00 $ 47,000.00 $ 50,000.00 $ 12,800.00 $ 16,000.00 -3000 -300 $ 75,800.00 $ 50,000.00 $ 11,200.00 $ 14,000.00 0 $ 50,000.00 $300,000.00 $ 800.00 $ 24,800.00 $ 1,000.00 $ 31,000.00 0 4500 450 $ 51,800.00 $360,300.00 $ 75,200.00