How to Make a Basic Fundamentals ratio excel sheet, A guide to select Quality undervalued stocks

As some new learners requested, I am making below steps for making understandable on how to make “Basic Fundamental ratios Sheet?” Like below

I dedicate this post to Mr. Mahesk Kaushik, the share genius, who is the starting point of my fundamental knowledge and inspiration to select good companies and value investing methods. You can see his beautiful blog, if not yet seen. But I think if you are reading this, you may already seen it. LLink given below

http://sharegenius.maheshkaushik.com/

Disclaimer: This is the process which I follow personally, This may be good or worse, you can decide as per your analysis and also this is not the ultimate tool for identifying thee valuable stocks, this is one of the sheet which makes you closer for the same.

Each step Explains each row , about data segregation, formulas, guidance etc etc

Before going to steps let’s see the sheet once.

 

This was the latest comparison sheet for Sector - Chemicals and Industry: Packaging Films, let’s assume sr. no 1-17 are as steps

Step: 1 Promoters holding:

We all know the promoters holding meaning, I sue www.bseindia.com for the same, the reason is accuracy and you can see last 4 quarters instantly.

Just visit the website, type stock name, come to the right corner below and click on SHP, you will see like below window.

Above 50, I feel safe and 50I means increase of promoters holding from last quarter and 50D means decrease from last quarter, refer below image

 

Step 2: Pledged Shares

  Promoters are the majority shareholder group that manages the day-to-day affairs of a company. When they need money, very often, promoters of listed companies pledge all or some of their shares with lenders. It means that these shares are offered as collateral to banks in exchange for loans.
So Zero pledged shares are always preferable
Go to moneycontrol.com, Enter share name and on from left column select share holding,

 

On the left column you can see the Pledged shares count and at bottom percentage

 

 

In our case its ZERO, a good sign

Step 3: Dividends / yield:

From moneycontrol.com, after selecting the share, below chart you can get dividends and Yield

You can also view Dividend payout history by selecting the corporate action

Step 4: Net sale per Share: NSS:

                As the name indicates, it is the value for net sale per share in rupees. The value should be greater than CMP, I use 75% to the CMP as my selection criteria as NSS of greater value is rare.

Formula for Net sale per share = Total Sales or revenue / Total no. of shares held.

Total Revenue you can get from Step 1 results tab &

Total Shares you can get from Step 2 end of the page.

 

Step: 5 200 DMA

200 DMA means 200 Day Moving average, always CMP trading near or above 200 DMA is positive.

You can get the same in moneycontrol.com, Enter share name and select “Historic Prices & Simple Moving Averages” as shown below

 Step 6: 3 Yr. WMA

  This value indicates the present position of value of shares in comparison with historical price. This important steps ensures not enter in high valued scrip’s and always CMP trading near or below 3 Yr WMA is positive (Same thing Mahesh sir taught me as base price in different method)
For the same go to ChartInk.com and select scrip, go to chart and in chart select MA and select Weighted and put value 780 (approx 3 years), then select period 3 Years and update chart, then you can see the 3 Yr. WMA as shown in below picture.

Step: 7 Profit / Loss: 

Its a Financial performance of the company compared with the previous years, whether the company in Profits or Loss or Nuetral. You can see it from Screener. In Annual Reports section.

Step 8: Market Cap

  Market capitalization is one of the best measures of a company's size. Also known as market cap, market capitalization is the total market value of a company's outstanding shares of stock. To calculate market capitalization, simply take the total number of a company's shares outstanding and multiply that figure by the stock's market price (CMP)

For total no. of shares, refer step 4

Step 9: Revenue: 

 You can get the total revenue by as like in Step 4 or “Moneycontrol.com>Financials>Profit and loss”

Always remember that Total revenue should be mostly from operations / sales, keeping other income as low as possible

Step 10: Net Profit:

Same as like step 9, you can get Net profit from either BSEindia.com or www. Moneycontrol.com

Net profit should always trending up or at least higher than previous year

