Buổi 10 — Mô hình Tài chính 3 Báo cáo — Chuẩn Investment Banking

3-Statement Model = Foundation. Mọi analysis khác (DCF, LBO, M&A) đều xây trên nền này.

---

Mục tiêu buổi học

#	Mục tiêu	Bloom's Level
1	Hiểu cấu trúc & best practices 3-Statement Model chuẩn IB	Understand
2	Liên kết IS → BS → CF trong Excel một cách hệ thống	Apply
3	Xây Revenue Build, Operating Model, Debt Schedule	Apply
4	Handle circular references (Interest ↔ Debt ↔ Cash)	Analyze

---

Tổng quan: Tại sao 3-Statement Model là nền tảng?

Trong Investment Banking, 3-Statement Model (hay Integrated Financial Model) là file Excel kết nối đầy đủ 3 báo cáo tài chính — Income Statement, Balance Sheet, và Cash Flow Statement. Mọi mô hình phức tạp hơn (DCF, LBO, M&A Merger Model) đều xây trên nền tảng của 3-Statement.

Tại sao phải "integrated"?

Income Statement     →  Net Income flows to BS (Retained Earnings)
                     →  NI flows to CF (starting point of CFO)

Balance Sheet        →  ΔWC feeds into CFO
                     →  Debt/Cash balances determine Interest Expense (IS)

Cash Flow Statement  →  Ending Cash → links back to BS
                     →  CapEx → affects PP&E (BS)
                     →  Debt issuance/repayment → updates BS

Nếu thay đổi 1 assumption (ví dụ: Revenue Growth), cả 3 báo cáo phải tự động cập nhật và Balance Sheet vẫn balance: $A=L+E$.

---

Phần 1 — Model Architecture: Best Practices chuẩn IB

1.1 Tab Structure

Một model chuẩn Investment Banking có cấu trúc tab:

┌──────────────────────────────────────────────┐
│  Cover ──→ Assumptions ──→ IS ──→ BS ──→ CF  │
│            ──→ Schedules ──→ DCF ──→ Output   │
└──────────────────────────────────────────────┘

Tab	Mô tả	Color
Cover	Project name, date, version, analyst	🔵
Assumptions	Tất cả hard-coded inputs tập trung 1 chỗ	🟢
IS	Income Statement: Historical + Projected	🟡
BS	Balance Sheet: Historical + Projected	🟡
CF	Cash Flow Statement: link từ IS & BS	🟡
Schedules	WC, D&A, CapEx, Debt — supporting schedules	🟠
DCF	FCFF / FCFE, TV, EV, Equity Bridge	🔴
Output	Summary page, football field, comps	⚫

1.2 Color Coding Convention

Quy tắc bất di bất dịch trong mọi IB model:

Màu font	Ý nghĩa	Ví dụ
🔵 Blue	Hard-coded input (có thể thay đổi)	Revenue growth = 15%, Tax rate = 20%
⚫ Black	Formula / Calculation	=Revenue * Gross Margin %
🟢 Green	Link từ sheet khác	=IS!D15 (link NI sang BS)
🔴 Red	Error check / warning	=IF(A≠L+E, "ERROR", "OK")

Tại sao color coding quan trọng? Vì model trong IB được review bởi VP, Director, MD — họ cần biết ngay cell nào là input (có thể challenge) và cell nào là formula (cần kiểm logic).

1.3 Naming Conventions & Structure

Nguyên tắc	Chi tiết
Thời gian trái → phải	Historical bên trái, projected bên phải
Named Ranges	Dùng cho key inputs: Tax_Rate, WACC, Terminal_Growth
Không hardcode	Mọi số cứng phải nằm trên tab Assumptions
1 row = 1 item	Không merge cells, không multi-level trong 1 row
Units nhất quán	Chọn 1: triệu VND hoặc tỷ VND — stick to it
Sign convention	Revenue = positive, Expense = positive (hiển thị dấu ngoặc)

1.4 Error Checks & Balance Check

Mọi model phải có Error Check section (thường ở cuối mỗi tab hoặc tab riêng):

Check	Formula	Expected
BS Balance	$\text{Total Assets}-\text{Total L\&E}$	= 0
CF Ending Cash = BS Cash	$\text{CF Ending Cash}-\text{BS Cash}$	= 0
NI consistency	$\text{IS Net Income}-\text{CF Starting NI}$	= 0
Debt Schedule	$\text{Begin Debt}+\text{New Issue}-\text{Repay}-\text{End Debt}$	= 0
Grand Error Flag	=IF(SUM(all checks)=0, "✅ NO ERRORS", "❌ ERROR")	✅

