Learning Objectives

What You'll Learn Today

By the end of this session, you will be able to:

Section 1

The Model Building Process

A systematic approach to building financial models

💭
Think About It

What could go wrong if you build a financial model without checking for errors?

Consider: garbage in, garbage out. A single error can cascade through your entire model.

5-Step Model Building Framework

1
Gather Historical Data

Collect 3-5 years of financial statements from annual reports, SEC filings, or databases like Capitaline, Bloomberg.

2
Input & Structure Data

Enter data into Excel with proper formatting. Use consistent layouts for Income Statement, Balance Sheet, and Cash Flow.

3
Calculate Key Ratios

Compute profitability, liquidity, efficiency, and leverage ratios for each historical year.

4
Perform Trend Analysis

Identify patterns, growth rates, and anomalies. Calculate CAGR and year-over-year changes.

5
Quality Checks

Verify balance sheet balances, cash flow reconciles, and cross-check totals. Implement error-checking formulas.

Historical_Model.xlsx 
┌─────────────────────────────────────────────────────────────────────────┐
│                    HISTORICAL FINANCIAL MODEL                            │
├─────────────────┬──────────┬──────────┬──────────┬──────────┬──────────┤
│ INCOME STATEMENT│   2021   │   2022   │   2023   │   2024   │   2025   │
├─────────────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ Revenue         │  800,000 │  880,000 │  968,000 │ 1,064,800│ 1,171,280│
│ COGS            │  480,000 │  519,200 │  561,440 │  607,536 │  657,117 │
│ Gross Profit    │  320,000 │  360,800 │  406,560 │  457,264 │  514,163 │
│ EBITDA          │  160,000 │  184,000 │  211,600 │  243,340 │  279,841 │
│ EBIT            │  120,000 │  140,000 │  163,800 │  191,530 │  223,873 │
│ Net Income      │   80,000 │   95,000 │  115,000 │  138,000 │  165,600 │
├─────────────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ GROWTH RATES    │          │          │          │          │          │
│ Revenue Growth  │    -     │   10.0%  │   10.0%  │   10.0%  │   10.0%  │
│ EBITDA Margin   │   20.0%  │   20.9%  │   21.9%  │   22.8%  │   23.9%  │
└─────────────────┴──────────┴──────────┴──────────┴──────────┴──────────┘
Section 2

Inputting Historical Financials

Best practices for data entry and organization

📊 Primary Data Sources

  • Annual Reports: Most reliable, audited data
  • SEC Filings (10-K, 10-Q): US companies
  • MCA Filings: Indian companies
  • Capitaline/Bloomberg: Database access

⚠️ Common Pitfalls

  • Mixing different fiscal year ends
  • Ignoring restatements and adjustments
  • Not checking for discontinued operations
  • Copy-paste errors from PDFs

📐 Excel Layout Best Practices

✅ Recommended Structure
Line Item
2022
2023
2024
2025
Revenue
100,000
115,000
132,250
152,088
COGS
60,000
67,500
75,938
85,429
Gross Profit
=B2-B3
=C2-C3
=D2-D3
=E2-E3
💡 Key Principles
  • Years in columns (left to right, oldest to newest)
  • Line items in rows
  • Use formulas for all calculations
  • Color code: Blue = hardcodes, Black = formulas
  • One sheet per statement
  • Include units (₹ in millions, etc.)
  • Round consistently
🎨 Color Coding Convention

Blue font: Hardcoded inputs (historical data you entered)
Black font: Formulas (calculated values)
Green font: Links to other sheets
Red font: Error checks or conditional alerts

Section 3

Calculating Key Ratios

Essential financial ratios for analysis

40%
Gross Margin
Gross Profit / Revenue
15%
EBITDA Margin
EBITDA / Revenue
2.5x
Current Ratio
Current Assets / Current Liabilities
0.8x
D/E Ratio
Total Debt / Total Equity

📈 Profitability Ratios

