Investor Diary Beginner Stock Analysis Excel (V-1)

http://www.investordiary.in/

HOW TO USE THIS SPREADSHEET?

Step 1 - This spreadsheet works only on Screener.in. The first step is to create a free account here - https://www.screener.in/register/

Step 2 - After creating your account, while you are logged in to Screener.in website, visit this page - https://www.screener.in/excel/ - and upload this excel file
Step 3 - Visit the home page of Screener.in and choose a company of your choice. Once you do that, you will see details of your
chosen company. Scroll down and come to the first financial statement table called "Quarterly Results" and click on "View
Consolidated". Now, all data you see for this company will be consolidated.

Step 4 - Scroll back to the top of the page, and you will see a button "Export to Excel" on the right side. Click the button and the
company's financial data will be exported in an excel file in the exact format as "Investor Diary Stock Analysis Excel".
Now onwards, any excel you export for any company on Screener.in will be downloaded in this very format.

IMPORTANT INSTRUCTIONS?
1. All financial data of your chosen company will be automatically updated in the sheet you download.
2. You may update the sheet and add your own analysis, formulae etc. and then upload again to Screener.in site using the Step 2 mentioned
above. But DON'T touch the sheet titled "Data Sheet" because this will cause errors in your future downloads.

3. I have added Comments and Instructions wherever necessary so as to explain the concepts. Read those carefully before working on the sheet.

4. This sheet is not a replacement of the work required to read annual reports as part of the analysis process. So please do that along with working
on this sheet. You may sometime find some discrepancy in numbers (though rare), but you will know this only when you read annual reports.
5. I could not find a bug/errors in this spreadsheet, but if you notice some, please email me at - [email protected] - and I will try to
fix the same and update the sheet
6. This excel won't work for banking and financial services companies.
Note: All data is sourced from Screener.in
click here for the explanation
 Investor Diary Beginner Stock Analysis Excel
http://www.investordiary.in/
Basic Company Details
Parameters Details
Company HERO MOTOCORP LTD
Current Stock Price (Rs) 1963
Face Value (Rs) 2
No. of Shares (Crore) 20
Market Capitalization (Rs Crore) 39201

Key Financials - Trend

Parameters Details

Sales Growth (9-Year CAGR) 8.80%

Profit Before Tax Growth (9-Year CAGR) 6.55%

Net Profit Growth (8-Year CAGR) 4.74%
Average Debt/Equity (5-Years, x) 0.00
Average Return on Equity (5-Years) 32.68%
Average P/E (5-Years, x) 15.26
Latest P/E (x) 10.47
xcel
 HERO MOTOCORP LTD
2-MINUTE TEST PASS

1 Does the firm pass minimum quality hurdle? YES

Avoid low market cap (minimum 500 crores) and IPO companies

2 Has the firm ever made an operating profit? YES

3 Does the company generate consistent cash flow from operations? YES

4 Are returns on equity consistently above 15% with reasonable leverage? YES

5 Is earnings growth consistent or erratic? CYCLICAL

6 How clean is the balance sheet? DEBT-FREE

If financial leverage ratio above 4 or debt-to-equity ratio above 1

a Is the firm in a stable business? CYCLICAL

b Has debt been going down or up as a percantage of total assets? GOING UP

c Do you understand the debt? YES

7 Does the firm generate free cash flow? YES

8 How much other is there?(One-time charges, etc) NONE

9 Has the number of shares outstanding increased markedly over the past several years? YES
Assuming no big acquisitions, if shares outstanding are consistently increasing around
by more than 2% per year, think long and hard before investing the firm
 ENTER MANUALLY

Market Capitalization (Rs Crore) 39201

Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
2834 2420 2886 2541 2879 3340 4440 4665 5250 5019

2687 2254 2360 1890 2963 2250 3849 4028 3981 979

64.41% 65.22% 55.44% 42.31% 37.66% 36.47% 35.77% 33.40% 31.42% 26.33%

-14% 23% -11% 0% 13% 32% 7% 9% -8%

FINANCIAL LEVERAGE= 1.38 DEBT TO EQUITY RATIO= 0 ICR 249.35

0.01 0.06 0.07 0.07 0.03 0.00 0.00 0.00 0.00 0.00

-571.54 1568.33 1439.96 1891.75 1182.15 2846.99 2864.4 3312.97 189.53

Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
NSI 9338 %change 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.02%
No. of Equity Shares 19.97 19.97 19.97 19.97 19.97 19.97 19.97 19.97 19.97 19.97
New Bonus Shares 0 0 0 0 0 0 0 0 0 0
Face value 2 2 2 2 2 2 2 2 2 2
 NONE
FEW
A LOT

CONSISTENT
CYCLICAL
ERRATIC

CONSISTENT
CYCLICAL
ERRATIC
 DEBT-FREE
LOW-DEBT
MODERATELY-LOW DEBT
MODERATE DEBT
MODERATELY-HIGH DEBT
HIGH-DEBT

CONSISTENT

YES
NO
 Balance Sheet
HERO MOTOCORP LTD
Rs Cr Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
Equity Share Capital 40 40 40 40 40 40 40 40 40 40
Reserves 3,425 2,916 4,250 4,966 5,560 6,501 8,794 10,071 11,729 12,817
Borrowings 66 693 719 642 284 - - - - -
Other Liabilities 4,992 7,083 4,886 4,001 4,218 3,987 3,796 4,645 5,036 4,849
Total 8,523 10,732 9,895 9,649 10,102 10,528 12,631 14,757 16,805 17,706

Net Block 1,659 4,080 3,786 3,071 2,243 2,913 3,584 4,396 4,655 4,619
Capital Work in Progress 48 50 39 62 854 713 605 465 318 542
Investments 3,926 5,129 3,964 3,624 4,089 3,154 4,581 5,890 7,525 5,969
Other Assets 2,890 1,473 2,107 2,892 2,916 3,749 3,860 4,006 4,307 6,577
Total 8,523 10,732 9,895 9,649 10,102 10,528 12,631 14,757 16,805 17,706

