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. –
- Index column A and B
- Expand column C
- Index column D
- Cells E2 to I2 will be Year 1 to Year 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.
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.
Thanks, Sridharan. In fact, I have discussed the steps involved in building a financial model here (Section 1.4) – https://zerodha.com/varsity/chapter/introduction-to-financial-modelling/. At this point I can only provide an overview at this macro level, because as you may have realized, there are many sub steps in each step that are involved.
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
I’d suspect at least 7-8 more chapters in this module. But broadly speaking the steps involved is discussed here – https://zerodha.com/varsity/chapter/introduction-to-financial-modelling/ (section 1.4)
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:)
Happy learning, Omkar!
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.
I’ll try my best, if not me, I’ll try to convince someone to write a guest post on this topic 🙂
the company which I have taken has not stated “gross block” separately in its balance sheet so how can I calculate it
Shivansh, check the netblock in asset and see in the schedule. I’ll try and add the details in the next chapter.
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 .
I’ve tried to do this in the Fundamental analysis module, Harsh, check this – https://zerodha.com/varsity/module/fundamental-analysis/
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.
You can be flexible with it. Think of what I’m laying down here as a guide, to give you a perspective.
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
You can keep it constant (2021) for the rest of years.
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!
I’ll address that in the 6th chapter, probably entirely dedicated to gross block.
when will other episodes for financial modelling will be uploaded?
I’ll try and upload the next chapter this week.
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
Thanks, Vinod. I’ll try and put up the next chapter in a few days.
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.
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.
How to import data from zerodha pi for current, near, far month contract for equity futures
Right-click, import to excel.
Wish you a guru purnima sir!
Thanks, Chandu! Means a lot 🙂
Dear Sir,
Please upload the next chapter soon.
Thanks
Yes, in 2 days max I will.
is goodwill considered in gross block
Nope.
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
Check the value, if its a high value, then add it to your assumptions, else a constant is also good.
should “income tax liabilities” be taken into consideration while calculating the liabilities as a % of gross block
Usually, the income tax bit is factored into the deferred tax bit.
Gross block is not stated in the balance sheet , so where can we get those information ?
YOu can look at the associated notes for this. Will do a detailed chapter on this soon.
Hi K,
I couldn’t get the association between main model and helper model properly. Could you please help.
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.
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”
Thanks, Subbaiya. I have uploaded the excel which you can download.
Okay.. thanks for the clarification
Good luck!
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
You can take the average or the other alternative is by how we have estimated inventory.
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.
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.
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.
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
Depends on the accounting policy, Sonu. Most companies treat this as revenue.
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?
Manjunath, you can add this in the debt schedule itself.
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
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.
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
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.
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?
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.
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.
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.
Okay Sir,Thankyou so much.That was very kind of you to solve my doubt in such less time period,I appriciate it .Thanks
Happy learning!
I do not see where I can download the excel. Can anyone help?
Right before the key takeaways from this chapter.
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.
I’d love to do that Salman. My first objective is to complete this module 🙂
Thank You Sir. Will wait for it 🙂
Sure, happy reading 🙂
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?
Gross block deals with fixed assets, so consider just Property, Plant and Equipment.
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)
Krish, so this is fine. Instead of 1 line item you have a bunch, treat them the same way as you’d treat liabilities.
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
The data is the same, Naresh. Have you checked the excel file?
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
So if its an asset, you dont make any future projections for it. Leave it as is.
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?
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.
Thank you for your help karthik. 🙂
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.
Each would be better. You get better insights.
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!!
Sonal, see the associated notes for Tangible assets. You will get the split up of the gross and net block.
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!!
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.
I have taken infosys for calculation there is no gross block or net block in balance sheet what can i do?
Thats because Infy has no debt.
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.
Check the P&L assumption for the current year’s tax. You can use that method.
I am not able to download the excel Sheets. Can you please drop the link?
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.
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.
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?
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.
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 ?
Krish, these are numbers belonging to an actual company, removed the name to keep it generic.
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.
Vishal, look for the link right above the Key takeaways section.
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
Of course. What we need to look for is consistency in trends. Please evaluate this on a case to case basis.
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.
You can, but try and see if you can build a revenue model for this?
Hi Karthik,
Can the “intangible asset” can be dealt in “assumption sheet” itself or dealt in “asset schedule” sheet
Yes, you can create a section within the asset schedule to evaluate intangible assets.
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.
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).
Thank You regardless sir
Sure, happy learning!
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.
Yeah, I think being comfortable with Excel will help you learn Financial Modelling faster.
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
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.
Thanks for the kind words, Ujjal. I’m glad you liked the content on Varsity 🙂
Sir, how can we find gross block
You can look at the balance sheet (assets side) for fixed assets, but do ensure you check the associated notes as well.
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 😄
Give it a read, hopefully the subsequent chapters will clear up some confusion 🙂
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)
Hmm, thats ok. You can retain the structure of model and model these line items in the balance sheet.
Ok thankyou sir,
One more thing wanted to ask
If Goodwill is not included in Gross Block than where should it come?
It comes under intangible asset and will taper down over the years.
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?
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.
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! 😀
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.
Sir,
You started the company analysis with Relaxo and from this chapter ur excel shows a completely different balance sheet. So i lost the flow of the analysis and what u have been talking about in the several chapters. Can u do a complete analysis of the Relaxo company in the excel sheet so that all others can understand very soon. Now i’m just stuck up and stopped looking other the further chapters
Regards,
Sonjoe.
Well, thats why I have stated clearly at the start of the chapter, that I’m using this example to explain only bit of the module.
Sir why don’t you all make videos of these modules?
Noted. Will try and do something around this 🙂
Hello karthik, you might have already answered some of these questions so please bare with me.
– What exactly is the Gross Block you are referring to. Should i use the gross block for PPE alone or include stuff like vehicles, computers furniture etc. Does it include ROU and capital work-in progress
– When subtracting depreciation i presume i should use the accumulated depreciation.
– When calculating liabilities as a percentage of gross block should i add up the individual line items or is it always better to handle each line item on its own
– Grossblock is the cummulative sum of all assets that the company owns
– Yes, accumulated depreciation from balance sheet
– It depends on the company, sometimes its better to deal with each line item separately.
Sir for assumption we have used rolling average
but sir there are lot of other factors with influence these number (like trends in market, Product life cycle, macro economic factors, etc.)
My question is some time there are forecasted percentage of revenue or etc. in annual report of the company. I think they are better assumption than rolling average & company know about itself more than us. Which one is preferred in your opinion and Why?
Thank you sir!
Yes, like I’ve mentioned in the chapter, this is possible. Use anything technique that you think makes sense for making these assumptions. But ensure its backed by a logical reason.
Hii Karthik,
I have a couple of questions:
1. is it necessary to keep the denominator the same for the line items? eg: in some cases, I using Gross block in some cases I am using something else to keep the narrow band.
2. As we dealt with Equity under a schedule, we would need to deal with the Property, plant, and equipment in a schedule only. Right? because it is also one of the high-weightage line items or can it be just taken as a proportion of the gross block?
3. At times I do not understand which line item to use under a schedule and which should i select under the Assumption sheet. Also, which field should I use as a denominator to get the percentage value in the assumption model? Can you help me with this, please?
1) Not necessary, you can keep based on what you think makes sense.
2) Not sure if I get your query fully, but gross block takes care of this fully right?
3) The way to think about this – either the line item should be assumed or you need a schedule for it. Based on that you can decide what to do 🙂
So there is a line item of property, plat and equipment in the Annual Report. I checked the notes and thats the net block and not the gross block.
So should i assume this with percentage of Net sales or gross block. Or should i treat this under a schedule
Yes, net block. Gross block – depreciation = Net block. I’d suggest you build a schedule for net block.