Objectives

Session Learning Objectives

5.1

Why Forecasting Matters

"A model is the formalization of opinions. As you build the model, and incorporate the latest reported results, your opinion of the company's value will change."
— John Moschella, CFA, CPA, Financial Modeling for Equity Research
📖 Key Concept: The Forecasting Foundation

Forecasting is the foundation of financial modeling. It involves predicting future financial performance based on historical data, market trends, and reasonable assumptions. The quality of your forecast directly impacts the quality of your valuation and decision-making. In equity research, the forecast is the bridge between historical analysis and forward-looking valuation.

⚖️ Key Concept: The Principle of Balance and Drive

Every financial model follows the Principle of Balance and Drive:

Balance: The three financial statements must always balance — the Balance Sheet must balance (Assets = Liabilities + Equity), and the Cash Flow Statement must reconcile to the cash on the Balance Sheet.

Drive: Every forecast cell should be driven by an equation (a formula based on assumptions), not hard-coded values. This is what makes a model a "working model." When you change an assumption, all connected calculations update automatically.

📊 The Forecasting Framework

  • Historical Analysis: Understand past trends and patterns — this is your starting point
  • Driver Identification: Find key variables that drive performance (e.g., volume, price, headcount)
  • Assumption Development: Create reasonable, supportable assumptions based on evidence
  • Projection: Build forward-looking financial statements using equations linked to assumptions
  • Calibration: Adjust forecasts for seasonality, management guidance, and changing circumstances

🔑 The "Blue Cell" Convention

In financial modeling, assumptions (the inputs you choose) are entered in blue font. All other cells contain equations (formulas that calculate automatically). This convention allows anyone reading your model to immediately identify which cells reflect your opinions (assumptions) versus which cells are mechanical calculations.

Blue font = Your assumption (input)
Black font = Equation (calculated)
5.2

Top-Down vs. Bottom-Up Forecasting

📈 Top-Down Forecasting

Start with market size and work down to company revenue

Revenue = TAM × Market Share %

TAM = Total Addressable Market
Steps:
1. Estimate Total Addressable Market (TAM) size
2. Determine company's market share
3. Calculate revenue from market share
Best For:
  • New products/markets
  • High-growth companies
  • Limited historical data
  • Startups and early-stage companies

📊 Bottom-Up Forecasting

Start with individual drivers and build up to revenue

Revenue = Volume × Price

OR

Revenue = Customers × Average Revenue Per User (ARPU)
Steps:
1. Identify key revenue drivers
2. Forecast each driver separately
3. Multiply drivers to get revenue
Best For:
  • Established businesses
  • Detailed operational data available
  • Multiple product lines or segments
  • Equity research models
💡 Best Practice: Use Both Approaches

Use both approaches and reconcile!
Top-down provides a sanity check — "Can this company really capture X% of the market?"
Bottom-up provides detail — "What specific drivers will get us there?"

In equity research, the bottom-up approach is preferred because it ties the forecast to observable operational metrics. The book Financial Modeling for Equity Research demonstrates this using a segment-based approach — breaking revenue down by business segment, then by sub-segment, and then by driver (volume × price).

🏗️ The "Earnings Engine" Concept

In professional equity research, the revenue forecast is often called the "Earnings Engine". Rather than forecasting total revenue as one number, you break it down by business segments and sub-segments, each with its own drivers. This is the approach used in the FedEx model from Financial Modeling for Equity Research:

Total Revenue = Segment 1 Revenue + Segment 2 Revenue + ... + Segment N Revenue

Segment Revenue = Sub-segment 1 + Sub-segment 2 + ... + Sub-segment M

Sub-segment Revenue = Average Daily Volume (ADV) × Operating Days × Yield ÷ 1,000

Example: FedEx Express Segment → U.S. Overnight Box Sub-segment → Revenue = ADV × Days × Yield

5.3

Driver-Based Forecasting

🎯 Core Idea

Driver-based forecasting means linking revenue to operational metrics that can be independently measured and forecast. Instead of saying "revenue will grow 15%," you say "volume will grow 5% and prices will increase 3%, so revenue will grow approximately 8.15%." This approach is more defensible and allows for sensitivity analysis.

🎯 Common Revenue Drivers by Industry

Industry Primary Driver Secondary Driver Revenue Formula
Shipping & Logistics Average Daily Volume (ADV) Yield (Revenue per package) ADV × Days × Yield
IT Services Headcount Revenue per Employee Headcount × Rev/Employee
SaaS / Software Subscribers Average Revenue Per User (ARPU) Subscribers × ARPU
Retail Store Count Sales per Store Stores × Sales/Store
Manufacturing Production Capacity Utilization Rate Capacity × Util% × Price
Airlines Available Seat Miles (ASM) Load Factor ASM × Load% × Yield
Banking Interest-Earning Assets Net Interest Margin (NIM) Assets × NIM
Telecom Subscriber Count Average Revenue Per User (ARPU) Subscribers × ARPU

