5 min read · Sep 13, 2022
--
The purpose of a 3-statement model (i.e. an integrated financial statement model) is to forecast or project the financial position of a company as a whole. It contains the three types of financial statements — balance sheet, income, and cash flow statement — which are linked together. Therefore, if there is a change in one financial statement, the other financial statements should adjust accordingly. From a financial modeling perspective, these financial statements should be interlinked.
To understand the high-level steps required to build a 3-statement model, it is important to have the iterations setting in Excel turned off before starting.
- 3-statement modeling links the income statement, balance sheet, and cash flow statement together for forecasting purposes.
- There are ten steps involved in building a 3-statement model.
- To start 3-statement modeling we need to have the iterations setting in Excel turned off.
In building an income statement (excluding interest), we start with our actual (historical) figures. From there, we build the forecasts (e.g. years 1 to 10). Further, we build these forecasts from assumptions for all line items — i.e. revenue, operating costs, tax, etc. — except for interest (income and expense).
The ten steps to building a 3-statement model are:
- Input the historical data: start with inputting historical or actual data for the income statement and balance sheet
- Calculate ratios and statistics decide on forecast assumptions: from the historical data, calculate the ratios and statistics
- Decide on forecast assumptions: Use the calculated statistics to create assumptions into the future i.e. sales may go up by 5% in the future in the forecast period
- Build forecast income statement except for interest: we now have everything we require to actually start building the forecast figures and we start by building the income statement — which will include the sales going up by 5%
- Build the forecast balance sheet except for cash, revolver, and debt: after building the forecast income statement, we then build the forecast balance sheet and we skip a couple of lines — cash, revolver, and debt — to do at the end of the modeling
- Build the cash flow statement using the rules of cash: after building the forecast balance sheet in the previous stage, we build the cash flow statement using the rules of cash
- Plug cash into the balance sheet from the cash flow statement: the only line items that have not been filled in are all interest-related. We know that cash, revolver, and debt all involve interest expense or interest income. We can take cash from the cash flow statement and put that into the balance sheet and thus one of the missing items is dealt with
- Build debt and interest calculations: after the aforesaid step is completed, we build the debt and interest calculations. We can plug the revolver and long-term debt into the balance sheet. So now, the balance sheet is done
- Balance the balance sheet: If we plug in all of these interest-related items, other figures are going to change. The balance sheet, which is completed, will change the cash flow statement — which will then change the cash number on the balance sheet. So, the balance sheet should balance. Having stated the above, note that the only item now missing is, interest. Plug the revolver and long-term debt into the balance sheet
- Circular references: So, now we need to link the interest into the income statement and deal with any circular reference that may arise. The interest will lead to some changes in the net income, which will, in turn, affect the cash flow statement and cash on the balance sheet. Assuming we built our model correctly, this should all balance out
In the example below, to build our income statement for a company, we start with our actual i.e. historical figures. From there we build the forecasts — for 10 years. We build them from assumptions for all line items — i.e., revenue, operating costs, tax, etc. except for interest (income and expense). Interest can often lead to a circular reference — so we tend to do this at the end of our model.
It is very common to forecast the whole of Year 1(P)’s income statement, before then copying year 1 to subsequent years. Before copying over we do a few checks on Year 1.
First, we do a sense, structure, and stress check of the Year 1 forecast, — for this, we look back at the historical data and compare. For example, in Year 1 revenues were US$8,800.2 and in Year 3 (H), they were US$7,652.3. So, when compared these two figures don’t look too outlandish i.e. they look sensible.
We then do a structure check to make sure the Year 1 (P) revenues are linked to Year 1 (P) assumptions. Lastly, we do a stress test — for example, if we increase our revenues by 10,000, then what might we expect to happen to net income? We should expect the net income to increase by 10,000 less the tax rate. If we put this increase into the revenues and if the net income changes, then we move on to the next stress test.
You might have noticed that the interest lines (interest income and interest expense) have not been completed at this stage. The final step to complete the income statement is to deal with the circular reference. Once done, the 3-statement model can be checked and hopefully successfully linked and working together.
Before sharing your model with your colleagues, it is important to do a final check of the following:
Download the accompanying Excel exercise sheets to practice your modeling skills.
Take our online financial modeling course to learn the perfect way to build models.
Complete Investment Banking Course Linking Three Financial Statements What Makes a Good Financial Model? DCF Modeling Guide