$$\boxed{{\displaystyle A=L+E\iff \text{Total Assets}-\text{Total Liabilities}-\text{Total Equity}=0}}$$

1.5 Version Control

Practice	Mô tả
File naming	[Company]_3SM_v[X.Y]_[YYYYMMDD].xlsx
Change log tab	Ghi lại: ngày, người sửa, nội dung thay đổi
Sensi backup	Trước khi thay đổi lớn → Save As
No circular + iterative	Tắt iteration khi không cần (tránh mask errors)

---

Phần 2 — Income Statement Build

2.1 Revenue Build: Segment / Units × Price / Growth

Có 3 cách model Revenue tùy industry:

Approach 1 — Growth Rate (Simple):

$$Revenu{e}_t=Revenu{e}_{t-1}\times (1+g_t)$$

Approach 2 — Units × Price (Manufacturing/Retail):

$$Revenu{e}_t=\sum _{i}Unit{s}_{i,t}\times Pric{e}_{i,t}$$

Approach 3 — Segment Build (Conglomerate):

$$Revenu{e}_t=\sum _{seg}Revenu{e}_{seg,t}$$

Ví dụ FPT:

Segment	2024 (tỷ)	Growth	2025E	2026E	2027E
IT Services (Global)	28,600	22%	34,892	41,871	49,409
Telecom	16,800	8%	18,144	19,596	21,164
Education	7,400	15%	8,510	9,787	11,255
Others	8,500	10%	9,350	10,285	11,314
Total Revenue	61,300		70,896	81,539	93,142

2.2 COGS & Gross Margin

$$COG{S}_t=Revenu{e}_t\times (1-{\text{Gross Margin}}_t)$$

Hoặc build by component:

Component	Driver	Formula
Direct Materials	% of Revenue	$=Revenue\times \text{Material \%}$
Direct Labor	Headcount × Avg cost	$=HC\times \text{Avg Salary}$
Overhead	Fixed + Variable	$=\text{Fixed}+Revenue\times \text{Var \%}$
Total COGS		Sum

2.3 Operating Expenses: SG&A, R&D, D&A

Line Item	Typical Driver	Formula
SG&A	% of Revenue (declining)	$SG{A}_t=Revenu{e}_t\times SGA{\mathrm{\%}}_t$
R&D	% of Revenue or flat growth	$R\mathrm{\&}{D}_t=Revenu{e}_t\times R\mathrm{\&}D{\mathrm{\%}}_t$
D&A	From PP&E Schedule	$D\mathrm{\&}{A}_t=\text{PP\&E Schedule output}$

2.4 Interest Expense — From Debt Schedule (⚠️ Circular!)

$${\text{Interest Expense}}_t={\text{Avg Debt}}_t\times \text{Interest Rate}$$$${\text{Avg Debt}}_t=\frac{{\text{Debt}}_{t-1}+{\text{Debt}}_t}{2}$$

⚠️ Đây là điểm gây Circular Reference! Vì:

• Interest → ảnh hưởng Net Income → ảnh hưởng Cash → ảnh hưởng Debt → ảnh hưởng Interest
• Xem chi tiết ở Phần 5

2.5 Tax Expense

$${\text{Tax}}_t=EB{T}_t\times {\text{Effective Tax Rate}}_t$$

Nếu EBT < 0 → kiểm tra NOL (Net Operating Loss) carry-forward:

$${\text{Tax}}_t=max(0,(EB{T}_t-NO{L}_{carry})\times t_c)$$

2.6 IS Template

Line Item (tỷ VND)	2023A	2024A	2025E	2026E	2027E
Revenue	52,349	61,300	70,896	81,539	93,142
(-) COGS	(38,196)	(44,136)	(50,437)	(57,492)	(65,199)
Gross Profit	14,153	17,164	20,459	24,047	27,943
Gross Margin	27.0%	28.0%	28.9%	29.5%	30.0%
(-) SG&A	(3,930)	(4,600)	(5,317)	(5,947)	(6,518)
(-) R&D	—	—	—	—	—
(-) D&A	(2,450)	(2,800)	(3,190)	(3,669)	(4,191)
(-) Other OpEx	(743)	(1,064)	(1,064)	(1,223)	(1,397)
EBIT	9,030	8,700	10,888	13,208	15,837
(-) Interest Expense	(650)	(700)	(from schedule)	(from schedule)	(from schedule)
(+) Interest Income	400	450	(from schedule)	(from schedule)	(from schedule)
EBT	8,780	8,450	—	—	—
(-) Tax	(1,317)	(1,268)	—	—	—
Net Income	7,463	7,183	—	—	—

