Utility of Stock Analysis Worksheet V2.0

What we intend to do when we analyse stocks?

The answer is simple, we want to know, if the stock is good for investing or not.

But how to arrive at this conclusion that a stock is good or bad for investing?

Different people apply different stock evaluating techniques.

  • Some buy stocks on advice.
  • There are people who use technical analysis.
  • Some invest passively in stocks, through index ETF’s etc, and
  • There are people who practice fundamental analysis.

Selection of techniques depends on ones personality, and also on the depth of knowledge. Here knowledge is not academic, but it’s more about the aptitude to ‘read into the businesses‘.

How one can read into business? By reading companies annual reports of past years.

People who like to read and comprehend businesses are called fundamental analysts.

People who like to buy stocks using fundamental analysis are half business men. They may not be doing business in literal sense, but still they are business men.

The reason being, they have learned to think like business men. How?

Their skill to interpret companies financial reports makes them similar to their business men counterparts.

Here it may look like I am giving too much importance to business men. But do not take me wrong.

What I am trying to communicate is that, when it comes to money-making, business men know more.

Hence when intention is to make money from stocks of companies, it is essential to think like business men who run those companies.

Thinking like them gives one that amazing ability to interpret the true value of a business.

The value of a business is built by many things as stated below:

  1. Efficiency of Management
  2. Profitability of Business
  3. Financial Health
  4. Future Growth Prospects
  5. Rich Cash Flow
  6. Current Price Valuation, and
  7. Hype

Serial number 1 to 5 build the companies intrinsic value (true value).

Serial number 6 gives a market value to the company. The market value of a company can be simply depicted as shown below:

Market Value and Intrinsic Value RelationshipWhen market value of company is less than its intrinsic value, its stock is undervalued. When market value of company is more than its intrinsic value, its stock is overvalued.

Now the question remains, why a stock becomes undervalued or overvalued? The most likely reason is serial number 6, because of hype.

Market often overreacts to good and bad news. When news is good stock price can jump too high, making it overvalued. When news is bad stock price can fall too low, making it undervalued.

This is what hypes can do to stock prices. And this overreaction happens to both large and small companies.

The task of fundamental analysts is to learn to interpret intrinsic value (true value) of a company using parameters of serial number 1 to 5.

This is 99% of the total work required to be done in fundamental analysis.

Balance 1% is comparing the intrinsic value with its market price, and identifying the reason for the hype.

The stock analysis worksheet provided here deals with 99% of the task. It also helps to compare the current price with the estimated intrinsic value.

Fundamental Analysis and utility of Stock Analysis Worksheet

Why use the stock analysis worksheet?

Instead one can buy books like ‘Intelligent Investor’ and ‘Security Analysis’ written by Benjamin Graham and learn fundamental analysis.

Surely, this worksheet is not even 0.01% of what these books can teach one about fundamental analysis.

Then why to invest in buying this stock analysis worksheet? It serves a very important purpose.

These books are like those theory classes that we took in our school/colleges. There are of paramount importance.

But if those theory classes were enough, why students were also made to attend the practicals in laboratories?

This is because practice makes perfect.

With this idea in mind, I developed an excel sheet that could help in making fundamental analysis more conclusive.

When we read books, we study. But when we use a worksheet like this, we understand.

#1. Financial Reports

When I was learning about stock analysis, I could understand that the only way out was to first learn to read financial reports of companies.

The financial report of a company is comprised of three sub-reports:

  1. Balance Sheet.
  2. Profit and Loss Accounts.
  3. Cash Flow Statements.

Being a person from non-financial background, I hardly knew what these reports are.

After gathering some courage, when I first read a companies balance sheet, I went into depression. 🙂

I felt that it will take me another 100 years to learn how to read and interpret its meaning.

As if this was not enough, I was told that reading only one year reports is not enough. To make a more useful interpretation of the numbers, I must read at least five years financial reports.

There are close to five thousand companies listed in Bombay Stock Exchange (BSE).

So could you understand how many annual reports one has to read to identify good stock?

3 x 5 x 5000 = 75,000

I had to do this exercise twice every year, it means in a year one has to read 150,000 financial reports each year.

This was impossible.

So now you could understand why I felt like a depressed person? 🙂

But very soon one realisation came to my rescue. I could understand that I need not start analyzing all 5000 stocks listed in stock exchange.

