Avenue Supermarts (DMart) — Equity Research Report & Financial Valuation Model.

Project Summary:

This project is a full-scale financial model and equity research report on Avenue Supermarts Limited (DMart), India's leading value-retail supermarket chain. Built from the company's historical financial statements (FY2020–FY2026), the model consolidates raw financial data into a complete three-statement framework — Income Statement, Balance Sheet, and Cash Flow Statement — supported by detailed schedules for depreciation, working capital, and revenue drivers (store count, retail area, and same-store growth).

The report layers in ratio analysis (profitability, liquidity, turnover, and returns), common-size statements to track margin and cost structure trends over time, and a DCF valuation to arrive at an intrinsic target price, benchmarked against the company's actual traded price. The end deliverable is structured as a formal equity research report — front page, table of contents, one-page company profile, and supporting exhibits — suitable for presentation to an investment audience.

Skills Showcased:

• 📊 Financial statement modeling: Building an integrated 3-statement model (P&L, Balance Sheet, Cash Flow) with historical actuals flowing through supporting schedules.

• 🏪 Revenue driver analysis: Decomposing top-line growth into operational drivers (store count, retail area expansion, revenue per sq. ft., like-for-like growth).

• 🔄 Working capital & fixed asset schedules: Building out inventory, receivables, payables, and depreciation roll-forwards.

• 📈 Ratio & margin analysis: Profitability, liquidity, turnover, and return ratios across a 7-year horizon.

• 📐 Common-size statement analysis: Standardizing the income statement and balance sheet to identify structural trends.

• 💰 DCF valuation: Deriving Free Cash Flow to Firm, discounting to enterprise value, and backing into a target share price.

• 🧹 Data cleaning & transformation: Parsing raw market data (dates, volumes, fiscal periods) into structured, formula-driven tables.

• 🧮 Excel financial modeling best practices: Structured tables, formula auditing, and a presentation-ready report layout (cover page, index, exhibits).

• 🧮 Advanced Excel skills: Financial formulas, data analysis, PivotTables, structured tables, data validation, dynamic calculations, charting, formula auditing, and presentation-ready reporting.

• 🐍 Python: • Eliminated manual CAPM input updates by automating share price, beta, and market return calculations with Python (xlwings, pandas) — keeping the model's cost-of-equity current with live market data on demand.

A detailed summary of the Model:

1) Company Snapshot — Avenue Supermarts Limited: A One-Page Financial Profile

Summary:

A single-page company overview introducing DMart's value-retail business model and EDLC/EDLP philosophy, anchored by a 7-year key metrics table (FY20–FY26) and three supporting charts — average share price history, traded volume, and shareholder composition.

Key Highlights:

• Revenue grew from ₹24,870 Cr (FY20) to ₹68,821 Cr (FY26), with FY26 growth of 15.9% YoY.
• EPS more than doubled, from ₹20.71 (FY20) to ₹45.65 (FY26).
• Gross profit margin stayed remarkably stable in a tight 14.8–15.1% band across all 7 years — a sign of consistent pricing discipline.
• Promoters hold 74.5% of the shareholding; institutional holders (Mutual Funds + FPIs + Pension Funds) together own ~17%.
• Average annual share price rose from ₹2,281 (2020) to ₹4,042 (2026).

2) Operating & Financial Summary — Six-Year Trend Dashboard (FY21–FY26)

Summary:

A visual dashboard of 8 charts tracking revenue, profitability, cash flow, operating efficiency, and leverage trends over FY21–FY26, backed by a documented list of data sources (DMart IR site, Screener, Yahoo Finance, Investing.com).

Key Highlights:

• Revenue climbed every year, from ₹24,143 Cr (FY21) to ₹68,821 Cr (FY26).
• Retail area more than doubled, from 8.8 mn sq. ft. to 20.6 mn sq. ft.
• Net profit grew from ₹1,099 Cr to ₹2,971 Cr, but net margin actually slipped from 4.55% to 4.32% — profit growth trailing revenue growth.
• The same behaviour as Net profit can be observed with EBIT and the EBIT margin, where the EBIT grows while the EBIT margin peaked at 7% (FY23) and steadily declined to 6% (FY26).
• Operating cash flow hit a period-high of ₹3,467 Cr in FY26.

3) Common-Size Statements — Structural Trend Analysis (FY20–FY26)

Summary:

A restatement of the income statement and balance sheet as percentages of revenue and total assets respectively, isolating genuine structural shifts in cost and capital structure from pure scale growth.

Key Highlights:

• COGS held stable at ~84.9–85.2% of revenue for 7 straight years.
• Employee costs crept up from 1.83% of revenue (FY20) to 2.24% (FY26) — wage costs outpacing revenue slightly.
• PP&E's share of total assets rose steadily from 42.3% (FY20) to 59.6% (FY26) — the business is getting more capital-intensive as the store network scales.
• Equity funding's share of the balance sheet fell from 91.8% (FY20) to 82.9% (FY26), however this needs to be studied further as Avenue Supermarts Limited does not have much in the form of debt and the equity in this company is mostly in the form of retained eranings. Another point to consider is that the company has not paid dividend since its IPO issue. Now, liabilities' share roughly doubled (8.2% → 17.1%).

