Excel for Financial Modelling — A Beginner's Guide

Financial modelling is the art of building a mathematical representation of a company's financial performance. Excel remains the industry standard tool for this — from startup pitch decks to Wall Street analysis. Here's how to build your first model.

Structure of a Financial Model

Every financial model has three core sections:

1. Assumptions — Input variables that drive the model (growth rate, costs, prices)
2. Calculations — Formulas that process the assumptions
3. Outputs — Financial statements and charts

Best practice: keep assumptions on a separate sheet, colour-code input cells (blue font for inputs, black for formulas), and never hardcode numbers inside formulas.

Building a Simple Revenue Model

Step 1: Define Assumptions

Assumption	Year 1	Year 2	Year 3
Starting customers	100	—	—
Monthly growth rate	5%	4%	3%
Average revenue per customer	₹500	₹525	₹550
Churn rate (monthly)	3%	2.5%	2%

Step 2: Build Monthly Calculations

For each month, calculate:

• New customers = Previous month customers × growth rate
• Churned customers = Previous month customers × churn rate
• End-of-month customers = Previous + New - Churned
• Monthly revenue = End-of-month customers × ARPC

Step 3: Annual Summary

Sum the monthly figures into annual totals. Use =SUMPRODUCT or simple =SUM for annual revenue, total new customers, and total churned customers.

Cost Modelling

Separate costs into:

• Fixed costs — Rent, salaries, subscriptions (same regardless of revenue)
• Variable costs — Cost of goods sold, transaction fees (scale with revenue)
• Semi-variable — Customer support, infrastructure (step-function increases)

Key Financial Formulas

Metric	Formula
Gross Margin	=(Revenue - COGS) / Revenue
Operating Margin	=(Revenue - Total OpEx) / Revenue
Burn Rate	=Total Monthly Expenses - Monthly Revenue
Runway (months)	=Cash Balance / Monthly Burn Rate
Customer LTV	=ARPC / Churn Rate
CAC Payback	=Customer Acquisition Cost / Monthly Revenue per Customer

Scenario Analysis

Build three scenarios by changing assumptions:

• Base case — Realistic projections
• Best case — Higher growth, lower churn
• Worst case — Lower growth, higher costs

Use Excel's Data Tables (Data → What-If Analysis → Data Table) to automatically calculate outputs for different input values. Or use Scenario Manager (Data → What-If Analysis → Scenario Manager) to save and switch between named scenarios.

Best Practices

1. One formula per row — Don't mix different formulas in the same row
2. Blue for inputs, black for formulas — Industry standard colour coding
3. No circular references — They make models unstable and hard to audit
4. Label everything — Every number should have a clear label
5. Separate sheets — Assumptions, calculations, and outputs on different tabs
6. Version control — Save dated copies before major changes