Step 11: Net Profit margin %: 

  Net profit margin is the percentage of revenue remaining after all operating expenses, interest, taxes and preferred stock dividends (but not common stock dividends) have been deducted from a company's total revenue.

Net Profit Margin % = Net profit / Revenue * 100 

Net Profit and net revenue, you can get from step 8 and 9

 

 

Up to now we seen the general terms and values of shares, from below we see the real ratios we cann as “Fundamental ratios” of the company

Step 12: ROE “Return on Equity” 

My preference for ROE is above 15 and above peers

Return on equity (ROE) is the amount of net income returned as a percentage of shareholders equity. Return on equity measures a corporation's profitability by revealing how much profit a company generates with the money shareholders have invested.

ROE is expressed as a percentage and calculated as:

Return on Equity = Net Income/Shareholder's Equity*100

Net Income or Net profit we can get from Step 10 and for share holders fund you can get from Moneycontrol.com<Financials, pls refer below image

Step 13: P/B Ratio:

P/B ratio means price to book ratio where price = CMP and book means book value. 

P/B ratio = CMP / Book Value. (Less than 1 is always preferred and to max up to 2.5)

 

You can directly get is from Moneycontrol.com, from step 3, refer below image

 

Step 14: Price/Earning, P/E ratio:

The price-earnings ratio (P/E ratio) is the ratio for valuing a company that measures its current share price relative to its per-share earnings. The price-earnings ratio is also sometimes known as the price multiple or the earnings multiple.

P/E ratio = CMP / EPS (EPS means Earning per share) 

I prefer P/E should be less than Industrial P/E and less than peers or below 15

 Earnings per share we can get from Step: 10, pls refer below image (Pls note EPS for last year also)

Step: 15, Debt/ Equity ratio: 

   The debt-to-equity ratio is a measure of the relationship between the capital contributed by creditors and the capital contributed by shareholders. It also shows the extent to which shareholders' equity can fulfill a company's obligations to creditors in the event of liquidation.

D/E ratio = Assets / Liabilities or Share holders fund / Total Liabilities

Refer step:12 for going to balance sheet and for share holders fund and for liabilities from the same sheet ADD “Total Non-Current Liabilities” and “Total Current Liabilities”

So, D/E ratio = Total Share holders Funds / (Total Non-Current Liabilities + Total Current Liabilities)

I prefer 0.5 and below as safe level and Maximum to 1. Refer below image

 

Step: 16, Earnings Yeild %:

Earnings yield are the earnings per share EPS for the most recent 12-month period divided by the current market price per share. The earnings yield (which is the inverse of the P/E ratio) shows the percentage of each rupee invested in the stock that was earned by the company.

Earnings Yield % = EPS / CMP *100 (CMP = Current market price)

*EPS you can get from step 14

Earning’s Yield should be greater compared to last QoQ or YoY 

Step: 17, P/E Growth, PEG Ratio:

 

Price-to-earnings, or P/E ratio, is perhaps the most commonly used metric used when valuing stocks. However, P/E ratios aren't always useful all by themselves, as they don't take a company's growth rate into account.

For this reason, the price-to-earnings-growth ratio, or PEG ratio, takes the P/E ratio and combines it with the company's expected earnings growth, in order to better express the valuation of growing companies. The PEG ratio is easy enough to calculate -- simply divide the P/E ratio by the company's expected earnings growth rate.

PEG Ratio = P/E ratio / Earnings growth rate

P/E ratio you can get from step: 14 and for growth rate = ((this year EPS/Last Year EPS)-1)*100, get EPS from Step: 14

In general, a PEG ratio of less than 1 is considered to be indicative of an undervalued stock and a PEG ratio of more than 1 could imply that a stock is too expensive.

Conclusion:

This way we can identify the basic fundamentals of a company, financial ratios, growth rates and Valuation of stock at CMP but before taking final decision, below things are needed to be assured

1.       Comparison of values with PEERS

2.       Companies performance QoQ and YoY

3.       Future growth aspects of a company (From annual reports and news)

Thank you, Comments are welcomed