Ross, Westerfield, and Jaffe's Spreadsheet Master
Corporate Finance, 9th edition
by Brad Jordan and Joe Smolira
Version 9.0
Chapter 6
In these spreadsheets, you will learn how to u
VDB
SLN
Solver
The following conventions are used in these sp
1) Given data in blue
2) Calculations in red
NOTE: Some functions used in these spreadsheets ma
the "Analysis ToolPak" or "Solver Add-In" be installed
To install these, click on the Office button
then "Excel Options," "Add-Ins" and select
"Go." Check "Analysis ToolPak" and
"Solver Add-In," then click "OK."
dsheet Master
l learn how to use the following Excel functions:
used in these spreadsheets:
e spreadsheets may require that
dd-In" be installed in Excel.
Chapter 6 - Section 2
The Baldwin Company: An Example
Because capital budgeting requires numerous repetitive cash flows, it is an ideal application f
should do few or no calculations on your own, but rather let Excel do the calculations for you.
projections for the project:
Units sold per year:
Price per unit for Year 1:
Price increase per year:
Inflation rate:
Tax rate:
Unit production cost for Year 1:
Increase in unit cost per year:
NWC to start project:
NWC for subsequent years:
Depreciation rate:
Cost of machine
Cost of warehouse:
Pretax salvage value:
$
$
$
$
$
Year 1
5,000
20.00
2%
5%
34%
10.00
10%
10,000
10%
20.0%
100,000
150,000
30,000
Year 2
8,000
32.0%
We will start off with some preliminary work, including the depreciation each year, sales price
Depreciation
Accumulated depreciation
Adjusted basis of machine
Price per unit
Sales revenue
Cost per unit
Operating costs
Year 1
20,000 $
20,000
80,000
20.00
100,000
10.00
50,000
Year 2
32,000
52,000
48,000
20.40
163,200
11.00
88,000
The change in net working capital for each year is the beginning net working capital for each
change in net working capital each year is:
Net working capital
Beginning NWC
End of year NWC
NWC cash flow
$
$
10,000 $
10,000
- $
10,000
16,320
(6,320)
The machine will have a salvage value at the end of the project, but we are concerned with th
Pretax salvage value
Taxes on sale
Aftertax salvage value
$
$
30,000
8,228
21,772
Now we can calculate the pro forma income statement for each year (Table 6.1), which will be
Sales revenue
Operating costs
Depreciation
Income before taxes
Taxes at 34 percent
Net income
$
$
100,000 $
50,000
20,000
30,000 $
10,200
19,800 $
163,200
88,000
32,000
43,200
14,688
28,512
With this, the incremental cash flows each year, NPV for different interest rates, and IRR for th
Year 0
Sales revenue
Operating costs
Taxes
Cash flow from operations
Bowling ball machine
Warehouse
Net working capital
Total cash flow of project
Year 1
100,000
50,000
10,200
39,800
(100,000)
(150,000)
(10,000)
(260,000) $
39,800
NPV
4%
10%
15%
15.68%
20%
$
$
$
$
$
123,643
51,590
5,473
0
(31,350)
A Note about Depreciation
There are actually six MACRS schedules: three-, five-, seven-, 10-, 15-, and 20-year schedules
double declining balance method, and switching to straight-line depreciation when it is more
(200%) when calculating the double declining balance depreciation amount, while the 15- and
can be used to construct a MACRS table. Below, we have constructed a MACRS table with all
Equipment Lif
Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
3
33.33%
44.44%
14.81%
7.41%
5
20.00%
32.00%
19.20%
11.52%
11.52%
5.76%
RWJ Excel Tip
To construct the MACRS table, we used the variable declining balance (VDB) function. Constru
will see what we entered for the second year of the three-year MACRS schedule.
Cost is the cost of the equipment. In this case, we entered one in order to get the answers as
zero. Life is the life of the asset. Since we have a table here, we entered the column as a floa
down the table was well as across. The Start_period is the starting period for which we want t
subtracted 1/2. To calculate the End_period, we used the MIN function. This function will retur
we could have taken the next year minus one-half, but this would not work for the last year. N
year. So, for the first year, we eliminated the MIN function. Finally, the Factor is not shown on
factor of two for the three-, five-, seven-, and 10-year schedules and a factor of 1.5 for the 15
Finally, note that the MACRS schedule we calculated can vary slightly from the table presente
is the schedule we used in the textbook. However, you are allowed to calculate the schedule
table above, not the table in the textbook (or the table published by the IRS!). In the future, w
ws, it is an ideal application for Excel. When doing a capital budgeting problem, as in most Excel uses,
l do the calculations for you. We will begin with the Baldwin Company project. We have the following
Year 3
12,000
19.2%
Year 4
10,000
11.5%
Year 5
6,000
11.5%
ciation each year, sales price, and unit costs:
Year 3
19,200 $
71,200
28,800
20.81
249,696
12.10
145,200
Year 4
11,500 $
82,700
17,300
21.22
212,242
13.31
133,100
Year 5
11,500
94,200
5,800
21.65
129,892
14.64
87,846
net working capital for each year minus the net working capital investment at the end of the year. So,
$
$
16,320 $
24,970
(8,650) $
24,970 $
21,224
3,745 $
21,224
21,224
but we are concerned with the aftertax salvage value, which is:
year (Table 6.1), which will be:
$
$
$
249,696 $
145,200
19,200
85,296 $
29,001
56,295 $
212,242 $
133,100
11,500
67,642 $
22,998
44,643 $
129,892
87,846
11,500
30,546
10,386
20,160
interest rates, and IRR for the project are (Table 6.4):
Year 2
163,200 $
88,000
14,688
60,512 $
Year 3
249,696 $
145,200
29,001
75,495 $
Year 4
212,242 $
133,100
22,998
56,143 $
(6,320)
54,192 $
(8,650)
66,846 $
3,745
59,889 $
Year 5
129,892
87,846
10,386
31,660
21,772
150,000
21,224
224,656
, 15-, and 20-year schedules. The MACRS schedule is calculated using the depreciation according to th
depreciation when it is more advantageous. The three-, five-, seven-, and 10-year schedules use a facto
on amount, while the 15- and 20-year schedules use a factor of 1.5 (150%). Excel has a function, VDB,
cted a MACRS table with all six schedules.
Equipment Life (Years)
7
14.29%
24.49%
17.49%
12.49%
8.92%
8.92%
8.92%
4.46%
10
10.00%
18.00%
14.40%
11.52%
9.22%
7.37%
6.55%
6.55%
6.55%
6.55%
3.28%
15
5.00%
9.50%
8.55%
7.70%
6.93%
6.23%
5.90%
5.90%
6.02%
6.02%
6.02%
6.02%
6.02%
6.02%
6.02%
3.01%
20
3.75%
7.22%
6.68%
6.18%
5.71%
5.28%
4.89%
4.52%
4.46%
4.46%
4.46%
4.54%
4.54%
4.54%
4.54%
4.54%
4.54%
4.54%
4.54%
4.54%
2.27%
ance (VDB) function. Constructing the MACRS table is tricky because of the half-year convention. Below
ACRS schedule.
order to get the answers as a percentage rather than a dollar amount. Salvage is the salvage value, w
entered the column as a floating input and locked the row. This allows us to copy and paste the formula
g period for which we want to calculate the depreciation. With the half-year convention, we used the y
ction. This function will return the lesser of the next year minus one-half, or the life of the asset. In mo
d not work for the last year. Notice that this MIN function will not work for the first year since there is no
y, the Factor is not shown on the picture above since Excel scrolls through the inputs in this case. We u
and a factor of 1.5 for the 15- and 20-year schedules.
ghtly from the table presented in the textbook. The reason is that the IRS publishes a MACRS schedule,
ed to calculate the schedule on your own based on the rules outlined by the IRS. If you do so, you will g
by the IRS!). In the future, we will use the table in the textbook for our calculations.
g problem, as in most Excel uses, you
y project. We have the following
stment at the end of the year. So, the
g the depreciation according to the
and 10-year schedules use a factor of 2
150%). Excel has a function, VDB, which
of the half-year convention. Below you
nt. Salvage is the salvage value, which is
s us to copy and paste the formula further
alf-year convention, we used the year and
half, or the life of the asset. In most years
k for the first year since there is no prior
ough the inputs in this case. We used a
e IRS publishes a MACRS schedule, which
by the IRS. If you do so, you will get the
our calculations.
Chapter 6 - Section 3
Inflation and Capital Budgeting
Inflation should always be considered in any long-term project. As long as inflation is correctly
projected proposed by Altshuler, Inc.
Example 6.10: Real and Nominal NPV
Altshuler, Inc. has generated the following forecast for a capital budgeting project. David Alts
Whose approach is correct?
Capital expenditures:
Revenues (real terms):
Cash expenses (real terms):
Depreciation:
Inflation rate:
Nominal rate:
Real rate:
Tax rate:
Year 0
1,210
Year 1
$
1,900
950
605
10.0%
15.5%
5.0%
40.0%
RWJ Excel Tip
To calculate the depreciation each year for straight-line depreciation, we can divide the initia
we have done here. The SLN we used in this case looks like this:
The inputs are Cost, which is the initial cost, Salvage, which is the salvage value, and Life, wh
by the life of the equipment in the cell rather than use this particular function, but it is availab
With these projections, we can generate the following nominal cash flows and NPV:
Capital expenditures
Revenues
Expenses
Depreciation
Taxable income
Taxes (40%)
Income after taxes
Depreciation
Cash flow
NPV @ 15.5%
Nominal cash flows
Year 0
$
(1,210)
Year 1
$
$
$
$
2,090
1,045
605
440
176
264
605
869
$268.00
We can also use real cash flows, which will be:
Real cash flows
Year 0
$
(1,210)
Capital expenditures
Revenues
Expenses
Depreciation
Taxable income
Taxes (0%)
Income after taxes
Depreciation
Cash flow
NPV @ 5%
Year 1
$
$
$
$
1,900
950
550
400
160
240
550
790
$268.00
When dealing with any cash flows, it is irrelevant whether you use real cash flows with the rea
value will always be the same.
ong as inflation is correctly handled, the NPV of the project will be the same. For example, consider the
dgeting project. David Altshuler prefers to work in nominal terms, while Stuart Weiss prefers real cash fl
Year 2
$
2,000
1,000
605
n, we can divide the initial cost by the life of the equipment, or we can use the built-in Excel function S
alvage value, and Life, which is the life of the asset. In general, we usually find it easier just to divide t
ar function, but it is available if you prefer.
h flows and NPV:
Year 2
$
$
$
$
2,420
1,210
605
605
242
363
605
968
Year 2
$
$
$
$
2,000
1,000
500
500
200
300
500
800
real cash flows with the real interest rate or nominal cash flows with the nominal interest rate, the pres
e same. For example, consider the
ile Stuart Weiss prefers real cash flows.
an use the built-in Excel function SLN as
usually find it easier just to divide the cost
the nominal interest rate, the present
Chapter 6 - Section 5
Investments of Unequal Lives: The Equivalent Annual Cost
To find the equivalent annual cost (EAC), we find the net present value of the project, then fin
Suppose we have two different options for a pollution control system, a filtration system or a
Equipment
Operating cost
Life (years)
Filtration
Precipitation
system
system
$
1,100,000 $
1,900,000
$
60,000 $
10,000
5
8
Discount rate
Tax rate
12%
34%
We can calculate the NPV of each project as:
Operating cost
Depreciation
EBIT
Tax
Net income
Income Statements
Filtration
Precipitation
system
system
$
60,000 $
10,000
220,000
237,500
$
(280,000) $
(247,500)
(95,200)
(84,150)
$
(184,800) $
(163,350)
So, using the bottom-up approach, the OCF for each alternative is:
OCF
35,200 $
74,150
Now, we can calculate the NPV of each project:
NPV
(973,112) $
(1,531,650)
Using the PMT function to find the EAC, we get:
EAC
($269,950.71)
($308,325.40)
In the final analysis, we should choose the system that is the least expensive, which is the filt
Setting a Bid Price: A Capital Budgeting Extension
Suppose the company you work for is entering a competitive bidding process for a new projec
the project? We know that you would not want to lose money on the project from a financial p
zero NPV, we make exactly the required return on the project. So, the minimum bid price we s
flows of the project such as the initial investment, salvage value, net working capital, etc., we
NPV. While doing this by hand is possible, it can often result in tedious calculations. Fortunate
We are bidding on the following project. The contract will last for four years, and the equipme
price we could submit?
Equipment
Pretax salvage value
Units per year
Price per unit
VC as a percentage of sales
Fixed costs
MACRS Year 1
MACRS Year 2
MACRS Year 3
MACRS Year 4
Immediate NWC
Tax rate
Required return
$
$
$
$
3,300,000
75,000
125,000
25.00
45%
425,000
33.30%
44.40%
14.80%
7.40%
80,000
35%
10%
We entered a price in the appropriate cell above. As we will show later, it does not really matt
project with our hypothetical price. This will be:
Year
Revenues
Variable costs
Fixed costs
Depreciation
EBIT
Taxes (35%)
Net income
+ Depreciation
OCF
$
$
$
1
3,125,000
1,406,250
425,000
1,098,900
194,850
68,198
126,653
1,098,900
1,225,553
Pro Forma Income Statements
2
3
$
3,125,000 $
3,125,000
1,406,250
1,406,250
425,000
425,000
1,465,200
488,400
$
(171,450) $
805,350
(60,008)
281,873
$
(111,443) $
523,478
1,465,200
488,400
$
1,353,758 $
1,011,878
To find the aftertax salvage value, we need to calculate the taxes. We get:
Pretax salvage value:
Taxes on sale:
Aftertax salvage value:
$
$
75,000.00
(26,250.00)
48,750.00
The total cash flows for each year of the project are:
Year
OCF
Change in NWC
Capital spending
Total cash flow
0
$
$
$
$
(80,000)
(3,300,000)
(3,380,000) $
Project Cash Flows
1
2
1,225,553 $
1,353,758
1,225,553 $
1,353,758
Finally, the NPV of the project at this unit price is:
NPV:
333,871.80
The minimum bid price is the price at which the NPV of the project is zero. We can use Solver
RWJ Excel Tip
To use Solver, go to the Data tab, then click Solver. The inputs we used for this problem are:
As you see, with Solver you first enter the target cell you would like to set to a specific value,
NPV, we chose to set the NPV cell equal to a value of zero. Next, we select the cell we would l
we changed the unit price cell. This is why the original value we entered for the unit price is i
we used Solver, we restored the original value. On the next worksheet, you can see the answ
Minimum bid price:
22.64
We restored the original unit price so you could use Solver on this problem for practice.
NOTE: There is a bug in Solver that will occur occasionally. In some cases, Solver will not laun
unexpected internal error or available memory was exhausted" pop up. In this case, the solut
1) Go to the Office button on the top left, click Excel options, choose Add-Ins, select Exc
2) Uncheck the Solver add-in and click OK.
3) Go to the Office button on the top left, click Excel options, choose Add-Ins, select Exc
a repeat of Step 1.
4) Check the Solver add-in and select OK.
alent Annual Cost Method
lue of the project, then find the annuity that represents the annual cost based on the life of the projec
m, a filtration system or a precipitation system. The relevant numbers for each alternative are:
expensive, which is the filtration system.
ng process for a new project. How do you determine the minimum bid price you would be willing to put
e project from a financial perspective. From our capital budgeting discussion, we know that if the proje
he minimum bid price we should submit is the price that results in a zero NPV. Since we know all of the
et working capital, etc., we can set up the cash flows we know and back into the price the results in a z
ous calculations. Fortunately, Excel has a built-in function that will make the process much easier.
ur years, and the equipment will be depreciated on a three-year MACRS schedule. What is the minimum
ter, it does not really matter what price we entered. Next, we need to calculate the cash flows and NPV
ements
We get:
$
$
$
4
3,125,000
1,406,250
425,000
244,200
1,049,550
367,343
682,208
244,200
926,408
ash Flows
$
3
1,011,878 $
1,011,878 $
4
926,408
80,000
48,750
1,055,158
is zero. We can use Solver to find this unit price (and much more.)
sed for this problem are:
to set to a specific value, in this case, the NPV cell. Since the lowest bid price is the price that results
e select the cell we would like to change in order to set the target cell equal to the value we chose. In t
tered for the unit price is irrelevant: Solver will change the value when it solves the problem. Note that
eet, you can see the answer report generated by Solver. In this case, the bid price that results in a zero
roblem for practice.
cases, Solver will not launch, or if you try to save one or more of the reports, you may see "Solver: An
up. In this case, the solution is to uninstall Solver and re-install it. To do this:
choose Add-Ins, select Excel Add-Ins in the pulldown menu near the bottom of the box, and click on Go
choose Add-Ins, select Excel Add-Ins in the pulldown menu near the bottom of the box, and click on Go
ost based on the life of the project.
s for each alternative are:
d price you would be willing to put in for
cussion, we know that if the project has a
zero NPV. Since we know all of the cash
ack into the price the results in a zero
ake the process much easier.
CRS schedule. What is the minimum bid
o calculate the cash flows and NPV for the
bid price is the price that results in a zero
l equal to the value we chose. In this case,
en it solves the problem. Note that after
the bid price that results in a zero NPV is:
e reports, you may see "Solver: An
o do this:
bottom of the box, and click on Go.
bottom of the box, and click on Go. This is
Solver
22.636335
Page 33
Microsoft Excel 12.0 Answer Report
Worksheet: [CF Chapter 06 Excel Master.xlsx]Section 6.5
Report Created: 6/11/2009 2:43:29 PM
Target Cell (Value Of)
Cell
Name
$C$92 NPV: Project Cash Flows
Original Value Final Value
$ 333,871.80 $
-
Adjustable Cells
Cell
Original Value Final Value
Name
Price per unit Precipitation
$D$48 system
Constraints
NONE
25.00
22.64
Chapter 10 - Master it!
For this Master It! assignment, refer to the Goodweek Tires, Inc. case at the end of Chap
as the price, variable cost, etc. on the next page. For this project, answer the following q
a.
What is the profitability index of the project?
b.
What is the IRR of the project?
c.
What is the NPV of the project?
d.
At what OEM price would Goodweek Tires be indifferent to accepting the project? Assum
e.
At what level of variable costs per unit would Goodweek Tires be indifferent to accepting
Inc. case at the end of Chapter 6. For your convenience, we have entered the relevant values in the c
roject, answer the following questions:
accepting the project? Assume the replacement market price is constant.
es be indifferent to accepting the project?
ntered the relevant values in the case such
Master it! Solution
Research and development
Test marketing cost
Initial equipment cost
Equipment salvage value
Year 1 depreciation
Year 2 depreciation
Year 3 depreciation
Year 4 depreciation
OEM market:
Price
Variable cost
Automobile production
Growth rate
Market share
Replacement market:
Price
Variable cost
Market sales
Growth rate
Market share
Price increase above inflation
VC increase above inflation
Marketing and general costs
Tax rate
Inflation rate
Required return
Initial NWC
NWC percentage of sales
$
$
$
$
10,000,000
5,000,000
140,000,000
54,000,000
14.30%
24.50%
17.50%
12.50%
$
$
38
22
5,600,000
2.50%
11.00%
$
$
59
22
14,000,000
2.00%
8.00%
1%
1%
26,000,000
40.00%
3.25%
15.90%
9,000,000
15%
Nominal price increase
Nominal VC increase
Year 0
OEM:
Automobiles sold
Tires for automobiles sold
SuperTread tires sold
Price
Replacement market:
Total tires sold in market
SuperTread tires sold
Price
Revenue:
OEM market
Replacement market
Total
Variable costs:
OEM market
Replacement market
Total
Revenue
Variable costs
Year 1
Year 2
Year 3
Marketing and general costs
Depreciation
EBT
Tax
Net income
OCF
New working capital:
Beginning
Ending
NWC cash flow
Book value of equipment
Aftertax salvage value:
Market value
Taxes
Total
Year 0
Operating cash flow
Capital spending
Net working capital
Total cash flows
NPV
IRR
Profitability index
Year 1
Year 2
Year 3
Year 4
Year 4
