262  Forecasting Performance
statements to calculate NOPAT, its reconciliation to net income, invested 
capital, and its reconciliation to total funds invested.
6. ROIC and FCF. Use the reorganized financials to build return on invested 
capital, economic profit, and free cash flow. Future free cash flow will be 
the basis of your enterprise valuation.
7. Valuation summary. Create a summary worksheet that sums discounted 
cash flows and converts the value of operations into equity value. The 
valuation summary includes the value of operations, value of nonoper-
ating assets, value of nonequity claims, and resulting equity value.
Well-built valuation models have certain characteristics. First, original data 
and user input are collected in only a few places. For instance, limit original 
data and user input to just three worksheets: raw data (worksheet 1), forecasts 
(worksheet 3), and market data (worksheet 4). To provide additional clarity, 
denote raw data and user input in a different color from calculations. Second, 
whenever possible, a given worksheet should feed into the next worksheet. For-
mulas should not bounce from sheet to sheet without clear direction.2 Raw data 
should feed into integrated financials, which in turn should feed into ROIC and 
FCF. Third, unless specified as data input, numbers should never be hard-coded 
into a formula. Hard-coded numbers are easily forgotten as the spreadsheet 
grows in complexity. Finally, use formulas that come built into the spreadsheet 
software sparingly, such as the net present value (NPV) formula. Built-in for-
mulas can obscure the model’s logic and make auditing results difficult.
Mechanics of Forecasting
The enterprise discounted-cash-flow (DCF) valuation model relies on a fore-
cast of free cash flow (FCF). However, as noted at the beginning of this chapter, 
FCF forecasts should be created indirectly by first forecasting the income state-
ment, balance sheet, and statement of retained earnings. Compute forecasts of 
free cash flow in the same way as when analyzing historical performance. (A 
well-built spreadsheet will use the same formulas for historical and forecast 
ROIC and FCF without any modification.)
We break the forecasting process into six steps:
1. Prepare and analyze historical financials. Before forecasting future finan-
cials, you must build and analyze historical financials. A robust analysis 
will place your forecasts in the appropriate context.
2. Build the revenue forecast. Almost every line item will rely directly or indi-
rectly on revenues. Estimate future revenues by using either a top-down 
2 Data should always flow in one direction and never loop back to create a circular reference. Circular 
references will prevent your spreadsheet from calculating results accurately.

Mechanics of Forecasting  263
(market-based) or a bottom-up (customer-based) approach. Forecasts 
should be consistent with evidence on growth.
3. Forecast the income statement. Use the appropriate economic drivers to 
forecast operating expenses, depreciation, nonoperating income, inter-
est expense, and reported taxes.
4. Forecast the balance sheet: invested capital and nonoperating assets. On the 
balance sheet, forecast operating working capital, net property, plant, 
and equipment, goodwill, and nonoperating assets.
5. Reconcile the balance sheet with investor funds. Complete the balance sheet 
by computing retained earnings and forecasting other equity accounts. 
Use excess cash and/or new debt to balance the balance sheet.
6. Calculate ROIC and FCF. Calculate ROIC on future financial statements 
to ensure your forecasts are consistent with economic principles, indus-
try dynamics, and the company’s ability to compete. To complete the 
forecast, calculate free cash flow as the basis for valuation. Future FCF 
should be calculated the same way as historical FCF.
Give extra emphasis to your revenue forecast. Almost every line item in 
the spreadsheet will be either directly or indirectly driven by revenues, so you 
should devote enough time to arrive at a good revenue forecast, especially for 
rapidly growing businesses.
Step 1: Prepare and Analyze Historical Financials
Before starting to build a forecast, input the company’s historical financials 
into a spreadsheet. To do this, you can rely on data from a professional service, 
such as Bloomberg, Capital IQ, Compustat, or Thomson ONE, or you can use 
financial statements directly from the company’s filings.
Professional services offer the benefit of standardized data (i.e., financial data 
formatted into a set number of categories). Since data items do not change across 
companies, a single spreadsheet can quickly analyze any company. However, 
using a standardized data set carries a cost. Many of the specified categories 
aggregate important items, hiding critical information. For instance, Compustat 
groups “advances to sales staff” (an operating asset) and “pension and other 
special funds” (a nonoperating asset) into a single category titled “other assets.” 
Because of this, models based solely on preformatted data can lead to meaning-
ful errors in the estimation of value drivers, and hence to poor valuations.
Alternatively, you can build a model using financials from the company’s 
annual report. To use raw data, however, you must dig. Often, companies ag-
gregate critical information to simplify their financial statements. Consider, for 
instance, the financial data for Honeywell presented in Exhibit 13.2. On Hon-
eywell’s reported balance sheet, the company consolidates many items into 
the account titled “accrued liabilities.” In the notes that follow the company’s