What I must undertake to analyze are only those companies which I feel is good for investing, and not every stock that the market has to offer.

This reduced the workload by almost to zero. I was then analyzing one company per day.

But this was also a big effort. Reading five years financial reports of a new company each day, was too much home work for a primary class student.

In those days, I first started the process to make my task easier and faster. I started building the stock analysis worksheet.

Since that day, the worksheet has seen about hundreds of iterations. Today it has come to a stage where a stock analysis should not take more than 15 minutes.

Just copy and paste the five years data into stock analysis worksheet, and it gives the result.

I am not sure how many such tools are available which can claim to be so conclusive.

Lets see few more details about my stock analysis worksheet.

#2. Stock analysis worksheet & “Important Things”

This is a simple Microsoft Excel Spreadsheet. This worksheet has been designed to analyze stocks.

What we mean by analyzing stocks?

Before buying any stock, the person must know “important things” about that stock.

This worksheet helps its users to know the important things about the stock in consideration.

What I mean by important things? Those information’s that highlights whether a stock is good or not.

But how this worksheet can tell important things about stocks?

This is a very valid question. If this is a simple excel spreadsheet, how it can analyze stocks?

Yes, I accept that. But some manual data-entry, and few intelligent formulas can transform a simple excel spreadsheet into a stock genius. 🙂

Lets dig slightly deeper into the so called “important things”:

#2.1 Important Thing: Intrinsic value

This is the core of stock analysis worksheet. It tries to estimate the intrinsic value of stocks.

Calculation of intrinsic value of stocks is a subject on its own. I am not sure how many people on this mother earth knows how to accurately estimate the intrinsic value of stocks.

The first such names that comes to mind are the Late Benjamin Graham, Warren Buffett, Peter Lynch etc.

I am sure, this list will not be too long.

The reason being; accurately estimating intrinsic value of a stock is a skill which is known only to the gifted few.

Hence, I will not boast that my stock analysis worksheet is perfect.

But what I can claim is that, my stock analysis worksheet can estimate intrinsic value by being very conservative.

Moreover, the intrinsic value indicated in this worksheet is only for a general reference. It is not a stock advice tool.

But personally speaking, with my own experience, this stock analysis worksheet works reasonably well.

Even though it is not very accurate in estimating intrinsic value, it is still effective.

Utility of Stock Analysis Worksheet - Price

It tries to estimate the intrinsic value of stocks using the following 5 methods:

  1. Net Current Asset Value Per Share (NCAPS) method.
  2. Benjamin Graham’s Formula.
  3. Earning Power Value (EPV) method.
  4. Absolute PE (APE) method.
  5. Discounted cash flow (DCF) method.
  6. Reproduction Cost Method.

All these stock valuation methods are unique in their own way. But what is common among them is that, they all use the information’s from companies financial reports to estimate its intrinsic value.

This stock analysis worksheet presents the intrinsic value of a stock calculated individually by all the above methods.

So what we have at the end is a range of values.

So what we can conclude with this range of values?

The range of value highlights that, the intrinsic value stock will lie within this range.

If the current market price of a is between this range, it becomes a good.

#2.2 Important Thing: Overall Score

A stock which has a market price lower than its intrinsic value is good.

But does it mean that this is all? We need not check anything else about this company?

Ideally speaking the answer is YES. Because the calculation of intrinsic value by methods like DCF, APE, EPV encompasses several parameters to generate its results.

A company whose financial report is weak, can never generate sufficiently high intrinsic value thus making it undervalued.

But I personally find it more convincing to analyze companies based on its other fundamentals as well.

Hence this stock analysis worksheet checks stock based on the following 6 parameters:

  1. Low Price (undervalued or overvalued).
  2. Future growth prospects of the company.
  3. Strength of its Management.
  4. Profitability of its business.
  5. Financial Health.
  6. Likeliness of the company to get Bankrupt any time soon.

Based on these 6 parameters, the worksheet then generates an overall score for the stock in a scale of 1 to 100.

But not all 6 parameters has the same weightage in generating the overall score.

A stock which is undervalued is more likely to get a high score and vice versa.

To be more clear, a stock which score high on profitability, strength of management; but is overvalued, is more likely to get low score.

Utility of Stock Analysis Worksheet - Overall score

Though there is no absolute rule, but upon several iterations, it was found that an overall score of 85%+ means the stock is good.