Ratio Formula Excel Formula Interpretation
Gross Margin Gross Profit / Revenue =GrossProfit/Revenue Higher = better pricing power
EBITDA Margin EBITDA / Revenue =EBITDA/Revenue Operating cash profitability
Operating Margin EBIT / Revenue =EBIT/Revenue Core business profitability
Net Profit Margin Net Income / Revenue =NetIncome/Revenue Bottom-line profitability
Return on Equity (ROE) Net Income / Shareholders' Equity =NetIncome/AvgEquity Return to shareholders
Return on Assets (ROA) Net Income / Total Assets =NetIncome/AvgAssets Asset efficiency

💧 Liquidity Ratios

Ratio Formula Healthy Range
Current Ratio CA / CL 1.5 - 3.0x
Quick Ratio (CA - Inventory) / CL 1.0 - 2.0x
Cash Ratio Cash / CL 0.5 - 1.0x

⚡ Efficiency Ratios

Ratio Formula Indicates
Asset Turnover Revenue / Avg Assets Asset utilization
DSO (A/R / Revenue) × 365 Collection speed
DIO (Inv / COGS) × 365 Inventory holding
DPO (A/P / COGS) × 365 Payment speed

💻 Ratio Calculation in Excel

Ratios
2021
2022
2023
2024
2025
Gross Margin
=B6/B2
=C6/C2
=D6/D2
=E6/E2
=F6/F2
EBITDA Margin
=B10/B2
=C10/C2
=D10/D2
=E10/E2
=F10/F2
Net Margin
=B18/B2
=C18/C2
=D18/D2
=E18/E2
=F18/F2
Section 4

Trend Analysis

Identifying patterns and growth trajectories

📊 Growth Rate Calculations

Year-over-Year (YoY) Growth
YoY Growth = (Current Year - Prior Year) / Prior Year

Excel: =(C2-B2)/B2

CAGR (Compound Annual Growth Rate)
CAGR = (End Value / Start Value)^(1/n) - 1

Excel: =(F2/B2)^(1/4)-1

📈 Revenue Trend Analysis Example

Metric 2021 2022 2023 2024 2025 CAGR
Revenue (₹ M) 800 880 968 1,065 1,171 10.0%
YoY Growth - 10.0% ↑ 10.0% ↑ 10.0% ↑ 10.0% ↑ -
Gross Profit (₹ M) 320 361 407 457 514 12.6%
Gross Margin 40.0% 41.0% 42.0% 42.9% 43.9% Improving ↑
✅ Analysis Insight

Gross Profit CAGR (12.6%) exceeds Revenue CAGR (10.0%), indicating improving operational efficiency and margin expansion. This is a positive trend for the company.

📉 Common-Size Trend Analysis

Express all items as % of Revenue to identify structural changes

% of Revenue 2021 2022 2023 2024 2025 Trend
Revenue 100.0% 100.0% 100.0% 100.0% 100.0% -
COGS 60.0% 59.0% 58.0% 57.1% 56.1% ↓ Improving
Gross Margin 40.0% 41.0% 42.0% 42.9% 43.9% ↑ Expanding
SG&A 15.0% 14.5% 14.0% 13.5% 13.0% ↓ Efficient
EBITDA Margin 20.0% 20.9% 21.9% 22.8% 23.9% ↑ Strong
Section 5

Quality Checks & Error Detection

Building bulletproof models with error-checking

✅ Balance Sheet Balance Check

The most fundamental check - Assets MUST equal Liabilities + Equity

Check Formula
=IF(Total_Assets = Total_L_and_E, "✓ BALANCED", "⚠ ERROR")

Or use: =Total_Assets - Total_L_and_E (should equal 0)

Example Check
✅
Balance Sheet Check: PASSED
Assets (₹900,000) = L&E (₹900,000) | Difference: ₹0

✅ Cash Flow Reconciliation Check

