Joel Greenblatt's Magic Formula

Stock Screening
46

Ahmed had published an excel sheet here Joel Greenblatt’s Magic Formula in the Indian context – Does it work? – Financial Tales
It was based on FY 2019-2020 Results

I tried to recreate it using screener. I expected at least 80% would match. However there very little overlap between his findings & my results
(I used screener to scrape data for verification so I don’t have to use any expensive dataset right now)

Below is the way how I scrapped & applied the formula. Can somebody let me know where I am doing wrong?

(I know there are few assumptions, but I thought the result should match at least 75% to Ahmed’s which did not)

  1. Created the Screen for MarketCap more than 250Cr. (MoreThan250MarketCap - Screener)
    ( I know its based on current Price. but I am taking top 250 companies & there could be change of 20-30 companies compared to FY19-20)
    (Hoped to match or having near to Top 250 results that of Ahmed’s)

  2. Sorted by Current Market Cap

  3. Picked top 250 companies

  4. Went to each company Screener page

The Formula used

  1. ROIC= EBIT/(Net Working Capital + Net Fixed Assets)
  2. Earnings Yield = (EBIT*1000000)/EnterpriseValue

Now How I calculated each component

  1. EBIT = Profit Before Tax + Interest

    • Profit Before Tax is taken from P&L (Yearly) under Mar 2020 column
    • Interest : Same Mar 2020 column
      (If Mar 2020 is not available. i.e. Company is not following Apr-Mar FY, I took Dec 2019 data. I could use Mar 2020 Quarterly results, but avoided complications for proof of concept)

  2. Net Working Capital = Trade Receivables + Inventories - Trade Payable

    • Trade Receivables & Inventories are taken from Other Assets under Balance Sheet of Mar 2020
    • Trade Payable from Other Liabilities

  3. Net Fixed Assets = Fixed Assets - Intangible Assets

    • Total Fixed Assets value is taken from Balance Sheet &
    • Intangible value is taken from by expanding the Fixed Assets.

  4. Enterprise Value = Current Market Value - NetDebt
    i.e. Enterprise Value = (OutstandingShares*3rdJul2020 Price) + (Borrowings - Cash)
    (3rd July Price was taken as Ahmed mentioned 3rd July Price on the sheet he shared or his exercise was on that date)

    • 3rd Jul 2020 price was scrapped separately for those companies
    • Outstanding Shares is calculated from Current Market Cap divided by Current Market Price (Both are given in Screener.in at the top section)
    • Borrowings scraped from Balance Sheet
    • Cash was scarped from Balance Sheet’s Other Asset’s data (Cash Equivalents)

Some of the companies were skipped based few criteria

  1. Sector Skipping: Screener gives “Sector” in Peer Comparison section. I skipped the company if sector is Banks or Finance or Insurance
  2. Other Assets Total has Other Assets Items value. I did not get clearly the significance of it.
    However I saw that if Other Asset Items value is more than 30% of Total Other Assets, it belongs to finance or non-operating assets.
    So I skipped the company if OtherAssetItems is greater than 30% of Total Other Assets
    (If I don’t skip, the results are totally different.)

After this standard procedure of sorting was applied.

Only 10-11 companies matched to that of Ahmed’s list. So I am not sure if I did correct thing.

If somebody can point out the issue in above logic, that will be great.

1 Like