Workshop — Buổi 05: Xây DCF Model hoàn chỉnh từ BCTC FPT

"The best way to learn valuation is to do valuation." — Aswath Damodaran

Mục tiêu Workshop

Xây dựng DCF Model hoàn chỉnh trong Excel, từ BCTC thực tế của FPT Corporation, bao gồm:

Step	Nội dung	Output
1	Thu thập & chuẩn hóa BCTC 3 năm	Clean financials
2	Revenue projection (5 năm)	Revenue forecast
3	FCFF calculation & projection	FCFF schedule
4	WACC estimation	Discount rate
5	Terminal Value (2 methods)	TV
6	EV → Equity Value → Price per Share	Target price
7	Sensitivity Table: WACC(8–12%) × g(1–4%)	Data table

Thời gian: 90 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, tác giả
Assumptions	🟢	Tất cả key inputs (WACC, g, margins...)
IS	🟡	Income Statement (3Y hist + 5Y proj)
BS	🟡	Balance Sheet (3Y hist + 5Y proj)
CF	🟡	Cash Flow Statement
Schedules	🟠	D&A, CapEx, WC, Debt schedules
DCF	🔴	FCFF, TV, EV, Equity Value
Sensitivity	🟣	Data Tables, Scenarios
Output	⚫	Summary, Football Field

Formatting Rules

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

Bước 1 — Thu thập & Chuẩn hóa BCTC FPT (3 năm)

1.1 Income Statement (Rút gọn)

Chỉ tiêu (tỷ VND)	2022	2023	2024E
Revenue	44,017	52,349	61,300
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)
Other Operating	(519)	(743)	(1,064)
EBIT	7,849	9,480	11,500
EBIT Margin	17.8%	18.1%	18.8%
Interest Expense	(580)	(650)	(700)
Other Income	320	400	450
EBT	7,589	9,230	11,250
Tax (15%)	(1,138)	(1,385)	(1,688)
Net Income	6,451	7,845	9,563
NI Margin	14.7%	15.0%	15.6%

1.2 Balance Sheet (Rút gọn)

Chỉ tiêu (tỷ VND)	2022	2023	2024E
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	5,000	5,500	6,000
Total Assets	45,000	52,100	61,000
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

1.3 Key Historical Metrics (Tab: Schedules)

Metric	2022	2023	2024E	Cách tính
Revenue Growth	23.4%	18.9%	17.1%	$\frac{R e v_{t} - R e v_{t - 1}}{R e v_{t - 1}}$
Gross Margin	26.3%	27.0%	28.0%	$\frac{G P}{R e v e n u e}$
EBIT Margin	17.8%	18.1%	18.8%	$\frac{E B I T}{R e v e n u e}$
NI Margin	14.7%	15.0%	15.6%	$\frac{N I}{R e v e n u e}$
D&A	2,100	2,450	2,800	Từ CF Statement
CapEx	2,500	3,100	3,700	Từ CF Statement
CapEx/Revenue	5.7%	5.9%	6.0%
D&A/Revenue	4.8%	4.7%	4.6%
DSO	68 days	70 days	71 days	$\frac{A R}{R e v e n u e} \times 365$
DIO	12 days	12 days	12 days	$\frac{I n v}{C O G S} \times 365$
DPO	54 days	56 days	58 days	$\frac{A P}{C O G S} \times 365$
CCC	26 days	26 days	25 days	$D S O + D I O - D P O$
Total Debt	7,500	8,000	8,500	ST + LT Debt
Net Debt	−9,000	−10,200	−13,500	Total Debt − Cash

FPT có Net Debt âm (cash > debt) — đây là đặc điểm quan trọng cho Equity Bridge.

Bước 2 — Revenue Projection (5 năm)

2.1 Setup Assumptions Tab

Nhập vào Excel (blue font = input):

