4.1 – Model integrity

I want to start this chapter by talking about a super important concept. I may have touched upon this topic earlier, but I would like to discuss it again with snapshots to emphasise its importance.

In the previous chapter, we set up the balance sheet and P&L for the helper model. Here is the snapshot of the same –

P&L –

And the balance sheet –

The model design ensures column E represent FY16 data, column F to FY17 so on and so forth. We do this to ensure that the numbers get identified quickly and linkages between cells are accurate.

For example, imagine a scenario wherein I want to calculate the ratio of Property, plant, and equipment to the Total revenue for FY18. If you realize, to calculate this, I need to divide a balance sheet item with a P&L item, which means I will have to crisscross between sheets to do the math. This further means that I can easily link the wrong cells without evening noticing it.

Anyway, let us go ahead and do this. I can easily calculate by linking the cells of Column G in the formula bar –

Now consider a situation where you’ve linked the wrong years while calculating this ratio. You can spot the wrong linkage easily –

In this case, I know column G in the balance sheet should be linked with column G of P&L. The moment I see the G and F combination, I know something is wrong.

I’ve quoted a relatively simple example here. But as the model grows and gets more complex, you’ll understand and appreciate the need to maintain the model integrity.

4.2 – Main model

It’s time to introduce you to the primary model. I’m sure many of you here would expect me to name the company we’d work on and also name the years under consideration. But I have different plans 😊

I’d rather keep the name and years under consideration unknown. I’m doing this for two reasons –

  • By not naming the company, I’ll hopefully eliminate biases one may have. For example, if I use a footwear manufacturing company’s data, some may feel that it may not apply to an auto component company. So I think it is better to keep it generic to establish the fact that this model template applies to all companies (except banking and NBFC)
  • Hopefully, by not quoting years, someone reading this module five years later will also understand that the overall structure of a financial model remains the same, no matter when you decide to learn financial modelling.

But for the sake of your understanding, assume that we are dealing with a simple manufacturing company’s data.

I’ve used the exact steps detailed in the previous chapter and set up the Balance Sheet and P&L data. Here is the snapshot of the same –

Balance sheet –

I’ve shrunk my excel sheet to 70% to ensure I capture both sides of the balance sheet; hence the numbers and format look a little different.

Here is the snapshot of the P&L –

A couple of things here –

    • The years in consideration is Year 1, Year 2, Year 3 up to year 5 etc. It means the latest 5 years of data. So even if you read this 10 years later, it won’t matter.
    • The data is from the Annual Report, as of March 31st,e. the financial year-end
    • Year 1A means Year 1 actual data. Year 6P means the year 6 data projected. The projected data is also as per March 31st. In a sense, this is our vision of how the financial statement will look like future annual reports

You can download the excel sheet from the end of this chapter. In the excel sheet, you’ll find the raw P&L and Balance sheet data; I’d suggest you use that data and lay it down in the format we’ve discussed. It will be good practice for you.

4.3 – Assumptions and Projections

Remember, in the first chapter; I mentioned that financial modelling is a bit of art and financial science?

The art part starts now 😊  

The idea behind a financial model, quite obviously, is to analyse the historical financial statements and project them forward. The common practice is to project the number to either three or five years forward. In this model, we will try and deal with five years projections.

To get an initial understanding of this, I’ll post a set of questions and answers –

>>>> How will you project the financial statements for the future years?

>>>> Well, you can project the financial statements by making a set of assumptions.

>>>> How will you assume these things to help you make the necessary projections?

>>>> We can assume the future trends based on historical trends.

>>>> How will you measure historical trends?

>>>>> The measurement of historical trends happens based on individual line items in the balance sheet and P&L. In most cases; we measure by taking a simple ratio of one line item over another. At times, we can consider the year on year growth rate as well.

 We will discuss this in greater detail later in this chapter.

>>>> After measuring the historical trend, how will you project the future trend?

>>>> There are two ways to make future projections – historical average or an intelligent guess.

At this point, I just want you to read the above and keep this in the back of your mind. Some parts may be clear, and some parts may sound confusing, but I hope by the end of this chapter, you’ll get a clear understanding of this topic.

With that in mind, let us go ahead and make our first assumption for the financial model, but before that, let’s set up our assumption sheet.

