Financial Modeling & Valuation
Module 2: Building a 3-Statement Model
Module Overview
This is where you become a financial modeler. In this module, you'll build an integrated 3-statement model from scratch—an income statement, balance sheet, and cash flow statement that are all connected, self-balancing, and driven by clearly defined assumptions.
The 3-statement model is the foundation of virtually all financial analysis: DCF valuation, M&A analysis, LBO modeling, and corporate planning all start here.
Learning Objectives:
By the end of this module, you will be able to:
- Structure a financial model for clarity and flexibility
- Build a revenue and expense forecast
- Project working capital and capital expenditures
- Create a debt schedule with interest calculations
- Construct an integrated balance sheet
- Build a cash flow statement from first principles
- Implement error checks and balancing mechanisms
Estimated Time: 6-8 hours
2.1 Model Structure and Best Practices
The Anatomy of a Professional Model
A well-structured model is easy to follow, easy to audit, and easy to modify. Here's the standard structure:
Tab Organization:
1. Cover/Summary - Model overview and key outputs
2. Assumptions - All inputs in one place
3. Income Statement - Revenue through Net Income
4. Balance Sheet - Assets, Liabilities, Equity
5. Cash Flow - Operating, Investing, Financing
6. Schedules - Supporting schedules (debt, D&A, working capital)
7. Valuation - DCF, multiples (added later)
8. Sensitivity - What-if analysis (added later)
Color Coding Standards
Professional models use consistent color coding:
| Color | Meaning |
|---|---|
| Blue | Inputs/assumptions (hardcoded numbers you can change) |
| Black | Formulas (calculated values) |
| Green | Links to other worksheets |
| Red | Errors or items needing attention |
This convention lets anyone quickly identify what can be changed versus what is calculated.
Formatting Guidelines
Numbers:
- Consistent decimal places (1-2 for percentages, 0-1 for large numbers)
- Use thousands or millions for readability
- Negative numbers in parentheses: (100) not -100
- Include units in headers ($ millions, %, etc.)
Layout:
- Freeze panes for row/column headers
- Use consistent column widths
- Group related items with subtotals
- Use borders sparingly but meaningfully
Formulas:
- One formula per row, copied across columns
- Never hardcode numbers in formulas
- Reference assumption cells, not typed values
- Use named ranges for key assumptions
The Golden Rules
Rule 1: Separate Inputs from Calculations All assumptions should be in one place. If you need to change revenue growth, you should change ONE cell, and it flows through everything.
Rule 2: One Formula per Row Write a formula once, copy it across. If a cell has a different formula than its neighbors, something is wrong or needs explanation.
Rule 3: No Magic Numbers Never type a number directly in a formula. Every number should reference an assumption cell.
Rule 4: Build in Checks The balance sheet must balance. Build checks that scream when something is wrong.
Rule 5: Document Your Work Use comments, labels, and notes. Future you (or a colleague) needs to understand this model.
2.2 Setting Up the Assumptions Tab
Time Structure
First, establish your model's time horizon:
Historical Years: 2022, 2023, 2024 (actual results)
Projection Years: 2025E, 2026E, 2027E, 2028E, 2029E (estimates)
Typically:
- 3-5 years of historical data
- 5-10 years of projections (depends on purpose)
Revenue Assumptions
Revenue Growth Assumptions:
2025E 2026E 2027E 2028E 2029E
Revenue Growth Rate 12% 10% 8% 6% 5%
Or segment-level detail:
Segment A Growth 15% 12% 10% 8% 6%
Segment B Growth 8% 7% 6% 5% 5%
Segment C Growth 20% 15% 12% 10% 8%
Margin Assumptions
Margin Assumptions:
2025E 2026E 2027E 2028E 2029E
Gross Margin 62.0% 62.5% 63.0% 63.0% 63.0%
SG&A % of Revenue 25.0% 24.5% 24.0% 23.5% 23.0%
R&D % of Revenue 10.0% 10.0% 10.0% 10.0% 10.0%
Working Capital Assumptions
Working Capital Assumptions:
2025E 2026E 2027E 2028E 2029E
Days Sales Outstanding 45 45 44 44 43
Days Inventory Out. 30 30 30 30 30
Days Payable Out. 42 42 43 43 44
CapEx and D&A Assumptions
Capital Expenditure:
CapEx as % of Revenue 8.0% 7.5% 7.0% 7.0% 7.0%
Depreciation:
D&A as % of Prior PP&E 15% 15% 15% 15% 15%
(or use detailed schedule)
Other Assumptions
Tax Rate 25%
Interest Rate on Debt 6.0%
Dividend Payout Ratio 20%
Minimum Cash Balance $50M
Tips for Setting Assumptions
- Start with historical averages - What have margins been?
- Consider industry trends - Is the sector growing or declining?
- Factor in company strategy - Are they investing for growth?
- Be realistic - Don't assume margins improve every year forever
- Document your reasoning - Why this growth rate?
2.3 Building the Income Statement
Revenue Forecast
Simple Approach: Growth Rate
Revenue(t) = Revenue(t-1) × (1 + Growth Rate)
Example:
2024 Revenue: $1,390M
2025E Growth: 12%
2025E Revenue: $1,390M × 1.12 = $1,557M
Excel Formula:
=Prior_Year_Revenue * (1 + Revenue_Growth_Assumption)
Segment Approach:
Total Revenue = Segment A + Segment B + Segment C
Each segment has its own growth rate
Cost of Goods Sold
COGS = Revenue × (1 - Gross Margin)
Example:
2025E Revenue: $1,557M
2025E Gross Margin: 62%
2025E COGS: $1,557M × (1 - 0.62) = $592M
Gross Profit
Gross Profit = Revenue - COGS
= $1,557M - $592M
= $965M
Check: Gross Profit / Revenue should equal your assumed gross margin.
Operating Expenses
SG&A:
SG&A = Revenue × SG&A%
= $1,557M × 25%
= $389M
R&D:
R&D = Revenue × R&D%
= $1,557M × 10%
= $156M
Depreciation & Amortization: (Linked from D&A schedule - covered in section 2.5)
Operating Income (EBIT)
Operating Income = Gross Profit - SG&A - R&D - D&A
= $965M - $389M - $156M - $120M
= $300M
Interest Expense
Interest Expense = Average Debt Balance × Interest Rate
This is tricky because it creates a circular reference:
- Interest depends on debt
- Debt depends on cash flow
- Cash flow depends on interest
Solutions:
- Use beginning debt balance (simpler, slightly less accurate)
- Enable iterative calculations in Excel (more accurate)
Pre-Tax Income
Pre-Tax Income = Operating Income - Interest Expense + Interest Income
Income Tax
Income Tax = Pre-Tax Income × Tax Rate
Note: Only pay tax if profitable. Use MAX(0, Pre-Tax Income × Tax Rate)
Net Income
Net Income = Pre-Tax Income - Income Tax
Complete Income Statement Structure
Income Statement ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Revenue 1,390 1,557 1,713 1,850
% Growth 14.9% 12.0% 10.0% 8.0%
Cost of Goods Sold 528 592 640 684
────────────────────────────────────────────────────────────────
Gross Profit 862 965 1,073 1,166
% Margin 62.0% 62.0% 62.6% 63.0%
Operating Expenses:
SG&A 347 389 420 444
R&D 139 156 171 185
Depreciation & Amortization 111 125 137 148
────────────────────────────────────────────────────────────────
Total Operating Expenses 597 670 728 777
────────────────────────────────────────────────────────────────
Operating Income (EBIT) 265 295 345 389
% Margin 19.1% 19.0% 20.1% 21.0%
Interest Expense (15) (18) (20) (22)
Interest Income 2 3 4 5
────────────────────────────────────────────────────────────────
Pre-Tax Income 252 280 329 372
Income Tax Expense 63 70 82 93
Effective Tax Rate 25.0% 25.0% 25.0% 25.0%
────────────────────────────────────────────────────────────────
Net Income 189 210 247 279
% Margin 13.6% 13.5% 14.4% 15.1%
2.4 Building the Balance Sheet
The Balance Sheet Equation
Remember: Assets = Liabilities + Equity
Your model must maintain this at all times. If it doesn't balance, you have an error.
Current Assets
Cash and Cash Equivalents Cash is typically the "plug" - the number that makes everything balance. We'll calculate it after building everything else.
Accounts Receivable
A/R = Revenue × (DSO / 365)
= $1,557M × (45 / 365)
= $192M
Inventory
Inventory = COGS × (DIO / 365)
= $592M × (30 / 365)
= $49M
Prepaid Expenses Often modeled as % of SG&A or flat:
Prepaid Expenses = SG&A × 5%
= $389M × 5%
= $19M
Long-Term Assets
Property, Plant & Equipment (Net) From the PP&E roll-forward schedule:
Ending PP&E = Beginning PP&E + CapEx - Depreciation
Intangible Assets
Ending Intangibles = Beginning Intangibles + Additions - Amortization
Other Long-Term Assets Often grown with revenue or held constant:
Other LT Assets = Prior Year × (1 + Revenue Growth)
Current Liabilities
Accounts Payable
A/P = COGS × (DPO / 365)
= $592M × (42 / 365)
= $68M
Accrued Expenses Often modeled as % of operating expenses:
Accrued Expenses = (SG&A + R&D) × 10%
= ($389M + $156M) × 10%
= $55M
Current Portion of Long-Term Debt From debt schedule - scheduled repayments due within one year.
Long-Term Liabilities
Long-Term Debt From debt schedule:
LT Debt = Prior Year LT Debt + New Borrowings - Repayments - Current Portion
Deferred Tax Liabilities Often held constant or grown slowly:
DTL = Prior Year × (1 + 2%)
Shareholders' Equity
Common Stock & APIC Usually constant unless new shares are issued:
Common Stock = Prior Year Common Stock
Retained Earnings
Ending RE = Beginning RE + Net Income - Dividends
Treasury Stock Increases with share repurchases:
Treasury Stock = Prior Year + Buybacks
Complete Balance Sheet Structure
Balance Sheet ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
ASSETS
Current Assets:
Cash and Cash Equivalents 150 178 225 289
Accounts Receivable 171 192 212 229
Inventory 43 49convergence 53 56
Prepaid Expenses 17 19 21 22
────────────────────────────────────────────────────────────────
Total Current Assets 381 438 511 596
Non-Current Assets:
PP&E, Net 833 836 834 829
Intangible Assets 100 95 90 85
Other Long-Term Assets 50 56 62 67
────────────────────────────────────────────────────────────────
Total Non-Current Assets 983 987 986 981
────────────────────────────────────────────────────────────────
TOTAL ASSETS 1,364 1,425 1,497 1,577
LIABILITIES
Current Liabilities:
Accounts Payable 61 68 74 79
Accrued Expenses 49 55 59 63
Current Portion of LT Debt 25 25 25 25
────────────────────────────────────────────────────────────────
Total Current Liabilities 135 148 158 167
Non-Current Liabilities:
Long-Term Debt 250 225 200 175
Deferred Tax Liabilities 40 41 42 43
────────────────────────────────────────────────────────────────
Total Non-Current Liabilities 290 266 242 218
────────────────────────────────────────────────────────────────
TOTAL LIABILITIES 425 414 400 385
SHAREHOLDERS' EQUITY
Common Stock 10 10 10 10
Additional Paid-in Capital 300 300 300 300
Retained Earnings 629 801 1,006 1,241
Treasury Stock 0 (100) (219) (359)
────────────────────────────────────────────────────────────────
Total Shareholders' Equity 939 1,011 1,097 1,192
────────────────────────────────────────────────────────────────
TOTAL LIABILITIES + EQUITY 1,364 1,425 1,497 1,577
CHECK (Assets - Liab - Equity) 0 0 0 0
2.5 Supporting Schedules
PP&E Schedule
The PP&E schedule tracks fixed assets over time:
PP&E Schedule ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Beginning PP&E (Gross) 1,500 1,611 1,735 1,863
+ Capital Expenditures 111 124 128 130
────────────────────────────────────────────────────────────────
Ending PP&E (Gross) 1,611 1,735 1,863 1,993
Accumulated Depreciation:
Beginning 667 778 899 1,029
+ Depreciation Expense 111 121 130 139
────────────────────────────────────────────────────────────────
Ending Accumulated Depr. 778 899 1,029 1,168
PP&E, Net 833 836 834 825
Key formulas:
CapEx = Revenue × CapEx%
Depreciation = Beginning Net PP&E × Depreciation Rate
Debt Schedule
The debt schedule tracks borrowings, repayments, and interest:
Debt Schedule ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Beginning Long-Term Debt 275 275 250 225
+ New Borrowings 0 0 0 0
- Scheduled Repayments (25) (25) (25) (25)
- Optional Repayments 0 0 0 0
────────────────────────────────────────────────────────────────
Ending Long-Term Debt 250 250 225 200
Current Portion of LT Debt 25 25 25 25
Total Debt 275 275 250 225
Average Debt Balance 275 263 238 213
Interest Rate 6.0% 6.0% 6.0% 6.0%
Interest Expense 17 16 14 13
Circular Reference Note: Interest expense affects net income, which affects cash, which might affect debt levels. Options:
- Use beginning balance for interest (avoids circularity)
- Enable iterative calculations in Excel (File > Options > Formulas > Enable iterative calculation)
Working Capital Schedule
Working Capital Schedule ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Accounts Receivable 171 192 212 229
Inventory 43 49 53 56
Prepaid Expenses 17 19 21 22
────────────────────────────────────────────────────────────────
Total Operating CA 231 260 286 307
Accounts Payable 61 68 74 79
Accrued Expenses 49 55 59 63
────────────────────────────────────────────────────────────────
Total Operating CL 110 123 133 142
Net Working Capital 121 137 153 165
Change in NWC (16) (16) (12)
Change in NWC is used in the cash flow statement. An increase in NWC is a use of cash (negative in CFO).
2.6 Building the Cash Flow Statement
Operating Activities
Start with Net Income and adjust for non-cash items and working capital changes:
Cash Flow from Operations ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Net Income 189 210 247 279
Adjustments for Non-Cash Items:
+ Depreciation & Amortization 111 125 137 148
+ Stock-Based Compensation 15 17 19 20
Changes in Working Capital:
(Increase)/Decrease in A/R (15) (21) (20) (17)
(Increase)/Decrease in Inv. (5) (6) (4) (3)
(Increase)/Decrease in Prepaid (2) (2) (2) (1)
Increase/(Decrease) in A/P 5 7 6 5
Increase/(Decrease) in Accr. 4 6 4 4
────────────────────────────────────────────────────────────────
Cash from Operations 302 336 387 435
Key insight:
- Increase in asset = cash outflow (you're tying up cash)
- Increase in liability = cash inflow (you're delaying payment)
Investing Activities
Cash Flow from Investing ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Capital Expenditures (111) (124) (128) (130)
Acquisitions 0 0 0 0
Asset Sales 5 0 0 0
────────────────────────────────────────────────────────────────
Cash from Investing (106) (124) (128) (130)
Financing Activities
Cash Flow from Financing ($M) 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Debt Issuance 0 0 0 0
Debt Repayment (25) (25) (25) (25)
Dividends Paid (38) (42) (49) (56)
Share Repurchases (50) (100) (119) (140)
────────────────────────────────────────────────────────────────
Cash from Financing (113) (167) (193) (221)
Net Change in Cash
Net Change in Cash 2024A 2025E 2026E 2027E
────────────────────────────────────────────────────────────────
Cash from Operations 302 336 387 435
Cash from Investing (106) (124) (128) (130)
Cash from Financing (113) (167) (193) (221)
────────────────────────────────────────────────────────────────
Net Change in Cash 83 45 66 84
Beginning Cash 67 150 195 261
Ending Cash 150 195 261 345
The Cash Plug
In most models, cash is the "plug" that makes the balance sheet balance. Here's how it works:
- Calculate all assets except cash
- Calculate all liabilities
- Calculate equity (including retained earnings from net income)
- Cash = Total Liabilities + Equity - Other Assets
Excel formula:
Cash = Total Liabilities + Total Equity - Non-Cash Assets
Verify: Ending cash from balance sheet should match cash flow statement ending cash.
2.7 Balancing the Model
The Balance Check
Add a check row to verify balance:
Balance Check = Total Assets - Total Liabilities - Total Equity
This should always equal zero. If it doesn't, you have an error.
Common Balancing Issues
Issue 1: Retained Earnings Not Connecting
Retained Earnings = Prior RE + Net Income - Dividends
Make sure dividends flow correctly from cash flow statement.
Issue 2: Cash Not Calculated Correctly Ensure cash is calculated as the plug, or verify cash flow statement ties to balance sheet.
Issue 3: Debt Schedule Mismatch Total debt on balance sheet should match debt schedule.
Issue 4: Working Capital Discrepancies Changes in working capital accounts should match cash flow adjustments.
Error-Checking Best Practices
1. Build balance checks on every tab
Balance Check: =IF(ABS(Assets-Liab-Equity)<0.01,"OK","ERROR!")
2. Cross-reference related items
- Depreciation on I/S = Depreciation on PP&E schedule
- Interest expense on I/S = Interest on debt schedule
- CapEx on CF statement = CapEx on PP&E schedule
3. Use conditional formatting Highlight cells in red if balance check fails.
4. Build a summary check section
Model Integrity Checks:
Balance Sheet Balances: OK
Cash Flow Ties to BS: OK
D&A Matches: OK
Debt Schedule Ties: OK
2.8 Sensitivity and Scenarios
Sensitivity Analysis
Once your model works, test how sensitive outputs are to key assumptions:
Revenue Sensitivity:
| Revenue Growth | 2029 Net Income | 2029 FCF |
|---|---|---|
| 8% | $320M | $380M |
| 10% | $350M | $410M |
| 12% | $380M | $445M |
| 14% | $410M | $480M |
Margin Sensitivity:
| Gross Margin | 2029 Net Income |
|---|---|
| 60% | $290M |
| 62% | $350M |
| 64% | $410M |
Scenario Analysis
Create distinct scenarios:
Base Case: Management guidance/consensus Upside Case: Optimistic assumptions (higher growth, better margins) Downside Case: Conservative assumptions (slower growth, margin pressure)
Use Excel's Scenario Manager or build a scenario selector dropdown.
2.9 Practical Exercise: Build Your First Model
Exercise Instructions
Build a 3-statement model for a simplified company:
Given Information (Historical FY 2024):
Income Statement:
Revenue: $500M
Gross Margin: 60%
SG&A: $100M
D&A: $30M
Interest Expense: $10M
Tax Rate: 25%
Balance Sheet:
Cash: $50M
Accounts Receivable: $60M
Inventory: $40M
PP&E Net: $200M
Total Assets: $350M
Accounts Payable: $30M
Accrued Expenses: $20M
Long-Term Debt: $150M
Total Liabilities: $200M
Shareholders' Equity: $150M
Assumptions for Projections (2025E-2027E):
Revenue Growth: 10% per year
Gross Margin: 60% (constant)
SG&A: 20% of revenue
CapEx: 8% of revenue
Depreciation: 15% of beginning PP&E
DSO: 44 days
DIO: 29 days
DPO: 22 days
Interest Rate: 6%
Debt Repayment: $15M per year
Dividend Payout: 25% of Net Income
Tax Rate: 25%
Your Task:
- Set up the assumptions tab
- Build the income statement
- Build the supporting schedules (PP&E, Debt, Working Capital)
- Build the balance sheet
- Build the cash flow statement
- Add balance checks
- Verify everything ties
2.10 Key Takeaways
Model Structure
- Organize tabs logically: Assumptions → Statements → Schedules → Analysis
- Use consistent formatting and color coding
- Keep inputs separate from calculations
Income Statement
- Revenue driven by growth rates or detailed drivers
- Costs driven by margins or % of revenue
- D&A and interest from supporting schedules
Balance Sheet
- Working capital driven by days ratios
- PP&E from roll-forward schedule
- Debt from debt schedule
- Cash is the plug
Cash Flow Statement
- Start with Net Income
- Add back non-cash charges
- Adjust for working capital changes
- CapEx in investing
- Debt and dividends in financing
Balancing
- Assets must equal Liabilities + Equity
- Build checks to catch errors
- Cross-reference schedules to statements
Looking Ahead to Module 3
You now have a working 3-statement model. This is the foundation for valuation.
In Module 3, you'll use this model to perform DCF valuation:
- Calculate Free Cash Flow
- Determine the appropriate discount rate (WACC)
- Project cash flows and terminal value
- Arrive at an intrinsic value
Your 3-statement model generates the Free Cash Flows that drive DCF valuation.
Summary
Congratulations on completing Module 2! You can now:
- Structure a financial model professionally
- Build integrated income statement projections
- Create balance sheet forecasts with working capital
- Construct cash flow statements from first principles
- Build supporting schedules (PP&E, Debt, Working Capital)
- Balance and error-check your model
This is a major milestone. The 3-statement model is the core skill of financial modeling. Everything else builds on this foundation.
Ready for valuation? Proceed to Module 3: DCF Valuation to learn how to value companies using discounted cash flow analysis.
"The function of a financial model is not to tell you what will happen. It is to help you think about what might happen." — Michael Mauboussin