Working Capital -2,102 -5,610 -2,779 -1,109 -1,302 -238 64 -639 -729 1,728
Receivables 108 131 272 665 921 1,390 1,283 1,562 1,520 2,822
Inventory 436 525 676 637 670 815 673 656 824 1,072
USE COMMON FORM ANALYSIS AND FINANCIAL HEALTH WORKSHEET TO ANALYSE THE BALANCE SHEET
 Common Form Balance Sheet
Rs Cr Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18
Equity Share Capital 0% 0% 0% 0% 0% 0% 0% 0% 0%
Reserves 40% 27% 43% 51% 55% 62% 70% 68% 70%
Borrowings 1% 6% 7% 7% 3% 0% 0% 0% 0%
Other Liabilities 59% 66% 49% 41% 42% 38% 30% 31% 30%
Total Liabilities 100% 100% 100% 100% 100% 100% 100% 100% 100%
Net Block 19% 38% 38% 32% 22% 28% 28% 30% 28%
Capital Work in Progress 1% 0% 0% 1% 8% 7% 5% 3% 2%
Investments 46% 48% 40% 38% 40% 30% 36% 40% 45%
Other Assets 34% 14% 21% 30% 29% 36% 31% 27% 26%
Total Assets 100% 100% 100% 100% 100% 100% 100% 100% 100%
Receivables 1% 1% 3% 7% 9% 13% 10% 11% 9%
Inventory 5% 5% 7% 7% 7% 8% 5% 4% 5%
Cash & Bank 22% 1% 1% 2% 1% 2% 1% 1% 1%
 AVG MIN MAX
Mar-19
0% 0% 0% 0%
72% 56% 27% 72%
0% 2% 0% 7%
27% 41% 27% 66%
100% 100% 100% 100%
26% 29% 19% 38%
3% 3% 0% 8%
34% 40% 30% 48%
37% 28% 14% 37%
100% 100% 100% 100%
16% 8% 1% 16%
6% 6% 4% 8%
1% 3% 1% 22%
 HERO MOTOCORP LTD
FINANCIAL HEALTH
FINANCIAL LEVERAGE= 1.38 MORE THAN 4 IS RISKY
DEBT TO EQUITY RATIO= 0.00 LESS THAN 1 IS GOOD
INTEREST COVERAGE RATIO= 249.35 HIGHER IS BETTER (atleast greater than 10)

Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16

INVENTORY TURNOVER 36.11 36.95 34.90 37.33 37.75 33.83 42.26
DAYS IN INVENTORY 10.11 9.88 10.46 9.78 9.67 10.79 8.64
DAYS IN RECEIVABLES 2.51 2.46 4.22 10.21 13.29 18.39 16.46
FIXED ASSET TURNOVER 9.50 4.75 6.23 7.74 11.27 9.47 7.94
Total assets/Total liabilities= 1.69 1.38 1.77 2.08 2.24 2.64 3.33
Total debt/Total assets= 0.01 0.06 0.07 0.07 0.03 0.00 0.00
Total debt/Total equity= 0.02 0.23 0.17 0.13 0.05 0.00 0.00
Interest coverage ratio= 1349.44 159.52 135.49 213.36 243.58 301.16 907.93
CFFO/capital expenditures= 0.8 3.0 4.2 2.8 2.1 3.8
Depreciation/CFFO= 7.1% 17.9% 46.5% 60.4% 37.4% 24.0% 11.4%
Capital expenditures/Depreciation= 7.02 0.72 0.39 0.97 1.98 2.29
TOTAL DEBT / CASH FLOW = 0.02 0.31 0.30 0.34 0.10 0.00 0.00
TOTAL DEBT / FREE CASH FLOW = -1.21 0.46 0.45 0.15 0.00 0.00
Mar-17 Mar-18 Mar-19 AVG MIN MAX
43.43 39.13 31.38 37.31 31.38 43.43
8.41 9.33 11.63 9.87 8.41 11.63
20.00 17.22 30.60 13.54 2.46 30.60
6.48 6.92 7.29 7.76 4.75 11.27
3.18 3.34 3.65 2.53 1.38 3.65
0.00 0.00 0.00 0.02 0.00 0.07
0.00 0.00 0.00 0.06 0.00 0.23
770.99 840.07 583.64 551 135 1349
3.5 6.0 1.2 3.0 0.8 6.0
12.2% 14.0% 61.5% 29.2% 7.1% 61.5%
2.36 1.20 1.31 2.03 0.39 7.02
0.00 0.00 0.00 0.11 0.00 0.34
0.00 0.00 0.00 -0.02 -1.21 0.46
LESS THAN 3 IS BEST
 INCOME STATEMENT
Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
SALES 15758 19398 23579 23768 25275 27585 28443 28500 32230 33651
% of Growth YoY 23% 22% 1% 6% 9% 3% 0% 13% 4%
COST OF GOODS SOLD 10,949 14424.5 17749.5 17675.2 18583.2 20217.7 19665.6 19228.2 22260 23782.6
GROSS PROFIT 4809 4,973 5,830 6,093 6,692 7,368 8,777 9,272 9,970 9,868
SELLING,GENERAL&ADMINISTRATIVE EXP 2,135 2,489 2,378 2,743 3,135 4,040 4,346 4,511 4,737 4,995
OPERATING INCOME BEFORE DEPRECIATION 3025 2822 3983 3683 3986 3880 4877 5157 5806 5621
DEPRECIATION,DEPLETION&AMORTIZATION 191 402 1097 1142 1107 540 438 493 556 602
OPERATING PROFIT 2834 2,420 2,886 2,541 2,879 3,340 4,440 4,665 5,250 5,019
OTHER INCOME 363 290 365 398 446 493 422 522 526 691
INTEREST EXPENSE 2 15 21 12 12 11 5 6 6 9
PRETAX INCOME 2832 2,405 2,865 2,529 2,867 3,329 4,435 4,658 5,244 5,011
% of Growth YoY -15% 19% -12% 13% 16% 33% 5% 13% -4%
TOTAL TAXES 600 477 487 411 758 943 1275 1281 1547 1626
NET PROFIT 2232 1,928 2,378 2,118 2,109 2,386 3,160 3,377 3,697 3,385
% of Growth YoY -14% 23% -11% 0% 13% 32% 7% 9% -8%
EPS 559.4 483 596 531 529 598 158 169 185 169
% of Growth YoY -14% 23% -11% 0% 13% -74% 7% 9% -8%
PRICE TO EARNINGS 3.5 3.3 3.4 2.9 4.3 4.4 18.6 19.1 19.1 15.1
PRICE 1944 1,589 2,055 1,542 2,275 2,640 2,946 3,222 3,543 2,553
DIVIDEND PAYOUT RATIO 98.4% 108.8% 37.8% 56.6% 61.5% 50.2% 45.5% 50.3% 51.3% 51.3%
MARKET CAP 7758 6,342 8,200 6,153 9,076 10,533 58,826 64,342 70,750 50,986
RETAINED EARNINGS 35 -169 1479 920 811 1187 1722 1680 1800 1647
BUFFETT'S $1 TEST 3.9

