Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)™. Register today!

3 Statement Model

A free guide on three statement models.

What is a 3 statement model?

A 3 statement model links the income statement, balance sheet, and cash flow statement into one dynamically connected financial model.  3 statement models are the foundation on which more advanced financial models are built such as discounted cash flow DCF models, merger models, leveraged buyout LBO models, and various other types of financial models.

overview of a 3 statement model
Levels of modeling

 

Layout and Structure

There are two common approaches to structuring a 3 statement model: single worksheet and multip worksheet. While both approaches are fine, CFI strongly recommends using a single worksheet structure (with grouping) for several reasons outlined below.

Advantages of a single worksheet model are:

  • Easier to navigate (don’t have to switch between tabs)
  • Less risk of mislinking formulas (all time periods are in the same column)
  • More organized with the use of grouping cells
  • Allows more room for consolidating multi-business companies

 

layout of a three statement model
Single versus multi worksheet models

 

How do you build a 3 statement 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
  7. Complete the cash flow statement

In this guide, we will walk you through each of the above steps.  For a more detailed, video-based tutorial on how to build a model from scratch, please watch our online financial modeling courses.

 

approach and steps to build a three statement model
Steps in linking the statements

 

Input the historical information into Excel

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 (see this free course on Excel best practices), you’ll need to do some basic formatting to make the information easy to read and follow the structure you want your model to take. As you can see in the screenshot below, the historical information is entered in a blue font color under the historical time periods.

 

Three Statement Model Screenshot

 

Download the financial model template

Simply enter your information below and the template is yours!

3 Statement Model Template

Download the completed 3 statement
model from this webinar!

 

Determining the assumptions that will drive the forecast

With the historical financial information in Excel, and in an easy-to-use format, we can start calculating some metrics to evaluate the historical performance of the company.  We need to calculate metrics such as revenue growth, margins, capital expenditures, and working capital terms (such as accounts payable, inventory, and accounts receivable). Below is an example of the assumptions section that drives the forecast.

 

3 statement model - forecast

 

Forecasting the income statement

With the assumptions in place, it’s time to start forecasting the income statement, beginning with revenue and building down to EBITDA (Earnings Before Interest Taxes Depreciation and Amortization).  At that point, we will require support schedules to be built for capital assets and financing activity.

 

Forecasting capital assets

At this point, we need to forecast capital assets like Property Plant & Equipment PP&E before we can finish the income statement in the model.  To do this we take last period’s closing balance, add any capital expenditures, deduct depreciation, and arrive at the closing balance.  Depreciation can be calculated in a variety of ways, such as straight line, declining balance, or percent of revenue.

 

three statement model - assets

 

Watch a video of how to build this model.

 

 

Forecasting financing activity

Next up we have to build a debt schedule to determine interest expense on the income statement.  Similar to the section above, we take last period’s closing balance, add and increases or decreases in principle, and arrive at the closing balance  The interest expense can be calculated on opening balance, closing balance, or the average balance of debt outstanding.  Or, a detailed interest payment schedule can be followed if available.

 

Forecasting the balance sheet

At this stage, it’s possible to complete the balance sheet in our 3 statement model, except for the cash balance which will be the last step.  Working capital items are forecasted based on assumptions around average days payable and receivable, as well as inventory turns.  Capital assets like PP&E come from the schedule above, as well as debt balances.

 

3 statement model - balance sheet

 

Completing the cash flow statement

With the balance sheet completed (except for cash), we can build the cash flow statement and complete our three statement model in Excel.  This section is essentially just linking to items that have already been calculated above in the model.  We have to complete each of the three main sections: cash from operations, cash from investing, and cash from financing.  For a more detailed explanation of how to calculate each of these sections, please check out our course, how to build a financial model in Excel.

 

3 statement model - cash flow section

 

Download the financial model template

Simply enter your information below and the template is yours!

3 Statement Model Template

Download the completed 3 statement
model from this webinar!

 

More modeling resources

We hope this has been a helpful guide on how to build a 3 statement model in Excel.  At CFI we’ve created a vast database of resources to help you learn financial modeling and advance your career.

Below are some of our most helpful resources and guides:

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)™ by completing CFI’s online financial modeling classes!