Table of Contents
- Historical data
- Common issues when inputting historical income statement data
- Cost of goods sold
- Operating expenses
- Depreciation and amortization
- Stock-based compensation expense
- Forecasting interest expense
- Interest income
- Other non-operating items
- Shares outstanding and earnings per share
Forecasting the income statement is a key part of building a 3-statement model because it drives much of the balance sheet and cash flow statement forecasts. In this guide, we address the common approaches to forecasting the major line items in the income statement in the context of an integrated 3-statement modeling exercise.
Before any forecasting can begin, we start by inputting historical results. The process involves either manual data entry from the 10K or press release, or using an Excel plugin through financial data providers such as to drop historical data directly into Excel.
Here is Apple’s 2016 income statement:
Common issues when inputting historical income statement data
When inputting historical income statement data, several issues are usually encountered:
Deciding the level of revenue (sales) detail
Some companies report segment- or product-level revenue and operating detail in footnotes (which roll up into the consolidated income statement). For example, while Apple provides a consolidated “net sales” figure in the income statement, the footnotes provide sales by product (iPhone, iPad, Apple Watch, etc.).
If it’s important that the final model includes a scenario analysis — for example, what if iPhone unit sales are better than expected, but the iPhone average selling price is worse than expected? — a detailed historical segment breakout is useful to provide a foundation for forecasts. Otherwise, relying on the net sales line on the income statement is sufficient.
Line item classification
Not all companies classify their operating results the same way. Some companies will aggregate all operating expenses into one line, while others will break them into several line items. If our model will be used to compare performance across other firms, the classifications need to be apples-to-apples and often require us to make judgments on how to classify line items and whether to hunt for more detailed breakdowns in the financial footnotes.
For example, notice that Apple’s 2016 income statement above contains a line called “Other income/(expense), net” of $1,348 million. This line aggregates interest expense, interest income and other non-operating expenses, as we can see in Apple’s 10K footnotes:
Since 3-statement financial models need to forecast future interest expense based on debt levels and interest income based on future cash levels, we needed to identify and use the more detailed breakout provided in the footnotes.
Companies prepare their historical income statement data in line with US GAAP or IFRS. That means income statements will not contain financial metrics like EBITDA and Non GAAP operating income, which ignore certain items like stock-based compensation. As a result, we often have to dig in footnotes and other financial statements to extract the data needed to present income statement data in a way that’s useful for analysis.
Putting it all together
Below is an example of how to input Apple’s historical results into a financial model:
If you compare it with Apple’s actual income statement (shown previously) you’ll notice several differences. In the model:
- Other income is broken out to explicitly show interest expense and interest income.
- Depreciation and amortization as well as stock based compensation is explicitly identified in order to arrive at EBITDA.
- Growth rates and margins are calculated.
Notice the adherence to several financial modeling best practices including:
- Formulas are colored black and inputs are blue.
- The model presents data from left to right (unfortunately companies report results from right to left).
- Decimal places are consistent (two for per-share data, none in Apple’s case for operating results).
- Negative numbers are in parentheses.
- Expenses are all negative (not all models follow this convention — the key here is consistency).
Once the historical data is inputted into the model, forecasts can be made. Before diving in, let’s establish a few realities of forecasting.
Effective forecasting has very little to do with modeling
While our focus in this article is to give you guidance on the mechanics of effective modeling, a much more important facet of forecasting is something this guide cannot provide: A deep understanding of the business and industry in question. To forecast a company’s revenue, an analyst must have an understanding of the company’s business model, key customers, addressable market, competitive position and sales strategy. Garbage in = garbage out, as the old saying goes.
Your role will determine how much time you spend on getting the assumptions right
Most investment banking analysts spend very little time conducting the due diligence required to arrive at their own assumptions. Instead, they rely on equity research and management estimates to provide a “management case” and “street case” for future performance. Then the analyst ideally builds other cases that should show what would happen if the street and management cases don’t materialize. That’s why a lot of peopleas all style and no substance. On the other hand, a buy side or private equity analyst will spend far more time understanding the businesses they are considering as an investment. If they get the assumptions wrong, after all, their returns will suffer.
Messy models are useless
Assumptions are the most important part of getting a model “right.” But a model that is messy, error-prone and is not integrated will never be a useful tool despite great underlying assumptions.