Table of Contents

    • Introduction to 3-statement modeling
    • Formatting a 3-statement model
    • Periodicity
    • Model structure
    • Basic elements of an integrated 3-statement financial model
    • Gathering data ahead of financial modeling
    • The income statement
    • The balance sheet
    • Cash flow statement
    • Model plugs: cash and revolver
    • Handling circularity
    • Calculating shares and earnings per share (EPS)
    • Scenario Analysis
    • Sensitivity Analysis
    • Effective modeling requires a combination of skills
    • M&A, DCF and LBO models depend on forecasts produced in the 3-statement model

    Introduction to 3-statement modeling

    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.

    Formatting a 3-statement model

    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:

    • Color code your model so that inputs are blue and formulas are black. The table below shows other color-coding best practices: 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
    • Format data consistently (for example keep consistent unit scale, use 1 decimal place for numbers, 2 for per share data, 3 for share count).
    • Avoid partial inputs that commingle cell references with hard numbers.
    • Maintain standard column widths and consistent header labels.

    Periodicity

    One of the first decisions to make in a 3-statement financial 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:

    • Annual models: Common when using the model to drive a DCF model valuation. This is because a DCF model needs at least 5 years of explicit forecasts before making terminal value . LBO models are often also annual models, as the investment horizon is around 5 years. An interesting wrinkle with annual models is the handling of the “stub period,” which captures the latest 3-, 6-, or 9-month historical data).
    • Quarterly models: Common in equity research, credit, financial planning and analysis , mergers and acquisitions (accretion/dilution) models where near-term issues are a catalyst. These models often roll up into an annual buildup.
    • Monthly models: Common in restructurings and project finance where month to month liquidity tracking is critical. One thing to note is that the data required for a monthly buildup is usually unavailable to outside investors unless it is privately provided by management (companies don’t report monthly data). These models often roll up into a quarterly buildup.
    • Weekly models: Common in bankruptcies. The most common weekly model is called the thirteen-week cash flow model (TWCF). The TWCF is a required submission in a bankruptcy process to track cash and liquidity.

    Model structure

    When models get large, adhering to a strict structure is critical. Key rules of thumb include:

    • Use roll-forward schedules when forecasting balance sheet items.
    • Aggregate inputs in one worksheet or one section of the model and separate them from calculations and outputs.
    • Avoid linking files together.

    Basic elements of an integrated 3-statement financial model

    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.

    Gathering data ahead of financial modeling

    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

    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 .

    The balance sheet

    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

    Cash flow statement

    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 .

    Model plugs: cash and revolver

    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 .

    Handling circularity

    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 .

    Step-by-Step Online Course

    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 Today

    For 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 .

    Scenario Analysis

    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.

    Sensitivity Analysis

    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 .

    Effective modeling requires a combination of skills

    Building a 3-statement financial modeling requires the combination of the following skills:

    • Excel Getting strong in Excel may seem daunting, but it’s actually the easiest skill on this list to develop. A general rule of thumb in finance is to avoid the mouse and memorize some keyboard shortcuts. Wall Street Prep offers an Excel Crash Course to get you up to speed.
    • Accounting This is the single most important (and least glamorous) part of getting strong in modeling. Understanding how the three financial statements are tied together, and what each line item on the income statement, balance sheet and cash flow statement represents is the key to the conceptual understanding of how a 3-statement financial model works. Wall Street Prep’s Accounting Crash Course is a great way to learn these skills.
    • Reading Financial Reports Even though 3-statement financial models are designed to illuminate a firm’s future performance, setting up the model depends on a thorough understanding of what happened to the company in the past. For that, investment bankers and investors gather historical financial data . Whether you’re looking through SEC filings or quarterly press releases, or modeling a private company where you’re only provided piecemeal disclosures, finding the data you need will feel like a scavenger hunt. Your ability to navigate those reports and to find the exact data you are looking can make the difference between a model. Our course on Analyzing Financial Reports covers all of these skills.
    • Company and industry knowledge One of the realities for new investment bankers is that they are often tasked with building a lot of models for industries and companies they don’t really know and don’t have time to learn. A 3-statement financial model’s assumption about things like revenue growth and profit margins are critical to making a good forecast, so knowing the resources available to collect company and industry insights is very important. Quite often, investment bankers rely on sell side equity research to quickly get smart on company and industry. Meanwhile, institutional investors (who, unlike investment bankers, have skin in the game) spend even more time getting to know the company, often through a lot of due diligence such as speaking with management and customers, going on site visits and trying out products themselves.
    • Attention to detail Once wrong decimal place is all it takes to completely screw up a model. In investment banking, corporate finance, and equity research, the stakes are high and attention to detail is often the difference between getting promoted and getting fired.

    M&A, DCF and LBO models depend on forecasts produced in the 3-statement model

    The output of a 3-statement model serves as the foundation for several types of financial models :

    • Discounted Cash Flow (DCF) modeling In investment banking , private equity , and on the investment management side, practitioners value companies using a methodology called the DCF approach. This approach looks at a company’s future expected cash flows and discounts those cash flows to the present. While analysts sometimes rely on a “back of the envelope” approach when building the DCF, a rigorous DCF analysis requires a full 3-statement model feed the cash flow forecasts.
    • Mergers & Acquisitions (M&A) modeling To analyze the impact of an acquisition on a variety of key considerations for buyers and sellers, such as the acquirer’s profitability, accretion/dilution, capital structure and synergies post acquisition, as well as the seller’s tax implications, 3-statement financial models for both companies need to be constructed and fused together.
    • Leveraged Buyout (LBO) modeling The only way to truly understand how a leveraged buyout (or a management buyout) or a corporate bankruptcy or restructuring will impact a company’s performance (and thus ultimately determine the potential returns to the financial sponsors and lenders involved in the buyout), is to construct a 3-statement financial model for the buyout candidate, and it must be flexible enough to handle the new leveraged capital structure.
    How do you build a 3 way financial model?
    There are several steps required to build a three statement model, including:
    1. Input the historical financial information into Excel.
    2. Determine the assumptions that will drive the forecast.
    3. Forecast the income statement.
    4. Forecast capital assets.
    5. Forecast financing activity.
    6. Forecast the balance sheet.
    more
    Which came first the Model T or the Model A?
    Henry Ford conceived a series of cars between the founding of the company in 1903 and the introduction of the Model T. Ford named his first car the Model A and proceeded through the alphabet up through the Model T, twenty models in all. Not all the models went into production. more
    What are the 10 steps guide in building a financial model?
    How do you build a financial model? (10 Step Guide)
    • Historical results and assumptions.
    • Start the income statement.
    • Start the balance sheet.
    • Build the supporting schedules.
    • Complete the income statement and balance sheet.
    • Build the cash flow statement.
    • Perform the DCF analysis.
    • Add sensitivity analysis and scenarios.
    more
    What information would you look to build financial models?
    In order to build a financial model, you need a solid understanding of accounting fundamentals. You have to know what all the various accounts mean, how to calculate them, and how they're connected. We recommend having at least a few accounting courses under your belt. more
    What is the difference between a Model T and a Model A?
    Engine – The Ford Model T was equipped with a 177 cubic inch four-cylinder engine with 20 horsepower, with a top speed around 45 mph. The Ford Model A was equipped with a similar flat head four-cylinder engine, this one with 201 cubic inch displacement and twice the power at 40 hp and a top speed of 65 mph. more
    What was first Model A or Model T?
    The Ford Model A (also colloquially called the A-Model Ford or the A, and A-bone among hot rodders and customizers) was the Ford Motor Company's second market success, replacing the venerable Model T which had been produced for 18 years. It was first produced on October 20, 1927, but not introduced until December 2. more
    How do you build a strong financial model?
    1. Always plan ahead.
    2. Ensure your financial model is logically structured.
    3. Keep your financial model simple.
    4. Avoid using values instead of formulas.
    5. Ensure your cash flow calculations and balance sheets are fully integrated.
    6. Check the accuracy of your model.
    more
    Is Model S better than model Y?
    One of the most apparent distinctions is size, with the Model Y being a noticeably taller cross-over SUV, while the Model S is a sportier, more compact sedan. While the Model Y offers more room for luggage and comfortable seating, the Model S makes up for that by being capable of going 20 mph faster. more
    How long does it take to build a 3 statement model?
    For investment banking, specifically, these types of case studies are most common: 3-Statement Models – You might receive a company's financial statements in Excel and then get 20-30 minutes, up to 2-3 hours, depending on the complexity, to build a 3-statement projection model for the company. more
    How do you build a strong financial model?
    1. Always plan ahead.
    2. Ensure your financial model is logically structured.
    3. Keep your financial model simple.
    4. Avoid using values instead of formulas.
    5. Ensure your cash flow calculations and balance sheets are fully integrated.
    6. Check the accuracy of your model.
    more
    What is build build build program in the Philippines?
    Build! Build! (BBB) Program is the centerpiece program of the Duterte administration that aims to usher the “Golden age of infrastructure” in the Philippines. Lack of infrastructure has long been cited as the “Achilles' heel” of Philippine economic development. more

    Source: www.wallstreetprep.com

    You may be interested in...

    Where do I find coin pushers?

    Which crab has the most meat female or male?

    Do you break out when you're in love?

    How much does it cost to become a Solana validator?

    Does Australia have universal healthcare?

    How the snake lost its legs?

    Does turmeric cause liver damage?

    What is one major problem that has resulted from globalization in the 20th and 21st centuries?

    What does CX mean in dental terms?

    Who are Vogue Williams parents?

    How do you know your liver is struggling?

    Which of the following procedures is part of the management by objective feature of the ICS?

    How can I lose inches off my waist in 2 weeks?

    Can you short and long on KuCoin?

    What is the most popular burger at Wendy's?

    About Privacy Contact
    ©2022 REPOKIT