Free Value Investing CourseJoin Now

Analyze a Stock in 30 Minutes (Free Excel)

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:

  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. P&L Account: Enter the income statement numbers here (only the ones in red font).
  5. Cash Flow: Enter the cash flow numbers here, and also calculate the free cash flow (only the ones in red font).
  6. 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.
  7. DCF: This sheet calculates the discounted cash flow or DCF-based intrinsic value of the stock, after you input some key numbers.
  8. 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.
  9. 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.)

Now, download the excel here, and get onto the path of becoming an independent, do-it-yourself investor.

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.

Anyways, in case you were not able to see my “Manifesto of a Small Investor” I had shared in my last post, click here to download PDF.

Hail Small Investor! :-)

Print Friendly

Comments

  1. You have done real hard work Vishal. Really useful stuff that will ensure that key numbers are not missed out.

  2. Amazing work Vishal.
    This seems to be the ‘Bramhastra’ investors need to use for investing. :-)
    The detailed work shows the effort that must have been put in it.
    Great work. Really appreciate it.

    • Thanks Dev! :-)

      Well, I re-read on Brahmastra – “It never misses its mark and must be used with very specific intent against an enemy, whether an individual or army, as the target will face complete annihilation.”

      In investing, ironically, the target is no one but the one who’s shooting the arrow. :-)

      So, shooter beware!

  3. Jana Vembunarayanan says:

    Awesome work Vishal!

    Regards,
    Jana

  4. Amazing work, Vishal…:) Really liked it a lot…:)

  5. Akhilesh Pathak says:

    Great work Vishal,You are sowing seeds for better investor education and financial freedom. …True to what Thomas Moor said- “Education is not the piling on of learning, information, data, facts, skills, or abilities – that’s training or instruction – but is rather making visible what is hidden as a seed.” .

  6. This is awesome, Vishal! Don’t have enough words to thank you. Still, thank you so much for this and everything else! You are one amazing human being and teacher. Cheers!

  7. Reni George says:

    Dear Vishal
    Man man man……you have just cut the rock and put down the road.Thanks for the same on behalf of all the readers

    Thanks and Regards
    Happy Investing

  8. Hi,

    Very nice work and very helpful to share it with everyone.

    One major problem which i am facing and everyone might face.. is the change in the reporting system from FY 12-13 especially the balance sheet makes it a bit difficult to put the exact numbers in place historically.

    May be you can have a graph section in your excel which can be helpful in understanding the trend more easily then by going number by number.

    Once again thank you for the excel.

    Regards

    Yogesh N Bang.

    • Thanks Yogesh! I had through of putting in charts but many a times, a chart may give a wrong impression and we may just skip over the bigger picture that may come from reading through the raw numbers.

      Yeah, there might be some issues given the slight change in reporting standards, but let’s see if any other tribesman raises this issue….it will be an indication that he/she has read last few years’ annual reports. :-)

      Thanks & regards.

    • Sanjeev Bhatia says:

      Our esteemed tribesman Reni has painstakingly put out a detailed explanation on new and old formats of reporting (schedule VI) along with a sample excel sheet on SN group. You can go through that for getting a clearer picture.

      Hope that Helps :)

      • I was not able to find the Reni’s post on on new and old formats of reporting (schedule VI), where can I find it?

  9. Wow , Vishal , just wow!!!…..This is one awesome excel that anybody who is barely cognizant of reading financial statements can use. Whole hearted thanks on the behalf of the small Indian investor.You do put in a lot of hard work, and that too just to enlighten people you do not know at all.You are a true inspiration.

  10. Sudheendra says:

    Great work Vishal !! Thanks for this !!
    Just one question, is there any one site where we can get 10 years historical data. Most of the sites give 3-5 years only. And not all annual reports have historical data given. Any suggestions?

  11. This is exemplary work Vishal! Really appreciate your passion.

  12. Sudheendra says:

    Hi Vishal

    Did not quite understand how can Historical Earnings Growth, Sustainable Earnings Growth estimates be considered as fair value estimates. Dont they have to be discounted? Does it mean stocks are valued based on 10 year total future earnings?

  13. sanjeev sharma says:

    Dear vishal
    wow its going to be of great use . only thing which we will face problem to get these values for ten years as annual reports are not available online for these many years. Ur teachings to make us independent thinkers will definitly help us in attaining financial freedom.
    regards

  14. satheesh babu says:

    hi Vishal,
    Thanks for the awesome dessert after the great workshop!

  15. Ajay Rawat says:

    Thanks Vishal

    This is the best gift you could give to your readers.
    Appreciate the efforts and your willingness to freely share the knowledge and resources.

  16. Raghu Raman says:

    Wonderful. Thanks for sharing the excel sheet. I very much like the ‘checklist’ part.

    Raghu

  17. Varun Panaskar says:

    One of the best excel sheets! Thanks Vishal and keep it up.

  18. Thanks a lot vishal. Really appriciate your efforts and humbleness.

    Vikas Kukreja

  19. Shamil Abdul Kader says:

    Thanks Vishal. It is very useful. I really appreciate the effort you are putting in.

    I was waiting for DCF spreadsheet, you gave me a gold mine. This is the most precious gift you could give to your readers. I have been searching for something like this for a long time.

    Regards.
    Shamil

  20. Kaushik Das says:

    Dear Mr. Vishal,

    I don’t even words how to express THANK to you. This is the first time I got a real BONUS which will help me in time to come.

    Regards,

    Kaushik Das

  21. Ashwin Rananaware says:

    I just wanna say, what a wonderful job of empowering and educating the normal investor, you are doing. Excellent…!!

    Please accept my sincere appreciation of your work. Thanks. God bless you and your family.

  22. HI Vishal,

    Thanks for sharing with us this valuable tool.

    Can u let us know about your source of numbers of Asian paints u have entered on the excel sheet. The numbers of the balance sheet on the annual report differ; thats why want to know ur source.

  23. Dear Vishal,

    Thanks Vishal!!!
    Recently, I was in process to create this kind of valuation models on my own. This will really help me to finalize my valuation model.
    Thanks a lot!

    Regards,
    Mahadev

  24. Vishal… An Awsome Work.. An excellent investor empowerment tool…..grt going

  25. Nilesh Mundale says:

    Vishal….Simply amazing work… well done…

  26. Sanjeev Bhatia says:

    Wow, Wow and WOW.

    You keep on surprising us these days, Vishal. It really is a treasure trove of a template which will definitely reduce so may man hours of work for lesser mortals like us :P , as also reducing the chances of errors. Being one who uses excel extensively, I can imagine the time you must have had to put in, especially when there are so many cross-linkages. Hats Off to you and a very, very sincere Thank You for such a painstaking effort.

    No doubt the number crunching part comes at a later stage, much after a particular business passes your “Circle of Competence” test and “Understanding the Business” part. But the importance of the same can’t be ignored. The valuation part, the arrival at a MBP with suitable MoS , the nitty-gritty of a company’s financial muscle, the existence (or absence) of a moat, as well as the true contributor to a company’s revenue can be gauged only from the numbers. Your excel template will definitely go a long way in helping us put our time(always a scarce commodity) to much more constructive use.

    Regarding Data, yes, that’s a big issue. Even for most of the companies, not more than 3 or maximum 5 ARs are available. The problem is compounded by the fact that many a times there is data mismatch between various sites like moneycontrol, equitymaster, morningstar etc. I guess, the best source is AR only and we can make do with whatever is available to us.

    There are certain sections where I also felt some Graphs would have been better. But it is like as if you are getting one thing free without asking and now you want two ;) . Will try to add them myself.

    Thanks a lot once again for this stupendous work.

    • Sanjeev you can get 10 year financial data for free at craytheon.com

      • Sanjeev Bhatia says:

        Thanks a lot, Krish. I didnot know about that. Will definitely give it a try.

        • Sanjeev, I will still suggest to cross-check with the annual reports because the idea is to get the numbers directly from ARs as this also cultivates the habit of reading them.

          If you don’t find annual reports for all 10 years, please write to the Company Secretary and they will send the reports. Regards.

  27. Vishal, why are you including Other manufacturing expenses in Gross Profit? Shouldn’t it be : Net sales – Cost of goods sold

    The 2009 long term borrowings of asian paints given in the excel is 152 crores while in the annual report its 83.75 crores.

    How should we calculate short term and long term borrowings if the annual report does not have that breakup. Lot of pre 2011 years we just have secured and unsecured loans and many companies don’t give the break up.

    • Hi Krish, COGS will include all direct costs associated with the product, and thus I have included “Other Mfg. Cost”.

      Please don’t go by the data on Asian Paints. I have taken all data from Ace Equity and have not cross-checked with the annual reports. The analysis in this excel is for representation purpose only.

      As for calculating short and long term borrowings, after the change in reporting standards from FY11, you won’t be able to get the exact number but just look at the prior years’ schedules for “Secured loans” and “Unsecured loans” and they will have components for long and short term loans.

      Hope this helps. Regards.

  28. Sanjeev Aggarwal says:

    You are doing a noble job of educating and empowering the investors through knowledge sharing in taking informed decisions while making investments.
    One can imagine the amount of hardwork work and time you have devoted making the excel sheet.
    Excel sheet is excellent and accept my heartest thanks for making it available to all.
    thanks a lot again.
    regards

  29. Rajaji Selvaraju says:

    Dear Vishal,

    Simply superb!! Instead of giving us fish that serves for a day, but you taught us fishing through this XL that can serve us life time. You truely deserve an appreciation and praise from all of us for the detailed work connecting all the dots in a meaningful way where a lay man can understand.

    regards
    Rajaji

  30. Its a good back of the envelope way. I appreciate your effort in putting this together.
    Of course as you said it is a dipstick and should not be the only tool to decide.

  31. vishal sir, this has been really helpful in many ways. please clarify my doubt regarding calculation of debt burden ratio. y only long term debt taken into consideration. it should be both short term and long term.?as per the video , ” how to read balance sheet”,total debt is taken (long term borrowing n short term)for calculation.
    thanks a lot ..

  32. Amit Kinhikar says:

    Thanks Vishal !
    I was planning to ask you something like this.


    Kind regards,
    Amit

  33. satheeshkumar says:

    Hello Vishal,
    I am very new into investing and i have found all your posts very usefull. gearing towards the wonderfull world of investing. will stay in touch.
    the excel is exahustive , frank as novice i dont understand many but it is intresting..so manythings to know across..
    also where do we get last 10 years financial data of companies ?
    reg
    satheesh

  34. R.K.Chandrashekar says:

    Dear Vishal
    I have no words to express my gratitude for what you have done- with one master stroke you have provided a tool for investors to analyse a company the easy way. This would go a long away to motivate investors who are either scared or plain lazy to do the number crunching. You continue to delight and surprise us !

  35. Dear Vishal
    Thanks a ton for this tool….this was really brilliant ….this is really helpful for beginers like me …. after using this for a particular stock , i have the following questions… could you pls guide me on this ….
    1. The historical earning growth (buffet valuation) number is much much higher than the price valuation from other methods…. does not is scew the fair value calcuation ?
    2. If a company was listed within the last 5 – 6 years, how do we get the financial data for the 10 years period as there wont be any annual reports prior to listing ?

  36. Hi Vishal,

    Many thanks for the excellent spreadsheet. Life is much more simpler now.

    A suggestion – Would you consider adding another tab on your spreadsheet, which would be called value traps, and could highlight the traps one could fall into despite following the principles.

    Thanks.

  37. Vihang Gangan says:

    I really think this is a good tool. The beauty of this tool is that it provides objectivity to your gut feeling. For instance, I was convinced of the idea of Abbott pharma as it was a leader in gastro market. However, after putting the number s in the front using this sheet and subsequently reading the annual reports wherever there was a sudden change in the numbers, I realized the time of buying may not be right.
    This tool will help you buy the stock at right time.
    However, I am facing a problem.
    1. What to do in case of stock splits or bonus issues -How to factor these changes in the highest and lowest stock price that we write in Fair Value calculation sheet, and subsequently the PE of the stock

    • You have to manually adjust the stock price and earnings per share according to stock split and bonus.

      Check out craytheon.com if you dont want to do it manually.

  38. Thanks a lot for this excel sheet. You made work easier involved in conducting self-analysis of companies by your tribesman.

  39. Hi Vishal,
    Thanks for sharing the excel file. I am not sure if I have the right to make such blanker statement but still all the analysis around Indian stock market is “GOOD FOR NOTHING”.
    Following are the reasons:
    1. Govt companies are run with vested interest of involved politicians thus never give money to investors. Infact govt ensures that the company is first fall sick and then it is sold to private people at throw away prices.
    2. Fundamentals of the company or industry does not matter reason Corporate Governance (Management: whats that ???) Look at any of the big groups: Reliance, Adani, Thapars, Modi’s or new age infra giants (All from Andhra and now all are MLA’s or MP’s)
    3. SEBI is tooth less tiger. What they did about Satyam, Reliance Power, Kwality dairy (end less list of big shots forget about fly by night operators)??
    Anything about Indian stock market is speculation, value investing can simply be defined as how good you know an operator and how reliable is your operator.
    Keeping money in bank deposit will not beat the inflation but will grow. In stock market you will either loose all the money or earn nothing in net net terms obviously unless you have great luck or you know the good operators.
    You might agree
    Regards,
    Atul

  40. Prashant Pednekar says:

    hi Vishal,

    First and foremost I would like to thank you for the beautiful site and effort you have taken to develop it.

    I have been doing some technical analysis but after seeing your excel I feel I should move to fundamental analysis and use the fair value as a reference. But before this I want to know something from you honestly. No matter how the fundamentals work there is lot of manipulation in the Indian market. If you see it might take years before traders turn to investors and investors turn traders. What is the way to battle this?

  41. May I ask if the excel formulas are applicable any other stocks listed elsewhere in the world say Austrslis or Singapore? Hope to hear for you soon

  42. question– does this sheet factor in debt?

  43. wonderful excel brother, really loved your work and I hope to receive newsletters containing some fine techniques of value investing ….

  44. The spreadsheet needs some work. Personally, I do not like the color red & there is way to much information. You should keep it simple & not allow the brain to get so cluttered. In the real world, you can not forecast cash flow 10 years in advance. If you could, Benjamin Graham would have found a way. Of course the cash statement wasn’t in circulation many decades later & I think the problem with failures in investing is using the cash statement. Benjamin Graham didn’t analysis it so why should I ? The less information to the brain, the better the results of your analysis. It appears fundamental analysis has learned from technical analysis to clutter up the screen. The more information you have, the more roads you have to decide to take. Benjamin Graham formula used earnings & today investors take it where it doesn’t need to go. What is the formula for ? to find the margin of safety & not to go wow! a chest of gold in 10 years. Determine your safety, that is the key. Trends rather technical or fundamental have what purpose ? to compare one apple to another apple. If you flip a coin, you have 2 choices to make.. how much information do you really need to know to make that decision ? Quality is the key, if you can glance at something for 30 seconds then turn the page & glance at something else, your brain has all the information it needs to make a logical decision.. no need to dwell on it. You either have quality or you do not. If you picked 20 fundamentals to analysis, your results would be no different then it you only used 5 fundamentals.. either way, you find quality in what you choose to work with. The more variables you use to arrive at that decision, the less quality & foundation you have. If you simply took 2 of your rules from page 2, you could come to a decision of quality & it would be more solid without hesitation & without question while if you use all the rules, you leave room for failure. The choice is yours, you either find success using 2 variables or you find it using 20 variables.. the result would be the same, your ability to use 2 variables over 20 comes from skill. If you combine Richard Whyckoff & follow the market campaign on those fundamental stocks of quality, you would know which ones have the best leadership for the future 1 year down the road. Once again, you can not precise forecast the cash of a company 10 years down the road. We can try but there would be changes taking place in those 10 years & no formula is going to be able to foretell the future. Benjamin Graham Formula is really all that is needed, it’s simple & in the minds of many, simple doesn’t work in a fast pace technology world while simple will always put you on the right track & keep you there. Good luck!!!

  45. Hi Vishal,

    Pardon my potential ignorance in the following questions, but, in terms of the layout of the spreadsheet (which is wonderful, by the way), I am finding some names / labels to be unfamiliar (I.E. – L-9 through L-1). In reading through the many comments on your website I certainly understand that this is created for the Indian market.

    My primary question is in terms of converting this into a spreadsheet that would be eligible for use in the US stock market, etc. What in particular would you change to enable that?

    Thank you for the insight and for the spreadsheet itself!

    -D

  46. Vishal,

    Really a good piece of work to rest investing minds. Well done.

  47. Hi Vishal,

    I truly admire your passion for investing and helping others understand the basics of saving and investing for a better life. This spreadsheet is a very good template for evaluating Indian Companies. It would be very helpful to non-residential Indians if you could come up with a similar tempate for US based companies.

    Best Regards,
    Balaji

  48. Michael O says:

    Hi Vishal,

    I found your site while searching for value valuation. I must say you have done a great job. I like the two year course outline with all of the reading material. I have been reading some of the same books. I really liked Philip Fisher’s “Common Stocks and Uncommon Profits” and Peter Lynch’s “One Up on Wall Street”. You might enjoy reading John Train’s “Money Masters of Our Time”, copyright in 2000, ISBN 0-887-30970-4. It includes chapters on many of the professional investors like T. Roe Price, Warren Buffet, Philip Fisher, Ben Graham, John Neff, Jim Rogers, George Soros, Peter Lynch, and others plus lesson learned. As you may already know, Buffet was taught by Graham, then later became aware of Fisher and some of his methods as I recall Buffet saying in a book I read. So my impression was that he was using methods from both.

    I just started looking over you spread sheet, which is very well done, and would like to ask you a few questions, if I may do so. First, on the first page, are Rs the currency in your country? Second, what is Fave Value (Rs)? (I can see it is not the current stock price) Third, what is Number of Shares Core? (Is it the Number of Outstanding Shares of Common Stock?) Four, What is Promoter Shareholder (Latest Quarter)? Five, Promoter Pledged Shareholding (Latest Quarter)? I get the impression that the financial reporting is some what different in your country. Six, what does the CAGR percent numbers on the P&L tell me? In addition, I find the comments in the cells very helpful in understanding the various spread sheets. I’ve already signed up for your twenty week emails.

    Thanks in advance for your response. And again great website!

    Best regards,
    Michael O.

    • Thanks Michael! I have read “Money Masters…”.

      As for your questions on the excel sheet…

      On the first page, are Rs the currency in your country? Yes. That’s Indian Rupee.

      Second, what is Fave Value (Rs)? It’s not the stock price but the “par value” at which the stock is issued by a company.

      Third, what is Number of Shares Core? That’s the no. of outstanding shares (Crore = Ten Million)

      Four, What is Promoter Shareholder (Latest Quarter)? This is the latest amount of stake held by the company’s promoters.

      Five, Promoter Pledged Shareholding (Latest Quarter)? This is the amount of shares the promoters may have pledged to borrow money.

      Six, what does the CAGR percent numbers on the P&L tell me? CAGR = compounded annual growth rate, or the average annual rate of growth at which the company has grown in the past.

      Hope this helps.

  49. Anish Chandy says:

    Hi Vishal,

    Great sheet, makes my job simpler. Where do I find minority interest in the balance sheet?

  50. Hi there, for this excel sheet, how about for a company which only 3 to 5 years of financial data is available? should i just leave the rest of the red words blank?

  51. Its really helpful…Thanks :)

  52. Aditya Malik says:

    Hi Vishal,

    I have downloaded the file but it seems to only have sheet 1 and no other sheets.
    Please tell me how to correct this problem

    Thanks-
    Aditya

  53. Great work..
    Vishal. keep it up.. I myself was thinking of making an excel sheet but this one is excellent

  54. Hi Vishal,

    The excel model is very good. I had few queries on it-
    1. The balance sheet tab does not tally i.e. total assets and total liabilities do not match. Whether to take only relevant nos or the entire assets and liabilities has to match?
    2. It has only annual figures. How to incorporate quarter financials into this spreadsheet?
    3. in case of DCF tab- the initial cash flow is taken avg cash flow of past 3 years. Any specific reason for not taking only latest years Cash flow. As the net debt is considered for the lastet year, is it not prudent to take intial cash flow of latest year?

    I hope to get the reply from your side

  55. I have been looking for a ready made template.
    I googled it for days to reach at this page.
    I really thank you so so so so much…!!!!

    You have done a great work.
    Thing have never been made easy by anyone except SAFALNIVESHAK.
    Jai Ho !!!

    Thanks a ton…..!!!

Trackbacks

  1. […] those you understand (simple businesses), add them to your circle of competence, and then use a basic excel model to do a deeper […]

  2. […] with their clients, but also investors and businesses. Excel is dangerous! Ever since I shared my investment analysis Excel sheet, I have received a lot of emails from readers who have found “amazing businesses” that were […]

  3. […] not explaining how I arrive at these different valuations, as the calculations are all there in the excel sheet I shared earlier…just that I have done some slight modifications here and there in my […]

  4. […] you can establish this range – which this excel spreadsheet can help you do – you can then determine if the stock is undervalued, overvalued, or fairly […]

Speak Your Mind

*