Standards & Frameworks — Buổi 10: 3-Statement Financial Modeling

"A model that cannot be audited is a model that cannot be trusted." — Wall Street Prep

Tổng quan các Frameworks

Buổi 10 tham chiếu 4 frameworks chuyên nghiệp cho 3-Statement Modeling:

#	Framework	Tổ chức	Ứng dụng chính
1	WSP Financial Modeling Standards	Wall Street Prep	IB / PE Interview & On-the-Job
2	CFI FMVA Modeling Framework	Corporate Finance Institute	FMVA Certification
3	Macabacus Modeling Best Practices	Macabacus (Excel Add-in)	Formatting & Audit
4	Goldman Sachs Internal Standards	Goldman Sachs	Bulge Bracket IB

1. Wall Street Prep (WSP) — Financial Modeling Standards

1.1 Overview

Wall Street Prep là nền tảng training được hơn 200 banks và PE firms sử dụng (including Goldman Sachs, Morgan Stanley, Blackstone, KKR). WSP standards là de facto industry standard cho 3-Statement Modeling.

1.2 Model Architecture — WSP Standard

Tab Structure (7–10 tabs):

Tab	Position	Color	Content
Cover	1	Blue	Company, date, version, analyst
Assumptions	2	Green	All hard-coded inputs
IS	3	Yellow	Income Statement
BS	4	Yellow	Balance Sheet
CF	5	Yellow	Cash Flow Statement
Schedules	6	Orange	WC, D&A, Debt, PP&E
DCF	7	Red	Valuation (if needed)
Sensitivity	8	Purple	Data tables, scenarios
Output	9	Black	Summary, charts

1.3 WSP — Formatting Rules

Rule	Standard	Rationale
Blue font	All hard-coded inputs	Instantly identify what can change
Black font	All formulas	Logic / calculation
Green font	Cross-sheet links	Track dependencies
Red font	Error flags	Immediate attention
Italic	Historical data	Distinguish from projected
Bold	Subtotals & totals	Visual hierarchy
Yellow fill	Key output cells	Draw attention
No merged cells	Ever	Breaks copy/paste & formulas
No hidden rows/columns	Ever	Model must be fully transparent

1.4 WSP — Sign Convention

Item	Sign in Model	Display
Revenue	Positive (+)	44,017
COGS	Positive (+)	32,449 (shown as (32,449) with format)
Expenses	Positive (+)	Use `()` or `−` in display
Assets	Positive (+)
Liabilities	Positive (+)
Cash outflow (CapEx)	Positive (+)	Subtract in FCFF formula

WSP convention: Nhập tất cả là positive → dùng formulas để trừ. Tránh "double negative" errors.

1.5 WSP — Interest Expense (Circular)

WSP sử dụng Average Balance Method:

{Interest Expense}_{t} = \frac{D e b t_{t - 1} + D e b t_{t}}{2} \times r

Implementation:

Tạo Circ_Switch cell trên Assumptions tab (1 = ON, 0 = OFF)
Interest formula: =IF(Circ_Switch, (Debt_begin + Debt_end)/2 * Rate, 0)
Build entire model với Switch = 0
Khi complete → Switch = 1, bật Iterative Calculation
Verify convergence: BS Balance = 0

1.6 WSP — Error Checks

WSP yêu cầu error check row cuối mỗi sheet:

Check	Formula	Pass
BS Balance	`=Total Assets − Total L&E`	0
CF Cash = BS Cash	`=CF_EndCash − BS_Cash`	0
IS NI = CF NI	`=IS_NI − CF_StartNI`	0
Debt Sched = BS Debt	`=Sched_EndDebt − BS_TotalDebt`	0
PP&E Sched = BS PP&E	`=Sched_EndPPE − BS_PPE`	0
Grand Check	`=IF(SUM(all)=0, "PASS", "FAIL")`	PASS

1.7 WSP — Version Control