EXPENSES
Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19

COST OF MATERIALS CONSUMED 10,730 14,135 17,365 17,365 18,222 19,784 19,322 18,974 21,858 23,346
POWER AND FUEL 81 100 113 129 137 158 122 113 139 157
OTHER MANUFACTURING EXPENSES 143 165 188 214 233 245 210 205 240 251
SELLING AND ADMIN 1,279 1,397 1,155 1,392 1,592 1,931 2,007 2,077 2,159 2,150
CHANGES IN INVENTORY -6 24 84 -33 -8 30 12 -63 23 28
EMPLOYEE BENEFIT EXPENSES 560 619 736 821 930 1,173 1,316 1,396 1,540 1,730
FINANCE COSTS 2 15 21 12 12 11 5 6 6 9
DEPRECIATION 191 402 1,097 1,142 1,107 540 438 493 556 602
OTHER EXPENSES 296 473 488 530 614 937 1,023 1,038 1,037 1,114
 INCOME STATEMENT
Trailing Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16
30483 % % % % % % %
SALES 100% 100% 100% 100% 100% 100% 100%
COST OF GOODS SOLD 69.48% 74.36% 75.28% 74.37% 73.52% 73.29% 69.14%
GROSS MARGINS 30.52% 25.64% 24.72% 25.63% 26.48% 26.71% 30.86%
SELLING,GENERAL&ADMINISTRATIVE EXP 13.55% 12.83% 10.09% 11.54% 12.41% 14.65% 15.28%
EBITDA MARGINS 19.20% 14.55% 16.89% 15.49% 15.77% 14.07% 17.15%
DEPRECIATION,DEPLETION&AMORTIZATION 1.22% 2.07% 4.65% 4.80% 4.38% 1.96% 1.54%
5,024 OPERATING MARGINS 15.68% 10.98% 10.69% 9.02% 9.62% 10.32% 14.12%
1,450 OTHER INCOME/EXPENSE 2.31% 1.49% 1.55% 1.68% 1.77% 1.79% 1.49%
20 INTEREST EXPENSE 0.01% 0.08% 0.09% 0.05% 0.05% 0.04% 0.02%
5,004 PBT MARGINS 17.97% 12.40% 12.15% 10.64% 11.34% 12.07% 15.59%
TOTAL TAXES 3.81% 2.46% 2.06% 1.73% 3.00% 3.42% 4.48%
NET MARGINS 14.16% 9.94% 10.09% 8.91% 8.34% 8.65% 11.11%
3,743 EFFECTIVE TAX RATE 21.18% 19.83% 16.99% 16.25% 26.44% 28.33% 28.74%

187

10.5
1,963
Mar-17 Mar-18 Mar-19
% % %
100% 100% 100% AVG MIN MAX
67.47% 69.07% 70.68% 71.66% 67.47% 75.28%
32.53% 30.93% 29.32% 28.34% 24.72% 32.53%
15.83% 14.70% 14.84% 13.57% 10.09% 15.83%
18.10% 18.01% 16.71% 16.59% 14.07% 19.20%
1.73% 1.72% 1.79% 2.59% 1.22% 4.80%
14.53% 14.66% 12.86% 12.25% 9.02% 15.68%
1.83% 1.63% 2.05% 1.76% 1.49% 2.31%
0.02% 0.02% 0.03% 0.04% 0.01% 0.09%
16.35% 16.27% 14.89% 13.97% 10.64% 17.97%
4.50% 4.80% 4.83% 3.51% 1.73% 4.83%
11.85% 11.47% 10.06% 10.46% 8.34% 14.16%
27.51% 29.50% 32.45% 24.72% 16.25% 32.45%
 Common Form P&L