📦 Deep Dive: Shipping & Logistics — The ADV × Yield Method

This is the method used in Financial Modeling for Equity Research for the FedEx model. It demonstrates how professional equity research analysts break down revenue into granular drivers.

Sub-segment Revenue Formula:
Revenue = Average Daily Volume (ADV) × Operating Days × Yield ÷ 1,000

Where:
• ADV = Average number of packages shipped per day in the quarter
• Operating Days = Number of operating days in the quarter (typically ~63-64 for a quarter)
• Yield = Revenue per package (in $ per package, ÷ 1,000 for unit conversion)

Forecast Approach: Forecast year-over-year (YoY) growth rates for ADV and Yield separately
📊 Example: Express Segment — U.S. Overnight Box Sub-segment
Metric F1Q18
(Historical)		 F2Q18
(Historical)		 F3Q18
(Historical)		 F4Q18
(Historical)		 F1Q19E
(Forecast)
ADV (thousands) 583 621 698 641 580
ADV YoY Growth -2.8% -1.1% 1.5% 2.2% -1.0%
Operating Days 64 64 63 64 64
Yield ($ per package) $24.18 $24.87 $25.32 $25.88 $25.80
Yield YoY Growth 3.5% 4.2% 3.8% 4.5% 3.5%
Revenue ($M) 902 990 1,114 1,061 958
Revenue Calculation (F1Q19E):
= 580 × 64 × 25.80 ÷ 1,000 = $958M

Excel Formula: =ADV_cell * Days_cell * Yield_cell / 1000

📡 Where to Find Driver Data (Indian Context)

Getting the right data is critical for driver-based forecasting. Here's where to find operational data for Indian listed companies:

📄 BSE / NSE Filings & Annual Reports

The primary source for Indian public company data. Download Annual Reports, Quarterly Results, and Shareholding Patterns from BSE India (bseindia.com) or NSE India (nseindia.com). Look at the "Management Discussion & Analysis" (MD&A) section and segment-wise revenue breakup in the Notes to Accounts.

🎙️ Quarterly Earnings Call Transcripts

Indian companies like TCS, Infosys, Reliance Industries hold quarterly concalls. Transcripts are available on the company's Investor Relations page, Trendingly, Moneycontrol, and Seeking Alpha (for ADRs). Listen for management commentary on headcount, utilization rates, and volume guidance.

📊 Company Investor Presentations

Most large Indian companies publish quarterly investor presentations and fact sheets on their IR websites. For example, TCS discloses headcount, utilization, and deal pipeline; IndiGo reports Available Seat Kilometers (ASK) and load factor; Reliance Jio reports subscriber additions and ARPU.

📰 Industry Reports & Rating Agencies

Indian industry data from NASSCOM (IT sector), CRISIL, ICRA, CARE Ratings, and India Brand Equity Foundation (IBEF). Sector-specific: SIAM & FADA (auto), CREDAI (real estate), ICRA (logistics), FICCI reports across sectors.

🏢 Regulatory & Government Data Sources

RBI (rbi.org.in) — banking data (loan growth, NIM, NPA trends); TRAI (trai.gov.in) — telecom subscriber data & ARPU; SEBI (sebi.gov.in) — listed company filings; MCA (mca.gov.in) — company financials (MCA21 portal); CSO/MoSPI — GDP, IIP, inflation data.

💻 Indian Data Platforms & Screeners

Screener.in — free financial data with custom queries; Trendingly — concall transcripts and quarterly data; ACE Equity / Prowess (CMIE) — professional-grade Indian company data; Moneycontrol — financials, ratios, news; Bloomberg / Capital IQ — for companies with global coverage.

🇮🇳 Quick Reference: Indian Data Sources by Sector

