Stock Analysis Excel Template (Version 4)

Description

Key benefits:

Finding a good stock from 6,000+ stocks listed in the stock market is like finding a needle in a haystack. An investor needs to analyse a large amount of financial data to select a good stock. This Excel helps the investor in the following manner:

• Financial Overview: It presents key financial data of the company for the last 10 years in an easy-to-interpret format. In a single view, an investor can easily identify if the company’s performance is improving or deteriorating over the years.
• Colour-coding: The cells showing an improvement in a parameter over the previous year are highlighted “Green” whereas the cells showing deterioration are highlighted “Red”. These visual cues help a lot in analysing the changes in the performance of the company.
• Comprehensive Analysis: The Excel template covers all the important parameters of the Profit & Loss Statement, Balance Sheet, Cash Flow Statement, and other key ratios.
• Explanations of all the ratios are provided as pop-up comments in the first column. Therefore, an investor can quickly know how she should interpret any ratio or parameter.
• Self-Sustainable Growth Rate (SSGR): It contains SSGR, a ratio developed by us that identifies if a company has the ability to support its growth rate from its profits.
• Strong focus on cash flow performance: Critical parameters of PAT, CFO, capital expenditure (Capex), free cash flow (FCF) and increase in debt over the last 10 years are provided together in one section. A look at this section helps an investor understand the cash flow performance of the company over the last 10 years. She gets to know:
  • Whether the company has collected its profits in cash or there are red flags like inflated sales.
  • Whether the company is making any surplus cash from its operations or is continuously consuming cash.
  • Whether the company is making investments/acquisitions from its own money or is relying on debt for expansion. Debt-funded growth puts a company at risk of bankruptcy. An investor gets to know all these aspects of a company’s financial position by a single look at this section.

• Fund Flow Analysis: A quick way to see from where funds are coming into the company (profits, debt etc.) and where funds are deployed (capex, working capital, loans to others, investments). Quickly identify if the promoter is siphoning off funds.
  • Let us take an example of a company, Cigniti Technologies Ltd. The below snapshot of its fund flow analysis from the Stock Analysis Excel Template shows that over FY2015-FY2023, it had a total fund inflow of ₹661 cr and out of it, a significant amount of money (₹135 cr) has moved out of the company in Other Assets. The maximum outflow (₹161 cr) happened in FY2016. (Purple-coloured cells indicate fund inflows and orange-coloured cells indicate outflows).

• After zeroing in on FY2016, when an investor reads the annual report of the company for FY2016, she notices that Cigniti Technologies Ltd has given “Loans & Advances to related parties” of about ₹116 cr. (page 106 of FY2016 annual report).

• Now, an investor can do her deeper due diligence around these loans and advances to related parties and if needed, she may approach the company for more information/clarifications.
• You may observe that on using this Stock Analysis Excel Template, an investor needs to just look at the financial data presented to her in the form of a dashboard and make an opinion. There is no requirement to manually add any data from annual reports. The analysis, which previously used to take many hours can now be done in a few minutes using this Excel template.

Illustration: How this Excel helps in quickly identifying good companies

Let us see how an investor can differentiate a good company from a poor one in a single look when she uses this Excel template at Screener.in website (Export to Excel feature).

See the example of Inox Wind Ltd. In the below snapshot, at one glance, even without scrolling down the Excel template, an investor can see that

• The company is continuously making losses for the last 6 years (FY2018 onwards). Overall, it had lost ₹766 cr in the last 10 years.
• It made operating losses in 5 out of the last 10 years and operating cash flow losses (negative CFO) in 6 out of the last 10 years.
• It lost more than ₹700 cr in cash flow from operations (CFO) over the last 10 years. However, still, it spent more than ₹2,100 cr in capital expenditure in the last 10 years.
• You will appreciate that if the company has not made any profits or cash flow from operations, then all the money for capital expenditure will come from either the debt or equity dilution. The same is visible for Inox Wind Ltd. Its debt increased by ₹1,859 cr over the last 10 years. It also raised more capital from its shareholders in FY2015 (IPO) and FY2023 (preferential allotment). (As per the annual reports, a total of more than ₹1,400 cr equity capital was raised in these years).
• Therefore, if an investor is interested in investing in companies with strong fundamentals and cash flow strength, then she may ignore this company with just a single glance at the Excel template without spending any more time on the company.

Therefore, an investor can save her precious time by ignoring such companies and instead spending it only on those companies that provide some signs of fundamental strength in the initial overview by using the Excel template.

The availability of the above critical data about any company with a single click from the Screener website saves many hours, which otherwise might have been wasted in analysing stocks that we might have had to reject at a later stage due to poor fundamentals.

An investor may see the below example of another company PI Industries Ltd, which shows signs of fundamental strength in the initial snapshot of the Excel template. From the snapshot containing its consolidated financials, an investor notices that

• Its sales never declined and its profits increased in 9 out of the last 10 years. Over the last 10 years, its sales have increased by more than 4 times and its profits have increased by more than 6 times.
• It had a cash flow from operations (CFO) of ₹5,272 cr over the last 10 years, which is higher than its net profit after tax (PAT) of ₹5,251 cr for the last 10 years indicating that it has converted all of its profits into cash. It shows that the company has kept its working capital under control.
• You will see that the CFO is more than its capital expenditure of ₹3,262 in the last 10 years. Therefore, it had a surplus of more than ₹2,500 cr.
• It used this surplus to pay dividends exceeding ₹600 cr, repaid its debt and kept the balance as cash and investments. In FY2023, it had cash + investments of more than ₹3,000 cr.
• It has a Self-Sustainable Growth Rate (SSGR) exceeding 25%, which is more than the sales growth of 17% achieved in the last 10 years. It indicates that if the company decides, then it can easily meet its sales growth aspirations from its profits.

In light of the above good business signs on the initial assessment, an investor can decide to spend her precious time on further analysis of PI Industries Ltd.

