a$)$ Assuming no backlogs, no subcontracting, and no new hires, mathematically formulate a linear program that models the aggregate planning problem at Skycell. $(20$ points$)$

Hint: review the Red Tomato example that we covered in class. First, carefully define the decision variables similar to Page $27$ of the post$-$class slides for aggregate planning. A mathematical formulation is similar to what you find on Slide $32$ of the post$-$class slides.

b$)$ Formulate the linear program you found in Part $($a$)$ in Excel and solve it using excel solver. What is the optimal annual cost of operating the plant? Use Sheet $$Part $($b$)$ of the Excel template file for your formulation. $(20$ points$)$

Hint: review the excel formulation for Red Tomato example that we covered in class.

c$)$ What is the $$average$$ per unit cost of in$-$house production $($including holding and overtime cost$)?(5$ points$)$

Hint: You calculated the optimal annual cost in Part $($b$).$ You can also calculate the total number of units produced over the year by summing up monthly productions found in Column E of the Excel template file. Then the average unit cost is simply the total cost divided by the total number of units produced.

d$)$ Now suppose a third party has offered to produce cell phones as needed at a cost of $26$ euros per unit $($this includes the component costs of $20$ euros per unit$).$ Formulate an aggregate planning problem for Skycell that includes the subcontracting decision in Excel and solve it using Excel solver to find out how Skycell should use the third party. Use Sheet $$Part $($d$)$ of the Excel template file for your formulation. $(10$ points$)$

e$)$ From Part $($c$),$ you should be able to observe that the average per unit cost of in$-$house production is less than $26$ euros. Why would Skycell use the third party even when per unit cost of the third party is higher than the average per unit cost for in$-$house production? $(5$ points$)$