IT Services (TCS, Infosys, Wipro): Company IR pages (headcount, utilization), NASSCOM reports (industry growth), BSE/NSE filings (segment revenue)
Banking (HDFC Bank, ICICI, SBI): RBI quarterly bulletins (sector loan growth), Company quarterly results (NIM, NPA, loan book), Care/ICRA sector reports
Telecom (Airtel, Jio, Vodafone Idea): TRAI quarterly reports (subscriber data, ARPU), Company investor presentations
Airlines (IndiGo, SpiceJet): DGCA data (passenger numbers, load factor), Company filings (ASK/ASM, yield)
Automobile (Maruti, Tata Motors, M&M): SIAM monthly sales data, FADA retail sales data, Company monthly sales announcements
FMCG (HUL, ITC, Britannia): Nielsen/IMRB market share data, Company quarterly volume growth disclosures, ACNielsen sector reports
Real Estate (DLF, Godrej Properties): RERA registrations, CREDAI reports, Company quarterly pre-sales/booking data
Pharma (Sun Pharma, Dr. Reddy's, Cipla): US FDA approvals data, NPPA pricing notifications, Company US/India segment filings

🔄 What If Detailed Driver Data Is Not Available?

Not all companies disclose granular data like ADV. Here's a hierarchy of approaches, from most detailed to least detailed:

Level Data Available Forecast Approach Example
Level 1
(Best)		 Full driver data (volume, price, days) Forecast each driver separately
Revenue = Volume × Price × Days		 FedEx: ADV × Days × Yield
Level 2 Segment revenue + some operational metrics YoY growth rate on segment revenue
+ driver adjustments		 IT Services: Headcount growth × Rev/Employee growth
Level 3 Segment revenue only YoY growth rate based on historical trends
+ management guidance		 Conglomerates with minimal segment disclosure
Level 4
(Fallback)		 Total revenue only Historical CAGR + adjustments
+ analyst consensus		 Private companies or companies with limited disclosure
⚠️ Important Note

When detailed driver data is not available from public filings, you can:
• Use industry reports to estimate company-level volume and pricing
• Back-calculate drivers from total revenue (e.g., if you know revenue and headcount, you can derive revenue per employee)
• Use peer company data to estimate drivers for a company that doesn't disclose them
• Ask management during earnings calls or investor days for specific metrics
• Apply year-over-year growth rates directly to revenue as a simplified alternative

📋 Example: IT Services Company Revenue Forecast (Level 2 Approach)

Metric 2025A 2026E 2027E 2028E
Headcount 600,000 660,000 726,000 798,600
Headcount Growth - 10.0% 10.0% 10.0%
Revenue per Employee (₹L) 48.2 50.6 53.1 55.8
Rate Growth - 5.0% 5.0% 5.0%
Revenue (₹ Cr) 289,456 334,236 385,610 444,821
Implied Growth - 15.5% 15.4% 15.4%
Excel Formula:
Revenue = Headcount × Revenue_per_Employee

Example 2026E: =660,000 × 50.6 = 334,236
5.4

Seasonality & Model Calibration

📖 Key Concept

Seasonality refers to regular, predictable patterns that repeat over a year. Many businesses have seasonal revenue patterns that must be captured in quarterly or monthly forecasts. The book Financial Modeling for Equity Research uses year-over-year (YoY) growth rates specifically because they automatically capture seasonality — each forecast quarter is compared to the same quarter of the previous year.

📊 Example: Seasonal Revenue Pattern (Retail / Shipping)

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Retail and shipping peak during the holiday season (Oct-Dec). FedEx experiences its highest Average Daily Volume (ADV) in fiscal Q3 (Nov-Jan).

🔢 Seasonality Index Calculation

Seasonality Index = (Quarterly Revenue / Average Quarterly Revenue) × 100

Index > 100 = Above average quarter
Index < 100 = Below average quarter
Quarter Revenue % of Annual Seasonality Index
Q1 (Apr-Jun) 65,000 22.4% 89.6
Q2 (Jul-Sep) 72,000 24.8% 99.2
Q3 (Oct-Dec) 85,000 29.3% 117.2
Q4 (Jan-Mar) 67,456 23.5% 94.0
Full Year 289,456 100% 100

🎯 The 6-Step Model Calibration Framework

From Financial Modeling for Equity Research, Chapter 4 — the systematic process for calibrating your forecast:

Step 13: Incorporate Historic Trends

Start with historical data before incorporating opinions. Use 4-quarter moving averages for growth rates. This prevents bias in your analysis.

Step 14: Adjust for Seasonality

Year-over-year (YoY) growth rates automatically capture seasonality. Review projected trends vs. past years to confirm seasonal patterns are reasonable.

Step 15: Adjust for Changes in Circumstances

Consider: pricing changes, acquisitions, new products, accounting changes, input price changes, competitive dynamics, and macroeconomic events.

Step 16: Consider Management Guidance

Incorporate company's own forecasts from earnings calls and investor presentations. Management has the most insight into near-term operations.

Step 17: Review Consensus Estimates

Compare your forecast to analyst consensus (Bloomberg, Reuters, FactSet). Significant deviations should be explainable.

Step 18: Incorporate Opinions & Monitor

Form your own view. Update the model as new information becomes available each quarter. A model is a living document.

⚠️ Practitioner Tip: Start with History to Avoid Bias

From the book: "The historic trends should be considered first, prior to incorporating management's guidance, or any other factors. This will prevent us from developing a bias in our analysis. By extending the historic trends into the future, you will force yourself to question deviations from recent history."

Types of changes to look for:
1. "Big bang" events — acquisitions, divestitures that instantly change the earnings profile
2. Gradual structural changes — shifts in sales mix, regional shifts, business model changes
3. Non-recurring items — legal settlements, one-time orders, cyber attacks (e.g., TNT cyber attack on FedEx)

5.5

Growth Rate Estimation

📊 Methods for Estimating Growth Rates

Method Description Formula Best For
Historical Average Average of past growth rates =AVERAGE(growth rates) Stable companies
Moving Average Average of last N quarters =AVERAGE(last 4 quarters) Quarterly forecasting (preferred in equity research)
CAGR Compound Annual Growth Rate =(End/Start)^(1/n) - 1 Multi-year trends
Analyst Consensus Industry analyst estimates From Bloomberg / Reuters Public companies
Management Guidance Company's own forecast From earnings calls All companies
GDP + Premium Economic growth plus premium =GDP + Industry Premium Mature industries

🔢 CAGR Calculation Example

CAGR Formula:
CAGR = (Ending Value / Beginning Value)^(1/n) - 1

Excel: =POWER(End/Start, 1/n) - 1
OR: =(End/Start)^(1/n) - 1
Year Revenue YoY Growth
FY2021 164,177 -
FY2022 191,754 16.8%
FY2023 225,458 17.6%
FY2024 257,289 14.1%
FY2025 289,456 12.5%
4-Year CAGR - 15.2%
CAGR Calculation:
= (289,456 / 164,177)^(1/4) - 1
= 1.763^0.25 - 1
= 1.152 - 1 = 15.2%

📈 The Moving Average Approach (from Equity Research Practice)

The book Financial Modeling for Equity Research recommends using a 4-quarter moving average for growth rate forecasts. This approach:

  • Smooths out quarterly volatility
  • Captures recent trends without over-weighting the latest quarter
  • Can be adjusted as new information becomes available
4-Quarter Moving Average:
F1Q19E Growth = AVERAGE(F1Q18, F2Q18, F3Q18, F4Q18)

Example (ADV YoY Growth for U.S. Overnight Box):
= AVERAGE(-2.8%, -1.1%, 1.5%, 2.2%) = -0.05%

This captures the slightly declining trend while smoothing the quarterly volatility.
💡 Adjusting for Changes in Circumstances

After setting the base moving average, adjust for specific events:
• Pricing changes: If prices increased in recent quarters, use a 2-quarter average for affected segments
• Acquisitions: Don't use pre-acquisition data for comparisons (e.g., FedEx couldn't use pre-TNT data for International segments)
• One-time events: Adjust for non-recurring items like cyber attacks, natural disasters
• Macro changes: Reduce growth estimates if economic conditions are deteriorating

