Staged Entry Analysis
koehler1incase21_5th_edition.xls
CASE21
CASE 21 | Instructor Version | Copyright 2014 Health Administration Press | ||||||||
8/12/13 | ||||||||||
NATIONAL REHABILITATION CENTERS | ||||||||||
Staged Entry Analysis | ||||||||||
This case is designed to give further insight into the capital budgeting decision process. It | ||||||||||
focuses on the timing and relevancy of cash flows, the use of decision trees, abandonment, | ||||||||||
value, and the advantages and disadvantages of staged entry. | ||||||||||
The model calculates NPV, IRR, MIRR, payback, and discounted payback on the basis of | ||||||||||
input data for two stages of a project. Note that the model extends to Column K. | ||||||||||
The model consists of a complete base case analysis--no changes need to be made | ||||||||||
to the existing MODEL-GENERATED DATA section. However, all values in the student | ||||||||||
version INPUT DATA section have been replaced with zeros. Thus, students must determine | ||||||||||
the appropriate input values and enter them into the model. These cells are colored red. | ||||||||||
When this is done, any error cells will be corrected and the base case solution will appear. | ||||||||||
However, students must create their own graphics (charts) as needed to present their results. | ||||||||||
Note that the model contains a second sheet to help with the risk calculations. Here, users | ||||||||||
enter the NPVs and joint probabilities of each branch of the decision tree and the model | ||||||||||
calculates expected NPV, variance, standard deviation, and coefficient of variation. | ||||||||||
INPUT DATA: (000s of $) | KEY OUTPUT: (000s of $) | |||||||||
Stage 1: | Stage 1: | |||||||||
Facilities Costs: | Poor Scenario | Good Scenario | ||||||||
NPV | ($21,083,631) | $13,336,679 | ||||||||
Labor market study costs | $2,000,000 | IRR | -1.0% | 15.9% | ||||||
Land acquisition costs | $10,000,000 | MIRR | 3.1% | 13.2% | ||||||
Land salvage value | $10,000,000 | Payback | 0.0 | 6.8 | ||||||
Building / equipment costs | $50,000,000 | Disc payback | 100.0 | 7.5 | ||||||
Bldg / equip salvage value | $30,000,000 | |||||||||
Expected values: | ||||||||||
Operating Costs: | NPV | ($3,873,476) | ||||||||
IRR | 7.5% | |||||||||
Variable costs | 30.0% | |||||||||
Fixed costs | $25,000,000 | Stage 2: | ||||||||
Fixed cost inflation rate | 2.0% | High | Medium | Low | ||||||
NPV @ Yr 0 | $277,986,895 | $130,185,848 | ($91,171,442) | |||||||
Revenue Data: | IRR | 35.2% | 23.3% | -1.9% | ||||||
MIRR | 30.0% | 20.3% | 0.9% | |||||||
Poor Demand: | ||||||||||
Year 4 total revenues | $40,000,000 | Expected NPV | ||||||||
Revenue growth rate | 3.0% | Stage 1 poor demand | $34,287,308 | |||||||
Stage 1 good demand | $181,950,643 | |||||||||
Good Demand: | Combined NPV | $108,118,975 | ||||||||
Year 4 total revenues | $60,000,000 | |||||||||
Revenue growth rate | 6.0% | Combined Stage 1 and Stage 2 NPV: | ||||||||
Other Data: | Poor Stage 1: | |||||||||
Stage 1 + Low demand | ($112,255,073) | |||||||||
Unexpensed dev. costs | $5,000,000 | Stage 1 + Medium demand | $109,102,217 | |||||||
Tax rate | 30.0% | Stage 1 + High demand | $256,903,264 | |||||||
Corporate cost of capital | 10.0% | |||||||||
Risk adj for project risk | 0.0% | Good Stage 1: | ||||||||
Demand probabilities: | Stage 1 + Low demand | ($77,834,763) | ||||||||
Poor | 50.0% | Stage 1 + Medium demand | $143,522,527 | |||||||
Good | 50.0% | Stage 1 + High demand | $291,323,574 | |||||||
Stage 2: | Expected NPV | $104,245,499 | ||||||||
Cash Flow Data: | ||||||||||
Net Cash Flow | ||||||||||
End of Year | High Demand | Med Demand | Low Demand | |||||||
6 | ($240,000,000) | ($240,000,000) | ($240,000,000) | |||||||
7 | (240,000,000) | (240,000,000) | (240,000,000) | |||||||
8 | 210,000,000 | 160,000,000 | 70,000,000 | |||||||
9 | 228,000,000 | 170,000,000 | 75,000,000 | |||||||
10 | 241,000,000 | 175,000,000 | 75,000,000 | |||||||
11 | 256,000,000 | 180,000,000 | 75,000,000 | |||||||
12 | 500,000,000 | 350,000,000 | 150,000,000 | |||||||
Other Data: | ||||||||||
Demand probabilities: | ||||||||||
Poor Stage 1 | Good Stage 1 | |||||||||
Low | 50.0% | 10.0% | ||||||||
Medium | 40.0% | 40.0% | ||||||||
High | 10.0% | 50.0% | ||||||||
MODEL-GENERATED DATA: | ||||||||||
Stage 1: | ||||||||||
Depreciation Cash Flow Worksheet: | ||||||||||
Year | Build / Equip | |||||||||
4 | $7,150,000 | |||||||||
5 | 12,250,000 | |||||||||
6 | 8,750,000 | |||||||||
7 | 6,250,000 | |||||||||
8 | 4,450,000 | |||||||||
$38,850,000 | ||||||||||
Cash Flow Statements: | ||||||||||
Poor Demand Scenario: | ||||||||||
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||
Feasibility study costs | ($2,000,000) | |||||||||
Land acquisition costs | ($10,000,000) | |||||||||
R & D tax savings | (1,500,000) | $300,000 | $300,000 | $300,000 | $300,000 | $300,000 | ||||
Building/equipment costs | (25,000,000) | (25,000,000) | ||||||||
Total facilities costs | ($3,500,000) | ($10,000,000) | ($25,000,000) | ($25,000,000) | $300,000 | $300,000 | $300,000 | $300,000 | $300,000 | |
Total revenues | $40,000,000 | $41,200,000 | $42,436,000 | $43,709,080 | $45,020,352 | |||||
Variable costs | 12,000,000 | 12,360,000 | 12,730,800 | 13,112,724 | 13,506,106 | |||||
Total fixed costs | 25,000,000 | 25,500,000 | 26,010,000 | 26,530,200 | 27,060,804 | |||||
Depreciation | 7,150,000 | 12,250,000 | 8,750,000 | 6,250,000 | 4,450,000 | |||||
Operating income | ($4,150,000) | ($8,910,000) | ($5,054,800) | ($2,183,844) | $3,443 | |||||
Tax | (1,245,000) | (2,673,000) | (1,516,440) | (655,153) | 1,033 | |||||
Net income | ($2,905,000) | ($6,237,000) | ($3,538,360) | ($1,528,691) | $2,410 | |||||
Plus depreciation | 7,150,000 | 12,250,000 | 8,750,000 | 6,250,000 | 4,450,000 | |||||
Operating cash flow | $4,245,000 | $6,013,000 | $5,211,640 | $4,721,309 | $4,452,410 | |||||
Land salvage value | 10,000,000 | |||||||||
Land salvage value tax | 0 | |||||||||
Building / Equip salvage value | 30,000,000 | |||||||||
Building / Equip SV tax | (5,655,000) | |||||||||
Net cash flow | ($3,500,000) | ($10,000,000) | ($25,000,000) | ($25,000,000) | $4,545,000 | $6,313,000 | $5,511,640 | $5,021,309 | $39,097,410 | |
NPV, IRR, MIRR, and Payback: | ||||||||||
Project cost of capital = | 10.0% | |||||||||
Cash | PV of | Cumulative | Cumulative | Terminal | ||||||
Year | Flow | Cash Flow | Flows | PV | Value | |||||
0 | ($3,500,000) | ($3,500,000) | ($3,500,000) | ($3,500,000) | ||||||
1 | (10,000,000) | (9,090,909) | (13,500,000) | (12,590,909) | ||||||
2 | (25,000,000) | (20,661,157) | (38,500,000) | (33,252,066) | ||||||
3 | (25,000,000) | (18,782,870) | (63,500,000) | (52,034,936) | ||||||
4 | 4,545,000 | 3,104,296 | (58,955,000) | (48,930,640) | $6,654,335 | |||||
5 | 6,313,000 | 3,919,876 | (52,642,000) | (45,010,764) | 8,402,603 | |||||
6 | 5,511,640 | 3,111,177 | (47,130,360) | (41,899,587) | 6,669,084 | |||||
7 | 5,021,309 | 2,576,726 | (42,109,051) | (39,322,861) | 5,523,440 | |||||
8 | 39,097,410 | 18,239,230 | (3,011,641) | (21,083,631) | 39,097,410 | |||||
NPV | ($21,083,631) | PV of COF | ($52,034,936) | |||||||
IRR | -1.0% | TV of CIF | $66,346,872 | |||||||
MIRR | 3.1% | |||||||||
Payback | 0.0 | |||||||||
Discounted payback | 100.0 | |||||||||
Good Demand Scenario: | ||||||||||
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||
Feasibility study costs | ($2,000,000) | |||||||||
Land acquisition costs | ($10,000,000) | |||||||||
R & D tax savings | (1,500,000) | $300,000 | $300,000 | $300,000 | $300,000 | $300,000 | ||||
Building/equipment costs | (25,000,000) | (25,000,000) | ||||||||
Total facilities costs | ($3,500,000) | ($10,000,000) | ($25,000,000) | ($25,000,000) | $300,000 | $300,000 | $300,000 | $300,000 | $300,000 | |
Total revenues | $60,000,000 | $63,600,000 | $67,416,000 | $71,460,960 | $75,748,618 | |||||
Variable costs | 18,000,000 | 19,080,000 | 20,224,800 | 21,438,288 | 22,724,585 | |||||
Total fixed costs | 25,000,000 | 25,500,000 | 26,010,000 | 26,530,200 | 27,060,804 | |||||
Depreciation | 7,150,000 | 12,250,000 | 8,750,000 | 6,250,000 | 4,450,000 | |||||
Operating income | $9,850,000 | $6,770,000 | $12,431,200 | $17,242,472 | $21,513,228 | |||||
Tax | 2,955,000 | 2,031,000 | 3,729,360 | 5,172,742 | 6,453,968 | |||||
Net income | $6,895,000 | $4,739,000 | $8,701,840 | $12,069,730 | $15,059,260 | |||||
Plus depreciation | 7,150,000 | 12,250,000 | 8,750,000 | 6,250,000 | 4,450,000 | |||||
Operating cash flow | $14,045,000 | $16,989,000 | $17,451,840 | $18,319,730 | $19,509,260 | |||||
Land salvage value | 10,000,000 | |||||||||
Land salvage value tax | 0 | |||||||||
Building / Equipment salvage value | 30,000,000 | |||||||||
Building / Equipment SV tax | (5,655,000) | |||||||||
Net cash flow | ($3,500,000) | ($10,000,000) | ($25,000,000) | ($25,000,000) | $14,345,000 | $17,289,000 | $17,751,840 | $18,619,730 | $54,154,260 | |
NPV, IRR, MIRR, and Payback: | ||||||||||
Project cost of capital = | 10.0% | |||||||||
Cash | PV of | Cumulative | Cumulative | Terminal | ||||||
Year | Flow | Cash Flow | Flows | PV | Value | |||||
0 | ($3,500,000) | ($3,500,000) | ($3,500,000) | ($3,500,000) | ||||||
1 | (10,000,000) | (9,090,909) | (13,500,000) | (12,590,909) | ||||||
2 | (25,000,000) | (20,661,157) | (38,500,000) | (33,252,066) | ||||||
3 | (25,000,000) | (18,782,870) | (63,500,000) | (52,034,936) | ||||||
4 | 14,345,000 | 9,797,828 | (49,155,000) | (42,237,108) | $21,002,515 | |||||
5 | 17,289,000 | 10,735,109 | (31,866,000) | (31,501,999) | 23,011,659 | |||||
6 | 17,751,840 | 10,020,451 | (14,114,160) | (21,481,548) | 21,479,726 | |||||
7 | 18,619,730 | 9,554,866 | 4,505,570 | (11,926,683) | 20,481,703 | |||||
8 | 54,154,260 | 25,263,362 | 58,659,830 | 13,336,679 | 54,154,260 | |||||
NPV | $13,336,679 | PV of COF | ($52,034,936) | |||||||
IRR | 15.9% | TV of CIF | $140,129,863 | |||||||
MIRR | 13.2% | |||||||||
Payback | 6.8 | |||||||||
Discounted payback | 7.5 | |||||||||
Stage 2: | ||||||||||
High Demand Scenario: | ||||||||||
Cash | PV of | Cumulative | Cumulative | Terminal | ||||||
Year | Flow | Cash Flow | Flows | PV | Value | |||||
6 | ($240,000,000) | ($240,000,000) | ($240,000,000) | ($240,000,000) | ||||||
7 | (240,000,000) | (218,181,818) | (480,000,000) | (458,181,818) | ||||||
8 | 210,000,000 | 173,553,719 | (270,000,000) | (284,628,099) | ||||||
9 | 228,000,000 | 171,299,775 | (42,000,000) | (113,328,325) | 303,468,000 | |||||
10 | 241,000,000 | 164,606,243 | 199,000,000 | 51,277,918 | 291,610,000 | |||||
11 | 256,000,000 | 158,955,859 | 455,000,000 | 210,233,777 | 281,600,000 | |||||
12 | 500,000,000 | 282,236,965 | 955,000,000 | 492,470,742 | 500,000,000 | |||||
Project cost of capital = | 10.0% | |||||||||
NPV @ Year 6 | $492,470,742 | PV of COF | ($284,628,099) | |||||||
IRR | 35.2% | TV of CIF | $1,376,678,000 | |||||||
MIRR | 30.0% | |||||||||
Payback | 3.2 | |||||||||
Discounted payback | 3.7 | |||||||||
NPV @ Year 0 | $277,986,895 | |||||||||
Medium Demand Scenario: | ||||||||||
Cash | PV of | Cumulative | Cumulative | Terminal | ||||||
Year | Flow | Cash Flow | Flows | PV | Value | |||||
6 | ($240,000,000) | ($240,000,000) | ($240,000,000) | ($240,000,000) | ||||||
7 | ($240,000,000) | (218,181,818) | (480,000,000) | (458,181,818) | ||||||
8 | $160,000,000 | 132,231,405 | (320,000,000) | (325,950,413) | ||||||
9 | $170,000,000 | 127,723,516 | (150,000,000) | (198,226,897) | 226,270,000 | |||||
10 | $175,000,000 | 119,527,355 | 25,000,000 | (78,699,542) | 211,750,000 | |||||
11 | $180,000,000 | 111,765,838 | 205,000,000 | 33,066,296 | 198,000,000 | |||||
12 | $350,000,000 | 197,565,876 | 555,000,000 | 230,632,171 | 350,000,000 | |||||
Project cost of capital = | 10.0% | |||||||||
NPV @ Year 6 | $230,632,171 | PV of COF | ($325,950,413) | |||||||
IRR | 23.3% | TV of CIF | $986,020,000 | |||||||
MIRR | 20.3% | |||||||||
Payback | 3.9 | |||||||||
Discounted payback | 4.7 | |||||||||
NPV @ Year 0 | $130,185,848 | |||||||||
Low Demand Scenario: | ||||||||||
Cash | PV of | Cumulative | Cumulative | Terminal | ||||||
Year | Flow | Cash Flow | Flows | PV | Value | |||||
6 | ($240,000,000) | ($240,000,000) | ($240,000,000) | ($240,000,000) | ||||||
7 | (240,000,000) | (218,181,818) | (480,000,000) | (458,181,818) | ||||||
8 | 70,000,000 | 57,851,240 | (410,000,000) | (400,330,579) | ||||||
9 | 75,000,000 | 56,348,610 | (335,000,000) | (343,981,968) | 99,825,000 | |||||
10 | 75,000,000 | 51,226,009 | (260,000,000) | (292,755,959) | 90,750,000 | |||||
11 | 75,000,000 | 46,569,099 | (185,000,000) | (246,186,860) | 82,500,000 | |||||
12 | 150,000,000 | 84,671,090 | (35,000,000) | (161,515,771) | 150,000,000 | |||||
Project cost of capital = | 10.0% | |||||||||
NPV @ Year 6 | ($161,515,771) | PV of COF | ($400,330,579) | |||||||
IRR | -1.9% | TV of CIF | $423,075,000 | |||||||
MIRR | 0.9% | |||||||||
Payback | 100.0 | |||||||||
Discounted payback | 100.0 | |||||||||
NPV @ Year 0 | ($91,171,442) | |||||||||
END |
&A
Page &P
STANDARD DEVIATION CALCULATOR
STANDARD DEVIATION CALCULATOR | |||
INPUT DATA: | |||
Branch | Joint | ||
NPV | Probability | ||
$291,323,574 | 25.0% | ||
$143,522,527 | 20.0% | ||
($77,834,763) | 5.0% | ||
$256,903,264 | 5.0% | ||
$109,102,217 | 20.0% | ||
($112,255,073) | 25.0% | ||
100.0% | |||
KEY OUTPUT: | |||
E(NPV) = | $104,245,499 | ||
Variance = | 23,603,811,234,396,300 | ||
Standard Deviation = | $153,635,319 | ||
Coefficient of Variation = | 1.5 | ||
END |