Ending Cash on CF Statement must match Cash on Balance Sheet

Check Formula
=IF(CF_Ending_Cash = BS_Cash, "✓ MATCH", "⚠ MISMATCH")
Example Check
✅
Cash Reconciliation: PASSED
CF Ending Cash (₹150,000) = BS Cash (₹150,000)

📋 Model Quality Checklist

✓
Balance Sheet Balances: Total Assets = Total Liabilities + Equity
✓
Cash Flow Reconciles: CF ending cash = BS cash
✓
Net Income Links: NI flows to Retained Earnings and CF Operations
✓
Depreciation Links: IS depreciation = CF add-back = BS accumulated change
✓
Working Capital Signs: Increase in CA is cash outflow; Increase in CL is cash inflow
✓
Growth Rates Reasonable: No negative growth unless business declined
✓
Margins in Valid Range: Gross margin 20-60%, Operating margin 5-30%
✓
No #REF! or #DIV/0! errors: All formulas working correctly

🔍 Error Detection Dashboard

Create a summary sheet with all error checks

Check Name Formula Expected Result Status
BS Balance Check =Assets - (Liab + Equity) 0 ✓ OK
Cash Reconciliation =CF_Cash - BS_Cash 0 ✓ OK
Retained Earnings =RE_End - (RE_Begin + NI - Div) 0 ✓ OK
Debt Schedule =Debt_Begin + New_Debt - Repaid = Debt_End ✓ OK
Revenue Growth =IF(Growth>50%,"Check","OK") OK ⚠ Review
⚠️ Pro Tip: Conditional Alert

Use conditional formatting to highlight errors automatically:
=IF(ABS(check_value)>0.01, "ERROR", "OK")
Set cell to turn RED if "ERROR" appears.

Excel Lab

Hands-On Practice

Build your own historical model

📥 Download Practice Files

Download the data files and practice guide to work through the exercises.

Practice Guide
📖 Download Practice Guide (DOCX)

The practice guide contains step-by-step instructions for all exercises with expected results.

View all practice files →

🎯 Practice Exercises

Exercise 1: Build Historical Income Statement

Input 5 years of historical data for a company and calculate all subtotals using formulas.

Deliverable: Income Statement with Revenue, COGS, Gross Profit, Operating Expenses, EBIT, Net Income

Exercise 2: Calculate Ratio Dashboard

Create a ratio analysis section with at least 10 key ratios for all 5 years.

Include: Gross Margin, EBITDA Margin, Net Margin, Current Ratio, D/E Ratio, ROE, ROA, Asset Turnover

Exercise 3: Implement Quality Checks

Build an error-checking dashboard that flags any issues in your model.

Include: BS balance check, CF reconciliation, margin range checks, growth rate sanity checks

📚 Key Terms - Click to Flip

Knowledge Check

Test Your Understanding

Let's see what you've learned!

Summary

Key Takeaways

📝 What We Covered Today

  • 5-Step Process: Gather data → Input → Calculate ratios → Trend analysis → Quality checks
  • Data Input Best Practices: Years in columns, color coding (blue=hardcode, black=formula), consistent formatting
  • Key Ratios: Profitability (margins, ROE, ROA), Liquidity (current, quick), Efficiency (turnover, DSO, DIO)
  • Trend Analysis: YoY growth, CAGR, common-size analysis to identify patterns
  • Quality Checks: BS balance, CF reconciliation, NI linkage, error detection dashboard

📋 Quick Reference - Essential Formulas

Growth Rates
  • YoY = (CY - PY) / PY
  • CAGR = (End/Start)^(1/n) - 1
Error Checks
  • BS: Assets - (L+E) = 0
  • CF: End Cash = BS Cash
📚 Next Session

Lecture 6: Revenue Forecasting
Now that we have a solid historical foundation, we'll learn to project future revenues using various methods - bottom-up, top-down, and driver-based approaches.
Reading: Pignataro, Ch. 6