5.6

Expense Forecasting

📖 Key Concept

Expense forecasting typically uses one of two approaches:
• % of Revenue: Variable costs that scale with sales (e.g., Cost of Goods Sold (COGS), fuel)
• Fixed Growth or Balance Sheet-linked: Costs that depend on assets or other factors (e.g., Depreciation and Amortization (D&A) linked to Property, Plant and Equipment (PP&E))

📋 Summary of Expense Forecasting Methods (from the Book)

Adapted from Financial Modeling for Equity Research, Table 3 — Summary of Assumptions and Earnings Per Share (EPS) Calculation:

Line Item Driver (Assumption) Forecast Equation
Revenue Average Daily Volume (ADV), yield, and day count — or YoY growth estimates ADV × Days × Yield ÷ 1,000
Depreciation & Amortization (D&A) Capital Expenditure (CapEx) estimates and depreciation-to-Property & Equipment (P&E) ratio Average PP&E × Depreciation-to-PP&E ratio
Fuel Expense Fuel prices and estimates of fuel usage (in gallons) Fuel price × Estimate of gallons used
All Other Operating Expenses (OpEx) Operating Expense (OpEx)-to-revenue ratio Revenue × OpEx-to-revenue ratio
Interest Expense Interest rates and debt balance forecast Debt balance × Interest expense-to-average debt ratio
Interest Income Interest rates and cash/investment projections Cash equivalents × Interest-to-interest bearing accounts ratio
Other Income Typically immaterial Historical average
Tax Expense Effective tax rate Income before tax × Effective tax rate

📋 Common Expense Categories and Forecasting Methods

