(CF - Project 2) Excel Sheet

Download as xlsx, pdf, or txt
Download as xlsx, pdf, or txt
You are on page 1of 15

Calculate the FCF

Assumptions 2023 2024 2025


Net Income 22,508 56,083 89,658
Depreciation and Amortization 110,588 110,588 110,588
Capital Expenditure and Acquistions 43,000 0 0
Change in Non-cash Working Capital 64,249 366,724 366,724
New Borrowings - Debt repaid 1,392,832 1,389,059 1,389,059

Free Cash Flow


Net Income 22,508 56,083 89,658
Depreciation and Amortization 110,588 110,588 110,588
Capital Expenditure and Acquistions (43,000) 0 0
Change in Non-cash Working Capital (64,249) (366,724) (366,724)
New Borrowings - Debt repaid 1,392,832 1,389,059 1,389,059
Free Cash Flow 1,418,679 1,189,006 1,222,581
Market
CTD
(VNINDEX)

Ngày Closing price Closing price Market return (Rm) CTD return (Rs)

10/08/2023 1,220.61 68.2 -1.09% 3.28%


09/08/2023 1,233.99 66 -0.67% -1.20%
08/08/2023 1,242.23 66.8 0.07% 4.28%
07/08/2023 1,241.42 64 1.25% -0.93%
04/08/2023 1,225.98 64.6 1.23% 0.93%
03/08/2023 1,210.95 64 -0.78% 1.10%
02/08/2023 1,220.43 63.3 0.24% -5.53%
01/08/2023 1,217.56 66.9 -0.44% -7.21%
31/07/2023 1,222.90 71.9 1.25% -2.34%
28/07/2023 1,207.67 73.6 0.86% 6.74%
27/07/2023 1,197.33 68.8 -0.29% -5.65%
26/07/2023 1,200.84 72.8 0.41% -1.63%
25/07/2023 1,195.90 74 0.43% 0.41%
24/07/2023 1,190.72 73.7 0.41% -7.20%
21/07/2023 1,185.90 79.2 1.11% 0.25%
20/07/2023 1,172.81 79 -0.01% 1.66%
19/07/2023 1,172.98 77.7 -0.09% -1.02%
18/07/2023 1,174.09 78.5 0.08% 0.64%
17/07/2023 1,173.13 78 0.40% -2.53%
14/07/2023 1,168.40 80 0.26% 2.92%
13/07/2023 1,165.42 77.7 0.97% -0.39%
12/07/2023 1,154.20 78 0.21% 0.90%
11/07/2023 1,151.77 77.3 0.24% 3.02%
10/07/2023 1,149.02 75 0.96% 1.34%
07/07/2023 1,138.07 74 1.05% 6.27%
06/07/2023 1,126.22 69.5 -0.74% -0.29%
05/07/2023 1,134.62 69.7 0.23% -0.43%
04/07/2023 1,132.00 70 0.58% 0.00%
03/07/2023 1,125.50 70 0.47% 1.58%
30/06/2023 1,120.18 68.9 -0.46% 0.44%
29/06/2023 1,125.39 68.6 -1.15% -1.02%
28/06/2023 1,138.35 69.3 0.35% 0.72%
27/06/2023 1,134.33 68.8 0.20% 0.00%
26/06/2023 1,132.03 68.8 0.23% -1.73%
23/06/2023 1,129.38 70 0.36% 3.34%
22/06/2023 1,125.30 67.7 0.61% 0.00%
21/06/2023 1,118.46 67.7 0.60% 4.07%
20/06/2023 1,111.72 65 0.57% 1.24%
19/06/2023 1,105.40 64.2 -0.88% -2.31%
16/06/2023 1,115.22 65.7 -0.16% -0.76%
15/06/2023 1,116.97 66.2 -0.04% 0.61%
14/06/2023 1,117.42 65.8 -0.45% -3.29%
13/06/2023 1,122.46 68 0.58% -2.61%
12/06/2023 1,116.02 69.8 0.76% 4.84%
09/06/2023 1,107.53 66.5 0.56% 1.52%
08/06/2023 1,101.32 65.5 -0.74% -0.15%
07/06/2023 1,109.54 65.6 0.11% -1.36%
06/06/2023 1,108.31 66.5 0.95% 2.59%
05/06/2023 1,097.82 64.8 0.64% 1.87%
02/06/2023 1,090.84 63.6 1.15% -0.63%
01/06/2023 1,078.39 64 0.30% 0.00%
31/05/2023 1,075.17 64 -0.27% -2.32%
30/05/2023 1,078.05 65.5 0.29% 2.79%
29/05/2023 1,074.98 63.7 1.05% -0.47%
26/05/2023 1,063.76 64 -0.08% 0.00%
25/05/2023 1,064.63 64 0.27% -1.24%
24/05/2023 1,061.79 64.8 -0.38% -1.83%
23/05/2023 1,065.85 66 -0.45% 4.02%
22/05/2023 1,070.64 63.4 0.33% 0.16%
19/05/2023 1,067.07 63.3 -0.12% -1.10%
18/05/2023 1,068.31 64 0.74% 0.00%
17/05/2023 1,060.44 64 -0.51% 0.94%
16/05/2023 1,065.91 63.4 0.02% 0.32%
15/05/2023 1,065.71 63.2 -0.11% -2.19%
12/05/2023 1,066.90 64.6 0.92% 4.11%
11/05/2023 1,057.12 62 -0.11% 6.67%
10/05/2023 1,058.26 58 0.43% 0.17%
09/05/2023 1,053.77 57.9 0.03% -1.88%
08/05/2023 1,053.44 59 1.25% -1.01%
05/05/2023 1,040.31 59.6 -0.03% 4.99%
04/05/2023 1,040.61 56.7 -0.81% -3.98%
28/04/2023 1,049.12 59 0.91% -0.51%
27/04/2023 1,039.63 59.3 -0.11% 1.36%
26/04/2023 1,040.80 58.5 0.57% 0.86%
25/04/2023 1,034.85 58 -0.63% -1.37%
24/04/2023 1,041.36 58.8 -0.15% 6.68%
21/04/2023 1,042.91 55 -0.61% 3.14%
20/04/2023 1,049.25 53.3 0.03% 0.56%
19/04/2023 1,048.98 53 -0.57% 3.26%
18/04/2023 1,055.02 51.3 0.11% 6.65%
17/04/2023 1,053.81 48 0.09% 4.58%
14/04/2023 1,052.89 45.85 -1.08% -0.33%
13/04/2023 1,064.30 46 -0.48% -4.05%
12/04/2023 1,069.45 47.9 0.00% -0.83%
11/04/2023 1,069.46 48.3 0.39% 2.73%
10/04/2023 1,065.35 47 -0.41% -0.85%
07/04/2023 1,069.71 47.4 -0.11% 1.49%
06/04/2023 1,070.91 46.7 -0.92% -1.70%
05/04/2023 1,080.86 47.5 0.22% 3.86%
04/04/2023 1,078.45 45.7 -0.08% 1.77%
03/04/2023 1,079.28 44.9 1.37% 2.71%
31/03/2023 1,064.64 43.7 0.49% -0.91%
30/03/2023 1,059.44 44.1 0.29% 6.68%
29/03/2023 1,056.33 41.25 0.19% 4.08%
28/03/2023 1,054.29 39.6 0.19% -1.01%
27/03/2023 1,052.25 40 0.52% 1.51%
24/03/2023 1,046.79 39.4 0.16% -1.51%
23/03/2023 1,045.10 40 0.44% 3.56%
22/03/2023 1,040.54 38.6 0.78% 3.56%
21/03/2023 1,032.43 37.25 0.91% 1.62%
20/03/2023 1,023.10 36.65 -2.13% -1.49%
17/03/2023 1,045.14 37.2 -0.22% 0.81%
16/03/2023 1,047.40 36.9 -1.40% -1.88%
15/03/2023 1,062.19 37.6 2.10% 3.79%
14/03/2023 1,040.13 36.2 -1.21% -3.26%
13/03/2023 1,052.80 37.4 -0.02% -1.46%
10/03/2023 1,053.00 37.95 -0.28% -0.53%
09/03/2023 1,055.95 38.15 0.64% 3.60%
08/03/2023 1,049.18 36.8 1.09% 2.20%
07/03/2023 1,037.84 36 1.03% -0.14%
06/03/2023 1,027.18 36.05 0.23% 2.67%
03/03/2023 1,024.77 35.1 -1.25% 0.14%
02/03/2023 1,037.61 35.05 -0.28% -1.56%
01/03/2023 1,040.55 35.6 1.54% 5.63%
28/02/2023 1,024.68 33.65 0.34% 0.45%
27/02/2023 1,021.25 33.5 -1.78% -4.38%
24/02/2023 1,039.56 35 -1.35% -1.42%
23/02/2023 1,053.66 35.5 -0.06% -2.78%
22/02/2023 1,054.28 36.5 -2.62% -2.84%
21/02/2023 1,082.23 37.55 -0.41% 0.94%
20/02/2023 1,086.69 37.2 2.55% 5.95%
17/02/2023 1,059.31 35.05 0.10% 0.86%
16/02/2023 1,058.29 34.75 0.96% 1.30%
15/02/2023 1,048.20 34.3 0.92% 2.36%
14/02/2023 1,038.64 33.5 -0.49% 1.50%
13/02/2023 1,043.70 33 -1.11% -7.16%
10/02/2023 1,055.30 35.45 -0.82% -1.68%
09/02/2023 1,064.03 36.05 -0.77% 0.28%
08/02/2023 1,072.22 35.95 0.60% -0.14%
07/02/2023 1,065.84 36 -2.18% -2.74%
06/02/2023 1,089.29 37 1.12% 0.27%
03/02/2023 1,077.15 36.9 -0.04% 0.41%
02/02/2023 1,077.59 36.75 0.15% 0.55%
01/02/2023 1,075.97 36.55 -3.22% -7.25%
31/01/2023 1,111.18 39.3 0.78% 0.00%
30/01/2023 1,102.57 39.3 -1.31% -0.51%
27/01/2023 1,117.10 39.5 0.81% 1.92%
19/01/2023 1,108.08 38.75 0.89% 1.30%
18/01/2023 1,098.28 38.25 0.91% 1.05%
17/01/2023 1,088.29 37.85 2.01% 6.69%
16/01/2023 1,066.68 35.4 0.61% 1.57%
13/01/2023 1,060.17 34.85 0.36% 0.00%
12/01/2023 1,056.39 34.85 0.06% -1.42%
11/01/2023 1,055.76 35.35 0.23% 0.00%
10/01/2023 1,053.35 35.35 -0.08% 4.48%
09/01/2023 1,054.21 33.8 0.26% -0.30%
06/01/2023 1,051.44 33.9 -0.42% -3.48%
05/01/2023 1,055.82 35.1 0.90% -0.28%
04/01/2023 1,046.35 35.2 0.23% 0.00%
03/01/2023 1,043.90 35.2
Average daily return 0.10%
Yearly return 29.99%
Risk free rate 1.81%
Risk premium 28.18%
Beta 2
Cost of equity 44.90%
Terminal Value Calculation