(Projected Interest & EBT hoàn thành sau khi build Debt Schedule — xem Phần 3.3)

---

Phần 3 — Balance Sheet Build

3.1 Working Capital: AR (DSO), Inventory (DIO), AP (DPO)

Working Capital được drive bởi days ratios — liên kết IS (Revenue, COGS) với BS.

Accounts Receivable:

$$A{R}_t=\frac{DS{O}_t\times Revenu{e}_t}{365}$$

Inventory:

$$Inventor{y}_t=\frac{DI{O}_t\times COG{S}_t}{365}$$

Accounts Payable:

$$A{P}_t=\frac{DP{O}_t\times COG{S}_t}{365}$$

Net Working Capital (NWC):

$$NW{C}_t=A{R}_t+Inventor{y}_t-A{P}_t$$

Change in Working Capital → goes to CFO:

$$\mathrm{\Delta }W{C}_t=NW{C}_t-NW{C}_{t-1}$$

WC Item	Driver	2024A	2025E	2026E	2027E
DSO (days)	Assumption	71	70	69	68
DIO (days)	Assumption	12	12	12	12
DPO (days)	Assumption	58	59	60	60
AR	DSO × Rev / 365	12,000	13,594	15,413	17,352
Inventory	DIO × COGS / 365	1,500	1,658	1,890	2,143
AP	DPO × COGS / 365	7,000	8,153	9,448	10,716
NWC	AR + Inv - AP	6,500	7,099	7,855	8,779
ΔWC	NWC_t - NWC_		599	756	924

3.2 PP&E Schedule: Begin + CapEx - Depreciation = End

$$PP\mathrm{\&}{E}_{end,t}=PP\mathrm{\&}{E}_{begin,t}+CapE{x}_t-Depreciatio{n}_t$$

Trong đó:

• $PP\mathrm{\&}{E}_{begin,t}=PP\mathrm{\&}{E}_{end,t-1}$ (link từ năm trước)
• $CapE{x}_t$ = từ Assumptions (thường % Revenue)
• $Depreciatio{n}_t$ = từ D&A Schedule (useful life hoặc % PP&E)

PP&E (tỷ VND)	2024A	2025E	2026E	2027E
Beginning PP&E	10,200	12,100	12,810	13,626
(+) CapEx	4,700	3,900	4,485	5,123
(-) Depreciation	(2,800)	(3,190)	(3,669)	(4,191)
Ending PP&E	12,100	12,810	13,626	14,558

3.3 Debt Schedule: Begin + Borrow - Repay = End

$$Deb{t}_{end,t}=Deb{t}_{begin,t}+{\text{New Issuance}}_t-{\text{Repayment}}_t$$

Debt Schedule (tỷ VND)	2024A	2025E	2026E	2027E
Beginning Total Debt	8,000	8,500	8,500	8,500
(+) New Issuance	1,500	1,000	500	500
(-) Repayment	(1,000)	(1,000)	(500)	(500)
Ending Total Debt	8,500	8,500	8,500	8,500
Average Debt	8,250	8,500	8,500	8,500
Interest Rate	7.5%	7.5%	7.5%	7.5%
Interest Expense	(619)	(638)	(638)	(638)

3.4 Equity Rollforward

$${\text{Equity}}_{end,t}={\text{Equity}}_{begin,t}+{\text{Net Income}}_t-{\text{Dividends}}_t+{\text{Other Comprehensive Income}}_t$$

Equity (tỷ VND)	2024A	2025E	2026E	2027E
Beginning Equity	28,700	34,600	—	—
(+) Net Income	7,183	—	—	—
(-) Dividends	(1,283)	—	—	—
Ending Equity	34,600	—	—	—

3.5 Plug: Cash or Debt?

Trong 3-Statement Model, 1 trong 2 phải là plug variable (biến cân bằng) để BS balance:

Scenario	Plug Variable	Logic
Surplus cash	Cash = Total Assets cần – Tổng assets khác	Excess cash tích lũy
Cash deficit	Revolver (Debt) = Funding gap	Vay thêm để tài trợ

$${\text{Cash}}_{plug}=\text{Total L\&E}-\text{Non-cash Assets}$$

hoặc

$${\text{Revolver}}_{plug}=\text{Total Assets}-\text{Non-revolver L\&E}$$