Rs Cr Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18
Sales 100% 100% 100% 100% 100% 100% 100% 100% 100%
Raw Material Cost 68% 73% 74% 73% 72% 72% 68% 67% 68%
Change in Inventory 0% 0% 0% 0% 0% 0% 0% 0% 0%
Power and Fuel 1% 1% 0% 1% 1% 1% 0% 0% 0%
Other Mfr. Exp 1% 1% 1% 1% 1% 1% 1% 1% 1%
Employee Cost 4% 3% 3% 3% 4% 4% 5% 5% 5%
Selling and Admin Cost 8% 7% 5% 6% 6% 7% 7% 7% 7%
Other Expenses 2% 2% 2% 2% 2% 3% 4% 4% 3%
Operating Profit 17% 13% 15% 14% 14% 12% 16% 17% 16%
Other Income 2% 1% 2% 2% 2% 2% 1% 2% 2%
Depreciation 1% 2% 5% 5% 4% 2% 2% 2% 2%
Interest 0% 0% 0% 0% 0% 0% 0% 0% 0%
Profit Before Tax 18% 12% 12% 11% 11% 12% 16% 16% 16%
Tax 4% 2% 2% 2% 3% 3% 4% 4% 5%
Net Profit 14% 10% 10% 9% 8% 9% 11% 12% 11%
Dividend Amount 14% 11% 4% 5% 5% 4% 5% 6% 6%
Mar-19 AVG MIN MAX
100% 100% 100% 100%
69% 70% 67% 74% A common-size financial statement is displays line
items as a percentage of one selected or common
0% 0% 0% 0% figure. Creating common-size financial statements
0% 0% 0% 1% makes it easier to analyze a company over time and
1% 1% 1% 1% compare it with its peers. Using common-size
financial statements helps investors spot trends that a
5% 4% 3% 5% raw financial statement may not uncover.
6% 7% 5% 8%
3% 3% 2% 4%
14% 15% 12% 17%
2% 2% 1% 2%
2% 3% 1% 5%
0% 0% 0% 0%
15% 14% 11% 18%
5% 4% 2% 5%
10% 10% 8% 14%
5% 7% 4% 14%
ancial statement is displays line
age of one selected or common
mmon-size financial statements
nalyze a company over time and
ts peers. Using common-size
helps investors spot trends that a
tatement may not uncover.
 HERO MOTOCORP LTD
Rs Cr Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
Net Fixed Asset Turnover 6.8 6.0 6.9 9.5 10.7 8.8 7.1 7.1 7.3
NET MARGINS 14% 10% 10% 9% 8% 9% 11% 12% 11% 10%
DIVIDEND PAYOUT RATIO 98% 109% 38% 57% 62% 50% 45% 50% 51% 51%
Depreciation as % of NFA 12% 10% 29% 37% 49% 19% 12% 11% 12% 13%

SELF-SUSTAINABLE GROWTH RATE (SSGR) -4.90% -5.76% -0.74% 16.33% 33.97% 33.11% 27.08%

TRENDS: 3 YEARS 5 YEARS 7 YEARS 10 YEARS

SALES GROWTH RATE (CAGR) 5.8% 5.9% 5.2% 8.8%
PBT Growth (CAGR) 4.2% 11.8% 8.3% 6.5%
Net Profit Growth (CAGR) 2.3% 9.9% 5.2% 4.7%

Price to Earning 17.8 15.3 11.9 9.4

Receivables (CAGR) 30.1% 25.1% 39.7% 43.6%
Inventory (CAGR) 16.8% 9.9% 6.8% 10.5%
Check for long term vs short term trends here. Check if the growth over past 3 or
5 years has slowed down / improved compared to long term (7 to 10 years)
growth numbers.
NFAT
NPM
DPR
DEP

FORMALA FOR SSGR = NFAT*NPM*(1-DPR)-DEP

 HERO MOTOCORP LTD
Rs Cr Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15
SALES 15758 19398 23579 23768 25275 27585
NET PROFIT 2232 1928 2378 2118 2109 2386
NET MARGINS 14% 10% 10% 9% 8% 9%
DEPRECIATION,DEPLETION& 191 402 1097 1142 1107 540
NFA 1659 4080 3786 3071 2243 2913
Depreciation as % of NFA 12% 10% 29% 37% 49% 19%
Net Fixed Asset Turnover 6.8 6.0 6.9 9.5 10.7
DIVIDEND PAYOUT RATIO 98% 109% 38% 57% 62% 50%
(1-DPR) 0.02 -0.09 0.62 0.43 0.38 0.50
Mar-16 Mar-17 Mar-18 Mar-19
28443 28500 32230 33651
3160 3377 3697 3385
11% 12% 11% 10%
438 493 556 602
3584 4396 4655 4619
12% 11% 12% 13%
8.8 7.1 7.1 7.3
45% 50% 51% 51%
0.55 0.50 0.49 0.49
 Cash Flow Statement 500
HERO MOTOCORP LTD
Rs Cr Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19 Total -
Cash from Operating Activity (CFO) 2,687 2,254 2,360 1,890 2,963 2,250 3,849 4,028 3,981 979 27,241 Jan-10 Jan-12 Jan-14 Jan-16 Jan-18
% Growth YoY -16% 5% -20% 57% -24% 71% 5% -1% -75% -500
Cash from Investing Activity -528 -1,322 93 -733 -1,617 12 -2,206 -1,944 -1,915 1,321 -8,840
Cash from Financing Activity -2,109 -955 -2,458 -1,056 -1,415 -2,231 -1,687 -2,096 -2,047 -2,294 -18,347 -1,000 CFFO
Net Cash Flow 49 -23 -6 101 -69 32 -44 -12 19 6 54 Cf-invest
CFO/Sales 17% 12% 10% 8% 12% 8% 14% 14% 12% 3% -1,500 Cf-finance
CFO/Net Profit 120% 117% 99% 89% 141% 94% 122% 119% 108% 29% 103.83%
Capex 2,826 791 450 1,072 1,068 1,002 1,164 668 790 -2,000
FCF -572 1,568 1,440 1,892 1,182 2,847 2,864 3,313 190 14,725
Average FCF (3 Years) 2,122 -2,500 CFFO (Good, i.e., business is generating cash)
FCF Growth YoY -374% -8% 31% -38% 141% 1% 16% -94% CFI (Bad, i.e, instead of reinvesting in business, they are liquidating it)
-3,000
FCF/Sales -3% 7% 6% 7% 4% 10% 10% 10% 1%
FCF/Net Profit -30% 66% 68% 90% 50% 90% 85% 90% 6% 4,500
Cf-finance Cf-invest CFFO Net Profit CFFO 4,000 Cash Flow From Operating Activities
Some Important Questions to Ask while Analysing Mar-19 -2,294 1,321 979 Mar-19 3,385 979 Cash Flow From Investing Activities
3,500
the Cash Flow Statement of the Company Mar-18 -2,047 -1,915 3,981 Mar-18 3,697 3,981 Cash Flow From Financing Activities
3,000
What are the major sources of cash? Mar-17 -2,096 -1,944 4,028 Mar-17 3,377 4,028 Multiple Sources
2,500
Cash Flow From Operating Activities Mar-16 -1,687 -2,206 3,849 Mar-16 3,160 3,849 Net Profit
2,000 CFFO
Is cffo sufficient to cover cap ex? Mar-15 -2,231 12 2,250 Mar-15 2,386 2,250 Cash Flow From Operating Activities
1,500
YES Mar-14 -1,415 -1,617 2,963 Mar-14 2,109 2,963 Cash Flow From Investing Activities
1,000
Relationship between Net Profit & CFFO for past 10yrs? Mar-13 -1,056 -733 1,890 Mar-13 2,118 1,890 Cash Flow From Financing Activities
Mar-12 -2,458 93 2,360 Mar-12 2,378 2,360 500
Consistent Multiple Sources
Mar-11 -955 -1,322 2,254 Mar-11 1,928 2,254 0
Jan-10 Jan-12 Jan-14 Jan-16 Jan-18
Mar-10 -2,109 -528 2,687 Mar-10 2232 2,687 Consistent
In-Consistent
 HERO MOTOCORP LTD
