Copy and customize this prompt when requesting a financial model:
I need you to create a comprehensive, integrated 3-statement financial model
for [COMPANY NAME] using the Integrated Financial Modeling framework.
COMPANY DETAILS:
- Company Name: [Full Legal Name]
- Ticker: [BSE/NSE Symbol]
- ISIN: INE[CODE]
- Sector: [Industry/Sector]
- Current Price: ₹[X] on [Date]
MODEL SPECIFICATIONS:
✓ 13-sheet Excel workbook following JK Paper template structure
✓ Historical: FY[XX]A to FY[XX]A (typically FY19A-FY24A, 6 years)
✓ Projections: FY[XX]E to FY[XX]E (typically FY25E-FY27E, 3 years)
✓ All figures in ₹ Crores
✓ Ind-AS compliant
✓ Fully integrated (P&L → BS → CFS)
REQUIRED SHEETS:
1. Introduction - Company overview & key metrics
2. Cons P&L - Consolidated Profit & Loss
3. Cons BS - Consolidated Balance Sheet
4. Cons CFS - Consolidated Cash Flow Statement
5. Reconciliation - Cash & Bank verification
6. Ratio Analysis - Comprehensive financial ratios
7. Common Size P&L - Vertical analysis
8. Common Size BS - Vertical analysis
9. Revenue Model - Revenue build-up with drivers
10. Quarterly Data - Last 3 quarters actual results
11. Capex Model - Fixed asset & depreciation schedule
12. DCF Valuation - ⭐ ENHANCED: 3 methods (Dividend, FCFE, FCFF) + 2 sensitivity tables
13. Sheet2 - Scenarios/backup calculations
IMPORTANT - Sheet 12 DCF Requirements:
✓ Method 1: Gordon Growth Model (Dividend Discount)
✓ Method 2: FCFE (Free Cash Flow to Equity)
✓ Method 3: FCFF (Free Cash Flow to Firm) with WACC
✓ Sensitivity Table 1: FCFF (WACC vs. g)
✓ Sensitivity Table 2: FCFE (Ke vs. g)
✓ Valuation Triangulation: Average of all 3 methods
✓ Final Target Price & Recommendation
All DCF calculations in ONE sheet (Sheet 12) - no separate sensitivity sheet needed.
DATA PROVIDED:
[Attach annual reports or paste extracted data]
PROJECTION ASSUMPTIONS TO USE:
Revenue Growth:
- FY[XX]E: [X]% (rationale: [specify])
- FY[XX]E: [X]%
- FY[XX]E: [X]%
Margins:
- COGS as % Revenue: [X]%
- EBITDA Margin: [X]%
- Tax Rate: 25.17%
Working Capital:
- Inventory Days: [X]
- Debtor Days: [X]
- Creditor Days: [X]
Capex & Depreciation:
- Annual Capex: ₹[X] Cr or [Y]% of revenue
- Depreciation Rate: [X]% of Gross Block
Financing:
- Dividend Payout: [X]%
- Interest Rate: [X]%
- Debt Strategy: [Maintain/Reduce/Increase]
VALUATION INPUTS:
- Risk-Free Rate (Rf): [X]% (10Y G-Sec)
- Beta: [X]
- Risk Premium (Rp): 7.26%
- Terminal Growth: Choose conservative from (ROE×Retention, GDP, Hist CAGR)
DELIVERABLES REQUESTED:
□ Excel file with all 13 sheets
□ All formulas working (no hardcoded projections)
□ Balance sheet balancing
□ Cash flow reconciling
□ DCF valuation with sensitivity analysis
□ Quality check passed
Please confirm understanding and begin with Introduction, Cons P&L, and Cons BS sheets.
STEP 1: SETUP INTRODUCTION & EXTRACT HISTORICAL DATA
Company: [NAME]
Task: Create Introduction sheet and extract historical data (FY[XX]-FY[XX]) from
uploaded annual reports.
Extract and populate:
- P&L: Revenue, COGS, EBITDA, Depreciation, Interest, Tax, PAT (all years)
- BS: Fixed Assets, Working Capital, Debt, Equity (all years)
- CFS: Operating, Investing, Financing cash flows (all years)
Verify:
✓ All figures in ₹ Crores
✓ Data matches annual reports
✓ Balance sheet balances for all historical years
✓ Cash flow reconciles for all years
Once complete, show summary table of historical performance.
STEP 2: BUILD INTEGRATED FINANCIAL STATEMENTS
Using historical data, now create projection sheets (FY[XX]E - FY[XX]E):
Cons P&L:
- Apply revenue growth: FY[XX]E = [X]%, FY[XX]E = [X]%, FY[XX]E = [X]%
- COGS as % of revenue: [X]%
- SG&A with operating leverage
- Other Income: [X]% of investments
- Depreciation: Link to Capex Model
- Tax: 25.17% of PBT
Cons BS:
- PPE: Link to Capex Model
- Working Capital: [X] days inventory, [X] days debtors, [X] days creditors
- Investments: Grow at [X]% or maintain
- Debt: [Strategy]
- Retained Earnings: Rolling forward with PAT - Dividends
- Cash: Balancing figure
Cons CFS:
- Operating CF: Start with PBT, adjust for D&A, working capital
- Investing CF: Capex, investments
- Financing CF: Debt movements, interest, dividends
- Must reconcile: Opening + Net Change = Closing (equals BS cash)
Critical Check: Balance Sheet MUST balance for all years.
STEP 3: BUILD SUPPORTING SCHEDULES
Reconciliation Sheet:
- Formula: Total Liabilities - All Assets except Cash = Cash
- Verify zero difference
Ratio Analysis:
- Calculate all ratios using formulas (not hardcoded)
- Liquidity, Activity, Leverage, Profitability, Valuation ratios
- Show trends across all years
Common Size Statements:
- P&L: Each item as % of Net Revenue
- BS: Each item as % of Total Assets
- Link formulas to base statements
Revenue Model:
- If volume data available: Volume × Realization
- Show quarterly breakdown
- Document assumptions
Quarterly Data:
- Input last 3 quarters actual results
- Format matching annual P&L
Capex Model:
- Opening Gross Block + Capex - Disposals = Closing Gross Block
- Track Accumulated Depreciation
- Net Block = Gross - Accumulated
- Link to BS and P&L
STEP 4: ENHANCED DCF VALUATION - All 3 Methods in Sheet 12
Build comprehensive DCF sheet with three independent valuation methods:
═══════════════════════════════════════════════════════════════
METHOD 1: GORDON GROWTH MODEL (DIVIDEND DISCOUNT)
═══════════════════════════════════════════════════════════════
CAPM - Cost of Equity:
- Rf = [X]% (10Y G-Sec yield on [date])
- Beta = [X] (from Moneycontrol/Screener)
- Rp = 7.26% (India equity risk premium - Damodaran)
- Ke = Rf + Beta × Rp = [Y]%
Terminal Growth Rates:
- g(f) = ROE × Retention Ratio (from FY25E ratios) = [X]%
- g(h) = Dividend CAGR (FY19A-FY27E) = [X]%
- g(GDP) = 6.5% (India long-term GDP growth)
- Selected g = [Choose most conservative] %
- ✓ VALIDATE: Ke > g? [MUST BE TRUE]
Dividend Valuation:
- FY25E Dividend = ₹[X] Cr (from Ratio Analysis)
- FY26E Dividend = ₹[X] Cr
- FY27E Dividend = ₹[X] Cr
- Terminal Value = FY27E Div × (1+g) / (Ke-g)
- Discount at Ke: PV = CF / (1+Ke)^year
- Value of Equity = Sum of all PVs
- Target Price = Value / Shares Outstanding
═══════════════════════════════════════════════════════════════
METHOD 2: FCFE (FREE CASH FLOW TO EQUITY)
═══════════════════════════════════════════════════════════════
FCFE Calculation (for FY25E, FY26E, FY27E):
- PAT (from P&L)
- + Depreciation (from P&L)
- - Capex (from Capex Model)
- - Change in NWC (from Balance Sheet)
- + Net Borrowing (debt increase from BS)
= FCFE
Terminal FCFE = FY27E FCFE × (1+g) / (Ke-g)
Discount at Ke:
- PV of FY25E FCFE = FCFE / (1+Ke)^1
- PV of FY26E FCFE = FCFE / (1+Ke)^2
- PV of FY27E FCFE = FCFE / (1+Ke)^3
- PV of Terminal = Terminal FCFE / (1+Ke)^3
Value of Equity = Sum of all PVs
Target Price = Value / Shares Outstanding
═══════════════════════════════════════════════════════════════
METHOD 3: FCFF (FREE CASH FLOW TO FIRM)
═══════════════════════════════════════════════════════════════
WACC Calculation:
Cost of Equity (Ke) = [from Method 1] = [Y]%
Cost of Debt:
- Interest Expense FY25A = ₹[X] Cr (from P&L)
- Total Debt FY25A = ₹[X] Cr (from BS)
- Pre-tax Kd = Interest / Debt = [Y]%
- After-tax Kd = Kd × (1 - 0.2517) = [Z]%
Capital Structure (Market Values):
- Equity (Book) = ₹[X] Cr (from BS)
- Debt = ₹[X] Cr (from BS)
- Total Capital = [X] Cr
- We = Equity / Total = [Y]%
- Wd = Debt / Total = [Z]%
WACC = (We × Ke) + (Wd × After-tax Kd) = [Y]%
✓ VALIDATE: WACC > g? [MUST BE TRUE]
FCFF Calculation (for FY25E, FY26E, FY27E):
- EBIT (from P&L)
- - Tax on EBIT (EBIT × 0.2517)
= NOPAT
- + Depreciation (from P&L)
- - Capex (from Capex Model)
- - Change in NWC (from Balance Sheet)
= FCFF
Terminal FCFF = FY27E FCFF × (1+g) / (WACC-g)
Discount at WACC:
- PV of FY25E FCFF = FCFF / (1+WACC)^1
- PV of FY26E FCFF = FCFF / (1+WACC)^2
- PV of FY27E FCFF = FCFF / (1+WACC)^3
- PV of Terminal = Terminal FCFF / (1+WACC)^3
Enterprise Value = Sum of all PVs
- Net Debt (FY25A) = Debt - Cash
= Equity Value
Target Price = Equity Value / Shares Outstanding
═══════════════════════════════════════════════════════════════
SENSITIVITY ANALYSIS - 2 TABLES IN SHEET 12
═══════════════════════════════════════════════════════════════
Table 1: FCFF Valuation Sensitivity
Rows: WACC varies from 9% to 15% (in 1% increments)
Columns: g varies from 2% to 6.5%
Cells: Show Target Price under each scenario
Mark base case with your WACC and g values
Table 2: FCFE Valuation Sensitivity
Rows: Ke varies from 10% to 16% (in 1% increments)
Columns: g varies from 2% to 6.5%
Cells: Show Target Price under each scenario
Mark base case with your Ke and g values
═══════════════════════════════════════════════════════════════
VALUATION SUMMARY (Triangulation)
═══════════════════════════════════════════════════════════════
Method | Target Price | Upside/(Downside) | Recommendation
─────────────────────────────────────────────────────────────────────────
1. Dividend Discount | ₹[X] | [Y]% | [BUY/HOLD/SELL]
2. FCFE | ₹[X] | [Y]% | [BUY/HOLD/SELL]
3. FCFF | ₹[X] | [Y]% | [BUY/HOLD/SELL]
─────────────────────────────────────────────────────────────────────────
Average Target Price | ₹[X] | [Y]% |
Current Price (Date) | ₹[X] | |
─────────────────────────────────────────────────────────────────────────
FINAL RECOMMENDATION | [BUY/HOLD/SELL based on average]
All calculations with complete traceability to P&L, BS, CFS, Capex Model.