Expense Category Type Forecast Method Typical Range
Cost of Goods Sold (COGS) Variable % of Revenue 60-80% (IT: 50-70%)
Employee Costs Semi-Fixed % of Revenue or Headcount × Avg Salary 15-25%
Rent & Utilities Fixed Inflation adjustment 2-5%
Marketing Variable % of Revenue 5-15%
Research & Development (R&D) Semi-Fixed % of Revenue 3-10%
Depreciation & Amortization (D&A) Fixed (BS-linked) % of Gross PP&E 5-10%
Fuel Expense (Transportation) Variable Price per gallon × Gallons used 8-15%

🔬 Advanced: Expense Allocation by Historical Weights

From the book — when companies report a combined "All Other Operating Expenses" line, you need to allocate it to specific Income Statement line items using historical weights:

Step 1: Calculate each expense's weight from the comparable historical quarter:
Weight = Specific Expense / Sum of all allocable expenses

Step 2: Apply weights to the forecast "All Other OpEx":
Allocated Expense = Weight × Total "All Other OpEx"
Example (from FedEx Model):
Expense Line Historical Value ($M) Weight Forecast Allocation
Salaries $5,518 43.4% $5,950
Purchased Transportation $3,445 27.1% $3,715
Rental & Landing Fees $818 6.4% $882
Maintenance $675 5.3% $728
Other Expense $2,270 17.8% $2,448
Total $12,716 100% $13,722
Weight Calculation (Salaries Example):
= $5,518 / ($5,518 + $3,445 + $818 + $675 + $2,270)
= $5,518 / $12,716 = 43.4%

Forecast Allocation (Salaries):
= 43.4% × Total All Other OpEx = 43.4% × $13,722 = $5,950

📊 Example: Complete Revenue & Expense Forecast

Line Item 2025A 2026E 2027E 2028E
Revenue 289,456 334,236 385,610 444,821
Growth Rate 12.5% 15.5% 15.4% 15.4%
COGS (% of Rev) 62.0% 61.5% 61.0% 60.5%
COGS 179,463 205,555 235,222 269,117
Gross Profit 109,993 128,681 150,388 175,704
Gross Margin 38.0% 38.5% 39.0% 39.5%
Selling, General & Administrative (SG&A) (% of Rev) 12.0% 11.5% 11.0% 10.5%
SG&A 34,735 38,437 42,417 46,706
Depreciation & Amortization (D&A) (% of Rev) 5.5% 5.5% 5.5% 5.5%
D&A 15,920 18,383 21,209 24,465
Earnings Before Interest & Taxes (EBIT) 59,338 71,861 86,762 104,533
EBIT Margin 20.5% 21.5% 22.5% 23.5%
Key Excel Formulas:
Revenue = Prior_Revenue × (1 + Growth_Rate)
COGS = Revenue × COGS_Percentage
Gross Profit = Revenue - COGS
EBIT = Gross Profit - SG&A - D&A
EBIT Margin = EBIT / Revenue
Excel Lab

Hands-On: Build a Revenue & Expense Forecast

📥 Download Practice Data

Download the practice data file to follow along with the step-by-step exercises below:

📥 Download: lecture-05-revenue-expense-forecast-data.csv

🎯 Exercise: Build a 3-Year Forecast Model — Step by Step

Follow these steps in order. Open the downloaded CSV file in Excel and use it as your data reference. Build your model in a new Excel sheet.

Step 1: Set Up Your Excel Workbook

  1. Open Excel → New Blank Workbook
  2. Rename Sheet1 to "Model"
  3. Rename Sheet2 to "Data"
  4. Open the downloaded CSV file → Copy all data → Paste into the "Data" sheet
  5. Set up column headers in your "Model" sheet:
Column A Column B Column C Column D Column E Column F Column G
Row 1: Line Item 2023A 2024A 2025A 2026E 2027E

Tip: Use blue font for all assumption cells (inputs you choose) and black font for formula cells (calculated automatically).

Step 2: Create the Assumptions Section (Rows 2-7)

In your Model sheet, create an Assumptions area at the top:

Cell Label (Col A) Value (Col F = 2026E) Font Color
A2 ASSUMPTIONS - Section header
A3 Revenue Growth Rate 12% Blue (assumption)
A4 COGS % of Revenue 62% Blue (assumption)
A5 SG&A % of Revenue 12% Blue (assumption)
A6 D&A % of Revenue 5% Blue (assumption)
A7 Tax Rate 25% Blue (assumption)
✏️ What to do: Type these values in cells F3 through F7. Format them as blue font — these are YOUR assumptions.

Step 3: Enter Historical Revenue (Row 9-10)

From the CSV data sheet, copy the historical revenue values into your Model sheet:

Cell Label 2023A (Col C) 2024A (Col D) 2025A (Col E)
A9 Revenue 191,754 225,458 257,289
A10 Revenue Growth 16.8% 17.6% 14.1%
✏️ Formula for growth (cell D10):
=D9/C9 - 1 → Format as %
Copy this formula to E10. These are black font (equations, not assumptions).

