Process of Creating a Financial Modeling
The process of creating financial modeling is a step by step approach that starts with populating the historical financial data in an excel sheet, performing financial analysis, making assumptions and forecasting and finally assessing risk by performing sensitivity analysis and stress testing. We have broadly divided this process in 7 steps –
- The entry of Historical Financial Data
- Analysis of Historical Performance
- Gathering of Assumptions for Forecasting
- Forecast the Three Statement Model
- Future Business Risk Assessment
- Performance of Sensitivity Analysis
- Stress Testing of the Forecast
#1 – Entry of Historical Financial Data
The start of any financial model happens with the entry of the historical financial statements. Generally, analysts prefer latest 3 to 5 years of historical data as it provides a fair bit of insight about the company’s business trend in the recent past. The analyst then inputs the historical information into an excel spreadsheet, which marks the start of financial modeling. The analyst should be cautious while capturing the historical data from the three financial statements and the corresponding schedules. Any mistake in this step can potentially deteriorate the quality of the end model.
#2 – Analysis of Historical Performance
In this step, the analyst is required to apply all his/ her knowledge of accounting and finance. Each line items of the historical income statement, balance sheet and cash flow statement should be analysed to draw meaningful insights and identify any trend. For instance, growing revenue, declining profitability, deteriorating capital structure etc.
Once the trend has been identified, the analyst should try and understand the underlying factors driving the trend. For instance, the revenue has been growing due to volume growth; the profitability has been declining in the last 3 years owing to surge in raw material prices, capital structure has deteriorated on the back of debt-laden capex plan etc. It is important to note that this analysis will have a strong influence on the assumptions for forecasting.
4.9 (927 ratings) 16 Courses | 15+ Projects | 90+ Hours | Full Lifetime Access | Certificate of Completion
#3 – Gathering of Assumptions for Forecasting
Next, the analyst has to build the assumptions for the forecast. Now, the first method to draw assumptions is by using the available historical information and their trends to project future performance. For instance, forecast the revenue growth as an average of the historical revenue growth in the last three years, project the gross margin as an average of the historical period etc. This method is useful in case of stable companies.
On the other hand, some analysts prefer to use forecast assumptions based on the current market scenario. This approach is more relevant in the case of companies operating in a cyclical industry, or the entity has a limited track record. Nevertheless, the assumptions for some of the line items in the balance sheet, such as debt and capex, should be drawn from the guidance provided by the company to build a reliable model.
#4 – Forecast the Financial Statements using the Assumptions
Once the assumption is decided, now it is time for building the future income statement and balance sheet based on the assumptions. After that, the cash flow statement is linked to both income statement and balance sheet to capture the movement of cash in the forecasted period. At the end of this step, there are two basic checks –
- The value of the total asset should match with the summation of total liabilities and shareholder’s equity
- The cash balance at the end of the cash flow statement should be equal to the cash balance in the balance sheet
#5 – Future Business Risk Assessment
Next, the analyst should create a summary of the output of the final financial model. The output is usually customized as per the requirement of the end-user. Nevertheless, the analyst must provide his/ her opinion on how the business is expected to behave in the upcoming years based on the financial model. For instance, the analyst can comment that the company will be able to grow sustainably and service its debt obligations without any discernible risks in the near to medium term.
#6 – Performance of Sensitivity Analysis
In this step, the analyst needs to build scenarios into the model to perform sensitivity analysis. The objective of this step is to determine at what point the performance of the company will start to decline and to what extent. In other words, the resilience of the business model will be tested based on scenarios. This step is beneficial as it helps in the assessment of variation in performance in case of an unanticipated event.
#7 – Stress Testing of the Forecast
Here the analyst assumes the worst-case (extreme) scenario based on some unfavourable event during a specific period of time, say a decade. For instance, the recession of 2008-09 is used for stress testing the forecasting models of US-based companies. This step is also crucial as it helps in understanding how a company will behave in such an extreme scenario, whether it will able to sustain.
Recommended Articles
This has been a guide to the process of Financial Modeling. Here we discuss the top 7 steps including – entry of historical financial data, Analysis of historical performance, Gathering of assumptions for forecasting, Forecast the three financial statements etc. You can learn more about from the following articles –
