Three Statement Financial Modeling

With these formulas, we can now add these links to the Cash Flow Statement and set the “Other” line item in Cash Flow from Financing to ~2% of Debt Issuances to represent the issuance fees. The case study document tells us to “follow company guidance” for these last few line items. This last one is a “catch-all” for everything else on the CFS that has not yet been reflected on the Balance Sheet, and it’s sometimes also known as the Statement of Owner’s Equity. In this case, the company provides specific guidance on the Dividend Payout Ratio, so we increase it slightly over the period to match their targets (see below). One exception to these simple rules is the Dividends line, which we forecast based on the Dividend Payout Ratio (i.e., Dividends / Net Income) (for more, see our tutorial on the dividend yield).

1. Once you’ve finished inputting the historical data on the Income Statement and Balance Sheet, you can calculate key historical financial ratios.
2. The 3-Statement Model is an integrated model used to forecast the income statement, balance sheet, and cash flow statement of a company for purposes of projecting its forward-looking financial performance.
3. The cash flow statement helps forecast cash and short-term borrowings and is an important step in linking the three statements.
4. The net result is a forecast of the company’s income and earnings per share.

This makes both the task of modeling and auditing other people’s models far more transparent and useful. In this step, we take the historical financial information of the company and either download, type or paste it into Excel. Once the information is in Excel, you’ll need to do some basic formatting to make the information easy to read and to make it follow the structure you want your model to take.

At this stage, the users will have completed projecting the three financial statements. This number may be adjusted before it is used in the forecasted statements if necessary. Sign up to receive a FREE swipe file containing a collection integrated 3-statement build of quality financial modeling templates to help your finance skills and prepare for interviews. It starts with the revenue in the first line, and after deducting various direct and indirect expenses, arrives at the company’s net income.

They are established on the results of the analyses of the historical data. With the advent of Python and other programming languages in financial modeling, the use of Stocks APIs to retrieve data quickly is becoming increasingly common. Hence, our finance experts have created a three-statement model template for you to experiment with. The net cash flows for the period must equal the difference in the opening and the closing cash balances. Sadaf Abbas, with over 16 years in the financial consulting realm, has showcased her expertise across diverse industries like Blockchain, Gaming, and SaaS.

Step 5: Linking the Three Statements

We can calculate the average interest rate on Debt in the previous years, but we don’t know how it will change in the future. It would also be helpful to know about something like the degree of operating leverage, so we could better forecast different expenses. An integrated model is powerful because it enables the user to change an assumption in one part of the model to see how it impacts all other parts of the model consistently and accurately. Once we have created the Balance Sheet assumptions, we can fill in the Balance Sheet line items while leaving out the Cash, Net PP&E, Debt and Shareholders’ Equity line items.

Model Design & Layouts

As you can see in the screenshot below, the historical information is entered in a blue font color under the historical time periods. With the income statement projected (purple-shaded line items excluded), the next step is to project the balance sheet. Five items will need to be shaded purple on the balance sheet for the same reason outlined above.

Education Industry Financial Model

The template also includes other tabs for other elements of a financial model. 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. In this article, we will explain what the three statements are, what a three-statement model is, and how to build it. Constructing a Three-Statement Financial Models is both an art and a science, requiring a blend of analytical rigor and strategic foresight.

You’ll need to format the data and organize the income statement, balance sheet, and cash flow. In this guide, I’ll walk you through the steps required to build a three-statement model. Before starting to build a 3-statement model, please ensure that the Iterations setting is disabled in Excel.

This comprehensive approach is invaluable for financial analysts and planners, enabling detailed financial modeling, cash flow projections, and scenario planning. Through such an integrated framework, stakeholders can gauge a company’s financial health, strategize effectively, and make informed decisions. The model stands as a foundational tool in finance, pivotal for anyone from investment bankers to corporate financial planners, guiding the evaluation of company performance and strategic direction. 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.

Once you add these details, you’ll have the beginning and ending cash balances for all forecast periods. The balance sheet tells you the amount of assets, liabilities, and equity your business has. To create the income statement, you sum up the revenue and other incomes and reduce the sum by the amount of expenses to get a net profit. 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. The template is plug-and-play, and you can enter your numbers or formulas to auto-populate output numbers.

The three-statement model involves projecting the three financial statements based on historical financial statements and assumptions about the future. 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.