Please set up an Excel file to analyze the case study described below. Please show the Excel formulas that you use as well.

Baker Street Bakery (BSB) is well known for producing the best organic wholegrain bread in the town of Piscataway, NJ. Based on BSB's current sales records, the daily demand for their bread can be well approximated by a normal distribution with mean value of 500 and standard deviation of 75. (For the purposes of this exercise, we consider only one type of bread; similar probabilistic models could be set up for other products.)

The bread is baked in the early morning at a cost of $2 per loaf, then sold during that day for $5 per loaf. If some bread remains unsold on the day when it was baked, then it is relabeled as day-old bread and sold subsequently at a discount price of $ 2.50 per loaf. We assume that the maximal amount of relabeled bread that can be sold the next day at the discounted price is 100, and that the rest of the bread (if there are still loafs left over from the previous day) is donated to a charity.

You have been asked to assist the manager of the bakery to choose the daily bread production level. You have to consider two criteria: 1) you want to maximize BSB's average profits, and 2) you want to keep stockout probability below a certain level.

(a) Set up a simulation model, assuming that a fixed amount of bread is baked every day: first, you will have to choose this amount by making a reasonable guess. In your model, use the appropriately truncated and discretized normal distribution (as discussed in class) to simulate daily bread demands. Record your net daily profits over a simulated 50-day time period, also including bread sales at the discounted price.

c

(b) Since you want to optimize the bakery's profits under the stated stockout probability constraint, you are interested in experimenting with the production level that you chose in (a). You can try to find by trial and error the "best" setting for the order size, considering both criteria 1) and 2). This search could be formalized, but now for simplicity just experiment with the following tentative production level settings: 300, 400, 500, 600, and 700. For each given production level setting, estimate the profit and stockout probability by running your 50-day simulation repeatedly 10 times. Record the estimated profits and the stockout probability based on your simulation runs.

(c) For the best order size that you found in (b) which meets the probability constraint, create numerical and graphical summaries of your analysis. Based on the 50-day simulation runs, you can estimate the mean and standard deviation of the demand and the daily profits. Next, we need a line plot of the daily demands and profits. Finally, for each production level analyzed, plot the average profits and the stockout probability based on your simulation runs. Observe (by repeated runs) that these summaries also will change somewhat with each simulation.