This Excel template provides enough information to decide, which company one should spend more time on and which company one should reject at first glance. This decision to pursue further or reject a company at an early stage is the most critical step to appropriately allocate the limited time available to the investors.

This Excel template allows investors an opportunity to spend their time only on those companies that show a promise of fundamental strength on the initial overview.

Customers’ Feedback about the Stock Analysis Excel Template

“This is a great tool for getting down to the heart of a company’s financials. When I was doing my MBA at NYU, I had a valuation professor who encouraged everyone in the class of 60 to make their own customised sheet similar to what you’ve made. I was a fan of Buffett; so, I remember keeping some of his metrics in view and creating a sheet! Of course, yours is head and shoulders above anything else I’ve seen – kudos!”

– Uday (via email)

Dear Sir,

I must say the Stock Analysis Template is amazing and yet simple to use. I am learning new insights using the sheet and analysing shortlisted stocks, and I have never felt it can be so easy. I am a relatively new retail investor – just about 16 months since I bought my first stock. Thank you for the great work on the Excel sheet and also for motivating countless others like me to venture out in the world of stocks. Best wishes and warm regards.

– Raza (via email)

Full screenshot of the Excel template:

This Excel template has values of more than 80 key parameters in the form of a dashboard. These parameters cover analysis of profitability, capital structure, valuation, the margin of safety, cash flow, creation of wealth, sources of funds and their utilization, growth rates, return ratios, working capital and operating efficiency etc.

The details of all the ratios and parameters included in the Excel template along with their description and interpretation are present later on this page.

Screenshot of larger resolution output of the Analysis Sheet: Click Here

How to download this Excel template

After you have made the payment, you may download the Excel template in any of the following methods:

1. After the order is successful, you will receive an email from us containing your order receipt and payment details. This order receipt will have a link to download the Excel template.
2. After a successful order, you will get an email from us containing your username and password to log in at premium.drvijaymalik.com. You may log in to your account using these details and then download the Excel template from My Account > Downloads

If you still face any challenges in downloading the Excel template, then you may email us at vijay.malik@drvijaymalik.com, detailing the issues that you are facing. We would be happy to help you out.

How to start using this Excel template on Screener

• This Excel template does NOT require a premium subscription of the Screener website. You may use it with the FREE account of Screener.
• The Excel template is very easy to use. After downloading it from our website, the customer has to upload it once to her Screener account. That’s it. Thereafter, whenever she wants to analyse any company, all she has to do is to go to the company’s page on the Screener website and click on the “Export to Excel” button. She will get all the financial data of the company in this Excel template, which she can quickly analyse to decide if it is a good company or a poor company.
• After logging into the Screener website, an investor should upload this Excel template on the following link: https://www.screener.in/excel/ . On this page, first, click on the “Reset Customization” button to clear all previous modifications. Only after reset customization, upload the Excel template to the Screener account.
• You may read the following article for step-by-step instructions along with screenshots to upload this Excel template on Screener: How to Use Screener.in “Export to Excel” Tool
• The formulas of the Excel template can be edited. If an investor wants to further customize it, then she can make the changes. If anything goes wrong, then she can reset her Screener account, download a fresh copy of the Excel template from our website and start afresh.
• On mobile phones, an investor may use the app “Google Sheets” to open the Excel template.

Key instructions to the buyer

1) This purchase is limited to the current version of the Excel sheet only. Any future updates/versions of the Excel sheet need to be bought separately.

2) If in future, because of any reasons, Screener changes the format of data it provides in the “Data Sheet” or makes this template invalid, then I would not be able to provide a resolution of the issues in this Excel template. In such a scenario, I might come up with a new version of the Excel template. However, the new version needs to be bought separately by the users.

3) This Excel sheet is for the sole use of the buyer from www.drvijaymalik.com. Any copying or sharing of this Excel sheet is strictly prohibited.

Frequently Asked Questions (FAQs)

Q. Do I need to avail premium version of Screener.in to use this Excel template?

Ans. No. You do NOT need to avail premium version of Screener.in to use this Excel template. You may use it with the FREE account of Screener.

 

Q. What are the updates/new parameters in various versions of the Excel Template?

The following section provides details of the changes brought out in each of the versions of the Excel template over its previous version i.e. Version 4, Version 3, Version 2 and Version 1.6.

Changes in Version 4 over Version 3:

• Added a new section: Fund Flow Analysis, which is essential to understand the trail of money in the company’s business. Fund flow analysis highlights where the company is getting its funds from and how it is using them. Fund flow analysis can help an investor identify cases where promoters siphon off money from the company.
• Changed conditional formatting for row “Tax%” so that from FY2019 onwards, the tax payout ratio of >20% is highlighted as Green and less than 20% as Red
• Updated many formulas to show blank instead of error code if there is an output error like division by 0. Now, it will show a blank cell instead of error codes like #DIV/0! etc.
• Updated custom formatting colour shades of Green and Red to make them more prominent
• Removed Cash & Eq. at the end of the year from the cash flow statement section, as row number 22: Cash + Investments (CI +NCI) is much more relevant in analysis.

Changes in Version 3.0 over Version 2.0:

• Added a new parameter: Net Cash Generation, which factors in the Free Cash Flow, Other Income, Incremental Debt, Interest Expense, and Dividends. Net Cash Generation for a company during a year should be compared with the change in Cash & Investments since last year and whether it is in line with the Net Cash Generation. Any wide variation between Net Cash Generation and change in Cash & Investments in the year indicates a need for deeper analysis of the financial data while reading the annual report.
• Updated the Free Cash Flow to Equity shareholders (FCFE) to a two-step process: Introduced two new parameters: FCFE.1 and FCFE.2
  • FCFE.1 assumes the deduction of all interest payments for the company as the capitalized interest is deducted as a part of Capex and the non-capitalized interest is deducted as interest expense from P&L.  FCFE.1 is a more stringent parameter than free cash flow (FCF) as in addition to deducting Capex, it also deducts interest payment from CFO.
  • FCFE.2 represents the surplus money generated by the company from its business activities including operating activities as well as non-operating activities (other income) after deducting capital expenditure and interest payments.
