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

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.

1 Like

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.

1 Like

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

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.

1 Like

@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?
  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).
2 Likes

@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?

1 Like

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.

@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?

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.

1 Like

What should be the value for Ashok Leyland?

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.

2 Likes

@dineshssairam I couldn’t find any minority interest part in KRBL balance sheet . Could you please share where did you get the value of 19.22 ? Also should we add interest expenses back to PBT to get Operating Income ? Or should we go with Operating Income from screener(Sales - Expenses) without considering other income, depreciation and finance costs?
TIA

KRBL has two subsidiaries and I have valued them as follows. The original figure of Rs. 19.22 crores appears to me to be a Majority Interest (Using a slightly different P/B, which I don’t recall now), which was a mistake from my side. This is the actual Minority Interest calculation for KRBL:

In essence, since we’re calculating the value for KRBL, KRBL DMCC and KB Exports together (Consolidated Financial Numbers), we need to remove the portion of KRBL DMCC and KB Exports that KRBL doesn’t own.

I think I updated the KRBL excel to this effect, although this shouldn’t have any material impact.

Take PBT and add Interest Expense. That’s the easier approach.

I’ve personally found Screener to be inaccurate, at least at this level. In fact, even for Balance Sheet data, Screener generalizes a lot of things. I like to read the Annual Report / Quarterly Report and fill in the numbers from there.

Could you please let me know why are you subtracting cash and cash equivalents while calculating capital turn over? Could you please explain a bit more on capital turnover calculation you used?

Capital Turnover = Sales / (Equity + Debt - Cash & Cash Equivalents)

In my model, this ratio is used to determine how much capital reinvestment (i.e. Working Capital and Capex combined) a company should make to obtain 1 unit of Sales/Revenues. So, a 2 Capital Turnover essentially means that the company can generate a Revenue of Rs. 2 from Rs. 1 of invested capital. If the company manages to contain its expenses and taxes within, say, Rs. 1.80, then its profits would be Rs. 0.2, which is representative of a 20% return on invested capital. I hope it’s clear up until this point.

The reason behind deducting Cash and Cash Equivalents is that, in any sensible Valuation, income generated from cash and cash equivalents (Such as dividends, interest income) are supposed to be ignored. Instead, Cash is simply taken at Face Value (Nothing more, nothing less). This is because the business and the cash do not carry the same amount of risk.

Say, if a company invests Rs. 100 Cr in Machinery and Rs. 15 Cr in Government Bonds, a logical conclusion is that the Rs. 100 Cr is at a far greater risk (Business Risk) than the Rs. 15 Cr (Governmental Default Risk–which is almost zero). Therefore, it makes sense to say that the proceeds from the Rs. 100 Cr should be discounted at the WACC and the proceeds from the Bond Investment at the Risk-free Rate. We do discount the proceeds of the ‘Machinery investment’ i.e. The Free Cash Flow at WACC.

But what about the relatively riskless Bond Investment? If the Bond Investment earns at the RfR and gets discounted at the RfR, its Value remains the same. That is, assuming a RfR of 8%, (15 Cr * 0.08)/0.08 = Rs. 15 Cr. This coincides with our logic of taking all Cash and Cash Equivalents at Face Value.

With that said, there is definitely a catch-22 situation with regards to Cash holdings. Prof. Aswath Damodaran addressed this problem in one of his blog posts:

He also wrote an extensive paper on the topic, if you’d like to go through it: SSRN-id841485.pdf (1.9 MB) (Warning: It’s 55 pages long)

The TLDR is: If you trust a company to reinvest cash holdings appropriately, it makes sense to simply ignore cash-related income and consider cash at Face Value. If you don’t, then it makes sense to ‘penalize’ the excess cash by reducing the Value of cash:

Say, a company has Rs. 100 Cr. You don’t trust the company to reinvest this cash properly. Assume that the WACC is 12% and RfR is 8%. You will simply do 100 * 0.08 / 0.12 = Rs. 66.67. So, in line with your doubts, you ‘penalized’ Rs. 33.33 worth of Cash holdings from the company. You can now use this reduced Value of Rs. 67 Cr in the Capital Turnover formula instead of the Rs. 100 Cr (Which will results in a lower Capital Turnover and so, lesser free cash flows).

Alternatively, you could include the earnings from cash and cash equivalents (Dividends, interest income) and let it all get discounted at the WACC. You don’t adjust anything. But the risk here is that you are also projecting income from cash out for many years, whereas income from cash tends to be very fickle. This is a part of the discussion I was having in another thread:

If all this is too complex, a simple thumb-rule is to ignore Cash completely from the equation. Use the ‘Non Operating Asset’ field in the model to populate Cash balance. Don’t include Cash while calculating Capital Turnover. This is a very conservative approach. The impact of this kind of a method can range from minimal (For a company like KRBL) to enormous (For a company like Cochin Shipyard).

1 Like

Hi Dinesh,

Thanks Dinesh for your all your help and explanation on your valuation model i.eNumbers and Narratives. Appreciate your help in handholding novice like me on various aspects of valuation for organisations. I am sure other members like Me( new to investing) will get good insights on valuation from this model.

regards
Ashit

2 Likes

Happy to help an enthusiastic person like yourself, Ashit. :slight_smile:

1 Like

Answering both of these posts here, because I don’t want the Mods removing any such comments (Rightfully, however) and then get blamed for ‘getting them removed’.

The ‘model’ isn’t something I fashioned myself out of thin air. It is based on widely known financial theory. I cannot quote a “success rate”, because the model being ‘good’ or ‘bad’ entirely depends on how it is used (That is to say, depends on the insights, financial acumen of the person using it). Someone may use it way, way better than me. In fact, it’s assured that someone will.

Time and again, I have claimed that the model is simply a tool. Say, it’s like a wrench. How silly do you think is asking “What is the success ratio of a wrench?” The wrench itself does not fix stuff wonderfully, the person who handles the wrench does. I really hope I have made myself clear.

Of course, I understand that there’s the chance of the wrench itself being faulty or in this case, my model itself being faulty. I will be more than happy to discuss along these lines. In fact, that is what I have been doing in this thread.

I just think it’s quite silly and impossible to calculate how ‘successful the model was’. If there was any such way, please do let me know.

1 Like

It looks like the new rules require Indian companies to report Financial Position (Balance Sheet) as well during Quarters. I just realized this after looking at a couple of Quarterly filings.

It’s a long way to go to meet the standards set by the USA’s 10-K. But yes, a minor victory for those interested in stock valuation :slight_smile:

1 Like