Calibri 11 A A Aa Po Paste B I U ab A Undo Clipboard Page 3 of 3 58F Clear 1061 words E Font Questions T Paragraph Emphasis Heading 1 Normal OPMA 3306 Operations Management Ops Analytics Case 6: Developing an Aggregate Plan Styles 1. Prepare an aggregate plan for the coming year, assuming that the sales forecast is perfect. Use the spreadsheet "Bradford Manufacturing." In the spreadsheet, an area has been designated for your aggregate plan solution. Supply the number of packaging lines to run and the number of overtime hours for each quarter. You will need to set up the cost calculations in the spreadsheet. You may want to try using the Excel Solver to find a low-cost solution. Remember that your final solution needs an integer number of lines and an integer number of overtime hours for each quarter. (Solutions that require 8.9134 lines and 1.256 hours of overtime are not feasible.) It is important that your spreadsheet calculations are set up so that any values in the number of lines and overtime hours rows evaluates correctly. Your spreadsheet will be evaluated based on this. 3. Find a solution to the problem that goes beyond just minimizing cost. Prepare a short write-up that describes the process you went through to find your solution and that justifies why you think it is a good solution. Text Predictions: On Accessibility: Investigate DELL O X W Focus Editing Dictate Editor Reuse Files Voice Editor Reuse Files > + 100 10:37 PM 10/26/2022 Ch08_Bradford Manufacturing.xlsx Open with A B D E F G H I J 1 1,000 case units. Quarter (Week Numbers) 2 1st (1-13) 2nd (14-26) 3rd (27-39) 4th (40-52) 1st (Next Year) Notes 3 Forecast Demand 2,000 4 Ending Inventory Target 338 2,200 385 2,500 2,650 408 338 2,200 Each number is a 13 week forecast. This is calculated based on 6 weeks of future forecast demand. 5 6 Planning Data 7 Initial number of employees 8 Emplyees per line Numbers 60 employees 6 Units of measure Notes Forecast Demand by Quarter (1,000 Case Units) 9 Standard production rate (each line) 450 Cases per hour 10 Employee pay rate $20.00 per hour 11 Overtime pay rate 12 Standard hours per shift 13 Maximum overtime per day 14 Inventory carry cost 15 Stockout cost $30.00 per hour 7.5 hours 2 hours $1.00 per case (per year) $2.40 per case 16 Employee hiring and training cost $5,000.00 per employee 17 Employee layoff cost $3,000.00 per employee 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) 18 Aggregate Plan Quarter (Week Numbers) 21 Lines run 22 Overtime hours per day 1st (1-13) 10 0 2nd (14-26) 10 0 3rd (27-39) 4th (40-52) 10 10 0 0 Notes Construct solution here. 23 24 Beginning Inventory 25 Production 26 Expected Demand 27 Ending Inventory 200.0 393.8 387.5 81.3 2,193.8 2,193.8 2,193.8 2,193.8 2.000.0 2,200.0 2.500.0 2,650.0 393.8 387.5 81.3 -375.0 28 29 Deviation from Inventory Target 55.3 2.9 -326.4 -713.5 30 Employees 60 60 60 60 31 32 Cost of Plan 33 Labor Regular Time 34 Labor Overtime 35 Hiring and Training 36 Layoff 37 Inventory Carry Cost 38 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. Based on negative deviation from target. 39 Quarter Budget $0 $0 $0 $0 40 41 Total Cost of Plan $0 1 of 19,918 < > @Oct 25, 2022, 3:41 PM (1 day ago) 9:32 PM (1 hour ago) +4