Element	Standard
File name	`[Company]_3SM_v[Major].[Minor]_[YYYYMMDD].xlsx`
Example	`FPT_3SM_v2.3_20260218.xlsx`
Change log	Tab riêng: Date, Author, Description of change
Backup	Save As trước mỗi major revision

2. CFI FMVA — Financial Modeling & Valuation Analyst

2.1 Overview

CFI's FMVA (Financial Modeling & Valuation Analyst) certification là chương trình rộng nhất cho financial modeling. Hơn 1 triệu learners globally. CFI approach thiên về accessibility và learning curve.

2.2 CFI — Model Architecture

Tab Structure (5–8 tabs, simpler):

Tab	Content	Note
Assumptions	All inputs	Same as WSP
Historical	3–5 năm historical IS+BS+CF	Separate from projection
IS	Projected Income Statement	5 năm
BS	Projected Balance Sheet	5 năm
CF	Projected Cash Flow	5 năm
Supporting	WC, D&A, CapEx schedules	Combined into 1 tab
Valuation	DCF (if applicable)

2.3 CFI — Revenue Build

CFI đề xuất 3-layer revenue build:

Layer 1 — Top-down:

R e v e n u e_{t} = R e v e n u e_{t - 1} \times (1 + g)

Layer 2 — Segment split:

R e v e n u e_{t} = \sum_{s e g} R e v e n u e_{s e g, t}

Layer 3 — Unit economics (advanced):

R e v e n u e_{s e g} = U n i t s \times A S P

CFI khuyên: Start with Layer 1, refine to Layer 2 hoặc 3 khi có đủ data.

2.4 CFI — Interest (No Circular)

CFI sử dụng Beginning Balance Method để tránh circular reference:

{Interest Expense}_{t} = D e b t_{t - 1} \times r

Ưu điểm:

Không cần iterative calculation
Dễ build, dễ debug
Phù hợp cho người mới học

Nhược điểm:

Kém chính xác khi debt thay đổi lớn trong kỳ
Không phản ánh mid-period borrowing/repayment

So sánh Accuracy:

Giả sử: $D e b t_{b e g i n} = 10,000$ , $D e b t_{e n d} = 6,000$ , $r = 8 %$

Method	Formula	Interest
CFI (Beginning)	$10,000 \times 8 %$	800
WSP (Average)	$\frac{10,000 + 6,000}{2} \times 8 %$	640
Difference		160 (20%)

Khi debt giảm 40% trong kỳ, sai lệch lên đến 20% — rất significant cho LBO models.

2.5 CFI — Working Capital

CFI dùng Days-based approach (giống WSP):

Metric	Formula	Driver
$D S O$	$\frac{A R}{R e v e n u e} \times 365$	Operational efficiency
$D I O$	$\frac{I n v}{C O G S} \times 365$	Inventory management
$D P O$	$\frac{A P}{C O G S} \times 365$	Supplier payment terms
$C C C$	$D S O + D I O - D P O$	Cash Conversion Cycle

Projection:

A R_{t} = \frac{D S O_{t} \times R e v e n u e_{t}}{365}, I n v_{t} = \frac{D I O_{t} \times C O G S_{t}}{365}, A P_{t} = \frac{D P O_{t} \times C O G S_{t}}{365}

2.6 CFI — Balance Sheet Plug

CFI sử dụng Cash as sole plug:

{Cash}_{e n d} = Beginning Cash + C F O + C F I + C F F

→ Cash được tính trong CF Statement, rồi link ngược lên BS.

CFI không dùng Revolver plug (WSP có). Giữ model đơn giản hơn.

3. Macabacus — Modeling Best Practices & Formatting

3.1 Overview

Macabacus là Excel add-in phổ biến trong IB, cung cấp tools cho formatting, audit, và model quality control. Hơn 250,000 finance professionals sử dụng.

3.2 Macabacus — Color Coding (Extended)

Macabacus mở rộng color coding chuẩn:

Font Color	RGB	Ý nghĩa
Blue	(0, 0, 255)	Hard-coded input
Black	(0, 0, 0)	Formula — same sheet
Green	(0, 128, 0)	Formula — link other sheet
Purple	(128, 0, 128)	Formula — link other workbook
Red	(255, 0, 0)	Error / Warning / Check
Brown	(128, 64, 0)	Circular reference formula

3.3 Macabacus — Audit Tools

Tool	Chức năng
Formula Audit	Highlight tất cả cells không theo color convention
Precedent/Dependent	Trace links giữa cells và sheets
Error Check	Tự động scan cho common errors
Format Painter	Apply consistent formatting across tabs
Cell Lining	Underline totals, double-underline grand totals

3.4 Macabacus — Number Formatting Standards

Item	Format	Example
Revenue, costs	`#,##0` hoặc `#,##0.0`	44,017
Margins	`0.0%`	27.0%
Price per share	`#,##0`	85,600
Ratios	`0.00x`	3.45x
Days	`0`	70
Negative	`(#,##0)` in red	(32,449)
Dates	`MMM-YY`	Dec-25

3.5 Macabacus — Model Structure Rules

Rule	Chi tiết
One row, one item	Không nhập 2 items trên cùng row
No merged cells	Bất di bất dịch
Time left → right	Column B = earliest year, last column = latest year
Units in column A	Row header bao gồm unit: "Revenue (tỷ VND)"
Source in last column	Ghi nguồn: "Assumption", "IS link", "BS link"
Consistent row ordering	IS → BS → CF → Schedules follow same line item sequence

4. Goldman Sachs — Internal Modeling Standards

4.1 Overview

Goldman Sachs không publish standards ra ngoài, nhưng từ các training programs (và WSP partnership), key principles được biết rộng rãi:

4.2 GS — "Three Pillars" of Modeling

Pillar	Standard
Transparency	Mọi cell có thể trace back nguồn gốc. Không "black box" formulas
Flexibility	Thay đổi 1 assumption → toàn bộ model cập nhật. Scenario switch phải instant
Accuracy	Balance check = 0. Error flags = ✅. Circularity handled properly

4.3 GS — Revenue Build Standard

Goldman Sachs revenue models thường có 3 levels of granularity:

Level 1: Total Revenue = Last Year × (1 + Growth)
    ↓ refine to
Level 2: Revenue by Segment = Σ Segment Revenue
    ↓ refine to
Level 3: Revenue by Segment = Units × ASP × Utilization

Khi nào dùng	Level
Pitchbook / quick analysis	Level 1
Client presentation / formal model	Level 2
Deep dive / due diligence	Level 3

4.4 GS — Debt Schedule Standard

Goldman Sachs Debt Schedule bao gồm:

Component	Chi tiết
Revolver	Draw/repay based on cash need (plug variable)
Term Loan A	Mandatory amortization schedule
Term Loan B	Bullet maturity, 1% annual amort
Senior Notes	Fixed coupon, bullet maturity
Subordinated	Higher coupon, PIK option
Total Debt	Sum of all tranches
Interest (per tranche)	Avg balance × rate for each

Total Interest = \sum_{i} \frac{D e b t_{i, b e g i n} + D e b t_{i, e n d}}{2} \times r_{i}

4.5 GS — Model Deliverables

Khi model hoàn thành, Goldman Sachs yêu cầu:

Deliverable	Mô tả
Model file	`.xlsx` with all tabs, error checks pass
Output page	1-page summary: Key metrics, valuation range
Assumption book	Separate doc justifying each key assumption
Sensitivity tables	WACC × Growth, Revenue × Margin
Scenario analysis	Base / Bull / Bear with probabilities

So sánh tổng hợp 4 Frameworks