To set up the assumption sheet, please go to a new sheet in the workbook and rename the sheet to ‘Assumption’ at the bottom.

Now, we do the usual, i.e. –

  1. Index column A and B
  2. Expand column C
  3. Index column D
  4. Cells E2 to I2 will be Year 1 to Year 5
  5. Cells J2 to N2 will be Year 5P to tear 10P

I’ve followed the same steps, and here is how my excel sheet now looks.

The idea with the assumption sheet is to lay down each of the financial statements line items and project it based on our assumptions. So let us go ahead and lay down these line items. Let me start with the Balance sheet; take a look at these two lines in the balance sheet, i.e. liabilities and provisions under the current liabilities section –

Now, recollect this part from the QnA we had earlier –

To measure historical trends, we usually take the line item as a ratio of another line item. For the balance sheet, usually, the ratio is measured by keeping the ‘Gross Block’ as the denominator. Gross block, because the gross block is one of the most oversized balance sheet items, also sucks up the company’s CAPEX.

So, if you were to look at ‘Year 2’, liabilities as a percentage of Gross block,

Liabilities as a % of Gross Block (Y2) = 102.74/310.58

= 33.08%

Of course, we can do this in excel directly –

Notice, I’m dealing with Year 2 data. Hence in the balance sheet, I divide F6 over F34.

You may wonder why I’ve done this for Year 2 and not for Year 1. This is because there will be instances where we’d need to calculate the year-on-year growth rate, which means our starting point will be year 2. Hence, for this reason, we ignore Year 1 and directly deal with year 2. You will notice this pattern in several places throughout this module.

Alright, now that we have calculated  Liabilities as a % of Gross Block, we can drag the formula across Y3, Y4, and Y5.

As you can see, liabilities as a percentage of gross blow hovers between 27% and 35% consistently. So, if I were to figure out what this ratio would be for Year 6, I can just take the historical average and get a perspective.

Let me do the same –

Congratulations! With this, we have projected the very first line item of our balance sheet. Few things to note here –

    • I’ve used the simple average function here
    • The first average, i.e. for the year 6, is the average of Year 2 to Year 5
    • The 2nd average, i.e. for year 7, the average is between Year 3 and Year 6
    • We are calculating the rolling average here, so at any point, we consider the latest four years data
    • The average which we have calculated hovers within the expected range, i.e. between 27% and 35%, so this is ok.

Whenever you calculate such ratios, it is best if the variance range is narrow. The narrower the range, the more consistent is the average calculation. The more consistent the average, the tighter is your model.

I’m not too happy with a range, i.e. 27% to 35%; it could have been better. If you are not too happy with it, you can try exploring other ratios like ‘labilities as a percentage of total assets or as a percentage of netblock or something like that.

Wait! So what should you consider? Liabilities as a % of the gross block, or netblock, or total assets?

Well, this is where the art form kicks in. There is no guiding principle here. There is no rule which says you have to consider the denominator as gross block only. I’ve taken it because I’m comfortable with it.

The end objective here is to ensure the calculated numbers are as consistent as possible. Also, don’t stress too much on this; after all, this is a financial model based on excel. We can change things at any point during this journey.

I’ll now go to the next line item, i.e. the Provisions under the current liabilities. Again, I’ll calculate provisions as a percentage of the gross block.

Hopefully, you get the drift by now.

Let us go back to the balance sheet for a bit –

Under the liabilities side, we have projected Provisions and Liabilities. What’s next is shareholders funds and non-current liabilities. Usually, big-ticket items like these in the balance sheet should be dealt with separately in the financial model. We deal with it by creating something called a ‘Schedule’. Of course, we will talk more about schedules later in the module, but for now, think about schedules as a separate dedicated sheet within the financial model.

So all the things marked is treated in the schedule, where we will also make future projections. That leaves us with just the deferred tax liabilities on the liabilities side of the balance sheet.

For the deferred tax liabilities, I’ll consider the year on year growth rate. If you look at Y1 and Y2 numbers, it’s at 13.61 Cr and 16.95Cr. To calculate the year on year growth rate –

(16.95/13.61) – 1

= 25.55%

Note, this is the growth rate for Year 2. On excel –

Of course, you can now drag the cells for the rest of the years, up to Year 5, and take the rolling average from Year 6 onwards.

