This case puts you in the driver seat (as a production planner). There is an xls workbook on your student DVD that you can access, and save to your PC, as a starting point - or simply download the attachment. Your job is to prepare production schedule (considering the constraints and rules of this case in Chp 19), and to determine the operating budget for such a plan. There are 3 worksheets available to execute this plan - the 1st Aggregate Plan is where you determine your production plan/schedule (by indicating the number of lines and overtime hrs per day), and the budget (costs) associated with your plan - these are the yellow highlighted sections of the worksheet - you need to determine how many production lines to run, how many hours of overtime to work per day, and the corresponding budgeted line items in the lower highlighted budget section - you can use the defaulted number of production lines and overtime hours as a first try; 2nd is the Simulation Worksheet where you need to indicate the Actual Production Rate and the Actual Demand columns cells (in reality the actual data would be getting filled in each week as time progresses, but you need to fill all 52 weeks with numbers (simply enter 450 into the Actual Production Rate column (each cell) and then copy the Forecast column into the Actual Demand column) - which will end up simulating how much your plan will actually cost onto the 3rd sheet; 3rd is the Actual Cost worksheet, which will fill in automatically, based on data from the Simulation Worksheet. The main idea here is to do your planning (sheet 1), estimate your budget (sheet 1), enter in the production rate and demand (sheet 2), which will automatically generate numbers showing the actual costs (sheet 3) - lastly, you should compare your budget (sheet 1) to what it actually costs to execute (sheet 3). 1,000 case units. . Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 2,200 2,500 385 408 1st (1-13) 2,000 338 Forecast Demand Ending Inventory Target 4th (40-52) 2,650 338 1st (Next Year) Notes 2,200 Each number is a 13 week forecast. This is calculated based on 6 weeks of future forecast demand. Notes 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 Numbers Units of measure 60 employees 6 450 Cases per hour $20.00 per hour $30.00 per hour $ 7.5 hours Forecast Demand by Quarter (1,000 Case Units) ( 2 hours 3,000 2,500 2.000 1,500 1,000 500 $1.00 per case (per year) $2.40 per case $5,000.00 per employee $3,000.00 per employee D 1st (1-13) 2nd (14-26) 3rd (27-39) 4th (40-52) 1st (Next Year) Aggregate Plan 1st (1-13) 10 1 Lines run Overtime hours per day Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 8 9 2 4th (40-52) 11 1 Notes Construct solution here. Beginning Inventory Production Expected Demand Ending Inventory 200.0 2,486.3 2,000.0 686.3 686.3 1,989.0 2,200.0 475.3 475,3 2,500.9 2,500.0 476.1 476,1 2,734.9 2,650.0 561.0 Deviation from Inventory Target Employees 347.8 60 90.6 48 68.4 54 222.5 66 $624,000 $499,200 $561,600 $686,400 Employees are paid for 8 hrs work per day. Cost of Plan Labor Regular Time Labor Overtime Hiring and Training Layoff Inventory Cary Cost Stockout Cost 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 Scheduled Qtr WK Lines Scheduled Beginning Inventory Production Forecast Demand Expected Weeks. Actual Production of-Supply Rate (cases/hour) Actual Demand (1,000 Cases) Actual Production Actual Ending Inventory 10 10 10 10 10 10 191.3 191.3 191.3 191.3 191.3 Scheduled (hours) 1 1 1 1 1 1 1 1 1 1 1 1 Ending Inventory 237.4 274.8 312.2 349.6 387.0 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 191.3 424.4 10 461.8 499.2 536.6 574.0 153 8 1 2 3 4 5 6 1st 7 1 8 8 9 9 10 11 12 13 14 15 16 17 18 19 2nd 20 21 22 23 24 25 26 27 28 29 10 10 10 10 10 10 10 10 8 8 611.4 200.0 237.4 274.8 312.2 349.6 387.0 424.4 461.8 499.2 536.6 574.0 6444 611.4 648.8 686.3 670.0 653.8 20 637.6 621.3 605.1 588.9 572.6 556.4 540.2 523.9 507.7 491.5 475.3 475.3 475.4 191.3 191.3 191.3 191.3 191.3 191.3 191.3 153.0 153.0 1 153.0 153.0 100 153.0 153.0 153.0 153.0 153.0 153.0 153.0 153.0 153.0 192.4 192.4 192.4 192.4 648.8 686.3 670.0 653.8 637.6 621.3 605.1 588.9 572.6 1 1 1 1 1 1 1 1 1 1598 153.8 1600 153.8 153.8 en 169.2 100 169.2 169.2 169.2 es 169.2 2009 169.2 169.2 169.2 169.2 169.2 169.2 169.2 169.2 192.3 192.3 8 8 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 1.5 1.8 2.0 2.3 2.5 2.8 3.0 3.2 3.4 3.6 3.7 3.9 4.1 4.0 3.9 3.8 3.7 3.6 3.5 3.4 3.2 3.1 2.9 2.8 2.6 2.5 2.5 2.5 2.5 2.5 2.5 2.5 2.5 2.4 2.4 2.4 2.4 2.4 2.3 2.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 556.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 no 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 no 0.0 0.0 0.0 540.2 523.9 507.7 491.5 475.3 475.3 475.4 475.5 30 475.5 192.3 192.3 192.3 475.5 192.4 31 32 3rd 33 34 35 36 37 38 39 40 2 2 2 2 2 2 2 2 2 2 2 2 1 475.5 475.6 475.7 4757 475.7 476 475.8 475.9 475.9 476.0 476.1 476.1 192.4 192.4 192.4 192.4 192.4 192.4 192.4 192.4 210.4 192.3 192.3 192.3 1092 192.3 192.3 192.3 192.3 192.3 203.8 475.6 475.7 4757 475.7 4760 475.8 475 475.9 475.9 476.0 476.1 476.1 482.7 0.0 0.0 0.0 0.0 0.0 11 0.0 41 42 43 44 45 46 47 48 49 50 51 52 11 11 11 11 11 11 11 11 11 11 11 11 1 1 1 1 1 1 1 1 4th 482.7 489.2 495.7 502.2 508.8 515.3 521.8 528.4 534.9 541.4 547.9 554.5 210.4 210.4 210.4 210.4 210.4 210.4 210.4 210.4 210.4 210.4 210.4 210.4 489.2 495.7 502.2 508.8 515.3 521.8 528.4 534.9 541.4 547.9 554.5 561.0 2.4 2.4 2.5 2.5 2.5 2.6 2.7 2.8 2.9 3.0 3.2 3.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Oo 0.0 1 1 1 0.0 0.0 0.0 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 5th Stockout Cost Hiring and Training Cost Layoff Cost Total For Week Cumulative 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 0 0 0 0 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 12 0 0 0 0 Regular Qtr Wk Production Cost 1 0 2 0 3 0 4 0 5 0 6 0 1st 7 0 8 0 9 0 0 10 0 11 0 0 0 13 0 14 0 15 0 16 0 17 0 18 0 0 19 0 2nd 20 0 21 0 22 0 23 0 24 0 25 0 26 0 0 27 0 28 0 29 0 0 30 0 31 0 0 32 0 3rd 33 0 0 34 0 0 35 0 36 0 37 0 38 39 0 0 0 0 0 36,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 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 66,000 0 0 0 0 0 0 0 0 0 36,000 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 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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 OOOO 0 60,000 60,000 0 0 0 0 0 0 0 4th 40 41 42 43 44 45 46 47 48 49 50 51 52 0 126,000 126,000 126,000 126,000 126,000 126,000 126,000 126,000 126,000 126,000 126,000 126,000 126,000 0 0 0 0 0