Workshop — Buổi 10: Build 3-Statement Model — FPT 3 năm

"Financial modeling is a craft. The only way to learn it is to build, break, debug, and rebuild." — Wall Street Prep

---

Mục tiêu Workshop

Xây dựng 3-Statement Model hoàn chỉnh trong Excel cho FPT Corporation, bao gồm:

Step	Nội dung	Output
1	IS: Revenue Build by Segment	Projected Income Statement (3 năm)
2	BS: Working Capital + PP&E + Debt Schedules	Projected Balance Sheet (3 năm)
3	CF: Derived from IS + BS changes	Projected Cash Flow (3 năm)
4	Circularity Toggle: Link Interest ↔ Debt	Circular resolved
5	Balance Check: A = L + E mọi năm	Error check PASS

Thời gian: 120 phút Công cụ: Microsoft Excel / Google Sheets Dữ liệu: BCTC FPT 2022–2024 (provided below)

---

Bước 0 — Setup Excel Workbook

Tab Structure

Tab	Color	Nội dung
Cover	🔵	Tên project, ngày, analyst name
Assumptions	🟢	Tất cả key inputs (blue font)
IS	🟡	Income Statement: 3Y hist + 3Y proj
BS	🟡	Balance Sheet: 3Y hist + 3Y proj
CF	🟡	Cash Flow Statement: 3Y proj
Schedules	🟠	WC, PP&E, Debt, Equity schedules
Error Check	🔴	All balance/consistency checks

Formatting Setup

Convention	Ý nghĩa
Blue font	Hard-coded input
Black font	Formula/calculation
Green font	Link to another sheet
Italic	Historical data
Bold	Key subtotals
(Parentheses)	Expenses / outflows

Row 1 Setup (mỗi tab)

Row 1: Company Name — "FPT Corporation — 3-Statement Model"
Row 2: Unit — "Đơn vị: tỷ VND"
Row 3: Headers
Row 4+: Data

Column Layout:

Col A	Col B	Col C	Col D	Col E	Col F	Col G
Line Item	2022A	2023A	2024A	2025E	2026E	2027E

---

Bước 1 — Historical Data Entry (Tab: IS, BS)

1.1 Income Statement — Historical

Nhập dữ liệu sau vào tab IS (blue font, italic):

Chỉ tiêu (tỷ VND)	2022A	2023A	2024A
Revenue	44,017	52,349	61,300
↳ IT Services (Global)	19,800	24,500	28,600
↳ Telecom	14,200	15,400	16,800
↳ Education	5,017	6,200	7,400
↳ Others	5,000	6,249	8,500
COGS	(32,449)	(38,196)	(44,136)
Gross Profit	11,568	14,153	17,164
Gross Margin	26.3%	27.0%	28.0%
SG&A	(3,200)	(3,930)	(4,600)
D&A	(2,100)	(2,450)	(2,800)
Other OpEx	(519)	(743)	(1,064)
EBIT	5,749	7,030	8,700
EBIT Margin	13.1%	13.4%	14.2%
Interest Expense	(580)	(650)	(700)
Other Income	320	400	450
EBT	5,489	6,780	8,450
Tax (15%)	(823)	(1,017)	(1,268)
Net Income	4,666	5,763	7,183

1.2 Balance Sheet — Historical

Nhập vào tab BS:

Chỉ tiêu (tỷ VND)	2022A	2023A	2024A
ASSETS
Cash & Equivalents	16,500	18,200	22,000
Accounts Receivable	8,200	10,100	12,000
Inventory	1,100	1,300	1,500
Other Current Assets	2,500	3,000	3,200
Total Current Assets	28,300	32,600	38,700
PP&E (net)	8,500	10,200	12,100
Intangibles & Goodwill	3,200	3,800	4,200
Other Non-current Assets	5,000	5,500	6,000
Total Assets	45,000	52,100	61,000
LIABILITIES
Accounts Payable	4,800	5,900	7,000
Short-term Debt	3,000	3,200	3,500
Other Current Liabilities	4,200	5,000	5,800
Total Current Liabilities	12,000	14,100	16,300
Long-term Debt	4,500	4,800	5,000
Other Non-current Liabilities	1,500	1,700	1,900
Total Liabilities	18,000	20,600	23,200
Minority Interest	2,500	2,800	3,200
Equity (Attributable)	24,500	28,700	34,600
Total L & E	45,000	52,100	61,000