Please note that the rule of 85% can be variable. For me, 85% works good.

There will be some investors who are more defensive, they would like this score to be higher (say 90%).

There will be some investors who likes aggressive investing. For them even a score of 80% may look good.

So, do your own experiments and try to fix an acceptable overall score for yourself. At the moment, the worksheet assumes that 85%+ score is ideal.

#2.3 Important Thing: Managements

Everyone would like to buy stocks of GREAT companies, right?

But the business in itself cannot become great. It is often the managers who execute the decisions in the company makes it great.

An average company being run by good managers has potential to become great.

Similarly, once a great company being run by average managers will perform only averagely in long term.

For investors it is a absolutely must to know who is running the business before buying its stocks.

The qualification, experience, personality, personal ethics, thoughts of the top managers of the company speaks a lot about how they approach to manage their business.

There will always be difference between Bill Gates, Elon Musk’s, Warren Buffett’s, Ratan Tata’s etc of corporate world with average leaders.

But these are not only names, when you look deeper into the financial reports of their companies, the numbers also speak for them.

In my stock analysis worksheet, I have used some numbers which highlights the managements efficiency. They are as follows:

  1. Return of Equity (ROE).
  2. CROIC
  3. Return on Capital Employed (RoCE).
  4. Return on Asset.

Utility of Stock Analysis Worksheet - Management

I know more ratios could be used here to check the efficiency of the management. But these 4 ratios work just fine.

Basically, the role of the top managers of a company is to ensure that efficiency and effectiveness of all resources of the company.

The main resource of a company is its shareholders equity base, total capital, total assets and free cash flow.

Good managers generate more returns from its available  resources. Some resource that are at ready disposal of the company are as follows:

  • Shareholders Equity.
  • Employed Capital.
  • Total Asset (Fixed, Current etc).
  • Free Cash Flow (FCF).

The higher the returns generated per unit resource of the company talks a load about the companies effective management style.

#2.4 Important Thing: Key Indicator

No matter how big and renowned is the company, it will be not good for investing in it, if its financial health is bad.

Digging slightly deeper into the financial health of a company, it is possible to understand if the company is heading towards bankruptcy or not.

A company whose overall financial health is good, and there is no threat to bankruptcy, such companies are like safe heavens for investing.

Piostroski F Score and Altman Z score talks about the overall financial health and bankruptcy threat of a company respectively.

The stock analysis worksheet calculates the Piostroski F and Altman Z scores of companies.

Utility of Stock Analysis Worksheet - Piostroski Altman

I have personally found these two stock metrics to be reasonably accurate in analyzing the financial health of a company.

I specially like Piostroski score as it uses a range of values from financial reports to judge a company. The metrics used by Piostroski to generate its score are as follows:

  1. ROA
  2. Net cash flow from operating activity
  3. Net Profit
  4. Debt Equity Ratio
  5. Current Ratio
  6. Operating Margin and
  7. Asset Turnover Ratio.

Altman Z score also utilises the following metrics to forecast the bankruptcy threat of a given company:

  1. Working Capital
  2. Total Assets
  3. Retained Earnings
  4. EBIT
  5. Total Liability and
  6. Sales
#2.5 Important Thing: SMA

Not many analysts will agree to the usage of Simple Moving Average of price (SMA) as a metric to fundamentally analyze a stock. But for me its slightly more than helpful.

SMA gives an idea of the current price trends of the stock.

But why a long term investor should care about short term price trends?

Yes this is true, short term price trends do not matter, but it still gives an idea.

It gives a feeble hint that if the stock price is trending towards overvaluation or undervaluation.

Suppose you have analyzed a stocks and it comes out that it is undervalued (intrinsic value more than market price).

For the same stocks, suppose its SMA is showing a falling trend. It means, the price may fall further.

In such a case, an investor may decide to wait slightly more to grab the stock at even attractive prices.

Utility of Stock Analysis Worksheet - SMA

What makes SMA even more attractive to me is its graphical representation by the stock analysis worksheet.

Just giving a glance to this SMA price chart gives a clear idea that, recently, the price has been falling or moving up.

#2.5 Important Thing: Financial Ratios

The worksheet also highlights the financial ratios as computed from its financial statements.

What this worksheet presents here is last five years financial rations of companies.

