Module 13. Integrated Financial Modelling

1. Introduction to Financial Modelling

Chapters Next →

1.1 – Unusual approach

We are all living in a very uncertain and unprecedented time. Covid 2^nd wave has been brutally devastating and has caused a lot of pain and misery to humanity. I hope you are your family are staying safe. Please double mask if you really have to step out. I hope humanity does not have to face this situation ever again, and we get out of this situation as quickly as possible.

Let me start this module on Financial Modelling with an apology. I know this module was due for a while now. I know, I’ve taken a lot of time to get started on this. There were multiple reasons for the delay, but that’s all behind now. Here we are, all set. I’m super excited to deliver this module, and I hope you are excited as well 😊

But there are a few things to note before we get started –

Financial modelling as a subject is taught either in the classroom or in a video format. There is a reason for this – while teaching this subject, at any given point, we tend to open up multiple threads and then tie it all together in the end. So in a sense, there are hops, jumps, crisscrossing, and a bit of number juggling. Given the nature of this subject, it makes sense to teach this online or via a physical classroom setup.

Think of it as producing a movie. I’m sure you understand that a movie is not shot scene after scene in a sequential manner. Different scenes are shot, songs are recorded, action scenes are shot, edited, and then patched together and eventually made to look like the entire movie was show scene after scene.

In a sense, financial modelling is very similar. You will understand this better as we dig deeper.

I don’t know if financial modelling is taught in the classic article format. I could make a huge mistake attempting this task, but I think it is worth the shot.

As I just hinted above, the learning won’t be sequential. We will have multiple threads open; numbers will crisscross and move from one sheet to another, adding to the non-sequential learning format. But that’s the way this will go, so please be prepared for it.

As we progress through, you will realise that Financial Modelling is more of an art form than financial science. We throw in a ton of assumptions while building any financial model. The assumptions may vary from person to person based on the individual’s experience.

However, the good part is that the model we create will very easily accommodate changes and updates; this flexibility makes financial modelling a beautiful endeavour.

1.2 – What are you learning and why?

Perhaps an essential question – what is ‘Integrated Financial Modelling’, and why do we need to learn this?

Think about a typical company; as you can imagine, the company can have several moving parts. For example, a manufacturing company can have a team procuring raw materials, workforce to manufacture goods, admin team, finance team, regulators, compliance, marketing, supply chain, distribution, R&D, and whatnot.

Given the enormity, how do you break a company down into smaller parts and gain meaningful insights into its functioning? How do we gauge its efficiency?

Well, this is where financial modelling comes into play. Eventually, whatever the company does, it all boils down to numbers and metrics.

For example, successful operations lead to revenue generation, successful cost management leads to operating profits. Good financial practice leads to manageable debt levels; good supply chain management leads to better inventory management. Good dividend policy strikes a balance between a company’s growth and shareholder value. So on and so forth.

So the approach we take here is that if we can systematically analyze the numbers presented in the financial statements, perhaps it opens up a window to understand the company better.

When I talk about understanding financial statements, I’m talking about getting into granular details; we go line by line. Many often assume that a series of simple financial ratio analysis results in great insights into the company. Yes, to some extent, it does, but we can do a lot more to better understand the company.

Better understanding leads us to a better insightful investment decision.

Think about Financial modelling as a systematic way to understand the company. Here is what the name, ‘Integrated Financial Modelling’, means –

Financial = Indicates that we are working with the company’s financial statements

Modelling = Indicates that we are laying down a company’s financials systematically, connecting these financial statements and subjecting the same to a bunch of equations. The entire thing tied together is called a model, a model with specific input (financial statements) and a specific output (valuations).

Integrated = Implies that all the numbers are interconnected, and no part of the financial model is isolated. You will understand this better as we progress through building the financial model.

The end objective of any financial model is to help you build a perspective of valuation. The final output of the financial models is the company’s share price after factoring in everything that matters. You take the share price from the model, compare the share price against the market share price, and figure if the stock is fairly valued, undervalued, or overvalued.

The ultimate satisfaction is when you know that the stock is undervalued and available for a throwaway price in the market, trust me on that 😊

1.3 – Tools of the craft

Let me break the ‘not so good news’ first – to learn, build, and benefit from a financial model; it is mandatory to have some background knowledge about the following –

- How to read an annual report
- How to read the financial statements of the company – Balance Sheet, P&L, Cash Flow
- It would be best if you were comfortable working with MS Excel or any other software similar to MS Excel

The good part is that you can learn how to read the annual report, Balance sheet, P&L, and Cash flow in the fundamental analysis module.

Unfortunately, we don’t have a module on MS Excel, so please try and self-study MS Excel. If you are uncomfortable with any of the three topics mentioned above, please stop right now and learn these things before learning Financial Modelling.

