case study of accounting

8.2 Determining Free Cash Flow and NPV 245

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.