Though looking at financial ratios (after one has already computed the intrinsic value) is not so relevant, but still it better to give a glance.

To know more about financial ratios, read this.

The stock analysis worksheet generates the financial ratios for last 5 years. These ratios emphasise on the following aspects of investing and doing business in general:

  1. Price valuation rations (like P/E, P/B, EV/EBITDA etc).
  2. Fair price estimation (intrinsic value).
  3. Solvency (ability of company to meet its long term financial obligations).
  4. Liquidity (ability of company to meet its short term financial obligations).
  5. Efficiency of the Management.
  6. Debt Dependency.
  7. Profitability of Business.
  8. Quality of business (financial health and bankruptcy threat).

Conclusion

Who can use the stock analysis worksheet?

People who find it difficult to interpret the meaning of financial reports can use this worksheet.

Interpreting meaning of jargons written on financial reports is only one hurdle, the next bigger hurdle is to read more than ones years reports.

Ideally an investor should read at least 5 years reports to take judgement about a stock.

Suppose is from a financial background and knows how to read balance sheets, income statements and cash flow reports.

But in order to convert this knowledge into a meaningful conclusion, one must know to how calculate the intrinsic value.

Even if one knows how to prepare companies Balance sheets, but he/she may still not know how to convert those numbers into “its intrinsic value”.

This is the job of a fundamental analysts.

The main utility of this stock analysis worksheet is to try to estimate a probable intrinsic value of a company.

So anybody who wants to know the intrinsic value of a company (for academic reasons), can use this worksheet for the purpose.

This worksheet has its own limitations, but it works.

If you want to buy this worksheet, click here….


Disclaimer: All blog posts of getmoneyrich.com are for information only. No blog posts should be considered as an investment advice or as a recommendation. The user must self-analyse all securities before investing in one.

15 Comments on "Utility of Stock Analysis Worksheet V2.0"

  1. N Bhanu prasad | February 20, 2018 at 9:16 am | Reply

    Is this worksheet availability to purchaser is time bound or one time purchase and usable forever? Are there any hide facts in using this w.sheet?

  2. Hi Mani

    I downloaded the sheet today. Unfortunately I was not able to copy paste the balance sheet from money control to the sheet. It was always saying that file is password protected. I did clear the contents which was pre existing and then pated using method ..am I doing something wrong ?

  3. I must admire the knowledge sharing done by you in this site. Hats off . I have downloaded your utility sheet. Its amazing. I have following queries
    1) you have shown graph of PB ratio and PE ratio. How to correlate or interpret with Graph?
    PB ratio- with comparison of current, 5Y & Sensex., PE ratio current , Industry and Sensex PE.
    2) You have emphasized on PEG ratio but couldnt found in your excel sheet.
    3) for banking fund how to arrive intrisic value or how to evaluate bank funds ?

    Thanks

  4. I HAVE PLAN TO PURCHASE SOME GOOD SHARE FOR LONG TERMS AND SHORTS. SO KINDLY SUGGEST SOME SHARE GOOD ONE.

    JOY JACOB

  5. PRAVIN RAJPUT | July 20, 2016 at 11:48 pm | Reply

    The work sheet cost was Rs 255/- when i show before 2 to 3 days. But today it’s price ramp up from 255 to 345/-. Anyone will get rich or not but you….

  6. Hello Mani,

    I want to know more about the Worksheet “Download: Stock Analysis Worksheet (1.1.2)”. What all information i can get in this Worksheet information you provide. Can you please give me you contact number over mail, So i can ask for the detailed information and buy the Worksheet.

  7. could not find worksheet ?

  8. how do i get the worksheet? Didnt find any link to download

  9. is the worksheet retrieve the data online to present the valuation ratios ?

  10. Brexit: Does it matter for India? What should I do hold or sell my stock before date 23.

    • Hello Samir, GB exiting EU will be good for anybody? I don’t think so….
      For a moment its a wait and watch phase.

      • yeah! Thanks.

        I have seen reports come out this week saying that whichever way it happens markets will go up 10 percent or down 10 percent globally, so that’s the swing factor we have at the moment. It is quite huge because as I said on the negative people haven’t taken risk of the table because of the odds are more in favour of Britain staying in at the moment, but if they are wrong then obviously we are in for a bit of a shock.

        I just read this on moneycontrol.

Your comments fuels me to write better...