0. Prepare an aggregate plan for the coming year, assuming that the sales forecast is perfect. Use the workbook Bradford.xlsx.

   1. Use heuristic worksheet in the provided Excel file. 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 (your final solution needs an integer number of lines and an integer number of overtime hours for each quarter). You will need to set up the cost calculations in the spreadsheet.

   2. Copy your model in part (a) to a new worksheet in the same workbook and make the necessary modifications to create a Linear Programming model. Use the Excel Solver to find a solution. Remember that your final solution needs an integer number of lines and an integer number of overtime hours for each quarter.

AutoSave OFF BESU- Bradford Home Insert Draw Page Layout Formulas Data Review View Tell me Share a Comments X Calibri (Body) Insert 14 AI = = ab Wrap Text General PPA III. ||11 MIT WE 48- 0 DX Delete Paste I U B A * 191 Merge & Center Find & % TH Sensitivity Conditional Format Formatting as Table Cell Styles Format Sort & Filter Analyze Data Select B40 . fx A B D E F Inv. carrying cost (per case per year) Inv. carrying cost (per case per Qtr) Backorder cost (per case per year) Backorder cost (per case per qtr.) Cost of hiring Cost of lay off $1 $0,25 $2,40 $0,60 $5.000 $3.000 cells you need to complete with formulas 1 Number of employees per line 6 2 Hrs lines run per shift 7,5 3 Hrs employees paid per shift 8 4 Maximum overtime hrs per day 2 5 Wage per hr (regular) $20,00 6 Wage per hr (overtime) $30,00 7 Cases/hr for each line 450 8 Work days per quarter 65 9 Current nbr of employees 60 10 11 All production/demand are in 1000-case units 12 13 Aggregate Plan Quarter (Week Numbers) 14 1st (1-13) 2nd (14-26) 15 Lines run 10 16 Overtime hours per day 0 17 18 Beginning Inventory 200 393,8 19 Production 2.193,75 2.193,75 20 Expected Demand (fcast) 2.000,00 2.200,00 21 Ending Inventory 393,75 387,55 22 Ending Inventory Target (Rounded) 338 385 23 24 Deviation from Inventory Target 55,75 2,55 25 Employees 60 60 26 Change in employees 0 0 3rd (27-39) 10 4th (40-52) 10 0 10 0 0 387,5 2.193,75 2.500,00 81,25 520 2.193,75 2.650,00 63,75 338 408 -274,25 -326,75 60 60 0 0 27 heuristic + o A + 124% AutoSave OFF BESU Bradford Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) Insert V 14 AA V = = ab Wrap Text General V DIV WAO WE 48- 0 DX Delete v Paste B B I U * : Merge & Center LE % ) V .00 00 0 Sensitivity Conditional Format Formatting as Table Cell Styles Format Sort & Filter Find & Select Analyze Data B40 Ax fx A B D E F 28 Cost of Plan 29 Labor Regular Time 30 Labor Overtime 31 Hiring and Training 32 Layoff 33 Inventory Carry Cost 34 Stockout Cost 35 Quarter Budget 36 37 Total Cost of Plan 38 39 40 $0 $0 $0 $0 $0 41 42 43 44 45 46 47 48 49 50 51 52 53 heuristic + a + 124%