Module 13   Integrated Financial ModellingChapter 5

Assumptions (Part 2)

View chapters →

5.1 – Deferred tax  

A gentleman posted an interesting comment in the previous chapter. The company he chooses to model did not present the gross block data in the way the company we are dealing with has, i.e. –

Gross block – Depreciation = Net block

Instead, the company directly reported the ‘Net block’ data.

Given this, how would one go about building the assumptions with Gross block as the base for many balance sheet based assumptions?

While the balance sheet reports only the ‘Net block’ number, the associated notes usually carry the gross block and depreciation numbers. One has to extract these details from the associated notes and rebuild the gross block.

It may sound a bit complex at this stage, but don’t worry; we will take this up in the next chapter and lay down the steps involved one at a time.

By the way, I hope you got to look at the raw data of P&L and Balance Sheet and layout the data in a model friendly manner. Assuming you’ve done that, we will now continue from where we left off in the previous chapter.

 

The previous chapter calculated the deferred tax’s growth rate from Y2 to Y5 and its average from Y6 to Y10. While this is ok, it still results in a somewhat volatile set of numbers. There is a better way to do this, and I’d like to discuss it.

If you understand deferred tax, you’d know that it occurs due to the way depreciation is treated. Hence deferred tax and depreciation is connected.

So, rather than taking the growth rate of deferred tax, it probably makes sense to consider deferred tax as a percentage of depreciation.

For Y2, the deferred tax is 16.95Cr, and depreciation is 121.73 Cr. So deferred tax as a percentage of depreciation for Y2 is –

16.95/121.73

= 13.92%

We can continue this for Y3, Y4, and Y5 on excel –

 

As you see, the numbers look much more stable. I’d request you to please make this change in your model. Now, for the projections, you need to take the rolling average. For Y6, it would be the rolling average of Y2 to Y5; for Y7, it’s the rolling average of Y3 to Y6 and likewise.

The resulting percentage range is also relatively stable.

Before you crib and curse me for making you redo the deferred tax bit, I’d like to tell you that the growth rate method for assumptions is critical, and we will use it in this chapter when we take up P&L assumptions.

So in that sense, you already have a heads up 😊

5.2 – Dealing with inventory 

With the deferred tax assumption, we also complete the liabilities side of the assumption. Please note that we have not made any assumptions for share capital and borrowings; these are line items we will deal with separately by building ‘schedules’.

So we now proceed to the asset side of the balance sheet, and the first line item to consider is the inventory.

If you look at the inventory data as stated in the balance sheet, you’ll realise the worth of inventory that’s lying with the company. For instance, for Y1, the inventory worth was 92.17 Crs; for Y2, it’s 194.33 Crs, Y3 it’s 160.83 Crs etc.

Any manufacturing company ends up having inventories in its balance sheet, and as you know, the inventory is nothing but the company’s finished goods. The objective of the company is to sell the inventory as quickly as possible. Hence lesser the number of days the company takes to sell the inventory, the better it is for the company.

Based on the nature of every company, the company takes up a certain number of days to convert its inventory to sales.

For example, a company manufacturing pressure cooker may convert its inventory to sales in 30 days, but a company manufacturing cars may take 75 days to convert inventory to sales.

When it comes to the inventory assumptions, we take the following approach –

      • Convert the Rupee value of inventory to the number of days the company takes to convert to sales
      • Find the average number of days for the future years
      • Convert the average number of days back to the Rupee value for the future years

Sounds complex? Perhaps, but let’s go ahead and execute the above steps in our model and see how it goes. I’m sure you’ll eventually find it easy 😊

But before we proceed, why even take the pain of doing all the above? Why not directly take the growth rate of inventory and its average and move ahead (like how we treated deferred tax in the previous chapter)?

When you convert the Rupee value of inventory into the number of days to sales, you also get additional insights about the company. These insights help make investment decisions. For instance, imagine there are two companies manufacturing cameras that are similar in all aspects. Company A takes 40 days to convert inventory to sales, and company B takes 70 days to convert. What can you infer from this?

      • Company A seem to have a better inventory management
      • Maybe Company A has a superior product. Hence the market prefer cameras from company A
      • Or maybe Company B’s sales incentives for merchants is not as attractive as A’s, so merchants tend to push Company A
      • Perhaps, company A have efficient management, meticulously planning these things

You see, the list of insights can go on and on. Hence it makes sense to take that extra effort to juggle and calculate the inventory number of days and let’s do that right away.

On excel, the inventory number of days is calculated easily by applying a formula. I call it the conversion formula because it converts the Rupee value of inventory to the inventory number of days.