Step 4: Forecast Revenue (Cells F9, G9, H9)

Now use your assumption to forecast future revenue:

✏️ Cell F9 (Revenue 2026E): =E9 * (1 + $F$3)
✏️ Cell G9 (Revenue 2027E): =F9 * (1 + $F$3)
✏️ Cell H9 (Revenue 2028E): =G9 * (1 + $F$3)

Note: The $ signs make F3 an absolute reference — when you copy the formula, it always points to your assumption cell.
Check Your AnswerValue
Revenue 2026E288,164
Revenue 2027E322,744
Revenue 2028E361,473

Step 5: Enter Historical Expenses & Forecast (Rows 12-20)

Enter historical data from the CSV, then build formulas for forecast years:

RowLabel2025A (from CSV)2026E Formula
12 COGS 159,519 =F9 * $F$4
13 Gross Profit 97,770 =F9 - F12
14 Gross Margin 38.0% =F13 / F9
16 SG&A 30,875 =F9 * $F$5
18 D&A 14,151 =F9 * $F$6
20 EBIT 52,744 =F13 - F16 - F18
21 EBIT Margin 20.5% =F20 / F9
✏️ After entering 2026E formulas: Copy cells F12 through F21 and paste into columns G and H for 2027E and 2028E.
The $ signs ensure assumptions stay fixed while revenue references update automatically.

Step 6: Calculate Net Income (Row 23)

✏️ Cell F23 (Net Income 2026E): =F20 * (1 - $F$7)
✏️ Cell G23 (Net Income 2027E): =G20 * (1 - $F$7)
✏️ Cell H23 (Net Income 2028E): =H20 * (1 - $F$7)

Logic: Net Income = EBIT × (1 - Tax Rate)

Step 7: Verify Your Answers (2026E)

Line ItemYour AnswerExpected Answer
Revenue?288,164
COGS (62%)?178,662
Gross Profit?109,503
Gross Margin?38.0%
SG&A (12%)?34,580
D&A (5%)?14,408
EBIT?60,514
EBIT Margin?21.0%
Net Income (25% tax)?45,386

Step 8 (Advanced): Driver-Based Revenue Forecast

Using the Driver_Advanced section from the CSV, build a separate revenue forecast using headcount and revenue per employee:

Step 8a: In a new section below your model, enter the 2025A historical data from CSV:
• Headcount = 600,000 | Revenue per Employee = ₹42.88L

Step 8b: Set up assumptions (blue font):
• Headcount Growth = 10% | Rev/Employee Growth = 5%

Step 8c: Build forecast formulas:
• Headcount 2026E = 600,000 × (1 + 10%) = 660,000
• Rev/Employee 2026E = 42.88 × (1 + 5%) = 45.02
• Driver Revenue 2026E = 660,000 × 45.02 ÷ 100 = 297,132 ₹ Cr

Step 8d: Compare this to your simple growth rate revenue (288,164). Why is it different?
Answer: (1.10 × 1.05) - 1 = 15.5% growth, not 12%. The driver method gives higher revenue because the implied growth from the drivers is higher than your simple 12% assumption.

Step 9 (Bonus): Seasonality Analysis

Using the Seasonality section from the CSV:

Step 9a: Calculate the average quarterly revenue for 2025A:
= 257,289 ÷ 4 = 64,322

Step 9b: Calculate Seasonality Index for each quarter:
• Q1: 56,337 / 64,322 × 100 = 87.6
• Q2: 63,115 / 64,322 × 100 = 98.1
• Q3: 76,456 / 64,322 × 100 = 118.9 (peak!)
• Q4: 61,381 / 64,322 × 100 = 95.4

Step 9c: Apply seasonality to your 2026E forecast:
• Q1 2026E = 288,164 × (56,337/257,289) = 63,108
• Q2 2026E = 288,164 × (63,115/257,289) = 70,684
• Q3 2026E = 288,164 × (76,456/257,289) = 85,649
• Q4 2026E = 288,164 × (61,381/257,289) = 68,723
• Total = 288,164 ✓ (reconciles to annual forecast!)
Summary

Key Takeaways

  • Top-Down starts with market size; Bottom-Up starts with operational drivers. Use both and reconcile.
  • Driver-based forecasting links revenue to operational metrics (ADV × Yield, Headcount × Revenue/Employee)
  • YoY growth rates automatically capture seasonality; use 4-quarter moving averages for smoothing
  • CAGR provides a smoothed long-term growth rate; moving averages are better for quarterly forecasting
  • Expenses use % of Revenue (variable), Balance Sheet-linked ratios (D&A), or historical weights (allocated OpEx)
  • Model calibration follows a 6-step process: historic trends → seasonality → circumstances → guidance → consensus → your opinion
  • Data availability determines your approach — use the most granular drivers available and work down from there
