Can someone help me with a few things on the spreadsheet. I need help with the data tables, the Scenario Analysis and the ARR.

Case 59H Student Version 9/29/96 TASTY FOODS CORPORATION (B) Capital Budgeting and Risk Analysis This case focuses on capital budgeting risk assessment using the lite athletic drink project that was introduced in Case 12B, Tasty Foods Corporation (Part I). The model develops incremental cash flow estimates, then calculates NPV, IRR, MIRR, ARR, and Payback for the lite athletic drink project. This model differs from the Case 12B model only in that this one includes additional data tables which can greatly facilitate sensitivity analysis. Also, this model contains a graph which can be used to plot the sensitivity diagrams. You can change the data tables, then use them to change the graph. Click on the tab labelled 'TASTY' to view the graph. If you are using the student version of the model, the cells containing formulas have been blanked out. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. ========= ================= ============== ============= INPUT DATA: Initial Investment: Equipment cost $700,000 Freight $35,000 Installation $70,000 Change in NWC $30,000 Operating Flows and Inflation Rates: Unit sales 700,000 t=0 sales price $2.00 Fixed oper costs $190,000 t=0 VC per unit $1.25 Price inflation 4.0% Cost inflation 2.0% SV, Taxes, and C of C: Salvage value $87,500 Useful life (yrs) 4 Tax rate 40% Cost of capital 12% ========= ================= ============== ============= ============= ============= ============= KEY OUTPUT: NPV IRR MIRR ARR Payback years Cannibalization of Other Projects: Revenue Loss Cost Reduction Net Cannibalization $80,000 $35,000 $45,000 ============= ============= ============= |:: MODELGENERATED DATA: TASTY FOODS CORPORATION (PART II) Net Investment Outlay: Equipment cost Freight Installation Change in NWC Cash Flows: Depreciation Schedule: Basis: MACRS Dep. Year Factor Expense 1 33% $265,650 2 45% $362,250 835000 3 15% $120,750 4 7% $56,350 700000 35,000 70,000 30,000 Year 1 $2.08 700,000 Year 2 $2.16 700,000 Year 3 $2.25 700,000 Year 4 $2.34 700,000 Revenues Fixed operating costs Variable operating costs $1,456,000 190,000 892,500 $1,514,240 190,000 910,350 $1,574,810 190,000 928,557 $1,637,802 190,000 947,128 Total operating costs $1,082,500 $1,100,350 $1,118,557 $1,137,128 Depreciation Net cannibalization effects 265,650 45,000 362,250 45,000 120,750 45,000 56,350 45,000 Before tax income Taxes $62,850 25,140 $6,640 2,656 $290,503 116,201 $399,324 159,730 Net income Plus depreciation $37,710 265,650 $3,984 362,250 $174,302 120,750 $239,594 56,350 $303,360 $366,234 $295,052 $295,944 Unit price Unit sales Net operating cash flow Salvage value SV tax Recovery of NWC Termination CF Year 0 $2.00 805000 End of Year Book Value $539,350 $177,100 $56,350 $0 $87,500 35,000 30,000 82500 $ (835,000) $ 303,360 $ 366,234 $ 295,052 $ 378,444 Project NCF Decision Measures: NPV IRR TV MIRR ARR Payback $178,337 21.6% 1,594,505 17.6% #NAME? 2.56 years Cumulative Cash Flows: 0 $ (835,000) 1 (531,640) 2 (165,406) 3 129,646 4 508,090 |:: Scenario Analysis: Scenario Worst Base Best Prob. NPV IRR MIRR ARR Expected value Standard deviation Coeff. variation Data Tables for Sensitivity Analysis 1. Unit Sales: Units: Deviation 0.3 0.2 0.1 0 0.1 0.2 0.3 NPV: ### IRR: ### MIRR: ### ARR: #NAME? Payback: ### NPV: ### IRR: ### MIRR: ### ARR: #NAME? Payback: ### NPV: ### IRR: ### MIRR: ### ARR: #NAME? Payback: ### NPV: ### IRR: ### MIRR: ### ARR: #NAME? Payback: ### NPV: ### IRR: ### MIRR: ### ARR: #NAME? Payback: ### 490,000 560,000 630,000 700,000 770,000 840,000 910,000 2. Initial Sales Price Price: Deviation 0.3 0.2 0.1 0 0.1 0.2 0.3 1.40 1.60 1.80 2.00 2.20 2.40 2.60 3. Salvage Value Salvage Value: Deviation 0.3 0.2 0.1 0 0.1 0.2 0.3 61,250 70,000 78,750 87,500 96,250 105,000 113,750 4. Variable Cost: VC per Unit: Deviation 0.3 0.2 0.1 0 0.1 0.2 0.3 0.88 1.00 1.13 1.25 1.38 1.50 1.63 5. Cost of Capital: WACC: Deviation 0.3 0.2 0.1 0 0.1 0.2 0.3 8.4% 9.6% 10.8% 12.0% 13.2% 14.4% 15.6% Inflation (Data Table 2 with NPV as Output Variable) Price Inflation: 0% 1% 2% 3% 4% 5% 6% 7% 8% Cost Inflation: 0% 1% 2% 3% 4% $0 $0 $0 $0 $0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 |:: ========= ================= ============== ============= ============= ============= ============= END SENSITIVITY ANALYSIS $12 $10 $8 NPV $6 $4 $2 $0 -0.3 -0.2 -0.1 0 0.1 0.2 % DEVIATION FROM EXPECTED VALUE Units Price Salvage VC/Unit WACC 0.3 Module 12 Student Version 11/19/15 HEAVENLY FOODS CORPORATION Capital Budgeting Methods, Cash Flow Estimation, and Risk Analysis This case combines capital budgeting decision methods, cash flow estimation, and risk analysis which are presented in Cases 12I and 13I. In addition, the case focuses on quantifying the strategic option value of developing the new line of lite frozen pizzas. The model develops incremental cash flow estimates, then calculates NPV, IRR, MIRR, ARR, and payback for the lite athletic drink project. Also, this model contains a graph which can be used to plot the sensitivity diagrams. You can change the data tables, then use them to change the graph. Press CTRL-A to view the graph. In addition, the option value of the lite pizza project is determined. If you are using the student version of the model, some of the cells have been blanked out. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. The following cells have been blanked out: C71, E72, F74.F75, G75, D86, E92, D95, G98.G99, D101, G104, G107, C114, C117, F117, D129 ======= ======= ======== ========== ========== ========== INPUT DATA: KEY OUTPUT: Initial Investment: Equipment cost $700,000 NPV $0 Freight $35,000 IRR 0.0% Installation $70,000 MIRR 0.0% Change in NWC $30,000 ARR 0.0% Operating Flows and Inflation Rates: Payback 0.00 Unit sales 700,000 Strategic Opt. Value t=0 sales price $2.00 Fixed oper costs $190,000 t=0 VC per unit $1.25 Price inflation 4.0% Cost inflation 2.0% SV, Taxes, and C of C: Salvage value $87,500 Useful life (yrs) 4 Tax rate 40% Cost of Capital 12% Cannibalization of Other Projects: Revenue Loss $80,000 Cost Reduction $35,000 Net Cannibalization $45,000 $79,302.72 Black-Scholes Option Valuation: Strategic Opt. Value $79,302.72 Lite Pizza Project: Yr. 1 Outlay $500,000 Yr. 2 CF $193,591 Yr. 3 CF $234,751 Yr. 4 CF $278,593 Av. Cost of Cap. 12% Risk-Adj. COC 15% Black-Scholes Model Inputs Current price P= $485,468 Exercise price X= $434,783 Risk-free rate of interest r= 0.05 Time to expiration t= 1.00 Variance of stock returns s^2 = 0.03 ======= ======= ======== ========== ========== ========== ========== |:: MODEL-GENERATED DATA: HEAVENLY FOODS CORPORATION Net Investment Outlay: Equipment cost Freight Installation Change in NWC -------$0 ======== Cash Flows: Year 0 -------$2.00 Depreciation Schedule: Basis: MACRS Year Factor ----------1 2 45% 3 15% 4 7% -----67% ====== Dep. Expense -------- $805,000 End of Year Book Value -------- -------$0 ======== Year 2 -------- Year 3 -------- Year 4 -------- -------- -------- -------- -------- --------$0 --------0 --------$0 --------- --------$0 --------0 --------$0 --------0 -------- Unit price Unit sales Year 1 -------- -------$0 0 -------$0 0 -------$0 -------- -------$0 0 -------$0 0 -------$0 -------- -------$0 0 -------- Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes 0 -------$0 0 -------- Net income Plus depreciation Net operating cash flow -------- -------$0 -------- Salvage value SV tax Recovery of NWC -------Termination CF Project NCF Decision Measures: NPV IRR TV MIRR ARR Payback -------$0 ======== -------$0 ======== -------$0 ======== -------$0 ======== Cumulative Cash Flows: 0 1 2 3 4 years -------$0 ======== |:: Scenario Analysis: Scenario -------Worst Base Best Prob. NPV ------------25% 50% 25% Expected value Standard deviation Coeff. variation IRR ----- MIRR ----- ARR ----- $0 $0 0.00% 0.00% #DIV/0! 0.00% 0.00% #DIV/0! 0.00% 0.00% #DIV/0! NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 Data Tables for Sensitivity Analysis 1. Unit Sales: Units: Deviation -0.3 -0.2 -0.1 0 0.1 0.2 0.3 490,000 560,000 630,000 700,000 770,000 840,000 910,000 2. Initial Sales Price Price: Deviation -0.3 -0.2 -0.1 0 0.1 0.2 0.3 1.40 1.60 1.80 2.00 2.20 2.40 2.60 3. Salvage Value Salvage Value: Deviation -0.3 61,250 -0.2 70,000 -0.1 78,750 0 87,500 0.1 96,250 0.2 105,000 0.3 113,750 4. Variable Cost: VC per Unit: Deviation -0.3 0.88 -0.2 1.00 -0.1 1.13 0 1.25 0.1 1.38 0.2 1.50 0.3 1.63 5. Cost of Capital: WACC: Deviation -0.3 -0.2 -0.1 0 0.1 0.2 0.3 8.4% 9.6% 10.8% 12.0% 13.2% 14.4% 15.6% Inflation (Data Table 2 with NPV as Output Variable) Price Cost Inflation: Inflation: 0 0% 1% 2% 3% 4% 5% 6% 7% 8% -------- -------------- ----------------- ----------------- ----------------0% 1% 2% 3% 4% |:: Black-Scholes Model-Generated Data: ln(P/X) = (s^2)/2 = s*(t^0.5) = e^(-rt) = 0.11027 0.01500 0.17321 0.95123 d1 = d2 = N(d1) = N(d2) = 1.01191 0.83870 0.84410 0.79908 Table of Normal Probabilities z ------0.00 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.85 0.90 0.95 1.00 1.05 1.10 1.15 1.20 1.25 1.30 1.35 1.40 1.45 1.50 1.55 1.60 1.65 1.70 1.75 1.80 1.85 1.90 1.95 2.00 2.05 2.10 2.15 2.20 2.25 2.30 2.35 2.40 2.45 2.50 2.55 2.60 2.65 2.70 2.75 2.80 2.85 2.90 2.95 3.00 3.05 3.10 3.15 Cumul Prob ------0 ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### d1 = 1.012 ------0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 d2 = 0.839 ------0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3.20 3.25 3.30 3.35 3.40 3.45 3.50 ### ### ### ### ### ### ### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### ### ### ### ======= ======= ======== ========== ========== ========== ========== END SENSITIVITY AN ALYSIS 12 10 NPV 8 6 4 2 0 -0.3 -0.2 -0.1 0 Un its Price 0.1 Salvage VC/Un it % DEVIATION FROM EXPECTED VALUE 0.2 wacc 0.3 13B Capital Budgeting and Risk Analysis Tasty Foods Corporation (Part B) Directed In Case 12B, you analyzed a lite athletic drink project for Abigail Abercrombie, the daughter of Tasty Foods Corporation's founder. The project is expected to require an initial investment of $805,000 in fixed assets (including shipping and installation charges), plus a $30,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 $87,500. If the project is undertaken, the firm expects to sell 700,000 cartons annually of High Energy-Lite for the life of the project at a current dollar (Year 0) wholesale price of $2 per carton. However, the sales price will be adjusted for inflation, which is expected to average 4 percent annually, so the actual expected sales price at the end of the first year is $2.08, the expected price at the end of the second year is $2.1632 and so on. Because the two product lines are somewhat competitive, the lite athletic drink project is expected to cannibalize the before-tax profit Tasty Foods earns on its regular athletic drink sales by $45,000. Fixed costs (other than depreciation) associated with production of the lite product are expected to amount to $190,000 per year, and Year 0 variable costs per unit are estimated at $1.25. Copyright 1997 by The Dryden Press. All rights reserved. 13B-1 Capital Budgeting Variable costs per unit are expected to increase by 2 percent per year. Therefore, total operating cash costs during the first year of operation (Year 1) are expected to be $190,000 + ($1.25)(1.02) (700,000) = $1,082,500. Tasty Foods' tax rate is 40 percent, and its cost of capital for an average project is 12 percent. Partial cash flow data and other financial information, as developed by you using a spreadsheet model, are given in Table 1. When Abigail presented your initial analysis to Tasty Foods' executive committee, things went well, and she was congratulated on both the analysis and the presentation. She, in turn, congratulated you on a job well done. However, several questions were raised during the meeting, and Abigail has asked for your assistance once more. In particular, the executive committee wanted to see more risk analysis on the lite 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, Abigail was asked to start with the base case situation you had developed and then to discuss risk analysis, both in general terms and as it should be applied to the lite athletic drink project. You met with the marketing and production managers to get a feel for the uncertainties involved in the cash flow estimates. After several sessions, you concluded that the greatest uncertainty involved unit sales and salvage value. Cost estimates were fairly well defined, but unit sales could vary widely, and the realized salvage value could be quite different from the $87,500 estimate. Companies in competitive markets typically set sales prices on the basis of competitors' prices, so, at least initially, you decided to treat the sales price as being fairly certain. However, the supply of ingredients for the lite drink can rise or fall sharply due to market production and demand conditions, and that can lead to large price swings in both variable costs and product sales prices. As estimated by the marketing staff, if product acceptance is \"normal,\" then sales quantity during the life of the project would be 700,000 units annually; if acceptance is poor, then only 450,000 units would be sold annually during the life of the project (the price would be kept at the forecasted level); and if consumer response is strong, then the sales volume would be 950,000 units annually during the life of the project. In all cases, the price would probably increase at the inflation rate (currently estimated to be 4 percent), so 13B-2 Case 13BCapital Budgeting and Risk Analysis Year 1 revenues stated in Year 1 dollars, as they would appear on the cash flow statement, would be $1,456,000 under the expected conditions; they would be only $936,000 if things went badly; and they would amount to $1,976,000 if things went especially well. Cash variable costs per unit would remain at $1.25 before adjusting for inflation, so total cash operating costs in Year 1 would be $1,082,500 under normal conditions, $763,750 in the worst-case scenario, and $1,401,250 in the best-case scenario. Variable costs would probably continue to increase in each successive year at a 2 percent rate. In addition, the production manager believes that the equipment's Year 4 salvage value could be as low as zero and as high as $125,000, depending on the demand for such equipment after 4 years. Abigail also discussed the scenarios' probabilities with the marketing staff. After considerable debate, they finally agreed on a \"guesstimate\" of 25 percent probability of poor acceptance, 50 percent probability of average acceptance, and 25 percent probability of excellent acceptance. In addition, Tasty Foods' executive committee requires that all sensitivity analyses consider changes in at least the following variables: sales quantity, sales price, variable costs, 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 percent, 20 percent, and 30 percent in such an analysis. Abigail also discussed with Claude Vandermere, Tasty Foods' director of capital budgeting, both the risk inherent in the firm's average project and how it typically adjusts for risk. Based on historical data, most of Tasty Foods' projects have had coefficients of variation of NPV in the range of 0.50 to 1.00, and Vandermere has been adding or subtracting 3 percentage points to the cost of capital for projects whose CVs lie outside that range to adjust for differential project risk. Abigail and you wonder about whether the cash flows from the lite athletic drink project would be positively or negatively correlated with the sales of Tasty Foods' other drinks and the S&P 500, and you also wondered how those correlations should be dealt with in the analysis. The discussion with Vandermere raised another issue: Should the project's cost of capital be based on its stand-alone risk, on its risk as measured within the context of the firm's portfolio of assets (withinfirm, or corporate, risk), or in a market risk context? Tasty Foods' target capital structure calls for 40 percent debt and 60 percent 13B-3 Capital Budgeting common equity, and the before-tax marginal cost of debt is currently 13 percent. You also determined that the T-bond rate, which you use as the long-term risk-free rate, is 8.2 percent, and that the market risk premium is 7 percent. In addition, you estimated that the market beta for the project would be about 1.75. Since most members of Tasty Foods' executive committee are unfamiliar with modern techniques of risk analysis, Abigail decided to first discuss the types of risk that are normally considered in capital budgeting, and then to consider the strengths and weaknesses of risk analysis. Next, she plans to discuss a comprehensive risk analysis including sensitivity analysis (refer to Table 2), scenario analysis (refer to Table 3), and an estimate of the project's differential riskadjusted profitability. Finally, she plans to carry out or at least discuss Monte-Carlo simulation, and then to provide a comparison of the various risk-analysis techniques. To help structure your analysis and report, answer the following questions: Questions 1. a. Why should firms be concerned with the riskiness of individual projects? b.(1) What are the three types of risk that are normally considered in capital budgeting? (2) Which type of risk is most relevant? (3) Which type of risk is easiest to measure? (4) Would you normally expect the three types of risk to be highly correlated? Would they be highly correlated in this specific instance? 2. a. What is sensitivity analysis? b. Complete the sensitivity tables in Table 2, assuming initially that the project has average risk. Also, develop new tables which show sensitivity of NPV and the other variables to the initial variable cost and the cost of capital. Assume that each of these variables can deviate from its base case, or expected 13B-4 Case 13BCapital Budgeting and Risk Analysis value, by plus or minus 10 percent, 20 percent, and 30 percent. c. Prepare a sensitivity diagram and discuss the results. d. What are the primary weaknesses of sensitivity analysis? What are its primary advantages? 3. Complete the scenario analysis in Table 3. What is the base case (most likely) NPV? The best-case IRR? Use the worst-case, most likely, and best-case NPVs, and their probabilities of occurrence, to find the project's expected NPV, standard deviation, and coefficient of variation. 4. What are the primary advantages and disadvantages of scenario analysis? 5. What is Monte Carlo simulation, and what are simulation's advantages and disadvantages vis-a-vis scenario analysis? 6. a. Would the lite athletic drink project be classified as high risk, average risk, or low risk by your analysis thus far? (Hint: Consider the project's coefficient of variation of NPV.) What type of risk have you been measuring? b. What do you think the project's corporate, or within-firm, risk would be, and how could you measure it? c. How would it affect your risk assessment if you were told that the cash flows from this project were totally uncorrelated with Tasty Foods' other cash flows? What if they were expected to be negatively correlated? d. How would the project's cash flows probably be correlated with the cash flows of most other firms, say the S&P 500, hence with the stock market? What difference would that make in your capital budgeting analysis? 7. Calculate the project's risk-adjusted NPV. Should the project be accepted? What if it had a coefficient of variation (CV) of NPV of only 0.15 and was judged to be a low-risk project? 8. Abigail and you thought long and hard about the lite athletic drink project's market beta. You finally agreed to use 1.75 as your best estimate of the beta for the equity invested in the project. 13B-5 Capital Budgeting a. On the basis of market risk, what is the project's required rate of return? b. Describe briefly two methods that might possibly be used to estimate the project's beta. Do you think those methods would be feasible in this situation? c. What are the advantages and disadvantages of focusing on a project's market risk rather than on the other types of risk? 9. What is your recommendation? Should Tasty Foods accept or reject the lite athletic drink project? 13B-6 Case 13BCapital Budgeting and Risk Analysis Table 1 Model-Generated Data: Tasty Foods Corporation (Part B) Net Investment Outlay: Equipment cost Freight Installation Change in NWC Cash Flows: Unit price Unit sales Revenues $700,00 0 $835,00 0 Year 0 $2.00 Fixed op costs Variable op costs Total op costs Depreciation Net cannibalization effects Before tax income Taxes Net income Plus depreciation Net op cash flow Salvage value SV tax Recovery of NWC Termination CF Project NCF Depreciation Schedule: Basis: $805,000 MACRS Dep. Year 1 2 3 Factor 33% 45 15 Expense $265,650 4 7 100% 56,350 $805,000 Year 1 $2.08 700,000 $1,456,00 0 190,000 892,500 $1,082,50 0 265,650 Year 2 Year 3 700,000 700,000 45,000 45,000 $ 62,850 Year 4 $2.34 700,000 $1,637,802 190,000 947,128 $1,137,128 45,000 $ 399,324 25,140 $ 37,710 265,650 $ 303,360 Decision Measures: NPV $178,337 IRR TV 1,594,505 MIRR 17.6% ARR 28.5% Payback 2.56 years (0) 56,350 45,000 ($835,00 0) End of Year Book Value $539,350 $ 303,360 Cumulative Cash Flows: 0 ($835,000) 1 (531,640) 2 (165,406) 3 4 508,090 13B-7 159,730 $ 239,594 56,350 $ 295,944 $ 87,500 35,000 30,000 $ 82,500 $ 378,444 Capital Budgeting Table 2 Sensitivity Analysis Results Summary of Sensitivity Analysis NPV after Indicated Change Variable Change from Base Level -30% -20% -10% Base Case +10% +20% +30% Unit Sales ($160,688) Price Salvage Value 65,329 178,337 291,345 ($662,068) (381,933) (101,798) 178,337 458,472 1,018,741 k 175,000 178,337 181,673 517,361 VC 188,346 Inflation (Data Table 2 with NPV as Output Variable) Price Inflation +$C$113 0% 1 2 3 4 5 6 7 8 0% $ 5,021 65,773 127,676 190,744 320,447 387,116 455,018 524,172 1% ($32,949) 27,803 89,705 152,774 217,026 282,477 349,146 486,202 Cost Inflation 2% 3% ($71,638) ($111,056) (10,886) (50,304) 11,599 114,085 74,667 178,337 138,919 243,788 204,370 310,457 378,359 338,941 447,513 408,095 4% ($151,213) (90,461) (28,559) 34,510 164,213 230,882 298,784 367,938 Table 3 Scenario Analysis Results Scenario Worst Base Best Prob. 25% 50% 25% NPV ($258,628) 13B-8 MIRR 2.1% 17.6% 20.18% 16.23% 0.8 596,236 Expected value Standard deviation Coeff. variation IRR -4.1% 21.6% 16.34% 9.30% 0.6 ARR -5.0% 28.5% Module 12 Student Version 11/19/15 HEAVENLY FOODS CORPORATION Capital Budgeting Methods, Cash Flow Estimation, and Risk Analysis This case combines capital budgeting decision methods, cash flow estimation, and risk analysis which are presented in Cases 12I and 13I. In addition, the case focuses on quantifying the strategic option value of developing the new line of lite frozen pizzas. The model develops incremental cash flow estimates, then calculates NPV, IRR, MIRR, ARR, and payback for the lite athletic drink project. Also, this model contains a graph which can be used to plot the sensitivity diagrams. You can change the data tables, then use them to change the graph. Press CTRL-A to view the graph. In addition, the option value of the lite pizza project is determined. If you are using the student version of the model, some of the cells have been blanked out. Before using the model, it is necessary to fill in the empty cells with the appropriate formulas. Once this is done, the model is ready for use. The following cells have been blanked out: C71, E72, F74.F75, G75, D86, E92, D95, G98.G99, D101, G104, G107, C114, C117, F117, D129 ======= ======= ======== ========== ========== ========== INPUT DATA: KEY OUTPUT: Initial Investment: Equipment cost $700,000 NPV $0 Freight $35,000 IRR 0.0% Installation $70,000 MIRR 0.0% Change in NWC $30,000 ARR 0.0% Operating Flows and Inflation Rates: Payback 0.00 Unit sales 700,000 Strategic Opt. Value t=0 sales price $2.00 Fixed oper costs $190,000 t=0 VC per unit $1.25 Price inflation 4.0% Cost inflation 2.0% SV, Taxes, and C of C: Salvage value $87,500 Useful life (yrs) 4 Tax rate 40% Cost of Capital 12% Cannibalization of Other Projects: Revenue Loss $80,000 Cost Reduction $35,000 Net Cannibalization $45,000 $79,302.72 Black-Scholes Option Valuation: Strategic Opt. Value $79,302.72 Lite Pizza Project: Yr. 1 Outlay $500,000 Yr. 2 CF $193,591 Yr. 3 CF $234,751 Yr. 4 CF $278,593 Av. Cost of Cap. 12% Risk-Adj. COC 15% Black-Scholes Model Inputs Current price P= $485,468 Exercise price X= $434,783 Risk-free rate of interest r= 0.05 Time to expiration t= 1.00 Variance of stock returns s^2 = 0.03 ======= ======= ======== ========== ========== ========== ========== |:: MODEL-GENERATED DATA: HEAVENLY FOODS CORPORATION Net Investment Outlay: Equipment cost Freight Installation Change in NWC -------$0 ======== Cash Flows: Year 0 -------$2.00 Depreciation Schedule: Basis: MACRS Year Factor ----------1 2 45% 3 15% 4 7% -----67% ====== Dep. Expense -------- $805,000 End of Year Book Value -------- -------$0 ======== Year 2 -------- Year 3 -------- Year 4 -------- -------- -------- -------- -------- --------$0 --------0 --------$0 --------- --------$0 --------0 --------$0 --------0 -------- Unit price Unit sales Year 1 -------- -------$0 0 -------$0 0 -------$0 -------- -------$0 0 -------$0 0 -------$0 -------- -------$0 0 -------- Revenues Fixed operating costs Variable operating costs Total operating costs Depreciation Net cannibalization effects Before tax income Taxes 0 -------$0 0 -------- Net income Plus depreciation Net operating cash flow -------- -------$0 -------- Salvage value SV tax Recovery of NWC -------Termination CF Project NCF Decision Measures: NPV IRR TV MIRR ARR Payback -------$0 ======== -------$0 ======== -------$0 ======== -------$0 ======== Cumulative Cash Flows: 0 1 2 3 4 years -------$0 ======== |:: Scenario Analysis: Scenario -------Worst Base Best Prob. NPV ------------25% 50% 25% Expected value Standard deviation Coeff. variation IRR ----- MIRR ----- ARR ----- $0 $0 0.00% 0.00% #DIV/0! 0.00% 0.00% #DIV/0! 0.00% 0.00% #DIV/0! NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 NPV: 0 IRR: 0 MIRR: 0 ARR: 0 Payback: 0 Data Tables for Sensitivity Analysis 1. Unit Sales: Units: Deviation -0.3 -0.2 -0.1 0 0.1 0.2 0.3 490,000 560,000 630,000 700,000 770,000 840,000 910,000 2. Initial Sales Price Price: Deviation -0.3 -0.2 -0.1 0 0.1 0.2 0.3 1.40 1.60 1.80 2.00 2.20 2.40 2.60 3. Salvage Value Salvage Value: Deviation -0.3 61,250 -0.2 70,000 -0.1 78,750 0 87,500 0.1 96,250 0.2 105,000 0.3 113,750 4. Variable Cost: VC per Unit: Deviation -0.3 0.88 -0.2 1.00 -0.1 1.13 0 1.25 0.1 1.38 0.2 1.50 0.3 1.63 5. Cost of Capital: WACC: Deviation -0.3 -0.2 -0.1 0 0.1 0.2 0.3 8.4% 9.6% 10.8% 12.0% 13.2% 14.4% 15.6% Inflation (Data Table 2 with NPV as Output Variable) Price Cost Inflation: Inflation: 0 0% 1% 2% 3% 4% 5% 6% 7% 8% -------- -------------- ----------------- ----------------- ----------------0% 1% 2% 3% 4% |:: Black-Scholes Model-Generated Data: ln(P/X) = (s^2)/2 = s*(t^0.5) = e^(-rt) = 0.11027 0.01500 0.17321 0.95123 d1 = d2 = N(d1) = N(d2) = 1.01191 0.83870 0.84410 0.79908 Table of Normal Probabilities z ------0.00 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.85 0.90 0.95 1.00 1.05 1.10 1.15 1.20 1.25 1.30 1.35 1.40 1.45 1.50 1.55 1.60 1.65 1.70 1.75 1.80 1.85 1.90 1.95 2.00 2.05 2.10 2.15 2.20 2.25 2.30 2.35 2.40 2.45 2.50 2.55 2.60 2.65 2.70 2.75 2.80 2.85 2.90 2.95 3.00 3.05 3.10 3.15 Cumul Prob ------0 ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### ### d1 = 1.012 ------0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 d2 = 0.839 ------0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3.20 3.25 3.30 3.35 3.40 3.45 3.50 ### ### ### ### ### ### ### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### ### ### ### ======= ======= ======== ========== ========== ========== ========== END SENSITIVITY AN ALYSIS 12 10 NPV 8 6 4 2 0 -0.3 -0.2 -0.1 0 Un its Price 0.1 Salvage VC/Un it % DEVIATION FROM EXPECTED VALUE 0.2 wacc 0.3