Beginner’s Guide- becoming a Pro in Excel Financial Modeling

A short summarized guide on becoming better at financial modeling. I share some of the hard lessons learned along the way.

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.

Content

Identifying the Approach

Blame thee the experiencee but 99% of the beginners forget the most important part in financial modeling. 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 opportunties?
  • May be you want to calcualte the fundamental value of a stock?

So write that down first clearly, you can also write some subordinate objectives that you want to acomplish.

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, lets 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 volumes 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, eachrepresenting 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 layout 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.

Multiple Sheet Financial Model Layout
Multiple Sheet Model

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.

Solar Businses Model Template
Solar Businses Model

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
Tempalte Orientation

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.

https://youtu.be/XwYcX_YBZi8

Foresee Complications

This is where experience counts but I am going to share that experience so you don’t have to wait to expereince it!

Hope that made sense!

Anways as expereinced financial modelers, if there is a word like that you learn the mistakes you made at the start of the modeling process and hence use that expereince 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 laterif 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 the 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

assumption linking
linking to main sheet
  • 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 check 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 modeling 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!


References

Default image
ALLEN ARAVINDAN,CFA

Leave a Reply