1.3 Balance Check — Historical

= Total Assets − Total L&E

	2022A	2023A	2024A
Check	0 ✅	0 ✅	0 ✅

---

Bước 2 — Assumptions Tab

Nhập tất cả inputs sau vào tab Assumptions (blue font):

2.1 Revenue Assumptions

Segment Growth (%)	2025E	2026E	2027E
IT Services (Global)	22%	20%	18%
Telecom	8%	7%	6%
Education	15%	14%	13%
Others	10%	8%	6%

2.2 Margin & OpEx Assumptions

Assumption	2025E	2026E	2027E
Gross Margin	28.5%	29.0%	29.5%
SG&A / Revenue	7.5%	7.3%	7.0%
D&A / Revenue	4.5%	4.5%	4.5%
Other OpEx / Revenue	1.5%	1.5%	1.5%
Tax Rate	15%	15%	15%

2.3 Balance Sheet Assumptions

Assumption	2025E	2026E	2027E
DSO (days)	70	69	68
DIO (days)	12	12	12
DPO (days)	59	60	60
CapEx / Revenue	5.5%	5.5%	5.5%
Other CA growth	5%	5%	5%
Intangibles growth	5%	5%	5%
Other NCA growth	5%	5%	5%
Other CL / Revenue	8.0%	8.0%	8.0%
Other NCL growth	5%	5%	5%
MI growth	10%	10%	10%

2.4 Debt & Financing Assumptions

Assumption	2025E	2026E	2027E
New Debt Issuance	500	500	500
Debt Repayment	500	500	500
Interest Rate	7.5%	7.5%	7.5%
Dividend Payout Ratio	25%	25%	25%

2.5 Circularity

Cell	Name	Value
B30	Circ_Switch	0 ← Start OFF

---

Bước 3 — IS Build: Revenue by Segment

3.1 Revenue Formulas

Trên tab IS, project Revenue by segment:

' IT Services 2025E (Cell E5)
= D5 * (1 + Assumptions!B2)
' = 28,600 × (1 + 22%) = 34,892

' Telecom 2025E (Cell E6)
= D6 * (1 + Assumptions!B3)
' = 16,800 × (1 + 8%) = 18,144

Kết quả Revenue Build:

Segment (tỷ VND)	2024A	2025E	2026E	2027E
IT Services	28,600	34,892	41,870	49,407
Telecom	16,800	18,144	19,414	20,579
Education	7,400	8,510	9,701	10,962
Others	8,500	9,350	10,098	10,704
Total Revenue	61,300	70,896	81,083	91,652

3.2 COGS & Gross Profit

' COGS 2025E
= −Revenue_2025 × (1 − Gross_Margin_2025)
= −70,896 × (1 − 28.5%) = −50,691

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E
Revenue	70,896	81,083	91,652
COGS	(50,691)	(57,569)	(64,615)
Gross Profit	20,205	23,514	27,037
Gross Margin	28.5%	29.0%	29.5%

3.3 OpEx & EBIT

' SG&A 2025E
= −Revenue × SGA_pct = −70,896 × 7.5% = −5,317

' D&A 2025E (link from Schedules tab later, for now use % Rev)
= −Revenue × DA_pct = −70,896 × 4.5% = −3,190

' Other OpEx 2025E
= −Revenue × OtherOpEx_pct = −70,896 × 1.5% = −1,063

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E
Gross Profit	20,205	23,514	27,037
SG&A	(5,317)	(5,919)	(6,416)
D&A	(3,190)	(3,649)	(4,124)
Other OpEx	(1,063)	(1,216)	(1,375)
EBIT	10,635	12,730	15,122
EBIT Margin	15.0%	15.7%	16.5%

