In the Citrus Sunshine Company Case, Julia Johnson and Stewart Thompson analyzed a light orange juice project for the Citrus Sunshine Company. The project required an initial investment of $570,000 in fixed assets (including shipping and installation charges), plus a $10,000 addition to net working capital. The machinery would be used for 4 years and be depreciated on the basis of a 3-year MACRS class life. The appropriate MACRS depreciation allowances are 0.33, 0.45, 0.15, and 0.07 in Years 1 through 4, respectively, and the machinery is expected to have a salvage value of $100,000. If the project is undertaken, the firm expects to sell 425,000 cartons of light orange juice at a current dollar (Year 0) wholesale price of $2 per carton. However, the sales price will be adjusted for inflation, which the executive committee expects to average 5% annually, so the actual expected sales price at the end of the first year is $2.10, the expected price at the end of the second year is $2.205, and so on. The light orange juice project is expected to cannibalize the before-tax profit Citrus Sunshine earns on its regular orange juice line by $20,000, because the two product lines are somewhat competitive. Further, the company expects cash operating costs to be $1.50 per unit in Year 0 dollars, and the executive committee expects these costs to increase by 2% per year. Total operating cash costs during the first year of operation (Year 1) are expected to be ($1.50)*(1.02) *(425,000) = $650,250. Citrus Sunshines tax rate is 40%, and its cost of capital is 10%. When Julia and Stewart presented their initial analysis to Citrus Sunshines executive committee, things went well, and they were congratulated on both their analysis and their presentation. However, several questions were raised. In particular, the executive committee wanted to see some type of risk analysis on the projectit appeared to be profitable, but what were the chances that it might nevertheless turn out to be a loser, and how should risk be analyzed and worked into the decision process? As the meeting was winding down, Julia and Stewart were asked to start with the base case situation they had developed and then to discuss risk analysis, both in general terms and as it should be applied to the light orange juice project. To begin, Julia and Stewart met with the marketing and production managers to get a feel for the uncertainties involved in the cash flow estimates. After several sessions, they concluded that there was little uncertainty in any of the estimates except for unit sales and salvage value. Cost and sales price estimates were fairly well defined, but unit sales could vary widely. Also, the realized salvage value could be quite different from the $100,000 estimate. (In theory, sales price is also uncertain, but companies typically set sales prices on the basis of competitors prices, so, at least initially, it can be treated as certain.) Julia and Stewart also discussed the scenarios probabilities with the marketing staff. After considerable debate, they finally agreed on a guesstimate of 15% probability of the poorest outcome, 70% probability of an average outcome, and 15% probability of the best outcome. In addition, Citrus Sunshines internal auditors require that all sensitivity analysis consider changes in at least the following three variables: sales quantity, salvage value, and the cost of capital. Company policy also mandates that each of the variables be allowed to deviate from its expected value by plus or minus 10%, 20%, and 30% in such an analysis. When Stewart gave you the memo he quietly muttered something that you couldnt quite comprehend about being sure to discuss Monte Carlo at the meeting. You informed him that although its regarded as a nice vacation destination, you have never been there, so you will have to research its relevance to risk analysis. Citrus Sunshine Decision Analysis Case 2 The first step is to develop the financial model needed to prepare a report to the Executive Committee. Over the next week, create the Excel model.

1. Using Excel, create the following cases (one case per tab clearly labeled), including computation of the NPV (Three tabs). It is strongly advised to make the Base Case dynamic enough to accommodate all subsequent cases, as it will save you a lot of additional work on the subsequent 20 tabs. a. Base Case (when it works your NPV should be $166,719.) b. Best Case c. Worst Case

2. Using Excel, conduct analysis using the guidance of company policy to guide the various adjustments, including the NPVs. (One case per tab clearly labeled): a. Quantity (6 tabs) b. Salvage Value (6 tabs) c. Discount Rate (6 tabs)

3. In another Excel tab, provide a summary table of your findings and use that table to generate a sensitivity diagram. (One tab).

4. In another Excel tab, use the probabilities developed in conjunction with the marketing department, and the NPVs from scenario analysis, to calculate the projects differential riskadjusted NPV and the standard deviation. (One tab).