Reference no: EM132847189

Excel Assignment #1: Abridged Freemark Abbey Winery Case

Overview of the Decision

A storm is approaching the winery. Management must decide whether to harvest the Riesling grapes immediately or to leave them on the vines through the storm. A light, warm rain sometimes causes a beneficial mold (botrytis cinerea) to from on the grape skins, which results in a highly-valued, complex sweet wine. On the other hand, the storm could ruin the crop of grapes entirely.

The Winery

Freemark Abbey Winery is located within the famed Napa Valley and produces only premium wines from the very best grape varieties. The winery produces about 25,000 cases of wine bottled under its label each year, with the majority being Cabernet Sauvignon and Chardonnay. It produces about 1,000 cases of Riesling each year, with each case containing 12 bottles of wine.

Winemaking

Wine is produced from grapes when the naturally occurring fruit sugar is converted by yeast into alcohol and carbon dioxide. The wine maker influences the type of wine created through many decisions throughout the vinification process. An important piece of the process is the timing of harvesting the grapes. As they ripen, the sugar levels increase while the acidity decreases. Thus, the timing of the harvest determines the balance between sweetness and acidity, but other factors such as the weather affect the process and can prevent the ideal balance from occurring.

Riesling is a sweet wine that comes in several styles. Harvesting the grapes at 20% sugar results in a "dry" or "near dry" wine. Harvesting grapes at 25% sugar results in a sweet, full-bodied wine because more sugar remains after the fermentation process. A third, rare style occurs when the botrytis mold develops on the grapes. The mold allows water to evaporate, leaving the sugar behind, and causing 35% or greater sugar content in the grapes. The resulting wine is both highly complex and sweet.

The Decision Problem

Weather reports indicate a 50/50 chance that a large rainstorm will hit Napa Valley. The storm originated over warm water near Mexico, leading management to estimate there is a 40% chance that if the storm strikes, it will lead to the botrytis mold, resulting in a wine that will sell for $8.00/bottle, but will produce 30% fewer bottles due to the reduction in water volume. (The vinification and bottling costs remain the same in spite of the reduced volume.) If the botrytis does not form, the rain could swell the grapes and decrease the sugar concentration, resulting in a thin wine that would sell wholesale (under a different label) for only $2.00/bottle.

Management could harvest immediately to avoid the risk of the storm altogether, but the grapes are not quite ripe yet. Currently, they will produce a wine that will sell wholesale (under a different label) for about $2.85/bottle.

If management chooses not to harvest immediately, and the storm does not hit, the grapes will be left to further ripen. With luck, the grapes will likely reach 25% sugar, producing a wine that will sell for $3.50/bottle. With less favorable weather, the grapes would likely still reach 20% sugar, producing a wine that will sell for $3.00/bottle. Management believes reaching 25% sugar or 20% sugar are equally likely. If the weather is especially unfavorable (which is estimated to occur with a 20% probability), the acidity will drop too low before the grapes reach 19% sugar, which necessitates an immediate harvest. In this case, the wine produced will sell for $2.50/bottle.

Should management harvest now, or should it wait?

Questions:

1) Construct the decision tree for the original decision. (You may draw this neatly by hand, use figures in PowerPoint, or use another software of your choosing.)

2) Construct a spreadsheet model to calculate the EMV of the original decision.

3) Use "what-if" data tables in Excel to conduct a sensitivity analysis to the probability of the storm for the original decision.

4) Construct the decision tree to calculate the expected value of perfect information on whether or not the storm hits.

5) Calculate the expected value of perfect information based on the tree constructed in #4