SCMA 5300 Due Feb. 18, 2020 Dr. Hupman borryris 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? Tasks 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. Recommendation (Turn in a Word document with your response.) Compose a short (less than 1 page) written summary of your analysis and recommendation to management that includes The EMV of harvesting now, The EMV of waiting to harvest, Whether a change in the probability of the storm changes the better course of action, The expected value of perfect information on the probability that the storm will hit, Any other factors that the winery might want to consider, and A final recommendation for how management should proceed. What to turn in: File of your choice with the two decision trees. Excel file with the sensitivity analysis. Word file with your recommendation that includes all required components.SCMA 5300 Due Feb. 27, 2022 Dr. Hupman Excel Assignment #1: Abridged Freemark Abbey Winery Case' Creating Decision Trees and Spreadsheet Models for Analysis Case In this assignment, you will gain experience constructing decision trees to derive insight for improved decision making. This case is based on an actual decision faced by the management of the Freemark Abbey Winery located in St. Helena, CA. 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 form 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 botrysis 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 botryris 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 1. Adapted from Krasker, William S. "Freemark Abbey Winery." Harvard Business School Case 181-027, August 1980. (Revised March 1994.)