Question

1 Approved Answer

Posted on Jul 26, 2024

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,

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).

image text in transcribed

Bradford Manufacturing - Planning Plant Production 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 Forecast Demand by Quarter (1,000 Case Units) 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 2 hours $1.00 per case (per year) ) $2.40 per case $5,000.00 per employee $3,000.00 per employee 3,000 2 500 2.000 1,500 1,000 500 0 1st (1-13) 2nd (1428) 3rd (27-39) 4th (40-52) 1st (Next Year) Aggregate Plan Lines run Overtime hours per day 1st (1-13) 10 1 Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 8 9 1 2 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 Carry 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 Total Cost of Plan $2,371,200 Simulation Worksheet Qtr WK Beginning Inventory Forecast Demand Actual Production Rate (cases/hour) Actual Demand (1,000 Cases) Actual Production Actual Ending Inventory 0.0 0.0 Overtime Scheduled (hours) 1 1 1 " 1 1 1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1 2 3 4 5 6 1st 7 8 9 10 11 12 13 14 15 16 17 18 18 19 2nd 20 21 1 1 1 0.0 0.0 0.0 0.0 0.0 Lines Scheduled 10 10 10 10 10 10 10 10 10 10 10 10 10 10 8 8 8 8 8 O 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 0.0 0.0 3.6 Expected Ending Inventory 237.4 274.8 312.2 349.6 387.0 424.4 461.8 1000 499.2 536.6 574.0 611.4 648.8 686.3 670.0 ecco 653.8 637.6 243 621.3 6051 605.1 588.9 572.6 556.4 540.2 540.2 523.9 523.9 507.7 sur.! 491.5 475.3 475.3 475.4 4754 475.5 475.5 475.6 475.7 475.7 475.8 4736 0.0 no 0.0 no 0.0 no 0.0 00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 22 23 24 25 26 27 27 20 28 29 30 20 31 32 3rd 33 35 34 34 35 36 37 38 Scheduled Production 191.3 191.3 191.3 191.3 191.3 191.3 191.3 10 191.3 191.3 191.3 191.3 191.3 191.3 153.0 os 153.0 462 153.0 1520 153.0 469 153.0 1530 153.0 153.0 153.0 153.0 153.0 153.0 153.0 153.0 153.0 100 192.4 102 192.4 4022 192.4 1024 192.4 192.4 192.4 192.4 192.4 192.4 192.4 102.7 192.4 Joe 192.4 102.17 192.4 210.4 w 210.4 107 210.4 210.4 210.4 210.4 . 210.4 210.4 20 210.4 210.4 210.4 210.4 210.4 200.0 237.4 274.8 3122 122 349.6 so 387.0 424.4 461.8 499.2 100 536.6 574.0 w 611.4 648.8 686.3 670.0 acea 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 4752 475.3 475 475.4 475.5 4755 475.5 475.6 475.7 475.7 110. 475.8 wo 475.9 475.9 476.0 476.1 0! 476.1 482.7 489.2 495.7 2001 502.2 508.8 cara 515.3 521.8 528.4 534.9 541.4 547.9 554.5 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 wo 153.8 1000 153.8 100.0 153.8 ro 153.8 100 169.2 100 169.2 100 169.2 1002 169.2 169.2 4602 1802 169.2 1692 169.2 169.2 169.2 169.2 169.2 169.2 169.2 192.3 192.3 1992 4022 192.3 1923 192.3 1923 192.3 192.3 192.3 192.3 192.3 182.0 192.3 100 Joo 192.3 192.3 102.0 192.3 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 mo 203.8 22 203.8 203.8 203.8 2000 1692 169.2 169.2 400 169.2 400 169.2 400 169.2 169.2 169.2 169.2 Expected Weeks-of- Supply 1.5 1.8 2.0 2.0 2.3 2.0 2.5 2.8 3.0 3.2 3.4 3.6 3.7 3.9 4.1 4.0 3.9 20 3.8 22 3.7 20 25 3.5 24 3.4 3.2 3.1 3.1 2.9 2.8 2.6 2.5 2.5 25 2.5 25 2.5 2 2.5 25 2.5 2.5 25 2.5 2.4 24 2.4 24 2.4 2.4 43 2.4 2.3 2.4 2.4 2.4 2.5 2.5 2.5 2.6 2.7 2.8 2.9 3.0 3.2 3.3 1 . 1 1 1 4 1 4 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 4 1 1 4 1 1 1 1 0.0 0.0 0.0 0.0 0.0 0.0 no 0.0 00 0.0 0.0 0.0 0.0 0.0 0.0 475.9 %%wBH&g8985& 0.0 0.0 vo 0.0 0.0 0.0 0.0 0.0 9 9 9 9 9 9 9 9 9 9 9 9 9 11 11 11 11 11 11 11 11 44 11 44 11 11 11 11 40 41 42 43 44 45 4th 46 47 48 49 50 51 52 475.9 110.9 476.0 *100 476.1 *10! 476.1 10. 482.7 100 489.2 AOE 495.7 0 5022 508.8 515.3 521.8 528.4 5240 534.9 541.4 547.9 554.5 561.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 5th Actual Costs Stockout Cost_Hiring and Total For Week Layoff Cost Cumulative Training Cost 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 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 0 0 0 0 0 0 0 0 0 0 0 36,000 57,000 0 0 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 0 Regular Qtr Wk Production Cost 1 0 0 2 0 3 0 4 0 5 0 6 0 0 1st 7 0 8 8 0 9 0 10 0 11 0 12 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 27 0 28 0 0 29 0 30 0 31 0 32 0 3rd 33 0 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 4th 46 0 47 0 48 0 49 0 50 0 51 0 52 0 0 0 0 0 0 0 0 0 0 0 0 0 0 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 30,000 0 0 0 0 0 0 0 0 57,000 114.000 171,000 228,000 285,000 342,000 399,000 456,000 513,000 570,000 627,000 684,000 741,000 822,600 868,200 913,800 959,400 1,005,000 1,050,600 1,096,200 1,141,800 1,187,400 1,233,000 1,278,600 1,324,200 1,369,800 1,459,200 1,518,600 1,578,000 1,637,400 1,696,800 1,756,200 1,815,600 1,875,000 1,934,400 1,993,800 2,053,200 2,112,600 2,172,000 2,294,700 2,357,400 2,420,100 2,482,800 2,545,500 2,608,200 2,670,900 2,733,600 2,796,300 2,859,000 2,921,700 2,984,400 3,047,100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 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 60,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 Bradford Manufacturing - Planning Plant Production 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 Forecast Demand by Quarter (1,000 Case Units) 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 2 hours $1.00 per case (per year) ) $2.40 per case $5,000.00 per employee $3,000.00 per employee 3,000 2 500 2.000 1,500 1,000 500 0 1st (1-13) 2nd (1428) 3rd (27-39) 4th (40-52) 1st (Next Year) Aggregate Plan Lines run Overtime hours per day 1st (1-13) 10 1 Quarter (Week Numbers) 2nd (14-26) 3rd (27-39) 8 9 1 2 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 Carry 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 Total Cost of Plan $2,371,200 Simulation Worksheet Qtr WK Beginning Inventory Forecast Demand Actual Production Rate (cases/hour) Actual Demand (1,000 Cases) Actual Production Actual Ending Inventory 0.0 0.0 Overtime Scheduled (hours) 1 1 1 " 1 1 1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1 2 3 4 5 6 1st 7 8 9 10 11 12 13 14 15 16 17 18 18 19 2nd 20 21 1 1 1 0.0 0.0 0.0 0.0 0.0 Lines Scheduled 10 10 10 10 10 10 10 10 10 10 10 10 10 10 8 8 8 8 8 O 8 8 8 8 8 8 8 8 8 8 9 9 9 9 9 0.0 0.0 3.6 Expected Ending Inventory 237.4 274.8 312.2 349.6 387.0 424.4 461.8 1000 499.2 536.6 574.0 611.4 648.8 686.3 670.0 ecco 653.8 637.6 243 621.3 6051 605.1 588.9 572.6 556.4 540.2 540.2 523.9 523.9 507.7 sur.! 491.5 475.3 475.3 475.4 4754 475.5 475.5 475.6 475.7 475.7 475.8 4736 0.0 no 0.0 no 0.0 no 0.0 00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 22 23 24 25 26 27 27 20 28 29 30 20 31 32 3rd 33 35 34 34 35 36 37 38 Scheduled Production 191.3 191.3 191.3 191.3 191.3 191.3 191.3 10 191.3 191.3 191.3 191.3 191.3 191.3 153.0 os 153.0 462 153.0 1520 153.0 469 153.0 1530 153.0 153.0 153.0 153.0 153.0 153.0 153.0 153.0 153.0 100 192.4 102 192.4 4022 192.4 1024 192.4 192.4 192.4 192.4 192.4 192.4 192.4 102.7 192.4 Joe 192.4 102.17 192.4 210.4 w 210.4 107 210.4 210.4 210.4 210.4 . 210.4 210.4 20 210.4 210.4 210.4 210.4 210.4 200.0 237.4 274.8 3122 122 349.6 so 387.0 424.4 461.8 499.2 100 536.6 574.0 w 611.4 648.8 686.3 670.0 acea 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 4752 475.3 475 475.4 475.5 4755 475.5 475.6 475.7 475.7 110. 475.8 wo 475.9 475.9 476.0 476.1 0! 476.1 482.7 489.2 495.7 2001 502.2 508.8 cara 515.3 521.8 528.4 534.9 541.4 547.9 554.5 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 153.8 wo 153.8 1000 153.8 100.0 153.8 ro 153.8 100 169.2 100 169.2 100 169.2 1002 169.2 169.2 4602 1802 169.2 1692 169.2 169.2 169.2 169.2 169.2 169.2 169.2 192.3 192.3 1992 4022 192.3 1923 192.3 1923 192.3 192.3 192.3 192.3 192.3 182.0 192.3 100 Joo 192.3 192.3 102.0 192.3 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 203.8 mo 203.8 22 203.8 203.8 203.8 2000 1692 169.2 169.2 400 169.2 400 169.2 400 169.2 169.2 169.2 169.2 Expected Weeks-of- Supply 1.5 1.8 2.0 2.0 2.3 2.0 2.5 2.8 3.0 3.2 3.4 3.6 3.7 3.9 4.1 4.0 3.9 20 3.8 22 3.7 20 25 3.5 24 3.4 3.2 3.1 3.1 2.9 2.8 2.6 2.5 2.5 25 2.5 25 2.5 2 2.5 25 2.5 2.5 25 2.5 2.4 24 2.4 24 2.4 2.4 43 2.4 2.3 2.4 2.4 2.4 2.5 2.5 2.5 2.6 2.7 2.8 2.9 3.0 3.2 3.3 1 . 1 1 1 4 1 4 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 4 1 1 4 1 1 1 1 0.0 0.0 0.0 0.0 0.0 0.0 no 0.0 00 0.0 0.0 0.0 0.0 0.0 0.0 475.9 %%wBH&g8985& 0.0 0.0 vo 0.0 0.0 0.0 0.0 0.0 9 9 9 9 9 9 9 9 9 9 9 9 9 11 11 11 11 11 11 11 11 44 11 44 11 11 11 11 40 41 42 43 44 45 4th 46 47 48 49 50 51 52 475.9 110.9 476.0 *100 476.1 *10! 476.1 10. 482.7 100 489.2 AOE 495.7 0 5022 508.8 515.3 521.8 528.4 5240 534.9 541.4 547.9 554.5 561.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 5th Actual Costs Stockout Cost_Hiring and Total For Week Layoff Cost Cumulative Training Cost 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 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 0 0 0 0 0 0 0 0 0 0 0 36,000 57,000 0 0 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 0 Regular Qtr Wk Production Cost 1 0 0 2 0 3 0 4 0 5 0 6 0 0 1st 7 0 8 8 0 9 0 10 0 11 0 12 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 27 0 28 0 0 29 0 30 0 31 0 32 0 3rd 33 0 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 4th 46 0 47 0 48 0 49 0 50 0 51 0 52 0 0 0 0 0 0 0 0 0 0 0 0 0 0 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 30,000 0 0 0 0 0 0 0 0 57,000 114.000 171,000 228,000 285,000 342,000 399,000 456,000 513,000 570,000 627,000 684,000 741,000 822,600 868,200 913,800 959,400 1,005,000 1,050,600 1,096,200 1,141,800 1,187,400 1,233,000 1,278,600 1,324,200 1,369,800 1,459,200 1,518,600 1,578,000 1,637,400 1,696,800 1,756,200 1,815,600 1,875,000 1,934,400 1,993,800 2,053,200 2,112,600 2,172,000 2,294,700 2,357,400 2,420,100 2,482,800 2,545,500 2,608,200 2,670,900 2,733,600 2,796,300 2,859,000 2,921,700 2,984,400 3,047,100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 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 60,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