Assumption	2025E	2026E	2027E	2028E	2029E
Revenue Growth	18%	17%	15%	12.5%	12%
Gross Margin	28.5%	29.0%	29.5%	30.0%	30.0%
SG&A / Revenue	7.5%	7.3%	7.0%	6.8%	6.5%
Other OpEx / Revenue	1.5%	1.5%	1.5%	1.5%	1.5%
Tax Rate	15%	15%	15%	15%	15%
CapEx / Revenue	5.5%	5.5%	5.5%	5.5%	5.5%
D&A / Revenue	4.5%	4.5%	4.5%	4.5%	4.5%
DSO	70	69	68	67	66
DIO	12	12	12	12	12
DPO	58	59	60	60	60

2.2 Revenue Projection (Formulas)

Trong Excel, tab IS, row Revenue:

Cell G4 (2025E): =F4*(1+Assumptions!B2)
Cell H4 (2026E): =G4*(1+Assumptions!C2)
...

Kết quả:

	2025E	2026E	2027E	2028E	2029E
Revenue (tỷ)	72,334	84,631	97,326	109,491	122,630

2.3 Projected Income Statement

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E	2028E	2029E
Revenue	72,334	84,631	97,326	109,491	122,630
COGS	(51,719)	(60,088)	(68,615)	(76,644)	(85,841)
Gross Profit	20,615	24,543	28,711	32,847	36,789
SG&A	(5,425)	(6,178)	(6,813)	(7,445)	(7,971)
Other OpEx	(1,085)	(1,269)	(1,460)	(1,642)	(1,839)
EBIT	14,105	17,096	20,438	23,760	26,979
EBIT Margin	19.5%	20.2%	21.0%	21.7%	22.0%

Bước 3 — FCFF Calculation & Projection

3.1 FCFF Formula trong Excel

FCFF = EBIT × (1 − Tax Rate) + D&A − CapEx − ΔWC

3.2 Working Capital Schedule

Accounts Receivable:

A R_{t} = \frac{D S O_{t} \times R e v e n u e_{t}}{365}

Inventory:

I n v_{t} = \frac{D I O_{t} \times C O G S_{t}}{365}

Accounts Payable:

A P_{t} = \frac{D P O_{t} \times C O G S_{t}}{365}

Net Working Capital:

N W C_{t} = A R_{t} + I n v_{t} - A P_{t}

Kết quả:

	2024E	2025E	2026E	2027E	2028E	2029E
AR	12,000	13,866	15,994	18,127	20,082	22,167
Inventory	1,500	1,699	1,976	2,256	2,520	2,823
AP	7,000	8,213	9,710	11,268	12,594	14,103
NWC	6,500	7,352	8,260	9,115	10,008	10,887
ΔWC		852	908	855	893	879

3.3 FCFF Projection

Chỉ tiêu (tỷ VND)	2025E	2026E	2027E	2028E	2029E
EBIT	14,105	17,096	20,438	23,760	26,979
(×) (1 − Tax 15%)
= NOPAT	11,989	14,532	17,372	20,196	22,932
(+) D&A	3,255	3,808	4,380	4,927	5,518
(−) CapEx	(3,978)	(4,655)	(5,353)	(6,022)	(6,745)
(−) ΔWC	(852)	(908)	(855)	(893)	(879)
= FCFF	10,414	12,777	15,544	18,208	20,826
FCFF Margin	14.4%	15.1%	16.0%	16.6%	17.0%

Check: FCFF margin tăng dần — hợp lý vì operating leverage và giảm ΔWC relative to revenue.

Bước 4 — WACC Estimation

4.1 Cost of Equity ( $K_{e}$ )

Sử dụng CAPM + Country Risk Premium (Damodaran approach):

K_{e} = R_{f} + β \times E R P + C R P

Component	Value	Source
$R_{f}$ (US 10Y Treasury)	4.0%	Market data
$E R P$ (Equity Risk Premium, US)	5.5%	Damodaran 2025 update
$β$ (FPT, levered)	0.85	Bloomberg / Regression
$C R P$ (Vietnam)	3.5%	Damodaran country risk

