| 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: | KEY OUTPUT: | ||||||||||||
| Initial Investment: | |||||||||||||
| Equipment cost |
$700,000 | NPV | |||||||||||
| Freight | $35,000 | IRR | |||||||||||
| Installation | $70,000 | MIRR | |||||||||||
| Change in NWC |
$30,000 | ARR | |||||||||||
| Payback | years | ||||||||||||
| 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% | Cannibalization of Other Projects: | |||||||||||
| SV, Taxes, and C of C: | Revenue Loss | $80,000 | |||||||||||
| Salvage value |
$87,500 | Cost Reduction | $35,000 | ||||||||||
| Useful life (yrs) |
4 | Net Cannibalization | $45,000 | ||||||||||
| Tax rate | 40% | ||||||||||||
| Cost of capital |
12% | ||||||||||||
| = | = | = | = | = | = | = | |||||||
| |:: | |||||||||||||
| MODEL-GENERATED DATA: TASTY FOODS CORPORATION (PART II) | |||||||||||||
| Net Investment Outlay: | Depreciation Schedule: Basis: |
805000 | |||||||||||
| Equipment cost |
700000 | MACRS | Dep. | End of Year | |||||||||
| Freight | 35,000 | Year | Factor | Expense | Book Value | ||||||||
| Installation | 70,000 | —— | —— | ——– | ——– | ||||||||
| Change in NWC |
30,000 | 1 | 33% | $265,650 | $539,350 | ||||||||
| 2 | 45% | $362,250 | $177,100 | ||||||||||
| 835000 | 3 | 15% | $120,750 | $56,350 | |||||||||
| 4 | 7% | $56,350 | $0 | ||||||||||
| 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 |
700,000 | 700,000 | 700,000 | 700,000 | |||||||||
| Revenues | $1,456,000 | $1,514,240 | $1,574,810 | $1,637,802 | |||||||||
| Fixed operating costs |
190,000 | 190,000 | 190,000 | 190,000 | |||||||||
| Variable operating costs |
892,500 | 910,350 | 928,557 | 947,128 | |||||||||
| Total operating costs |
$1,082,500 | $1,100,350 | $1,118,557 | $1,137,128 | |||||||||
| Depreciation | 265,650 | 362,250 | 120,750 | 56,350 | |||||||||
| Net cannibalization effects |
45,000 | 45,000 | 45,000 | 45,000 | |||||||||
| Before tax income |
$62,850 | $6,640 | $290,503 | $399,324 | |||||||||
| Taxes | 25,140 | 2,656 | 116,201 | 159,730 | |||||||||
| Net income |
$37,710 | $3,984 | $174,302 | $239,594 | |||||||||
| Plus depreciation |
265,650 | 362,250 | 120,750 | 56,350 | |||||||||
| Net operating cash flow |
$303,360 | $366,234 | $295,052 | $295,944 | |||||||||
| Salvage value |
$87,500 | ||||||||||||
| SV tax | 35,000 | ||||||||||||
| Recovery of NWC |
30,000 | ||||||||||||
| 82500 | |||||||||||||
| Termination CF |
$ (835,000) | $ 303,360 | $ 366,234 | $ 295,052 | $ 378,444 | ||||||||
| Project NCF |
|||||||||||||
| Decision Measures: | Cumulative Cash Flows: | ||||||||||||
| NPV | $178,337 | 0 | $ (835,000) | ||||||||||
| IRR | 21.6% | 1 | (531,640) | ||||||||||
| TV | 1,594,505 | 2 | (165,406) | ||||||||||
| MIRR | 17.6% | 3 | 129,646 | ||||||||||
| ARR | #NAME? | 4 | 508,090 | ||||||||||
| Payback | 2.56 | years | |||||||||||
| |:: | |||||||||||||
| Scenario Analysis: | |||||||||||||
| Scenario | Prob. | NPV | IRR | MIRR | ARR | ||||||||
| ——– | —– | ——— | —– | —– | —– | ||||||||
| Worst | |||||||||||||
| Base | |||||||||||||
| Best | |||||||||||||
| Expected value | |||||||||||||
| Standard deviation | |||||||||||||
| Coeff. variation | |||||||||||||
| Data Tables for Sensitivity Analysis |
|||||||||||||
| 1. Unit Sales: | |||||||||||||
| Units: | NPV: | IRR: | MIRR: | ARR: | Payback: | ||||||||
| Deviation | 178336.7163 | 0.215583062 | 0.175533063 | #NAME? | 2.560600324 | ||||||||
| -0.3 | 490,000 | ||||||||||||
| -0.2 | 560,000 | ||||||||||||
| -0.1 | 630,000 | ||||||||||||
| 0 | 700,000 | ||||||||||||
| 0.1 | 770,000 | ||||||||||||
| 0.2 | 840,000 | ||||||||||||
| 0.3 | 910,000 | ||||||||||||
| 2. Initial Sales Price | |||||||||||||
| Price: | NPV: | IRR: | MIRR: | ARR: | Payback: | ||||||||
| Deviation | 178336.7163 | 0.215583062 | 0.175533063 | #NAME? | 2.560600324 | ||||||||
| -0.3 | 1.40 | ||||||||||||
| -0.2 | 1.60 | ||||||||||||
| -0.1 | 1.80 | ||||||||||||
| 0 | 2.00 | ||||||||||||
| 0.1 | 2.20 | ||||||||||||
| 0.2 | 2.40 | ||||||||||||
| 0.3 | 2.60 | ||||||||||||
| 3. Salvage Value | |||||||||||||
| Salvage | |||||||||||||
| Value: | NPV: | IRR: | MIRR: | ARR: | Payback: | ||||||||
| Deviation | 178336.7163 | 0.215583062 | 0.175533063 | #NAME? | 2.560600324 | ||||||||
| -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: | NPV: | IRR: | MIRR: | ARR: | Payback: | ||||||||
| Deviation | 178336.7163 | 0.215583062 | 0.175533063 | #NAME? | 2.560600324 | ||||||||
| -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: | NPV: | IRR: | MIRR: | ARR: | Payback: | ||||||||
| Deviation | 178336.7163 | 0.215583062 | 0.175533063 | #NAME? | 2.560600324 | ||||||||
| -0.3 | 8.4% | ||||||||||||
| -0.2 | 9.6% | ||||||||||||
| -0.1 | 10.8% | ||||||||||||
| 0 | 12.0% | ||||||||||||
| 0.1 | 13.2% | ||||||||||||
| 0.2 | 14.4% | ||||||||||||
| 0.3 | 15.6% | ||||||||||||
| Inflation (Data Table 2 with NPV as Output Variable) |
|||||||||||||
| Price | Cost Inflation: | ||||||||||||
| Inflation: | ——– | – | – | – | – | ||||||||
| 0% | 1% | 2% | 3% | 4% | |||||||||
| 0% | $0 | $0 | $0 | $0 | $0 | ||||||||
| 1% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 2% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 3% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 4% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 5% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 6% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 7% | 0 | 0 | 0 | 0 | 0 | ||||||||
| 8% | 0 | 0 | 0 | 0 | 0 | ||||||||
| |:: | |||||||||||||
| = | = | = | = | = | = | = | |||||||
| END | |||||||||||||
|
|
