Solver Parameters X Set Objective: SC$3 To: Max Min Value Of: 0 By Changing Variable Cells: SE$23:51$34 Subject to the Constraints: $C$34 > = SG515 Add SD$34 = $G$19 SE$23:SF$34 = integer Change SG$23:5GS34 = SM$23:5M$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 Part 1 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.09:04 f myQ GT 2 45 1 74% Document View . . . Operations Management GA: Dire Wolf Redux You are working for Dire Wolf Industries (DWI), helping them run their Sales & Operations Planning (S&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 (COO) and the Vice President of Sales to agree upon the next 12 months plan. The aggregate model is attached here: Dire Wolf Industries Annual Costs by Category T Base Price Discount (9% Demand decrease) Total Annual Cost $ Regular Labor Cost 06 otal Annual Profit S Overtime Labor cost 096 U A W N - Avg Cost ($/unit) #DIV/O! Cost of Hiring Avg Profit ($/unit) #DIV/O! Cost of Firing 096 Cost of Inventory 096 Cost of Stockouts 6 0% Cost of Materials 7 096 Cost of Outsourcing 8 096 Inputs: 10 096 Material cost (S/unit) Hours worked By employee per month 11 096 Inventory holding costs (S/unit/month) Max overtime (hours/month/employee) Cost of stockouts (S/unit/month Starting inventory Cost for hiring and training new worker Ending inventory (min) Base Price Cost for laying off an employee Starting backlog Elasticity Keep this positive since discount is positive Labor hours required per item produced Allowable ending backlog (max) Regular worker cost (S/month) Starting workforce Overtime cost (S/hour) Ending workforce (min) Outsourcing cost (S/item Ending workforce (max) Backlogged Internal Monthly Employees Employees Overtime Outsourced Demand Maximum Maximum Period Time Inventory (#) Workforce Fired (hrs) Production Production Available Price Hired (units) (units) Forecast Production Overtime (units) (units) (units) (hours) 2,267 440.0 0.00 2,240.0 27.0 #DIV/O! 4,770 440.0 2.67 2,240.0 260.3 ,770 2 O S #DIV/O! 7,039 440.0 31.67 2,240.0 0.0 7,039 2 #DIV/O! 8,772 0.0 247.00 1,517.7 0.0 8,772 2 o $ #DIV/O! 9,781 0 0.0 0.00 1,256.0 0.0 9,781 2 o S #DIV/O! 10,304 0 0.0 0.00 523.0 0.0 10,30412 #DIV/O! 11,560 0 0.0 0.00 1,256.0 0.0 11,560 2 o S #DIV/O! 12,816 0.0 0.00 1,256.0 0.0 12,816 2 o S #DIV/O! 9 14,072 0 0.0 0.00 1,256.0 0.0 14,072 2 o $ #DIV/O! 10 15,416 0 264.0 0.00 1,344.0 0.0 15,416 2 o S #DIV/O! 11 16,760 264.0 0.00 ,344.0 0.0 16,760 2 #DIV/O! 12 19,672 264.0 0.00 1,344.0 1.568.0 19,672 2 #DIV/O! Sum 133,229 2,112 17,817 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: O9:050 ' 8.511 GHQ? ..II 74%- 6 Document View Labor hours required per item produced: 0.84 Regular worker cost ($/month): 2040.0 Overtime cost (SE/hour): 14.4196 Outsourcing cost (Fa/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 ($litem): 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. Part 2 Using the status quo (Part 1) 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 Yes, it will increase No, it will remain the same No, it will decrease It cannot be determined Solving optimally, what is the total annual profit with this promotion? Enter your answer in dollars, without currency symbol or thousands delimiters. Part 3 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)? Select an option It will decrease more than 5% It will decrease about 3% It will decrease less than 1% It will increase It cannot be determined Solving optimally, what is the total annual profit under this policy? Enter your answer in dollars, without currency symbol or thousands delimiters. Part 4 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)? 9:050 ' 8.51} 339'? ..II 74%- 6 Document View It will decrease less than 1% It will increase It cannot be determined Solving optimally, what is the total annual profit under this policy? Enter your answer in dollars, without currency symbol or thousands delimiters. Part 4 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)? Select all correct answers Outsourced production decreases by two thirds Firing employees is no longer done Total annual profit increases Outsourced production is no longer used 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, if your answer is $1,234 then type 1234 in the space. Part 5 There are some concerns about the limitations of Dire Wolf's warehousing capacity. Using the status quo (Part 1) as starting point, 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, if your answer is $1,234 then type 1234 in the space. My stats Today CF Scote2NlA Answered:1 Skipped:1 24