The Utility of Stock Analysis Worksheet: How to Interpret its Intrinsic Value and Overall Score?

Some people might question the utility of my Stock Analysis Worksheet. Why? Because it mostly rates its stocks as overvalued or of a low overall score. Some users find it frustrating and send emails to me about the plight.

I can understand this feeling. We want the worksheet to throw a list of names of high-scoring, undervalued stocks. People desire this because it will take-off the burden of stock research.

But this is not how a true stock researcher thinks about investing. They know that finding quality, undervalued stock among the plethora of other stocks is like searching for a needle in a haystack. The activity requires both patience and perseverance.

People who desire a list of stocks assume that there will always be some stocks available for investing. But this is not the case. Quality stocks do not often trade at undervalued price levels.

Moreover, the majority of stocks trading in the stock market are of low quality. Hence such stocks are not suitable for investing. That is an essential understanding. Even famous Stocks, camouflaged as ‘Good Stocks,’ emerge as of low quality upon analysis. 

So, the idea behind developing my worksheet was to build a tool that can uncover the hidden secrets of a company (good or bad). How the worksheet does it? By reading and analyzing the last 10 years’ financial reports of the company.

The worksheet is not a screener.

Screener vs stock analysis worksheet

The user should not confuse the ‘Stock Analysis Worksheet’ with a stock screener. It is a tool that digs deep into the financial reports of a company. The purpose is to estimate its intrinsic value. 

A stock screener will screen a list of stocks based on a theme (like profitabilitylow P/Ehigh returns, among others). But my Stock Analysis Worksheet” applies various themes to judge a business behind the stock. More importantly, what it does is the estimation of intrinsic value.

The point is, a screener can handle a lot of stocks at a time as their focus area is not so large. But a tool like ‘Stock Analysis Worksheet’ does deeper calculations, hence prefers to handle one company at a time.

How A Pro-Investor Estimates Intrinsic Value?

How pro investor estimates intrinsic value of a company

How an expert investor like Warren Buffett estimates the intrinsic value of a business? They do it by judging the company’s future potential. The judgment is based on three things:

  1. Past History: Analysis of the company will start with a study of the company’s financial reports. Reports like balance sheet, profit & loss accounts, and cash flow statements are studied. It gives an impression of the company’s past and present.
  2. Discussion With Company: Pro-investors discuss with the company’s top management. It is done to get an idea about the company’s plans and prospects for future earnings growth
  3. Future Estimates: Based on the past studies and discussions with the management, pro-investors takes a decision. They try to arrive at a decision regarding the capability of the company to increase its free cash flow in times to come.

The above three steps build the basis for intrinsic value estimation. In step #1, the investor gets an idea about the asset base, profitability, and cash flows. Step #2 establishes the reliability of the company’s management. It also provides a peep inside the management’s plans to render future earnings growth.

Step #1 and step #2 provides information for intrinsic value estimation. If the estimated intrinsic value is higher than the current price, the stock is undervalued.

How The Worksheet Estimates Intrinsic Value?

How worksheet estimates intrinsic value of a company

The above infographics highlight the difference between stock analysis done by a pro and my stock analysis worksheet. The worksheet has the ability to read and comprehend the financial data of the company. Read more about the concept of analyzing stocks in excel.

But what it obviously cannot do is to judge future growth aspects of a business. The best it can do is to estimate future growth based on the past trends.

As an investor, we must remember that growth rate numbers make a difference in the intrinsic value estimation. Looking only at the past trends may not give a real feel of the future.

But why I’m highlighting this weakness of my worksheet? Because it will help its users to make better use of the numbers highlighted by the tool. How? Allow me to explain using an example.

Example: Utility of Stock Analysis Worksheet

Example Company - 1

The above is a screenshot of a portion of the report generated by my stock analysis worksheet. What is visible in the report are three main things:

  • Current Price: The current price of the example stock shows as Rs.264 per share. Why we need the current price? Because this is the price at which the stocks of the company are available for purchase.
  • Intrinsic Value: The estimated Intrinsic Value of the company comes out as Rs.142. It is the indicative price at which one can purchase the shares. But as Rs.142 is lower than the current price (Rs.264), shares look overvalued.
  • Overall Score: A company earns an overall score on five parameters (price, growth, management, profitability, and financial health). For me, an undervalued stock that has an overall score of 75%+ is good. Our example company’s score is just 55.2%.

So, this is a typical situation. A user will find many stocks whose price valuation and overall score will be similar. How to comprehend this information?

How to Comprehend a Low Overall Score?

Before comprehending, we must recall how the worksheet is estimating the intrinsic value.

Fundamentals plus growth equals to intrinsic value

Here the essential point of consideration is the “growth trend’. The worksheet will try to judge the future growth prospects of the company based on their past performances. A company that has done poorly in the past will render a low growth score. It eventually reflects in the ‘low overall score’ and a ‘lower intrinsic value’.

Example Company - 2

But as an investor, we must remember that a company which did badly in the past may not continue to do so in the future.

For example, pharma stocks did not perform well for almost four years together. Why? Because the whole Parma sector was underperforming in this period. Check below for the Nifty Pharma Index.

Nifty Pharma Index

Hence, my worksheet will render lower ratings to even Pharma majors like Dr. Reddy’s and Sun Pharma. But considering that this sector is reviving from a slump, an informed investor can bet on Pharma stocks.