Please do note, when I say you need to know how to read financial statements, I only mean that you need to know this from a user’s perspective. As long as you know the basics, that is good enough.

The same goes with Excel. It would help if you were good enough with essential functions and formats. I don’t expect you to have the knowledge required to build a complicated dashboard on excel.

The good news is that when I decided to learn Financial Modelling, I had no clue about the three things I mentioned above. I had to learn these things first and then get back to financial modelling. If a person like me can do this, then I’m confident anyone can.

By the way, financial modelling as a concept can be applied to any part of market finance, be it investing or derivatives trading. Financial modelling is nothing but a structured way of thinking through a complex problem; some even call this ‘Design thinking’ of sorts.

If you are a regular reader of Varsity, we have dabbled with Financial Modelling in the module related to Risk management and in the Trading systems module. It’s just that we never called it ‘Financial Modelling’.

This module, however, will be focused on Fundamentals and Financial Modelling for investments.

1.4 – The steps involved

At this point, I’d like to share with you a brief overview of the steps involved in creating an integrated financial model. These steps only give you a sense of direction. We will dig deeper into each of these steps as we proceed.

These are the steps involved in building a financial model –

Set up a layout – Perhaps the most crucial aspect of financial modelling. I foresee myself stressing on this several times throughout this module, so bear with me. A typical Financial model will have multiple excel sheets within a single workbook. We need to ensure our Excel workbook is appropriately indexed and formatted and the format stays consistent across the entire model.

For example, if I’m dealing with 2018 data in column ‘E’ of my excel sheet, I’ll ensure that column E across all the other sheets will always deal with 2018 data. Or here is another example of the layout, column A and B will be shrunk to ensure easy indexation across all the sheets.

At this point, this may come across as a bunch of vague statements, but you will appreciate these points as we progress along.

Historical Data – A rather painful task, but this need to be done. We need to download the Annual report of the company we are dealing with, preferably for the last five years. We need to extract the balance sheet and P&L data from the annual report and input this in our excel sheet. Of course, we will be dealing with consolidated numbers here and not standalone data.

Most importantly, please use the annual report as your primary data source and not any other 3^rd party data vendors.

Assumption Sheet – Remember I spoke about financial modelling as an art form rather than financial science? Well, we create an assumption sheet and dump all our assumptions in one sheet here. We assume things about the company should be close to reality; the further we go from reality, the more distorted our model gets. Let me give you an example.

Suppose a company’s revenue is growing at 7% year on year for the last five years; what do you think will be the growth rate for the 6^th year? If we have to assume something, it has to be in the region of 7%, unless you foresee a significant change. Anything higher or lower will distort the P&L from reality.

Asset and other schedules –Throughout the model, we create something called a ‘schedule’. We create a schedule with oversized line items. For example, the asset schedule deals with plants, machinery, and all the company’s fixed assets. We lay down the numbers in a systematic way and deal with them. For example, we extract the gross block number, depreciation, netblock, and even the CAPEX figures in the asset schedule.

So a single schedule gives us insights into multiple aspects of the company.

Like the asset schedule, we create other schedules such as – reserves schedule and the debt schedule.

Projections – Once the assumptions are complete and the schedules, we project the balance sheet and P&L for either 3 or 5 years forward. This is one of the crucial steps while building the model.

Cashflow derivation – Again, a very crucial step in financial modelling. In this step, we derive the cash flow statement using the P&L and Balance sheet data, called the ‘indirect method’, of cash flow preparation. Note, unlike the Balance sheet and P&L data, historical data of cash flow is not extracted from the annual report but instead derived. This step can be tricky; it sometimes works and sometimes does not work due to its complexity.

Hence we will also look at alternatives here.

Ratios – Once all the data is in place, we can quickly draw up ratios and charts for our model. The ratio sheet will include things like liquidity, solvency, profitability ratios etc.

Valuations – In the valuation sheet, we deploy the discounted cash flow method of valuation and finally value the company. Think of this step as including a model within a model. Of course, we will have sufficient checks and balances in places to ensure we are not going way off the mark, and even if we do, the sensitivity tables that we develop should help us get back on track.

These are roughly the steps involved in developing a full-fledged integrated financial model. While it makes it seem simple, trust me, it is not.

I’m excited to dig deeper. I hope you are too, so buckle up for the ride 😊

Key takeaways from this chapter

- A financial model takes in inputs in the form of financial statements and gives us an output mainly in terms of valuations
- Financial modelling involves a non-sequential learning path
- Multiple discussion threads open up while building a financial model
- Basic working knowledge of MS Excel, Balance Sheet, P&L, cashflow is mandatory before venturing into financial modelling
- There are 7-8 steps to follow while building a financial model
- The model that we build has to be flexible to accommodate changes and updates.