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.
There are two common approaches to structuring a 3 statement model: single worksheet and multi-worksheet. While both approaches are acceptable, CFI strongly recommends using a single worksheet structure (with grouping), for several reasons that are outlined below.
Advantages of a single worksheet model include the following:
There are several steps required to build a three statement model, including:
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 to make it 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.
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, which drives the forecast.
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 items such as capital assets and financing activity.
At this point, we need to forecast capital assets such as 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, and then 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.
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, and then add any increases or decreases in principal , and arrive at the closing balance
The interest expense can be calculated on opening balance, closing balance, or the average balance of debt outstanding. Alternatively, a detailed interest payment schedule can be followed if one is available.
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 (PP&E, etc.) come from the schedule above, as well as debt balances.
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 completed, essentially, by 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.
Credits to : Corporate Finance Institute