People have a misconception that Financial Modelling is a very difficult task and it takes a lot of effort to build one. Financial Modelling is often regarded as one of the most difficult exercises in the industry and some people find it nearly impossible to build one financial model.

Here, in this article, I will give you some useful tips which will make your model building exercise much easier. These are some of the easy steps to be adopted while working on a model that will make the exercise more fun. These tips will make any model look professional and will majorly avoid any chances of hassle being faced by the analysts and the investors/ evaluators.

1. Understanding of the Industry and Competitors

Some start working on the model directly post the company’s evaluation without even reading about the Industry. However, it is equally important to get a sense of the industry and doing a competitive company’s analysis in order to make the projections.

One should keep in mind that the projections should not be majorly off from the Industry standards and competitors unless there’s a specific reason to justify the same deviation.

It is always considered a good exercise to gather knowledge of other companies’ financials and ratios before projecting the target. This makes the model more reliable and in line with the Industry.

2. Colour Coding

Make different colour codes for the following to make it easily understandable by the model evaluator:

1. Audited Numbers
2. Assumptions
3. Ratios
4. Forecasting

These colour codes can be in the form of cell background colour, font colour, font size, font type etc. There are two major benefits for colour coding:

1. It makes a model look professional
2. One can easily distinguish the numbers in the abovementioned heads 

3. Make a separate tab for Assumptions

It is required to make a separate tab of assumptions in your model and you’re required to keep it separate from all the numbers. The assumption is the most critical part of the model and it serves as a foundation for the projections taken. Hence, investors want to specifically take a look at the assumptions taken.

4. Discussion with the Management

It is extremely important to get the assumptions validated by the management once. As management is running the business and they understand about the company better than anyone. Analysts can write the numbers on excel but it is in management’s hands to make those numbers real.

Analysts should properly discuss these numbers with management and take their observations into consideration. And, the model should be discussed with the majority of people in the management.

5. Make notes for any business point/ observation

A model can be built on various numbers and various scenarios, it is important to list all the observations and critical points in notes for an investor to understand the assumptions taken. Notes justify the answer for taking x assumption instead of y.

For Example; if a company has decided to source their raw materials from a different vendor which is offering them materials at a lower price. It will reduce the raw materials costing taken in Statement of Profit & Loss by some percentages. Mentioning the same reason in notes will give investor/ evaluator an understanding as to why the reduction in raw material prices is built in the model.

6. Break complicated formulas into pieces

Incorporating complicated formulas in a model makes it difficult for others to understand. To make a model more understandable, you are required to split all the complicated formulas or all the complicated steps into various formulas and steps respectively.

Breaking of steps will be slightly longer than the previous complicated method but it will be easy to operate while you will be working on the model along with making it simpler for others.

It there is any complicated formula used in the model, then it is important to provide its simple breakup in notes with proper examples.

7. Make “Index”, “Summary” and “Charts & Graphs” Tab

The model consists of several tabs and it takes hours to go through each and every model item in detail. Hence, analysts are advised to make a summary and charts tab separately for all the important numbers including Revenue, Gross Profit, EBITDA etc.

It gives a summary of the important values forecasted in the model and one does not have to go through the whole model to understand the business in brief. They can simply take a look at these two tabs in detail.

You can also insert hyperlinks on the index sheet for one to directly reach to the required tab by just clicking on the hyperlink. Also, hide some of the extra working files in your model for clean-up.

8. Audit the Model

Once you complete the model, get it auditing by a couple of people to remove all the unexpected errors. There are two ways of auditing:

1. Do audit yourself- By checking each and every formula inserted and the assumptions are taken.
2. Share with others for audit- It is important to get the model audited by at least 2 people because every other individual will come up with their own points and they will not miss something which you might have missed.

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)