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 AnalysisSensitivity AnalysisSensitivity analysis is a type of analysis that is based on what-if analysis, which examines how independent factors influence the dependent aspect and predicts the outcome when an analysis is performed under certain conditions.read more
- Stress Testing of the Forecast
You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Financial Modeling Process (wallstreetmojo.com)
#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 statementsFinancial StatementsFinancial statements are written reports prepared by a company's management to present the company's financial affairs over a given period (quarter, six monthly or yearly). These statements, which include the Balance Sheet, Income Statement, Cash Flows, and Shareholders Equity Statement, must be prepared in accordance with prescribed and standardized accounting standards to ensure uniformity in reporting at all levels.read more 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 statementIncome StatementThe income statement is one of the company's financial reports that summarizes all of the company's revenues and expenses over time in order to determine the company's profit or loss and measure its business activity over time based on user requirements.read more, balance sheetBalance SheetA balance sheet is one of the financial statements of a company that presents the shareholders' equity, liabilities, and assets of the company at a specific point in time. It is based on the accounting equation that states that the sum of the total liabilities and the owner's capital equals the total assets of the company.read more and cash flow statementCash Flow StatementStatement of Cash flow is a statement in financial accounting which reports the details about the cash generated and the cash outflow of the company during a particular accounting period under consideration from the different activities i.e., operating activities, investing activities and financing activities.read more should be analysed to draw meaningful insights and identify any trend. For instance, growing revenue, declining profitabilityProfitabilityProfitability refers to a company's ability to generate revenue and maximize profit above its expenditure and operational costs. It is measured using specific ratios such as gross profit margin, EBITDA, and net profit margin. It aids investors in analyzing the company's performance.read more, 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 structureCapital StructureCapital Structure is the composition of company’s sources of funds, which is a mix of owner’s capital (equity) and loan (debt) from outsiders and is used to finance its overall operations and investment activities.read more has deteriorated on the back of debt-laden capexCapexCapital Expenditure is the total amount that a Company spends to buy & upgrade its fixed assets like PP&E (Property, Plant, Equipment), technology, & vehicles etc. You can calculate it by adding the net change in PP&E value over a given period to the depreciation expense for the same year. read more plan etc. It is important to note that this analysis will have a strong influence on the assumptions for forecasting.
#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 marginGross MarginGross margin is derived by deducing the cost of goods sold (COGS) from the net revenue or net sales (gross sale reduced by discounts, returns, and price adjustments). Gross margin formula (in absolute term) = Net sales – COGSread more 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 industryCyclical IndustryCyclical industries refer to those businesses whose performance efficiency is highly correlated with or sensitive to the economic cycles. These companies grow when the economy is in the growth or expansion stage and declines with an economic recession or depression—for instance, automobiles, aviation, construction industries.read more, 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 assetTotal AssetTotal Assets is the sum of a company's current and noncurrent assets. Total assets also equals to the sum of total liabilities and total shareholder funds. Total Assets = Liabilities + Shareholder Equityread more 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 –
50.3K subscribers