For Y1 and Y2, the inventory value is 92.17 Crs and 194.33 Crs, respectively. To convert, we apply the following formula –

= (Average inventory of Y1 & Y2 / Materials consumed for Y2) * 365

In the denominator, you may ask why we use the materials consumed for Y2 and not Y1. Well, this is because we are calculating the inventory number of days for Y2. If we were to do this for Y1, then the formula is –

= (Average inventory of Y0 & Y1 / Materials consumed for Y1) * 365

Since we don’t have the Y0 data, we start with Y2.

So applying the formula for Y1 and Y2 –

= Average (92,17, 194.33)

= 143.25

Material consumed for Y2 (data available in P&L) = 762.86 Crs

=143.25/762.86

= 0.18778

Finally, we multiply the above result with 365 to get the inventory number of days –

= 0.18778 *365

= 68.53

The above number means the company takes about 68 days to convert 143.25Cr of inventory to sales.

Of course, you can do this in excel in one shot –

Please notice, I’ve included ‘inventory number of days in the assumption sheet and executed the conversion formula directly. I’d suggest you do the same in your excel.

Once I’ve calculated the inventory number of days for Y2, I can drag the excel to rows Y3, Y4, Y5 and get the respective values.

Notice, the inventory number of days consistently ranges between 68 to 78 days. To get a sense of how good or bad this number is, you need to compare it to a company operating in the same sector, of similar size. For example, Bajaj Auto and Hero Motors are similar companies doing similar business.

Moving ahead, for the Year 6 to Year 10, we can take the moving average of the inventory number of days.

We have calculated the historical inventory number of days and projected the inventory number of days for the future years.

In fact, you can take a similar approach to Sundry Debtor/Account receivables as well i.e. to convert receivables from Rupee value to receivable number days and then back to receivable in Rupee value.

In the next chapter, I’ll probably explain the process with the help of the helper model.

For now, let us move ahead with other balance sheets and P&L assumptions.

5.3 – Other Balance sheet assumptions

If you look at the asset side of the balance sheet, these are the line items stated by the company –

We have dealt with the inventories already.

Just like on the liabilities side, we will build a schedule for the gross block. Cash and Bank balance in current assets will be dealt with in detail in the cash flow statement.

We will make the assumptions for the remaining line items on the asset side. Let me quickly run you through the thought process before we jump to excel.

      • Sundry debtors – I’ll consider this as a percentage of Gross block (but remember there is an alternate way i.e. to convert to days and back)
      • Loans, advances, and deposits – As you can imagine, this line item is related to the company’s working capital. Hence I’ll consider this as a percentage of net sales
      • Other current assets – This is a small number for Year 1 and does not exist for the rest of the years, so I’ll ignore
      • Capital work in progress – As a percentage of net sales
      • Investments – As a percentage of Gross block

Once I calculate the historical percentages, I’ll go ahead and calculate the rolling average for the future years. Like I’ve mentioned earlier, feel free to change the denominator based on your understanding of the firm and its financial statements. Remember, assumptions are the art bit in financial modelling; you are free to experiment, but ensure it is not too way out of wack 😊

So let me go ahead and implement the above in the excel sheet. I’ll post a series of snapshots hopefully that will be self-explanatory –

I’ve continued on the assumption sheet and lined up the line items in the same sequence as it appears in the balance sheet. Remember, I’ll do all the necessary calculations starting from Year 2 for consistency with the other assumptions.

I’ve calculated the percentages for Year 2, and I’ve highlighted the loans, advances, and deposits as a percentage of net sales. You can see both the formula bar as well as the F16 cell. I’ve highlighted this to showcases the  P&L line item in the denominator.

Hopefully, you will find this as an easy step to implement. Do let me know if you find any difficulties in implementing this by commenting below.

In the next step, I’ll drag the rows to the right till year five, and from year 6 onwards, I’ll take the averages.

I’ve highlighted the average calculation for your better understanding. For the last balance sheet line item, i.e. investment as a percentage of Gross Block, I’ll not calculate the average for Y6 to Y10. Instead, I’ll assume a constant of 3.5% of the gross block.

Why not the average like other line items? Why 3.5%? Why not 4% or 3%? These are all valid questions.

The percentage calculated is quite volatile. It ranges from 3% to 11%, I’m not too happy with it, and therefore I’d like to keep it at a constant 3.5%.

Why not 4 or 3%? Well, that’s the beauty of a financial model. Once the model is complete, I can change this to any value that I think makes sense. Hence I don’t have to stress on it now and stick to 3.5% and move ahead.

