A few readers have accused me in the past of being a sadist who wants them to do the dirty work of analyzing companies on their own, instead of simply recommending stocks like so many other blogs do.
But I’d rather give you a compass instead of a map, for you can confuse the map with the territory and lose your life’s savings walking that path.
In this pursuit of handing you another compass, here is Version 3.0 of my Stock Analysis Excel Sheet that you can download on your computer, read through the instructions to follow a few simple steps, and then analyze not just the past performance of a company but also arrive at its approximate intrinsic value range.
And unlike the previous versions where you were required to enter most data manually, this latest version feeds in data automatically from Screener.in website, which subsequently feeds into my sheets on financial analyses and intrinsic value calculations. So you must thank Screener’s creators and my friends Ayush and Pratyush before thanking me. 🙂
If you have been into financial modeling in the past, this excel file may seem like a child’s play. But, if my 14+ years of experience as an analyst is anything to go by, this is most of all you require to “quantitatively” analyze stocks…not models running into hundreds of rows and tens of sheets.
I have personally tried my hands at both the kind of models – the simple one that you can download below, and the complex ones that most analysts use in their doomed pursuits of finding the elusive target prices. Without a doubt, simplicity beats complexity hands down.
By the way, my analysis excel that you will download below – especially the intrinsic value calculations – works well with simple businesses that have a good track record of performance. Complex, volatile businesses must not be analyzed anyways, and no spreadsheet can help you there. Also, this excel won’t work for banking and financial services companies.
Let me now explain briefly the sheets this excel file contains:
- Instructions: After downloading the excel file on your computer, read this instructions sheet carefully before proceeding to use the rest of the excel.
- Summary: Contains some basic data of the company and a few warnings and disclaimers.
- Checklist: Contains a simple Buffett checklist that will help you a lot in your overall decision-making.
- Balance Sheet: Contains key Balance Sheet numbers and a few related ratios. Updated automatically. You just have to manually enter the “Cash & Bank” number (in Rs Crore) from the annual reports.
- Profit & Loss: Contains key Profit & Loss Statement numbers. Updated automatically.
- Common Size Analysis: Updated automatically. Contains common size analysis of both the Balance Sheet and the P&L Statement, and is an insightful way to analyze the changes in key numbers over the years, and compare across companies.
- Cash Flow: Contains key Cash Flow numbers. Updated automatically, except the “Capex” number that you must enter manually (in Rs Crore) from the annual reports.
- EPV Valuation: Updated automatically. Contains intrinsic value calculation as per the EPV framework described by Bruce Greenwald in his book Value Investing: From Graham to Buffett and Beyond.
- Dhandho Valuation: Updated automatically, though you may change assumptions in the black cells. Contains intrinsic value calculation as per the framework described by Mohnish Pabrai in his book The Dhandho Investor.
- Ben Graham Formula: Updated automatically. Contains intrinsic value calculation as per the framework mentioned by Ben Graham in The Intelligent Investor.
- DCF: Updated automatically. Contains intrinsic value calculation as per the discounted cash flow method.
- Expected Returns: Updated automatically, though you may change assumptions in the black cells. Contains intrinsic value calculation as per the framework described by Prof. Sanjay Bakshi in an interaction with me.
- Intrinsic Values: Updated automatically. Contains intrinsic value ranges calculated in the previous five sheets to enable you to assess the broad range as compared to the market cap of the stock.
- Quarters: Updated automatically. Contains a few key quarterly numbers.
- Data Sheet: Don’t touch this sheet at all. This is the core sheet from Screener.in site and any changes to it may produce errors if you want to customize the Safal Niveshak excel and upload again to Screener’s site.
Click Here to Download – Safal Niveshak Stock Analysis Excel Version 3.0
While you can download this Analysis Sheet FREE of charge, you may pay me through your feedback, testimonials, and tweets. 🙂
Words of Warning!
Before you get down to using this excel, remember six critical things…
- It’s just a compass and not a map. So take your next step carefully.
- Don’t look for perfection. It is overrated.
- Focus on decisions, not outcomes.
- Look for disconfirming evidence. Avoid falling in love with the numbers.
- Remember Charlie Munger who said, “All I want to know is where I’m going to die, so I won’t go there.” Depending just on this excel for decision-making can really kill you (financially)!
Let me know if you found this excel helpful. I will try to improve upon this in the future based on your feedback.
Share your thoughts, suggestions, and testimonial for this excel in the Comments section of this post.
This is great work Vishal!! Many thanks for creating this gem.
Thanks Ruchir!
Tried today. Fantastic work. Thanks a lot. Shall share once I really go through and study the output
Sure, thanks Deepak!
Thank you very much, Vishal for providing such a tool which does all the mundane manual work on its own. This is simple and easy to use excel. One can use this to zero down the stocks which he wants to invest. Again all your disclaimers are well written and it becomes our responsibility to evaluate what a good stock is. This tool brings down the pain work of an investor to visit different websites to capture data. I will use this tool in the coming days to come out with my stock picks for investment.
Glad to know that, Roshan. Thanks!
You are making us lazy!
Thanks a ton anyway!
Thanks Monil! 🙂
Simple awesome. You saved the pain of manual entry. Now one need to focus only on AR.
Thanks Rakesh!
Vishal Sir, Hats off to you !!!
Best Gift one can give to small or novice investors.
I am in CA Final & practically it was very difficult to me to make my own models for valuation.l learned different valuation techniques from different sources. like Expected Return from Sanjay Bakshi Sir, Dhandho Valuation from Mohnish Sir, EPV & Asset value method from Bruce Greenwald Sir. But above all the best part is you provided practical exposure to entire toolkit of Valuation.
Glad to know that, Sandesh. Thanks!
Thanks a ton Vishal.
Thanks AP!
first thanks to ayush,pratyush and you also.
Thanks Devendra!
Can this be used with US markets also? If so, how?
Thanks,
Pankaj
No Pankaj. This excel takes in data from Screener that provides it for Indian companies only. Regards.
Sir I am very very thankful to you and other people like you , who share there knowledge and hard work done over years. My kid is just 6 years old tell me age by which I can send him to your classes in Delhi.
Thanks Swapnil! He may join when he is 8. Regards.
Great work!
I tried on my screener account but I’m not getting the data for any of the companies, tried ITC, Maruti and Hero MotorCorp. Did someone from the tribe try this out?
Thanks!
Bhushan
Dear Vishal,;
Many many thanks for providing the update of the stock analysis spread sheet. What is also needed perhaps more important is domain knowledge. For example, one comes across a company that is making a loss. How does he gather the information on the industry and then the company to figure out if it is going to turn around soon without meeting the management?
That knowledge advantage could make the difference between a brilliant investor and an ordinary one. Please therefore write more articles on industries like one of your articles on valuation of cement companies. Also include risk factors to consider.
Thanks again!!
Thanks for your suggestions, Ranjan. Regards.
Thank you Vishal for this.
But I am seeing this issue while trying to download excel for a company after I successfully uploaded the excel.sheet provided by you.
I am getting 500 error while trying to download excel for a company.
Hope you were able to get over the issue, Mr. Deviprasad. Regards.
Hi Vishal,
First of all the earlier excel.I downloaded was throwing 500 error when I tied to get the excel for different company. I redownloaded the excel from the website rather directly from the email link.
It worked to get the excel for other companies.
I have a query on how can we map the DCF on a market to the stock price?
When the DCF is calculated per market capital of a company, can just blindly divide the DCF by the total number of shares to get the intrinsic value of the stock?
Or am I reading it wrong when the current excel sheet says hero motor corp has DCF at 52% as of the market cap, it suggests that the stock price can stay at 52% of the current price or the 52% would be the probable growth on the stock price?
Looking forward for your response.
Thanks in advance.
Wonder full Post!!
Thanks for sharing great information related to Stock Analysis.Really appreciated
Thanks Rakhi! Regards.
Vishal…. this is great work ! Like really good.
Pls keep it coming for individual investors.
Thanks Kushal!
Thanks Vishal! Is there a version that works with U.S. based companies. If not, there are APIs that provide that number and I happy to help / collaborate to create a such a spreadsheet (the valuation models part is where I may need some help)
Thanks Bhavin! That would be helpful. Let me know where you need my help etc. You may write to me at – vishal@safalniveshak.com. Regards.
This is awesome work Vishal! Thank you so much!!
Very good work team ^ Vishal.
You have taken pain of collecting data out (which usually is headache in analysis).
microsoft excel rules 🙂
Vishal,
I came to know about just few days back While watching video on you tube for Share market related news and decision, Found a interesting person discussion on ETNOW and that’s the genius Vishal, then I starts searching about you on net. Finally landed on your YouTube page and Blog safalniveshak.
I’m a retail investor in Stocks and Mutual funds from last 7 yrs, found your analysis, presentation and blogs very help. Thanks for sharing your knowledge with us.
I have used your excel sheet version 3 on screener, its amazing. Thanks & Best Wishes to you, your team and family.
HI Vishal, Thanks for the excel. However a fresher like me in stock market who has just stated learning is not able to understand actually how to use this and make inferences from these numbers. Can you please suggest how can I utilize this tool?
Thanks so much Vishal..!! Very helpful tool you have provided…!! Only because of you, many people like me came to know about value investing and started their investment journey.
Hello Sir,
I want to thank you for all the hard work you have done in sharing your knowledge with others.
I have learned a lot from you & wish to learn much more.
Hi Vishal,
Great work. Just we need to resolve one issue.
Whenever data is not available for particular year on Screener.in excel sheet will by default takes data of uploaded template sheet for that particular year.
e.g. Alembic Pharma data is not available on Screener for year 2008, hence if we export this sheet to Excel, it will take data from template sheet (i,e.Heromoto corp)
Regards,
Sharad Thorat
Dear Vishal
This is excellent. Thank you very much.
regards
ayaz
i am unable to get value of – DCF,EPV, DHANDHO , EXPECTED RETURN, GRAHAM.only there is default value of HERO MOTO , from where i will get above values of other stocks. anyone piease help
Hi Vishal..Appreciate your knowledge and demonstration on analyzing stock and using excel.
As the word Excel means Shining and Growing, its really important know excel for excelling.. Cheers!!!
Very helpful tool…Thanks Vishal!!
Hello sir, namaste. I downloaded your excel 3.0 , but after export values, it’s not updating, means I’m getting blank in the place of values, could you help me to work on it, thank you sir.
Thanks Vishal… great work.
I have one query. I was analyzing ONGC using this excel. On “Summary” sheet, its showing following P/E details…..
Average P/E (5-Years, x) : -42.5
Latest P/E (x) : 64.3
I have followed steps suggested by you. I haven’t changed any details in “Data Sheet”. I just updated values in black cells. Still it is showing wrong P/E ratios. I might be making some error. Please advise.
I have a feedback on Greenwald’s EPV method that you have used in the excel. He has given valuation for these three scenarios:
1. The business with no growth from here onwards
2. The business with Franchise and with some growth where return on capital is consistently higher than cost of capital. Hero Honda kind stocks fits in here to some extent.
You have covered the first one in your excel. I think it will be good idea to cover valuation for businesses with growth because in country like India most of the businesses will show some growth over the years.
Even then I would say, it is good idea to get some measure of lowermost valuation with no growth assumed at all. Some times during severe correction you find such opportunities. The growth is thrown is as free add on.
Another suggestion would be to get the expected returns (discount rate r) assumed by the market embedded in the current stock price. Some times the market’s expected return data is more useful for decisions than explicitly forecasting 5 years worth of sales and profits, which is not at all easy even for the company CEO.
Great Work!!!
wonderful work. Amazed to see the data interpretation. Thank a ton.
Vishal Bhai, exceptional work and great help. It fast tracks the basic number crunching which is extremely helpful and further helps in doing a quick filter to move onto ARs.
Nothing but love and best regards.
Hello Sir,
Thanks a ton for this wonderful tool. however, I had a query. Using your excel, how can i calculate someo fthe ratios like Quick ratio or current ratio because the data sheet does not classify the assets as current assets or non-current assets
I tried calculating the same for Prozone Intu. Pls see if you can help me out regarding this.
Regards
Keshav Sood
Hi Sir,
Thank you for the wonderful tool.
Is there room to add an important parameter “Promoter holdings” percentage for last 5/10 years in the excel sheet?
Thank you for your efforts.
Vishal, I just finished reading the Greenwald book on Value Investing and wanted to do the EPV Valuation. Before I created my own Excel version, I googled around to see if someone had already taken the pain to build a Excel version. And I stumbled into your Excel version that saved hours for me and is an Excellent tool for armchair Value Investors. Thanks a lot for letting us use this version.
Thankyou
thank you for this awesome sheet (Safal Niveshak Stock Analysis Excel Version 3.0). However, currently it seems to be broken – when uploaded to screener.in and then when exported the data to excel, the sheet comes blank – without any analysis. Just the file name changes to the name of the stock all empty inside. If there something I missed here?
respected vishal,
hope your USA trip was fantastic with lots of more knowledge you gained. i am silent follower of yours on twitter. recently during my reading i come across intrinsic value, and i know if someone know stock’s IV that gives more confidence to invest.
i would like to point one thing, how one can get IV of recent listed company whose data are not available since 2008. hope you will soon come out with this solution in far better way. i always look forward to you for learning from you.
thank you very much for making fantastic xls sheet.
may god bless you
Thank you for sharing your expertise in stock analysis. I found the website very helpful.
I would like to ask if there is an analysis sheet for banks and financial services.
Thank you
Sir thank you for this tool. I had one doubt. In “Cash Flow” chart, Capex is required to be manually entered. However, the chart already contains “cash flow from investing activity” and capes figure would already be included in that. So do we need to count Capex twice over?
Apologies I have understood. Kindly ignore the query. Thanks.