Everything You Need To Master Financial Modeling
Enroll in The Premium Package: Learn Financial Statement Modeling, DCF, M&A, LBO and Comps. The same training program used at top investment banks.
Enroll TodayA complete guide to building one of the most widely used financial models
Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.
An integrated 3-statement financial model is a type of model that forecasts a company’s income statement, balance sheet and cash flow statement.
While accounting enables us to understand a company’s historical financial statements, forecasting those financial statements enables us to explore how a company will perform under a variety of different assumptions and visualize how a company’s operating decisions (i.e. “let’s reduce prices”), investing decisions (i.e. “let’s buy an additional machine”) and financing decisions (i.e. “let’s borrow a bit more”) all interact to impact the bottom line in the future.
A well-built 3-statement financial model helps insiders (corporate development professionals, FP&A professionals) and outsiders (institutional investors, sell side equity research, investment bankers and private equity) see how the various activities of a firm work together, making it easier to see how decisions impact the overall performance of a business.
It is critical that a complex financial model like the 3-statement model adheres to a consistent set of best practices. This makes both the task of modeling and auditing other people’s models far more transparent and useful. We have written an Ultimate Guide to Financial Modeling Best Practices, but we’ll summarize some key takeaways here.
The most basic formatting rules are:
Type of cells | Color |
---|---|
Hard-coded numbers (inputs) | Blue |
Formulas (calculations) | Black |
Links to other worksheets | Green |
Links to other files | Red |
Links to data providers (i.e. CIQ, Factset) | Dark Red |
One of the first decisions to make in a 3-statement model concerns the periodicity of the model. Namely, what are the shortest time periods the model will be partitioned into: annual, quarterly, monthly or weekly. This will typically be determined by the 3-statement financial model’s purpose. Below we outline some general rules of thumb:
When models get large, adhering to a strict structure is critical. Key rules of thumb include:
An integrated 3-statement model
3-statement models include a variety of schedules and outputs, but the core elements of a 3-statement model are, as you may have guessed, the income statement, balance sheet and cash flow statement. A key feature of an effective model is that it is “integrated,” which simply means that the 3-statement models are modeled in a way that accurately captures the relationship and inter-linkages of the various line items across the financial statements. An integrated model is powerful because it enables the user to change an assumption in one part of the model in order to see how it impacts all other parts of the model consistently and accurately.
Before firing up Excel to begin building the model, analysts need to gather the relevant reports and disclosures. At a minimum, they will need to gather the company’s latest SEC filings, press releases and possibly equity research reports. Data is much harder to find for private companies than for public companies, and reporting requirements vary across countries. We have compiled a guide on gathering historical data needed for financial modeling here.
The income statement illustrates a company’s profitability. All three statements are presented from left to right, with at least 3 years of historical results present in order to provide historical rations and growth rates from which forecasts are based. Inputting the historical income statement data is the first step in building a 3-statement financial model. The process involves either manual data entry from the 10K or press release, or the use of an Excel plugin such as Factset or Capital IQ to drop historical data directly into Excel.
Forecasting typically begins with a revenue forecast followed by the forecasting of various expenses. The net result is a forecast of the company’s income and earnings per share. The income statement covers a specified period such as quarter or year.
For more on this, check out the complete income statement forecasting guide.
Income Statement Screenshot from the Wall Street Prep Premium Package Training Program
Unlike the income statement, which shows operating results over a period of time (a year or a quarter), the balance sheet is a snapshot of the company at the end of the reporting period. The balance sheet shows the company’s resources (assets) and funding for those resources (liabilities and shareholder’s equity). Inputting historical balance sheet data is similar to inputting data in the income statement. The data is inputted either manually or through an Excel plugin.
In large part, the balance sheet is driven by the operating assumptions we make on the income statement. Revenues drive the operating assumptions in the income statement, and this continues to hold true in the balance sheet: Revenue and operating forecasts drive working capital items, capital expenditures and a variety of other items. Think of the income statement as the horse and the balance sheet as the carriage. The income statement assumptions are driving the balance sheet forecasts.
Click here for a complete guide to forecasting the balance sheet
Balance Sheet Screenshot from the Wall Street Prep Premium Package Training Program
The final core element of the 3-statement model is the cash flow statement. Unlike on the income statement or the balance sheet, you aren’t actually forecasting anything explicitly on the cash flow statement and it isn’t necessary to input historical cash flow statement results before forecasting. That’s because the cash flow statement is a pure reconciliation of the year-over-year changes in the balance sheet.
Every individual line item on the cash flow statement should be referenced from elsewhere in the model (it should not be hardcoded) as this is a reconciliation. Constructing the cash flow statement correctly is critical to getting the balance sheet to balance. To see how this done, watch this free lesson on cash flow statement modeling.
Cash Flow Statement Screenshot from the Wall Street Prep Premium Package Training Program
A universal feature of a 3-statement model is that cash and a revolving credit line serve as model “plugs.” This simply means that a 3-statement model has an automatic way of ensuring that, when the model projects a cash shortfall after all the line items are forecast, additional debt via a “revolver” account will automatically increase to finance the shortfall. Conversely, if the model projects a cash surplus, cash will accumulate by the amount of the surplus. While this seems fairly logical, modeling this can be tricky. Click here for a guide to forecasting the revolver and cash balance with a free excel template.
Many financial models have to deal with a problem in Excel called circularity. A circularity in Excel occurs when one calculation either directly or indirectly depends on itself to arrive at an output. In the 3-statement model, a circularity can occur because of the model plugs described above. This makes Excel unstable and can create a variety of problems for those using the model. There are several elegant ways to deal with this issue. To learn more about how to deal with circularity, go to the “Circularity” section of this article about financial modeling best practices.
Enroll in The Premium Package: Learn Financial Statement Modeling, DCF, M&A, LBO and Comps. The same training program used at top investment banks.
Enroll TodayFor public companies, projecting earning per share is a key forecast. Forecasting the numerator of EPS is described in detail in our income statement forecasting guide, but forecasting shares outstanding can done in a variety of ways, ranging from simply straight-lining the historical share count to a more sophisticated analysis that takes into account forecasts for share repurchases and issuances. Click here for a guide to forecasting EPS.
The purpose of building a 3-statement financial model is to observe how various operating, financing and investing assumptions impact a company’s forecasts. Once the initial case is built it is useful to see — using either equity research, management guidance, or other assumptions — how the forecasts change given changes in a variety of key model assumptions. To this end, financial models often have a drop-down that lets users choose to select either the original case (often called “base case”) or a variety of other scenarios (“strong case,” “weak case,” “management case,” etc.).
Click here to watch a free video on how to perform scenario analysis in a financial model.
A close cousin of scenario analysis is sensitivity analysis. Any good 3-statement financial model (or a DCF model, LBO model or M&A model, for that matter) will include the ability to toggle between various scenarios in order to see how the model’s output changes, as well as something called sensitivity analysis. Sensitivity analysis is the process of isolating one (usually critical) model output to see how it is impacted by changes to one or two key inputs. For example, how would Apple’s 2020 EPS forecast change, at various assumptions for 2020 revenue growth and gross profit margins? Click here to learn how to build a sensitivity analysis into a 3-statement model.
Building a 3-statement financial modeling requires the combination of the following skills:
The output of a 3-statement model serves as the foundation for several types of financial models:
The Wall Street Prep Quicklesson Series
7 Free Financial Modeling Lessons
Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.
Wonderful. I have a question. I have seen 3 statement model which is generated with the help of VBA bases macros. I guessed it needs some support files like simulation, forecasting assumptions etc to build a model. How to prepare these support files? Is it really required for modeling? Apart… Read more »