Appendix A

Industry Revenue Forecasting Examples

📦 Shipping & Logistics (e.g., FedEx, UPS, DHL)

Revenue Formula: Average Daily Volume (ADV) × Operating Days × Yield ÷ 1,000
Key Drivers: Volume (packages/day), Yield ($/package), Operating Days
Forecast Method: YoY growth rates for ADV and Yield separately
Data Sources: 10-K/10-Q filings (Business Segment section), Statistical Supplements on Investor Relations (IR) website, Earnings Call transcripts
Segments to model separately: Express (U.S. and International), Ground, Freight
Special considerations: Fuel surcharges pass through to customers; currency impacts on international; seasonality around holidays

💻 IT Services (e.g., TCS, Infosys, Wipro)

Revenue Formula: Headcount × Revenue per Employee
Key Drivers: Employee headcount (by vertical/service), Utilization Rate, Billing Rate
Forecast Method: Forecast headcount growth and rate growth separately
Data Sources: Quarterly earnings releases, Company filings, NASSCOM industry reports
Segments to model: By service line (Application Development, Infrastructure, Consulting), By geography (North America, Europe, Rest of World)
Special considerations: Attrition rates impact headcount, H-1B visa policy changes, Currency fluctuations (USD/INR)

☁️ SaaS / Software (e.g., Salesforce, Adobe, Microsoft Cloud)

Revenue Formula: Subscribers × Average Revenue Per User (ARPU)
Key Drivers: Net new subscribers, Churn rate, ARPU, Expansion revenue (upsells)
Forecast Method: Beginning subscribers + New subscribers - Churned subscribers = Ending subscribers; Revenue = Subscribers × ARPU
Data Sources: Company filings, Investor presentations, Earnings calls, Industry reports (Gartner, IDC)
Key metrics: Annual Recurring Revenue (ARR), Monthly Recurring Revenue (MRR), Customer Acquisition Cost (CAC), Lifetime Value (LTV)
Special considerations: Free-to-paid conversion rates, Enterprise vs. Individual pricing tiers, Platform vs. Service revenue split

🛒 Retail (e.g., Walmart, Target, Reliance Retail)

Revenue Formula: (Store Count × Sales per Store) + E-commerce Revenue
Key Drivers: New store openings, Same-store sales growth (comps), E-commerce growth, Average transaction value
Forecast Method: Forecast store count growth, comps growth, and e-commerce separately
Data Sources: 10-K/10-Q, Company IR pages, Industry reports (Retail Federation), Foot traffic data
Segments to model: By format (Supercenters, Discount stores, Online), By category (Grocery, General merchandise)
Special considerations: Strong seasonality (Q4 holiday quarter), Shift to omnichannel, Pricing strategy vs. competitors

✈️ Airlines (e.g., Delta, United, IndiGo)

Revenue Formula: Available Seat Miles (ASM) × Load Factor × Passenger Yield
Key Drivers: ASM (capacity), Load Factor (occupancy), Yield ($/passenger-mile), Fuel costs
Forecast Method: Forecast capacity growth, load factor trends, and yield separately
Data Sources: Department of Transportation (DOT) filings, IATA industry reports, Company IR pages, Earnings calls
Segments to model: Passenger revenue (by route/region), Cargo revenue, Ancillary revenue
Special considerations: Fuel hedging, Currency exposure on international routes, Seasonality (summer/holiday peaks), Regulatory environment

🏭 Manufacturing (e.g., Tata Steel, Caterpillar, Samsung)

Revenue Formula: Production Capacity × Utilization Rate × Average Selling Price (ASP)
Key Drivers: Installed capacity, Capacity utilization, ASP, Raw material costs
Forecast Method: Forecast capacity additions, utilization trends, and pricing separately
Data Sources: Company filings, Industry associations, Commodity price data (LME, MCX), Government statistics
Segments to model: By product line, By geography
Special considerations: Commodity price cyclicality, Inventory build/draw cycles, Government tariffs and trade policies

🏦 Banking (e.g., JPMorgan, HDFC Bank, ICICI Bank)

Revenue Formula: Interest-Earning Assets × Net Interest Margin (NIM) + Non-Interest Income
Key Drivers: Loan growth, Net Interest Margin (NIM), Fee income, Provisions for loan losses
Forecast Method: Forecast loan growth by segment, NIM based on rate environment, fee income as % of assets
Data Sources: RBI bulletins, Federal Reserve data, Company filings, Earnings calls
Segments to model: Retail banking, Corporate banking, Treasury, Wealth management
Special considerations: Interest rate cycle impact, Credit quality trends, Regulatory capital requirements (Basel III/IV)

