Simulation Worksheet Qtr Wk Lines Scheduled Overtime Scheduled Expected Beginning Scheduled Forecast (hours) Inventory Production Demand Ending Inventory 1 10 1 200.0 191.3 153.8 237.4 Expected Weeks-of- Supply 1.5 Actual Production Rate (cases/hour) Actual Demand (1,000 Cases) Actual Production Actual Ending Inventory 0.0 0.0 2 10 1 237.4 191.3 153.8 274.8 1.8 0.0 0.0 3 10 1 274.8 191.3 153.8 312.2 2.0 0.0 0.0 4 10 1 312.2 191.3 153.8 349.6 2.3 0.0 0.0 5 10 1 349.6 191.3 153.8 387.0 2.5 0.0 0.0 6 10 387.0 191.3 153.8 424.4 2.8 0.0 0.0 1st 7 10 424.4 191.3 153.8 461.8 3.0 0.0 0.0 8 10 461.8 191.3 153.8 499.2 3.2 0.0 0.0 9 10 1 499.2 191.3 153.8 536.6 3.4 0.0 0.0 10 10 1 536.6 191.3 153.8 574.0 3.6 0.0 0.0 11 10 574.0 191.3 153.8 611.4 3.7 0.0 0.0 12 10 611.4 191.3 153.8 648.8 3.9 0.0 0.0 13 10 648.8 191.3 153.8 686.3 4.1 0.0 0.0 14 8 686.3 153.0 169.2 670.0 4.0 0.0 0.0 15 8 1 670.0 153.0 169.2 653.8 3.9 0.0 0.0 16 8 1 653.8 153.0 169.2 637.6 3.8 0.0 0.0 17 8 637.6 153.0 169.2 621.3 3.7 0.0 0.0 18 8 621.3 153.0 169.2 605.1 3.6 0.0 0.0 19 8 605.1 153.0 169.2 588.9 3.5 0.0 0.0 2nd 20 8 1 588.9 153.0 169.2 572.6 3.4 0.0 0.0 21 8 1 572.6 153.0 169.2 556.4 3.2 0.0 0.0 22 8 556.4 153.0 169.2 540.2 3.1 0.0 0.0 23 8 1 540.2 153.0 169.2 523.9 2.9 0.0 0.0 24 8 1 523.9 153.0 169.2 507.7 2.8 0.0 0.0 25 8 507.7 153.0 169.2 491.5 2.6 0.0 0.0 26 8 1 491.5 153.0 169.2 475.3 2.5 0.0 0.0 27 9 2 475.3 192.4 192.3 475.3 2.5 0.0 0.0 28 9 2 475.3 192.4 192.3 475.4 2.5 0.0 0.0 29 9 2 475.4 192.4 192.3 475.5 2.5 0.0 0.0 30 9 2 475.5 192.4 192.3 475.5 2.5 0.0 0.0 31 9 2 475.5 192.4 192.3 475.6 2.5 0.0 0.0 32 9 2 475.6 192.4 192.3 475.7 2.5 0.0 0.0 3rd 33 9 2 475.7 192.4 192.3 475.7 2.5 0.0 0.0 34 9 2 475.7 192.4 192.3 475.8 2.4 0.0 0.0 35 9 2 475.8 192.4 192.3 475.9 2.4 0.0 0.0 36 9 2 475.9 192.4 192.3 475.9 2.4 0.0 0.0 37 9 2 475.9 192.4 192.3 476.0 2.4 0.0 0.0 38 9 2 476.0 192.4 192.3 476.1 2.4 0.0 0.0 39 9 2 476.1 192.4 192.3 476.1 2.3 0.0 40 11 1 476.1 210.4 203.8 482.7 2.4 0.0 41 11 1 482.7 210.4 203.8 489.2 2.4 0.0 42 11 1 489.2 210.4 203.8 495.7 2.4 0.0 43 11 1 495.7 210.4 203.8 502.2 2.5 0.0 44 11 502.2 210.4 203.8 508.8 2.5 0.0 45 11 508.8 210.4 203.8 515.3 2.5 0.0 4th 46 11 515.3 210.4 203.8 521.8 2.6 0.0 47 11 1 521.8 210.4 203.8 528.4 2.7 0.0 48 49 50 51 52 FFFFF 11 1 528.4 210.4 203.8 534.9 2.8 0.0 11 1 534.9 210.4 203.8 541.4 2.9 0.0 11 1 541.4 210.4 203.8 547.9 3.0 0.0 11 1 547.9 210.4 203.8 554.5 3.2 0.0 11 554.5 210.4 203.8 561.0 3.3 0.0 88888888888888 0.0 169.2 169.2 169.2 169.2 169.2 169.2 5th 169.2 169.2 Actual Costs Regular Overtime Qtr Wk Production Production Inventory Carrying Cost Stockout Cost Hiring and Training Cost Layoff Cost Total For Week Cumulative Cost Cost 1 0 0 0 0 0 0 2 0 0 0 0 3 0 4 0 0 0 5 6 1st 7 8 0 0 0 0 9 10 0 ooo oo ooooo 0 0 0 0 0 0 0 0 0 0 11 0 0 12 0 0 0 0 0 13 0 0 0 14 0 36,000 36,000 36,000 18 19 2nd 20 4th LOTOOOO2222222232333EMBOTIT3144747 15 0 0 0 36,000 16 0 0 0 0 0 0 36,000 36,000 0 0 0 0 0 0 36,000 36,000 0 36,000 36,000 0 0 36,000 0 36,000 0 0 0 0 36,000 25 26 0 0 36,000 0 0 0 36,000 0 30,000 30,000 66,000 28 29 30 0 0 0 66,000 66,000 0 0 0 0 0 3rd 33 35 0 66,000 66,000 0 66,000 36 38 39 40 50 51 52 booooo booooo O oooooooo ooo oo o 0 0 0 0 0 0 0 0 ooooooo 66,000 0 66,000 66,000 0 66,000 66,000 0 66,000 0 66,000 0 0 60,000 60,000 126,000 126,000 0 0 0 0 0 126,000 126,000 0 0 0 0 0 0 0 0 0 0 ooooooooo 0 126,000 126,000 0 126,000 126,000 0 126,000 126,000 0 126,000 126,000 0 126,000 Bradford Manufacturing - Planning Plant Production 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 Quarter (Week Numbers) 1st (1-13) 2nd (14-26) 3rd (27-39) 4th (40-52) 1st (Next Year) 2,000 2,200 2,500 2,650 338 385 408 338 Notes 2,200 Each number is a 13 week forecast. This is calculated based on 6 weeks of future forecast demand. Numbers Units of measure Notes 60 employees 6 Forecast Demand by Quarter (1,000 Case Units) 450 Cases per hour $20.00 per hour 3,000 Overtime pay rate Standard hours per shift Inventory carry cost Stockout cost $30.00 per hour 7.5 hours 2,500 2,000 Maximum overtime per day 2 hours 1,500 $1.00 per case (per year) 1,000 $2.40 per case 500 Employee hiring and training cost $5,000.00 per employee 0 Employee layoff cost Aggregate Plan Lines run $3,000.00 per employee 1st (1-13) 2nd (14-26) 3rd (27-39) 4th (40-52) 1st (Next Year) Quarter (Week Numbers) 1st (1-13), 2nd (14-26) 3rd (27-39) 4th (40-52) Notes 10 9 11 Construct solution here. Overtime hours per day 1 2 1 Beginning Inventory 200.0 686.3 475.3 476.1 Production 2,486.3 1,989.0 2,500.9 2,734.9 Expected Demand 2,000.0 2,200.0 2,500.0 Ending Inventory 686.3 475.3 476.1 2,650.0 561.0 Deviation from Inventory Target 347.8 90.6 Employees 60 48 68.4 54 222.5 66 Cost of Plan Labor Regular Time $624,000 $499,200 $561,600 $686,400 Labor Overtime Hiring and Training Layoff Inventory Carry Cost Stockout Cost 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. Quarter Budget $624,000 $499,200 $561,600 $686,400 Total Cost of Plan $2,371,200 Actual Costs Regular Overtime Qtr Wk Production Production Inventory Carrying Cost Stockout Cost Hiring and Training Cost Layoff Cost Total For Week Cumulative Cost Cost 1 0 0 0 0 0 0 2 0 0 0 0 3 0 4 0 0 0 5 6 1st 7 8 0 0 0 0 9 10 0 ooo oo ooooo 0 0 0 0 0 0 0 0 0 0 11 0 0 12 0 0 0 0 0 13 0 0 0 14 0 36,000 36,000 36,000 18 19 2nd 20 4th LOTOOOO2222222232333EMBOTIT3144747 15 0 0 0 36,000 16 0 0 0 0 0 0 36,000 36,000 0 0 0 0 0 0 36,000 36,000 0 36,000 36,000 0 0 36,000 0 36,000 0 0 0 0 36,000 25 26 0 0 36,000 0 0 0 36,000 0 30,000 30,000 66,000 28 29 30 0 0 0 66,000 66,000 0 0 0 0 0 3rd 33 35 0 66,000 66,000 0 66,000 36 38 39 40 50 51 52 booooo booooo O oooooooo ooo oo o 0 0 0 0 0 0 0 0 ooooooo 66,000 0 66,000 66,000 0 66,000 66,000 0 66,000 0 66,000 0 0 60,000 60,000 126,000 126,000 0 0 0 0 0 126,000 126,000 0 0 0 0 0 0 0 0 0 0 ooooooooo 0 126,000 126,000 0 126,000 126,000 0 126,000 126,000 0 126,000 126,000 0 126,000