Problem $2$: Simulation

Madhu Chocolates is a small, artisanal chocolate factory located in Austin, TX$,$ specializing in bean$-$to$-$bar production. They source high$-$quality cocoa beans from Colombia and India, ensuring sustainable practices and unique flavor profiles. As they prepare for their yearly operations they asked Samarth, an expert in spreadsheet models, to model their quarterly operations to help them decide their production level and the price to charge.

Production

Elliott and Harshit, owners and solely workers at Madhu Chocolate, estimate that they can jointly produce $100$ bars per day, which involves roasting the beans, melting, molding, decorating and packaging. As a result, they estimate they can produce $6,000$ bars per quarter $($working on average $20$ days per month$),$ and their fixed cost is $\backslash (\backslash$$ $5,000\backslash )($covering rent, insurance, equipment and maintenance$).$ In addition, they estimate that the cost of their cacao beans is random in each quarter and uniformly distributed between $\backslash (\backslash$$ $0\mathrm{.}2\backslash )$ and $\backslash (\backslash$$ $0\mathrm{.}8\backslash )$ per bar $($i$.$e$.,$ the price of beans may change from one quarter to the next one$),$ while other variable costs $($including electricity, materials, etc.$)$ add up to $\backslash (\backslash$$ $1\mathrm{.}5\backslash )$ per bar. Finally, to promote sales, they committed to offer free delivery on all orders, so they must absorb the delivery cost, which they estimate to be $\backslash (\backslash$$ $0\mathrm{.}3\backslash )$ per bar.

Inventory

Given their limited space, Elliott and Harshit can store at most $1,000$ bars, and they estimate that the storage cost is $\backslash (\backslash$$ $0\mathrm{.}75\backslash )$ per bar $($to account for potential spillovers$).$ However, they partnered with Central Texas Food bank $($located less than one mile away$)$ to get extra $($unlimited$)$ storage if needed, at a cost of $\backslash (\backslash$$ $1\mathrm{.}2\backslash )$ per bar. For simplicity, they compute their inventory cost considering the average inventory over the quarter. For example, if they begin a quarter with $1,400$ bars in storage and end with $1,000$ bars, then the average inventory over the quarter is $1200($i$.$e$.,\backslash (=(1400+1000)/2\backslash ))$ and, thus, the inventory cost is $\backslash (1,000\backslash$times $\backslash$$ $0\mathrm{.}75+200\backslash$times $\backslash )\backslash (\backslash$$ $1\mathrm{.}2\backslash )=\backslash$$$990.$ Finally, they estimate that they will have $1,000$ bars in storage at the beginning of the year.

Demand

Based on historical data, Samarth estimates that demand in quarter $1$ is normally distributed with average demand being equal to $\backslash (20,000-1,300\backslash$times P $\backslash )$ and standard deviation of $1,000,$ where P is the price per bar. Moreover, he estimates that demand will growth in the following quarters at a rate following a triangular distribution with minimum $\backslash (1\backslash \%\backslash ),$ most likely value $\backslash (2\backslash \%\backslash ),$ and maximum $\backslash (5\backslash \%\backslash ),$ which is randomly drawn in each quarter $($i$.$e$.,$ the growth rate may differ in quarters $2,3$ and $4).$ For instance, if demand in quarter $2$ is $8000$ and the drawn growth rate for quarter $3$ is $\backslash (1\mathrm{.}5\backslash \%\backslash ),$ then demand in quarter $3$ is $\backslash (8,000\backslash$times$(1+1\mathrm{.}5\backslash \%)=8,120\backslash ).$ Finally, if demand in any quarter exceeds their inventory available $($given by their initial inventory in each quarter plus their production$),$ Madhu takes backorders, committing to deliver them the next quarter but incurring in a backorder cost of $\backslash (\backslash$$ $1\mathrm{.}25\backslash )$ per bar. For example, if demand in quarter $1$ is $8,000$ and they only have $7,000$ bars available $(\backslash (6,000+1,000\backslash )$ from inventory$),$ then the backorder cost is $\backslash (1,000\backslash$times $\backslash$$ $1\mathrm{.}25=\backslash$$ $1,250\backslash ).$

In the next questions, assume for simplicity that all quantities can be fractional.

$1.(20$ points$)$ Build a Crystal Ball model to estimate Madhu Chocolates' net profit at the end of the year, assuming that the price of coffee beans is uniformly distributed between $\backslash (\backslash$$ $0\mathrm{.}2\backslash )$ and $\backslash (\backslash$$ $0\mathrm{.}8\backslash )$ per bar.

$2.(5$ points$)$ Based on the model in part $1.,$ what is the price per bar that maximizes Madhu Chocolates' expected profit? To answer this, it is enough a level of precision of $\backslash$$$1.$

$3.(5$ points$)$ Samarth realizes that the assumption regarding the price of cocoa beans may be too optimistic. Instead, he decides to use historical information on beans prices, which is shown in the sheet $"$Q$2-$ Historical Prices". Update your model to use this information. What is the expected profit?

$4.(5$ points$)$ After careful evaluation of the production process, Samarth realizes that the main bottleneck is the roasting of cocoa beans. He estimates that, if they invest $\backslash$$$2,000$ to update their commercial roaster, they would be able to produce $2,000$ additional bars per quarter starting from the second quarter. Update your model in part $1.$ to evaluate whether they should do the investment.

$5.(5$ points$)$ Instead of committing to make the investment, Samarth decides they should first observe the demand in the first quarter and then decide whether to make the investment. What is the minimum demand in the first quarter that would justify the investment in terms of expected profit? To answer this, it is enough a level of precision of $500$ bars.

Table $3$: Sources of uncertainty in part $1.$