Numbers and Narratives: A Simple Discounted Cash Flow (DCF) Model


(Dinesh Sairam) #62

Sure, I would be glad to do that. I’ve been making some incremental changes in the model too, so the timing couldn’t have been more apt. Expect something within this weekend.


(Dinesh Sairam) #63

As promised, I have updated my original post on the model. Now, it offers step by step procedures on how to use the model, based on KRBL’s Q4/Full Year results:

Incidentally, the Value, at Rs. 454.98. has not changed a lot since my original Valuation. As always, comments and criticisms are welcome.


(yourraj) #64

Hi
I am learning the valuations it will be agreat help if you please share which year you had taken as base year .It will be graet if you can provide link to AR or incorporate some examplary figure from the annual report .Thanks


(Dinesh Sairam) #65

For the KRBL Valuation? Everything is in the blog.

I’ve taken the latest Q4/Full Year results report of KRBL.

The blog post I’ve provided just above your post explains in small details how to fill up each figure. If there are specific doubts, feel free to ask.


(VP_amit) #66

@dineshssairam - brilliant work!

Have not been able to go through the explanations fully, but some initial silly questions -

  1. Should operating income (EBIT) be Rs 724 cr = Rs 659 (PBT)+ Rs 69 (finance costs)?
  2. ICRA rating I found was AA (stable) and not AA- (stable)
  3. Company Beta of KRBL as I see on economictimes is 1.27, the link you have taken it from (topstockresearch) also says about 1.4, how have you taken it as 0.49, am I missing something?
  4. LT Foods Beta also around 1.5, you have taken 0.899, is that right?
  5. While calculating Industry Beta you have taken average of LT and Kohinoor. Should KRBL beta not be included as it is part of the industry, or do we always have to leave out the company being valued?

(Dinesh Sairam) #67
  1. Operating Income is only Rs. 655.01 Cr (According to the Q4/Full Year Report). You do not include Finance Costs, because it’s already accounted for in Value of Debt. Even in the Accounting sense, Finance Costs do not relate to Operating Cash Flows.

  2. Looks like you are correct. KRBL’s Rating recently got reassigned and upgraded to AA from AA- (Source). The problem is, India doesn’t have a huge Debt market, a Corporate Debt market nonetheless. Here’s a list of the Corporate Debt being traded in India. The ideal way to do this would be to find 4-5 companies in the same industry with the same Credit Rating as KRBL (AA Stable) and find out their average Yield. Since India does not have a vast Debt market, we have to settle for guess work based on the few samples we have (Ignoring the fact that we need to check Yield levels in the same industry, because there’s no other choice). So, even for AA Stable, I think 7.50% is a decent estimate.

3/4. Really weird about the Beta. From the screenshot I have attached in the post, it’s clear that I’ve taken it from TopStockResearch (I usually take it here - since they have long term Betas as well). In the screenshot, it says 0.488. Sames goes for LT Foods, it was around 0.899. Maybe the website was inaccurate? I honestly don’t know.

The most accurate way to calculate the Beta (Just to be sure yourself) would be like this:

KRBL Beta.xlsx (44.5 KB)

As you can see, the 5-Year Beta of KRBL is 0.66, Kohinoor 1.38 and LT Foods 0.96 (You can also see that NIFTY’s 5-Year CAGR is 12%, so I didn’t mess up there). Let me see if I can change the inputs and change the screenshots as well. Perhaps a good lesson in why you shouldn’t trust random sources on the internet.

Rest assured, this should not affect the outcome by much, since I wasn’t way off from the actual values. But thanks a lot for pointing this out. Oh, and the source for all this historical price data is Yahoo Finance. I have use Y! Finance extensively in the past for calculating Betas and getting historical price data, but with this one, I got a little lazy.

  1. Usually a good practice to ignore the company in question. What you’re essentially doing is calculating the risk suggested by the market for the ‘competitors’ using their Beta, remove the effect of their D/E Ratio. Once this is done, you end up with what’s called ‘Asset Beta’ or ‘Unlevered Beta’. Then you work your way backwards and ‘re-lever’ this using the D/E Ratio of the company in question (Here, KRBL). Since your end results is to obtain the Beta for KRBL, it’s best not to include anything related to KRBL in any intermediate steps. Theory on this here, if you are interested (Or just search for ‘Bottom Up Beta’ on Google).

Investing Basics - Feel free to ask the most basic questions
(VP_amit) #68

