Indeed, becoming a pro in making financial models has a lot to do with time and experience, but that effort without goals and benchmarks is useless.
In this article, we will discuss, the simple tips, approaches and mistakes which you can avoid or incorporate and steer your way to mastering this skill.
Identifying the Approach
Blame the experience but 99% of beginners forget the most important part of financial modelling. i.e objective?
It is a challenge that we all have faced and the reason why you will not get it right the first time you start building a model is forgetting the most commonsensical step
The biggest mistake beginners make with learning financial modeling is not explicitly spelling out the objective of the model
Allen Aravindan,CFA
Top 5 Steps to Evaluate the financial model objective
What exactly do you wish to accomplish at the end of this model?
- Is it deciding what price you want to pay for the real estate commercial project?
- Or comparing two business opportunities?
- Maybe you want to calculate the fundamental value of a stock.
So write that down first clearly, you can also write some subordinate objectives that you want to accomplish.
Gathering Data
If your objecting in the first step we discussed was not properly identified then this step is going to be a nightmare.
Why do I say that?
Let’s take an example, let’s say you are creating a financial model to calculate the fundamental or intrinsic value, as we call it finance.
In this process we have
- Annual report and its subparts from MD&A, Schedules, footers
- Quarterly Reports
- Investors Presentation
- Con call and analyst transcripts
- Secondary brokerage reports
We can never really use all the data points, and there is a good chance you will be overwhelmed. The result is, that you will soon abandon the model itself.
Let me give you some tips on how to plan the data-gathering part
- Is the model objective based on an existing business, which you want to buy your ownership on?
- Or is the objective a prospective one? Which is to decide the venture itself?
Most of the financial models will fall into these two categories.
Existing Business
In case we are dealing with an existing business then the data points needed would be,(apart from the regular financial statements of the past)
- Revenue model data
We need data that can help us calculate or understand the crux of the business. For example, in the case of an IT Company: The data which can help us know the billing rate and data which can help us in understanding the human resource structure
So typical places to look for it would be the investor presentation, the con call transcripts, and quarterly reports. Hence you can see that very little can be found in the annual report itself.
- Cost Structures
In the case of an already operating business, cost data is usually not that difficult to understand. The way to look at this is to understand costs from two angles: 1) Direct costs 2) Indirect costs.
Direct costs can be particular to a company. For example, Indigo Airlines’ leasing cost is the most challenging thing to simplify. So you need data that can help you derive that.
So in case of indigo airlines
- Purchase of aircrafts in the future
- Per aircraft purchase costs
- Per aircraft leasing rent
Needs to be derived
Indirect costs on the other hand don’t need so much work. We can use common size analysis, i.e, dividing the specific expense over the revenue across years and catching the trend.
New Venture
Gathering data for a business that does not exist and our objective is to decide its fate is more straightforward in terms of research but demands the complication.
A model can be over complicated beyond repair, just because certain data is available
Allen Aravindan,CFA
Let me give you an elementary example of this. You want to create a revenue model for a restaurant business.
The wise thing is to understand that any retail-based business like supermarkets, restaurants, movie theatres are sq feet-based. Larger the space, the larger the volume. Hence the revenue model should be simple.
Revenue/ Sq Feet x Sq Feet
So if you want to create a financial model on this, the data required is the product list and its weighted average pricing. Likewise, it would be best to have the area under operation and future expansion of this space. But many beginners will try to create a model, estimating each bugger price and volume they can sell. This is called over-complication.
Deciding the Template
Once you discover your objective and the data required, the template gets decided basis this.
Simply put, What you need to decide is this?
Are we going to have a lot of subordinate calculations to the financial statements and valuation?
If yes, we create a multiple-sheet model, each representing a specific data type used in the model.
Don’t make too many sheets for small calculations. That will increase the linking work later, and debugging becomes a difficulty
Allen Aravindan,CFA
So there are basically two types of layouts we can use
- Single Sheet Layout
- Multiple Sheet Layout
Single sheet layouts are usually preferred in real estate because the underlying drivers are less complicated. Also, the focus of the real estate business is on cash flows, and accruals don’t happen.
This image above is the financial model of the shipping port, and its significant calculations happen in capital expenditure and operational expenses with regards to fuel.
Here is a different layout; although it has multiple sheets, notice how the assumptions or model data gets managed in a single sheet.
Apart from this the basic hygiene to maintain is to make sure
- You leave two rows above the model for model title and date.Make sure you leave two columns after your particulars for some assumptions to be written
You can check this free tutorial on creating financial model templates. The video also has a template that you can follow through to make it easier.
Foresee Complications
This is where experience counts but I am going to share that experience so you don’t have to wait to experience it!
Hope that made sense!
Anyway, as experienced financial modellers, if there is a word like that you learn the mistakes you made at the start of the modelling process and hence use that experience to correct the approach at the start of the process itself.
A model can get complicated for the following reasons, which you should keep an eye on. So what to keep an eye on and warning signs.
- Using too many conditional operators
A model will become very difficult to evaluate later if you have used a high number of conditional operators.
In fact, while creating those conditions, it may seem right and easy, but you need to keep the user in mind(which may not be in most cases you)
- Hard Coding
Often there can be instances where you might skip the process and add assumptions in ad hoc spaces in the model. The problem with this is that later, you want to tweak the model or check what is going wrong. It will feel like finding a needle in a haystack. So follow the assumption sheet rule. Keep all your assumptions that are static in one place.
Even if you would like to see those assumptions in the calculation area, make sure to link to the main sheet. Here is an example
- Too Many Assumptions
The model is as good as its assumptions & often, analysts feel that their assumptions are the truth. However, we must understand that this model itself is an assumption, so the best thing to do is research your assumptions well and take the conservative approach.
Being conservative will give you buffer for your assumption when all hell breaks loose.
Allen Aravindan, CFA
- Curve Fitting
Another problem is the bias, which slowly creeps into the model. This happens because there might be an answer which we feel we are expecting.
That’s okay! But the next problem is that the analyst, on not finding the expected answer, will try to fiddle with the assumptions and try to feel happy that his model works.
Make sure you are always aware of this subtle change in the approach of the financial model.
Sense Check Tips
What is a sense check? Analysts popularly use sense checks to critic their model.
Its basically trying to see the result and asking yourself
Does that make sense?
This common and very nontechnical step can save you lots of embarrassment.
Let me give you some examples to show how this should feel
- Getting an IRR of 200%- does that make sense? Or is it because we assumed meager investments?
- Getting an intrinsic value ten times higher than the market price. Is the intrinsic value too high because your assumptions are too aggressive, or may your discounting rate be too low?
- The model is too good to be true? Maybe it is. Often it is. Out of experience, I can say even a well-researched model is mostly 60% replicative of the real world. That’s the best case.
Concluding Remarks
Hopefully, these simple tips and guidelines will help you create better financial models. Remember that financial modelling skill is not a one-day stunt that you can pull off.
It takes practice and awareness, and an open-minded attitude to learn. So best of luck, Happy modeling!
