1,000 case units. Forecast Demand Ending Inventory Target Planning Data Initial number of employees Emplyees per line Standard production rate (each line) Employee pay rate Overtime pay rate Standard hours per shift Maximum overtime per day Inventory carry cost Stockout cost Employee hiring and training cost Employee layoff cost
1st (1-13) 2,000 338 Numbers
Units of measure 60 employees 6 450 Cases per hour $20.00 per hour $30.00 per hour 7.5 hours 2 hours $1.00 per case (per year) $2.40 per case $5,000.00 per employee $3,000.00 per employee
Aggregate Plan Lines run Overtime hours per day Beginning Inventory Production Expected Demand Ending Inventory Deviation from Inventory Target Employees Cost of Plan Labor Regular Time Labor Overtime Hiring and Training Layoff
Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 2,200 2,500 385 408
1st (1-13) 9 1
4th (40-52) 1st (Next Year) Notes 2,650 2,200 Each number is a 13 week forecast. 338 This is calculated based on 6 weeks future forecast demand.
Notes Forecast Dem and by Quarte r (1,000 Case Units ) 3,000 2,500 2,000 1,500 1,000
Profit margin loss
500 0 1st (1-13)
Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 9 10 1 2
4th (40-52) 10 1
200.0 2,237.6 2,000.0 437.6
437.6 2,237.6 2,200.0 475.3
475.3 2,778.8 2,500.0 754.0
754.0 2,486.3 2,650.0 590.3
99.2 54
90.6 54
346.3 60
251.8 60
2nd (14-26)
3rd (27-39)
4th (40-52)
Notes Construct solution here.
Employees are paid for 8 hrs work p
60 employees are employeed curren
Carrying cost per year,divided by 4, Basic on negative deviation from tar
Inventory Carry Cost Stockout Cost Quarter Budget
$0
$0
$0
$0
Total Cost of Plan
$0
Each number is a 13 week forecast. This is calculated based on 6 weeks of future forecast demand.
Forecast Dem and by Quarte r (1,000 Case Units ) 3,000 2,500 2,000 1,500 1,000 500 0 1st (1-13)
2nd (14-26)
3rd (27-39)
4th (40-52)
1st (Next Year)
Construct solution here.
Employees are paid for 8 hrs work per day. 60 employees are employeed currently.
Carrying cost per year,divided by 4, multiplied by 1,000. Basic on negative deviation from target.
Simulation Worksheet
Qtr Wk
1st
2nd
3rd
4th
5th
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
Lines Scheduled
Overtime Scheduled (hours)
Beginning Inventory
Scheduled Production
Forecast Demand
9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1 1 1 1
200.0 218.3 236.6 254.8 273.1 291.4 309.7 328.0 346.2 364.5 382.8 401.1 419.3 437.6 440.5 443.4 446.3 449.2 452.1 455.0 457.9 460.8 463.7 466.6 469.5 472.4 475.3 496.7 518.1 539.6 561.0 582.5 603.9 625.3 646.8 668.2 689.7 711.1 732.6 754.0 741.4 728.8 716.2 703.6 691.0 678.4 665.8 653.2 640.6 628.0 615.4 602.8
172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 172.1 213.8 213.8 213.8 213.8 213.8 213.8 213.8 213.8 213.8 213.8 213.8 213.8 213.8 191.3 191.3 191.3 191.3 191.3 191.3 191.3 191.3 191.3 191.3 191.3 191.3 191.3
153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 192.3 192.3 192.3 192.3 192.3 192.3 192.3 192.3 192.3 192.3 192.3 192.3 192.3 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2
Expected Ending Inventory
Expected Weeks-ofSupply
218.3 236.6 254.8 273.1 291.4 309.7 328.0 346.2 364.5 382.8 401.1 419.3 437.6 440.5 443.4 446.3 449.2 452.1 455.0 457.9 460.8 463.7 466.6 469.5 472.4 475.3 496.7 518.1 539.6 561.0 582.5 603.9 625.3 646.8 668.2 689.7 711.1 732.6 754.0 741.4 728.8 716.2 703.6 691.0 678.4 665.8 653.2 640.6 628.0 615.4 602.8 590.3
1.4 1.5 1.7 1.8 1.9 2.0 2.1 2.2 2.3 2.4 2.4 2.5 2.6 2.6 2.6 2.6 2.7 2.7 2.7 2.7 2.7 2.6 2.6 2.5 2.5 2.5 2.6 2.7 2.8 2.9 3.0 3.1 3.3 3.3 3.4 3.5 3.6 3.6 3.7 3.6 3.6 3.5 3.5 3.4 3.3 3.3 3.3 3.3 3.4 3.4 3.4 3.5
Actual Production Rate (cases/hour)
Actual Demand (1,000 Cases)
Actual Production
Actual Ending Inventory
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Actual Costs Regular Qtr Wk Production Cost 1 0 2 0 3 0 4 0 5 0 6 0 0 1st 7 8 0 9 0 10 0 11 0 12 0 13 0 14 0 15 0 16 0 17 0 18 0 19 0 0 2nd 20 21 0 22 0 23 0 24 0 25 0 26 0 27 0 28 0 29 0 30 0 31 0 32 0 0 3rd 33 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 0 4th 46 47 0 48 0 49 0 50 0 51 0 52 0
Overtime Production Cost 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Inventory Carrying Cost 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Stockout Cost 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Hiring and Training Cost 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 30,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Layoff Cost
Total For Week
Cumulative
18,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
18,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 30,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 18,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000 48,000