Here, the utility of the ‘stock analysis worksheet’ will be to highlight how good or bad the company has performed in the past. Users can analyze other stocks in the same sector. If the score of all stocks is coming as low, then it can mean two things.

  • First, either the whole sector is seeing a decline.
  • Second, all major stocks of the sector are trading at expensive price levels.

In such cases, the user should either stay away from the Sector or pick a mid-cap or low-cap stock. They might render better returns in times to come. 

How I Apprehend The Overall Score?

I check the bigger Overall Score and also its constituents. The spider chart of my worksheet highlights the said numbers. Let’s take the example of a couple of companies that have fared low in the overall score:

Company #1
Overall Score - Airtel

This is a company that has fared poorly in all five parameters. It is not only overvalued and shows low growth prospects but is also low in profitability, financial health, and management’s score. This company is Bharti Airtel.

Due to the Retrospective Amendment of the Income Tax Law passed by the Government in 2012, the whole telecom sector has suffered. Companies like Airtel, Vodafone Idea, Tata Tele has faced troubles. It also reflects in their overall score.

So from the numbers of the telecom companies, it is clear that they are in trouble. We can ask, they can survive the crisis? In the next 5 to 7 years, how fast they can grow? How to judge this? By reading news about the company. A lot can also be understood by reading the latest annual report of these companies.

If I have a very long term holding capacity (like 7 to 10 years), I might like to buy stocks like Airtel. But for shorter horizons, I would rather go with other non-troubled companies.

Company #2

This is another example of a company that scores high on profitability (4.75), management (4.5), and financial health (3.68) score. But the stock is extremely overvalued (1.63). Even the future growth numbers (based on past records) is low (2.4). How we can interpret this stock?

The five parameters shown in the spider diagram are five pillars based on which a company’s overall investability is decided. Even though the business looks very profitable, but its valuation is expensive. Hence, its overall score might be as low as 60%.

For such stocks, I’ll further investigate future growth prospects. If the future growth looks good, a buy-call can is possible. I might decide to buy upon price corrections

Conclusion

What the stock analysis worksheet reports are a reflection of the past. It cannot fully gauge the future growth of the business. It is especially true for companies trading in troubled sectors like Telecom, Real Estate, etc.

So what users can do is to draw the report of a company using the worksheet. It is step number one. In the next step, they should read the news about the company. It will give an idea about their future growth plans.

I generally use Google Alerts to receive the news related to a company right into my Inbox. Over a period of time, with daily news updates, I start to get a more realistic feel about the company. If I’ve to buy stocks of a company, I prefer to do it after reading news about it for at least a month.

It helps me to build a “story” about the company. The story of the company assists me in deciding if a company is worth investing in or not. Coupled with the report generated by my stock analysis worksheet, I give myself the go-ahead.

Automatically Analyze 820+ Stocks in Excel
You are interested to know if your stock is worth investing? You can use my MS EXCEL based tool to analyze it like a pro. You need not do any data entry. Just pick stock from a list and click a buttons. The report is generated automatically. Try my stock analysis worksheet >> Read testimonials.



nv-author-image

Hi. I’m Mani, I’m an Engineering graduate who in pursuit of financial independence, has converted into a full time blogger. After working in the corporate world for almost 16+ years, I bid it adieu....read more

18 thoughts on “The Utility of Stock Analysis Worksheet: How to Interpret its Intrinsic Value and Overall Score?”

  1. I read a lot of blog posts and i never heard of a topic like this. I Love this topic you made, really amazing.

  2. TERRENCE DSOUZA

    Hi Mani,

    Updated post of my previous comment.

    I am using the latest version of the stock analysis worksheet 3.1 , first, most of the comment boxes in the ratio sheet under price valuation ratios is not visible in certain cells, second I need to know if the intrinsic value calculated on the cover sheet includes the margin of safety i.e 2/3 value. or do we have to calculate it manually.

  3. Hi, I just got the sheet. Unable to fill up 5years current and previous data, moneycontrol is giving 4 years data only. Can you help.

    Thank you.

      1. Namaste maniji,
        I suggest to note this all those uniques question in form of FAQs. Hope this will be good references for this product.

  4. Hi Mani
    in the general sheet Market prize is not editable. how to edit the market prize of stock? in front of Market prize column is it showing green arrow. Does it means that excel sheet will calculate automatically?

    please provide your inputs

  5. When u update to a new version,does the user get it by default or he has to purchase a new one.
    Do u have a sheet for analysis of banks and nbfc

  6. Dear Mani,

    i have latest worksheet V2.1.2_10, somehow P/E ratio is with green mark, which means it can nnot be entered by us. and after entering data, it is still showing #DIV/0

  7. dear mani i subscribe your excel sheet utility but now days this are not showing many data due to moneycontrol increase the coloum due to data showing mismatch pls correct this for old version pls

  8. Hi
    I have put in the relevant data but, values through NCAPS and EPV is coming as zero. Definitely not missed a step. Could you advice what to amend?

  9. Dear sir,
    General : Effective Tax rate cell value is locked at 30%, unable to edit due to password protection. Where as video guide suggests to take from equitymaster. Should we enter actual value or retain at 30% ? Kindly guide as i’m unable to edit the that cell.

    1. In the updated version, there is no need to manually enter effective tax rate. Worksheet calculates it by itself.

Leave a Reply

Your email address will not be published. Required fields are marked *