Hello Dear Readers,
Today I have a very interesting surprise I want to share with all of you.
I have designed an Excel template for screener.in to help me dissect years of data and understand the real story behind the numbers.
At the outset, the template has three tabs
The Dashboard,
The Snapshot, and
The AARD Screener
The AARD Screener is basically where I have done all my calculations. I have taken to best of my knowledge data from the financial statements (Income Statement, Balance Sheet, Cash Flow) and tried to arrive at meaningful ratios and metrics. This is where I’ve dumped *everything*. It’s my “case file” on a company, pulling a decade of financial statements and calculating every ratio I could think of. It’s comprehensive, powerful, and honestly, a bit overwhelming.
This is the reason I have created the Snapshot tab. I’ve taken the most critical clues from the AARD Screener and put them in the Snapshots tab in one place.
This section can be a very powerful statistical analysis workflow. I have provided in this section a step-by-step instruction for performing regression analysis using Excel’s Data Analysis toolkit.
The process involves selecting stock prices as your dependent variable (Y-range) and choosing independent variables like ROCE, ROE, sales growth, or profit margins (X-range). Excel then calculates correlation coefficients and statistical significance levels.
The workflow explains how to interpret R-squared values, which indicate what percentage of stock price variance can be explained by your chosen financial metrics. Higher R-squared values suggest stronger predictive relationships between financial performance and stock price movements.
You can experiment with different combinations of financial metrics to build predictive models.
The Dashboard as the name suggests gives a visual representation to all key metrics.
Sometimes I just need a clean, high-level view on the company and a visual representation. This was my reason for creating The Dashboard. It takes the key findings and presents them in a simple, visual format.
Things I have tried to inculcate in my template:
Profitability & Efficiency:
Return on Equity (ROE): How much profit the company generates with the money shareholders have invested.
Return on Capital Employed (ROCE): A broader measure of how efficiently the company is using all its capital (both equity and debt) to generate profits.
Operating Profit Margin (OPM) & Net Profit Margin (NPM): How much profit is left from each rupee of sales, before and after all costs.
Asset Turnover: How efficiently the company uses its assets to generate sales.
Growth Trends:
Sales, PAT, and EPS Growth: The year-on-year growth rates for Sales, Profit After Tax, and Earnings Per Share. This helps you see if the company is expanding.
Financial Health (Leverage & Safety):
Debt to Equity: How much debt the company uses compared to its equity. A high ratio can mean higher risk.
Interest Coverage Ratio: Can the company easily pay the interest on its debt? A higher number is safer.
Financial Leverage: The degree to which a company uses borrowed money.
Cash Flow & Valuation:
OCF/ Net Income: The quality of earnings. Is the company’s net profit backed by actual cash coming in?
Price to Earnings (P/E) Ratio: The classic valuation metric.
EV/EBITDA: Another popular valuation metric that incorporates debt.
Free Cash Flow (FCF) Estimate: The cash left over after the company has maintained its assets. This is the lifeblood of a company.
The most interesting aspect about this screener template is its usability.
In plain English, this helps me answer the question: “What really drives this company’s stock price?”
When I use the stock price as your dependent variable Y (the “result” I am trying to explain), I now can choose independent variables (the “causes”) from the wealth of data in the Snapshot Tab over 8 year period, such as:
ROE
ROCE
Sales Growth
Profit Growth
EPS Growth
By running a simple regression, Excel will spit out a statistic called R-Square. This tells me what percentage of the stock price’s movement over the years can be explained by the financial metrics I selected.
For example, an R-Square of 65% would mean that 65% of the historical ups and downs in the stock price are correlated with the metrics I tested.
In a nutshell:
The AARD Screener is a deep dive into a decade of financial data, but can be overwhelming.
The Dashboard is a quick, high-level health check visual representation.
The Snapshot is a financial detective to discover what factors have historically moved the stock price.
I have also tried to inculcate WACC calculation in the template and also added a simple DCF calculator.
Disclaimer
All information is sourced from publicly available data, and while every effort has been made to ensure the accuracy and reliability of the information provided in these notes from the management meeting, Ayush Agarwal Research cannot guarantee that the information is complete or free from errors.
I, Ayush Agrawal, am registered with SEBI as an Individual Research Analyst under the registration number INH000013013, effective from September 14, 2023.
I do not offer any paid research services.
Opinions expressed regarding specific securities in the AARD Research Room are not investment advice and shall not be treated as recommendations.
Neither I nor my associates/ employees shall be liable for any losses incurred based on such opinions.
All matter displayed in this content is purely for Illustrative, Knowledge and Informational purposes and shall not be treated as advice or opinion of any kind.
The content presented should not be construed as investment advice.
I nor my employees/associates shall be held liable/responsible in any manner whatsoever for any losses the readers may incur due to acting upon this content.
All information is taken from publicly available sources and data. I make no warranties or guarantees regarding the accuracy, completeness, or timeliness of the information provided, including data such as news, prices, and analysis.
We cannot guarantee the completeness or reliability of the information presented. Readers are encouraged to conduct their own research and consult with a professional advisor before making any investment decisions.
In no event shall I be liable to any person for any decision made or action taken in reliance upon the information provided by me. I, Ayush Agrawal, expressly disclaim liability for any direct or consequential losses from reliance on this content.
Registration granted by SEBI and certification from NISM in no way guarantee performance of the intermediary or provide any assurance of returns to investors
Investment in securities market are subject to market risks. Read all the related documents carefully before investing.
Compliance officer: Ayush Agrawal
Telephone number: +91 9425412028.
E-mail address: themicrocapinvestor@gmail.com
Contact Details
themicrocapinvestor@gmail.com
First Floor, B.P. Complex, Baldeobagh, Jabalpur, Madhya Pradesh, 482002
+91 9425412028
SEBI Research Analyst No. : INH000013013
BSE Enlistment No. : 5849