How to make a Financial Model? Complete Step by Step Guide (2024)

Preparation of financial models requires precision and might take even more than 3 weeks of time to complete one fully.

Do you really think that preparation of financial models is a complex task and only financial analysts can perform that task? Or is it really tough to work on complex and lengthy financial models which are even time consuming?

Well, if it is performed in a proper sequential manner with proper steps, then the complexity breaks down into pieces and it becomes easier to make a professional model.

In the article below, I’ll give you a detailed step by step approach to prepare Financial Models.

STEP 1 : KNOW YOUR COMPANY

It is extremely important to first study about the company whose financial model is getting prepared. This basic study will serve as the base in preparation of the model.

You can learn about the company through:

1. Public sources,
2. Company’s website
3. Discussion with the management of the Company etc.
4. Published annual reports and analysts’ coverage reports (if the company is listed)
5. Regulatory Authority of respective countries (if the company is unlisted)

For example; If you are looking for a company which is based in India, then refer to the link of MCA (Ministry of Corporate Affairs) Website for extraction of the basic information relating to the company.

STEP 2 : UNDERSTAND THE INDUSTRY DYNAMICS

Next step is to read and understand the industry dynamics from industry analyses reports. It is required to first determine the right industry of your company as majority of the companies function as per their industry dynamics.

For Example; if you are looking at a company which is operating a QSR (Quick Service Restaurant) Chain in India, then you are required to look at consumer industry then dig down into further subsets of the industry.

STEP 3 : START WITH THE AUDITED NUMBERS

Once you are ready with your study on the company and the industry, first step is to start with the insertion of audited numbers (Statement of Profit & Loss, Balance Sheet & Cash Flow Statement) in excel sheet in a proper format for the last 3-4 years.

It is highly recommended to incorporate audited numbers for a minimum tenure of last 3 years as it helps in better consideration of growth projections.

STEP : 4 FIND THE ASSUMPTIONS

Next thing is calculate the past ratios like Revenue Growth, Expenses to a percentage of Revenue, Gross Profit Margin, EBITDA Margin, Working Capital Days etc.

Based on the calculation, you are required to forecast the same ratios for future years to calculate the forecasted numbers.

For Example; if your company has grown by a Revenue CAGR (Compounded Annual Growth Rate) of 20% for last 3 years and the Industry is also growing by the same rate for future years, then you can also assume the same growth rate of Revenue for next 2-3 years.

Assumptions need to be created in a separate tab for Revenue, Cost, Balance Sheet items and other numbers.

STEP 5 : FORECAST THE INCOME STATEMENT

Post finding the right assumptions, you should start calculating all the forecasted numbers of Statement of Profit & Loss (P&L) from top to down till all the expenses except Depreciation, Finance cost (Interest) and Income tax.

Till this stage, you will get the forecasted EBITDA figures on your sheet.

As a Financial Analyst, if you are having very initial rounds of conversation with the investors for fund raising, then forecasted numbers till EBITDA will suffice and you will engage in detailed conversation with the investors at later stages.

STEP 6 : PREPARE THE SUPPORTING SCHEDULES

Before jumping to working on Balance Sheet (BS) directly, it is important to prepare supporting schedules for Balance Sheet. These schedules may include:

1. Fixed Assets Schedule – To show the bifurcation of fixed assets
2. Depreciation Schedule – To show depreciation calculation on various fixed assets as per the relevant country laws
3. Tax Schedule – To Show bifurcation of current tax and deferred tax
4. Equity Schedule – To show bifurcation of equity and retained earnings and money required for funding
5. Loan Repayment Schedule – To show interest and principal obligation occurring periodically
6. Working Capital Schedule – To show the calculation of Receivables, Payables and Inventory
7. Schedule for other Balance Sheet Items

STEP 7 : COMPLETE STATEMENT OF PROFIT & LOSS (P&L) AND BALANCE SHEET

Next is to complete the projection of P&L and Balance Sheet through the referencing of schedules build in earlier step.

You can complete P&L after linking Depreciation, Finance Cost and Income tax from respective schedules. Similarly.

Balance Sheet can be completed through linking subsequent schedules except linking Cash & Bank Balance (Cash & Bank Balance will be calculated post completion of Cash Flow Statement)

STEP 8 : COMPLETE THE CASH FLOW STATEMENT:

Cash Flow preparation is the easiest part in overall Financial Modelling exercise.

Once the P&L and Balance Sheet (BS) are ready, it only leaves the task of incorporating formulas and doing the linking with P&L and BS for Cash Flow Completion.

Cash Flow Statement leaves a balance of Cash and Bank at the end of the year which will get linked with the BS’s Cash Balance and will complete the “Three Statement Financial model”.

STEP 9 : PREPARE FREE CASH FLOWS

Before reaching to this step, you have already succeeded in preparation of “Three Statement Model” i.e; P&L, BS and Cash Flow Statement.

Further task is to get into Valuations and Sensitivity Analysis.

For Valuation, you are first required to calculate Free Cash Flows to the Firm and Free Cash Flows to Equity through already calculated numbers from 3 statements

STEP 11 : PERFORM DCF ANALYSIS

Next step is to calculate Cost of Equity through CAPM (Capital Assets Pricing Model) Model using Market Rate of Return, Risk Free Rate and Beta along with calculation of Cost of Debt using Interest Rate and Tax Rate which will be helpful in calculation of Weighted Average Cost of Capital (WACC).

This WACC will be used as a present value rate for calculation of present value of future projected free cash flows.

STEP 12 : PERFORM SENSITIVITY ANALYSIS

Post calculation of present value of free cash flows, it is required to do the scenarios check through Sensitivity Analysis.

In this, you are required to calculate various valuation results through changing your assumptions in Optimistic as well as Pessimistic manner. This helps in drawing better conclusion on the authenticity of the assumptions taken.

STEP 13 : PERFORM RATIO ANALYSIS

Performing of Ratio Analysis is considered as a near to completion step. In this step, you’re required to estimate profitability, solvency and liquidity ratios for investors to take better judgement on the investing decisions.

For Example; In your ratio analysis, you need to calculate ratios like Return on Equity, Return on Capital Employed, Return on Assets to highlight profitability position of the company to investors

STEP 14 : PREPARE CHARTS AND GRAPHS

Now, it is the time to do some representational efforts. One way is to make required charts and graphs of the important numbers in your first tab.

As Chart and Graphs help in better interpretation of data, you can use them to show those intrinsic values which you want to highlight to your investor.

Investors usually don’t get that much time to spend on just one model, hence, it is recommended to depict important values in a visually comfortable manner, i.e; through graphs.

STEP 15 : FINAL TOUCH UP – (INDEX, FORMATTING ETC.)

As your model is complete now, the only thing which is left now is to do some minor formatting, insert statements & schedules into table, prepare an index with hyperlink etc.

You can also skip this part, but this step makes your model to look more professional and more attractive.

FREQUENTLY ASKED QUESTIONS (FAQS)