Historical Data

3.1 – Annual Report recce

Picking up from the previous chapter, now that we have our excel sheet set up, we will extract the data from the annual report to our blank excel sheet. The excel sheet at this stage should look like this –

And a similar page set up for the profit and loss statement.

Now, before we start extracting the financial statements data from the annual report to the excel sheet, we need to conduct a simple survey of the annual report. Remember, for our financial model; we need the historical financial data from the last five years. We will use the data of the last five years as the primary input for the model.

It is essential to ensure that the last five years data is consistent and there no missing items in the statements. Let us understand this with a quick example.

Assume this is the revenue section of the P&L for an imaginary company –

Year 1 –

• • Gross Income
  • Duties
  • Net Income
  • Other income

Year 2

• • Net income
  • Other income

The company states the Gross income and duties paid in year one, but in year 2, the company states the net income directly. Inconsistencies like this can be a problem while modelling since it creates multiple gaps in the model. For this reason, even before we start copying the data from the annual report to the excel sheet, we need to first look at the last five years annual report and ensure that the statements are consistent over the years we are interested.

Let us go ahead do this now.

In the previous chapter, we discussed the ‘main model’ and the ‘helper model’. The main model is the one in which we will build a financial model end to end, and the helper model will help us understand concepts related to the financial model.

So I guess it’s time to introduce the company which will act as the first ‘Helper Model’.

We would be dealing with Relaxo Footwear. Relaxo is one of the largest manufacturers of footware in the country.

As a first step, I download the company’s last five years’ annual report and put these in a single folder. Usually, a listed company puts up the annual report in the ‘Investors’ section of the website. I’d suggest you download the same from Relaxo’s website.

I’ve even renamed these reports in a format that I like. I now go ahead and open all these annual reports side by side.

Please note, we deal only with the consolidated financial statements and not the standalone statements.

I’ll start by reviewing the consolidated balance sheet of the company. At the very first inspection, I can see that company changed the accounting format in 2018. How did I figure this? 

You’d probably know that in every annual report, the company states the numbers for the financial statements for the year in review and the previous Financial year. 

The company has restated the Balance sheet for FY 2016, 2017, and 2018. So as a financial modeller, I’d ignore the financial statement from the 2016, 2017 Annual report and take the numbers for FY 2016, 2017, and 2018 from the 2018 Annual report.

Next, when replicating the Balance sheet on excel, I’d take the line items as per the latest financial year. Let me explain why; 

Under current liabilities, there is a line item called ‘Lease Liabilities’, but this was missing in 2018 and 2019. But because it is present in the 2020 balance sheet, I will have to consider this line item and include it in my excel sheet; of course, the value against this line item will be 0 from 2016 to 2019, and INR 27.61 Cr in 2020.

I’m trying to suggest that if you take the line items as stated in the latest year annual report, chances are you’d have covered almost all the line items. But this is just a hack; it may not work all the time.

3.2 – Data extraction

Alright, with that in place, let’s start extracting the data from the Annual report to the excel sheet we set up in the previous chapter. Of course, this is a lot of manual work, but there is no other way around this. Remember, we start with a blank excel sheet; we’ve only set up the skeleton for now. 

To start with, on the left-hand side of the excel sheet, I type down the line items of the balance sheet. The order in which these line items are listed is the same order in which the balance sheet is reported.

Notice a few things here; I’ve used column A and B as an Index. I’ve typed out the heading and subheadings in these columns. I’ve highlighted what I mean by main and subheading here –

In column C, I’ve mentioned the actual description of the line item. There are two main reasons to do this –

• • Indexing and segregation of heading and subheading is an excellent way to present financial statements. It not just looks easy on the eye but also captures more information
  • Navigation becomes easy

What do I mean by navigation? When you have a lot of data to deal with, you need a quick way to navigate through it, and excel allows you to do that. I want you to do a small exercise to appreciate the ease of navigation.

By the way, I’m assuming that at this stage, you’d have entered the asset side of the balance sheet in your respective excel sheet, in the same way as I’ve done. If not, I’d suggest you do that quickly before reading further.

Now place your cursor in cell B5, where we’ve typed ‘Non – Current Assets’. Now, press the control key + the down arrow on your keyboard. The cursor should directly jump to the next indexed cell, i.e. ‘Financial Assets’.

This quick jump helps you navigate faster and focus on the primary data chunks.

