Tasty Foods Corporation Worksheet

CASE13CM| | Student Version| | 9/21/96| | | | | | | | 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. | | | | | Click on the tab labelled ‘HEAVENLY’ 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, F117, and D129. | | | | | | =| =| =| =| =| =| | INPUT DATA:| | | | KEY OUTPUT:| | | Initial Investment:| | | | | | | Equipment cost| | #####| | NPV| ######| | Freight| | #####| | IRR| 0. 0%| | Installation| | #####| | MIRR| 7. 2%| | Change in NWC| | #####| | ARR| 17. 4%| | Operating Flows and Inflation Rates:| | | | Payback| 2. 74 | | Unit sales| | #####| | Strategic Opt. Value | | ######| t=0 sales price| | $2. 0 | | | | | Fixed oper costs| | #####| | | | | t=0 VC per unit| | $1. 25 | | | | | Price inflation| | 4. 0%| | | | | Cost inflation| | 2. 0%| | | | | SV, Taxes, and C of C:| | | | | | | Salvage value| | #####| | | | | Useful life (yrs)| | 4 | | | | | Tax rate| | 40%| | | | | Cost of Capital | | 12%| | | | | Cannibalization of Other Projects:| | | | | | | Revenue Loss| | | ######| | | | Cost Reduction| | | ######| | | | Net Cannibalization| | | ######| | | | | | | | | | | Black-Scholes Option Valuation:| | | | Strategic Opt. Value | | ######| Lite Pizza Project:| | | | | | | Yr. 1 Outlay| | #####| | | | | Yr. CF| | #####| | | | | Yr. 3 CF| | #####| | | | | Yr. 4 CF| | #####| | | | | Av. Cost of Cap. | | 12%| | | | | Risk-Adj. COC| | 15%| | | | | Black-Scholes Model Inputs| | | | | | | Current price| | | P =| ######| | | Exercise price| | | X =| ######| | | 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:| | | Depreciation Schedule: Basis:| | | ######| Equipment cost| | #####| | MACRS| Dep. End of Year| Freight| | #####| Year| Factor| Expense| Book Value| Installation| | | ——| ——| ——–| ——–| Change in NWC| | #####| 1| | $0 | ######| | | ——–| 2| 45%| ######| ######| | | #####| 3| 15%| | ######| | | ========| 4| 7%| | | | | | | ——| ——–| | | | | | 67%| ######| | | | | | ======| ========| | | | | | | | | Cash Flows:| | Year| Year| Year| Year| Year| | | 0| 1| 2| 3| 4| | | ——–| ——–| ——–| ——–| ——–| Unit price| | $2. 00 | $2. 08 | $2. 16 | $2. 25 | $2. 34 | Unit sales| | | ######| ######| ######| ######| | | | ——–| ——–| ——–| ——–|

Revenues| | | | ######| ######| ######| Fixed operating costs| | | 190,000 | 190,000 | 190,000 | 190,000 | Variable operating costs| | | ######| 910,350 | ######| 947,128 | | | | ———| ———| ———| ———| Total operating costs| | | ######| ######| ######| ######| | | | ———| ———| ———| ———| Depreciation| | | 0 | | 0 | 0 | Net cannibalization effects| | | 45,000 | 45,000 | 45,000 | 45,000 | | | | ——–| ——–| ——–| ——–| Before tax income| | | | ######| ######| ######| Taxes| | | 0 | 147,556 | 164,501 | 182,270 | | | | ——–| ——–| ——–| ——–|

