Question $3$

Recall the jackets problem in Tutorials $4$ and $5.$ The storyline remains the same, but some of the numbers are different. All values are provided in the Excel tab $"$Q$3".$

Your company sells winter jackets. You receive two shipments of jackets during the selling season, and each shipment contains $800$ jackets. The first shipment arrives on November $1.$ The second shipment arrives on January $1.$ Each jacket costs you $\backslash (\backslash$$ $500\backslash )$ and you cannot refund unsold jackets.

Demand for jackets can also be divided into two periods. During November $1-$December $31,$ we sell the jackets for $\backslash (\backslash$$ $1,800\backslash )$ each and the distribution of demand is as follows:

If we run out of inventory between November $1$ and December $31,$ then the extra demand is lost. If there are unsold jackets, we can sell them along with the jackets from the second shipment. However, if your inventory on January $1($remaining inventory $+$ new shipment$)$ is $650$ or more jackets, then you will need to rent extra storage for $\backslash (\backslash$$ $75,000\backslash )$ total.

Starting on January $1,$ we reduce the selling price to $\backslash (\backslash$$ $1,500\backslash )$ for all of the remaining jackets. The distribution of demand during this period is as follows:

Any unsold jackets as of March $1$ will be donated to a charity.

In this question, you MUST use the stated method in order to get points. If you submit Tutorial $5,$ you will not get any credit $($see notes$),$ especially for parts b and c$.$

a$)$ Set up one round of simulation on cells C$29$:C$41$ to compute the total profit for one trial. You may refer to Tutorial $5$ for help with this calculation.

b$)$ Use a data table to replicate $\backslash (\backslash$underline$\{200\}\backslash )$ simulation trials. Report the average profit in cell D$7.$

c$)$ Use the average computed from your data table $($i$.$e$.$ part b$)$ AND one of the tools under "What$-$if Analysis" in Excel to report on the average profit if each shipment contains $350,450,550,650,$ vs$.750$ jackets. Name the necessary cells so that the resulting report is easy to read! Highlight the optimal shipment size on your Excel output.