3 Statement Model
A free guide on three statement models.
A three-statement model is a dynamically integrated financial model developed by linking together a company's three primary statements. This is one of the most important models as it serves as a base for other complex models such as the Leveraged Buyout (LBO) Model or the Discounted Cash Flow (DCF) Model.
It uses the numbers from the company's historical financial statements as base inputs, makes some assumptions about the company's operations in the future, and forecasts the figures in the three statements based on these inputs and assumptions.
The purpose of this model is to project what the company's financial health might look like if certain decisions are made or if certain assumptions materialize in the future.
In this article, we will explain what the three statements are, what a three-statement model is, and how to build it.
The three financial statements are the income statement, the balance sheet, and the cash flow statement. The information in each of these statements is linked to the information in the other two statements.
- Income statement:
The income statement presents the earnings and the profitability of a company for a given period, such as a year or a quarter. It is also known as "statement of operations," or "profit and loss account," or "P&L account." It is generally prepared using the accrual system of accounting. It starts with the revenue in the first line, and after deducting various direct and indirect expenses, arrives at the company's net income. It is often the first place an analyst or investor looks at to gauge the performance of a business.
- Balance sheet:
Also known as the "statement of financial position," the company's balance sheet illustrates its financial position at a particular point in time. Unlike the income statement, it is not drawn for a period. Instead, it shows the company's sources of funds and its utilization of those funds at a particular point in time. The total funds raised from various sources (i.e., capital and liabilities) must always match the utilization of those funds (i.e., assets).
It displays the account balances of capital, liabilities, and assets at the end of a period so users can assess the changes in those balances against previous periods. The net earnings (loss) from the income statement are added in (subtracted from) retained earnings, which is part of equity/capital.
- Cash flow statement:
It is also known as "statement of cash flows" or "funds flow statement." Like the income statement, the cash flow statement is prepared for a period. However, unlike the income statement, it is prepared using the cash system of accounting. Since the income statement is prepared using the accrual system, it does not tell us how the cash moves in or out of the business.
The purpose of the cash flow statement is to tell us about the inflows and outflows of cash over a period. It breaks down the inflows and outflows into three categories: cash flow from operating activities, investing activities, and financing activities. The net cash flows for the period must equal the difference in the opening and the closing cash balances.
The three statements are vital to gaining a complete understanding of a company's performance. First, the income statement provides an insight into income and expenses. The balance sheet focuses on managing capital. Finally, the cash flow statement illustrates how cash is generated and invested.
The top-performing companies are efficient in all components of the three statements – their operations, capital allocation, and cash management. Efficiency in each of these areas is significant due to the interconnectedness of the three statements.
Data from the financial statements is used to conduct further analyses and create forecasts to help make decisions for the future. Users may also create pro forma financial statements based on the analyses to see how various choices can affect the financial statements.
A three-statement model takes a company's financial statements – the balance sheet, the income statement, and the cash flow statement – and combines them into a single dynamically linked financial model. Its purpose is to project what the financial statements may look like if the company makes certain decisions, given certain assumptions. Since the statements are dynamically linked in a 3-statement model, changes in one statement are automatically reflected in the other two statements.
It is the base on which other more complex financial models are constructed, such as discounted cash flow (DCF) models, leveraged buyout models, and merger models, among numerous others. Most of what might seem to be complicated in a three-statement model are basic mathematics. Building and understanding the model becomes easier if the model developers and the users are familiar with the fundamental relationships among the three statements.
Moreover, while simpler financial models use only one of these statements (the income statement or the cash flow statement), they often fail to show the entire picture. The main benefit of using as comprehensive a financial model as the three-statement model is that users can use tools like what-if analysis and scenario analysis and get a bird's eye view of how various decisions can affect the financial statements. This is crucial to making informed decisions.
The following video gives a brief overview of why the three statements are so important and why building financial models is a core skill in any high-paying job in finance.
To put together a three-statement financial model, we begin with the income statement and the balance sheet. First, we start with the actual numbers from the previous period. Then, we move on to building forecasts based on some calculated model drivers (assumptions). After building a forecasted income statement and a balance sheet, we create the cash flow statement. After these steps, we work towards linking the three statements.
The steps to build a three-statement model are listed in detail below. Although you will be all set to construct a three-statement model with these steps, we firmly believe that a more hands-on approach will help you better understand the topic. Hence, our finance experts have created a three-statement model template for you to experiment with.
Download WSO's free three-statement model template below along with other financial modeling templates! This template allows you to create your own 3 statement model for a company – specifically, the balance sheet, income statement, and statement of cash flows. The template is plug-and-play, and you can enter your numbers or formulas to auto-populate output numbers. The template also includes other tabs for other elements of a financial model.
Here we explain the steps to building a three-statement model. Before starting to build a 3-statement model, please ensure that the Iterations setting is disabled in Excel. It is to deal with the unavoidable circularity (when the output of a computation is also an input for it) in the model.
On the Windows version of Excel, users can go to File > Options > Formulas and deselect the "Enable iterative calculation" checkbox while Mac users can go to Preferences > Calculation and then disable the "Use iterative calculation" option. Please check out this article by Microsoft on removing or allowing a circular reference.
1. Input historical data:
First and foremost, input the actual numbers for the statement of operations and the statement of financial position. This task becomes easier if the data can be downloaded or copied from another source. Please note the tips below, which can make the process easier.
- Some formatting is highly recommended to ensure following the best practices for financial modeling. It makes data easy to follow for our eyes. In WSO's guide to the best practices, we recommend following the widely used color palette – blue for hard-coded inputs and assumptions, black for formulas and referencing in the same sheet, green for formulas and referencing to other sheets, and red for external links to other files.
- Use comments where necessary. They are generally inserted in the cell to the side of the relevant line item and are italicized.
- Use shortcuts if possible. One of the most used functions is Sum. Users can press "Alt + =" right under a list of numbers to calculate their sum.
- Ensure that the input is correctly entered. Users can run "balance checks" to figure and tallies. Remember the fundamental equation: Equity (Capital) + Liabilities = Assets.
Stocks APIs to retrieve data quickly is becoming increasingly common.of Python and other programming languages in financial modeling, the use of
2. Analyze historical data:
Historical data is analyzed by evaluating trends, computing ratios, and statistical information. Model drivers are based on the results of these analyses. Generally used metrics include:
- Year-on-year (YoY) growth rates for revenue, gross profit, operating profit or earnings before interest and taxes (EBIT), and net profit.
- Margins and ratios that affect them, like direct costs, financing costs, and non-cash expenses (depreciation and amortization).
- Balance sheet ratios like current ratio, receivables outstanding, inventory turnover, accounts payables days, operating cycle, and cash conversion cycle. In addition, users may also calculate ratios related to the company's capital structure.
3. Determining model drivers:
Model drivers (assumptions) guide the forecasts further. They are established on the results of the analyses of the historical data.
For example, users may use the ratios calculated in the previous step or assume how they might change in the coming years. Here are a few examples of critical assumptions that are generally used:
- Revenue growth rate. If a company's revenue has been growing at high rates in recent years, it may grow at declining rates going forward due to increase in competition.
- Costs and margins. It is reasonable to assume that a company will achieve grows, so direct costs might decline, leading to improved gross margins. In addition, with increased revenues and reduced direct costs, the company may be able to allocate an increasing sum for (R&D) expenses each year.
- Other indirect expenses may be taken as a percentage of sales. Although the capital expenditure ( ) requirements are usually forecasted using complex calculations, users may assume a fixed percentage of sales as a simple estimate. Unless changes in tax law are anticipated, historical effective tax rates are typically expected to apply in the future.
4. Projecting income statement:
Once the inputs are in place and assumptions are determined, users have all they need to begin forecasting. The entire forecasting exercise starts with the EBITDA. Although we have the inputs and the assumptions at this stage, building the income statement still requires supporting schedules for line items such as depreciation, taxes, and ., starting from the sales and down to the
However, net financing costs (interest expense) are not linked to the income statement at this stage but rather at the end. It is because financing costs are connected to the other two statements, so incorporating them in the income statement at this stage is bound to produce circularity in the model.
5. Forecasting capital assets and financing activity:
Next, users must build supporting schedules to forecast metrics related to capital assets. The closing balance for capital assets can be calculated by a simple formula which is Opening balance + Capital expenditures – Depreciation. There are multiple ways to account for depreciation, i.e., straight-line method, declining balance method, etc.
Users must also build the debt schedule to determine financing cost payments and principal repayments. We can use this simple formula to arrive at the closing figures: Opening balance + (-) increases (decreases) in the principal. The interest may be calculated in various ways, i.e., based on the closing balance of debt, the opening balance, or an average of the two. Using an average is ideal, as there might be principal repayments throughout the year. For a more intensive calculation, users may build a separate supporting schedule for financing costs altogether.
6. Projecting balance sheet:
The balance sheet is slightly tougher to build. It can be built without cash, equity, and debt at this stage. These line items will be derivedlater.
For other items, average ratios from recent years are used (with or without adjustments based on assumptions) to determine closing balances during forecasting. These ratios may, inventory days, , etc.
For instance, days payable may be computed for each of the recent years as average accounts payable ÷ cost of goods sold ( ) which can there be used to calculate the average of days accounts payable for the recent years. This number may be adjusted before it is used in the forecasted statements if necessary.
For example, to predict accounts payables for the coming years, use this formula: (adjusted) days accounts payable x expected COGS ÷ 365 days. Here are a few tips to help users at this stage.
- Unless users are expecting M&A transactions, goodwill should be unchanged going forward.
- Determining the assumptions first makes building the model and the error checking process easy.
- The Trace tool in Excel can be beneficial in checking whether the formulas have been entered using the correct dependencies.
7. Building cash flow statement:
With the partially complete balance sheet and the income statement, users can move on tostatement. There are three sections in a cash flow statement, each of which must be completed by linking the line items to the ones already calculated in the model.
We start with the net earnings for the period and make all the adjustments necessary to convert it from the accrual system to the cash system of accounting to determine the cash flows from operating activities. We also adjust for items that might belong in investing or financing activities.
Here are some critical adjustments required to be made to net earnings to ascertain the cash flows from operating, investing, and financing activities.
- Depreciation and amortization are non-cash expenses deducted from income to calculate net earnings. Therefore, these and other non-cash costs must be added back to the net income.
- An implies cash utilization, which needs to be deducted from operating activities. Conversely, a decrease in current assets means an increase in liquidity as the cash is no longer tied up in those assets. The opposite – an increase is due to not using up cash to pay them off, while a decrease is due to utilizing cash to pay them.
- Debt-related items like issuances and repayments can be derived from the . While preparing the balance sheet, expected stock issuances and buybacks must also have been factored in beforehand. These are financing activities and will not affect cash flows from operating and investing activities.
- The net cash flows for the period are calculated as the sum of cash flows from operating, investing, and financing activities.
At this stage, the users will have completed.
8. Linking the statements:
This is the concluding stage of building the three statement model. A few items in the balance sheet were intentionally left out. Users can now build on them and link the three statements.
- Debt: Prior long-term debt is adjusted for expected issuances and repayments. It is worth noting that long-term debt becomes short-term debt as it nears its expected repayment.
- Equity: Shareholders' equity is computed as - Opening balance + Expected issuances or buybacks (from the cash flow statement) + Net income (from the income statement) – Dividends (from the cash flow statement).
- Cash: The closing balance for cash is calculated as - Opening balance + Net cash flow for the period.
Once these items are calculated, they will be plugged into the balance sheet. Please ensure that the balance checks do not indicate any errors.
9. Net interest expense:
The puzzle is almost complete. The interest expense (or income) is the single item left to be plugged. Plugging in the net finance costs in the income statement will change the net earnings, further impacting the balance sheet throughand the cash flow statement through cash flow from operating activities.
Thus, financing costs affect all three statements, and this produces circularity. It is also known as iterative calculation. Excel (or other spreadsheet software) runs different numbers through the calculations to find the values which satisfy each of such analyses.
As the final piece of the puzzle, Iterations must be enabled in Excel. On the Windows version of Excel, users can go to File > Options > Formulas and select the "Enable iterative calculation" checkbox. Users with the Mac version of Excel can go to Preferences > Calculation and enable "Use iterative calculation." The net earnings figure should get updated in the income statement now, which should flow into the other two statements.
At the end of the above steps, your three-statement model should be ready to help you make more informed decisions.
Check this page out for another 3 Statement model example.