Best practice: Dùng MIN/MAX logic: nếu Cash ≥ 0 → Cash là plug; nếu Cash < 0 → Revolver kicks in.

---

Phần 4 — Cash Flow Statement Build

4.1 CFO — Cash from Operations

$$\boxed{{\displaystyle CFO=NI+\text{Non-cash charges}+\mathrm{\Delta }WC}}$$

Item	Source
Net Income	IS (bottom line)
(+) D&A	PP&E Schedule
(+) Amortization	Intangibles Schedule
(+) Stock-based Comp	Assumption
(-) Gains on sale	IS Other income
(-/+) ΔAR	WC Schedule (increase = cash outflow)
(-/+) ΔInventory	WC Schedule
(+/-) ΔAP	WC Schedule (increase = cash inflow)
(+/-) ΔOther WC	WC Schedule
= CFO

4.2 CFI — Cash from Investing

$$\boxed{{\displaystyle CFI=-CapEx+\text{Proceeds from Asset Sales}-\text{Acquisitions}+\text{Other}}}$$

Item	Source
(-) CapEx	PP&E Schedule
(+) Asset sale proceeds	Assumption
(-) Acquisitions	M&A schedule (if any)
= CFI

Simplified cho most models:

$$CFI=-CapEx$$

4.3 CFF — Cash from Financing

$$\boxed{{\displaystyle CFF=\mathrm{\Delta }Debt+\text{Equity Issuance}-\text{Dividends}-\text{Share Buybacks}}}$$

Item	Source
(+) Debt issuance	Debt Schedule
(-) Debt repayment	Debt Schedule
(+) Equity issuance	Assumption
(-) Dividends	Equity Rollforward
(-) Share buybacks	Assumption
= CFF

4.4 Total CF → Ending Cash → Link back to BS

$${\text{Ending Cash}}_t={\text{Beginning Cash}}_t+CF{O}_t+CF{I}_t+CF{F}_t$$$$\boxed{{\displaystyle \text{CF Ending Cash}=\text{BS Cash}✓}}$$

4.5 CF Template

Cash Flow (tỷ VND)	2025E	2026E	2027E
Net Income	—	—	—
(+) D&A	3,190	3,669	4,191
(-) ΔWC	(599)	(756)	(924)
CFO	—	—	—
(-) CapEx	(3,900)	(4,485)	(5,123)
CFI	(3,900)	(4,485)	(5,123)
(+) Debt issuance	1,000	500	500
(-) Debt repayment	(1,000)	(500)	(500)
(-) Dividends	—	—	—
CFF	—	—	—
Net Change in Cash	—	—	—
Beginning Cash	22,000	—	—
Ending Cash	—	—	—
→ Must = BS Cash	✅	✅	✅

---

Phần 5 — Handling Circularity: Interest ↔ Debt ↔ Cash

5.1 Vấn đề Circular Reference

Đây là thách thức lớn nhất khi build 3-Statement Model:

Interest Expense (IS)
    ↓ affects
Net Income (IS)
    ↓ flows to
Cash Flow Statement (CF)
    ↓ determines
Ending Cash (BS)
    ↓ affects
Net Debt / Revolver Balance (BS)
    ↓ determines
Average Debt
    ↓ calculates
Interest Expense (IS)    ← CIRCULAR!

Tóm tắt vòng lặp:

$$\text{Interest}\to NI\to \text{Cash}\to \text{Debt}\to \text{Interest}$$

Excel mặc định không chấp nhận circular reference → sẽ báo lỗi.

5.2 Giải pháp 1: Iterative Calculation (Recommended)

Bật Iterative Calculation trong Excel:

File → Options → Formulas → ✅ Enable iterative calculation
    Maximum Iterations: 100
    Maximum Change: 0.001

Excel sẽ iterate (lặp đi lặp lại) cho đến khi kết quả converge (hội tụ).

Ưu điểm: Đơn giản, chính xác, investment banks đều dùng. Nhược điểm: Nếu model bị lỗi → iteration chạy vô hạn → giá trị sai.

5.3 Giải pháp 2: VBA Macro

Sub SolveCircularity()
    Dim i As Integer
    For i = 1 To 100
        Range("InterestExpense").Value = Range("AvgDebt").Value * Range("IntRate").Value
        ' Recalc other cells
        Application.Calculate
    Next i
End Sub

Ưu điểm: Kiểm soát tốt hơn. Nhược điểm: Phức tạp, khó maintain.

5.4 Giải pháp 3: Circularity Toggle Switch (Best Practice)

Tạo toggle switch trên tab Assumptions:

