ch11 13ed cf estimation minicmaster
TRANSCRIPT
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
1/20
1/1/2012
Chapter 11 Mini CaseCash Flow Estimation
Situation
Analysis of New Expansion Project
Part I: Input Data
Equipment cost $200,000 Key Output: NPV = $88,026
Shipping charge $10,000
Installation charge $30,000
Economic Life 4
Salvage Value $25,000
Tax Rate 40%
Cost of Capital 10%
Units Sold 1,250
Sales Price Per Unit $200
Incremental Cost Per Unit $100
NWC/Sales 12%
Inflation rate 3%
Shrieves Casting Company is considering adding a new line to its product mix, and the capital budgeting analysis isbeing conducted by Sidney Johnson, a recently graduated MBA. The production line would be set up in unused
space in Shrieves' main plant. The machinerys invoice price would be approximately $200,000, another $10,000 in
shipping charges would be required, and it would cost an additional $30,000 to install the equipment. The machinery
has an economic life of 4 years, and Shrieves has obtained a special tax ruling that places the equipment in the
MACRS 3-year class. The machinery is expected to have a salvage value of $25,000 after 4 years of use.
The new line would generate incremental sales of 1,250 units per year for 4 years at an incremental cost of $100 per
unit in the first year, excluding depreciation. Each unit can be sold for $200 in the first year. The sales price and
cost are expected to increase by 3% per year due to inflation. Further, to handle the new line, the firms net working
capital would have to increase by an amount equal to 12% of sales revenues. The firms tax rate is 40%, and its
overall weighted average cost of capital is 10%.
a. Define incremental cash flow. Answer: See Chapter 11 Mini Case Show
(2.) Suppose the firm had spent $100,000 last year to rehabilitate the production line site. Should this be
included in the analysis? Explain. Answer: See Chapter 11 Mini Case Show
(1.) Should you subtract interest expense or dividends when calculating project cash flow? Answer: See
Chapter 11 Mini Case Show
(3.) Now assume that the plant space could be leased out to another firm at $25,000 per year. Should this be
included in the analysis? If so, how? Answer: See Chapter 11 Mini Case Show
(4.) Finally, assume that the new product line is expected to decrease sales of the firms other lines by$50,000 per year. Should this be considered in the analysis? If so, how? Answer: See Chapter 11 Mini
Case Show
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
2/20
Annual Depreciation Expense
Depreciable Basis = Equipment + Freight + Installation
Depreciable Basis = $240,000
Year % x Basis = Depr.
ema n ng
Book
Value
1 0.33 $240,000 $79,200 $160,800
2 0.45 240,000 108,000 52,800
3 0.15 240,000 36,000 16,800
4 0.07 240,000 16,800 0
Annual Operating Cash Flows
Year 1 Year 2 Year 3 Year 4
Units 1,250 1,250 1,250 1,250
Unit price $200.00 $206.00 $212.18 $218.55
Unit cost $100.00 $103.00 $106.09 $109.27
Sales $250,000 $257,500 $265,225 $273,182
Costs 125,000 128,750 132,613 136,591
Depreciation 79,200 108,000 36,000 16,800
Operating income before taxes (EBIT) $45,800 $20,750 $96,613 $119,791
Taxes (40%) 18,320 8,300 38,645 47,916
EBIT (1T) $27,480 $12,450 $57,968 $71,875
Depreciation 79,200 108,000 36,000 16,800
Net operating CF $106,680 $120,450 $93,968 $88,675
Annual Cash Flows due to Investments in Net Working Capital
Year 0 Year 1 Year 2 Year 3 Year 4
Sales $250,000 $257,500 $265,225 $273,182
NWC (% of sales) 30,000 30,900 31,827 32,782
CF due to investment in NOWC) (30,000) (900) (927) (955) 32,782
b. Disregard the assumptions in Part a. What is Shrieves' depreciable basis? What are the annual
depreciation expenses?
c. Calculate the annual sales revenues and costs (other than depreciation). Why is it important to include
inflation when estimating cash flows? See answer to part d.
d. Construct annual incremental operating cash flow statements.
e. Estimate the required net working capital for each year, and the cash flow due to investments in net working
capital.
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
3/20
f. Calculate the after-tax salvage cash flow.
After-tax Salvage Value
Based on
facts in case: $25,000 $10,000
Salvage value $25,000 $25,000 $10,000Book value 0 16,800 16,800
Gain or loss $25,000 $8,200 ($6,800)
Tax on salvage value 10,000 3,280 (2,720)
Net terminal cash flow $15,000 $21,720 $12,720
Projected Net Cash FlowsYear 0 Year 1 Year 2 Year 3 Year 4
I nvestment Outlay: Long Term Assets ($240,000)Operating Cash F lows $106,680 $120,450 $93,968 $88,675
CF due to investment in NWC (30,000) (900) (927) (955) 32,782
Salvage Cash F lows 15,000
Net Cash Flows ($270,000) $105,780 $119,523 $93,013 $136,457
NPV $88,026
IRR 23.9% PV of Inflow TV of Inflows
$358,026 $524,186
Find MIRR 0 1 2 3 4Net Cash Flows ($270,000) $105,780 $119,523 $93,013 $136,457
102,314
144,623
140,793
PV= ($270,000) TV = $524,186
MIRR = 18.0%
Find Payback
0 1 2 3 4
Cash Flow ($270,000) $105,780 $119,523 $93,013 $136,457Cumulative Cash Flow for Payback ($270,000) ($164,220) ($44,697) $48,316 $184,772
Payback = 2.5
Years
g. Calculate the net cash flows for each year. Based on these cash flows, what are the projects NPV, IRR,
MIRR, and payback? Do these indicators suggest the project should be undertaken?
Years
To find MIRR, we could now find the discount rate that equates the PV and TV. But it is easier to use the MIRR
function.
Hypothetical: If sold after
3 years for
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
4/20
Evaluating Risk: Sensitivity Analysis
Sensitivity of NPV and to Variations in Input Variables
% Deviation % Deviation % Deviation
from NPV from Units NPV from Variable NPV
Base Case WACC 88,026 Base Case Sold $88,026 Base Case Cost $88,026
-30% 7.0% $113,284 -30% 875 $16,665 -30% $17,500 $84,953
-15% 8.5% 100,306 -15% 1,063 52,346 -15% 21,250 86,490
0% 10.0% 88,026 0% 1,250 88,026 0% 25,000 88,026
15% 11.5% 76,395 15% 1,438 123,707 15% 28,750 89,563
30% 13.0% 65,368 30% 1,625 159,387 30% 32,500 91,100
We summarize the data tables and show the sensitivity analysis graph below:
1st YEAR UNIT SALES SALVAGE
j. (1.) What is sensitivity analysis? Answer: See Chapter 11 Mini Case Show
h. What does the term risk mean in the context of capital budgeting; to what extent can risk be quantified; and
when risk is quantified, is the quantification based primarily on statistical analysis of historical data or on
subjective, judgmental estimates?
Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected
outcome. For example, if there were a high probability that the expected NPV as calculated above will actually turn
out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expectedoutcome is, typically, because sales were lower than expected, costs were higher than expected, and/or the project
turned out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse
than expected then the output will likewise be worse than expected. We use Excel to examine the project's sensitivity
to changes in the input variables.
WACC
Here we use an Excel "Data Table" to find the NPVs for changes in unit sales, salvage value, and WACC holding
other things constant--changing one variable at a time. This produces the sensitivity analys as shown below.
i. (1.) What are the three types of risk that are relevant in capital budgeting? Answer: See Chapter 11 Mini Case
Show
(3.) How is each type of risk used in the capital budgeting process? Answer: See Chapter 11 Mini Case Show
(2.) Perform a sensitivity analysis on the unit sales, salvage value, and cost of capital for the project. Assume
that each of these variables can vary from its base-case, or expected, value by plus and minus 10%, 20%, and
30%. Include a sensitivity diagram, and discuss the results.
(2.) How is each of these risk types measured, and how do they relate to one another? Answer: See Chapter 11
Mini Case Show
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
5/20
Evaluating Risk: Sensitivity Analysis
Deviation NPV Deviation from Base Case
from Units
Base Case WACC Sold Salvage
-30% $113,284 $16,665 $84,953
-15% 100,306 52,346 86,490
0% 88,026 88,026 88,026
15% 76,395 123,707 89,563
30% 65,368 159,387 91,100
Range $47,915 $176,053 $6,147
Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, hence
to see the combined effects of changes in several variables on NPV, and (2) it allows us to bring in the probabilities
of changes in the key variables.
(3.) What is the primary weakness of sensitivity analysis? What is its primary usefulness? Answer: See
Chapter 11 Mini Case Show
k. Assume that Sidney Johnson is confident of her estimates of all the variables that affect the projects cash flows
except unit sales and sales price: If product acceptance is poor, unit sales would be only 900 units a year and the
unit price would only be $160; a strong consumer response would produce sales of 1,600 units and a unit price of
$240. Sidney believes that there is a 25% chance of poor acceptance, a 25% chance of excellent acceptance, and
a 50% chance of average acceptance (the base case).
(1.) What is scenario analysis?
(2.) What is the worst-case NPV? The best-case NPV?
(3.) Use the worst-, most likely, and best-case NPVs and probabilities of occurrence to find the projects expected
NPV, standard deviation, and coefficient of variation.
0
20,000
40,000
60,000
80,000
100,000
120,000
140,000160,000
180,000
-40% -20% 0% 20% 40%
NPV ($)
Deviation from Base-Case Value
Sensitivity Analysis
Salvage Value
Units Sold
WACC
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
6/20
Evaluating Risk: Scenario Analysis
Scenario Analysis
Probability Unit Sales Unit Price NPV
25% 1,600 $240 $278,965
50% 1,250 $200 $88,030
25% 900 $160 ($48,514)
Quick calculation:
Expected NPV = $101,628 $101,628
Standard Deviation = $116,577 $116,577
Coefficient of Variation = Std Dev / Expected NPV = 1.15
Monte Carlo Simulation
Risk Adjusted Cost of Capital
Cost of capital for average projects: 10%
Adjustment for risky projects: 3%
Risk adjusted cost of capital: 13%
NPV with risk-adjusted cost of capital: $65,368 (See the +30% WACC in the sensitivity analysis above.)
The CV of this project is 1.15, which is larger than the CV range of the firm's average project. Consequently, this
project is riskier than the firm's average project, so management should add 3% to the WACC to risk adjust.
$7,862,111,358.79
$92,450,542.34
$5,635,612,088.43
m. (1.) Assume that Shrieves' average project has a coefficient of variation in the range of 0.2 to 0.4. Would the
new line be classified as high risk, average risk, or low risk? What type of risk is being measured here?
Answer: See Chapter 11 Mini Case Show
Best Case
Monte Carlo simulation is similar to scenario analysis in that different values of key input variables are used. Unlike
scenario analysis, Monte Carlo simulation draws the input values from specified probability distributions and then
computes the NPV. It repeats this process hundreds, or even thousands, of times. It then averages the NPVs from
each repetition.
Scenario
l. Are there problems with scenario analysis? Define simulation analysis, and discuss its principal advantages and
disadvantages. Answer: See Chapter 11 Mini Case Show
Base Case
Worst Case
m. What is a real option? What are some types of real options? Answer: See Chapter 11 Mini Case Show
(2.) Shrieves typically adds or subtracts 3 percentage points to the overall cost of capital to adjust for risk.
Should the new line be accepted?
(3.) Are there any subjective risk factors that should be considered before the final decision is made? Answer:
See Chapter 11 Mini Case Show
Squared Deviation
times Probability
We could find the NPV by entering the value of unit sales and price for each scenario and then recording the NPV
(this is what we did for the table below). Alternatively, we could use Tools, Scenarios to define the inputs for each
scenario, which we did and show in the Scenario Summary Tab below. In fact, you could even use Tools, Scenarios,
and then click the Summary button on the dialog box, and it will automatically create a table similar to the one
below. This is a powerful feature of Excel, and we encourage you to explore it.
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
7/20
Scenario Summary
Current Values: Base Case Best Case
Changing Cells:$D$36 $200,000 $200,000 $200,000$D$37 $10,000 $10,000 $10,000
$D$38 $30,000 $30,000 $30,000
$D$39 4 4 4
$D$40 $25,000 $25,000 $25,000
$D$41 40% 40% 40%
$D$42 10% 10% 10%
$D$43 1,250 1,250 1,600
$D$44 $200 $200 $240
$D$45 $100 $100 $100
$D$46 12% 12% 12%$D$47 3% 3% 3%
Result Cells:$C$113 $88,030 $88,030 $278,965
$C$114 23.9% 23.9% 48.3%Notes: Current Values column represents values of changing cells attime Scenario Summary Report was created. Changing cells for eachscenario are highlighted in gray.
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
8/20
Worst Case Base-but forget inflation
$200,000 $200,000$10,000 $10,000$30,000 $30,000
4 4$25,000 $25,000
40% 40%10% 10%900 1,250
$160 $200$100 $100
12% 12%3% 0%
($48,514) $78,387
1.0% 22.7%
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
9/20
Analysis of New Expansion Project
Part I: Input Data
Equipment cost $200,000 Key Output: NPV =
Shipping charge $10,000
Installation charge $30,000Economic Life 4
Salvage Value $25,000
Tax Rate 40%
Cost of Capital 10%
Expected
Value Std. Dev.
Units Sold Random variable = 1,485 1,250 200
Sales Price Per Unit Random variable = $219 $200 $30
Incremental Cost Per Unit $100
NWC/Sales 12%
Inflation rate 3%
Annual Depreciation Expense
Depreciable Basis = Equipment + Freight + Installation
Depreciable Basis = $240,000
Year % x Basis = Depr.
Monte Carlo simulation is similar to scenario analysis in that different values of key inputs are use
analysis, Monte Carlo simulation draws a trial set of input values from specified probability distrib
computes the NPV for this trial. This process is repeated for hundreds, or even thousands, of trial(like NPV) saved from each trial. After running the number of desired trials, the NPVs from the trial
estimate the project's expected NPV; the trial results can also be used to provide a histogram sho
possible outcomes.
The green area below is the same project as in the mini case, but we have replaced the inputs fro u
price with random variables drawn from normal distributions with the expected values and means
inputs. Notice that each time the sheet makes a calculation, the values for unit sales, sales price, a
you can make the sheet calculate by hitting the F9 key).
Here is a tip for simulating a project analysis. If you have already done the analysis and it is in a dif
how many rows it takes. Delete the green area below and add enough rows so that there will be roanalysis. For example, this model was in the "Model" tab in the file Ch 11 Mini Case.xls, rows 33-1
file, selected Rows 32-135, copied them, and then pasted them into Rows 32-135 of this Worksheet.
them into the same row numbers from which we copied them, all the formula references remained
edited this worksheet.
b. Disregard the assumptions in Part a. What is Shrieves' depreciable basis? What are the annual
Section 11.7 Scenario Analysis
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
10/20
1 0.33 $240,000 $79,200
2 0.45 240,000 108,000
3 0.15 240,000 36,000
4 0.07 240,000 16,800
Annual Operating Cash Flows
Year 1 Year 2 Year 3
Units 1,485 1,485 1,485
Unit price $219.37 $225.95 $232.73
Unit cost $100.00 $103.00 $106.09
Sales $325,845 $335,621 $345,692
Costs 148,538 152,994 157,584
Depreciation 79,200 108,000 36,000Operating income before taxes (EBIT) $98,107 $74,627 $152,108
Taxes (40%) 39,243 29,851 60,843
EBIT (1T) $58,864 $44,776 $91,265
Depreciation 79,200 108,000 36,000
Net operating CF $138,064 $152,776 $127,265
Annual Cash Flows due to Investments in Net Working Capital
Year 0 Year 1 Year 2 Year 3
Sales $325,845 $335,621 $345,692
NWC (% of sales) 39,101 40,275 41,483 42,727
CF due to investment in NOWC) (39,101) (1,174) (1,208) (1,244)
f. Calculate the after-tax salvage cash flow.
After-tax Salvage Value
Based on
facts in case: $25,000 $10,000
Salvage value $25,000 $25,000 $10,000
Book value 0 16,800 16,800
Gain or loss $25,000 $8,200 ($6,800)
Tax on salvage value 10,000 3,280 (2,720)
Net terminal cash flow $15,000 $21,720 $12,720
Projected Net Cash FlowsYear 0 Year 1 Year 2
c. Calculate the annual sales revenues and costs (other than depreciation). Why is it important to include
inflation when estimating cash flows? See answer to part d.
Hypothetical: If sold
g. Calculate the net cash flows for each year. Based on these cash flows, what are the projects NPV, IRR,
MIRR, and payback? Do these indicators suggest the project should be undertaken?
d. Construct annual incremental operating cash flow statements.
e. Estimate the required net working capital for each year, and the cash flow due to investments in net worki
capital.
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
11/20
I nvestment Outlay: Long Term Assets ($240,000)
Operating Cash F lows $138,064 $152,776
CF due to investment in NWC (39,101) (1,174) (1,208)
Salvage Cash F lows
Net Cash Flows ($279,101) $136,890 $151,568
NPV $188,709
IRR 37.4% PV of Inflow V of Inflows
$467,810 $684,920
Find MIRR 0 1 2Net Cash Flows ($279,101) $136,890 $151,568
PV= ($279,101)
MIRR = 25.2%
Find Payback
0 1 2
Cash Flow ($279,101) $136,890 $151,568
Cumulative Cash Flow for Payback ($279,101) ($142,211) $9,357
Payback = 1.9
How the Simulation Works
Column input cell to "trick" Excel into updating random variables in Data Table: 1
Excel normally updates all values in a Data Table each time any cell that is related to the Data Tabl
case, we have random variables in the Data Table, so each time any cell in the worksheet makes a
Table is updated. If the Data Table has many rows, updating it can take up to 20 or 30 seconds. Wit
updates very quickly. But if it bothers you, you can set the worksheet to do automatic calculation e
We use a Data Table to perform the simulation (the Data Table is below, shaded bright yellow). Wh
updated, it will insert new random variables for each of the inputs we allow to change in Panel A a
is Panel C above, and then save the NPV for each trial (we also save the input variables for each tri
verify that they are behaving as we expect). We set the first column of the Data Table (the variable
row) to numbers from 1-100. We don't really use these numbers anywhere in the analyis, but if we t
treat these as the Column inputs, Excel will recalculate all items in the Data Table, including the ra
resulting NPV. In other words, we "trick" Excel into doing a simulation. We tell Excel to insert each
in the Data Table into the cell immediately below this box. This cell isn't linked to anything else, bu
updates a row of the Data Table, all the random values will be updated.
Years
Years
To find MIRR, we could now find the discount rate that equates the PV and TV. But it is easier to use the MI
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
12/20
Number of Trials = 100
NPV
Mean $1,280 199 $89,403Standard deviation 221 30 $83,604
Maximum 1,786 272 $291,009Minimum 838 119 -$91,203
Median $81,777Probability of NPV > 0 86.0%
Coefficient of variation 0.94
Units Sold
Sales
Price Per
Unit
Simulated Input Variables and Key Results
Key Results:
You don't need to change anything in this section. It will be updated automatically if you do a sim
of the simulation results and the histogram are based on the simulation trials n the Data Table belo
automatically when you do a simulation. You can do an updated simulation by hitting the F9 key.
Figure 11-27 Summary of Simulation Results (Thousands of Dollars)
-291,009 -145,504 0 145,504
Probability
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
13/20
Output of Simulation in Data Table
Trial Number Units Sold
Sales Price
Per Unit NPV
1,485 $219 $188,709
1 1286.4418 195.9223 84770.12492 987.56646 261.22552 155540.7393 988.66522 172.83622 -13876.16254 961.89588 152.60289 -55358.97465 1576.558 242.21225 279448.6856 1550.4039 227.14475 226951.7957 915.11743 226.35045 71145.56618 1330.9959 192.29614 83524.54599 1141.8941 184.87605 33909.4793
10 1191.3958 194.55978 64285.122111 1198.0698 171.87254 12682.657512 1323.6941 190.10262 76601.818713 959.24721 218.42795 67036.150714 1332.5825 202.08658 109139.30115 864.44556 146.15347 -75769.180316 1396.1755 140.61386 -45218.033917 1123.8948 221.48622 110936.99518 1001.7728 221.23507 82116.078519 1378.2263 204.88904 125521.135
20 1186.5449 170.95729 9010.5421821 1379.0628 180.09233 59255.5191
22 1209.8423 157.36125 -19829.506723 848.97229 213.23064 33538.191224 1566.1622 201.93062 154070.33125 1388.4118 196.3712 104579.15926 1206.9476 207.77276 98061.1927 1526.2278 181.08644 84509.336228 1176.8269 179.84892 28031.943929 1205.1292 185.2283 44911.03230 1177.2635 272.30684 239550.3331 1354.374 197.06012 100152.342
32 1342.5931 209.52277 130482.8533 1123.2977 213.89411 94235.736634 1375.1261 243.72929 228652.408
35 1767.0401 119.12158 -91203.430436 1702.6843 211.25553 211398.26437 1381.5838 201.73928 117740.89838 985.60931 165.55957 -28228.296239 1187.235 167.87042 1978.3292140 1069.7153 231.33881 118843.913
41 1366.9459 212.78389 144225.00542 915.39405 233.67922 84244.299643 1416.3912 220.72696 176725.616
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
14/20
44 1483.9245 165.53009 33177.221245 1225.7136 221.17434 133819.17146 1411.4842 165.73588 24805.8851
47 1128.449 212.35655 91986.039848 1441.1985 185.3975 83531.361749 997.63677 201.05387 42048.0139
50 1349.9929 157.81077 -3588.1695951 1138.6369 220.21378 111542.51552 1210.0944 267.33939 238731.16653 1437.9396 230.38128 208654.154 1506.2886 175.63362 65500.391355 1339.9211 190.89281 81438.779556 1455.1541 206.80612 146306.01157 1313.8266 191.09623 77442.9503
58 1416.1463 241.67443 234287.74259 1256.1977 233.83715 171781.1760 1198.1991 137.21253 -67973.208261 1622.5228 241.90766 291008.595
62 1178.9652 184.91718 39969.416963 1235.6515 248.76766 202362.12864 1716.566 192.07921 150403.77165 838.34202 202.17806 13235.343366 1543.9464 196.21087 132596.98667 1786.0216 169.71382 84952.2343
68 1248.4751 157.2398 -15966.683669 1065.4118 179.21692 9884.3979970 839.06204 195.72642 2865.1532771 1537.4824 176.51587 72600.604872 1286.2522 175.11074 32729.483973 1507.0986 165.07453 34702.168674 1300.0919 254.51812 235251.139
75 1305.4022 191.72077 77579.070876 1340.4016 244.26588 220489.6777 1203.1425 134.81989 -73230.41678 1126.8315 203.25189 71708.15
79 1049.6159 191.63905 32847.020880 1647.0967 208.48968 190765.76381 1282.6876 192.88699 76520.484982 970.52486 177.85289 -6913.2353783 1319.2708 205.21869 114587.24884 1201.2391 163.96571 -5338.6849985 1443.5183 174.11081 52249.41486 1195.8431 198.38857 73976.2338
87 1488.3572 205.36092 148891.13888 1099.9464 197.67381 54499.982589 1480.6397 214.83614 174592.48190 1527.5799 226.11027 218333.30991 1120.9392 202.22142 68309.731892 1167.1577 164.47961 -8266.9748793 1746.6447 218.9593 246862.22894 1314.8341 208.28927 121533.86395 958.86425 215.84567 62141.1734
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
15/20
96 1076.0013 240.71634 140025.89897 1435.5205 182.47692 74467.533598 1307.0832 192.94571 80982.412
99 1617.3489 200.4013 159200.505100 1149.8379 211.69949 95099.3112
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
16/20
1/1/2012
$188,709
Remaining
Book Value
Unlike scenario
tions and then
, with key resultscan be averaged to
ing the project's
nits sold and sales
hown next to the
d NPV change (Hint:
erent worksheet, see
m for your previous2. We went into that
Because we pasted
orrect. We then
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
17/20
$160,800
52,800
16,800
0
Year 4
1,485
$239.71
$109.27
$356,060
162,307
16,800$176,953
70,781
$106,172
16,800
$122,972
Year 4
$356,060
42,727
Year 3 Year 4
g
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
18/20
$127,265 $122,972
(1,244) 42,727
15,000
$126,021 $180,699
3 4
$126,021 $180,699
138,623
183,398
182,201
TV = $684,920
3 4
$126,021 $180,699
$135,378 $316,077
Don't change the the red cell.
changes. In our
alculation, the Data
only 100 rows, it
xcept for data tables.
n the Data Table is
ove, run the analysis
l so that we can
o be changed in each
ell the Data Table to
dom inputs and the
of the Column inputs
each time Excel
R function.
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
19/20
Scratch work for chart: see comments.
CountRange bottom 0 Percent
-$291,009 0 0%-$270,222 0 0%
-$249,436 0 0%
-$228,650 0 0%-$207,863 0 0%
-$187,077 0 0%-$166,291 0 0%-$145,504 0 0%-$124,718 0 0%-$103,932 1 1%
-$83,145 3 3%-$62,359 2 2%-$41,573 1 1%-$20,786 7 7%
$0 6 6%$20,786 9 9%$41,573 6 6%$62,359 16 16%
$83,145 11 11%$103,932 8 8%$124,718 6 6%$145,504 6 6%$166,291 3 3%$187,077 2 2%$207,863 5 5%$228,650 6 6%$249,436 0 0%
lation. The summary
w and are updated
291,009
PV ($)
-
8/10/2019 Ch11 13ed CF Estimation MinicMaster
20/20
$270,222 2 2%$291,009 0 0%
Sum 100 100%