What is the 3 Statement Model?
A 3 statement model is a type of financial modeling which connects three key financial statements like the income statement, balance sheet and cash flow statement and prepares a dynamically connected one single financial model which is used as the base of complex financial models like leverage buyout, discounted cash flow, merger models and other financial models.
A 3 statement model is a complex financial model which combines the three critical financial statement like income statement, balance sheet, and cash flow statement and integrates all the three into a single financial model. This model acts as the base for further critical models like DCF valuation, merger and acquisition models, etc. It is also used for scenario and sensitivity analysis. The leading utility of these models is that in a single excel file, we can capture the fundamentals of three statements simultaneously. There is less risk of wrong linkages of formulas. It looks more organized when the entire thing is presented in a single excel file and rather than using three different models. It also provides an enhanced scope of consolidation of multi-business organizations.
You can learn the step by step 3 statement of Colgate from this link – Financial Modeling in Excel.
How to Create a 3 Statement Model?
A 3 statement model requires several steps to finally integrate all the three financial statements into a single model. The steps required are as follows:
Step 1: Gathering or Collating Historical Information from Financial Statement in Single Excel File
In this step, financial information is obtained from the company website or their press release, and these are considered historical information. These data are either downloaded to a single excel file or copy-pasted into it. After doing this, the excel file needs to be formatted a bit to make the data readable and understandable.
Step 2: Chalking Out Assumptions which will Determine Forecasting
Now once we have the historical data in our excel file, we can implement some formulas to calculate or evaluate the historical performance of the company. Metrics such as margins, growth of revenues, capital expenditures, and working capital calculation can be considered as the following.
4.9 (927 ratings) 16 Courses | 15+ Projects | 90+ Hours | Full Lifetime Access | Certificate of Completion
Step 3: Income Statement Forecasting
After all the assumptions taken into consideration, it is now the stage to forecast the income statement. It all begins from the revenue and drills now further to the calculation of the EBITDA. In this stage, we also require support scheduling methods for financing activity processes and capital assets.
Step 4: Capital Asset Forecasting
Here forecast is made on aspects like plant and machinery, property, and only after this, one can end the income statement part of the model. Here the closing balance of the last period is considered, and then the capital expenditure is added, or depreciation deducted to arrive at the final closing balance.
Step 5: Financing Activity Forecasting
Here we need to set up a schedule of debt plan to arrive at the interest expense on the income statement. Here too, we consider the last period’s closing balance and then add to this any increase or decrease in the value of the principal to conclude the closing balance.
Step 6: Balance Sheet Forecasting
Here we consider the balance sheet information and working capital elements are forecasted here, taking into consideration assumptions like average payable days, average receivables, inventory turns, etc. Capital assets come here from the schedule we mentioned above. Here the cash balance is not forecasted or completed, which is the last step of the 3 statement model.
Step 7: Cash Flow Statement Completion
The last stage of three-statement modeling is the completion of the cash flow statement. This statement requires a simple linking of the earlier items, calculated to arrive at the cash balance. There are three main sections: cash from operations, cash from investing activity, and cash from financing activity. Thus, by linking these three to the other statements, we arrive at the final cash in hand/bank balance.
A template related to the 3 statement model based on excel is now attached. Here we have included historical data till 2020 and forecasted based on the same till 2025. Three key financial statements have been used: profit and loss statement, balance sheet, and cash flow statement. In the end, we have also attached a plan of schedule, and at the beginning of the template, there are the assumptions that have been considered.
There are five steps to build a 3 statement model, and we rely on our base on historical. Assumptions are essential because we need to assume many factors like growth rate, interest rate, etc.
- Thus the first and foremost important approach is our assumptions, which means how the business will drive itself in the forecasted period.
- Next comes the income statement, which is a summary of profit and loss, and thus, based on the historical data, the future is predicted.
- After this, we have the balance sheet, which explains the position of the company at a particular point in time. Same as profit and loss statement here too based on historical data, we forecast the future period.
- After this, from data points in the above two statements, we build our cash flow statement, which is the ultimate goal of our 3 statement modeling. Here few data gets populated from profit and loss statement and balance sheet. Thus, we find all three statements are linked to one another. It shows the different levels of cash from operations, investing activity, and financing activity and finally arrives as closing cash balance or net cash balance.
- The last part of the three-statement model is the supporting schedules, which help calculate the depreciation or interest and other such factors.
The 3 statement model is very dynamic modeling to integrate all the key financial statements into a single excel file. This makes the job more organized and more comfortable and reduces the chances of human error. It is a very crucial model used for forecasting modules. It is also very user friendly, and because one uses excel to build it, this is easily understandable by all.
This has been a guide to the 3 Statement Model and its Meaning. Here we discuss how to create these models along with examples and approaches. You can learn more about from the following articles –