Incremental operating cash flow statements

Extracted Content from Question Files:

Ch 14 Mini Case 3/26/2001

Chapter 14 Mini Case

Situation
John Crockett Furniture Company is considering adding a new line to its product mix, and the capital
budgeting analysis is being conducted by John Samuels, a recently graduated finance MBA. The
production line would be set up in unused space in Crockett's main plant. The machinery's invoice
price would be approximately $200,000: another $10,00 in shipping charges would be required; and it
would cost an additional$30,000 to install the equipment. Further, the firm's inventories would have to
be increased by $25,000 to handle the new line, but its accounts payable would rise by $5,000. The
machinery has an economic life of 4 years, and Crockett has obtained a special tax ruling which 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 $125,000 in incremental net revenues (before tax and excluding depreciation)
in each of the next 4 years. The firm's tax rate is 40 percent, and its overall weighted average cost of
capital is 10 percent.

Analysis of New Expansion Project

Part I: Input Data

Costs (Depreciable Basis) $240,000 Net Investment Outlays
Change in NWC $20,000 Equipment -$200,000
Economic Life 4 Freight & Inst. -$40,000
Salvage Value $25,000 Change In NWC -$20,000
Incremental Gross Sales $250,000 Net CF -$260,000
Incremental Cash Op. Costs $125,000
Tax Rate 40%
Cost of Capital 10%
Units Sold 1250
Sales Price Per Unit $200
Incremental Cost Per Unit $100

Annual Depreciation Expense

Year % x Basis = Depr.
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

Operating Cash Flows
Year 1 Year 2 Year 3 Year 4
Net Revenue $125,000 $125,000 $125,000 $125,000
Depreciation $79,200 $108,000 $36,000 $16,800
Before-tax-income $45,800 $17,000 $89,000 $108,200
Taxes (40%) $18,320 $6,800 $35,600 $43,280
Net Income $27,480 $10,200 $53,400 $64,920
Depreciation $79,200 $108,000 $36,000 $16,800

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
Net Operating CF $106,680 $118,200 $89,400 $81,720

Net Terminal Cash Flows at Year 4

Salvage Value $25,000
Tax on Salvage Value $10,000
Recovery on NWC $20,000
Net Terminal Cash Flow $35,000

Suppose the project is terminated after 3 years. How does this affect the after-tax cash flows?

Cash Flow from Sale = Sales Proceeds - Taxes Paid

Taxes are based on the difference between sales price and tax basis, where:

Basis = Original Basis - Accumulated Depreciation

Original Basis $240,000
After 3 years $16,800
Sales Price $25,000
Tax On Sale $3,280
Cash Flow $21,720

Projected Net Cash Flows Years
0 1 2 3
2002 2003 2004 2005
Investment Outlays: Long Term Assets
Equipment ($240,000)
Additional NOWC ($20,000)

Operating Cash Flows over Project's Life
Units sold 1250 1250 1250
Sales $250,000 $250,000 $250,000
Costs $125,000 $125,000 $125,000
Net Revenue $125,000 $125,000 $125,000
Depreciation $79,200 $108,000 $36,000
Before-tax-income $45,800 $17,000 $89,000
Taxes (40%) $18,320 $6,800 $35,600
Net Income $27,480 $10,200 $53,400
Depreciation $79,200 $108,000 $36,000
Net Operating CF $106,680 $118,200 $89,400

Salvage Cash Flows
After Tax Salvage Cash Flow from equipment
Return of NOWC

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
Net Cash Flows ($260,000) $106,680 $118,200 $89,400

NPV $81,556.6
IRR 23.8%

Years
Find MIRR 0 1 2 3
Net Cash Flows ($260,000) $106,680 $118,200 $89,400

PV= ($260,000) TV =

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.

MIRR = 17.8%

Find Payback Years
0 1 2 3

Cumulative Cash Flow for Payback ($260,000) ($153,320) ($35,120) $54,280
Cum. CF > 0, hence Payback Year: FALSE FALSE FALSE TRUE
Payback found with Excel function = 0 0 0 2.4

Payback = 2.4

Inflation
Suppose sales prices and costs are expected to inflate at 5% per year after Year 1. How does this affect the analysis?

First, we assume that the original cost of capital was a nominal cost of capital; i.e., it already included an inflation premium.
Therefore, the cash flows should also be nominal cash flows; i.e., they should incorporate inflation. This means that we
must inflate the sales revenues and costs. We also assume that the dollar value of working capital will also inflate. Notice,
however, that the cash flow due to working capital is based on the change in working capital from year to year, not the total
dollar value of working capital. Inflation will not affect depreciation, since it is based on the original cost of the equipment.

5%
Inflation=

Projected Net Cash Flows Years
0 1 2 3
2002 2003 2004 2005
Investment Outlays: Long Term Assets
Equipment ($240,000)

Operating Cash Flows over Project's Life
Units sold 1250 1250 1250
Sales $250,000 $262,500 $275,625
Costs $125,000 $131,250 $137,813
Net Revenue $125,000 $131,250 $137,813

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
Depreciation $79,200 $108,000 $36,000
Before-tax-income $45,800 $23,250 $101,813
Taxes (40%) $18,320 $9,300 $40,725
Net Income $27,480 $13,950 $61,088
Depreciation $79,200 $108,000 $36,000
Net Operating CF $106,680 $121,950 $97,088

