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:

Model time structure

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-level growth assumptions

	2025E	2026E	2027E	2028E	2029E
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 (DSO)	45	45	45	44	44
Days inventory outstanding (DIO)	30	30	30	30	30
Days payable outstanding (DPO)	42	42	42	43	43

CapEx and D&A Assumptions

CapEx and D&A assumptions

	2025E	2026E	2027E	2028E	2029E
Capital expenditure
CapEx as % of revenue	8.0%	7.5%	7.0%	7.0%	7.0%
Depreciation
D&A as % of revenue	8.0%	8.0%	8.0%	8.0%	8.0%

Or use a detailed schedule for depreciation.

Other Assumptions

Other assumptions

Tax rate	25%
Interest rate on debt	6.0%
Dividend payout ratio	20%
Minimum cash balance	$50M

Tips for Setting Assumptions

1. Start with historical averages - What have margins been?
2. Consider industry trends - Is the sector growing or declining?
3. Factor in company strategy - Are they investing for growth?
4. Be realistic - Don't assume margins improve every year forever
5. Document your reasoning - Why this growth rate?

---

2.3 Building the Income Statement

Revenue Forecast

Simple Approach: Growth Rate

Excel Formula:

Segment Approach:

Cost of Goods Sold

Gross Profit

Check: Gross Profit / Revenue should equal your assumed gross margin.

Operating Expenses

SG&A:

R&D:

Depreciation & Amortization: (Linked from D&A schedule - covered in section 2.5)

Operating Income (EBIT)

Interest Expense

This is tricky because it creates a circular reference:

• Interest depends on debt
• Debt depends on cash flow
• Cash flow depends on interest

Solutions:

1. Use beginning debt balance (simpler, slightly less accurate)
2. Enable iterative calculations in Excel (more accurate)

Pre-Tax Income

Income Tax

Note: Only pay tax if profitable. Use MAX(0, Pre-Tax Income × Tax Rate)

Net Income

Complete Income Statement Structure

Income statement ($M)

$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)	(16)	(14)	(13)
Interest income	2	3	4	5
Pre-tax income	252	282	335	381
Income tax expense (25%)	63	71	84	95
Net income	189	211	251	286
% Margin	13.6%	13.6%	14.7%	15.5%

Tax is 25% of pre-tax income, rounded to the nearest $1M.

---

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

Inventory

Prepaid Expenses Often modeled as % of SG&A or flat:

Long-Term Assets

Property, Plant & Equipment (Net) From the PP&E roll-forward schedule:

Intangible Assets

Other Long-Term Assets Often grown with revenue or held constant:

Current Liabilities

Accounts Payable

Accrued Expenses Often modeled as % of operating expenses:

Current Portion of Long-Term Debt From debt schedule - scheduled repayments due within one year.

Long-Term Liabilities

Long-Term Debt From debt schedule:

Deferred Tax Liabilities Often held constant or grown slowly:

Shareholders' Equity

Common Stock & APIC Usually constant unless new shares are issued:

Retained Earnings

Treasury Stock Increases with share repurchases:

Complete Balance Sheet Structure

Balance sheet ($M)

$M	2024A	2025E	2026E	2027E
Assets
Current assets
Cash and cash equivalents	150	191	256	342
Accounts receivable	171	192	211	228
Inventory	43	49	53	56
Prepaid expenses	17	19	21	22
Total current assets	381	451	541	648
Non-current assets
PP&E, net	833	837	833	820
Intangible assets	100	95	90	85
Other long-term assets	50	56	62	67
Total non-current assets	983	988	985	972
Total assets	1,364	1,439	1,526	1,620
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	317	336	356
Retained earnings	629	798	999	1,228
Treasury stock	0	(100)	(219)	(359)
Total shareholders' equity	939	1,025	1,126	1,235
Total liabilities + equity	1,364	1,439	1,526	1,620
Check (assets - liabilities - equity)	0	0	0	0

Additional paid-in capital grows by stock-based compensation each year. Treasury stock grows by share buybacks.

---

2.5 Supporting Schedules

PP&E Schedule

The PP&E schedule tracks fixed assets over time:

PP&E schedule ($M)

$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	672	778	898	1,030
+ Depreciation expense	106	120	132	143
Ending accumulated depreciation	778	898	1,030	1,173
PP&E, net	833	837	833	820

Depreciation is total D&A minus the $5M of amortization on intangible assets each year.

Key formulas:

Debt Schedule

The debt schedule tracks borrowings, repayments, and interest:

Debt schedule ($M)

$M	2024A	2025E	2026E	2027E
Beginning total debt		275	250	225
+ New borrowings		0	0	0
- Scheduled repayments		(25)	(25)	(25)
- Optional repayments		0	0	0
Ending total debt	275	250	225	200
Less: current portion (due next year)	(25)	(25)	(25)	(25)
Long-term debt (balance sheet)	250	225	200	175
Average debt balance		262.5	237.5	212.5
Interest rate		6.0%	6.0%	6.0%
Interest expense		16	14	13

Interest is 6% of the average of beginning and ending debt, rounded to the nearest $1M. The 2024A column is the opening balance.

Circular Reference Note: In this example, debt repayments are fixed at $25M a year, so interest never depends on cash and there is no circular reference. It appears once debt depends on cash, for example with a cash sweep or a revolver: interest affects net income, which affects cash, which affects debt. Options:

1. Use beginning balance for interest (avoids circularity)
2. Enable iterative calculations in Excel (File > Options > Formulas > Enable iterative calculation)

