Safal Niveshak Stock Analysis Excel (Ver

www.safalniveshak.com

HOW TO USE THIS SPREADSHEET

Step 1 - This spreadsheet works only  on
 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
your chosen company. Scroll down and come to the first financial statement table called "Quarterly Result
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. Cli
the company's financial data will be exported in an excel file in the exact format as "Safal Niveshak's Stoc
5.0". Now onwards, any excel you export for any company on Screener.in will be downloaded in this very
Step 5 - Email me your love and testimonial for helping you with tthis
his excel. :-)

IMPORTANT INSTRUCTIONS
1. Ensure that the company whose data you are downloading has numbers at least starting from FY11 (M
because if, for instance, the company has financials starting from, say, FY15, you will see incorrect data f 
(which will be of Bajaj Auto on whose financials I have created this Analysis sheet format)
2. All financial data of your chosen company will be automatically updated in the sheet you downl
and Bank" (Balance Sheet sheet) and Capex (Cash Flow sheet)  figures, which you must update ma
company's annual reports. Don’t forget to make these changes as these numbers are key inputs in
Value calculations.
3. You may update the sheet and add your own analysis, formulae etc. and then upload again to Screene
Step 2 mentioned above. But DON'T touch the sheet titled "Data Sheet"  because this will cause errors

downloads.
4. DON’T touch any cell except the black ones,  where you are required to update the numbers manuall
Reports (just Cash and Capex numbers) or where you may change the growth assumptions etc.
 

4. I have added Comments and Instructions wherever necessary so as to explain the concepts. Read thos
working on the sheet.
5. This sheet is not a replacement of the work required to read annual reports as part of the analysis proc
that along with working on this sheet. You may sometime find some discrepancy in numbers (though rare)
this only when you read annual reports.
6. I could not find a bug/errors in this spreadsheet, but if you notice some, please email me at - vishal@sa
and I will try to fix the same and update the sheet
7. I will keep on updating the sheet from time to time and will update the same on the website. I invite you
feedback and thoughts on the sheet so that we can make it better together.
8. This excel won't work for banking and financial services companies.
9. You may see a blank excel when you download from Screener. Click on "Enable Editing" in the t
that excel to populate the data.

Note: All
Note:  All data is sourced from Screener.in

Go to Main Sheet
 

If you have received Safal Niveshak’s Stock Analysis Excel Template 5.0
as a forward and you wish to get all future updates, plus all my insights
and ideas on stock investing, please subscribe below to my free
newsletter – The Safal Niveshak Post . Subscribers call it a “gold mine”
for investors. When you subscribe, you can also instantly access all my
special e-books on investing ever written.
writt en. All for FREE!

CLICK TO SUBSCRIBE (IT'S FREE!)

 

Safal Niveshak Stock N

Analysis Excel (Ver. 5.0)
Analys
www.safalniveshak.com

Balance S
Basic Company Details
Parameters Details
Company BAJAJ AUTO LTD Income Stat
Current Stock Price (Rs) 2,900
Face Value (Rs) 10.0
Cash Flo
No. of Shares (Crore) 28.9
Market Capitalization (Rs Crore) 83,916
Key Rati
Key Financials - Trend
Parameters Details
Charts
Sales Growth (9-Year CAGR) 6.9%
Profit Before Tax Growth (9-Year CAGR) 4.7%
Net Profit Growth (9-Year CAGR) 4.7% Common S
 Average Debt/Equity (5-Years, x) 0.0
 Average Return on Equity (5-Years) 23.5%
Quarterl
 Average P/E (5-Years, x) 16.8
TTM EPS (Rs) 5,212
Latest P/E (x) 16.1

If you have received Safal Niveshak’s Stock Analysis Excel

Template 5.0 as a forward and you wish to get all future
updates, plus all my insights and ideas on stock
s tock investing,
please subscribe below to my free newsletter – The Safal
Niveshak Post . Subscribers call it a “gold mine” for
investors. When you subscribe, you can also instantly
access all my special e-books on investing ever written. All
for FREE!

CLICK TO SUBSCRIBE (IT'S FREE!)

 

vigation Buttons Warning! Excel can

Warning! can be a wonderfu
wonderfu
Butt it
Bu it can
can be a dea
deadldly
y wea
weapopon
n if
if y
the future! So be very careful of
Here, garbage in will always
always e
 eq
qua
need
ne ed th
the
e exc
excel
el to te
tell
ll yo
you
u wha
whatt y
is Valuation sto
tock
ck,, yo
youu must not use t

  eet Dhandho IV

  ment Ben Graham Remember! Focus on decisions,

disconfirming evidence.

  w DCF

  s Expected Returns Please! It's your money. Please d

this
this ex
exce
cell c
cau
ause
se you
you toto los
losee it
it a
all
ll!! I
aid
aid yyou
ourr own
own th
thin
inki
king
ng,, but
but you
you alo
alo
acti
ac tion
ons.
s. I wa
want
nt to live
live peac
peacefeful
ully
ly e
who wants
wants you
you to do thethe hard
hard wor
wor
on your
your own.
own. But I'd
I'd rather
rather give
give yo
  ize map, for you can confuse map with
the best!

Instructions
 

l tool to analyze the past.

u wish to use it to predict
hat you are getting into.
l garbage out. And if you
u must do with a given
is tool anyways.

 not outcomes. Look for

Calculate. Pray!

  n't blame me if results of

've designed this excel to
e are responsible for your
er after! I am not a sadist
by analyzing companies
u a compass instead of a
territory and lose it all. All
 

 
Parameter 

Consumer monopoly or
commodity?

Understand how business works

Is the company conservatively

financed?

Are earnings strong and do they

show an upward trend?

Does the company stick with

what it knows?

Has the company been buying

back its shares?

Have retained earnings been

invested well?

Is the company’s return on equity

above average?
 

Is the company free to adjust

prices to inflation?

Does the company need to

constantly reinvest in capital?

Conclusion

Never Forget

Go to Main Sheet
 

Buffett Checklist - Read, Remember, Follow!

Source - Buffettology by Mary Buffett & David Clark 
Explanation

Seek out companies that have no or less competition, either due to a patent or brand name or similar inta
unique. Such companies will typically have high gross and operating profit margins because of their uniqu
margins as high margins may simply highlight companies within industries with traditionally high margins.
gross, operating and net profit margins above industry norms. Also look for strong growth in earnings and

Try to invest in industries where you possess some specialized knowledge (where you work) or can more
industry, and its competitive environment (simple products you consume). While it is difficult to construct a
able to identify areas of interest. You should "only" consider analyzing those companies that operate in ar 
circle of competence. Of course you can increase the size of the circle, but only over time by learning abo
than the size of the circle is to know its boundaries.

Seeks out companies with conservative financing, which equates to a simple, safe balance sheet. Such c
flows, with little need for long-term debt. Look for low debt to equity or low debt-burden ratios. Also seek c
consistently generating positive free cash flows.

Rising earnings serve as a good catalyst for stock prices. So seek companies with strong, consistent, and
companies with 5/10 year earnings per share growth greater than 25% (along with safe balance sheets).
growth is still strong, look for companies where the last 3-years earnings growth rate is higher than the las
important than the rate of growth is the consistency in such growth. So exclude companies with volatile ea
the "average" growth has been high.

Like you should stock to your circle of competence, a company should invest its capital only in those busi
competence. This is a difficult factor to screen for on a quantitative level. Before investing in a company, l
acquisitions and new directions. They should fit within the primary range of operations for the firm. Be cau
very aggressive in acquisitions in the past.

Buffett prefers that firms reinvest their earnings within the company, provided that profitable opportunities
excess cash flow, Buffett favours shareholder-enhancing maneuvers such as share buybacks. While we d
up examination of a company would reveal if it has a share buyback plan in place.

Seek companies where earnings have risen as retained earnings (earnings after paying dividends) have b
way to screen for such companies is by looking at those that have had consistent earnings and strong ret

Consider it a positive sign when a company is able to earn above-average (better than competitors) return
debt. Average return on equity for Indian companies over the last 10 years is approximately 16%. Thus, s
this much (16%) or more than this. Again, consistency is the key here.
 

That's what is called "pricing power". Companies with moat (as seen from other screening metrics as sug
grow margins, low debt etc.) are able to adjust prices to inflation without the risk of losing significant volum

Companies that consistently need capital to grow their sales and profits are like bank savings account, an
term portfolio. Seek companies that don't need high capital investments consistently. Retained earnings m
current operations at competitive levels, so the lower the amount needed to maintain current operations, t
absolute assessment, a comparison against competitors will help a lot. Seek companies that consistently
cash flows.

Sensible investing is always about using “folly and discipline” - the discipline to identify excellent business
market to drive down the value of these businesses to attractive levels. You will have little trouble underst
successful implementation is dependent upon your dedication to learn and follow the principles, and apply

Focus on decisions, not outcomes. Look for disconfirming evidence.

 

Balance Sheet
BAJAJ AUTO LTD
Rs Cr Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19 Mar-20
Equity
ity Share Capita
ital 289 289 289 289 289 289 289 289 289 289
Reserves 4,518 5,792 7,776 9,878 10,806 13,731 17,567 20,136 22,944 21,373
Borrowings 347 150 116 59 11
112 118 120 121 125 126
Oth
the
er Liab
iabilit
ilitie
ies
s 3,99
,994 4,89
,894 4,
4,4
461 5,
5,0
081 4,7
4,758 3,1
3,102 3,
3,6
661 4,5
4,595 5,4
5,476 4,72
,722
Total 9,149 11
11,126 12,642 15,308 1
15
5,966 17,240 2
21
1,638 25,141 28,834 2
26
6,510

Net Block 1,855 1,920 2 ,3

,355 2 ,,6
660 2 ,,4
448 2 ,,0
026 2 ,,0
002 1 ,,8
878 1 ,,7
764 1,699
Ca
Capi
pita
tall Wo
Work
rk in Prog
Progre
ress
ss 70 42 294
294 144
144 255
255 52 42 56 48 60
Investments 4,211 4,473 6,059 8,448 8,985 11,067 15,477 18,895 20,603 19,914
Other Assets 3,013 4,692 3,934 4,056 4,277 4,095 4,117 4,312 6,420 4,837
Total 9,149 11
11,126 12,642 15,308 1
15
5,966 17,240 2
21
1,638 25,141 28,834 2
26
6,510

Working Capital -981 -202 -527 -1,025 -481 993 455 -283 944 115
Debtors 339 402 7
7334 796 717 7
7118 953 1,492 2,560 1,725
Inventory 576 70
704 64
644 64
641 81
814 71
719 72
728 7
74
43 96
962 1,064
Cash & Bank** 934 2,756 3,278 2,791 6,393 2,085 6,351 6,558 2,510 3,096
** Manually enter the Cash & Bank number; Convert to Rs Crore if not already done in the Annual Reports; Use Cash+Bank+Current Investments from Consolidated Balance Sheet in Annual Reports

TREND OVER YEARS

Key Ratios Dec-99 Dec-99 Dec-99 Dec-99 Dec-99 Dec-99 Dec-99 Dec-99 Dec-99 Dec-99 10 YEARS 7 YEARS 5 YEARS 3 YEARS
Debtor Days 8 7 1
13
3 14
14 12
12 12
12 16
16 22
22 31
31 2
21
1 15.6 18.2 20.2 24.5
Inven
vento
tory
ry Tu
Turn
rno
over 28 28 31 31 27 31 30 34 32 28 30.0 30.4
30 31
31.0
.0 3
31
1.2
Fixe
Fixed
d Asse
Assett Turn
Turnov
over
er 8.8
8.8 10.
10.2
2 8.5
8.5 7.6
7.6 8.8
8.8 1
11.
1.1
1 1
10.
0.9
9 13.
13.4
4 1
17.
7.2
2 17.
17.6
6 11.4
11.4 12.4
12.4 14.1
14.1 16.1
16.1
Debt/Equity 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Return on Equity 71% 49% 38% 32% 25% 29% 23% 21% 21% 24% 33% 25% 24% 22%
Return on Capital Employed 86% 65% 52% 46% 36% 40% 31% 29% 30% 31% 45% 35% 32% 30%
Return on Invested Capital 81% 86% 62% 43% 58% 34% 35% 30% 24% 28% 48% 36% 30% 27%

Go to Main Sheet

Profit & Loss Account / Income Statement

BAJAJ AUTO LTD TREND OVER YEARS
Rs Cr Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19 Mar-20 Trailing 9 YEARS 7 YEARS 5 YEARS 3 YEARS
Sales 16,408 19,582 20,025 20,137 21,595 22,574 21,755 25,210 30,358 29,919 29,919 6.9% 5.9% 6.7% 11.2%
% Growth YOY 19% 2% 1% 7% 5% -4% 16% 20% -1%
Expenses 13,249 15,901 16,377 15,982 17,663 17,786 17,331 20,377 25,168 24,823 24,832 7.2% 6.1% 7.0% 12.7%
 Material Cost (% of Sales) 73% 73% 72% 69% 69% 66% 67% 69% 72% 70%
 Power and Fuel 1% 1% 1% 1% 1% 1% 0% 0% 0% 0%
 Other Mfr. Exp 1% 1% 1% 3% 3% 3% 3% 2% 2% 2%
 Employee Cost 3% 3% 3% 4% 4% 4% 5% 4% 4% 5%
 Selling and Admin Cost 3% 4% 4% 3% 3% 4% 3% 3% 3% 3%
Gross Profit 4,506 5 ,,3
370 5 ,6
,624 6 ,2
,241 6,688 7 ,5
,580 7,087 7,810 8 ,5
,504 8 ,8
,847 7.8% 6.7% 5.8% 7.7%
Gross Profit Margin 27% 27% 28% 31% 31% 34% 33% 31% 28% 30% 30.0% 31.0% 30.9% 29.5%
Operating Profit 3,158 3 ,6
,681 3 ,6
,648 4 ,1
,155 3,932 4 ,7
,788 4,424 4,833 5 ,1
,190 5 ,0
,096 5 ,0
,087 5.5% 4.9% 5.3% 4.8%
Operating Profit Margin 19% 19% 18% 21% 18% 21% 20% 19% 17% 17% 17% 19.0% 19.1% 19.0% 17.8%
Other Income 1,406 502 797 682 425 1,199 1,473 1,417 2,036 1,846 1,855
Other Income as % of Sales 8.6% 2.6% 4.0% 3.4% 2.0% 5.3% 6.8% 5.6% 6.7% 6.2% 6.2%
Depreciation 124 147 168 181 267 307 307 315 266 246 246
Interest 2 23 1 1 6 1 1 1 4 3 3
Inte
Intere
rest
st Co
Cove
vera
rage
ge(T
(Tim
imes
es)) 1,85
1,858
8 17
177
7 3,595
3,595 5,67
5,678
8 63
630
0 5,40
5,409
9 3,992
3,992 4,53
4,530
0 1,554
1,554 2,11
2,119
9 2,119
2,119
Profit before tax (PBT) 4,438 4,013 4,277 4,655 4,083 5 ,,6
679 5,588 5,933 6,956 6,692 6,692 4.7% 6.6% 10.4% 6.2%
% Growth YOY -10% 7% 9% -12% 39% -2% 6% 17% -4%
PBT Margin 27% 20% 21% 23% 19% 25% 26% 24% 23% 22% 22% 23.1% 23.1% 23.9% 22.9%
Tax 1,006 1,020 1,229 1,420 1,271 1,618 1,508 1,714 2,028 1,480 1,480
Tax Rate 22.7% 25.4% 28.7% 30.5% 31.1% 28.5% 27.0% 28.9% 29.2% 22.1% 22.1%
Net profit 3,432 2
2,,993 3,
3,048 3,
3,234 2,
2,812 4,
4,061 4,
4,079 4,
4,219 4,
4,928 5,
5,212 5,
5,212 4.8% 8.0% 13.1% 8.5%
% Growth YOY -13% 2% 6% -13% 44% 0% 3% 17% 6%
Net Profit Margin 21% 15% 15% 16% 13% 18% 19% 17% 16% 17% 17% 16.8% 16.6% 17.4% 16.8%
EPS 118.6 103.4 105.3 111.8 97.2 140.3 141.0 145.8 170.3 180.1 180.1 4.8% 8.0% 13.1% 8.5%
% Growth YOY -13% 2% 6% -13% 44% 0% 3% 17% 6%
Pri
ric
ce to earnin
rning
g 12.3 1
16
6.2 17.1 1
18
8.6 2
20
0.8 17.1 19.9 1
18
8.8 1
17
7.1 11.2 0.0 16.9 17.7 16.8 15.7
Price 1,463 1,679 1,800 2,084 2,017 2,406 2,805 2,745 2,911 2,022 2,900
Dividend Payout 33.7% 43.5% 42.7% 44.7% 51.5% 39.2% 39.0% 41.2% 35.2% 66.6%
Market Cap 42,346 48,584 52,079 60,299 58,360 69,628 81,190 79,432 84,247 58,527 3.7% 1.7% 0.1% -10.3%
Retaine
ined Earnin
rning
gs 2,274 1,691 1,74
,746 1,78
,788 1,365 2,
2,4
470 2,48
,488 2,48
,483 3,191 1,739
Buffett's $1 Test 0.8

Check for long term vs short term trends

Check for wide fluctuations in key expense items.
here. Check if the growth over past 3 or 5
For manufacturing firms, check their material costs
years has slowed down / improved compared
etc. For services firms, look at employee costs.
to long term (7 to 10 years) growth numbers.
 Go to Main Sheet

Cash Flow Statement

BAJAJ AUTO LTD TREND OVER YEARS
Rs Cr Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17 Mar-18 Mar-19 Mar-20 Total 9 YEARS 7 YEARS 5 YEARS 3 YEARS
Cash from Operat
Operating
ing Activi
Activity
ty (CFO) 1,639 3,246 2,218 3,502 2,114 3,690 3,267 4,328 2,487 3,850 30,342 10.0% 8.2% 12.7% 5.6%
% Growth YoY 98% -32% 58% -40% 75% -11% 32% -43% 55%
Cash
Cash fr
from
om In
Inv
vesti
esting
ng Activ
ctivit
ity
y -627
-627 -850
-850 -1,3
-1,394
94 -2,0
-2,072
72 -380
-380 -68
-68 -3,6
-3,610
10 -1
-1,9
,954
54 -273
-273 1,76
1,766
6 -9,462
Cash
Cash fr
from
om Fi
Fina
nanc
ncining
g Activ
ctivit
ity
y -871
-871 -1,4
-1,462
62 -1
-1,4
,445
45 -1
-1,4
,496
96 -1
-1,6
,644
44 -3
-3,3
,384
84 -190
-190 -1
-1,8
,885
85 -2,0
-2,074
74 -6,2
-6,247
47 -20,698
Net Cash Flow 141 934 -621 -66 90 238 -532 488 140 -630 182
CFO/Sales 10% 17% 11% 17% 10% 16% 15% 17% 8% 13%
CFO/Net Profit 48% 108% 73% 108% 75% 91% 80% 103% 50% 74%
Capex** 163 87
87 488 213 253 259 191 170 87
87 266 5.6% -8.3% 1.0% 11.7%
FCF   1,477 3,159 1,730 3,289 1,861 3,430 3,077 4,158 2,399 3,584 28,165 10.4% 11.0% 14.0% 5.2%
 Average FCF (3 Years) 3,381
FCF Growth YoY 114% -45% 90% -43% 84% -10% 35% -42% 49%
FCF/Sales 9% 16% 9% 16% 9% 15% 14% 16% 8% 12%
FCF/Net Profit 43% 104% 55% 97% 61% 84% 75% 99% 49% 69%

** Manually enter this number; Convert to Rs Crore if not already done in the Annual Reports; Use ") Purchase of Property, Plant and
Equipment minus Sales of ) Purchase of Property, Plant and Equipment" number shown under "Cash Flow from Investing Activities"
segment of Consolidated Cash Flow Statement available in the Annual Reports

Go to Main Sheet

Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16

Sales Growth 19.3% 2.3% 0.6% 7.2% 4.5%
Expenses Growth 19.5% 1.5% -1.9% 11.1% -0.7%
Sustainable Growth Rate 47.3% 27.8% 21.6% 17.6% 12.3% 17.6%
Gross Profit Growth 19.2% 4.7% 11.0% 7.2% 13.3%
PBT Growth -9.6% 6.6% 8.8% -12.3% 39.1%
Net Profit Growth -12.8% 1.8% 6.1% -13.1% 44.4%
Dividend Growth 12.5% 0.0% 11.1% 0.0% 10.0%
Dividend Payout 33.7% 43.5% 42.7% 44.7% 51.5% 39.2%

Gross Margin 27.5% 27.4% 28.1% 31.0% 31.0% 33.6%

Operating Margin 19.3% 18.8% 18.2% 20.6% 18.2% 21.2%
 PBT Margin 27.0% 20.5% 21.4% 23.1% 18.9% 25.2%
Net Margin 20.9% 15.3% 15.2% 16.1% 13.0% 18.0%

Debtor Days 7.5 7.5 13.4 14.4 12.1 11.6

Inventory Turnover 28.5 2
277.8 31.1 31.4 26.5 31.4
Fixed Asset Turnover 8.8 1
100.2 8.5 7.6 8.8 11.1
Total Asset Turnover 0.6 0.6 0.6 0.8 0.7 0.8
Debt/Equity 0.1 0.0 0.0 0.0 0.0 0.0
Debt/Assets 3.8% 1.4% 0.9% 0.4% 0.7% 0.7%
In
Intteres
erestt Co
Cove
vera
rage
ge (Tim
(Times
es)) 1,85
1,8588 17
1777 3,
3,59
5955 5,
5,67
6788 63
630
0 5,
5,40
4099
Return on Equity 71.4% 49.2% 37.8% 31.8% 25.3% 29.0%
Return on Capital Employed 86.1% 64.8% 52.3% 45.5% 36.5% 40.2%
Return on Invested Capital 81.3% 86.1% 62.2% 43.5% 58.4% 33.7%

Free Cash Flow (Rs Cr) 1,477 3,159 1,730 3,289 1,861 3,430
Operating Cash Flow Growth 98.0% -31.7% 57.9% -39.6% 74.6%
Free Cash Flow Growth 113.9% -45.2% 90.1% -43.4% 84.4%
FCF/Sales 9% 16% 9% 16% 9% 15%
CFO/Total Assets 18% 29% 18% 23% 13% 21%
CFO/Total Debt 472% 2157% 1919% 5916% 1881% 3131%
Cash Int
nte
ere
res
st Covera
rag
ge 1,108.0 188.
8.2
2 2,897.5 6,003.7 52
522
2.5 5,055
55..8
CFO/Capex 10.1 37.4 4.5 16.5 8.3 14.2

Go to Main Sheet

 
Mar/17 Mar/18 Mar/19 Mar/20
-3.6% 15.9% 20.4% -1.4%
-1.4% 16.9% 24.2% -1.3%
13.9% 12.2% 13.7% 8.0%
-6.5% 10.2% 8.9% 4.0%
-1.6% 6.2% 17.2% -3.8%
0.4% 3.4% 16.8% 5.8%
0.0% 9.1% 0.0% 100.0%
39.0% 41.2% 35.2% 66.6%

32.6% 31.0% 28.0% 29.6%

20.3% 19.2% 17.1% 17.0%
 25.7% 23.5% 22.9% 22.4%
18.8% 16.7% 16.2% 17.4%

  16.0 21.6 30.8 21.0

29.9 33.9 31.6 28.1
10.9 13.4 17.2 17.6
1.0 1.0 0.9 0.9
0.0 0.0 0.0 0.0
0.6% 0.5% 0.4% 0.5%
  3,992 4,530 1,554 2,119
22.8% 20.7% 21.2% 24.1%
31.1% 28.9% 29.8% 30.7%
35.1% 30.2% 23.6% 27.9%

  3,077 4,158 2,399 3,584

-11.5% 32.5% -42.5% 54.8%
-10.3% 35.2% -42.3% 49.4%
14% 16% 8% 12%
15% 17% 9% 15%
2725% 3584% 1997% 3066%
  3,412.0 4,613.5 1,008.8 1,687.9
17.1 25.5 28.5 14.5

Key Ratios
BAJAJ AUTO LTD
What to look for?
Higher is better, but also look for long term stability and consistency 
Lower is better, but compare with sales growth. Growth higher than sales growth suggests weakening pric
Higher is better, as it indicates the effectiveness and efficiency with which the company is managing its op
Higher is better, but also look for long term stability and consistency 
Higher is better, but also look for long term stability and consistency 
Higher is better, but also look for long term stability and consistency 
Higher isn't always better, esp. when the company is generating high ROE, which means the managemen

Higher is better, but also look for long term stability and consistency, plus the nature of the industry. Also
Higher is better, but also look for long term stability and consistency, plus the nature of the industry. Also
 Higher is better, but also look for long term stability and consistency, plus the nature of the industry. Also
Higher is better, but also look for long term stability and consistency, plus the nature of the industry. Also

Lower/reducing is better. Compare with industry peer(s)

Higher/rising is better. Compare with industry peer(s)
Higher/rising is better. Compare with industry peer(s)

Nil / lower than 0.5 / reducing is better 

Lower is number
Look for better  > 5 
Look for number > 20%. Also check if the debt is low/nil. Compare with industry peer(s)
Look for number > 20%. Also check if the debt is low/nil. Compare with industry peer(s)
Look for number > 20%. Also check if the debt is low/nil. Compare with industry peer(s)

Look for positive and rising number. If the company consistently generates negative FCF over say 10 yea
Higher is better, but also look for long term stability and consistency 
Higher is better, but also look for long term stability and consistency 
The higher the percentage, the better as it shows how profitable the company is. Check for OCF growing
Higher is better. Show how well the company uses its assets to generate operating cash flow 
Higher, and more than 100%, is better. Shows the ability of the company to use its operating cash flows t 
Indicates the company's ability to make interest payments on its entire debt. A highly leveraged company
Measures the capital available for internal reinvestment and for payments on existing debt. When this rati 

 
   y be a sign of weakness and inconsistency 

  rong balance sheet will have a high multiple.

multiple. Any company with a cash interest
interest multipl
multipl
vailable to meet its capital investment 

 
   less than 1.0 runs an immediate risk of potential default 

Key Ch

Profit Margin   Gross Margin Operati ng Margi n PBT Margin

40%

30%

20%

10%
 Check for a rising trend
t rend and/or consistency. Compare with a close competit
0%
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18

Revenue (Rs Crore)

35,000
Check for a rising trend.
30,000
25,000
20,000
15,000
10,000
5,000
 -
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 M

Sustainable Growth   Sale

Sales
s Growth
wth Sus
Sustainab
inable
le Growth
wth R
40%

25%

10%

-5%
Check for a ris
rising/
ing/st
stabl
able
e trend
trend in SGR,
SGR, as that
that ind
indic
icate
ates
s how muc
muchh the
the comp
comp
through own
o wn resources, without
without needing additional sources like debt or equi
equ i
-20%
Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18

Cash Flow (Rs Crore) Operating Cash Flow Free Cash Flow
5,000
Check for positive numbers and which are rising over time.

3,750
 2,500

1,250

 -
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 M

Cash & B ank Inventory Receivables Ot

Assets Distribution
Distribution I3n%vestment4s% Fix
ixe
ed As
Ass
sets
3% 15% 1%
3%
100%6%
4%
5% 4%
5%
5%
4%
4%
4%
4%
10%
3%
6%
6% 5%
20% 6% 14% 13% 10%
16% 75%
4% 72%
17% 64%
55% 56%
75%46% 48%

40%

50%

25%

0%
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18

Go to Main Sheet

Charts  (Warning
Data for Charts (Warn ing!! Please
Please don'
don'tt touch
touch any
any number
number belo
below,
w, as
as that
that would
would chan
chan
Margins

Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19

Gross Margin 27% 27% 28% 31% 31% 34% 33% 31% 28%
Operating Margin 19% 19% 18% 21% 18% 21% 20% 19% 17%
PBT Margin 27% 20% 21% 23% 19% 25% 26% 24% 23%
Net Margin 21% 15% 15% 16% 13% 18% 19% 17% 16%

Management Effectiveness
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19
ROE 71% 49% 38% 32% 25% 29% 23% 21% 21%
ROCE 86% 65% 52% 46% 36% 40% 31% 29% 30%
ROIC 81% 86% 62% 43% 58% 34% 35% 30% 24%

Revenue & Profit Growth

Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19 Mar/20
 Revenue Growth 19% 2% 1% 7% 5% -4% 16% 20% -1%
PBT Growth -10% 7% 9% -12% 39% -2% 6% 17% -4%
Net Profit Growth -13% 2% 6% -13% 44% 0% 3% 17% 6%

Revenue & Profit

Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19
Revenue 16,408 1
199,582 20
0,,025 2
200, 137 2
211,595 22
22,574 21
1,,755 2
255, 210 3
300,358
Gross Profit 4,506 5,370 5,624 6,24
,241 6,68
,688 7,580 7,087 7,81
,810 8,50
,504
Profi
Profitt Befor
Before
e Tax
Tax 4,
4,43
438
8 4,01
4,013
3 4,27
4,277
7 4,6
4,655
55 4,08
4,083
3 5,67
5,679
9 5,58
5,588
8 5,9
5,933
33 6,95
6,956
6

Net Profit 3,432 2,993 3,048 3,234 2,812 4,061 4,079 4,219 4,928

Cash Flows
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19
Operating
Operating Cash Flow 1,639 3,246 2,218 3,502 2,114 3,690 3,267 4,328 2,487
Free
Free Cash
Cash Fl
Flow
ow 1,
1,47
477
7 3,15
3,159
9 1,73
1,730
0 3,2
3,289
89 1,86
1,861
1 3,43
3,430
0 3,07
3,077
7 4,1
4,158
58 2,39
2,399
9

Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19

Equity (Own) 53% 55% 64% 66% 69% 81% 83% 81% 81%
Liabilities (Owed) 47% 45% 36% 34% 31% 19% 17% 19% 19%

Asset Distribution
Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19
Fixed Assets 21% 18% 21% 18% 17% 12% 9% 8% 6%
Investments 46% 40% 48% 55% 56% 64% 72% 75% 71%
Other Assets 20% 17% 16% 14% 13% 10% 10% 5% 7%
Receivables 4% 4% 6% 5% 4% 4% 4% 6% 9%
Inventory 6% 6% 5% 4% 5% 4% 3% 3% 3%
Cash & Bank 3% 15% 4% 3% 4% 5% 1% 3% 3%

Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19 Mar/20

Sales Growth 19% 2% 1% 7% 5% -4% 16% 20% -1%

Sustainable Grow 28% 22% 18% 12% 18% 14% 12% 14% 8%

Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/17 Mar/18 Mar/19

FCF/Sales 9.0% 16.1% 8.6% 16.3% 8.6% 15.2% 14.1% 16.5% 7.9%
CFO/Total A
As
ssets 17.9% 29.2% 17.5% 22.9% 13.2% 21.4% 15.1% 17.2% 8.6%
 

  rts - Performance At A Glance

BAJAJ AUTO LTD

Net Margin R OE ROCE ROI

Capital Allocation Quality
Qua lity
100%
Check for a rising trend and/or con
90% Numbers > 20% long
long term are good
80% zero/marginal
zero/marginal debt. Compare
Co mpare with a
70%
60%

50%
40%
30%
 20%
r.
10%
0%
Mar/
ar/19 Mar
ar//20 Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 M

Annual Growth (YoY)   R evenue Growth P

50%

Check forCompare
volatility. a rising trend and/or
growth rateslow
30% with a close competitor.

10%

-10%

-30%
r/19 Mar/20 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16 Mar/

  ate Profit Over Time Gross Profit Profit Before Ta

10,000
Check for a rising trend.
7,500

5,000

 
 

2,500
  ny can grow
y.
 -
ar/19 Mar/20 Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16

  Cash Flow Ratios   FCF/Sales C FO/Total

40.0%
Check for high
high and rising
rising/stabl
/stable
e numbers.
numbers. Shows ho
and how well it uses its assets to generate cash flow
30.0%
 20.0%

10.0%

0.0%
r/19 Mar/20 Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16

er Assets Liabilities (O
(Owed) Equity (
Own Vs Owed Funds
3% 1%
4% 100%
3% 7%
9%
7%
7%
75%
71%
75%

50%

25%

0%
ar/19 Mar/20 Mar/11 Mar/12 Mar/13 Mar/14 Mar/15 Mar/16

  e the pre-designed charts. You may however change them if you wish to change charts

Mar/20
30%
17%
22%
17%

Mar/20
24%
31%
28%
 Mar/20
  29,919
8,847
6,692

5,212

Mar/20
  3,850
3,584

Mar/20
82%
18%

Mar/20
7%
75%
7%
7%
4%
1%

Mar/20
12.0%
14.5%
 

  istency.
. Also check if the company has
close competit
co mpetitor.
or.
 ar/17 Mar/18 Mar/19 Mar/20

T Growt
owth Net
Net Pr
Pro
ofit Growth
wth

7 Mar/18 Mar/19 Mar/20

  Net Profit

ar/17 Mar/18 Mar/19 Mar/20

 Assets

  profitable a company is
 Mar/17 Mar/18 Mar/19 Mar/20

  wn)

ar/17 Mar/18 Mar/19 Mar/20

  and/or add new ones)

Common Size P&L

Rs Cr Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17
Sales 100% 100% 100% 100% 100% 100% 100%
Raw Material Cost 73% 73% 72% 69% 69% 66% 67%
Change in Inventory 1% 0% 0% 0% 0% 0% 0%
Power and Fuel 1% 1% 1% 1% 1% 1% 0%
Other Mfr. Exp 1% 1% 1% 3% 3% 3% 3%
Employee Cost 3% 3% 3% 4% 4% 4% 5%
Selling and Admin Cost 3% 4% 4% 3% 3% 4% 3%
Other Expenses 1% 1% 1% 1% 2% 1% 2%
Operating Profit 18% 18% 19% 21% 18% 22% 20%
Other Income 9% 3% 4% 3% 2% 5% 7%
Depreciation 1% 1% 1% 1% 1% 1% 1%
 Interest 0% 0% 0% 0% 0% 0% 0%
Profit Before Tax 27% 20% 21% 23% 19% 25% 26%
Tax 6% 5% 6% 7% 6% 7% 7%
Net Profit 21% 16% 16% 17% 14% 18% 19%
Dividend Amount 7% 7% 7% 7% 7% 7% 7%

Common Size Balance Sheet

Rs Cr Mar-11 Mar-12 Mar-13 Mar-14 Mar-15 Mar-16 Mar-17
Equity Share Capital 3% 3% 2% 2% 2% 2% 1%
Reserves 49% 52% 62% 65% 68% 80% 81%
Borrowings 4% 1% 1% 0% 1% 1% 1%
Other Liabilities 44% 44% 35% 33% 30% 18% 17%
Total Liabilities 100% 100% 100% 100% 100% 100% 100%
Net Block 20% 17% 19% 17% 15% 12% 9%
Capital Work in Progress 1% 0% 2% 1% 2% 0% 0%
Investments 46% 40% 48% 55% 56% 64% 72%
Other Assets 33% 42% 31% 26% 27% 24% 19%
Total Assets 100% 100% 100% 100% 100% 100% 100%
Receivables 4% 4% 6% 5% 4% 4% 4%
Inventory 6% 6% 5% 4% 5% 4% 3%
Cash & Bank 3% 15% 4% 3% 4% 5% 1%

Go to Main Sheet

Mar-18 Mar-19 Mar-20

 A common-size financial statement is
100% 100% 100% displays line items as a percentage of one
69% 72% 70% selected or common figure. Creating
0% 0% 0% common-size financial statements makes it
0% 0% 0% easier to analyze a company over time and
2% 2% 2% compare it with its peers. Using common-
4% 4% 5% size financial statements helps investors
3% 3% 3% spot trends that a raw financial statement
may not uncover.
2% 1% 2%
19% 17% 17%
6% 7% 6%
1% 1% 1%
 0% 0% 0%
24% 23% 22%
7% 7% 5%
17% 16% 17%
7% 6% 12%

Mar-18 Mar-19 Mar-20

1% 1% 1%
80% 80% 81%
0% 0% 0%
18% 19% 18%
100% 100% 100%
7% 6% 6%
0% 0% 0%
75% 71% 75%
17% 22% 18%
100% 100% 100%
6% 9% 7%
3% 3% 4%
3% 3% 1%

Valuation - D
Read the book - The Dh

BAJAJ AUTO LTD

Dhandho IV - Lower Range
 Year FCF (Rs Cr) PV of FCF (Rs Cr) Assumed FCF Growth
0 Excess Cash (Latest) 3,096  Year 1-3 12%
1 FY21 3,786 3,381  Year 4-6 10%
2 FY22 4,241 3,381  Year 7-10 5%
3 FY23 4,750 3,381 Discount Rate 12%
4 FY24 5,225 3,320
5 FY25 5,747 3,261 Last 5-Years' CAGR
6 FY26 6,322 3,203 Sales 7%
 7 FY27 6,638 3,003 PBT 10%
8 FY28 6,970 2,815 FCF 14%
9 FY29 7,318 2,639
10 FY30 7,684 2,474
10   76,842 24,741
Intrinsic Value
Intrinsic 58,695
Current Mkt. Cap. 83,916
Premium/(Discount) to IV 43%

Note: See explanation of this model here

P.S. In case
case of co
companies
mpanies earning negativ
negative
e FCF, where this m
model
odel wi
willll not work, y
you
ou must u
assumption of FCF the business will earn in a normal year, without capex. Check the history
wi
wise
sely
ly w
wit
itho
hout
ut twis
twisti
ting
ng the
the m
mod
odel
el tto
o fit
fit y

Go to Main Sheet

  andho Calculation
  andho Investor by Mohnish Pabrai 

BAJAJ AUTO LTD

Dhandho IV - Higher Range
 Year FCF (Rs Cr) PV of FCF (Rs Cr) Assumed FCF
0 Excess Cash (Latest) 3,096  Year 1-3
1 FY21 3,888 3,471  Year 4-6
2 FY22 4,471 3,564  Year 7-10
3 FY23 5,142 3,660 Discount Rate
4 FY24 5,759 3,660
5 FY25 6,450 3,660
6 FY26 7,224 3,660
 7 FY27 7,946 3,594
8 FY28 8,741 3,530
9 FY29 9,615 3,467
10 FY30 10,576 3,405
10   158,643 51,079
Intrinsic Value
Intrinsic 89,846
Currentt Mkt. Cap.
Curren 83,916
Premium/(Discount) to IV -7%

  e a normalized positive FCF as the starting number. This number is your

of this business while arriving at your assumption, and use your judgment
ur version of reality.

  Growth
15%
12%
10%
12%
 

Valuation - Ben Gr
Low Range
Company Name BAJAJ AUTO LTD
Year Ended Mar/20

 Avg 5-Yr Net Profit (Rs Crore) 4,499.8

PE Ratio at 0% Growth 8.5
Long-Term Growth Rate 6.6

Ben Graham Value (Rs Crore)   97,357

Current Market Cap (Rs Crore)   83,916

Value as % of Market Cap 116%
 EXPLANAT
Ben Graham's Original Formula: Value = EPS x (8.5 + 2G)
Here,, EPS
Here EPS is the traili
trailing
ng 12
12 mont
month
h EPS,
EPS, 8.5 is the P/E ratio
ratio of
of a st
7-10 years

Ben Grah
Graham's
am's Revi
Revised
sed Form
Formulaula:: Value
Value = [EPS
[EPS x (8.5
(8.5 + 2G) x 4.
Here, 4.4 is what Graham determined to be his minimum required
was publicizing
publicizing his
his works, the
the risk free interest
interest rate was 4.4%
4.4% but t
 AAA corporate bond rate, represented by Y in the formula above.

Note: I have used Graham's original formula in the above calc

Go to Main Sheet

  ham Formula
High Range
Company Name BAJAJ AUTO LTD
Year Ended Mar/20

 Avg 5-Yr Net Profit (Rs Crore) 4,499.8

PE Ratio at 0% Growth 8.5
Long-Term Growth Rate 9.9

Ben Graham Value (Rs Crore)   126,911

Current Market Cap (Rs Crore)   83,916

Value as % of Market Cap 151%
 ON

  ck with 0% growth and g is the growth rate for the next

  4] / Y
ate of return. At the time of around 1962 when Graham
o adjust to the present, we divide this number by today’s

  lations

Valuation - Dicounted Cash

BAJAJ AUTO LTD

Initial Cash Flow (Rs Cr) 3,381 64,436

83,916
Years 1-5 6-10 77%
FCF Growth Rate 12% 10%
Discount Rate 12%
Terminal Growth Rate 2%

Net Debt Level (Rs Cr) (2,971)

  Year FCF Growth Present Value
1 3,786 12% 3,381
2 4,241 12% 3,381
3 4,750 12% 3,381
4 5,320 12% 3,381
5 5,958 12% 3,381
6 6,554 10% 3,320
7 7,209 10% 3,261
8 7,930 10% 3,203

190 8,,5
9 79
25
3 10
10%
% 3,,0
3 1849
6

Final Calculations
Terminal Year 9,787
PV of Year 1-10 Cash Flows 32,923
Terminal Value 31,513
Total PV of Cash Flows 64,436
Current Market Cap (Rs Cr) 83,916

Note: See explanation of DCF here

Go to Main Sheet

  low

DCF Value (As calculated in cell B29)

Current Market Cap
DCF as % of Current Mkt Cap
 

Valu
Valua
ati
tion
on - Exp
xpec
ecte
te
BAJAJ AUT
Particulars Mar/11 Mar/12 Mar/13 Mar/14
Net Profit (Rs Crore) 3,455 3,049 3,133 3,380
Net Profit Margin 21% 16% 16% 17%
Return on Equity 71% 49% 38% 32%

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) 13,518

Curre
Exit nt Pin/Ethe
P/E (x)10th year from now (x, Estimated)   16.1
20.0
Esti. Market Cap (10th year from now; Rs Cr) 270,367
 Cost of Capital/Discount Rate 12%
Discounted Value (Rs Cr)   87,051
Current Market Cap (Rs Cr)   83,916

Note: See explanation of this model here

Go to Main Sheet

 Returns Model
  LTD
Mar/15 Mar/16 Mar/17 Mar/18 Mar/19 Mar/20 CAGR (9-Yr) CAGR (5-Yr)
  3,026 4,061 4,079 4,219 4,928 5,212 5% 11%
14% 18% 19% 17% 16% 17%
25% 29% 23% 21% 21% 24%
 

Intrinsic Value Range

BAJAJ AUTO LTD
Lower Higher  
Dhandho   58,695 89,846
Ben Graham 97,357 126,911
DCF 64,436 Remember! Giive import
Remember! G
Expected Return 87,051 fai
fair v
val
alue
ue only
nly "af
"afte
ter"
r" y
these two questions - (1)
Curr
Curren
entt Mar
arke
kett Ca
Cap.
p. 83,,91
83 916
6
unde
un derst
rstoo
ood?
d? and
and (2)
(2) Ca
Ca

Don't try to quantify eve

Explanation: Considering the above range, less

less non-
noand
sensible, n-ma
math
them
emat
useful atic
ic
will
we can say that Bajaj Auto's Intrinsic Value Great analysis is gener
 range is between Rs 60,000 crore and Rs
130,000 crore. It's a big range, but that's fine Also
Also,, your
your cal
calcu
cula
late
ted
d "f
"faa
(who is looking for precision?). Now, if the in th
the fu
futu
turre, so
so d
do
on't inv
current market cap is within this IV range, you fall in love with it.
especially when it's near the lower level of overrated. Focus on deci
the range, it makes the stock disconfir
reasonably/attractively priced. If the current
market cap is higher then the higher value of
the range, it makes it overpriced. But
remember, these are just numbers!

Go to Main Sheet

  ance to a stock's valuations /

u have answered in "Yes" to
Is this business simple to be
I understand this business?

  ything. In stock research, the

lbe
you are,
your the more
analysis simple,
and results.
ally "back-of-the-envelope".