With this, we have completed the balance sheet side of assumptions. Whatever is left out will be dealt with in the form of schedules.

We will now move ahead with the P&L assumptions; this should be pretty easy.

5.3 – P&L assumptions

Let us start by taking a look at the P&L –

There is the revenue side, and then the expenses side to the P&L. Revenue side has the sales and other income data, while the expense has the details on all the expenses incurred during the year.

Making assumptions on the expenses side is super easy; all these line items are calculated as a percentage of the net sales or the total income. Revenues, on the other hand, is very interesting. You can either calculate the growth rate or deep dive to build a revenue model.

I want to discuss both these methods. In the primary model that we are dealing with, let us discuss the growth rate method of revenue forecasting. However, we will take the help of a helper model to build a revenue model.

Perhaps we can do both the revenue model and the receivable number of days in the next chapter.

Moving ahead, I’ll create another section in the assumption sheet to accommodate the P&L assumptions. Just for your clarity, this is how my assumption sheet looks at this stage –

Under the new P&L assumptions section, I will proceed sequentially, in the same order that the line items are present in the P&L.

Notice, as discussed earlier, I’ve considered the growth rate for net sales, and for the remaining line items, I’ve considered these as a percentage of net sales. For example, other income is the percentage of the net sale; and the increase in stock is also a percentage of the net sale. So on.

Let us start with the Net sales growth rate; the growth rate is calculated the same way we calculated the deferred taxes growth rate in the previous chapter. Here is the snapshot of Net sales growth rate –

Yes, 81.83% seems high, but it is based on the net sales numbers reported by the company in Y1 and Y2. Here is something interesting that you can do. If you feel the numbers are unusually high, then you can always cross-reference how the peer companies performed during the same period.

If a company belonging to a particular sector has done phenomenally well for a particular year, its peer companies would most likely have performed equally well. For example, if MRF posts a 20% increase in revenue for Y1, you should expect Apolo Tyres to post a 20% increase in revenue. But for whatever reason, Apollo posts 16%, then you know that MRF probably has the edge over its competition.

Of course, this is a very rough example, but I’m highlighting this to give you a perspective of how you can think about companies while building the model.

I’ll go ahead and complete the P&L assumptions. As you can imagine, it is pretty straightforward, or so I assume because we have done this in the balance sheet assumptions.

I’ve highlighted the Year 6 cell for net sales to showcase that subsequent calculations are all simple averages. Of course, this excel will be available for you to download and inspect each cell.

If you look at the P&L, the last two items on the expense side are Depreciation & Amortization and interest expense. These numbers will flow from the schedules that we will build subsequently.

The assumption sheet is now complete, and this is how it looks –

I’ve compressed the image to ensure you get to see the entire page.

I hope you followed the steps we’ve discussed in this and the previous chapter. Please do let me know if you have any queries; I’ll be happy to reply to your queries to the best of my abilities.

In the next chapter, we will take the help of a helper model and understand how to deal with receivables (assumptions) and set up a revenue model.

Download the excel sheet used in this chapter here.

Key takeaways from this chapter

      • Deferred tax is as a percentage of depreciation
      • Converting inventory data from Rupee value to the number of days helps us develop unique perspectives into the functioning of the business
      • Likewise, with the Receivable data
      • A detailed revenue model gives granular insights into the revenue pattern of a company
      • All line items belonging to P&L and Balance sheet are assumed in the assumptions sheet. A schedule is built for the items which cant be assumed directly
      • Specific schedules give us granular insights into the specific line item

 

 

 