Net income| | | $0 | ######| ######| | Plus depreciation| | | 0 | ######| 0 | | | | | ——–| ——–| ——–| ——–| Net operating cash flow| | | | ######| ######| $0 | | | | ——–| ——–| ——–| ——–| Salvage value| | | | | | ######| SV tax| | | | | | | Recovery of NWC| | | | | | 30,000 | | | | | | | ——–| Termination CF| | | | | | | | | ——–| ——–| ——–| ——–| ——–| Project NCF| | #####| $0 | ######| ######| $0 | | | ========| ========| ========| ========| ========| | | | | | | | Decision Measures:| | | | Cumulative Cash Flows:| | | | NPV| #####| | 0 | ######| | IRR| | | 1 | ######| | | TV| #####| | 2 | ######| | | MIRR| 7. 2%| | 3 | 65,336 | | | ARR| 17. 4%| | 4 | | | | Payback| 2. 74 | years| | | | |::| | | | | | | Scenario Analysis:| | | | | | | | Scenario| Prob. | NPV| IRR| MIRR| ARR| | ——–| —–| ———| —–| —–| —–| | Worst| 25%| ######| -4. 1%| 2. 1%| -5. 0%| | Base| 50%| ######| 21. 6%| 17. 6%| 28. 5%| | Best| 25%| ######| 41. 7%| 28. 2%| 57. 4%| | | | | | | | | Expected value| | ######| 20. 18%| 16. 34%| 27. 32%| | Standard deviation| | ######| 16. 23%| 9. 30%| 22. 10%| | Coeff. variation| | | 0. 8 | 0. 6 | 0. 8 | | | | | | | |