📡 Telecom (e.g., Bharti Airtel, Reliance Jio, AT&T)

Revenue Formula: Subscriber Count × Average Revenue Per User (ARPU)
Key Drivers: Net subscriber additions, ARPU, Mix of prepaid vs. postpaid, Data usage per subscriber
Forecast Method: Forecast subscriber additions/churn, ARPU growth from data usage and pricing
Data Sources: Telecom Regulatory Authority (TRAI) reports, Company filings, Industry reports
Segments to model: Mobile services, Fixed line, Enterprise/B2B, Digital services
Special considerations: Spectrum auction costs, Capital-intensive (5G rollout), Regulatory changes, Price wars

📊 Data Availability Hierarchy — Choosing Your Approach

The forecasting approach you choose depends heavily on what data is available. Use the most detailed approach your data supports:

Best → Worst Data Availability:

🟢 Full Driver Data → Use ADV × Yield × Days or equivalent granular formula
🟡 Partial Driver Data → Use YoY growth rates with driver adjustments
🟠 Segment Revenue Only → Use segment-level growth rates + management guidance
🔴 Total Revenue Only → Use CAGR + macro adjustments + analyst consensus

Always try to find more granular data before falling back to simpler approaches.
Appendix B

Abbreviations & Glossary

Abbreviation Full Form Description
ADV Average Daily Volume The average number of units (e.g., packages) shipped per day in a period. Key driver for shipping/logistics revenue.
ARPU Average Revenue Per User Total revenue divided by the number of subscribers/users. Key metric for SaaS, telecom, and subscription businesses.
ASM Available Seat Miles Total seats available multiplied by miles flown. A measure of airline capacity.
ASP Average Selling Price The average price at which a product or service is sold.
BS Balance Sheet Financial statement showing assets, liabilities, and equity at a point in time.
CAGR Compound Annual Growth Rate The annualized average rate of growth over multiple periods. Smoothes out volatility.
CAC Customer Acquisition Cost The cost to acquire one new customer. Important for SaaS and subscription businesses.
CapEx Capital Expenditure Funds used to acquire, upgrade, or maintain physical assets like property and equipment.
COGS Cost of Goods Sold Direct costs attributable to producing goods/services sold by the company.
D&A Depreciation and Amortization Systematic allocation of the cost of tangible (depreciation) and intangible (amortization) assets over their useful lives.
EBIT Earnings Before Interest and Taxes Operating profit — revenue minus operating expenses. A measure of core business profitability.
EPS Earnings Per Share Net income divided by the number of outstanding shares. A key valuation metric.
FASB Financial Accounting Standards Board The organization that establishes accounting standards in the United States.
GAAP Generally Accepted Accounting Principles The standard framework of accounting rules used in the United States.
IR Investor Relations The department responsible for communicating with shareholders and the investment community.
IS Income Statement Financial statement showing revenue, expenses, and profit over a period of time.
LTV Lifetime Value The total revenue expected from a customer over the entire duration of their relationship with the company.
MD&A Management's Discussion and Analysis A section of the annual report where management discusses financial results and outlook.
MRR Monthly Recurring Revenue The predictable revenue earned each month from subscriptions. Key SaaS metric.
ARR Annual Recurring Revenue The annualized value of recurring subscription revenue. MRR × 12 = ARR.
NIM Net Interest Margin (Interest Income - Interest Expense) / Interest-Earning Assets. Key profitability metric for banks.
OpEx Operating Expenses Expenses incurred in normal business operations, including COGS, SG&A, D&A.
P&E / PP&E Property, Plant and Equipment Long-term tangible assets used in operations. Found on the Balance Sheet.
R&D Research and Development Expenses for creating new products or improving existing ones.
SG&A Selling, General and Administrative Operating expenses not directly tied to production, including sales, marketing, and admin costs.
TAM Total Addressable Market The total revenue opportunity available for a product or service. Used in top-down forecasting.
YoY Year-over-Year Comparison of a metric to the same period in the previous year. Automatically accounts for seasonality.
References

Source Material

📚 Primary Reference

This lecture is adapted from and enriched with content from:

Financial Modeling for Equity Research
By John Moschella, CFA, CPA (3rd Edition)

Chapters Referenced:
• Chapter 1: Key Concepts (Principle of Balance and Drive, Working Models)
• Chapter 2: Building the Income Statement (Steps 4-5: Revenue and Expense Forecasting, Table 3)
• Chapter 4: Model Calibration & Forecasting (Steps 13-18: Historic Trends, Seasonality, Adjustments, Management Guidance, Consensus, Opinions)
← Course Home Next: Working Capital & Capex →