I’ll proceed to set up the liabilities side of the balance sheet as well. So at this point, my balance sheet sans the values is set up. Here is the snapshot, but please excuse the compressed image; this is the only way I can present the entire balance sheet.

Once you’ve reached this stage, the next step is to copy the data from the annual report to the excel sheet. Please do recollect; I’m looking at the 2018 balance sheet to copy the data for 2016, 2017, and 2018.

Let’s deal with the ‘Non – Current Assets’ first.

I’ll go ahead and copy the same onto my excel sheet –

So far, so good, I suppose.

3.3 – Assumptions, facts, and format

In the image above, I’ve deliberately placed my cursor in cell E6 so that you can see that the number, 462.30, is copied from the balance sheet and not a calculated number. In a sense, this number is hardcoded.

In the financial modelling world –

• • The hardcoded number is considered as a fact because we are directly copying the number from the annual report
  • A calculated number is considered an assumption since we apply a mathematical operation to arrive at the number.

Given this, it is essential to distinguish between the facts and assumptions in a financial model so that the user of the model can quickly identify which numbers are flowing directly from AR and the calculated numbers. Also, you will know where to look in case of an error in your model.

I’ll explain how this can be done, but before that, let’s add up the total non-current assets.

I’ve used the ‘=sum()’ function in excel to calculate the total non-current asset. The calculated number is treated as an assumption since I’ve calculated this on my own. The easiest way to distinguish assumptions and facts is to colour code the numbers.

You can easily colour code this by selecting all the hardcoded numbers in one go. Click the function + F5’ keys on your keyboard; you should get the following pop up –

Now click on special, and select only constants and numbers like shown below –

After you click ok, excel will highlight the hardcoded numbers or the facts.

Now without deselecting the numbers, select a colour of your choice. I prefer light blue for this, but you can pick whatever you like –

After you select the colour of your choice, you can keep the total non-current assets in bold.

If you have managed to follow the above step, then the rest of it is pretty straightforward. All you need to do is extract the numbers from the balance sheet and P&L and put them on your excel sheet.

3.4 – Other things to note

Some of you may wonder about the necessity to add up the numbers and colour code them. For example, one can copy the total non-current asset as well; why calculate it separately?

We need to calculate these numbers because going further in the model, we will project each line item in the balance sheet for future years. The total is calculated anyway. Therefore, calculating it now will maintain consistency in the model.

Before we conclude this chapter, few formatting tips –

• • Keep the numbers right-aligned
  • Extend the decimal points up to two digits
  • Keep all the heavy numbers in bold; these are usually the subtotal and main total numbers
  • Use double borders for cells wherever necessary

I’ve completed filling up the balance sheet. I want you to pay attention to few last things –

I’ve calculated the total assets on the asset side by adding up the two subtotals, i.e. total non-current assets and total current assets. I’ve taken a similar approach on the liabilities side as well –

Lastly, to ensure my balance sheet is balanced, I run a ‘True’ and ‘False’ check. Remember, if assets = liabilities, that means the balance sheet is balanced.

Since it’s true, the total assets are equal to total liabilities. Hence my balance sheet is balanced. I’m not going to explain the data extraction method for P&L. It is a similar process. Do let me know if you get stuck on any of the steps; I’ll be happy to explain. 

If you are attempting the P&L, you will notice that the ‘other expense’ in the expenses section is expanded. I’ve done this deliberately to showcase that when you have a heavy line item in the P&L, then it probably is not a bad idea to break down its constituents. The reason for doing this is that we can model these lines items at a more granular level, thus ensuring our model is realistic.

Remember, Relaxo is the helper model, and this won’t be our main model. We used this to help us understand how data can be copied from the financial statements to excel. We will move on to the main model in the next chapter.

By the way, ‘Historical data’ was supposed to be the first step of financial modelling, but I hope you realise that many tiny little steps are hidden within the main step. You can expect the same for all the other steps.

As an assignment, I’d suggest you replicate the balance sheet and P&L on your own. I’m sure the learnings from this exercise will be exciting.

Key takeaways from this chapter

• • Perform an annual report recce before setting up the excel sheet
  • It makes sense to take the latest year’s financial statement for the format; the chances are that you will cover all the line items. But this is only a hack
  • Indexing helps in quick navigation
  • Differentiate between fact and assumption data points. You can do this by colour coding
  • Maintain format hygiene across the sheet
  • If need be, breakdown the heavy line to get a better granular view