GA: Dire Wolf Redux You are working for Dire Wolf Industries (DWI), helping them run their Sales & Operations Planning (8&OP) program for a line of make to stock items. Specifically, you are running the aggregate planning model to help assist the Chief Operating Officer (C00) and the Vice President of Sales to agree upon the next 12 months plan. The aggregate model is attached here. The model provided here is offered without any input data. That data is provided below, and you must enter it by hand into the spreadsheet. When solving this model, while the number of workers must be an integer, the amounts of money, hours and items do not have to be integers. The solver settings can be seen here. Part 1 0.0}2.0 points [graded] The model provided above is offered without any of the input data. The demand data for the model is provided below, and you must enter it into the spreadsheet. The aggregate monthly demand of the item is as follows: January February March April 11068 10012 9076 6932 May June July August 4036 2092 1172 2932 September October November December 5108 6044 7948 12268 The remaining data for the model is provided below, and you must enter it into the spreadsheet. Please enter the input values with all the decimal values given below. . Material cost ($/unit): 16.7875 . Inventory holding costs ($/unit/month): 3.6125 . Cost of stockouts ($/unit/month): 6.5875 . Cost for hiring and training new worker: 2125.0 . Cost for laying off an employee: 4250.0 . Labor hours required per item produced: 0.84 . Regular worker cost ($/month): 2040.0 . Overtime cost ($/hour): 14.4196 . Outsourcing cost ($/item): 38.4625 . Hours worked by employee per month: 175.84 . Max overtime (hours/month/employee): 12.32 . Starting inventory: 2000.0 . Ending inventory (min): 2000.0 . Starting backlog: 0.0 . Allowable ending backlog (max): 0.0 . Starting workforce: 24.0 . Ending workforce (min): 24.0 . Ending workforce (max): 24.0 . Base price ($/item): 44.8375 You want to develop an initial aggregate plan for the next 12 months. Use the initial data provided above and assume that there are no planned promotions or discounts over the next 12 months. Find the optimal plan over the next 12 months. Based on this optimal solution, answer the following questions.What is the total annual profit? Enter your answer in dollars, without currency symbol or thousands delimiters. For example, if your answer is $1,234 then type 1234 in the space. Submit You have used 0 of 2 attemptsPart 2 0.0.0.0 points (graded) Using the status quo [Part1) as starting point, the senior team has asked you to assess the impact of running a three-month promotion during the Summer with a 15% discount in June [month 6), a 25% discount in July [month 7), and a 15% discount in August (month 8). Use an elasticity value of 5, and solve optimally. Will the total annual profit increase in this scenario, compared to the profit in Part 1? Select an option v Solving optimally, what is the total annual profit with this promotion? Enter your answer in dollars, without currency symbol or thousands delimiters. 3meit You have used 0 of 2 attempts save Part 3 0.0!23 points (graded) There are some concerns about the sustainability of the operations at the subcontractors. Using the status quo [Part 1) as starting point, the senior team has asked you to assess the impact of a policy that would eliminate all outsourcing of production. Solve optimally. How will the total annual profit under this policy compare to the profit of the status quo (Part 1)? ISelect an option v I Solving optimally, what is the total annual profit under this policy? Enter your answer in dollars, without currency symbol or thousands delimiters. Smeit You have used 0 of 2 attempts Save Part 4 0.0.12.0 points [graded] There is some new technology coming into the market that may allow Dire Wolf to produce the items faster than before. Using the status quo (Part 1] as starting point, you have been asked to assess the impact that such a technology could have if it could reduce the labor hours required per item produced by one third. (Hint: This means that the hours required to produce an item using the new technology would be two thirds of the hours required to produce an item in the status quo). Solve optimally. Which of the following statements would be true considering the faster production scenario, compared to the status quo [Part 1)? SQIECI all correct answers E] Outsourced production decreases by two thirds D Firing employees is no longer done E] Total annual profit increases [1 Outsourced production is no longer used E] Number of employees fired decreases by one third Solving optimally, what is the total annual profit in this scenario? Enter your answer in dollars, without currency symbol or thousands delimiters. For example it your answer is $1,234 then type 1234 in the space. Part 5 0.0120 points [graded] There are some concerns about the limitations of Dire Wolf's warehousing capacity. Using the status quo (Part 1] as starting pointI the senior team has asked you to assess the impact of limiting inventory to a maximum of 4000 items. (Hint: This means that at no point can the inventory levels exceed this limit). Solve optimally. What is the total annual profit? Enter your answer in dollars, without currency symbol or thousands delimiters. For example it your answer is $1,234 then type 1234 in the space. Smeit You have used 0 of 2 attempts Save Dire Wolf Industries Annual Costs by Category T Base Demand Price Discount (9% decrease Total Annual Cost 0 0 Regular Labor Cost 0% Total Annual Profit 0 0 Overtime Labor cost 0% Avg Cost ($/unit) 0 Cost of Hiring 0% Avg Profit ($/unit) O Cost of Firing JOU A W N - 0% 0 Cost of Inventory 0% 0 Cost of Stockouts 0% 0 Cost of Materials 0% O Cost of Outsourcing 8 0% 0% Inputs: 10 Material cost ($/unit) Hours worked By employee per month 11 0% Inventory holding costs ($/unit month Max overtime (hours/month/employee) 12 0% Cost of stockouts ($/unit/month Starting inventor Cost for hiring and training new worker Ending inventory (min) Base Price Cost for laying off an employed Starting backlog Elasticity 0 Keep this positive since discount is positive Labor hours required per item produced Allowable ending backlog (max) Regular worker cost ($/month) Starting workford Overtime cost ($/hour Ending workforce (min) Outsourcing cost ($/item) Ending workforce (max) Employees Overtime Backlogged Internal Outsourced Monthly Maximum Time Maximum Period Inventory (#) Workforce Employees Hired Fired Production Production Available Demand Price Production Overtime (hrs) (units) (units) (units) Forecast (units) (units) (hours) 2,267 16 440.0 0.00 2,240.0 27.0 2,267 2 0 0 0 N - 4,770 16 440.0 2.67 2,240.0 260.3 4,770 2 0 0 3 7.039 16 440.0 31.67 2,240.0 0.0 7,039 2 0 8,772 5 0.0 247.00 1,517.7 0.0 8,772 2 0 ,781 0 0.0 0.00 1,256.0 0.0 9,781 2 0 10,304 0.0 0.00 523.0 0.0 10,304 2 0 7 11,560 0 0.0 0.00 1,256.0 0.0 1,560 2 8 12,816 0.0 0.00 1,256.0 0.0 12,816 2 14.072 0.0 0.00 1,256.0 0.0 14,072 2 10 15,416 0 264.0 0.00 1,344.0 0.0 15,416 2 11 16,760 264.0 0.00 1,344.0 0.0 16,760 2 12 19,672 0 264.0 0.00 1,344.0 1,568.0 19.672 2 Sum 133,229 53 16 16 2,112 281 17,817 1,855Set Objective: SC$3 To: . Max Min Value Of: 0 By Changing Variable Cells: SES23:51534 Subject to the Constraints: SC$34 > = SG$15 SD534 = $G$19 SES23:SF$34 = integer Change SG523:5G$34 = SMS23:SM$34 Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close