Please solve using Excel and specifically need to know how to solve for inventory total and holding cost.
fx
B
c
D
G
M
Problem 6 Given the following forecast and cost information, determine the total cost of a plan that uses regular time production output of 600 units per month, overtime is used when needed up to a maximum of 60 units per month, and subcontracting is used if additional units are needed to meet the forecast. Production
Regular time cost Overtime cost subcontracting cost holding cost
$ $ $ $
40.00per unit 60.00per unit 80.00 per unit 10.00per unit per month
max ot
60
0
Inventory
Costs
Month
Forecast
Level
Overtime
Subcontracting
Total
Holding Cost Regular Time Overtime
Subcontracting
Total Cost
Production 1 560 600 2 600 600 E 640 600 4 660 600 5 660 600 6 680 600 Totals 3600
0 0 40 60 60 60 220
0 0 0 0 0 20 20
S 24,000.00 $ . $ 24,000.00 S $ S 24,000.00 s 2,400.00 $ 24,000.00 3,600.00 $ S 24,000.00 3,600.00 $ S 24,000.00 3,600.00 $ $ 13,200.00
$ 24,000.00 $ 24,000.00 $ 26,400.00 $ 27,600.00 $ 27,600.00 1,600.00 $ 29,200.00 158,800.00