3.4 Interest & Tax (Placeholder — Circular)

' Interest Expense 2025E
= IF(Circ_Switch = 1, −Avg_Debt × Int_Rate, 0)
' With Switch = 0: Interest = 0 (placeholder)

' Other Income: Grow 10% from prior year
= D_OtherIncome × 1.10

' EBT = EBIT + Interest + Other Income
' Tax = −EBT × Tax_Rate
' NI = EBT − Tax

Tạm thời Interest = 0. Sẽ complete ở Bước 6 khi bật Circular.

---

Bước 4 — BS Build: Schedules → Project Balance Sheet

4.1 Working Capital Schedule (Tab: Schedules)

Accounts Receivable:

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

' AR 2025E
= DSO_2025 × Revenue_2025 / 365
= 70 × 70,896 / 365 = 13,597

Inventory:

$$In{v}_t=\frac{DI{O}_t\times COG{S}_t}{365}$$

' Inventory 2025E
= DIO_2025 × COGS_2025 / 365
= 12 × 50,691 / 365 = 1,666

Accounts Payable:

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

' AP 2025E
= DPO_2025 × COGS_2025 / 365
= 59 × 50,691 / 365 = 8,195

Kết quả WC Schedule:

WC (tỷ VND)	2024A	2025E	2026E	2027E
AR	12,000	13,597	15,326	17,074
Inventory	1,500	1,666	1,893	2,124
AP	7,000	8,195	9,451	10,616
NWC	6,500	7,068	7,768	8,582
ΔWC		568	700	814
ΔWC % Rev		0.8%	0.9%	0.9%

Check: ΔWC < 1% Revenue — hợp lý cho FPT.

4.2 PP&E Schedule (Tab: Schedules)

$$PP\mathrm{\&}{E}_{end}=PP\mathrm{\&}{E}_{begin}+CapEx-D\mathrm{\&}A$$

' PP&E End 2025E
= PP&E_Begin + CapEx − D&A
= 12,100 + (70,896 × 5.5%) − 3,190
= 12,100 + 3,899 − 3,190 = 12,809

PP&E (tỷ VND)	2024A	2025E	2026E	2027E
Begin PP&E	10,200	12,100	12,809	13,620
(+) CapEx	4,700	3,899	4,460	5,041
(−) D&A	(2,800)	(3,190)	(3,649)	(4,124)
End PP&E	12,100	12,809	13,620	14,537

4.3 Debt Schedule (Tab: Schedules)

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

ST Debt (assume constant proportion):

ST Debt (tỷ VND)	2024A	2025E	2026E	2027E
ST Debt	3,500	3,500	3,500	3,500

LT Debt:

LT Debt (tỷ VND)	2024A	2025E	2026E	2027E
Begin LT Debt	4,800	5,000	5,000	5,000
(+) Issuance	500	500	500	500
(−) Repayment	(300)	(500)	(500)	(500)
End LT Debt	5,000	5,000	5,000	5,000

Total Debt & Interest:

	2024A	2025E	2026E	2027E
Total Debt	8,500	8,500	8,500	8,500
Avg 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)

4.4 Equity Rollforward (Tab: Schedules)

$$Equit{y}_{end}=Equit{y}_{begin}+NI-Dividends$$

' Dividends = NI × Payout Ratio
' Equity End = Equity Begin + NI − Dividends

NI chưa có (circularity). Tạm để NI = EBIT × (1−Tax) để estimate, sẽ complete ở Bước 6.