We now move to the asset side of the balance sheet. Perhaps, I’ll take it up on the next chapter, and I promise I’ll put up the next chapter soon 😊

You can download the excel sheet used in this chapter from here; please note, this excel also includes the raw data. I’d encourage you to use the raw data and build the P&L and Balance sheet from scratch.

 Key takeaways from this chapter

  • Please pay attention to model integrity, as it helps you identify accurate cell linkages
  • One can calculate the historical trends either as a growth rate or by taking a simple ratio
  • Projections are made by taking averages or by making an intelligent guess
  • It is best when the historical trends exhibit a non-volatile range
  • Assumptions are an art form; there is no standard method to make assumptions. Your guess is as good as mine.

 

 

 

 




126 comments

  1. Laxminarayanan Sridharan says:

    Thanks for sharing the understanding to provide a common man the complexities of financial jargons. :-). I’ve a suggestion, can we have a top down view, I mean for reaching a conclusion, we have a set of steps to go through in the journey. If conceptually I can understand something like this:

    1. These 15-20 parameters are taken up from the past 5 years
    2. We would end up calculating ratios across the five years with Param A / Param B etc. leading to Param X
    3. The valuation of the scrip will depend upon Param X, Y & Z.
    4. We have a formula to finally come to an price (realistic) to be paid
    5. If the market price is > realistic => Overvalued else vice versa

    Then we get into execution of building the model step-by-step using excel (you can share the template etc.).
    This would keep concept and implementation separately and also give users a view of where are they being lead to.

  2. Omkar says:

    can u tell how many chapters are gonna there in financial modeling or when tentatively all chapters are gonna uploaded?….and Thank you for your effort in giving free excellent knowledge

  3. Omkar says:

    I just cant explain how happy I became when I saw u bringing financial modelling. I have learned fundamental analysis and options from varsity only and the way varsity is written is top notch.I cant thank you enough:)

  4. RAMANATHAN SESHAN says:

    thank you very much sir. I would request u to give us a model for nbfc, insurance and banking companies(1/2 chapters) as well after the complete outline is done. It would help us to understand as the business models better sir.

    • Karthik Rangappa says:

      I’ll try my best, if not me, I’ll try to convince someone to write a guest post on this topic 🙂

  5. Shivansh Agarwal says:

    the company which I have taken has not stated “gross block” separately in its balance sheet so how can I calculate it

    • Karthik Rangappa says:

      Shivansh, check the netblock in asset and see in the schedule. I’ll try and add the details in the next chapter.

  6. Harsh says:

    Dear sir, great lesson again. Thanks for the same

    I have one query, while reading , Step by step I also downloaded the AR of TTK .
    but there are some term which has not been explained in above chapter i.e.(Interest expense, Transfer to general reserves, Increase in stock)
    please guide me
    If possible, can you make a structure in which all terms are covered i.e. Which term comes under what part of AR .

  7. Harsh says:

    Dear sir, our extracted RAW data should be SAME like yours given in excel sheet?? or we can be flexible with it, because my chosen company have slightly other terms shown within it.

    • Karthik Rangappa says:

      You can be flexible with it. Think of what I’m laying down here as a guide, to give you a perspective.

  8. Shivansh Agarwal says:

    the company which I have taken has deferred tax liability only for the year 2020 and 2021 so how can I forecast it for assumption years

  9. Dr Harshhan says:

    I just realized that I have to check for the Gross and Net Block in the Notes to Financial Statements part. Never ventured in detail into the Notes part previously. This is a much needed module. Thanks, Mr. Karthik!

  10. dhruv says:

    when will other episodes for financial modelling will be uploaded?

  11. Vinod says:

    Thank you sir for this wonderful series. Words cant express the gratitude I feel towards you for explaining the basics with so much of patience. Looking forward for your next article.
    God Bless

  12. Aman kaushik says:

    Hey Karthik, first of all, thanks a lot for sharing such kind of precious knowledge with us. I have a doubt if you have time then please clear it.

    Doubt: You teach us how to create and fill data in an excel sheet that’s good but when you teach us to create an assumption sheet, I am unable to understand why do you take only three assumptions on the liability side as a percentage.

    if you find some time out of your busy schedule then I am again a lot of thanks to you.

    • Karthik Rangappa says:

      That chapter is only part 1, there is more to it and will put up the new chapter shortly. Also, few line items will be assumed in the assumption sheet, for the rest, as I’ve explained in the chapter will be dealt with in a detailed manner by building a schedule.

  13. DARRYL DENIS says:

    How to import data from zerodha pi for current, near, far month contract for equity futures

  14. Chandu says:

    Wish you a guru purnima sir!

  15. Hasan says:

    Dear Sir,

    Please upload the next chapter soon.

    Thanks

  16. Shivansh Agarwal says:

    is goodwill considered in gross block

  17. Shivansh Agarwal says:

    the company I have taken has some provisions under non current liabilities also, so should I consider them also while calculating the ratio provisions as a percentage of gross block

    • Karthik Rangappa says:

      Check the value, if its a high value, then add it to your assumptions, else a constant is also good.

  18. Shivansh Agarwal says:

    should “income tax liabilities” be taken into consideration while calculating the liabilities as a % of gross block

  19. Rijo Daniel says:

    Gross block is not stated in the balance sheet , so where can we get those information ?

  20. ANish says:

    Hi K,
    I couldn’t get the association between main model and helper model properly. Could you please help.

    • Karthik Rangappa says:

      I’m using the main model to build a full-fledged financial model and I’m using the helper model to explain other concepts which are not possible to explain with the main model.

  21. Subbaiya M says:

    Sir, admire your effort and knowledge sharing.
    Sir, if possible, pls provide the excel sheet as you mentioned in the chapter.
    “You can download the excel sheet from the end of this chapter. In the excel sheet, you’ll find the raw P&L and Balance sheet data; I’d suggest you use that data and lay it down in the format we’ve discussed. It will be good practice for you”

  22. ANish says:

    Okay.. thanks for the clarification

  23. Krish says:

    Thanks for this amazing module!
    I just want to ask to forecast the elements in current liabilities like trade paybles, can we directly take the average of last 4 years or we have to first get trade payble as a % of netblock and then do the further calculations

  24. SUBBAIYA. M says:

    Sir, Pls take this as a query or request
    Zerodha is an institution with vision.
    Is there a possibility of providing BSE/NSE certification for the learning we do at varsity. (Thanks to zerodha certificate)
    Since its a structured learning process we do at varsity. If there is an institutional backed certificate people can take this for job prospectus also.
    Thank you Karthik Sir for you tireless effort. I am learning a lot from varsity and executed fairly successful in trading with “STOP LOSS”
    Now trying to model the investment possibilities with your financial modelling course.

    • Karthik Rangappa says:

      I’m glad you liked the content, Subbaiya. The institutional certificate is something we wanted to do, either with the exchange or an academic institute, but the problem with this is that we won’t have autonomy on the content. Let me explore this once again, thanks for suggesting.

  25. Kamlesh Jain says:

    Hey?
    How to find the Gross Block of any company? Say I am going through the Financial report of a tyre company, and i have the data in terms of PP&E, Financial Assets, Intangible Assets, other tangible assets and other Non-current assets, so how to go about the same?
    Thankyou in advance for the answer.

  26. Sonu says:

    Hi Sir,

    In the main model you are taking ” Increase in stock” under Revenue, shouldn’t this come under the head change in working capital in the expense column? In case I’m misunderstood then could you please explain a bit?

    Thanks

  27. Manjunath Maiya G says:

    Respected sir,
    I saw a Q&A of Balance sheet not mentioning gross block and the answer I obtained the data from BS, now should I add the data to the excel as separate row for calculate with formula or calculate manually and add it to assumption sheet?

  28. MANISH says:

    SIR , THE Balance sheet in the main model and the in the previous chapter are different with data and headlines. I got confused as the balance sheet i hv prepared and in the main model are not matching up. E.g. liabilities section have many types of liabilities but the main model there is only one liability and provisions
    now am confused how should i calculate my side of liability

    • Karthik Rangappa says:

      Manish, let me look into this, I quite doubt there is any change, but let me check. Meanwhile, you can consider the excel from the latest chapter.

  29. Chandu says:

    Sir in excel workbook setup chapter we copied data in latest year format which also includes line items in this chapter the format is different
    should we prepare another seperate P&L and Balance sheet in the above format for doing line items

    • Karthik Rangappa says:

      It will help if you stick to the format we are discussing here, it will be better until you get a grasp on the topic.

  30. Yash Mittal says:

    Hey Karthik first of all thank you for this amazing module. I am fairly new to learning finance so I get stuck in understanding basic concepts. While doing this module you have mentioned that since we calculate year on year growth rate we wont take year 1. I mean I didnt quite get it, like we have data for Year 1 just like Year 2 so why leave Year 1?
    And if we had calculated Year 1 would we take average for 5 years for forecasted period and does years matter in terms of taking average?

    • Karthik Rangappa says:

      Yash, consider the following Revenue data –

      Year 1 – 100
      Year 2 – 175
      Year 3 – 225
      Year 4 – 260
      Year 5 – 300

      The growth rate here is (300/100)^(1/5)….so you consider all 5 years. Now consider this –

      Year 1 – 100
      Year 2 – 175
      Year 3 – 225
      Year 4 – 260
      Year 5 – 300
      Year 6 – 350

      Now if you have to calculate the last 5 years growth rate, it will be (175/350)^(1/5), where you leave the 1st year data.

  31. Ruturaj says:

    Hi Sir ,I have been following you from the very first model and i have completed prety much most of the modules.Was really excitedt to learn FM from you but Im confused and stuck 1st,2nd chapter are smooth but the assumption part confused me.
    1)The helper model that we built in first 2 chapters,do we need to make the assumptions sheet in that model or main model?
    2)The woorksheets that we made of Relaxo are they still used to take the chapter forward in assumptions chapter?Because your balance sheet and P/L sheet looks so different from the one we made of relaxo and if they are diffrent how did you made those sheets and where that data came from?how to?
    3)Where to find gross block (i found out that from comments above and tried to find gross block for relaxo but numbers were so fudgy)
    I please request you to help me out as i really wanna learn this from you sir and i cant move forward without understanding this!!
    Thankyou.

    • Karthik Rangappa says:

      1) The helper model is only to help you understand side concepts which would not have been possible with the main model. Hence I took the help of a helper model 🙂
      2) No, relaxo was a helper model. The main model is a dummy company without a name. The reason why I’ve taken a dummy company is that people reading it many years later can also understand the model without any biases
      3) Gross block is in the balance sheet, on the asset side (fixed asset)
      Please do give a read, I hope you find it helpful.

  32. Ruturaj says:

    Okay Sir,Thankyou so much.That was very kind of you to solve my doubt in such less time period,I appriciate it .Thanks

  33. Salman says:

    I do not see where I can download the excel. Can anyone help?

  34. Salman says:

    Thank you Sir for you prompt reply. Your explanation and guidance is beyond comparison. With your knowledge and delivery of material, I request you for another, more complicated model where the company is dealing in multiple products. I know, I am being greedy but I could only request.

  35. Salman says:

    Thank You Sir. Will wait for it 🙂

  36. Sonu says:

    To calculate the Gross Block of assets shall we consider only ” Property, Plant and Equipment” or should we consider every item in the Non-Current Asset those are, Property, Plant and Equipment, Capital work in progress, Right-Of- Use Assets, Intangible Assets, and Intangible Assets Under Development?

  37. Krish says:

    Hi Kathick,

    I am working on the Helper Model (Relaxo Footwear) simultaneoulsy by referring your Assumption section.

    Under the Assumptions (Part1):
    The company you are referring here is quite straight forward having the amount for Liabilities, whereas in Relaxo we have sub divisions under them. What are the things we need to consider for Liabilities as a wholesome? Do i need to add (Borrowings, Lease Liabilities and Other Financial Liabilities) to arrive at Liabilities amount?
    Please guide me.

    This how it looks in Relaxo.

    Liabilities:
    Non-Current Liabilities
    Financial Liabilities:
    Borrowings
    Lease Liabilities
    Other Financial Liabilities
    Provisions
    Deferred Tax Liabilities (Net)

    Current Liabilities:
    Financial Liabilities:
    Borrowings
    Lease Liabilities
    Trade Payables
    Other Financial Liabilities
    Other Current Liabilities
    Provisions
    Current Tax Liabilities (Net)

    • Karthik Rangappa says:

      Krish, so this is fine. Instead of 1 line item you have a bunch, treat them the same way as you’d treat liabilities.

  38. naresh chand says:

    sir you used the different raw data here due to which i unable to understand few steps which are skipped like secured and unsecured loans from which part they are taken, in current liability portion part of liability is taken or sum of all current liability so please can you use previous relexo result to cleary the main model

  39. Sanjeev Sandhya says:

    Hi Karthik,
    Thank you for the wonderful training on Financial Modelling. I have a query regarding the Deferred tax Liability. In the company that I selected, they have mentioned only Deferred tax asset(net) under Non-Current assets in the balance sheet. There is no separate entry for Deferred tax Liability. How would i go about incorporating that in my modelling sheet? Please clarify.

    Thanks
    Sanjeev

  40. Rohan Jagtap says:

    Hi, How to figure out which line item to be assumed or scheduled and in balance sheet do we have to forecast each line item?

    • Karthik Rangappa says:

      Ah, this one you will get it by experience. If you are new to FM, then model the 1st company basis what we have already done and you can improve based on that.

  41. Sanjeev sandhya says:

    Thank you for your help karthik. 🙂

  42. Tamizh Selvi S says:

    Hi Karthik,

    In Relaxo, there are 4 line items under “current liablities”

    Borrowings
    Lease Liabilities
    Trade Payables
    Other Financial Liabilities

    For this company,in the assumption tab,do we need to consider each seperately as ‘% of Gross block’ or the sum of all these as ‘% of Gross block’.Can you please clarify.

  43. Sonal says:

    Hi Karthik Sir,

    I have one doubt , actually the company in which I am working on, in balance sheet, there is no gross block showing directly in my balance sheet , it’s showing the following items:
    Under Fixed assets
    *Tangible assets
    *Intangible assets
    *Capital work-in-progress
    *Intangible Assets Under Development

    So, I consider only tangible assets for calculating the assumptions, so, while considering the tangible assets for calculating do I need to deduct depreciation from P&L for making an assumption, “Liabilities as a % Tangible assets”.. Please suggest.

    Thanks in advance!!

    • Karthik Rangappa says:

      Sonal, see the associated notes for Tangible assets. You will get the split up of the gross and net block.

  44. Sonal says:

    Hi Sir,

    This is how Tangible assets has been seperated in Notes of Balance Sheet. How should I consider Gross block here?

    Tangible
    Owned Assets
    Land
    Buildings
    Plant and Equipments
    Furniture and Fixtures
    Office Equipments
    Vehicles
    Total

    Leased Assets
    Land
    Plant and Equipments
    Total

    Thanks in advance!!

    • Karthik Rangappa says:

      A simple way is to total all the tangible assets to arrive at the gross block. You can then look at the depreciation schedule and deduct it from the gross block to get the netblock.

  45. K V RAMANA REDDY says:

    I have taken infosys for calculation there is no gross block or net block in balance sheet what can i do?

  46. Sonal says:

    Hi Sir,

    The company I am working on, there is “Current tax liability” under current liability sections of balance sheet. How can I do assumption for this line item?
    Please suggest.

  47. Shreya Pandey says:

    I am not able to download the excel Sheets. Can you please drop the link?

  48. Sonal says:

    Sir,

    You mean to say that, I have to calculate “current tax liabilities” from balance sheet divide by Current tax from P&L, this is how i have to calculate the line item in current tax liabilities under current liability sections of balance sheet.

    • Karthik Rangappa says:

      You can calculate the current tax from P&L by taking historical averages. This will give you the current year tax in P&L. Use this line item to make balance sheet projections. For instance, current year tax (in P&L) as a % of current tax liabilities in the Balance sheet.

  49. Hrithik says:

    Provisions are given under Current Liabilities as well as under Non-Current Liabilities. So, when calculation ‘Provision as % of Growth’, which one should we use?

    • Karthik Rangappa says:

      Not sure if I understand your query completely. The % growth is specific to the line item, right? You can use it for the one you are dealing with.

  50. Krish says:

    Hello sir. In the primary model, are the balance sheet numbers assumed ? If not, can you please let me know where you got the numbers from ?

  51. Vishal says:

    Hi Karthik,

    First of all thank you for sharing such a valuable knowledge with us.

    I have a problem I am unable to find the link to download the excel sheet of balance sheet that you have created above can you please share it.

  52. paras says:

    What we can say is when we are capturing the historic trend of any line item the range of it should be as narrow as possible.However, it may be true if we are considering it as a %of something but in case of YOY it may vary drastically as in the case of Deferred Tax.So can we say that the range factor should be considered in case of Dependent Line items and for Independent Line items it may vary..

    PS:Dependent and Independent Line items may further change from Person to Person.

    Yours thought’s on this?
    Thanks in Advance

    • Karthik Rangappa says:

      Of course. What we need to look for is consistency in trends. Please evaluate this on a case to case basis.

  53. Tamizh says:

    Hi Karthik,
    For the net sales value , can I consider “Revenue from operations”(which also includes Scrap Sale,Export Incentives and Other Operating Income) in P/L statement.

  54. Tamizh says:

    Hi Karthik,
    Can the “intangible asset” can be dealt in “assumption sheet” itself or dealt in “asset schedule” sheet

    • Karthik Rangappa says:

      Yes, you can create a section within the asset schedule to evaluate intangible assets.

  55. Sakina Hussain says:

    Hello sir,
    I am making the same assumption sheet for Relaxo as well. Some of my doubts were cleared by other comments but one major issue I am facing is understanding which line item will be a per cent of Gross block and which will be a per cent of net sales, also are there any other line items that will be a per cent of depreciation. Also, whether I take the exact depreciation amount from the notes or the depreciation and amortisation amount would work for deferred tax liabilities.

    • Karthik Rangappa says:

      Sakina, my answer may disappoint you, but really, there is no straightforward answer to this. You can experiment with different denominators and choose the one that works well for you (basically, which shows consistency).

  56. Sakina Hussain says:

    Thank You regardless sir

  57. Jeevanandham M says:

    Liabilities as a % of Gross Block (Y2) = 102.74/310.58 the excel sheet formula is not working for me. it shows that the formula is in error, but I understand I need some training in excel first.

    • Karthik Rangappa says:

      Yeah, I think being comfortable with Excel will help you learn Financial Modelling faster.

  58. Jeevanandham M says:

    Hello sir,
    I hope you will answer me. Should I include only the things you mentioned in your main model assumptions sheet? Liabilities as % of gross block, etc?
    Thank you so much in advance

  59. Ujjal Mandal says:

    Only thanks is not enough for these great efforts.I am very grateful to the Team that we are being taught the financials in a such simple and interesting way.

  60. Maharshi purohit says:

    Sir, how can we find gross block

    • Karthik Rangappa says:

      You can look at the balance sheet (assets side) for fixed assets, but do ensure you check the associated notes as well.

  61. Harsh says:

    Hi sir, at start of making assumption, p&l, balance sheets, it was very involving and new.
    Now, I think what’s the point of creating numbers and percentages, if I don’t know how to use them.

    I m confused 😄

  62. Raj says:

    The non current liab in my balancesheet is in form of
    Financial liabilities
    Lease Liabilities
    other financial liabilities
    Provisions
    Deffered tax
    Non-current tax liabilities (net)
    Other non-current liabilities

    I am not able to convert it in the form you have presented (secured loan & unsecured)

    • Karthik Rangappa says:

      Hmm, thats ok. You can retain the structure of model and model these line items in the balance sheet.

  63. Raj says:

    Ok thankyou sir,
    One more thing wanted to ask
    If Goodwill is not included in Gross Block than where should it come?

  64. Shubham Chauhan says:

    I want to understand a fact: Why did you decide to take “Liabilities as a % of Gross Block” to use as assumption and why not anything else? Why not make assumptions about Growth rate? Profit and loss? Earnings? Revenue growth? Why only this factor and why not any other?

    • Karthik Rangappa says:

      As I’ve stated in the chapter, these assumptions are like art, there is no science to this. You can experiment and figure other things that make more sense.

  65. Shubham Chauhan says:

    So, following what you’re saying wouldn’t the model get kinda biased according to the knowledge ofthe modeller. I consider myself as a beginner so it’s really confusing if I don’t get a clarity as to why thisone particular factor is important as such. But kudos to your efforts! 😀

    • Karthik Rangappa says:

      Of course it does, thats where experience kicks in. The more models you do, the better is your understanding on business, and therefore the better your model gets.

Post a comment