@dineshssairam - some more questions, mostly naive - but will be great to get answers to these.

  1. Debt in D7 is long term + short term?

  2. Based on AA (stable) the yield seems to be more in the range of 9.5%? instead of 7.5%? - I could not find any other company with AA (stable). However ICICI bank is at 9.5% yield so have taken that

  3. Op Income - I still think it should be 724 Crores. Till 2016, KRBL first gave Op Income (EBIT), then gave finance costs, then gave PBT. From 2017 KRBL reports have started showing Finance cost as part of Total expenses (that includes COGS etc), so Rs 659 crores you see in 2018 report is actually PBT with Finance costs alredy subtracted. So should we not be adding Rs 69 crores to get EBIT/Op Income? Please confirm - it may not make a big difference to the ultimate valuation, but just want to be sure about the correct value & formula to be used?

  4. Where did you get the cash equivalent figure of 22.47 Crores?

  5. Any reason, you have taken indexed return from 1999 to 2014 and not till 2018?
    8th June 1999: 1145, 8th June 2018: 10767, so total return: 840%, 19 year CAGR return: 12.5%?, not much of a difference though

  6. risk free rate: you have taken 10-year govt bont yield of 7.88%, should we not replace it with risk free rate for a normal retail investor - which is post tax FD rate, of about 5%?

  7. How have you calculated 2018 Interest expense in F11? 2017 Interest expense was Rs 62.38 crores as in 2017 Annual Report

  8. I see the following long term debts in 2017 AR - the values are not matching your debt figures, minor point, but can you check this?
    a) ECB Loan ICICi (Rs 13.18): payable in 1 years
    b) SBI Loan (Rs 60 crores outstanding): 4.5 years
    c) Term Loan from ICICI Rs 0.58 crores): 0.6 months
    d) Term Loan from Corporation bank (Rs 0.21 crores) - 0.6 months

  9. Kotak mahindra Bank (3.8 crores): 1 years

  10. What is Debt type E and what is the logic of calculating it?

  11. Your formula of calculating market value of debt is a bit confusing, can you explain it?, especially at one place in the formula it is (1+F22)^F22, should it be (1+F7)^F22?

  12. Is the formula of reinvestment rate in cell B31 correct?

  13. General Feedback on your blog: always get the subscribe to valuation in Motion window which covers text … :-), can that be removed somehow?


(Dinesh Sairam) #69

Whew, that is a lot of questions. I’m glad you’re interested though. I will try my best to address all of them.

  1. I don’t remember this. But if you are asking if only Long Term Debt should be taken, the answer is yes. Short Term Loans are Working Capital Loans, which will be captured by your Capital Turnover Ratio.

  2. Corporate Debt Ratings are tricky. Because like I mentioned earlier, India has a very poor Debt Market. So, the least you could do is try to find a company in the same broader industry (Food). If that’s not possible, make a wild guess. I don’t think a few percentage points would make much of a difference. I’ve changed it to 8.50% now, and it didn’t make much of a difference. But I’ll let it be at that level anyway.

  3. On second thought, yes. It should be 724.25 (655.01+69.24). My bad.

  4. I think I’ve taken Other Financial Assets + Prepayments. You can see from their earlier AR that Other Financial Assets are Term Deposits. Prepayments are prepaid Lease Expenses (Meaning, they won’t have to pay these later). I guess you could consider Prepayments part of WC if you want to, as well (Meaning, ignore it here, but make it part of your Capital Turnover calculation).

  5. This is to avoid abnormalities caused by the bull market. You can see in my post above that even if I’d calculated for a 3-year period, I’d not have been far off. Oh well.

  6. Very good question. But no. The FCFF already excludes taxes paid by the company. Since you are discounting this figure, you need not adjust for tax again. Even if you use the WACC, the Cost of Equity is calculated tax-free. Only the Cost of Debt is adjusted for Tax, because Interest Expenses can be adjusted to reduce Tax for the company.

  7. I think I took the FY18 Finance Cost of 69.24 and made some proportionate adjustments based on how much of the Debt is Short Term Debt Vs Long Term Debt (Basically remove the interest component paid on Short Term Debt)

  8. Yes, again, I made some proportionate changes based on FY18 AR (One thing I do remember doing is that I reduced the maturity of the old loans by 1 year). We can’t be accurate about this unless the full FY18 AR is released.

  9. Same as above.

  10. You can check here. I’ve averaged all the existing loans and multiplied them by 0.14. I do not remember what 0.14 is, although I could have just made adjustments based on how much Long Term Debt increased from FY17 to FY18 (So, accounting for an additional component of Long Term Debt which we don’t know).

  11. Absolutely, nice catch! I must have dragged the cell or something, since the closest cell got selected. I have corrected the formula now.

  12. It’s not, but it doesn’t matter. For calculating actual Reinvestment Rate, I will have to create entry cells for Assets and Liabilities figures. So, I let go of accuracy for the sake of simplicity. The formula you see here is the same one you see in G31 - the Marginal Reinvestment Rate. I could also try to calculate it based on iterative calculations in excel (Based on Sales, Previous Sales, Capital Turnover and Post-Tax Operating Profit). Let me change that in the original upload. Hopefully, it should be fine. Edit: Just checked, it’s okay now, although this cell will also throw a circular reasoning error whenever someone opens in for the first time.

  13. Oh, interesting. I use MailChimp, which is known to have problems with Google. Have you subscribed to my blog? If yes, does the pop-up appear still? I can change the type of pop-up if that is the case (Something less intrusive). If you haven’t subscribed, I’m afraid I can’t change much.

P. S. I have changed my model a little based on your input. Thanks a lot. You can find the updated version in the same link - Numbers and Narratives.


Investing Basics - Feel free to ask the most basic questions
(aswin) #70

@dineshssairam Hi, first of all, a big thanks for the effort you put in which helps lesser mortals like me. I have a very basic query regarding your excel sheet for calculating the beta of KRBL. The NIFTY value seems to be as of Sundays (eg. 20/May/2018). Sundays?


(Dinesh Sairam) #71

I downloaded the data from Yahoo Finance. Maybe there was some kind of issue with the dates. But I actually aligned all the values before the calculation. Since the dates themselves aren’t used (Only the correlation of returns is used), it is a non issue.


(Karthickphilistines) #72

What should be the value for Ashok Leyland?


(Dinesh Sairam) #73

Hi Karthick,

I would much rather than you use the template provided and narrate your own story. I’d be happy to help, of course. I say this, because nothing beats the satisfaction of your story making it out as the final Value of a company. I hope that’s acceptable.