• Added a new parameter: Surplus Funds, which represents the total surplus funds remaining with the company from its operating + non-operating activities after payment of capital expenditure, interest, dividends as well as debt repayments or inflow from incremental debt. Investors may compare the “Surplus Funds” with the increase in cash + investments over 10 yrs.
• Added a new parameter: No. of Months of raw material held as inventory. It shows the number of months of raw material requirements held as inventory held by the company on an average throughout the year.
• Added a new parameter: CAGR of market cap increase or decrease from the market capitalization 10 years back to the latest date.
• Added a row of PE ratio history, which shows the value of PE ratio 10 yr back, 7 yr back, 5 yr back, 3 yr back and TTM PE. It helps in the quick assessment of the trend of PE ratio in the past without scrolling down the Excel sheet.
• Important: Removed the separate description sheet and instead added the interpretations of all the parameters of the Analysis Sheet as “comments” in the narration/label cell on the Analysis Sheet itself. So, if an investor wants to learn how a parameter is calculated and how it should be interpreted, then she has to just hover the mouse/cursor on the narration/label cell. A description will pop up, which will contain the details about the parameter, its calculation and its interpretation.

Changes in Version 2.0 over Version 1.6:

• Added the section on various expenses as a percentage of sales (Raw material, Power & Fuel, Employee Costs, Selling and Admin Costs, other manufacturing expenses and Other expenses as a percentage of sales).
• Added working capital cycle (days) calculation based on inventory and trade receivables.
• Added rows for trade receivables and inventory and the calculation of total funds consumed in/released from receivables and inventory over the last 10 years. It is helpful in the quick assessment of one of the key reasons for differences in PAT and CFO.
• Brought together the data of CFO, Capex, FCF, Total Debt, Share Capital, Dividend Payment and Cash + investment on the upper section of the dashboard. This is essential to analyse the cash movements in the company in any year. E.g. in case, the company has a negative FCF, then an investor can immediately see the sources of funds to meet the negative FCF. In such cases, the company may have raised more debt, which will increase total debt. OR the company may have raised additional equity, which will show as an increase in share capital. OR the company may have used existing cash, which will show as a decline in cash + investment. Similarly, if a company has positive FCF, then it may show as an increase in cash + investment OR as a decline in total debt OR high dividend payments or buyback (decline in share capital). The presence of these data points at the top section of the dashboard helps in the quick assessment of the flow of cash in the company over the years.
• Added a calculation of Free Cash Flow to Equity (FCFE), which is calculated as (FCF – interest expense). FCFE calculated in this manner indicates the surplus cash out of CFO remaining after meeting capital expenditure (Capex) and all interest payments (both capitalised as part of capex as well as the interest expensed in P&L.

 

Q. Will this Excel template help me analyse international stocks i.e. other than Indian stock markets? Can you provide me with a list of stocks that I can analyse using this Excel template?

Ans. The Excel template uses the data provided by the Screener website in its “Export to Excel” feature. As a result, it works only for the stocks whose data is present on the Screener website. Currently, the Screener website has the data of about 4,400+ Indian companies. For more details visit Screener.in

 

Q. I had purchased a previous version of this Excel template in the past. Will I get this newer version for free or any discount on the purchase of this newer version?

Ans: Unfortunately, as mentioned earlier, the purchase of any version of the Excel template is limited to that particular version only. An investor needs to purchase the new/updated version of the Excel template separately. There is no discount based on previous purchases.

Details of all the ratios and parameters in the Excel template

The following details of each of these parameters are included in the Excel Template as comments to the label/narration cell. An investor has to just hover the cursor/mouse on the label/narration cell and the details will pop up as a comment that the investor can refer to immediately while analysing the data in the Excel template.

Sales: “Represents the operating income for the company in a given financial year. The conditional formatting highlights the years where sales increased from the previous year as “Green” and years where sales decreased from the previous year as “Red”. Higher the sales growth, the better. However, very high growth rates above 35-50% are usually unsustainable.”

Operating Profit: “It represents EBITDA – Non-Operating Income. (EBITDA = Earnings before Interest, Tax, Depreciation & Amortization). The higher the operating profit the better”

Operating Profit Margin (OPM%) “= Operating Profit/Sales. The conditional formatting highlights the years where OPM increased from the previous year as “Green” and the years where OPM decreased from the previous year as “Red”. The higher the OPM, the better.

Focus on the trend of OPM over the years. If OPM has been fluctuating a lot over the years in a cyclical manner, then it means that the company does not have pricing power over its customers and is not able to pass on the increase in raw material costs to them. On the contrary, if OPM is stable/improving over the years, then it means that the company has sustainable advantages (MOAT) and can pass on the increase in raw material costs to its buyers to protect its margins”

Other Income “Represents the non-operating income for the financial year. Compare other income with the (cash + Investments) held by the company. If the non-operating income is not equal to at least the bank FD return on the cash + investments, then the investor should analyse it deeper to see where the cash has been invested by the company, which is not yielding at least a bank FD return.”

Interest “Represents the interest amount expensed by the company in a financial year. Do not get influenced by a low interest expense. Always try to find out whether the company has been capitalizing its interest cost. A simple method is to multiply total debt with an assumed interest rate and then find out the total interest outgo as has been explained in the calculation of interest coverage below.”

Profit before tax (PBT) Represents the profit before tax (PBT) for the financial year.

Tax% “=Tax/Sales. Represents the tax payout of the company for the financial year. The conditional formatting highlights “Green” in the years where the Tax % is >29.5% (before FY2019) and > 20% (FY2019 onwards) and as “Red” in the years where the Tax % is <29.5% (before FY2019) and < 20% (FY2019 onwards)

The tax payout ratio should be near the standard corporate tax rate in India i.e. about 30-33%. If the tax payout ratio is low, then investors should try to find out if the company has any tax incentives like a unit operating out of the special economic zone (SEZ) etc.

Net profit after tax (PAT) “Represents the net profit reported by the company for the financial year. The higher the PAT growth rate the better. Conditional formatting: If the PAT has increased from the previous year, then the cell will be highlighted “Green”. On the contrary, if the PAT has decreased over the previous year, then the cell will be highlighted as “Red”.

Net Profit Margin (NPM%) “=PAT/Sales. The conditional formatting highlights the years where NPM increased from the previous year as “Green” and the years where NPM decreased from the previous year as “Red”. The higher the NPM the better. Be wary of companies, which show high sales growth with declining/not improving NPM. Companies, which chase growth at the cost of profitability usually do not create sustainable wealth for shareholders”

Capex {(NFA+WIP) change+Dep} “Represents the capital expenditure done by the company in a financial year. Capex = Depreciation + Increase in (NFA+CWIP) over the year. The conditional formatting highlights the years where Capex is less than CFO as “Green” and years where Capex is higher than CFO as “Red”

It is an important parameter that represents the money spent by the company on its operations/plants which is not reflected in the P&L statement. An investor should always compare Capex with the CFO to see whether the company can fund its capital expansion through its operating cash flow. Companies that show high sales growth without much capex have the potential of turning out to be good investments.”

Free Cash Flow (FCF) =CFO – Capex. Free cash flow is the most important parameter of the company analysis. It is like the discretionary surplus that the company makes, which can be distributed to reward the shareholders. I believe that if a company is not able to generate FCF, then it should be avoided by investors, however good its sales growth and profitability margins be.

FCFE.1 (FCF-Int exp) “FCFE.1 = FCF-Interest Expense from P&L. The formula assumes the deduction of all interest payments for the company as the capitalized interest is deducted as a part of Capex and the non-capitalized interest is deducted as interest expense from P&L.

The higher the FCFE.1, the better. The interpretation is the same as FCF (Free Cash Flow) shared above in terms of the availability of surplus cash with the company after meeting capital expenditure. FCFE.1 is more stringent than FCF as in addition to deducting Capex, it also deducts interest payment from CFO

FCFE.2 (FCFE.1 + OI) “FCFE.2 = FCFE.1 + Other Income. (i.e. FCFE.2 = FCF – Interest Exp. from P&L + Other Income). It represents the surplus money generated by the company from its business activities including operating activities as well as non-operating activities after deducting capital expenditure and interest payments.”

Total Debt (D) “Represents the total debt of the company at the financial year ending date. The conditional formatting highlights the years where total debt decreased from the previous year as “Green” and years where total debt increased from the previous year as “Red”. Lower the debt, the better. Debt represents the excess funds used by the company than what it is generating from its operations. It is like living beyond your means.”

Share Capital “Represents the issued and paid-up share capital of the company at the end of the financial year. Capital increases either due to raising further equity by the company or by the issuance of bonus shares. Capital is decreased in cases of share buyback by the company.

The conditional formatting highlights the years where Share Capital decreased from the previous year as “Green” and the years where Share Capital increased from the previous year as “Red”. Ideally, share capital should be constant or decrease due to buyback. An increase in share capital over the years, which is not due to bonus shares, represents a dilution of the stake of existing shareholders.”

Dividend Paid (Div) Without DDT “Represents the dividend paid by the company in a financial year. The dividend shown here does not include the dividend distribution tax (DDT). The conditional formatting highlights the years where dividends increased from the previous year as “Green” and years where dividends decreased from the previous year as “Red”. A company, which generates FCF should pay dividends to shareholders to share the fruits of growth with the shareholders.”

Net Cash Generation (FCF+OI+Debt-Int-Div) “= FCF+OI+Debt-Int-DiV which factors in the Free Cash Flow; then adds other income; then adds incremental debt; and then deducts interest expense (P&L) and dividends. Investors should compare it with the cash & investments (current + non-current) and estimate how much cash & investments have changed since last year and whether it is in line with the Net Cash Generation calculated earlier. Any wide variation here indicates a need for a deeper analysis of the financial data while reading the annual report.

Some of the common causes of a higher increase in cash & investments than Net Cash Generation are:

1. Equity dilution whether by IPO, FPO, QIP, Warrants etc.
2. Increase in the value of market-linked investments like shares, mutual funds etc.

Some of the common causes of a lower increase in cash & investments than Net Cash Generation are:

1. Equity reduction like buy-back of shares
2. Giving loans & advances like capital advances, inter-corporate deposits, tax payments and prepaid expenses etc., which are cash outflow but do not reflect as investments.
3. A decline in the value of market-linked investments like shares, mutual funds etc.

Cash + Investments (CI +NCI) “=Cash & equivalent + Current Investments + Non-Current Investments held by the company at the end of the financial year. Very high cash levels in companies, which do not pay out dividends should be looked at with caution. It might be that the cash shown on the balance sheet is fictitious.”

Self-Sustainable Growth Rate (SSGR) “Represents the debt-free self-sustainable growth rate potential of a company. It has been observed that companies growing at a higher rate than SSGR are using more resources than their inherent operations can produce and therefore, witness increasing debt levels. Similarly, companies that are growing at a rate less than or equal to SSGR can sustain their growth rates without raising debt/see declining debt levels.

It is to be kept in mind that SSGR does not take into account the funds getting blocked or released from working capital. SSGR higher than the sales growth rate is desirable.”

Fund Flow Analysis: It helps investors in identifying the key sources of funds and the key areas of utilization of funds during a period. It provides a great help in finding companies, which are syphoning off money, giving loans to promoters/related parties, doing significant capital expenditure, investments in subsidiaries etc.

• Equity + Reserves: An inflow usually indicates profits retained by the company (after payment of dividends), equity dilution (e.g. IPO, QIP) etc. An outflow usually indicates a decline in net worth due to losses, very high dividend payments, buybacks, repayment of preferred shares etc.
• Debt: It includes primarily debt and lease liabilities. An inflow indicates more loans taken by the company from financial institutions or other corporations. An Outflow indicates loan repayments done by the company. An investor should be cautious because in corporate debt restructuring, where loans are written off by the banks or are converted into equity, this parameter will show an outflow due to the reduction of debt shown in the balance sheet, which might be confused by investors as loan repayment.
• Trade Payables + Other Liabilities: It includes multiple items like Trade Payables, Deferred Tax Liabilities, Long Term as well as Short Term Provisions. An inflow primarily shows an increase in trade payable and other liabilities and provisions. An outflow primarily shows a payment of trade payables and settlement of other liabilities and provisions.
• Fixed Assets: These include net fixed assets (netblock) and capital work in progress (CWIP). An Outflow indicates money spent in buying capital assets like land, plants & machinery as well as revaluation of assets like land etc. An inflow indicates the sale of assets like land, manufacturing plants or devaluation/write-off of assets. An investor should note that the fund outflow under this heading will be different (lesser) than the data in the capital expenditure (capex) row. It is because this row takes into account only changes in net block and CWIP whereas the calculation in the capex row includes changes in net block and CWIP as well as depreciation.
• Trade Receivables: An inflow indicates that the company has collected money from its customers and as a result, its trade receivables have declined. An outflow indicates that a higher amount of money of the company is now stuck with its customers. Usually, as a company grows its business, then with increasing business size, its trade receivables also increase in line with the sales growth, which reflects as an outflow under trade receivables. However, if there are abnormally large outflows in trade receivables, then an investor should be cautious. This is because if a company indulges in fictitious sales (frauds), then a large outflow under trade receivables indicating non-payment by customers is usually one of the signs to identify such cases.
• Inventory: An inflow indicates that the company has sold inventories or has improved its inventory utilization efficiency and as a result, its inventory has declined. An outflow indicates that a higher amount of money of the company is now stuck in inventory. Usually, as a company grows its business, then with increasing business size, its inventory levels also increase in line with the sales growth reflecting an increase in its inventory levels. However, if there are abnormally large outflows under inventory, then an investor should be cautious. This is because the company may be facing issues of obsolete inventory, which might be non-saleable and the company might have to write off/recognize losses later on.
• Cash + Investments: It includes cash & equivalents as well as current and non-current investments of the company. Equity investments into subsidiaries, joint ventures (JVs), special purpose vehicles (SPVs) etc. are also included in this section. An outflow in cash and investment indicates that the cash and investments on the company’s balance sheet have increased i.e. the company has bought investments, invested more money in fixed deposits, current accounts etc., and made equity investments in subsidiaries, JVs etc. It means that the money has flown from the company to banks/AMCs, JVs, subsidiaries, SPVs etc. An inflow in cash and investment indicates that the cash on the company’s balance sheet has declined i.e. the company has sold investments, liquidated fixed deposits, withdrawn money from the current account etc. It means that the money has flown from banks/AMCs to the company.
• Other Assets: These include long-term and short-term loans and advances to related parties, directors, and employees as well as other corporations like inter-corporate deposits (ICDs), advances for capital assets, advance income tax payments, income tax refund receivables, input tax (GST) credit receivable, taxes/duties/penalties paid under protest, prepaid expenses, security deposits paid etc. An outflow here means that the company has given advances to other parties, whereas an inflow here means that it has received its money from other parties. We have highlighted outflows in this line as “bold red” because almost all syphoning off of money by promoters in the form of loans & advances and ICDs to related parties etc. will show up as an outflow in this section. Therefore, investors must analyse in depth any significant outflow in this section (other assets).
• Total Inflow: It is the sum total of funds’ inflow from all the heads of the balance sheet. It should be equal to the Total Outflow.
• Total Outlow: It is the sum total of funds’ outflow from all the heads of the balance sheet. It should be equal to the Total inflow.

Trade Receivables Lower the trade receivables, the better it is for the company

Inventory Lower the inventory, the better it is for the company

PBT/Avg. NFA (<10%,>25%) “=PBT/average of Net Fixed Assets at the start and the end of the financial year. The conditional formatting highlights the years where PBT/NFA is > 25% as “Green” and years where PBT/NFA is <10% as “Red”

This is to highlight that if a company is not able to earn at least 10% or Bank FD rate from its fixed assets, then it should ideally sell all its assets and put the amount in a bank fixed deposit and earn a higher return without taking the pains of running a business. Higher the PBT/Avg. NFA, the better”

ROE on Avg Equity (<7%,>25%) “=PAT/average of shareholder’s equity at the start and the end of the financial year. The conditional formatting highlights the years where ROE is > 25% as “Green” and years where ROE is <7% as “Red”. This is to highlight that if a company is not able to earn at least 7% post-tax from its equity or shareholders’ funds, then it should ideally put its entire equity in a bank fixed deposit and earn a higher return without taking the pains of running a business.

The higher the ROE, the better. However, always be cautious with companies with high ROE and high Debt/Equity. In such companies, high leverage is the reason for high ROE, which is not sustainable”

ROCE (EBIT on Avg CE/TA) (<10%,>35%) “=EBIT/average of total assets at the start and the end of the financial year. (EBIT = Earnings before Interest and Tax). The conditional formatting highlights the years where ROCE is > 35% as “Green” and years where ROCE is <10% as “Red”.

This is to highlight that if a company is not able to earn at least 10% pre-tax ROCE from its assets, then it should ideally put its entire assets in a bank fixed deposit and earn a higher return without taking the pains of running a business”. Higher the ROCE, the better”

Incremental ROE 3Yr Rolling “Represents the incremental returns/profits generated by the earnings retained by the company over any consecutive three-year period. Represents the efficiency of utilization of incremental money being deployed by the company management in its operations.

The conditional formatting highlights the years where incremental ROE increased from the previous period as “Green” and years where incremental ROE decreased from previous years as “Red”. Ideally, incremental ROE 3 years rolling should be stable or improving.”

Net Fixed Asset Turnover (High is better) “=Sales/average of net fixed assets at the start and end of a financial year. The conditional formatting highlights the years where NFAT increased from the previous year as “Green” and years where NFAT decreased from the previous year as “Red”.

The higher the NFAT, the better. High NFAT represents that the company can use its fixed assets in a very efficient manner (many times, due to the nature of its business) and does not require to do a lot of capex.

Receivables days (Low is better) “=365/(Sales/average of account receivables at the start and end of the financial year). The conditional formatting highlights the years where receivables days decreased from the previous year as “Green” and years where receivables days increased from the previous year as “Red”.

The lower the receivables days, the better. It means that the company is not giving a higher credit period to customers to generate sales. In the case of fictitious sales where cash is not received from customers, the company will see increasing receivables days.”

Inventory Turnover (High is better) “=Sales/average of inventory at the start and the end of the financial year. The conditional formatting highlights the years where inventory turnover increased from the previous year as “Green” and years where inventory turnover decreased from the previous year as “Red”.

The higher the inventory turnover ratio, the better. Lower inventory turnover means that the company is accumulating a lot of inventory, which might become obsolete later. In extreme cases, the inventory being shown might be fictitious and may be an indicator of underlying fraud.”

Months of Raw Material held as Inventory (Low is better) “= {365/(Raw Material Cost during the financial year/average of inventory at the start of the year and the end of the year)}/30. It shows the number of months of raw material requirements held as inventory held by the company on an average throughout the year. The lower the amount of inventory held, the better.

Conditional formatting: if during the year, the number of months of raw material cost held by the company is higher than the previous year, then the cell will be highlighted as “Red”. On the contrary, if during the year, the number of months of raw material cost held by the company is lower than the previous year, then the cell will be highlighted as “Green”.

Working capital cycle days (Receivables + Inventory Days) “The formula calculates working capital days as a sum of receivables days and inventory days. It does not take into account payables days as otherwise, companies can easily mask the poor working capital position by delaying the payment to suppliers/vendors.

The conditional formatting highlights the cells in which working capital days have decreased (i.e. improved) over the previous year as “Green” and the cells in which the working capital days have increased (i.e. deteriorated) over the previous year as “Red”. Lower the working capital days, the better for the company”

Net Fixed Assets (NFA) “Represents the net fixed assets of the company after factoring in the accumulated depreciation at the end of a financial year. An increase in NFA means that the company has done capacity addition, which should lead to higher sales/income in future.”

Capital Work in Progress (CWIP) “Represents the capital work in progress (CWIP) of the company at the end of a financial year. CWIP usually shows the projects under execution by the company. Once the projects get completed and become operational, they are shifted from CWIP to fixed assets (NFA).

An increase in CWIP means that the company is currently executing any project/capacity expansion. Once the under-construction project is complete, it is shifted from CWIP to NFA.”

Dividend Payout (Div/PAT) “=Dividend paid/net profit after tax. This criterion helps in the identification of any stable dividend payout policy if it is being followed by the company for paying dividends year on year.

It is good if the dividend payout ratio is constant or improving. It means that the company follows:

1. a stable dividend policy.
2. is interested in sharing higher rewards of the growth with shareholders with higher growth in business”

Retained Earnings (RE=PAT-Div) “=Net profit after tax – Dividend Payout. Cumulative retained earnings for the last 10 years are compared with the increase in market capitalization of the company over the last 10 years to see whether the company has created at least equal wealth for its shareholders to the earnings that have been retained by it over the last 10 years.

It helps in identifying companies that have generated wealth for shareholders from those companies that have destroyed wealth for shareholders. Cumulative retained earnings should be compared with the cumulative increase in market capitalization over the years.

Price to earning “= Share market price/earnings per share for the period. The P/E ratio is an important criterion to arrive at the valuation levels of any company’s stock. The lower the P/E ratio, the better.

Conditional formatting: If the PE ratio has increased from the previous year, then the cell will be highlighted as “Green”. On the contrary, if the PE ratio has decreased over the previous year, then the cell will be highlighted as “Red”. (This is to show the increasing benefits to the existing shareholders).”

Market Capitalization: “Represents the market capitalization of a company for the financial year. The share price taken here is the average price for April of each financial year. Cumulative retained earnings should be compared with the cumulative increase in market capitalization over the years.

Conditional formatting: If the market capitalization has increased from the previous year, then the cell will be highlighted “Green”. On the contrary, if the market capitalization has decreased over the previous year, then the cell will be highlighted as “Red”.

Total Equity (E) “Represents the total shareholders’ funds invested in the company at the end of the financial year. It includes paid-up capital and reserves including retained earnings. Compare the increase in equity with the retained earnings for the year. They should ideally be the same.”

Debt-to-Equity ratio (D/E) “=Total Debt/Total Equity. It represents the extent of leverage that a company has in its capital structure. A Higher D/E ratio represents high leverage and vice versa.

The lower the debt-to-equity ratio, the better. However, more than the debt-to-equity ratio, it is important to check the debt serviceability by way of interest coverage and FCF. There have been cases where the company had a low debt-to-equity ratio but still faced financial stress as the operating profit & cash were not sufficient to meet debt obligations.

Cost of funds “It is a manual entry field provided for the investors to put in the assumed cost of debt (Interest rate), which the investor believes lenders would be charging to the company for their loans. The default value is 12%. The investor can change the value to her preference.

Interest Outgo “=Cost of funds*(average of total debt at the start and end of the financial year). The interest outgo, calculated on total debt, takes care of the interest that has been capitalized by the company and therefore, has not been shown as interest expense in the P&L statement. The lower the interest outgo the better.

Interest Coverage (OP/Int. Out) “=Operating profit/interest outgo. It represents the servicing ability of the company for the total interest outgo of the company, including P&L and capitalized interest.

The higher the interest coverage, the better. Conditional formatting: A cell will be highlighted in “Red” if the interest coverage ratio is less than 1.

Cash from Operating Activity (CFO) “Represents the CFO for the financial year as reported by the company in its cash flow statements. The conditional formatting highlights the years where the CFO is greater than PAT as “Green” and the years where the CFO is less than PAT as “Red”. The higher the CFO, the better. Always compare the CFO with PAT to see if the funds are getting stuck or released from working capital.

Cash from Investing Activity (CFI) “Represents the CFI for the financial year as reported by the company in its cash flow statements. If higher outflow in CFI, then read the annual report to find out whether the same is for capex or other investments”

Cash from Financing Activity (CFF) “Represents the CFF for the financial year as reported by the company in its cash flow statements. It contains debt repayments, interest payments, and dividend payments.

Net Cash Flow (CFO+CFI+CFF) “Represents the Net cash inflow/outflow (CFO+CFI+CFF) for the financial year et reported by the company in its net flow statements. Net cash made/consumed by the company in a year. The higher the net cash flow the better.

Retained Earnings (RE) in 10 Yrs (A) “represents the sum of earnings retained and not distributed by the company over the last 10 years. Cumulative retained earnings should be compared with the cumulative increase in market capitalization over the years.

Increase in Mcap in 10 yrs (B) “Represents the increase in market capitalization of the company since 10 years ago. Please note that the increase in market capitalization has been taken up to the current date from the market capitalization of 10 years before as I believe that the current market capitalization rather than the market capitalization at the end of the recent financial year is a better representative of up-to-date wealth generation data. Cumulative retained earnings should be compared with the cumulative increase in market capitalization over the years.

Conditional formatting: If the increase in market capitalization over 10 years is higher than the retained earnings, then the cell will be highlighted “Green”. On the contrary, if the increase in market capitalization over 10 years is lower than the retained earnings, then the cell will be highlighted “Red”.

The value created per INR of RE (B/A) “=Increase in market capitalization since 10 years ago/total retained earnings of the last 10 years. It is expected that the company should at least create a wealth of INR 1 in market capitalization for its shareholders for every INR 1 of earnings retained by it.

At least INR 1 of increase in market capitalization should have happened for every INR 1 retained by the company. Otherwise, it is a wealth-destroying company for shareholders. The higher the value created, the better.

Raw Material as % of Sales: “Formula used is (Raw Material Costs – Change in Inventory)/Sales for the year. It is preferable to have declining or stable Raw material costs as a % of sales over the years. It indicates that the company can pass on the increase in raw material costs to its customers.

Conditional formatting: A cell will be highlighted “Green” if the expense % has declined from the previous year. On the contrary, if the expense % has increased over the previous year, then the cell will be highlighted as “Red”.

Power & Fuel as % of Sales “Formula used is Power & Fuel costs/Sales for the year. It is preferable to have declining or stable power & fuel costs as a % of sales over the years.

Conditional formatting: A cell will be highlighted “Green” if the expense % has declined from the previous year. On the contrary, if the expense % has increased over the previous year, then the cell will be highlighted as “Red”.

Employee Costs as % of Sales “Formula used is Employee costs/Sales for the year. It is preferable to have declining or stable employee costs as a % of sales over the years. Investors may also compare the employee costs of a company with its peers to find out whether there are any major differences

Conditional formatting: A cell will be highlighted “Green” if the expense % has declined from the previous year. On the contrary, if the expense % has increased over the previous year, then the cell will be highlighted as “Red”.”

Selling & Admin Costs as % of Sales “Formula used is Selling & Admin Costs/Sales for the year. It is preferable to have declining or stable Selling & Admin costs as a % of sales over the years.

Conditional formatting: A cell will be highlighted “Green” if the expense % has declined from the previous year. On the contrary, if the expense % has increased over the previous year, then the cell will be highlighted as “Red”.

Other Manufacturing Expenses as % of Sales “Formula used is Other Mfr. Exp/Sales for the year. It is preferable to have declining or stable other manufacturing expenses as a % of sales over the years.

Conditional formatting: A cell will be highlighted “Green” if the expense % has declined from the previous year. On the contrary, if the expense % has increased over the previous year, then the cell will be highlighted as “Red”.

Other Expenses as % of Sales “Formula used is Other Expenses/Sales for the year. It is preferable to have declining or stable other expenses as a % of sales over the years.

Conditional formatting: A cell will be highlighted “Green” if the expense % has declined from the previous year. On the contrary, if the expense % has increased over the previous year, then the cell will be highlighted as “Red”.

Total Dividend Paid in 10 Yrs “Represents the total dividend paid by the company over the last 10 years excluding dividend distribution tax. Conditional formatting: If the amount of dividend paid in the last 10 years is more than FCFE.2, then the cell will be highlighted “Red”. This is to indicate that the company has paid a dividend in excess of the funds generated by its business activities.

Increase in Debt in 10Yrs “If the total debt has decreased over the last 10 years, then this cell will be highlighted as “Green”. If the total debt has increased in the last 10 years, then the cell will be highlighted as “Red”.

A decline in debt over the years is better. Companies with good cash generation over the years will witness their total debt decline over the years whereas the companies, which rely on debt to fund their growth, will witness the total debt increase over the years

Surplus funds “Represents the total surplus funds remaining with the company from its operating + non-operating activities after payment of capital expenditure, interest, dividends as well as debt repayments or inflow from incremental debt. Investors may compare the “Surplus Funds” with the increase in cash + investments over 10Yrs.

FCF/CFO “Represents the proportion of CFO, which has become available as free cash flow (FCF) to the shareholders in the last 10 years. Conditional formatting: If the FCF/CFO is more than 25%, then the cell will be highlighted “Green”. On the contrary, if the FCF/CFO is less than 25%, then the cell will be highlighted “Red”.

A higher proportion of CFO becoming available as FCF indicates low capex requirements, which is a feature to identify cash cows. The higher the proportion of Free Cash Flow out of Cash Flow from Operations, the better.

Sales Growth “Represents the trend of growth in sales for the last 10 years, last 7 years, last 5 years, last 3 years and the growth in the last 12 months over the last financial year sales. Conditional formatting: If the Growth rate is negative, then the cell will be highlighted “Red”.

Assessment of sales growth trends is essential to find out whether the company has been showing consistent sales growth year on year or is influenced by very high/low sales growth in any particular year. The higher the sales growth, the better. However, very high growth rates above 35-50% are usually unsustainable”

OPM “Represents the trend of average Operating Profit Margin for the last 10 years, last 7 years, last 5 years, last 3 years and the OPM in the last 12 months. Conditional formatting: If the Growth rate is negative, then the cell will be highlighted “Red”.

The higher the OPM the better. Focus on the trend of OPM over the years. If OPM has been fluctuating a lot over the years in a cyclical manner, then it means that the company does not have pricing power over its customers and is not able to pass on the increase in raw material costs to them. On the contrary, if OPM is stable/improving over the years, then it means that the company has sustainable advantages (MOAT) and can pass on the increase in raw material costs to its buyers to protect its margins.

PAT Growth “Represents the trend of growth in net profit after tax (PAT) for the last 10 years, last 7 years, last 5 years, last 3 years and the growth in last 12 months over last financial year PAT.  Conditional formatting: If the Growth rate is negative, then the cell will be highlighted “Red”.

Assessment of the PAT growth trend is essential to find out whether the company has been showing consistent PAT growth year on year or if it is influenced by very high/low PAT growth in any particular year. The higher the PAT growth rate, the better.

BV Growth “Represents the trend of growth in book value per share for the last 10 years, last 7 years, last 5 years and last 3 years. Conditional formatting: If the Growth rate is negative, then the cell will be highlighted “Red”. The higher the increase in book value over the years, the better.

PE History Shows the value of the PE ratio 10 years back, 7 years back, 3 years back and the PE ratio based on trailing twelve months (TTM) earnings.

CMP “Represents the closing market price of the stock of the company for the previous trading day. Ignore the absolute level of the current market price. It does not matter whether the current price is INR 10 or INR 10,000/-. Always focus on other parameters of the company assessment.

P/E “Represents the latest price-to-earnings (P/E) ratio based on the current market price and the earnings per share (EPS) for the last 4 quarters/trailing twelve months (TTM). If the data of any of the last 4 quarters is absent, then the cell would show the P/E ratio based on the last financial year’s earnings. The lower the P/E ratio, the better.

P/B “Represents the latest price to book value (P/B) ratio based on the current market price and the book value at the end of the recent most financial year. Lower the P/B ratio, the better.”

P/E*P/B “=latest P/E ratio * latest P/B ratio. The conditional formatting highlights the cell as “Red” whenever the P/E*P/B ratio exceeds 22.5, which is the maximum buy value guided by Benjamin Graham in his book “The Intelligent Investor”. The lower the P/E* P/B multiplication number, the better.

Div Yield “=Dividend paid in last financial year/latest market capitalization. The conditional formatting highlights the cell as “Green” whenever the dividend yield is greater than 4%. If the company generates positive FCF, then the higher the dividend yield, the better. If FCF is negative, then the dividend is funded by debt and the investor should not take any comfort from such dividend yield.”

M. Cap Represents the latest market capitalization of the company based on the closing price of the previous trading day.

Mcap CAGR 10Y “Represents the CAGR of market capitalization from the first reported financial year “Cell:B2” to today. (Labels it as 10 years CAGR). Conditional formatting: If the CAGR is positive, then the cell will be highlighted “Green”. On the contrary, if the CAGR is 0 or negative, then the cell will be highlighted “Red”.

Closing share price on March 31, 10 years back “It is a manual entry field, which is provided for the investor to punch in the split/bonus adjusted share price of the company for March 31 of the year 10 years back on her own so that she can get the accurate increase in market capitalization since 10 years ago.

Please note that the default value is “0” and the formula for calculating the increase in market capitalization has been drafted in a manner that if “0” is put in this cell, then the increase in market capitalization is shown as per the data provided by Screener. However, if the investor manually puts her own value in this cell, then the sheet will show the increase in market capitalization as per her share price value.

Dividend Growth “Represents the trend of growth in Dividends for the last 10 years, last 7 years, last 5 years and last 3 years. If the company generates positive FCF, then the higher the dividend growth rate, the better. If FCF is negative, then the dividend is funded by debt and the investor should not take any comfort from such a dividend growth rate.

Avg. PE “Represents the trend of average Price to Earnings ratio (P/E) for the last 10 years, last 7 years, last 5 years, last 3 years and the OPM in the last 12 months. The lower the P/E ratio, the better.

Debt “Represents the trend of growth in total debt for the last 10 years, last 7 years, last 5 years and last 3 years. The lower the debt, the better. Debt represents the excess funds used by the company than what it is generating from its operations. It is like living beyond your means.”

Book Value “Represents the trend of growth in book value per share for the last 10 years, last 7 years, last 5 years and last 3 years. The higher the increase in book value over the years, the better.

Change in 10 Yrs: Receivables “Shows the total money consumed in or released from trade receivables over the last 10 years

Change in 10 Yrs: Inventory Shows the total money consumed in or released from inventory over the last 10 years

Last 4 Quarters Please note that if the data for any of the last 4 quarters is absent in the “Data Sheet”, then column L, which contains the last 12 months (TTM) figure, will remain blank.