Module 13   Integrated Financial ModellingChapter 3

Historical Data

View chapters →

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.

My folder with the annual reports looks like this (I know this is basic stuff, but I’m posting an image just for clarification) –

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? Well, take a look at the below screenshots.

Balance sheet as stated in March 2016 –

 

Balance sheet as stated in March 2017 –

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. This is the case in the above two snapshots. However, for the Financial Year 2018-19 –

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; here is the balance sheet snapshot as per the 2020 Annual Report –

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. My sheet looks like this –

 

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. Please take a look at the snapshot below; I’ve typed out the assets side of the balance sheet.

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 in the following image –

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. Here is the snapshot from the annual report –

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. But I do hope your P&L would look like this –

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.

Download the excel sheet used in this chapter here.

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

 

 

 

 

103 comments

  1. Gaurav says:

    sir , I had a doubt that whenever I try to get data from annual report I cant copy it into excel its format changes (one word in each cell vertically) and when I download a excel format file and then copy paste it then the calculations don’t work because it says numbers are in text format and how much even I try they don’t turn to number.
    please resolve this I have this query for a long time (and writing them one by one will be exhausting and time consuming)

    • Karthik Rangappa says:

      Copy paste wont really work, Gaurav. It always leads to some sort of format issue. You need to type in each number, I know its a lot of effort but its worth it.

  2. Saurav(themodernmonk7) says:

    Thank you Sir and thanks for your hardwork.
    Sir, How long should we expect this module to be completed?

  3. Asha says:

    In FY19 the balance sheet total on Annual report is different ( 1611.43) vs 1604.56 that we got. What happened in that year ?.

    • Karthik Rangappa says:

      Can you double-check, FY19 AR Asha? I quickly did and the AR states the total as Rs.1604.56Crs. Btw, you must have got this from the FY2020 AR.

  4. Kallol Sen says:

    Sir, you are always be my hero, hats off, and respect 🙏🙏🙏🙏

  5. Deep_98 says:

    Sir,
    Firstly, thanks for sharing such good content and your knowledge with us.
    The question is off-topic but wanted to take your view on the current bull market which is constantly going up. Like what may be the possible reasons behind this?
    Also, if someone is looking to invest into a mutual fund (Equity) for a long term perspective, should they go for it or not and similarly for individual stocks?

    Thanks again for sharing complex concepts in such lucid ways’ sir.

    • Karthik Rangappa says:

      Deep, I’m not sure either. But I guess higher corporate earnings, higher GST collection and things like that is also getting factored in.

  6. Suraj says:

    Why “Keep the numbers left aligned”?
    Shouldn’t it be right-aligned?

  7. Aman says:

    Hello sr
    I have a doubt in FY16 data.
    Under Non current Assets,In financial asset you have mentioned” Long term loans and advances ” is ₹13.71cr whereas when I cross check it with actual balance sheet it’s ₹19.06cr , Further there is no “other financial Assets” but you have taken ₹5.73cr, then further” other Non-current assets” you mentioned is ₹5.59cr where as on balance sheet it’s ₹0.49 lacs and rest all are same in “Non current asset” part…….and few discrepancies are also there in” Current asset” part ……sr if I am wrong..then where I am getting stucked….I have already checked 2-3 times

    • Karthik Rangappa says:

      Aman, you need to look for it in the 2018 AR. As I mentioned, the company has restated 2016 and 2107 data in 2018. Can you please check this and get back? Thanks.

  8. john says:

    Would you Provide Download as PDF Option for the Innerworth — Mind over markets?
    Would be helpful to read it as PDF.
    Thanks 🖤

  9. Kallol Sen says:

    Yes Sir, whatever I have learned from versity I immediately practice it in Excel, by doing this I improved my hand in Excel, right now practicing chapter 9, it seems tricky when I first visit the chapter, but by the time, I can understand the logic behind portfolio management. God bless you, my hero, Karthik Sir.

  10. Mohammed Adil Nizam says:

    Karthik sir, thanks for providing us this valuable insights and we thank you for your hard work and commitment. God Bless You

  11. Naveen says:

    Sir, the matter posted here is very informative and the minute details of a model u r sharing are very important and they are very useful for the model to be dynamic.. eagerly waiting for the next chapter

  12. Bhavin Vakil says:

    Sir don’t you think it is better to download the excel from screener and then work on the individual items. As far I know those numbers are reformatted as per latest annual report and are consistent.

  13. Aman says:

    In FY19 balance sheet ,under current liabilities “Trade payable” is ₹190.93cr where as in FY20 balance sheet,the “Trade payable” of FY19 is ₹171.92cr and similarly “provisions” in FY19 balance sheet is ₹3.69cr and same in FY20 is ₹29.67cr……..Now sr which one we have to consider

  14. Pramod says:

    Dear Karthik,
    Is it okay when there is a discrepancy in reporting total assets in FY19 from AR FY2018-19 and in AR FY2019-20? How much discrepancy is acceptable?

    • Karthik Rangappa says:

      You can check the notes for this Pramod to see why there is a discrepancy. But yes, small differences is ok.

  15. Aman says:

    Sir In profit and loss statement..For FY16, from where have you taken “advertisement and sales promotion scheme” as ₹58.82cr…
    One more doubt sr, In p&l statement as well as BS statement,you have taken data for eg for FY17 …………..from FY17 sheet data as well as FY17 data of FY18 sheets and both data of same FY have differences…now either we should stick with FY17 or FY18

  16. SamayS says:

    Hello Sir,

    Will you be talking about banks/ life insurance / nbfcs in your modules?

    How on can someone invest in them reading their quarterly statements etc?

  17. Samays says:

    Hello Sir,

    Banks/NBFC/Insurance companies pretty much are the main investments for MFs and PMS etc.

    So how could I invest in these companies without a understanding of their financials??

    How do these MFs and PMS do it?

    • Karthik Rangappa says:

      There are ways in which you can, its just that I don’t know how or at least don’t know enough to write about it 🙂

  18. Lipu Sahoo says:

    Dear Sir,
    First of all thank you for your hard work.Earlier i understood Excel workbook in chapter 02 but, now i don’t understand it how you have prepared.Honestly I understand content but don’t understand the Excel workbook.I spent around Three days to learn Excelworkbook you have prepared in this chapter but till i don’t understand it.Kindly Help me Sir.

    • Karthik Rangappa says:

      Lipu, understanding excel is a prerequisite. Unfortunately, I cant teach excel here, although I’ve tried doing a bit in this chapter.

  19. Saurav says:

    Please search on YouTube
    ‘Excel crash course for finance professionals” and learn basic excel for finance modelling.

  20. lipu sahoo says:

    No Issues Sir,
    Thank you for replying.

  21. Lipu Sahoo says:

    Thank you Sourav Sir for recommendation.

  22. Arun says:

    Waiting for the next chapter !!!!

  23. Suresh Thammina says:

    Karthik sir,

    Really appreciate all your efforts to educate retailers, Thank you!

    Wanted to request you to shed some light on qualitative analysis and key points to identify the next possible blue-chip companies.
    Usually, all generally say for example in 1993 if invested and stayed with Infosys or such company it would be made you rich. We need to look forward and say which is next Infy… It will be really good if you can take us in that direction as well.

    Thanks & Best Regards,
    Suresh

  24. Arun Mahendra says:

    Sir,
    There are differences in Tax Expense in your Excel working compare to Annual Report which is leading to differences in PAT.
    I think, you will need to edit that.

    Thank you

  25. Raman says:

    Figures of Trade Receivables for FY18 in Annual Report of 2017-18 (192.35 Cr) and 2018-19 (173.45 Cr) are different. Sir, Is it a typing error or something else? Please explain.

  26. Augustine Charly says:

    Being a passive index fund investor (lazy investor), I may not use this (Integrated Financial Modelling) myself, but I believe this high quality financial education you are giving away for the masses will make our financial market more efficient and rational. So I can sleep better.

    Thank you Karthik Rangappa

  27. Augustine Charly says:

    Hi Karthik Rangappa, can you please think of delivering financial education on YouTube? There isn’t much quality content on Indian context. It doesn’t have to be an expensive project. You explaining to the camera, along with images you already got in your articles will be more than enough.

    • Karthik Rangappa says:

      We are thinking about this, Augustine. We will probably collaborate with someone who can take our content and produce these videos for us.

  28. Harsh Dhiman says:

    Dear sir,
    If i choose a company with subsidiaries as a Main model, then i have copy data from standalone statements or consolidated statements?

  29. SamayS says:

    Hello Sir,

    You mentioned that there are different ways one can invest in banking/nbfc/insuarance companies.
    Apart from trading them on a momentum basis, is there another way I could understand the financials of these companies?
    I am extremely eager to learn.

    • Karthik Rangappa says:

      You will have to do FA on these companies, its just that I’m not very confident talking about it, hence I avoid these companies 🙂

  30. Ayush Rout says:

    Sir, when is the next chapter coming

  31. Rahul says:

    I agree this is pure art. We have just started our canvas and as master painter you are guiding each step of the way. “Hold the brushes upright and dabble.” Thank you so much Karthik sir, I am so lucky to be here.

  32. Harsh says:

    Hello sir, First of all thanks a lot for all this. I have learned a lot from you.

    My question is this:
    How is being a SEBI registered investment advisor or research analyst as a career? To get registered, post graduation is neccesaary. I am currently in final year of graduation. After that, I am planning to do MBA finance, which will take two years. In the meantime, I will keep learning about markets and investments. I have been learning so already from past 2 years. After, I get postgraduated I can become an independent registerd investment advisor or resarch analyst. What do you think about it? Does it sound practical and is there “SCOPE” (hate to bring this word, but couldn’t find any alternative) in being an independent advisor or analyst? Do people in India pay for financial advice or they still want it for free??

    Sorry, if the question sounds naive but any guidance will be greatly helpful. Thanks.

    • Karthik Rangappa says:

      Its not a bad choice if you have the inclination for it. But it will take many years for you to build a reliable client base and your income levels will be volatile during the initial years. I’d suggest you speak to an RIA to understand from their perspective before jumping into it. Good luck.

  33. Harsh says:

    Thanks sir for guidance.

    Maybe, I can start a blog website on investments for now, so that when I start the advisory business after two years, I already have some target audience.

    I don’t have contact with any RIA but I will try. What according to you is the most important thing that can make or break such a venture?

    I am inclined toward this because of my interest in markets and my entrepreneurial inclinations. Do you have any other suggestions for me which can fulfill both?

    • Karthik Rangappa says:

      The most important thing is building trust. Trust is built over time when you do things with the right intentions, Harsh. Before you start out, maybe you should try working for an RIA for few years to understand the nuances of the job.

  34. Hani says:

    Hello Sir,

    I hope you are doing well.

    Can you make all your edits in a different color so one can see what has changed?

  35. Harsh says:

    Thanks Sir for your golden advice. Reminded me of the dialogue from Scam 1992. “Dhandha dharam h sir, aur is dharam ka bhagwan h trust (Business is a religion and the god of this religion is trust)…. The most expensive thing in this world is trust. Cheap people can’t afford it.”

    I wish I could have a personal Q&A session with you to clear all my doubts and queries. Anyways, I will trouble you again if any other stupid question pops up in my stupid mind:)

  36. Shivansh Agarwal says:

    I am taking globes spirits for my model is it okay for a beginner or should I change it

  37. Shivansh Agarwal says:

    the company I am talking about is GLOBUS SPIRITS due to autocorrect it was written as globes spirits, so should I take it or not

    • Karthik Rangappa says:

      I’ve layed down the criteria to select the company. See if the company fits this description. If it does, then go ahead.

  38. sorabh dhiman says:

    karthik sir, lots of respect

    i have one question,isn’t there any tool available through which we can convert these reports into our desired excel formats ?if not.

    then how companies like screener.in do it? manually?

  39. Korbind says:

    Hello,
    Any idea when this module would get completed?

  40. Korbind says:

    Hello,
    Any idea when this module would get completed?

    Like how often would each chapter get added?

  41. OMkar says:

    when will next part come sir?

  42. SD Naik says:

    Hi, could please let me know, when we can expect personal finance module in the application?

    • Karthik Rangappa says:

      We are developing the illustrations, once its done, it will be uploaded. Hopefully by this month end.

  43. Shivansh Agarwal says:

    while extracting the historical data I copied the figures for the years 2017 & 2108 from the balance sheet of 2018 but when I opened the balance sheet of 2019, the figures of two or three items for 2018 were not the same as in the previous year balance sheet, what should I do.
    the company I have taken is united spirits

    • Karthik Rangappa says:

      But why did you take 2016 and ’17 data from ’18? I did it because the company had restated the numbers in FY 2018.

  44. Suraj says:

    Hi Karthik!
    I have been learning from varsity for almost 1 year now and I love it!

    Question:
    I am planning to do freelancing in financial analysis and financial modelling.

    So, after completion of this (financial modelling) module and doing practice, is it a good idea to use this knowledge for building financial models in freelancing? I am serious about this.

    Thanks!

    • Karthik Rangappa says:

      Suraj, I’m glad you liked the content. But I really dont know if there is a market out there for something like this.

  45. Dinesh says:

    I’m like one of your friends who started with Hindalco – I started with ONGC couple of years back and never really got to cross the halfway mark. With this module, I resumed fin modeling again and would really love to finish this time. Please bring up the next chapters soon! 😛

  46. Shivansh Agarwal says:

    I didn’t take the no.s for 2016 as I am going for the last 5 years including 2021.
    what I am trying to say is that in the 2018 balance sheet and 2019 balance sheet the no.s are different for the year 2018 for some of the items and this same goes for the next year balance sheets when compared with each other

    • Karthik Rangappa says:

      Understood. I’m guessing the difference is not much. You can take 2018 data from 2108 AR itself or from 2019 AR, it wont really make a big difference in your model.

  47. Shivansh Agarwal says:

    Okay. I will do the same but what is the reason for the difference in no.s

  48. Mohammed Adil says:

    Thanks a lot Karthik sir for your hard work !! 🙏🏻

  49. Bhuvanesh says:

    Sir, One Request
    Why “Innerworth – Mind over Markets” not available for download ?
    After download, it becomes easy to write comments in specific section of the Module.
    Thank you so much..

  50. Dr Harshhan says:

    Hi, Karthik.
    Before even I looked at what company you have selected, I renamed my Excel sheet for Relaxo and, what a coincidence!
    Maybe I’ll rename it as Bata and do my own analysis for Bata, setting your Relaxo analysis as an example. 😉

    P.S. I was doing a model suicide earlier, after reading the Fundamental Analysis. And that suicide Model already has data entered for 2 companies including Financial Ratios. 😀

    • Karthik Rangappa says:

      Haha, but as you may have realised, Relaxo is a helper model. I won’t be continuing with that example 🙂

  51. Dr Harshhan says:

    Yes, I read the next chapter too. I never went in for predicting the flows in the future in my old model, but I did do some coloring, font changes, and a different way of indexing, etc. for easy reading of the data and even calculated values using the formulas to calculate the ratios mentioned in FA module, though I just copied values from the Annual Report directly.

  52. Chaitanya says:

    Actually i am repeating the same task with another company and in that there is one point that company had discontinued operation (which is always in non recurring nature). So what we should do in that cases (i mean is it going to impact our model???)
    Company Hindustan Copper (Single commodity company)
    Please help!!!! 😊😊
    Thanks in advance.

  53. Vaibhav raj says:

    Sir, is it important to make excel coz we can get 5-year data in screener.in, right?

Post a comment