PROFITABILITY
YEAR Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
RETURN ON CAPITAL EMPLOYED% 80.3% 66.3% 57.6% 45.0% 48.9% 51.1% 50.3% 46.1% 44.6% 39.0%

DUPONT-ANALYSIS
YEAR Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19

NET MARGIN% 14.2% 9.9% 10.1% 8.9% 8.3% 8.6% 11.1% 11.8% 11.5% 10.1%

ASSET TURNOVER 1.8 1.8 2.4 2.5 2.5 2.6 2.3 1.9 1.9 1.9

RETURN ON ASSETS% 26.19% 17.96% 24.03% 21.95% 20.88% 22.66% 25.02% 22.89% 22.00% 19.12%

FINANCIAL LEVERAGE 2.46 3.63 2.31 1.93 1.80 1.61 1.43 1.46 1.43 1.38

RETURN ON EQUITY% 64.4% 65.2% 55.4% 42.3% 37.7% 36.5% 35.8% 33.4% 31.4% 26.3%

FREE CASH FLOW TO SALES ANALYSIS

FREE CASH FLOW = CASHFLOW FROM OPERATIONS-CAPITAL SPENDING

YEAR Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19

CFFO (CASH FLOW STATEMENT) 2254.16 2359.78 1890.43 2963.41 2250 3849.14 4028.02 3980.86 979.05

CAPITAL EXPENDITURE 2825.7 791.45 450.47 1071.66 1067.85 1002.15 1163.62 667.89 789.52

OWNER'S EARNINGS(FCFE) -571.54 1568.3 1440.0 1891.8 1182.2 2847.0 2864.4 3313.0 189.5
 FREE CASH FLOW/SALES(%) -2.95% 6.65% 6.06% 7.48% 4.29% 10.01% 10.05% 10.28% 0.56%
MINIMUM 5% IS GOOD

PROFITABILITY MATRIX

FREE CASH FLOW TO SALES 7.04% HIGH LESS RISKY COMPANY

ROE<15% AND FREE CASH FLOW>5% ROE>15% AND FREE CASH FLOW>5%
RETURN ON EQUITY % 32.7%
GOOD FREE CASH FLOW BUT BAD ROE GREAT COMPANY
FREE CASH FLOW 5%

HIGH RISK COMPANY

ROE<15% AND FREE CASH FLOW<5% ROE>15% AND FREE CASH FLOW<5%

WORST COMPANY GOOD ROE BUT BAD FREE CASH FLOW

LOW
15%
RETURN ON EQUITY

PROTABILITY MATRIX GREAT COMPANY

 AVG MIN MAX
52.92% 39.04% 80.25%

AVG MIN MAX

10.5% 8.3% 14.2%

2.2 1.8 2.6

22.3% 18.0% 26.2%

1.9 1.4 3.6

42.8% 26.3% 65.2%

GREAT COMPANY
GOOD FREE CASH FLOW B
GOOD ROE BUT BAD FREE
WORST COMPANY
GREAT COMPANY
GOOD FREE CASH FLOW BUT BAD ROE
GOOD ROE BUT BAD FREE CASH FLOW
WORST COMPANY
 HERO MOTOCORP LTD
RELATIVE VALUATION

1. EARNINGS YIELD

EARNINGS YIELD 9.55%

10 YEAR GOVT BOND YIELD 6.68% click here to know govt bond yield

YIELD SPREAD 2.87%

2. CASH RETURN

CASH RETURN 6.41%

10 YEAR GOVT BOND YIELD 6.68%

YIELD SPREAD -0.27%

3. HISTORICAL PRICE TO EARNINGS RATIO

Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
P/E RATIO 3.5 3.3 3.4 2.9 4.3 4.4 18.6 19.1 19.1 15.1

3 YR AVG P/E RATIO 17.8

5 YR AVG P/E RATIO 15.3
10 YR AVG P/E RATIO 9.4

TRAILING P/E RATIO 10.47

4. HISTORICAL PRICE TO SALES RATIO

Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
P/S RATIO 0.5 0.3 0.3 0.3 0.4 0.4 2.1 2.3 2.2 1.5
3 YR AVG P/S RATIO 2.0
5 YR AVG P/S RATIO 1.7
10 YR AVG P/S RATIO 1.0

TRAILING P/S RATIO 1.29

5. HISTORICAL PRICE TO BOOK RATIO

Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19
P/B RATIO 2.2 2.1 1.9 1.2 1.6 1.6 6.7 6.4 6.0 4.0

3 YR AVG P/B RATIO 5.4

5 YR AVG P/B RATIO 4.9
10 YR AVG P/B RATIO 3.4

TRAILING P/B RATIO 3.05

 Dicounted Cash Flow Valuation
HERO MOTOCORP LTD