Working Capital Schedule

Working capital schedule ($M)

$M	2024A	2025E	2026E	2027E
Accounts receivable	171	192	211	228
Inventory	43	49	53	56
Prepaid expenses	17	19	21	22
Total operating CA	231	260	285	306
Accounts payable	61	68	74	79
Accrued expenses	49	55	59	63
Total operating CL	110	123	133	142
Net working capital	121	137	152	164
Change in NWC		(16)	(15)	(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)

$M	2024A	2025E	2026E	2027E
Net income	189	211	251	286
Adjustments for non-cash items
+ Depreciation & amortization	111	125	137	148
+ Stock-based compensation	15	17	19	20
+ Deferred taxes	0	1	1	1
Changes in working capital
(Increase)/decrease in A/R	(15)	(21)	(19)	(17)
(Increase)/decrease in inventory	(5)	(6)	(4)	(3)
(Increase)/decrease in prepaid	(2)	(2)	(2)	(1)
Increase/(decrease) in A/P	5	7	6	5
Increase/(decrease) in accrued expenses	4	6	4	4
Cash from operations	302	338	393	443

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)

$M	2024A	2025E	2026E	2027E
Capital expenditures	(111)	(124)	(128)	(130)
Acquisitions	0	0	0	0
Increase in other long-term assets	0	(6)	(6)	(5)
Asset sales	5	0	0	0
Cash from investing	(106)	(130)	(134)	(135)

Financing Activities

Cash flow from financing ($M)

$M	2024A	2025E	2026E	2027E
Debt issuance	0	0	0	0
Debt repayment	(25)	(25)	(25)	(25)
Dividends paid (20% of net income)	(38)	(42)	(50)	(57)
Share repurchases	(50)	(100)	(119)	(140)
Cash from financing	(113)	(167)	(194)	(222)

Net Change in Cash

Net change in cash ($M)

$M	2024A	2025E	2026E	2027E
Cash from operations	302	338	393	443
Cash from investing	(106)	(130)	(134)	(135)
Cash from financing	(113)	(167)	(194)	(222)
Net change in cash	83	41	65	86
Beginning cash	67	150	191	256
Ending cash	150	191	256	342

Ending cash matches the cash line on the balance sheet in every year.

The Cash Plug

In most models, cash is the "plug" that makes the balance sheet balance. Here's how it works:

1. Calculate all assets except cash
2. Calculate all liabilities
3. Calculate equity (including retained earnings from net income)
4. Cash = Total Liabilities + Equity - Other Assets

Excel formula:

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:

This should always equal zero. If it doesn't, you have an error.

Common Balancing Issues

Issue 1: Retained Earnings Not Connecting

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

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):

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: assets
Cash	$50M
Accounts receivable	$60M
Inventory	$40M
PP&E net	$200M
Total assets	$350M
Balance sheet: liabilities and equity
Accounts payable	$30M
Accrued expenses	$20M
Long-term debt	$150M
Total liabilities	$200M
Shareholders' equity	$150M

Assumptions for Projections (2025E-2027E):

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:

1. Set up the assumptions tab
2. Build the income statement
3. Build the supporting schedules (PP&E, Debt, Working Capital)
4. Build the balance sheet
5. Build the cash flow statement
6. Add balance checks
7. Verify everything ties

Check Your Answers

Build the model yourself first. Then compare your numbers with the ones below. Small differences from rounding are fine.

These answers use four choices the instructions leave open. If you made a different choice, your numbers will move a little, and that is fine too.

• A year has 365 days. Inventory and payables days are based on COGS.
• Interest is 6% of the debt balance at the start of each year. This avoids a circular reference.
• Accrued expenses stay at $20M.
• Cash is the result of the cash flow statement, not an input.

Show the answers

Income statement ($M)

	2025E	2026E	2027E
Revenue	550.0	605.0	665.5
Gross profit	330.0	363.0	399.3
SG&A	110.0	121.0	133.1
Depreciation	30.0	32.1	34.5
EBIT	190.0	209.9	231.7
Interest	9.0	8.1	7.2
Net income	135.8	151.4	168.3
Dividends	33.9	37.8	42.1

Balance sheet at year end ($M)

	2025E	2026E	2027E
Cash	122.3	197.5	282.3
Accounts receivable	66.3	72.9	80.2
Inventory	17.5	19.2	21.2
PP&E net	214.0	230.3	249.0
Total assets	420.1	519.9	632.6
Accounts payable	13.3	14.6	16.0
Accrued expenses	20.0	20.0	20.0
Long-term debt	135.0	120.0	105.0
Shareholders' equity	251.8	365.3	491.6
Total liabilities and equity	420.1	519.9	632.6

Cash flow statement ($M)

	2025E	2026E	2027E
Cash from operations	165.2	176.4	195.1
Cash from investing (CapEx)	(44.0)	(48.4)	(53.2)
Cash from financing	(48.9)	(52.8)	(57.1)
Change in cash	72.3	75.2	84.8

What to notice

• The balance sheet balances in every year. If yours does not, check that net income, depreciation, CapEx, debt repayment and dividends each appear in both the cash flow statement and the balance sheet.
• In 2025, inventory falls from $40M to $17.5M and payables fall from $30M to $13.3M. That happens because the assumed days (29 and 22) are much lower than 2024's actual days. In a real model, ask whether such a big change is realistic.
• Cash keeps building because the company earns far more than it spends. A real company would likely pay down more debt, buy back shares or invest the extra cash.

---

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