Hi Readers,

I came across this free resource from http://www.safalniveshak.com and thought that is will be useful for investors interested to calculate and analyse stocks.

The original post is from http://www.safalniveshak.com and it entitled’ “Analyze a Stock in 30 Minutes (Free Excel)” Here is the excel Stock-Analysis-Excel And here is the guide (original post for using the excel), enjoy!

——————————————————————————————————————————————————–

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 an excel file that you can download on to your PC, and analyze not just the past performance of a company but also arrive at its intrinsic value in 30 minutes or less (only after you already have the annual reports with you). Now when I say you can do all this in 30 minutes, I do not mean you can decide on the stock in so less a time. It’s just that, in 30 minutes, you can do the dirty work of number-crunching that can aid your overall decision-making process. Now that’s what I call a “back-of-the-envelope” approach to stock analysis. If you have been into financial modelling in the past, this excel file may seem like a child’s play.

But, if my ten years of experience as an analyst is anything to go by, this is 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 here, and the complex ones that most analysts use in their doomed pursuits of finding the elusive target prices. Anyways, the excel I am sharing with you will show you that stock analysis isn’t rocket science. Of course you must do some hard work.

But more importantly, you must behave well instead of putting your entire faith on numbers spewed out by this (or any) excel.

Anyways, this excel file has nine 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).

P&L Account: Enter the income statement numbers here (only the ones in red font).

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.

DCF: This sheet calculates the discounted cash flow or DCF-based intrinsic value of the stock, after you input some key numbers.

Buffett Valuation: This sheet calculates a stock’s value based on the company’s past earnings growth numbers and also its sustainable earnings growth numbers.Beware of this sheet as this can throw some funny numbers at you, but I have found it useful at times.

Fair Value: This is the final sheet, and shows you the fair value range of the stock based on all other sheets that you have been through till now. In all the sheets, you just need to fill out the values shown in red font, while leaving every other calculation as it is.

(This excel won’t work for banking and financial services companies.)

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)!

6. Pray! Pray! Pray!

Junk all broker/analyst reports you have been reading all these years…for now the power to analyze stocks on your own lies with you. Let me know if you found this excel helpful. I will try to improve upon this in the future based on your feedback. ——————————————————————————————————————————————————–