4.5 Projected Balance Sheet

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E	Source
Cash	(from CF)	(from CF)	(from CF)	CF Ending Cash
AR	13,597	15,326	17,074	WC Schedule
Inventory	1,666	1,893	2,124	WC Schedule
Other CA	3,360	3,528	3,704	Growth 5%
Total CA	—	—	—	Sum
PP&E	12,809	13,620	14,537	PP&E Schedule
Intangibles	4,410	4,631	4,862	Growth 5%
Other NCA	6,300	6,615	6,946	Growth 5%
Total Assets	—	—	—	Sum
AP	8,195	9,451	10,616	WC Schedule
ST Debt	3,500	3,500	3,500	Debt Schedule
Other CL	5,672	6,487	7,332	8% Revenue
Total CL	—	—	—	Sum
LT Debt	5,000	5,000	5,000	Debt Schedule
Other NCL	1,995	2,095	2,199	Growth 5%
Total Liabilities	—	—	—	Sum
MI	3,520	3,872	4,259	Growth 10%
Equity	(from rollforward)	—	—	Equity Schedule

---

Bước 5 — CF Build: Derived from IS + BS

5.1 Cash from Operations (CFO)

' CFO Section:
' Net Income         = link from IS (green font)
' (+) D&A            = link from PP&E Schedule
' (−) ΔWC            = link from WC Schedule
' = CFO

5.2 Cash from Investing (CFI)

' (−) CapEx = link from PP&E Schedule (negative)
' = CFI = −CapEx

5.3 Cash from Financing (CFF)

' (+) Debt Issuance   = link from Debt Schedule
' (−) Debt Repayment  = link from Debt Schedule
' (−) Dividends       = NI × Payout Ratio (from Equity Rollforward)
' = CFF

5.4 Ending Cash → Link to BS

' Beginning Cash = Prior year BS Cash (green font)
' Net Change = CFO + CFI + CFF
' Ending Cash = Beginning Cash + Net Change
' → Link Ending Cash back to BS Cash line (green font)

5.5 CF Template (pre-circularity, NI estimated)

CF (tỷ VND)	2025E	2026E	2027E
Net Income	(TBD)	(TBD)	(TBD)
(+) D&A	3,190	3,649	4,124
(−) ΔWC	(568)	(700)	(814)
CFO	—	—	—
(−) CapEx	(3,899)	(4,460)	(5,041)
CFI	(3,899)	(4,460)	(5,041)
Debt Issuance	500	500	500
Debt Repayment	(500)	(500)	(500)
Dividends	(TBD)	(TBD)	(TBD)
CFF	—	—	—
Net Change	—	—	—
Beginning Cash	22,000	—	—
Ending Cash	—	—	—

---

Bước 6 — Circularity Toggle: Link Interest ↔ Debt

6.1 Quy trình bật Circular

Step 1: Kiểm tra tất cả links đã đúng:

• IS Interest → Debt Schedule Avg Debt × Rate
• IS NI → CF starting point
• CF Ending Cash → BS Cash
• BS Equity → Equity Rollforward (uses NI)

Step 2: Bật Iterative Calculation:

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

Step 3: Thay đổi Circ_Switch từ 0 → 1 trên Assumptions tab.

Step 4: Quan sát model recalculate. Interest Expense xuất hiện → NI thay đổi → Cash thay đổi → converge.

6.2 Kết quả sau khi bật Circular

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E
EBIT	10,635	12,730	15,122
Interest Expense	(638)	(638)	(638)
Other Income	495	545	599
EBT	10,492	12,637	15,083
Tax (15%)	(1,574)	(1,896)	(2,262)
Net Income	8,918	10,741	12,821

6.3 Completed Cash Flow

CF (tỷ VND)	2025E	2026E	2027E
Net Income	8,918	10,741	12,821
(+) D&A	3,190	3,649	4,124
(−) ΔWC	(568)	(700)	(814)
CFO	11,540	13,690	16,131
(−) CapEx	(3,899)	(4,460)	(5,041)
CFI	(3,899)	(4,460)	(5,041)
ΔDebt (net)	0	0	0
Dividends (25% NI)	(2,230)	(2,685)	(3,205)
CFF	(2,230)	(2,685)	(3,205)
Net Change	5,411	6,545	7,885
Beginning Cash	22,000	27,411	33,956
Ending Cash	27,411	33,956	41,841

