Question

Please submit as a single Excel file. To receive credit, appropriate detail needs to be provided. In addition, a professional presentation of findings is important.

#1       Determine the total cost for this plan given the following forecast:

Month 1 2       3       4       5       6

Forecast 380   400   420   440   460   480

Use steady regular output of 400 units per month, use overtime as needed for up to 40 units per month, and use subcontracting to make up any needed output to match the forecast. Unit costs are:

Regular output $25

Overtime $40

Subcontract $60

Average Balance Inventory $15

Answer 1

Month	Forecast	Production Level	Inventory	Adjusted inventory	Overtime production Required	Subcontracting Quantity	Regular cost	Inventory Cost	Overtime cost	Subcontract cost	Total cost
1	380	400	20	20	0	0	$10,000	$300	$0	$0	$10,300
2	400	400	0	20	0	0	$10,000	$300	$0	$0	$10,300
3	420	400	-20	0	0	0	$10,000	$0	$0	$0	$10,000
4	440	400	-40	0	40	0	$10,000	$0	$1,600	$0	$11,600
5	460	400	-60	0	20	20	$10,000	$0	$800	$1,200	$12,000
6	480	400	-80	0	40	40	$10,000	$0	$1,600	$2,400	$14,000
Unit costs
Regular	$25
Overtime	$40
Subcontract	$60
Inventory	$15

The formula for excel calculation

Month	Forecast	Production Level	Inventory	Adjusted inventory	Overtime production Required	Subcontracting Quantity	Regular cost	Inventory Cost	Overtime cost	Subcontract cost	Total cost
1	380	400	=D3-C3	20	0	=IF(ABS(E3)>40,ABS(E3)-40,0)	=D3*$C$12	=F3*$C$15	=G3*$C$13	=H3*$C$14	=SUM(I3:L3)
2	400	400	=D4-C4	20	0	=IF(ABS(E4)>40,ABS(E4)-40,0)	=D4*$C$12	=F4*$C$15	=G4*$C$13	=H4*$C$14	=SUM(I4:L4)
3	420	400	=D5-C5	=IF(E5<0,IF(F4>0,E5+F4,0),0)	0	=IF(ABS(E5)>40,ABS(E5)-40,0)	=D5*$C$12	=F5*$C$15	=G5*$C$13	=H5*$C$14	=SUM(I5:L5)
4	440	400	=D6-C6	=IF(E6<0,IF(F5>0,E6+F5,0),0)	=IF(E6>=-40,ABS(E6),ABS(E6-(-40)))	=IF(ABS(E6)>40,ABS(E6)-40,0)	=D6*$C$12	=F6*$C$15	=G6*$C$13	=H6*$C$14	=SUM(I6:L6)
5	460	400	=D7-C7	=IF(E7<0,IF(F6>0,E7+F6,0),0)	=IF(E7>=-40,ABS(E7),ABS(E7-(-40)))	=IF(ABS(E7)>40,ABS(E7)-40,0)	=D7*$C$12	=F7*$C$15	=G7*$C$13	=H7*$C$14	=SUM(I7:L7)
6	480	400	=D8-C8	=IF(E8<0,IF(F7>0,E8+F7,0),0)	=IF(E8>=-40,ABS(E8),ABS(E8-(-40)))	=IF(ABS(E8)>40,ABS(E8)-40,0)	=D8*$C$12	=F8*$C$15	=G8*$C$13	=H8*$C$14	=SUM(I8:L8)
Unit costs
Regular	25
Overtime	40
Subcontract	60
Inventory	15

Please comment if any doubt you have