Cell	Value	Ý nghĩa
Circ_Switch	1	Circular ON — Interest linked to Avg Debt
Circ_Switch	0	Circular OFF — Interest = 0 (for debugging)

Formula Interest Expense:

= IF(Circ_Switch = 1, Avg_Debt * Interest_Rate, 0)

Workflow: Khi build model → set Switch = 0. Khi hoàn thành → set Switch = 1 → bật Iterative Calc → kiểm tra convergence.

5.5 Kiểm tra Convergence

Sau khi bật Circular:

Check	Cách kiểm	Expected
BS Balance	A - L - E	= 0 (hoặc < 0.01)
Interest hợp lý	Interest / Avg Debt	≈ Interest Rate
Cash không âm	Ending Cash	≥ 0
Iteration stable	Thay đổi 1 input, xem result stable	Không dao động

---

Tổng kết: Flow diagram đầy đủ

┌─────────────┐    ┌──────────────┐    ┌──────────────┐
│  ASSUMPTIONS │───→│    IS Build   │───→│   BS Build   │
│  • Rev growth│    │ • Revenue     │    │ • WC Schedule│
│  • Margins   │    │ • COGS        │    │ • PP&E Sched │
│  • CapEx %   │    │ • OpEx        │    │ • Debt Sched │
│  • DSO/DIO   │    │ • D&A         │    │ • Eq Rollfwd │
│  • Int Rate  │    │ • Interest*   │    │ • Plug Cash  │
│  • Tax Rate  │    │ • Tax → NI    │    │ • A = L + E ✓│
└─────────────┘    └──────┬───────┘    └──────┬───────┘
                          │                    │
                          ↓                    ↓
                   ┌──────────────────────────────────┐
                   │          CF Build                 │
                   │ CFO = NI + D&A + ΔWC             │
                   │ CFI = -CapEx                      │
                   │ CFF = ΔDebt - Div                │
                   │ End Cash → link BS Cash    ✓     │
                   └──────────────────────────────────┘
                          │
                   * Circular: Interest ↔ Debt ↔ Cash

---

Công thức quan trọng

#	Công thức	Mô tả
1	$Revenu{e}_t=Revenu{e}_{t-1}\times (1+g)$	Revenue growth
2	$A{R}_t=\frac{DSO\times Revenu{e}_t}{365}$	Accounts Receivable from DSO
3	$PP\mathrm{\&}{E}_{end}=PP\mathrm{\&}{E}_{begin}+CapEx-D\mathrm{\&}A$	PP&E rollforward
4	$Deb{t}_{end}=Deb{t}_{begin}+Issue-Repay$	Debt rollforward
5	$CFO=NI+D\mathrm{\&}A+\mathrm{\Delta }WC$	Cash from Operations
6	$CFI=-CapEx$	Cash from Investing
7	$CFF=\mathrm{\Delta }Debt-Div$	Cash from Financing
8	$\text{End Cash}=\text{Begin Cash}+CFO+CFI+CFF$	Cash rollforward
9	$\text{Interest}=\text{Avg Debt}\times r$	Interest from Debt Schedule
10	$A=L+E$	Balance Sheet must balance

---

Keywords

3-Statement Model · Integrated Financial Model · Revenue Build · Operating Model · Debt Schedule · Working Capital · DSO · DIO · DPO · PP&E Schedule · Circular Reference · Iterative Calculation · Toggle Switch · Balance Check · Color Coding · Investment Banking

---

Tài liệu tham khảo

1. Rosenbaum & Pearl — Investment Banking: Valuation, LBOs, M&A (3rd Edition, Wiley)
2. Corporate Finance Institute (CFI) — FMVA 3-Statement Modeling Course
3. Wall Street Prep (WSP) — Financial Modeling Best Practices
4. McKinsey & Company — Valuation: Measuring and Managing the Value of Companies (7th Ed.)
5. Macabacus — Financial Modeling Guidelines & Templates

---

Điều hướng Buổi 10

Tài liệu	Mô tả
📖 Bài giảng chính	Nội dung lý thuyết 3-Statement Model đầy đủ
📝 Blog	3-Statement Model: File Excel mà IB Analyst mất 80 giờ/tuần
📊 Case Study	Apple (Goldman Sachs), FPT BCTC thực, WSP/CFI comparison
📚 Standards	WSP, CFI, Macabacus, Goldman Sachs modeling standards
🔧 Workshop	Build 3-Statement Model — FPT 3 năm hoàn chỉnh
🎮 Game	Model Error Hunt — Tìm & sửa 8 lỗi ẩn trong model