Initial Cash Flow (Rs Cr) 2,122 39,037

Want to use my fundamental growth YES 39,201
Years 1-10 100%
FCF Growth Rate 12%
Discount Rate 12%
Terminal Growth Rate 2%

Net Debt Level (Rs Cr) (6,105)

Year FCF Growth Present Value

1 2,394 12.81% 2,138
2 2,701 12.81% 2,153
3 3,047 12.81% 2,169
4 3,437 12.81% 2,184
5 3,878 12.81% 2,200
6 4,304 11.00% 2,181
7 4,699 9.19% 2,126
8 5,046 7.37% 2,038
9 5,327 5.56% 1,921
10 5,526 3.75% 1,779

If you don't like using past growth rates, then you can use fundamental growth rates which are more dynamic and forwarding lo
reinvestment ( i.e., how much money you retain in the business ) and return of equity (i.e., the return you generate o

GROWTH RATE= (1- Payout Ratio) x Return on equity

ROE X (1- PAYOUT RATIO) = GROWTH

YEAR ROE (1-DPR) GROWTH
CURRENT 26.33% 48.66% 12.81%
1 26.33% 48.66% 12.81%
2 26.33% 48.66% 12.81%
3 26.33% 48.66% 12.81%
4 26.33% 48.66% 12.81%
5 26.33% 48.66% 12.81%
6 24.06% 43.93% 11.00%
7 21.80% 39.20% 9.19%
8 19.53% 34.46% 7.37%
9 17.27% 29.73% 5.56%
10 15.00% 25.00% 3.75%
TERMINAL (STABLE) 15.00% 25.00% 3.75%

WHAT IF, INITIAL FCF IS NEGATIVE, THEN MAKE YOUR BEST CASE ASSUMPTIONS,
 YEAR CFFO CAP EX FCF
CURRENT 979 50% 490
1 1104 50% 552
2 1246 50% 623
3 1406 50% 703
4 1586 50% 793
5 1789 50% 894
6 1985 50% 993
7 2168 50% 1084
8 2328 50% 1164
9 2457 50% 1229
10 2549 50% 1275
Valuation
TD

DCF Value (As calculated in cell B29)

Current Market Cap
DCF as % of Current Mkt Cap

Final Calculations
Terminal Year 5,637
PV of Year 1-10 Cash Flows 20,888
Terminal Value 18,149
Total PV of Cash Flows 39,037
Current Market Cap (Rs Cr) 39,201
Share Price 1963
Value Per Share 1954

wth rates which are more dynamic and forwarding looking. Growth in net profit is the function of
and return of equity (i.e., the return you generate on incremental retained earnings).

TH RATE= (1- Payout Ratio) x Return on equity

SSUMPTIONS,
 Dhandho Intrinsic Value Calculation
Read the book - The Dhandho Investor by Mohnish Pabrai

HERO MOTOCORP LTD HERO MOTO

Dhandho - Worst Case Dhandho -
Year FCF (Rs Cr) PV of FCF (Rs Cr) Assumed FCF Growth Year
0 Excess Cash (Latest) 6,105 Year 1-3 15% 0
1 FY18 2,441 2,179 Year 4-6 10% 1
2 FY19 2,807 2,238 Year 7-10 5% 2
3 FY20 3,228 2,297 Discount Rate 12% 3
4 FY21 3,551 2,256 4
5 FY22 3,906 2,216 Last 5-Years' CAGR 5
6 FY23 4,296 2,177 Sales 6% 6
7 FY24 4,511 2,041 PBT 12% 7
8 FY25 4,736 1,913 FCF -37% 8
9 FY26 4,973 1,793 9
10 FY27 5,222 1,681 10
10 52,220 16,813 10
Intrinsic Value 43,710 Intrinsic Va
Current Mkt. Cap. 39,201 Current Mkt. C
Premium/(Discount) -10% Premium/(Discou

You have to adjust the initial free cash flows in case if they are negative.
lue Calculation
vestor by Mohnish Pabrai

HERO MOTOCORP LTD

Dhandho - Best Case
FCF (Rs Cr) PV of FCF (Rs Cr) Assumed FCF Growth
Excess Cash (Latest) 6,105 Year 1-3 20%
FY18 2,547 2,274 Year 4-6 15%
FY19 3,056 2,436 Year 7-10 10%
FY20 3,667 2,610 Discount Rate 12%
FY21 4,217 2,680
FY22 4,850 2,752
FY23 5,578 2,826
FY24 6,135 2,775
FY25 6,749 2,726
FY26 7,424 2,677
FY27 8,166 2,629
122,492 39,439
Intrinsic Value 71,930
Current Mkt. Cap. 39,201
Premium/(Discount) -46%

hey are negative.

 Expected Returns Model
HERO MOTOCORP LTD
Particulars Mar/10 Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19
Net Profit (Rs Crore) 2,232 1,928 2,378 2,118 2,109 2,386 3,160 3,377 3,697 3,385
Net Profit Margin 14% 10% 10% 9% 8% 9% 11% 12% 11% 10%
Return on Equity 64% 65% 55% 42% 38% 36% 36% 33% 31% 26%

Calculations (Enter values only in black cells)

Estimated CAGR in Net Profit over next 10 years 10%
Estimated Net Profit after 10 years (Rs Cr) 8,779
Current P/E (x) 10.5
Exit P/E in the 10th year from now (x, Estimated) 20.0
Multi-Bagger Gains %
Esti. Market Cap (10th year from now; Rs Cr) 175,590
Cost of Capital/Discount Rate
Discounted Value (Rs Cr)
12%
56,535
222%
Current Market Cap (Rs Cr) 39,201

If you invest 1 lakh today, in 10 yrs it will grow into 447923

Expected Total Return (Cagr) from this stock in 10 years 16.18%

 CAGR (9-Yr) CAGR (5-Yr)
5% 10%

Bagger Gains %

222%
 Intrinsic Value Range
HERO MOTOCORP LTD
Market Cap Value Per Share
Lower Higher Lower Higher
Dhandho 43,710 71,930 2,188.35 3,601.20
DCF 39,037 1,954.42
Expected Return 56,535 2,830.45
Current Market Cap. 39,201 share price 1,962.60

ENTRY PRICE ASSUMPTIONS

PHASE-1 2,534 No.Of Shares Bought in Phase-1
PHASE-2 2,281 No.Of Shares Bought in Phase-2
PHASE-3 2,053 No.Of Shares Bought in Phase-3
Total No.Of Shares Bought
EXIT PRICE
PHASE-1 3601 Capital Gains by selling in Phase-1
PHASE-2 3961 Capital Gains by selling in Phase-1
PHASE-3 4357 Capital Gains by selling in Phase-1
Total Capital Gains (PROFITS)
 LET'S CALCULATE THE RETURN YOU CAN GENERATE

YOUR INVESTMENT = 30,000

Phase-1 Phase-2 Phase-3

10000 10000 10000

4
4
5
13

5,847
7,432
9,175 Before 30,000
22,455 74.85% After 52,455
 2,289
15847 3973
17432.02
19175
52455

Total Return
10,000

74%
HERO MOTOCORP LTD
SCREENER.IN
Narration Sep-17 Dec-17 Mar-18 Jun-18 Sep-18 Dec-18 Mar-19 Jun-19 Sep-19 Dec-19
Sales 8,372 7,314 8,564 8,810 9,091 7,865 7,885 8,030 7,571 6,997
% Growth YOY 9% 8% -8% -9% -17% -11%
Expenses 6,916 6,156 7,193 7,433 7,712 6,760 6,816 6,872 6,470 5,958
Operating Profit 1,456 1,158 1,371 1,377 1,379 1,105 1,069 1,158 1,101 1,039
Other Income 118 110 167 116 224 188 164 956 148 182
Depreciation 136 138 148 148 152 152 150 236 203 204
Interest 2 2 2 2 2 2 2 4 8 6
Profit before tax 1,436 1,128 1,387 1,343 1,448 1,138 1,081 1,874 1,038 1,012
PBT Margin 17% 15% 16% 15% 16% 14% 14% 23% 14% 14%
% Growth YOY 1% 1% -22% 40% -28% -11%
Tax 425 323 420 434 472 369 351 616 163 131
Net profit 1,010 805 967 909 976 769 730 1,257 875 880
% Growth YOY -3% -5% -25% 38% -10% 14%
OPM 17% 16% 16% 16% 15% 14% 14% 14% 15% 15%
 NEVER PLACE YOUR BUY ORDER WITHOUT GOING THROUGH THIS CHECKLIST

STUDY ITS PRODUCTS/SERVICES CAREFULLY,CHECK WHETHER THEY EXIST IN REAL WORLD AND WHAT
THEY CLAIM ABOUT THEIR PRODUCT/SERVICE IS TRUE OR MAKES SENSE.

READ ATLEAST 10 YEARS OF ANNUAL REPORTS SLOWLY AND SKEPTICALLY.

ARE YOU EXITED BY STORYLINE AND IGNORING WARNING SIGNS?

IS IT JUST LOW P/E OR LOW VALUATION THAT IS TEMPTING YOU TO INVEST? BUY CHEAP STOCKS, BUT
NOT CHEAP QUALITY STOCKS.

STAY AWAY FROM ACQUISITION HUNGRY COMPANIES.

IF YOU FIND ANY RED FLAGS IN YOUR ANALYSIS, NEVER IGNORE, DIG DEEPER.

IF RESULTS ARE GETTING DELAYED.STAY AWAY.

STAY AWAY FROM COMPANIES WHERE INSIDERS ARE SELLING LARGE STAKES.

PROMOTER SHARES ARE PLEDGED,THAT IS ALMOST EQUAL TO SELLING SHARES, SO BE WARY.

IF COMPANY IS FACING TROUBLES COLLECTING CASH (INCREASING RECEIVABLE DAYS).STAY AWAY.

ANALYSE RELATED PARTY TRANSACTIONS CAREFULLY, ESPECIALLY LOANS.

READ 10-YEARS OF REVENUE RECOGNITION POLICIES AND WATCH OUT FOR ANY POLICY CHANGES.

READ 10-YEARS OF AUDITOR REPORTS.

BE WARY WITH COMPANIES NOT CONDUCTING CONFERENCE CALLS AND NO CHAIRMANS LETTER IN THE
ANNUAL REPORT.

NO FINANCIAL DATA ABOUT ACQUIRED COMPANIES, WATCH OUT.

NEVER INVEST IN COMPANIES WITH HIGH POLITICAL CONNECTION.

COMPANY NAME HERO MOTOCORP LTD
LATEST VERSION 2.10 PLEASE DO NOT MAKE ANY CH
CURRENT VERSION 2.10

META
Number of shares 19.97
Face Value 2
Current Price 1962.6
Market Capitalization 39200.85

PROFIT & LOSS

Report Date Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15
Sales 15758.18 19397.93 23579.03 23768.11 25275.47 27585.3
Raw Material Cost 10,730.41 14,135.17 17,365.41 17,364.86 18,221.53 19,783.88
Change in Inventory -5.95 24.06 83.84 -32.80 -8.36 29.97
Power and Fuel 81.05 100.47 112.66 129.18 137.46 158.47
Other Mfr. Exp 143.49 164.83 187.56 213.96 232.61 245.4
Employee Cost 560.32 618.95 735.52 820.92 930.04 1172.87
Selling and admin 1278.97 1396.59 1155.37 1392.12 1591.85 1930.63
Other Expenses 296.02 473.29 487.57 529.79 613.56 936.88
Other Income 363.33 289.62 364.57 398.38 446.38 492.74
Depreciation 191.47 402.38 1097.34 1141.75 1107.37 539.97
Interest 2.1 15.17 21.3 11.91 11.82 11.09
Profit before tax 2831.73 2404.76 2864.71 2529.2 2867.25 3328.82
Tax 599.9 476.86 486.58 411.04 758.17 943.18
Net profit 2231.83 1927.9 2378.13 2118.16 2109.08 2385.64
Dividend Amount 2196.7 2096.85 898.65 1198.2 1298.05 1198.2

Quarters
Report Date Sep-17 Dec-17 Mar-18 Jun-18 Sep-18 Dec-18
Sales 8371.74 7314.21 8564.04 8809.82 9090.94 7864.82
Expenses 6916.04 6156.22 7193.45 7432.53 7712.23 6760.05
Other Income 117.6 110.02 166.5 115.73 223.66 187.64
Depreciation 136.03 138.28 148.34 148.17 151.78 151.83
Interest 1.56 1.57 1.54 2.11 2.14 2.16
Profit before tax 1435.71 1128.16 1387.21 1342.74 1448.45 1138.42
Tax 425.22 322.73 419.81 433.57 472.17 369.32
Net profit 1010.49 805.43 967.4 909.17 976.28 769.1
Operating Profit 1455.7 1157.99 1370.59 1377.29 1378.71 1104.77

BALANCE SHEET
Report Date Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15
Equity Share Capital 39.94 39.94 39.94 39.94 39.94 39.94
Reserves 3425.08 2916.12 4249.89 4966.3 5559.93 6501.39
Borrowings 66.03 693.35 719.44 641.58 284.26
Other Liabilities 4992.04 7082.8 4886.14 4001.16 4217.97 3986.68
Total 8523.09 10732.21 9895.41 9648.98 10102.1 10528.01
Net Block 1658.78 4080.28 3785.51 3070.98 2243.25 2912.69
Capital Work in Progress 48.14 49.96 38.84 62.09 854.11 712.55
Investments 3925.71 5,128.75 3964.26 3623.83 4088.77 3154.11
Other Assets 2890.46 1473.22 2106.8 2892.08 2915.97 3748.66
Total 8523.09 10732.21 9895.41 9648.98 10102.1 10528.01
Receivables 108.39 130.59 272.31 665 920.58 1389.59
Inventory 436.40 524.93 675.57 636.76 669.55 815.49
Cash & Bank 1907.21 71.52 76.82 181.04 117.5 159.25
No. of Equity Shares 199687500 199687500 199687500 199687500 199687500 199687500
New Bonus Shares
Face value 2 2 2 2 2 2

CASH FLOW:
Report Date Mar-10 Mar-11 Mar-12 Mar-13 Mar-14 Mar-15
Cash from Operating Activity 2686.64 2254.16 2359.78 1890.43 2963.41 2250
Cash from Investing Activity -528.17 -1322.31 92.79 -732.94 -1617.02 12.08
Cash from Financing Activity -2109.31 -955.23 -2458.16 -1056.27 -1414.93 -2230.52
Net Cash Flow 49.16 -23.38 -5.59 101.22 -68.54 31.56

PRICE: 1944.4 1589.45 2055.25 1542 2274.8 2639.8

DERIVED:
Adjusted Equity Shares in Cr 3.99 3.99 3.99 3.99 3.99 3.99
DO NOT MAKE ANY CHANGES TO THIS SHEET

Mar-16 Mar-17 Mar-18 Mar-19

28442.7 28500.46 32230.49 33650.54
19,321.72 18,974.11 21,857.79 23,346.10
11.88 -63.17 23.15 28.38
122.13 112.62 138.77 157.48
209.85 204.61 240.32 250.64
1315.93 1396.01 1540.13 1730.24
2006.56 2077.3 2159.1 2150.35
1023.42 1037.83 1037.34 1114.02
422.43 522.43 525.82 691.25
437.64 492.73 555.6 602.01
4.89 6.05 6.25 8.60
4434.87 4658.46 5244.16 5010.73
1274.68 1281.34 1546.8 1625.86
3160.19 3377.12 3697.36 3384.87
1437.84 1697.45 1897.15 1737.82

Mar-19 Jun-19 Sep-19 Dec-19

7884.96 8030.27 7570.7 6996.73
6815.64 6872.28 6469.58 5957.76
164.22 956.31 147.72 182.21
150.23 236.1 203.43 203.73
2.19 4.37 7.67 5.92
1081.12 1873.83 1037.74 1011.53
350.8 616.49 162.94 131.12
730.32 1257.34 874.8 880.41
1069.32 1157.99 1101.12 1038.97

Mar-16 Mar-17 Mar-18 Mar-19

 39.94 39.94 39.94 39.95
8794.47 10071.35 11728.94 12817.17

3796.17 4645.42 5035.94 4848.68

12630.58 14756.71 16804.82 17705.8
3584.35 4395.59 4654.54 4618.58
605.4 465.05 318.39 541.86
4581.02 5889.85 7525.2 5968.61
3859.81 4006.22 4306.69 6576.75
12630.58 14756.71 16804.82 17705.8
1282.8 1561.87 1520.18 2821.57
672.98 656.31 823.58 1,072.37
131.36 136.73 141.34 136.46
199690088 199696838 199696838 199726884

2 2 2 2

Mar-16 Mar-17 Mar-18 Mar-19

3849.14 4028.02 3980.86 979.05
-2206.19 -1943.94 -1915.22 1321.23
-1686.69 -2095.63 -2046.66 -2293.98
-43.74 -11.55 18.98 6.3

2945.7 3221.95 3542.8 2553.15

19.97 19.97 19.97 19.97

How to use it?
You can customize this workbook as you want.
You can add custom formating, add conditional formating, add your own formulas… do ANYTHING.
Please don't edit the "Data Sheet" only.

After customization, you can upload this back on Screener.

Upload on: https://www.screener.in/excel/

Download your customized workbooks now onwards.

Now whenever you will "Export to excel" from Screener, it will export your customized file.

TESTING:
This is a testing feature currently.
You can report any formula errors on the worksheet at: [email protected]
… do ANYTHING.

dalal-street.in