How to Build an Income Statement in a Financial Model
Once the bones of an income statement have been laid out, they may be included into a robust financial model to make projections about the company’s future success.
Step 1
To begin, use the Excel income statement template to record past financial results for whatever time frame that you have access to. If you want to tell the difference between hard-coded and computed data, you need use a certain format when entering historical data. To refresh your memory, blue is often reserved for hard-coded input whereas black is used for calculated or linked data in this type of format.
By doing so, both the user and the reader will be aware of which cells contain equations and thus should not be altered and which cells are for input only. However, consistency in use is essential to minimize misunderstanding, so use whichever approach you choose for formatting.
Step 2
The next step is to conduct trend analysis on the existing historical data in order to develop forecasting drivers and assumptions. Using data like sales growth rates and cost of goods sold percentages, you may make educated predictions about the future. Study up on various methods of prediction.
Step 3
Finally, anticipate future values for each line item in the income statement using the drivers and assumptions you established in step one. You may use your predictions for individual line items to determine individual subtotals. Gross profit forecasting, for instance, is more accurate if one first predicts Cost of Goods Sold and Revenue, and then subtracts these two figures.
Income Statement Template
Please download free income statement template to produce a year-over-year income statement with your own data.
What are Common Drivers for Each Income Statement Item?
| Line Item | Driver or Assumption |
|---|---|
| Sales Revenue | Selected growth percentage, pegged growth percentage based on index (such as GDP) |
| Cost of Goods Sold | Percentage of sales, Fixed dollar value |
| SG&A | Percentage of sales, fixed amount, trend, fixed dollar value |
| Depreciation and Amortization | Depreciation Schedule |
| Interest Expense | Debt Schedule |
| Income Tax | Percentage of pre-tax income (effective tax rate) |
Commonly used though they may be, the drivers discussed here should be seen as little more than suggestions. Intuition is needed for some times to select the right driver or assumption to apply. A company may generate no income, as one example. That means it’s impossible to calculate COGS based on the percentage of sales drivers. Instead, the analyst might have to make estimates about future COGS by looking at historical trends.
Financial modeling and accounting both rely on the same fundamental statements. There are three financial statements: the Income Statement, Balance Sheet, and Cash Flow Statement. All of the other statements’ values will be affected by the ones you choose in this list, because they all form part of the same financial model.
