This case puts you in the driver seat (as a production planner).

Question: This case puts you in the driver seat (as a production planner). Open the attached xls workbook.. 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 whereThis case puts you in the driver seat (as a production planner). Open the attached xls workbook.. 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). You should make an attempt to adjust your budget, based the actual costs that get simulated.
Sheet 1-
Sheet 2-
Sheet 3-
Show transcribed image textBradford Manufacturing – Planning Plant Production 4 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 E…View the full answerTranscribed image text: Bradford Manufacturing – Planning Plant Production 1,000 case units. 1st (1-13) Forecast Demand Ending Inventory Target Numbers 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 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 Inventory Carry Cost Stockout Cost Quarter Budget 2,000 338 Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 2,200 385 2,500 408 Units of measure Notes 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 1st (1-13) 10 1 200.0 2,486.3 2,000.0 686.3 347.8 60 $624,000 $624,000 Quarter (Week Numbers) 2nd (14-26) 8 1 686.3 1,989.0 2,200.0 475.3 90.6 48 $499,200 $499,200 3rd (27-39) 9 2 475.3 2,500.9 2,500.0 476.1 68.4 54 $561,600 $561,600 Total Cost of Plan 4th (40-52) 1st (Next Year) 2,650 338 3,000 2,500 2,000 1,500 1,000 500 0 4th (40-52) 11 1 476.1 2,734.9 2.650.0 561.0 222.5 66 $686,400 $686,400 $2,371,200 Notes 2,200 Each number is a 13 week forecast. This is calculated based on 6 weeks of future forecast demand. 1st (1-13) Forecast Demand by Quarter (1,000 Case Units) 2nd (14-26) 3rd (27-39) Notes 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. 4th (40-52) 1st (Next Year) Qtr 1st 2n d k 1 2 3 3456789mn2345旧FBD2012 23 4 25 257 28 29 3013233435387 38 380 23科548 49 50 1 52 11 24 26 3rd 33 36 39 40 41 44 4th 46 47 51 Lines Overtime Beginnin Schedule Schedule 9 d d [hours) Inventory 10 1 200.0 10 1 237.4 10 1 274.8 10 1 312.2 10 1 349.6 10 387.0 10 1 424.4 10 461.8 10 1 499.2 10 536.6 10 1 574.0 10 611.4 10 1 648.8 8 1 686.3 8 1 670.0 8 653.8 8 637.6 8 621.3 8 605.1 8 588.9 8 572.6 8 556.4 8 540.2 8 523.9 8 507.7 8 491.5 9 475.3 9 475.3 475.4 475.5 475.5 475.6 475.7 475.7 475.8 475.9 475.9 476.0 476.1 476.1 482.7 489.2 495.7 502.2 508.8 515.3 521.8 528.4 534.9 541.4 547.9 554.5 9 9 9 9 9 9 9 9 9 9 9 11 11 11 11 11 FFFFFFFFF 11 11 11 11 11 11 1 2 2 2 2 2 2 2 2 2 1 1 1 Schedule d Forecast Producti Demand on 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 191.3 153.8 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 153.0 169.2 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 192.4 192.3 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 210.4 203.8 169.2 169.2 169.2 Expected Expected Ending Weeks-of- Inventory Supply 237.4 1.5 274.8 1.8 312.2 2.0 349.6 2.3 387.0 2.5 424.4 2.8 461.8 3.0 499.2 3.2 536.6 3.4 574.0 3.6 611.4 3.7 648.8 3.9 686.3 4.1 670.0 4.0 653.8 3.9 637.6 3.8 621.3 3.7 605.1 3.6 588.9 3.5 572.6 3.4 556.4 3.2 540.2 3.1 523.9 2.9 507.7 2.8 491.5 2.6 475.3 2.5 475.3 2.5 475.4 2.5 475.5 2.5 475.5 2.5 475.6 2.5 475.7 2.5 475.7 2.5 475.8 2.4 475.9 2.4 475.9 2.4 476.0 2.4 476.1 2.4 476.1 2.3 482.7 2.4 489.2 2.4 495.7 2.4 502.2 2.5 508.8 2.5 515.3 2.5 521.8 2.6 528.4 2.7 534.9 2.8 541.4 2.9 547.9 3.0 554.5 3.2 561.0 3.3 Actual Actual Production Demand Rate (1,000 [cases/hour Cases] Actual Actual Producti Ending on 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 888888 OOOOO 99999 0.0 0.0 0.0 0.0 0.0 Actual Costs Qtr 1st 2n d k 1 2 3 4 56789mn佗BH567BD012234 25 26 27 28 25 20123334358783840=22科456F84950 1 52 11 29 3rd 33 4th 46 51 Regular Overtime Inventory Stockout Hiring and Productio Carrying Training Productio Cost n Cost n Cost Cost 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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 oooooo 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 OOOOOOOOO 0 0 0 0 0 0 0 0 oooooooo 0 0 0 0 0 0 0 0 0 0 0 ooooooooooooooooooooooo 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 oooooooooooooooooooooooooo 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 OOOOOOOOOOOOOOO 000000000000 oooooooooooo ooooo 000000000 0 0 0 60,000 Lagoff Cost 0 0 0 0 0 0 0 0 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 Total For Cumulativ Veek e 0 ooooooooooooo oooooooooooo 0 36,000 0 0 0 0 0 0 0 0 0 0 0 0 30,000 0 oooooooooooo 0 0 0 0 0 0 0 60,000 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 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