Analyze a Stock in 60 Minutes (Free Stock Analysis Excel Version 2.0)

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:

1. First Page: This sheet contains some basic data of the company and a lot of warnings and disclaimers.
2. Checklist: Most important sheet! It contains a simple Buffett checklist that will help you a lot in your overall decision-making.
3. Balance Sheet: Enter the balance sheet numbers here (only the ones in red font).
4. 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.
5. P&L Account: Enter the income statement numbers here (only the ones in red font).
6. Common Size P&L Account (New): All numbers in this sheet are automatically generated using values you entered in the ‘P&L Account’ sheet.
7. Cash Flow: Enter the cash flow numbers here, and also calculate the free cash flow (only the ones in red font).
8. 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.
9. Valuations: This sheet calculates the stock’s valuations using two methods – discounted cash flow or DCF, and an Expected Return model.
10. 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…

1. It’s just a compass, and not a map. So take your next step carefully.
2. Don’t look for perfection. It is overrated.
3. Focus on decisions, not outcomes.
4. Look for disconfirming evidence. Avoid falling in love with the numbers.
5. 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.