8.2 Determining Free Cash Flow and NPV 283
USINg exCel
Capital Budgeting
Using a Spreadsheet
program
Capital budgeting forecasts and analysis are most easily performed in a spreadsheet program.
Here we highlight a few best practices when developing your own capital budgets.
Create a project Dashboard
All capital budgeting analyses begin with a set of assumptions regarding future revenues and
costs associated with the investment. Centralize these assumptions within your spreadsheet in
a project dashboard so they are easy to locate, review, and potentially modify. Here we show an
example for the HomeNet project.
Color Code for Clarity
In spreadsheet models, use a blue font color to distinguish numerical assumptions from
formulas. For example, HomeNet’s revenue and cost estimates are set to a numerical value in
year 1, whereas estimates in later years are set to equal to the year 1 estimates. It is therefore clear
which cells contain the main assumptions, should we wish to change them at a later date.
maintain Flexibility
In the HomeNet dashboard, note that we state all assumptions on an annual basis even if we
expect them to remain constant. For example, we specify HomeNet’s unit volume and average
sale price for each year. We can then calculate HomeNet revenues each year based on the
corresponding annual assumptions. Doing so provides flexibility if we later determine that
HomeNet’s adoption rate might vary over time or if we expect prices to follow a trend, as in
Example 8.3.
Never hardcode
So that your assumptions are clear and easy to modify, reference any numerical values you need
to develop your projections in the project dashboard. Never “hardcode,” or enter numerical val-
ues directly into formulas. For example, in the computation of taxes in cell E34 below, we use the
formula “= -E21*E33< rather than “= -0.40*E33<. While the latter formula would compute
the same answer, because the tax rate is hardcoded it would be difficult to update the model if
the forecast for the tax rate were to change.
M08_BERK0160_04_GE_C08.indd 283 8/20/16 1:09 PM
Berk, J., & DeMarzo, P. (2016). Corporate finance, global edition. Retrieved from http://ebookcentral.proquest.com
Created from uql on 2019-09-18 18:49:09.
C
op
yr
ig
ht
©
2
01
6.
P
ea
rs
on
E
du
ca
tio
n
Li
m
ite
d.
A
ll
rig
ht
s
re
se
rv
ed