You have convinced Steve that some additional analysis might be useful. You are going to capture the uncertainty of the open$-$market price and other estimates and use Monte Carlo simulation to better understand the influence of this uncertainty on the decision of raisin price and amount of contracted grapes.

From earlier conversation with Steve, you already know that the open$-$market price in the fall is highly variable from year to year. Steve provided historical costs, and the lowest price was $$0\mathrm{.}20,$ the highest was $$0\mathrm{.}35$ and the most likely price was $$0\mathrm{.}25.$

While the spring price for contracted grapes is more certain than the price in the fall it could still vary from the expected price of $$0\mathrm{.}25.$ You decide to vary the price $+/-10\%$ to see if that uncertainty affects profit. Use a uniform distribution.

In addition, there is also uncertainty with the negotiation with HoC$$s customers. While HoC believes the contract will be about $$2\mathrm{.}20$ for $750,000$ pounds, you decide to explore the effect of the $$2\mathrm{.}20$ price being for different quantities. The most likely will be $750,000$ pounds and the minimum will be set at $675,000$ pounds and the maximum will be set at $825,000$ pounds.

Finally, there is uncertainty with the supplier used to outsource the production of raisins when demand in higher than HoC$$s capacity. There is an $80\%$ probability that the cost will be $$0\mathrm{.}45$ per pound as expected and a $20\%$ probability that the cost will be $$0\mathrm{.}50$ per pound.

Requirements for Horne of California $($B$)$:

Assume that the provided spreadsheet is correct. Modify the model as needed to implement a Monte Carlo simulation that incorporates the above uncertainties.

$1)$

Run the Monte Carlo simulation for $5000$ iterations. Consider these questions and incorporate what is relevant into your report for Steve.

a$.$

What is the probability that profit will be less than the $$448,125$ estimated in the first analysis.

b$.$

Provide the range of profit that has a $90\%$ probability.

c$.$

Look at the tornado chart, are all the uncertain variables affecting profit enough to stay in the model?

d$.$

Save this file to turn in with graphs to support the information to the above questions.

$2)$

Explore Raisin Price decision

a$.$

Copy the model you created in $(1)$ into a new file. Include $$RaisinPrice$$ in the file name.

b$.$

Set the value of contracted grapes to $1,000,000$ pounds. Perform a simtable analysis on Raisin Price with values from $$1\mathrm{.}70$ to $$2\mathrm{.}60$ by increments of $$0\mathrm{.}10.$

c$.$

Create a scattergraph of the simtable following best practices.

d$.$

Determine a price that you recommend based on the results of your simulation.

$3)$

Explore Contracted Grapes decision

a$.$

Copy the model you created in $(1)$ into a new file. Include $$ContractedGrapes$$ in the file name.

b$.$

Set the Raisin Price to the value that you recommend in $(2$d$).$ Perform a simtable analysis on Contracted Grapes with values from $500,000$ to $2,750,000$ by increments of $250,000.$

c$.$

Create a scattergraph of the simtable following best practices.

d$.$

Using the results, what do you recommend for Contracted Grapes.