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 forExcelandLibreOffice. The model provided hereis offered without any input data. That data is provided below, and you must enter it by hand into the spreadsheet. When solvingthis model, while the number of workers must be aninteger, the amounts of money, hours and items do not have to be integers.

• Material cost ($/unit): 5.6429
• Inventory holding costs ($/unit/month): 1.2143
• Cost of stockouts ($/unit/month): 2.2143
• Cost for hiring and training new worker: 1250.0
• Cost for laying off an employee: 2500.0
• Labor hours required per item produced: 0.3643
• Regular worker cost ($/month): 1200.0
• Overtime cost ($/hour): 11.1765
• Outsourcing cost ($/item): 12.9286
• Hours worked by employee per month: 133.45
• Max overtime (hours/month/employee): 9.35
• Starting inventory: 3500
• Ending inventory (min): 3500
• Starting backlog: 0
• Allowable ending backlog (max): 0
• Starting workforce: 24
• Ending workforce (min): 24
• Ending workforce (max): 24
• Base price ($/item): 15.0714

Part 1 :

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?

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.

Question: Will the total annual profit increase in this scenario, compared to the profit in Part 1?

Question : Solving optimally, what is the total annual profit with this promotion?

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

Solving optimally, what is the total annual profit under this policy?

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

-Total annual profit increases

-Firing employees is no longer done

-Outsourced production decreases by two thirds

-Outsourced production is no longer used

-None of the above

Solving optimally, what is the total annual profit in this scenario?

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 7000 items. (Hint: This means that at no point can the inventory levels exceed this limit). Solve optimally.

What is the total annual profit?