Assignment

$5$

: Using Simulations for Inventory ManagementImagine you are the manager of a small supermarket, and you need to determine how much of a popular brand of breakfast cereal to stock on your shelves each week to maximize profit while minimizing waste. The cereal has a shelf life of only ten days. Therefore, you plan on holding theinventory only for a maximum of a week.The weekly demand for this cereal follows the probability distribution below:Demand

$($

Boxes

$)$

$$Probability

$1$

$0$

$0$

$.$

$1$

$0$

$2$

$0$

$0$

$.$

$2$

$0$

$3$

$0$

$0$

$.$

$2$

$5$

$4$

$0$

$0$

$.$

$2$

$0$

$5$

$0$

$0$

$.$

$1$

$5$

$6$

$0$

$0$

$.$

$1$

$0$

Each cereal box costs $

$2$

$.$

$0$

$0$

$,$

$$and you sell it for $

$4$

$.$

$0$

$0$

$.$

$$Any unsold cereal boxes at the end of the week are sold for $

$0$

$.$

$5$

$0$

$$per box.Your task is determining the optimal number of cereal boxes to stock on your shelves at the beginning of each week to maximize your profit over a

$5$

$0$

$-$

week simulation period. Consider the costs, demand probabilities, and selling prices when making your decision.Please conduct the simulation for

$5$

$0$

$$weeks and present your findings in an MS Excel file. Include relevant calculations and explanations to support your recommendation for the optimal stocking quantity