K_{e} = 4.0 % + 0.85 \times 5.5 % + 3.5 % = 4.0 % + 4.675 % + 3.5 % = 12.175 % \approx 12.2 %

4.2 Cost of Debt ( $K_{d}$ )

K_{d} (1 - t) = Interest Rate \times (1 - Tax Rate)

Component	Value
Average interest rate	7.0%
Tax rate	15%
$K_{d} (1 - t)$	5.95%

4.3 Capital Structure (Market Weights)

Component	Value (tỷ VND)	Weight
Equity (Market Cap)	~180,000	95.5%
Debt (Book Value)	8,500	4.5%
Total Capital	188,500	100%

4.4 WACC Calculation

W A C C = w_{e} \times K_{e} + w_{d} \times K_{d} (1 - t)

W A C C = 0.955 \times 12.2 % + 0.045 \times 5.95 %

W A C C = 11.65 % + 0.27 % = 11.92 % \approx 12.0 %

Lưu ý: WACC FPT khá cao do CRP Vietnam (3.5%). Nếu dùng local CAPM (chỉ VN market), WACC sẽ thấp hơn (~10%).

Trong workshop này, sử dụng WACC = 10.5% (blended giữa global CAPM và local view) làm base case cho sensitivity.

Bước 5 — Terminal Value (2 Phương pháp)

5.1 Gordon Growth Model

T V = \frac{F C F F_{2030}}{W A C C - g} = \frac{F C F F_{2029} \times (1 + g)}{W A C C - g}

Với $g = 3 %$ , $W A C C = 10.5 %$ :

T V = \frac{20,826 \times 1.03}{0.105 - 0.03} = \frac{21,451}{0.075} = 286,013 tỷ VND

5.2 Exit Multiple

T V = E B I T D A_{2029} \times E V / E B I T D A_{e x i t}

$E B I T D A_{2029} = E B I T_{2029} + D & A_{2029} = 26,979 + 5,518 = 32,497$ tỷ VND

Comparable EV/EBITDA cho Asian IT Services: 13–16x. Chọn 14x:

T V = 32,497 \times 14 = 454,958 tỷ VND

5.3 Cross-check

Check	Gordon Growth	Exit Multiple
TV	286,013	454,958
Implied Exit Multiple	8.8x	14.0x (input)
Implied Terminal Growth	3.0% (input)	5.9%

Nhận xét: Exit Multiple TV implied growth 5.9% — cao hơn GDP growth → Gordon Growth Model hợp lý hơn cho base case. Sử dụng blended: 60% Gordon + 40% Exit Multiple.

T V_{b l e n d e d} = 0.6 \times 286,013 + 0.4 \times 454,958 = 171,608 + 181,983 = 353,591

6.1 Discount FCFFs và TV

Year	FCFF	Discount Factor	PV(FCFF)
2025	10,414	$(1.105)^{- 1} = 0.905$	9,425
2026	12,777	$(1.105)^{- 2} = 0.819$	10,464
2027	15,544	$(1.105)^{- 3} = 0.741$	11,518
2028	18,208	$(1.105)^{- 4} = 0.671$	12,218
2029	20,826	$(1.105)^{- 5} = 0.607$	12,641
Σ PV(FCFF)			56,266
TV	353,591	$(1.105)^{- 5} = 0.607$	214,630

6.2 Enterprise Value

E V = 56,266 + 214,630 = 270,896 tỷ VND

TV/EV = $214,630 / 270,896 = 79.2 %$ — trong khoảng expected (60–80%).

6.3 Equity Bridge

Component	Tỷ VND
Enterprise Value	270,896
(−) Total Debt	(8,500)
(+) Cash & Equivalents	+22,000
(−) Minority Interest	(3,200)
(+) Associates/Investments	+4,500
Equity Value	285,696

P r i c e = \frac{285,696}{1,370} = 208,538 VND/share

So sánh: Giá thị trường FPT (giả định) ~130,000 VND → Implied upside ~60%. Model có thể aggressive — cần kiểm tra qua sensitivity.

Bước 7 — Sensitivity Table