Assumptions
Growth rate 9%
Cost of equity 44.90%

Period 2022 2023 2024 2025


FCFE 1,947,236,212 1,418,680,299 1,189,008,562 1,222,583,291 (Thousand VND)
TERMINAL VALUE 3,405,524,487
Intrinsic value 5,352,760,699
Shares outstanding (2022) 73,859,473
Intrinsic value of stock 72.5
(Thounsand VND)
Discounted Cash Flow Full

Assumptions pt1 Assumptions pt2


Growth rate 1.7% Beta 1.3
EV/EBITDA Multiple 7.00x Market Return 10%
Cost of Debt 5% Equity value 17,500
Tax Rate 25% Debt value 15,000
10y Treasury 1.5%

Period 0 1 2 3 4

Free Cash Flows


EBIT 5,000 5,200 5,400 5,500
Tax
D&A 325 330 330 320
CapEx (1,550) (1,550) (1,500) (1,500)
Non-cash Work. Capital (inc) / dec (180) (170) (160) (150)
FCF

WACC
Cost of Equity
D/D+E
E/D+E
WACC

Terminal Value
EBITDA
Exit Multiple (EV/EBITDA)
Perpetuity Growth
Average
Discounting
Discount Factor
PV of FCF
PV of TV
Enterprise Value

Enterprise Value to Equity Value


Cash 500
Marketable Securities 4,500
Short term Debt 3,650
Long term Debt 16,540
Equity Value

Shares Outstanding 1,000


Implied Share Price
5

5,500

320
(1,500)
(145)

You might also like