Introduction to This Financial Modeling Techniques:
Mr Raj, a research analyst prepared a financial model on company ABC and unfortunately got sick and went on leave. During his absence, the market moved exactly opposite to his expectations and the financial model of company ABC required the changes as per the current situation. Due to Mr Raj Absence, his assistant Mr Saurabh is asked to incorporate the necessary changes in the financial model of company ABC.Mr Raj knew how to prepare a financial model but he lacked knowledge on important financial modeling techniques.
Mr Saurabh opens the model and gets confused looking at the model as he is not able to find out which one is the right cell in which changes needs to be incorporated. In some cells, due to interlinkages, there is no value that can be seen.
What do you think why did Mr Saurabh faced a lot of problem with the financial model. Do you think a model which another person is unable to understand is a good model?? According to me the answer to this question is No.
A good financial model should always be:-
- Realistically based on reasonable and defensible assumptions and projections
- Flexible and adaptable to dynamic working schedules (or modules)
- Easy to follow, should not intimidate the reader
Wondering how can a model have these features. So let’s learn some important financial modeling techniques and make a model flexible and easy to understand.
Financial Modeling Techniques:
A financial model represents the financial performance of a company for both past and future. Models being very cohesive it’s also advisable to build a financial model in excel. Knowledge of Excel, knowledge of accounting and knowledge of financial modeling techniques, corporate finance, understanding the company’s operations are some of the financial modeling skills sets required in an individual in order to build a model.
financial modeling techniques are as follows:
Financial modeling techniques 1 – Historical data
Your assumption for the future years is based on your historical. So it is very important to gather the right data from the right source. While gathering data remember one thing you are an analyst, not an auditor. So if the annual reports published by the company do not tally don’t panic and sit to tally them.
Financial modeling techniques 2– Assumption
Financial models need to have clear and well-defined assumptions which are Referred to as ‘drivers’ or ‘inputs’ these are based on a thorough understanding of the business
Assumptions should reflect business realities and expectations
In order to come up with an assumption analyzing the historical plays a vital role. To analyze the historicals one should do ratio analysis of the company financials and come up with answers to a certain question like
- Whether a certain ratio has declined or is growing
- What are the reasons behind this declining or growing percentage
- How would it affect future
The other criteria which one should consider while making an assumption are
- No bias should get into the assumptions on the business
- Clearly, understanding the expected changes in future performance
- Understand Management expectations
- Check out what other analysts think about the company
Financial modeling techniques 3 – Color coding /Linkages
Formatting is very important in anything you prepare. In financial modeling, colour coding is one of the formattings which one needs to take care of.
Let’s consider an example and try to understand why colour coding is so important.
You have prepared a financial model but the colour of all the numbers are same and you are on leave. There is some very important news that has been published which would change the assumptions that you had made for that particular company and your colleague wants to come up with the target price. In order to come up with a target price, your colleague has to change certain things in the model. Since it has the same colour throughout your colleague is finding very difficult to find the right cell in which changes need to do.
What can be done to overcome this situation?
A right colour coding would solve this problem. So there should always be different colour coding for Historical inputs, formulas and linkages. This would help your colleague to understand the financial model and make the necessary changes in the right cell.
We have used certain colour coding
Historical inputs in Blue
Formulas in Black Linkages in green
Financial Modeling Techniques 4 – a Circular reference
A circular reference is a series of references where the last object references the first, resulting in a closed loop.
Got confused let’s understand this with the help of an example.
We need to calculate the net income from the income statement. While calculating the net income, interest income is one of the items that need to be calculated. We are calculating net income as a percentage on the ending cash and cash balances which get calculated in the cash flow statement. Over here we are assuming that the entire cash balance we have deposited in a bank.
|Income Sheet (Rs m)||Year 1|
|(-)Selling, General & Admin Costs|
Here to calculate right net income we need to calculate interest income. Interest income will not be calculated unless we prepare a cash flow statement. So let’s see what is required to prepare the cash flow statement.
|Cash Flow Statement||Year 1|
|Change in Working Capital|
|Cash Flow from Operating Activities|
|Additions to Intangibles|
|Cash Flow from Investing Activities|
|Issuance/ (Repayment) of Long-Term Debt|
|Issuance/ (Repurchase of) Equity|
|Cash Flow from Financing Activities|
|Net Change in Cash|
|Beginning Cash Balance|
|Ending Cash Balance|
So we can see here we need net income to calculate the ending cash balance which will be used in calculating interest income.
|Cash balances||Year 1|
|Average Cash Balance|
So, let’s see how will we do this
First, we will calculate net income by considering interest income to be 0.
This net income will get linked to the cash flow statement through which we will be able to find the ending cash balance
Then this ending cash balance will get linked to average cash balances which will help us to calculate the interest income
Later we will link this interest income to the income statement and find out the right net income balance.
So you must be wondering whether the new net income figure will get reflected in the cash flow statement.
Yes through circular reference this entire process will be done automatically and accordingly the other figures in the income statement, balance sheet and cash flow statement would also be changed.
But remember one thing excel cannot calculate automatically when the model contains a circular reference
We need to Turn ON “Iterations” in order to resolve the situation
Let’s see how do you do that
File >>>Options >>>> Formulas >>>>> Enable iterative calculation >>>> OK
For in-depth knowledge of financial modeling techniques, you can refer to our Financial Modeling Training course
Financial Modeling Techniques, Infographic
Learn the juice of this article in just a single minute, Financial Modeling Techniques Infographic
This has been a guide to the Financial Modeling Techniques which are easy to remember. This post is a complete heads-up on Financial Modeling Techniques in order to make a financial model easy to understand.
Here are some articles that will help you to get more detail about the Financial Modeling Techniques so just go through the link.
- Why is Credit Research Analyst Jobs Opportunities
- Financial planner business plan | Top 6 tips | Benefits | Drawbacks
- 24 Most Valuable Steps of Financial Planning Success
- Why Is Financial Education Your Best Investment?
- Best Financial Modeling Skills
- Guide to Gross Profit Margin Formula