7.1 Data Table 2D: WACC × Terminal Growth

Excel Instructions:

Cell A1 = formula link đến Price/Share output
Row header: Terminal Growth rates (1%, 1.5%, 2%, 2.5%, 3%, 3.5%, 4%)
Column header: WACC rates (8%, 9%, 10%, 10.5%, 11%, 12%)
Select range → Data → What-If Analysis → Data Table
Row input cell = Terminal Growth cell in Assumptions
Column input cell = WACC cell in Assumptions

Kết quả:

VND/Share	g = 1%	g = 1.5%	g = 2%	g = 2.5%	g = 3%	g = 3.5%	g = 4%
WACC = 8%	311,200	336,400	367,100	405,200	454,000	519,300	610,800
WACC = 9%	248,500	265,700	285,800	309,700	338,600	374,500	420,500
WACC = 10%	203,100	215,200	229,000	245,200	264,300	287,200	315,200
WACC = 10.5%	184,500	195,000	206,800	220,400	236,300	255,200	278,200
WACC = 11%	168,100	177,200	187,400	198,900	212,300	228,000	246,800
WACC = 12%	141,200	148,000	155,600	164,000	173,600	184,600	197,600

7.2 Scenario Table

Scenario	Key Changes	Price/Share
🟢 Bull	Rev CAGR +2%, Margin +1%, WACC −1%, g +0.5%	~338,600
🟡 Base	As modeled	~208,500
🔴 Bear	Rev CAGR −3%, Margin −2%, WACC +1.5%, g −1%	~141,200

7.3 Implied Multiples Check

Multiple	Implied (DCF)	FPT Historical	Peer Median
P/E (2025E)	29.9x	18–25x	20–28x
EV/EBITDA (2025E)	15.6x	12–18x	14–20x
EV/Revenue (2025E)	3.7x	2.5–4.0x	3.0–5.0x

Cross-check passed: Implied multiples nằm trong range hợp lý → model không quá aggressive.

Deliverables

Sau khi hoàn thành workshop, sinh viên cần nộp:

#	Deliverable	Format
1	Excel DCF Model (8 tabs)	.xlsx
2	1-page summary: Key assumptions & output	Trong tab Output
3	Sensitivity Table 2D	Trong tab Sensitivity
4	3-line conclusion: Buy/Hold/Sell recommendation	Text cell

Rubric

Tiêu chí	Weight	Excellent (9–10)	Good (7–8)	Fair (5–6)
FCFF calculation	25%	Correct from 3 approaches	Correct from 1 approach	Minor errors
Revenue projection logic	20%	Top-down + bottom-up, justified	One method, justified	No justification
Terminal Value	20%	Both methods + cross-check	One method	Errors in formula
Sensitivity table	15%	2D functional table	Manual table	Missing
Formatting & structure	10%	Color-coded, clean	Readable	Messy
Implied multiple check	10%	Multiple checks, comment	One check	Missing

Tips & Common Mistakes

✅ Best Practices

Luôn tách Assumptions sang tab riêng — không hard-code trong formulas
Kiểm tra: Balance Sheet phải balance (A = L + E) ở mọi năm projected
Dùng mid-year convention nếu có thể — cho kết quả accurate hơn
Label mọi cell quan trọng — model phải readable bởi người khác

❌ Common Mistakes

Mistake	Hậu quả	Cách tránh
Discount FCFF bằng $K_{e}$	Overvalue (vì $K_{e} > W A C C$ → ít discount hơn, SAI)	FCFF → WACC. FCFE → $K_{e}$
Terminal growth > WACC	TV = negative (vô nghĩa)	$g < W A C C$ , thường $g \leq$ GDP growth
Quên trừ ΔWC	Overstate FCFF	Luôn check WC schedule
Dùng book value weights cho WACC	WACC sai	Dùng market cap cho equity weight
Hard-code numbers trong projection	Không thể sensitivity	Link mọi thứ qua Assumptions tab
Quên diluted shares	Overstate Price/Share	Treasury stock method cho options

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

