Please submit as a single Excel file. To receive credit, appropriate detail need
ID: 336505 • Letter: P
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
Explanation / Answer
The formula for excel calculation
Please comment if any doubt you have
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