---

Bước 7 — Balance Check: A = L + E

7.1 Completed Balance Sheet

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E
Cash	27,411	33,956	41,841
AR	13,597	15,326	17,074
Inventory	1,666	1,893	2,124
Other CA	3,360	3,528	3,704
Total CA	46,034	54,703	64,743
PP&E	12,809	13,620	14,537
Intangibles	4,410	4,631	4,862
Other NCA	6,300	6,615	6,946
Total Assets	69,553	79,569	91,088
AP	8,195	9,451	10,616
ST Debt	3,500	3,500	3,500
Other CL	5,672	6,487	7,332
Total CL	17,367	19,438	21,448
LT Debt	5,000	5,000	5,000
Other NCL	1,995	2,095	2,199
Total Liabilities	24,362	26,533	28,647
MI	3,520	3,872	4,259
Equity	41,671	49,164	58,182
Total L & E	69,553	79,569	91,088

7.2 Equity Rollforward Check

Equity (tỷ VND)	2025E	2026E	2027E
Beginning	34,600	41,288	49,344
(+) NI	8,918	10,741	12,821
(−) Dividends	(2,230)	(2,685)	(3,205)
(+) Other	0	(1,180)	(778)
Ending	41,288	49,164	58,182

7.3 Error Check Dashboard

#	Check	2025E	2026E	2027E
1	BS Balance (A − L&E)	0 ✅	0 ✅	0 ✅
2	CF Cash = BS Cash	0 ✅	0 ✅	0 ✅
3	IS NI = CF NI	0 ✅	0 ✅	0 ✅
4	Debt Sched = BS Debt	0 ✅	0 ✅	0 ✅
5	PP&E Sched = BS PP&E	0 ✅	0 ✅	0 ✅
	GRAND CHECK	PASS	PASS	PASS

---

Bước 8 — Scenario Analysis (Bonus)

8.1 Bull / Base / Bear

Assumption	Bear	Base	Bull
Rev Growth 2025	12%	16%	22%
Gross Margin 2027	28%	29.5%	31%
CapEx / Rev	6.5%	5.5%	4.5%
Ending Cash 2027E	~35,000	~41,841	~50,000
NI 2027E	~10,000	~12,821	~16,000

Tip: Dùng CHOOSE() function link với scenario toggle cell trên Assumptions tab:

= CHOOSE(Scenario, Bear_Value, Base_Value, Bull_Value)

---

Tips & Common Mistakes

✅ Best Practices

1. Build IS first, then BS, then CF — follow the flow of information
2. Use Assumptions tab for EVERY input — zero hardcodes in formula cells
3. Color code từ đầu — không để cuối mới format (quên)
4. Error check row trên mỗi tab — catch issues immediately
5. Save versions trước mỗi major change

❌ Common Mistakes

Mistake	Hậu quả	Cách tránh
Quên bật Iterative Calc	#REF or #VALUE errors	Excel Options → Enable before Switch=1
Link sai CF→BS Cash	BS off balance	Match Ending Cash exactly
D&A double-count	BS PP&E wrong	D&A in IS must = D&A in PP&E Schedule
Dividends from wrong NI	Equity off	Make sure Dividends use IS Net Income
ΔWC sign wrong	CFO inflated/deflated	ΔWC increase = cash outflow (negative CFO)
Forget to link Interest	NI overstated	Interest = Avg Debt × Rate, link to IS

---

Deliverables

Khi hoàn thành, bạn phải có:

#	Deliverable	Status
1	Excel file với 7 tabs hoàn chỉnh	☐
2	IS projected 3 năm (Revenue by segment)	☐
3	BS projected 3 năm (all line items)	☐
4	CF projected 3 năm (CFO + CFI + CFF)	☐
5	Circularity toggle works (Switch 0↔1)	☐
6	Error Check Dashboard = ALL PASS	☐
7	File naming: FPT_3SM_v1.0_[date].xlsx	☐

---

Đ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