Data Tables for Sensitivity Analysis| | | | | | | | | | | | | | 1. Unit Sales: | | | | | | | | | | | | | | | Units:| NPV:| IRR:| MIRR:| ARR:| Payback:| Deviation| | -1E+05 | 0 | 0. 0715 | 0. 1742 | 2. 7352 | -0. 3 | ####| #####| 2. 7%| 6. 2%| 3. 2%| 3. 77 | -0. 2 | ####| #####| 9. 3%| 10. 4%| 11. 6%| 3. 30 | -0. 1 | ####| #####| 15. 6%| 14. 1%| 20. 0%| 2. 92 | 0 | ####| #####| 21. 6%| 17. 6%| 28. 5%| 2. 56 | 0. 1 | ####| #####| 27. 3%| 20. 7%| 36. 9%| 2. 29 | 0. 2 | ####| #####| 32. 8%| 23. 6%| 45. 3%| 2. 07 | 0. 3 | ####| #####| 38. 2%| 26. 3%| 53. 7%| 1. 91 | | | | | | | | 2. Initial Sales Price| | | | | | | | | | | | | | Price:| NPV:| IRR:| MIRR:| ARR:| Payback:| Deviation| | -1E+05 | 0 | 0. 0715 | 0. 1742 | 2. 7352 | -0. 3 | 1. 40 | #####| -36. 0%| -24. 4%| -33. 9%| 4. 00 | -0. 2 | 1. 60 | #####| -11. 7%| -3. 9%| -13. 1%| 4. 00 | -0. 1 | 1. 80 | #####| 6. 2%| 8. 4%| 7. 7%| 3. 51 | 0 | 2. 00 | #####| 21. 6%| 17. 6%| 28. 5%| 2. 56 | 0. 1 | 2. 20 | #####| 35. 5%| 24. 9%| 49. 2%| 1. 97 | 0. 2 | 2. 40 | #####| 48. 6%| 31. 2%| 70. 0%| 1. 65 | 0. 3 | 2. 60 | #####| 61. 1%| 36. 7%| 90. 8%| 1. 42 | | | | | | | | 3. Salvage Value| | | | | | | | Salvage| | | | | | | Value:| NPV:| IRR:| MIRR:| ARR:| Payback:| Deviation| | -1E+05 | 0 | 0. 0715 | 0. 1742 | 2. 352 | -0. 3 | ####| #####| 21. 1%| 17. 3%| 28. 4%| 2. 56 | -0. 2 | ####| #####| 21. 3%| 17. 4%| 28. 4%| 2. 56 | -0. 1 | ####| #####| 21. 4%| 17. 5%| 28. 4%| 2. 56 | 0 | ####| #####| 21. 6%| 17. 6%| 28. 5%| 2. 56 | 0. 1 | ####| #####| 21. 7%| 17. 6%| 28. 5%| 2. 56 | 0. 2 | ####| #####| 21. 9%| 17. 7%| 28. 5%| 2. 56 | 0. 3 | ####| #####| 22. 0%| 17. 8%| 28. 5%| 2. 56 | | | | | | | | 4. Variable Cost:| | | | | | | | VC per| | | | | | | Unit:| NPV:| IRR:| MIRR:| ARR:| Payback:| Deviation| | -1E+05 | 0 | 0. 0715 | 0. 1742 | 2. 7352 | -0. 3 | 0. 88 | #####| 46. 1%| 30. 0%| 65. 6%| 1. 70 | -0. 2 | 1. 00 | #####| 38. 2%| 26. 2%| 53. 2%| 1. 9 | -0. 1 | 1. 13 | #####| 30. 0%| 22. 1%| 40. 8%| 2. 16 | 0 | 1. 25 | #####| 21. 6%| 17. 6%| 28. 5%| 2. 56 | 0. 1 | 1. 38 | 11,210 | 12. 6%| 12. 4%| 16. 1%| 3. 11 | 0. 2 | 1. 50 | #####| 3. 1%| 6. 4%| 3. 7%| 3. 75 | 0. 3 | 1. 63 | #####| -7. 4%| -0. 9%| -8. 6%| 4. 00 | | | | | | | | 5. Cost of Capital:| | | | | | | | | | | | | | | WACC:| NPV:| IRR:| MIRR:| ARR:| Payback:| Deviation| | -1E+05 | 0 | 0. 0715 | 0. 1742 | 2. 7352 | -0. 3 | 8. 4%| #####| 21. 6%| 16. 1%| 28. 5%| 2. 56 | -0. 2 | 9. 6%| #####| 21. 6%| 16. 6%| 28. 5%| 2. 56 | -0. 1 | 10. 8%| #####| 21. 6%| 17. 1%| 28. 5%| 2. 56 | 0 | 12. 0%| #####| 21. 6%| 17. 6%| 28. 5%| 2. 6 | 0. 1 | 13. 2%| #####| 21. 6%| 18. 1%| 28. 5%| 2. 56 | 0. 2 | 14. 4%| #####| 21. 6%| 18. 6%| 28. 5%| 2. 56 | 0. 3 | 15. 6%| #####| 21. 6%| 19. 1%| 28. 5%| 2. 56 | | | | | | | | Inflation (Data Table 2 with NPV as Output Variable)| | | | | | | | | | | | | Price| | | Cost Inflation:| | | | Inflation:| ——–| -| -| -| -| | ####| 0%| 1%| 2%| 3%| 4%| | 0%| ####| #####| ######| ######| ######| | 1%| ####| #####| (10,886)| ######| (90,461)| | 2%| ####| #####| 51,017 | 11,599 | ######| | 3%| ####| #####| 114,085 | 74,667 | 34,510 | | 4%| ####| #####| 178,337 | 138,919 | 98,762 | | 5%| ####| #####| ######| ######| 164,213 | | %| ####| #####| 310,457 | 271,039 | ######| | 7%| ####| #####| ######| 338,941 | ######| | 8%| ####| #####| 447,513 | ######| ######| | |::| | | | | | | Black-Scholes Model-Generated Data:| | | | | | | | | | | | | | ln(P/X) =| | #####| d1 =| 1. 01191 | | | (s^2)/2 =| | #####| d2 =| ######| | | s*(t^0. 5) =| | #####| N(d1) =| 0. 84410 | | | e^(-rt) =| | #####| N(d2) =| ######| | | | | | | | | | Table of Normal Probabilities| | | | | | | | | | | | | | | Cumul| d1 =| d2 =| | | | z| Prob| 1. 012 | 0. 839 | | | | ——-| ——-| ——-| ——-| | | | 0. 00 | 0 | 0 | 0 | | | | 0. 05 | 0. 2 | 0 | 0 | | | | 0. 10 | 0. 04 | 0 | 0 | | | | 0. 15 | 0. 06 | 0 | 0 | | | | 0. 20 | 0. 08 | 0 | 0 | | | | 0. 25 | 0. 1 | 0 | 0 | | | | 0. 30 | 0. 12 | 0 | 0 | | | | 0. 35 | 0. 14 | 0 | 0 | | | | 0. 40 | 0. 16 | 0 | 0 | | | | 0. 45 | 0. 17 | 0 | 0 | | | | 0. 50 | 0. 19 | 0 | 0 | | | | 0. 55 | 0. 21 | 0 | 0 | | | | 0. 60 | 0. 23 | 0 | 0 | | | | 0. 65 | 0. 24 | 0 | 0 | | | | 0. 70 | 0. 26 | 0 | 0 | | | | 0. 75 | 0. 27 | 0 | 0 | | | | 0. 80 | 0. 29 | 0 | 0 | | | | 0. 85 | 0. 3 | 0 | 0. 2991 | | | | 0. 90 | 0. 32 | 0 | 0 | | | | 0. 95 | 0. 33 | 0 | 0 | | | | 1. 00 | 0. 34 | 0 | 0 | | | | 1. 05 | 0. 35 | 0. 344 | 0 | | | | 1. 0 | 0. 36 | 0 | 0 | | | | 1. 15 | 0. 37 | 0 | 0 | | | | 1. 20 | 0. 38 | 0 | 0 | | | | 1. 25 | 0. 39 | 0 | 0 | | | | 1. 30 | 0. 4 | 0 | 0 | | | | 1. 35 | 0. 41 | 0 | 0 | | | | 1. 40 | 0. 42 | 0 | 0 | | | | 1. 45 | 0. 43 | 0 | 0 | | | | 1. 50 | 0. 43 | 0 | 0 | | | | 1. 55 | 0. 44 | 0 | 0 | | | | 1. 60 | 0. 45 | 0 | 0 | | | | 1. 65 | 0. 45 | 0 | 0 | | | | 1. 70 | 0. 46 | 0 | 0 | | | | 1. 75 | 0. 46 | 0 | 0 | | | | 1. 80 | 0. 46 | 0 | 0 | | | | 1. 85 | 0. 47 | 0 | 0 | | | | 1. 90 | 0. 47 | 0 | 0 | | | | 1. 95 | 0. 47 | 0 | 0 | | | | 2. 00 | 0. 48 | 0 | 0 | | | | 2. 05 | 0. 48 | 0 | 0 | | | | 2. 10 | 0. 48 | 0 | 0 | | | | 2. 15 | 0. 8 | 0 | 0 | | | | 2. 20 | 0. 49 | 0 | 0 | | | | 2. 25 | 0. 49 | 0 | 0 | | | | 2. 30 | 0. 49 | 0 | 0 | | | | 2. 35 | 0. 49 | 0 | 0 | | | | 2. 40 | 0. 49 | 0 | 0 | | | | 2. 45 | 0. 49 | 0 | 0 | | | | 2. 50 | 0. 49 | 0 | 0 | | | | 2. 55 | 0. 49 | 0 | 0 | | | | 2. 60 | 0. 5 | 0 | 0 | | | | 2. 65 | 0. 5 | 0 | 0 | | | | 2. 70 | 0. 5 | 0 | 0 | | | | 2. 75 | 0. 5 | 0 | 0 | | | | 2. 80 | 0. 5 | 0 | 0 | | | | 2. 85 | 0. 5 | 0 | 0 | | | | 2. 90 | 0. 5 | 0 | 0 | | | | 2. 95 | 0. 5 | 0 | 0 | | | | 3. 00 | 0. 5 | 0 | 0 | | | | 3. 05 | 0. 5 | 0 | 0 | | | | 3. 10 | 0. 5 | 0 | 0 | | | | 3. 15 | 0. 5 | 0 | 0 | | | | 3. 20 | 0. 5 | 0 | 0 | | | | . 25 | 0. 5 | 0 | 0 | | | | 3. 30 | 0. 5 | 0 | 0 | | | | 3. 35 | 0. 5 | 0 | 0 | | | | 3. 40 | 0. 5 | 0 | 0 | | | | 3. 45 | 0. 5 | 0 | 0 | | | | 3. 50 | 0. 5 | 0 | 0 | | | | | | | | | | | | | 0. 344 | 0. 2991 | | | | | | 0. 344 | 0. 2991 | | | | | | | | | | | | | | | | | | | | | | | | | =| =| =| =| =| =| =| | | | | | | END| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |