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 map with territory and lose your life’s savings walking that path!
In this pursuit of handing you another compass, here is Version 2.0 of my Stock Analysis Excel Sheet that you can download on to your computer, and analyze not just the past performance of a company but also arrive at its approximate intrinsic value.
If you have been into financial modelling in the past, this excel file may seem like a child’s play. But, if my fourteen years of experience as an analyst is anything to go by, this is most of all you require to “quantitatively” analyse 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.
Anyways, this excel file has the following sheets:
- First Page: This sheet contains some basic data of the company and a lot of warnings and disclaimers.
- Checklist: Most important sheet! It contains a simple Buffett checklist that will help you a lot in your overall decision-making.
- Balance Sheet: Enter the balance sheet numbers here (only the ones in red font).
- Common Size Balance Sheet (New): All numbers in this sheet are automatically generated using values you entered in the ‘Balance Sheet’ sheet. This is one insightful way to analyze the Balance Sheet, as it helps investigate the different items as a percentage of a common figure. For assets, using Total Assets as the denominator and every other category as a numerator item helps look at it in percentage terms. Once these figures are calculated, you can compare them over time for a single company.
- P&L Account: Enter the income statement numbers here (only the ones in red font).
- Common Size P&L Account (New): All numbers in this sheet are automatically generated using values you entered in the ‘P&L Account’ sheet.
- Cash Flow: Enter the cash flow numbers here, and also calculate the free cash flow (only the ones in red font).
- Ratios: All numbers in this sheet are automatically generated using values you entered in the previous three sheets. Don’t touch a formula in this sheet, until you want to modify as per your own understanding. I have explained most ratios here. You just need to hover the mouse on a term to know its definition.
- Valuations: This sheet calculates the stock’s valuations using two methods – discounted cash flow or DCF, and an Expected Return model.
- Quarterly Analysis (New): Use this sheet to analyze the most important numbers from the company’s quarterly reports.
In all the sheets, you just need to fill out the values shown in red font, while leaving every other calculation as it is.
Apart from these sheets, the new version also includes small trend charts alongside the most important numbers to tell you quickly the trend in those numbers over years.
(This excel won’t work for banking and financial services companies.)
Click here to download the excel sheet.
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.
Aditya Harite says
I used your earlier excel (have not checked the new one). I got the numbers from moneycontrol.com to fill the redd cells. In your example of Hero Motors you had used data from Ace Equity. Needed your help in getting the best way to get the numbers. I tried filling the details from ARs. However it was tedious. While as you mentioned the excel is just a tool and no substitute for thinking, I needed some help in filling the details.
Please help on this.
Venkatesh Jayaraman says
Thanks for the excel. Not able to make out, what changes this excel is with the earlier version. I did use your earlier version on a few occasions. I have a doubt regarding efficiency ratio…
a)Does the three values (1) Receivable Days, (2) Inventory Days (3) Payable Days help in getting the Cash Conversion Cycle?
b) Is DSI (Days Sales of Inventory) different from DIO (Days Inventory Outstanding)?
Thank you for all the effort. Have used the last excel on numerous occasions & I used the common size method for better understanding for myself so this version is super helpful.
Apart from this sheet, I also add another which does an industry overlook with a Porter’s 5 forces & a sheet on competitors key metrics (primarily P/E, net sales, EBITDA, PAT).
Have the same issue as mentioned above by Aditya regarding inputing of the numbers.
Wanted to ask you a very silly question – do you write to companies and get the hard copy of their last 10 yr AR’s or do you only refer to the pdf’s? I find myself trying to print out all the numbers sheets to help with inputting the data but hate wasting paper.
It would be very helpful if you share something similar for analyzing US companies
Good stuff. How can we customize for the US companies?