Tài liệu	Mô tả
📖 Bài giảng chính	Nội dung lý thuyết DCF đầy đủ
📝 Blog	DCF Valuation: Nghệ thuật định giá $1 tỷ từ 1 file Excel
📊 Case Study	Amazon DCF, FPT FCFF Model, Tesla Valuation
📚 Standards	CFA, McKinsey, Damodaran, CFI frameworks
🔧 Workshop	Xây DCF Model hoàn chỉnh từ BCTC FPT
🎮 Game	Valuation Auction — Ai định giá chính xác nhất?

Workshop — Buổi 05: Xây DCF Model hoàn chỉnh từ BCTC FPT ​

Mục tiêu Workshop ​

Bước 0 — Setup Excel Workbook ​

Tab Structure ​

Formatting Rules ​

Bước 1 — Thu thập & Chuẩn hóa BCTC FPT (3 năm) ​

1.1 Income Statement (Rút gọn) ​

1.2 Balance Sheet (Rút gọn) ​

1.3 Key Historical Metrics (Tab: Schedules) ​

Bước 2 — Revenue Projection (5 năm) ​

2.1 Setup Assumptions Tab ​

2.2 Revenue Projection (Formulas) ​

2.3 Projected Income Statement ​

Bước 3 — FCFF Calculation & Projection ​

3.1 FCFF Formula trong Excel ​

3.2 Working Capital Schedule ​

3.3 FCFF Projection ​

Bước 4 — WACC Estimation ​

4.1 Cost of Equity (Ke) ​

4.2 Cost of Debt (Kd) ​

4.3 Capital Structure (Market Weights) ​

4.4 WACC Calculation ​

Bước 5 — Terminal Value (2 Phương pháp) ​

5.1 Gordon Growth Model ​

5.2 Exit Multiple ​

5.3 Cross-check ​

Bước 6 — EV → Equity Value → Price per Share ​

6.1 Discount FCFFs và TV ​

6.2 Enterprise Value ​

6.3 Equity Bridge ​

6.4 Price per Share ​

Bước 7 — Sensitivity Table ​

7.1 Data Table 2D: WACC × Terminal Growth ​

7.2 Scenario Table ​

7.3 Implied Multiples Check ​

Deliverables ​

Rubric ​

Tips & Common Mistakes ​

✅ Best Practices ​

❌ Common Mistakes ​

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

Workshop — Buổi 05: Xây DCF Model hoàn chỉnh từ BCTC FPT

Mục tiêu Workshop

Bước 0 — Setup Excel Workbook

Tab Structure

Formatting Rules

Bước 1 — Thu thập & Chuẩn hóa BCTC FPT (3 năm)

1.1 Income Statement (Rút gọn)

1.2 Balance Sheet (Rút gọn)

1.3 Key Historical Metrics (Tab: Schedules)

Bước 2 — Revenue Projection (5 năm)

2.1 Setup Assumptions Tab

2.2 Revenue Projection (Formulas)

2.3 Projected Income Statement

Bước 3 — FCFF Calculation & Projection

3.1 FCFF Formula trong Excel

3.2 Working Capital Schedule

3.3 FCFF Projection

Bước 4 — WACC Estimation

4.1 Cost of Equity ( $K_{e}$ )

4.2 Cost of Debt ( $K_{d}$ )

4.3 Capital Structure (Market Weights)

4.4 WACC Calculation

Bước 5 — Terminal Value (2 Phương pháp)

5.1 Gordon Growth Model

5.2 Exit Multiple

5.3 Cross-check

Bước 6 — EV → Equity Value → Price per Share

6.1 Discount FCFFs và TV

6.2 Enterprise Value

6.3 Equity Bridge

6.4 Price per Share

Bước 7 — Sensitivity Table

7.1 Data Table 2D: WACC × Terminal Growth

7.2 Scenario Table

7.3 Implied Multiples Check

Deliverables

Rubric

Tips & Common Mistakes

✅ Best Practices

❌ Common Mistakes

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