Feature	WSP	CFI	Macabacus	Goldman Sachs
Target audience	IB/PE candidates	Broad finance	IB formatting	Bulge bracket bankers
Tabs	7–10	5–8	N/A (formatting)	10+
Color coding	Blue/Black/Green	Blue/Black	Extended (6 colors)	Blue/Black/Green
Interest method	Average (circular)	Beginning (no circular)	N/A	Average (circular)
Circular handling	Toggle + Iteration	Avoid	Audit tool	Toggle + Iteration
Revenue build	Segment bottom-up	Top-down → segment	N/A	3-level granularity
BS plug	Cash or Revolver	Cash only	N/A	Revolver (primary)
Error checks	Per-sheet row	Dedicated tab	Auto-scan	Per-sheet + Grand
Formatting rigor	High	Moderate	Very High	Very High
Accessibility	Online course ($)	Online course ($)	Excel add-in ($)	Internal training
Best for	IB interview prep	Self-study	Formatting & audit	On-the-job

Bảng tham chiếu nhanh: 3-Statement Formulas by Standard

Formula	WSP	CFI
Revenue	$\sum_{s e g} U n i t s \times A S P$	$R e v_{t - 1} \times (1 + g)$
COGS	$R e v \times (1 - G M %)$	Same
D&A	$% \times P P & E_{b e g i n}$	$% \times R e v e n u e$
Interest	$\frac{D_{t - 1} + D_{t}}{2} \times r$	$D_{t - 1} \times r$
AR	$\frac{D S O \times R e v}{365}$	Same
PP&E	$P P E_{b e g i n} + C a p E x - D & A$	Same
Debt	$D_{b e g i n} + I s s u e - R e p a y$	Same
CFO	$N I + D & A + Δ W C$	Same
Plug	$Cash or Revolver$	$Cash only$

Tài liệu tham khảo chi tiết

Sách

Rosenbaum, J. & Pearl, J. — Investment Banking: Valuation, LBOs, M&A (3rd Ed., Wiley)
Pignataro, P. — Financial Modeling and Valuation (Wiley Finance)
Benninga, S. — Financial Modeling (4th Ed., MIT Press)
Tjia, J. — Building Financial Models (McGraw-Hill)

Online Resources

Wall Street Prep — wallstreetprep.com/knowledge/3-statement-model/
Corporate Finance Institute — corporatefinanceinstitute.com/resources/financial-modeling/
Macabacus — macabacus.com/best-practices
Mergers & Inquisitions — mergersandinquisitions.com/3-statement-model/

Templates

WSP 3-Statement Template (course material)
CFI FMVA 3-Statement Template (course material)

Đ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

Standards & Frameworks — Buổi 10: 3-Statement Financial Modeling ​

Tổng quan các Frameworks ​

1. Wall Street Prep (WSP) — Financial Modeling Standards ​

1.1 Overview ​

1.2 Model Architecture — WSP Standard ​

1.3 WSP — Formatting Rules ​

1.4 WSP — Sign Convention ​

1.5 WSP — Interest Expense (Circular) ​

1.6 WSP — Error Checks ​

1.7 WSP — Version Control ​

2. CFI FMVA — Financial Modeling & Valuation Analyst ​

2.1 Overview ​

2.2 CFI — Model Architecture ​

2.3 CFI — Revenue Build ​

2.4 CFI — Interest (No Circular) ​

2.5 CFI — Working Capital ​

2.6 CFI — Balance Sheet Plug ​

3. Macabacus — Modeling Best Practices & Formatting ​

3.1 Overview ​

3.2 Macabacus — Color Coding (Extended) ​

3.3 Macabacus — Audit Tools ​

3.4 Macabacus — Number Formatting Standards ​

3.5 Macabacus — Model Structure Rules ​

4. Goldman Sachs — Internal Modeling Standards ​

4.1 Overview ​

4.2 GS — "Three Pillars" of Modeling ​

4.3 GS — Revenue Build Standard ​

4.4 GS — Debt Schedule Standard ​

4.5 GS — Model Deliverables ​

So sánh tổng hợp 4 Frameworks ​

Bảng tham chiếu nhanh: 3-Statement Formulas by Standard ​

Tài liệu tham khảo chi tiết ​

Sách ​

Online Resources ​

Templates ​

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