4) Ratio Analysis — Profitability, Liquidity, Solvency & Valuation (FY20–FY26)

Summary:

A comprehensive ratio suite — profitability, ROI, liquidity, turnover, activity, solvency, valuation, DuPont decomposition, and cash-flow coverage — tracked across 7 years to assess operating efficiency and financial health.

Key Highlights:

• Current ratio nearly halved, from 3.23x (FY20) to 1.98x (FY26) — still healthy, but the liquidity buffer has thinned considerably.
• Interest coverage dropped sharply to 29.2x in FY26 from 52.1x in FY25, driven by a jump in finance costs.
• Return on Equity peaked around 16% in FY23 and has since eased to 12.9% (FY26).
• P/E ratio compressed from 107x (FY20) to 89.3x (FY26), while the EPS raised from ₹36.72 (FY23) to ₹45.65 (FY26), indicating a continuous growth in the earnings.
• Debt-to-equity ratio doubled in FY26 (7.1%) versus FY25 (3.5%).

5) Income Statement — Historical Actuals (FY20–FY26) & 10-Year Forecast (FY27–FY36)

Summary:

Full income statement from revenue down to diluted EPS — reported historicals for FY20–FY26, plus a 10-year projected P&L (FY27–FY36) built on assumed margin and growth drivers, which feeds directly into the DCF valuation.

Key Highlights:

• Revenue is projected to grow from ₹68,821 Cr (FY26) to ~₹4,85,112 Cr by FY36 — an implied ~21.6% forward CAGR.
• EBITDA margin is modeled to expand to a stable 8.5% from FY27 onward, above the FY26 actual of 7.5%.
• Diluted EPS is projected to grow nearly 10x over the forecast horizon — from ₹45.63 (FY26) to ₹448.37 (FY36).
• Historically, net profit margin has compressed steadily since its FY23 peak of 5.55% down to 4.32% (FY26).

6) Balance Sheet — Historical Trend (FY20–FY26)

Summary:

Full consolidated balance sheet build — assets, liabilities, and equity — with an explicit "Balanced" check confirming the statement ties out every year, tracking how the balance sheet has scaled alongside store expansion.

Key Highlights:

• Total assets grew ~2.4x, from ₹12,076 Cr (FY20) to ₹29,524 Cr (FY26).
• PP&E alone grew over 3.4x, from ₹5,107 Cr to ₹17,587 Cr.
• FY26 saw the first meaningful short-term borrowings (₹965 Cr) after years of essentially zero — a notable financing shift.
• Combined trade payables roughly tripled, from ₹433 Cr (FY20) to ₹1,338 Cr (FY26).

7) Cash Flow Statement — Operating, Investing & Financing Activity (FY20–FY26)

Summary:

Indirect-method cash flow reconciliation from Profit Before Tax down to the closing cash balance, broken into operating, investing, and financing activities across all 7 years.

Key Highlights:

• Net cash from operations hit a 7-year high of ₹3,467 Cr in FY26, up 41% from FY25's ₹2,463 Cr.
• Capex has risen every year, reaching ₹4,113 Cr in FY26 — the highest in the dataset, funding continued store rollout.
• FY26 was the first year with a net cash inflow from financing activities (+₹288 Cr), reversing years of net outflows — driven by fresh short-term borrowings and commercial paper.
• Closing cash and cash equivalents turned negative (–₹96.6 Cr, i.e. bank overdrawn) in FY26, a departure from healthy positive balances in prior years.

8) DCF Valuation — Intrinsic Value & Target Price

Summary:

A discounted cash flow model projecting Free Cash Flow to Firm across a 10-year explicit forecast (FY27–FY36) plus a terminal value, discounted at WACC to derive Enterprise Value, Equity Value, and an implied per-share target price — supported by a WACC/terminal-growth sensitivity table. Key CAPM inputs — beta, current share price, and market rate of return — are pulled live via a Python (xlwings, pandas) automation, keeping the valuation's cost-of-equity assumptions current with market data rather than static manual entries.

Key Highlights:

• WACC calculation, built from a CAPM cost of equity (risk-free rate 6.97%, beta — computed via automated 6-year regression of DMart vs. Nifty 50 daily returns — and market return — derived from 6 years of Nifty 50 closing price data) and cost of debt of 7.75%.**
• FCFF is negative in FY26 (–₹755 Cr) due to heavy capex, turning strongly positive from FY27 onward.
• The terminal value (~₹3,96,295 Cr) dominates the valuation — nearly all of the ~₹2,05,944 Cr enterprise value comes from cash flows beyond the 10-year explicit window.
• Implied target price of ₹4,650.14 sits above the live-pulled current share price of ₹3,770 — the DCF implies the stock is trading below its modeled intrinsic value.
• The sensitivity table shows the target price swings meaningfully across WACC and terminal-growth scenarios (from "very low" to "very high").