81 comments

  1. Tanishk says:

    Hello Sir,

    How many chapters would this module have?

    Also would you be talking about enterprise value, PEG ratio, EV to EBITA, EV to EBIT, EV to sales etc, related party transactions etc?

  2. PRADIP BISWAS says:

    Please do the same in video format.

  3. AMAN KAUSHIK says:

    Hey Karthik,

    I learn a lot from your lesson. Can you suggest Books for us on Financial modelling or valuation.

  4. Prasanth says:

    Hi Karthik,

    Had a problem with Zerodha, bear with me for this small off topic question. For an equity intraday trade, the promised brokerage charge is 0.03% or 20/order whichever is lower but zerodha charged me the higher one!

    Just came across this discrepancy while going through my P&L statements, felt cheated as this cannot happen by mistake. Lost all my respect for Zerodha. Raised ticket, awaiting reply. But felt like having a word with someone higher up other than the customer service executives, so posted it here.

    Thanks in advance.

    • Karthik Rangappa says:

      Prashanth, these are system driven and does not vary for one another. PLease share your ticket number.

  5. Prasanth says:

    Hi, just rechecked the P&L statement again, confusion came up due to clubbing all the orders(of different dates) of a particular scrip as a single order and reporting it, got the necessary clarification after checking out the breakdown.

    Thanks.

  6. Ramanathan Seshan says:

    Respected sir,
    Please consider a module on forwards, swaps and swaptions sir. Your way of presentation is very simple and lucid. Would help a lot of mba finance students.

  7. AMAN KAUSHIK says:

    Hii karthik,

    I know that which learning material you provide us is suffice but I want to dig deep so please if you some other material related to valuing a business then It’s a great pleasure to me.

  8. Ravikumar says:

    Dear Team
    I understand that this is a simpler version of model you are trying to create but still, some expenses like employee salary are fixed in nature they don’t have correlation with Sales. Therefore, is it not better way to forecast them based on their past growth rate rather than equating with the % of net sales ?

    • Karthik Rangappa says:

      Of course, you can. Like I have mentioned in this and the previous chapter, there are no hard rules while making assumptions.

  9. RAMANATHAN SESHAN says:

    respected sir,
    I agree that swap, swaptions, forwards are not used in retail trading. It can be used for educational purposes. Not a lot of books give a lucid explanation u do sir. It would of great help for mba finance students. thank you sir

  10. Kartik Malhan says:

    Hi Karthik,

    Great Modules! I am however stuck on one point- The company I’m trying to model has Right-of-Use of Asset and they have just started incorporating it in balance sheet for last 2 years. There is no discernable trend which I can observe. Can you help me on this? Thank You.

    • Karthik Rangappa says:

      Invariably, this will be a constant for the rest of the years. By contact, I mean, it will be the latest year’s value for the projected years.

  11. Abc says:

    Do these assumptions really do the purpose? Just simply taking assumptions as a % of various items doesn’t actually do the job. At the end , the modeller won’t be confident himself because he knows that he had simply assumed many items as a % .

    Simply taking a % carries forward inconsistencies & these assumptions are more prone to errors . And given less detailed, they make lack sufficient details for decision making.

    Instead a better way to be breaking down items into it’s core drivers .
    For eg:- Revenues could be forecasted by breaking down into volume & price(bottom up) . Or assumed through market size & market share (top-down). Or through regression analysis.

    • Karthik Rangappa says:

      Yes, you are correct. In fact, I have highlighted this in the chapter itself. Few things to note –

      1) Ensure the assumptions when calculated as % is not volatile, it should be consistent. That way you know you are not too way out of wack.
      2) Go ahead and build the model anyway (Which is what we will do), and the assumptions can be tweaked
      3) Revenue model and few large items will be broken down into core drivers to ensure granular insights.

  12. Abc says:

    For Costs , we could make schedules such as breaking Costs into drivers. For eg COGS could be broken down into it’s components.

    For balance sheet items , again breaking down liabilities & assets into much more detail

  13. Dj says:

    Hi, How can we know DII/FII holding in a particular stock. I want to track Institutional activity in a particular stock. Is it possible?

  14. Shivansh Agarwal says:

    While calculating net sales, for my company from revenue from operations there were two things given
    1. Income from brand franchise arrangements
    2. Income from BCCI-IPL franchise
    the company which I have taken is mcdowell’s
    so will these two be considered in net ales or not

  15. Shivansh Agarwal says:

    The company which I have taken have 1 million in investments only for the Y2 and for rest of the years its nil. So, should I ignore it

  16. Shivansh Agarwal says:

    I have a question
    due to pandemic all the companies underperformed in every aspect so while building the model should I take the year 2021 into consideration or not and if I have taken it into consideration will it affect in building the model

  17. Shivansh Agarwal says:

    (Changes in inventories of finished goods, work-in-progress and stock-in-trade) is this same as increase in stock cause in my model company something like increase in stock is not given

  18. Shivansh Agarwal says:

    how to treat:-
    1. advertisement expenses
    2. Loss allowance on trade receivables and other financial assets (net)
    3. other expenses

    • Karthik Rangappa says:

      YOu can club this under expenses are you can build drivers for each of these. I’ll build a revenue model in the next chapter, hopefully that gives you a perspective.

  19. Shivansh Agarwal says:

    4. finance costs

  20. Shivansh Agarwal says:

    what is “drivers”

    • Karthik Rangappa says:

      Things that drive a line item. For example, if a company is manufacturing flip-flops, then the key driver for its revenue is also the rubber prices.

  21. Shaurya Shah says:

    Forex Rates with inflation is also important parameter in assumption for long project. So can you please show some small calculations & assumption based on that and where it has to consider at the end or in calculation portion itself?

    • Karthik Rangappa says:

      Thats right, especially for service-oriented companies exporting stuff. I’ll see if I can include this as well.

  22. pravin says:

    waiting for next model sir?
    when it will be released?

  23. shaiksha says:

    Hi, Sir…
    Please make this module for offline download like other modules…

  24. Vaibhav Kumar Yadav says:

    Sir, please upload others modules of this series fast. 🙏🙏
    And I want to say I had learned everything about stock market and financial markets from you
    Big fan of yours sir 😍😍

  25. Praveen Jat says:

    Hindi translate wale pdf ko download karne ka option hi nahi aa raha he, hindi translate pdf kese download karu from module 2 to module 13.
    Aur module 12 to english me bhi download karne ka option nhi aa raha he

  26. Omkar says:

    Everyday I check whether the next part is published but go disappointed…..Its been months…Hope it will be published soon.

  27. Akhil says:

    Sir, TCS has international clients, how will we take into account the dollar risk/premium and volatility

    Also sir, excited about this module, what are some future modules you will write about?

    • Karthik Rangappa says:

      They have forex hedges Akhil. Do check the associated notes. At present, the focus is on this module, and after this the personal finance part 2 🙂

  28. Omkar says:

    It fells like a series where we have to wait months for next Session(in this case chapter).

  29. Praveen Jat says:

    Sir, how to download hindi module pdf?
    I can’t manage to read online in laptop or in browser.

  30. Adil Khan says:

    sir if I post a comment in previous any module will you answer that ?

  31. Rahul Singh says:

    Hi Karthik ,

    this is so informative, just a request would you please post chapters week wise. ab raha nhi jata 🙂
    its so informative that i want to find intrinsic value for all my companies.

    • Karthik Rangappa says:

      I really wish I can do that Rahul. But this module is so complicated (as you can see already), that it is extremely challenging to simplify this yet cover all aspects. I’ll try my best, but until then, thanks for your understanding 🙂

  32. MAYANK GUPTA says:

    Do we have to copy all entries from annual reports by typing or is there an easy way to get data on excel? otherwise it becomes too cumbersome.

    • Karthik Rangappa says:

      Its just the Balance sheet and P&L that you need, a bit of an effort I agree, but it is worth taking that effort.

  33. Sumit Kohli says:

    Sir, is the flat rate assumption same as a straight line method of forecast?

  34. Sumit Kohli says:

    Bdw Sir, I am liking your content. The way you incorporated granular insights into each and every aspect right from Excel setup till forecasting , it’s just commendable.

  35. Sumit Kohli says:

    Yeah Sir, whenever I have some doubts or confusion, I go through your slides to refresh the concepts.

  36. Tarun says:

    I am not able to find any Excel sheet attached anywhere in this module. Could you please help?

  37. Ajay says:

    Hey, My balance sheet doesn’t have gross block or net block, so how can I calculate them from scratch? any feedback will be much appreciated.
    I do have below items though.
    ASSETS
    Non-Current Assets :
    Property, Plant and Equipment
    Right of Use Assets
    Capital Work-In-Progress
    Goodwill
    Other Intangible Assets
    Investments accounted for using equity method
    Thanks in advance.

    • Karthik Rangappa says:

      Property, plant and equipment = Fixed assets = Net block. Check the notes of this line item and see the depreciation details.

  38. Ajay says:

    Thanks much 🙂

  39. Akash says:

    Understood All! Thanks.

  40. Mansi says:

    Can we take base as total revenue for all apart the items i.e., for those items for which we are not preparing the schedule and also for deferred tax liabilities, inventories and debtors?

    • Karthik Rangappa says:

      Mansi, thats not the best idea. But why did you think about it that way? You can take relevant line items right?

  41. Mansi says:

    What I meant to say is that instead of taking gross block can we take total of liability or asset as the base?

  42. saroj kumar panda says:

    Sir, while calculating inventory days, taking raw material cost into account should we also consider purchase of stock in trade in addition to cost of raw materials. I was conductinf the financial modlling of relaxo footwears. There two rows have been mentioned. purchase of stock in trade and cost of raw materials used.

    Thank you so much

  43. Abhishek says:

    Great module. Was planning to learn financial modelling as I aspire to get into investment banking. instead of using gross block for debtors wont it make more sense to use it with relation to the credit sales? Just my curiousness. Would love some insight. Thank you

    • Karthik Rangappa says:

      That makes sense too. Like I mentioned earlier, you are free to use whatever as long you can justify it.

Post a comment