Salvage Cash Flows
After Tax Salvage Cash Flow from equipment

Cash Flows Due to Working Capital
Level of net operating working capital $20,000 $21,000 $22,050 $23,153
Cash flow due to NOWC ($20,000) ($1,000) ($1,050) ($1,103)

Net Cash Flows ($260,000) $105,680 $120,900 $95,985

NPV $98,054.1
IRR 26.1%

Notice that the NPV here is higher than the previous NPV when we ignored inflation. As this shows, ignoring inflation will
result in an estimated NPV that is too low-- it is biased downward. To correctly calculate NPV, you must either discount
nominal cash flows with a nominal cost of capital, as we did in this example, or you must use real cash flows (as we did in
the first example) but discount them at the real rate (which we did not do in the first example; we used a nominal rate). It is
usually much easier to forecast nominal cash flows and to estimate a nominal discount rate, so this is the approach we
recommend: use nominal cash flows and a nominal discount rate.

For the rest of the mini case, we will assume that the Federal Reserve has eliminated inflation, and so we don't need to make
any inflation adjustments.

Evaluating Risk: Sensitivity Analysis

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 $81,557 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-expected
outcome is, typically, because sales were lower than expected, costs were higher than expected, 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.

I. Sensitivity of NPV and to Variations in Unit Sales.

Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.
For example, after inputting the values for WACC in cells B205:B209 and the formula =C105 for
NPV in cell C204, select the range B204:C209. Then choose from the menu Data, Table, and enter
D31 (which is the input for WACC) as the Column input. This produces the sensitivity analysis for
WACC as shown below.

% Deviation WACC % Deviation 1st YEAR UNIT SALES
from NPV from Units NPV

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
Base Case WACC 81,557 Base Case Sold $81,557
-30% 7.0% $104,964 -30% 875 $10,235
-15% 8.5% $92,942 -15% 1,063 $45,896
0% 10.0% $81,557 Base Case 0% 1,250 $81,557
15% 11.5% $70,763 15% 1,438 $117,218
30% 13.0% $60,520 30% 1,625 $152,879

% Deviation SALVAGE
from Variable NPV
Base Case Cost $81,557
-30% $17,500.00 $78,483
-15% $21,250.00 $80,020
0% $25,000.00 $81,557
15% $28,750.00 $83,093
30% $32,500.00 $84,630

We summarize the data tables, arranged by sensitivity, and graphed the most sensitive items in the following chart:

Evaluating Risk: Sensitivity Analysis

Sensitivity Analysis

$180,000
$160,000
$140,000
$120,000
WACC
$100,000
NPV

Units Sold
$80,000
Salvage
$60,000
$40,000
$20,000
$0
-40% -30% -20% -10% 0% 10% 20% 30% 40%
Deviation from Base-Case Value

Deviation NPV Deviation from Base Case
from Units
Base Case WACC Sold Salvage
-30% $104,964 $10,235 $78,483
-15% $92,942 $45,896 $80,020
0% $81,557 $81,557 $81,557
15% $70,763 $117,218 $83,093

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
30% $60,520 $152,879 $84,630

Range 44,443 163,113 6,147

Evaluating Risk: Scenario Analysis

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.

Suppose there is a 25% chance of selling only 900 units per year (Worst Case), a 50% chance of selling 1,250
units per year (Base Case), and a 25% chance of selling 1600 units per year (Best Case). In a scenario analyis,
we will find the NPV for each scenario. We could do this simply by entering the value of unit sales 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. 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.

Scenario Analysis

Squared Deviation
Scenario Probability Unit Sales NPV times probability

Best Case 25% 900 $14,989 $1,107,791,372.25
Base Case 50% 1250 $81,556 $0.00
Worst Case 25% 1600 $148,123 $1,107,791,372.25

Expected NPV = sum, prob times NPV $81,556
Standard Deviation = Sq Root of column F sum $47,070
Coefficient of Variation = Std Dev / Expected NPV 0.58

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
4
2006

1250
$250,000
$125,000
$125,000
$16,800
$108,200
$43,280
$64,920
$16,800
$81,720

$15,000
$20,000

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
$116,720

4
$116,720
$98,340
$143,022
$141,991
$500,073

se the MIRR function.

4

$171,000
FALSE
0

ct the analysis?

an inflation premium.
his means that we
ll also inflate. Notice,
ar to year, not the total
cost of the equipment.

4
2006

1250
$289,406
$144,703
$144,703

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
$16,800
$127,903
$51,161
$76,742
$16,800
$93,542

$15,000

$23,153

$131,694

ignoring inflation will
ust either discount
h flows (as we did in
d a nominal rate). It is
the approach we

we don't need to make

expected outcome.
actually turn

project turned
orse than expected,

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
llowing chart:

Units Sold
Salvage

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.
at a time, hence
e probabilities of

Harcourt, Inc. items and derived items copyright © 2002 by Harcourt, Inc.