Excel Proyeksi Keuangan
Excel Proyeksi Keuangan
Excel Proyeksi Keuangan
FY2020 FY2021 (I) FY2022 FY2023 FY2024 FY20-1 FY20-2 FY20-3 FY20-4 FY20-5 FY20-6
in Millions of Rupiah
Cash and Equivalents 1,543,129 2,201,859 2,495,655 2,982,005 3,777,791 4,796,412 54,854 63,456 475,773
Accounts Receivable - Trade, Net 4,364,285 5,744,122 5,572,867 5,901,994 5,463,985 5,717,836 440,878 473,889 43,453,234 1 1 1 1 1
Receivables - Other 24,115 358,608 502,269 500,975 168,238 298,579 343,247 743,583 4,557,647,539 0 0 0 0 0
Total Receivables, Net 4,388,399 6,102,729 6,075,136 6,402,969 5,632,223 6,016,414 784,126 1,217,472 4,601,100,773 0 0 0 0 0
Total Inventory - Net 2,123,676 1,825,267 3,351,796 2,790,634 2,805,112 2,486,862 32,132,243 8,746,608 4,375,723 6 9 5 6 7
Prepaid Expenses 32,100 23,577 34,954 37,807 32,096 58,514 38,244 447,763 4,335,434 (0) 0 0
Advances for Purchases 184,989 76,606 63,914 101,544 106,661 405,357 324,335 324,324 4,453,453,475
Prepaid Taxes 467,429 444,160 626,404 461,144 484,846 390,895 875,456 43,764 987,567
Others Current Asset 60
Total Current Assets 8,739,783 10,674,200 12,647,859 12,776,103 12,838,729 14,154,455 33,009,468 10,475,299 4,610,287,704
Long Term Portion of Prepaid Rent
Defeered Tax Assets - Net 48,338 82,446 89,449 96,055 104,876 107,703 76,346 27 27
Right of use assets building 228,563 210,688 34,664
Property, Plant, Equipment 3,859,420 3,988,757 4,258,301 4,674,964 6,043,202 5,981,827 9,734,562 8,881,681 8,028,801
Intangible Assets 58 58
Advances for Purchases of Equipment 258,130 148,075 567,436 1,459,859 521,484 561,651 67,583,786
Guarantee Deposits 15,511 22,371 28,662 30,938 40,646 40,995 456,564
Other fixed assets 1,240
Total Non-Current Assets 4,182,639 4,241,650 4,943,848 6,261,816 6,938,771 6,902,865 77,851,258 8,881,767 8,028,886
Total Assets 12,922,422 14,915,850 17,591,706 19,037,919 19,777,501 21,057,320 110,860,726 19,357,066 4,618,316,590
Short Term Bank Loan 1,284,000 1,634,000 1,500,000 1,000,000 50,000 -
Trade Payable 1,329,633 1,717,219 1,551,172 1,312,183 1,591,797 2,145,506
Others Payable 74,184 111,286 74,318 100,230 314,132 310,157
Taxes Payables 139,294 131,496 61,508 196,860 147,124 347,490
Accrued Expenses 339,087 285,203 447,416 695,163 411,853 515,265
Unearned Income
CPLTD 467,853 594,424 1,130,098 421,924 960,418 765,440 - - -
Bank 467,853 594,424 380,211.72 421,924 804,081 609,106
Bonds 749,886 - 156,337 156,335
Leasing
Other
Other Current liabilities, Total 250,000
Total Current Liabilities 3,884,051 4,473,628 4,764,510 3,726,360 3,475,324 4,083,858 - - -
LTD 2,073,869 2,225,557 3,377,230 4,386,380 3,850,410 3,641,946 - - -
Bank 1,324,666 430,242 1,831,798 2,839,708 1,725,336 1,533,047
Bonds 749,203 1,795,315 1,545,432 1,546,672 1,888,883 1,889,551
Leasing 236,191 219,348
Other
Deferred Tax Liabilities 25,952 21,184 19,852 11,181 15,261 15,908
Employee benefits Obligation 673,294 841,134 887,569 1,014,058 1,165,038 1,194,649
Other Liablities - Total
Total Non-Current Liabilities 2,773,115 3,087,875 4,284,652 5,411,619 5,030,709 4,852,503 - - -
Total Liablities 6,657,166 7,561,503 9,049,162 9,137,979 8,506,032 8,936,360
Common Stock, Total 447,174 447,174 447,174 447,174 447,174 447,174 830 830 830
Additional Paid-In Capital 330 330 330 330 330 330 47 47 47
Differences arising from transaction with non-controlling interest (54) (54) (54)
Apropriate Retained Earnings (Accumulated Deficit) 39,000 41,000 43,000 45,000 47,000 47,000 51 56 61
Unapropriated Retained Earnings (Accumulated Deficit) 5,636,490 6,702,646 7,857,855 9,174,328 10,524,378 11,347,256
Other Equity, Total (1,504) (5,080) (5,712) (3,966) (7,812) (3,283)
Sub-total Equity 6,121,490 7,186,070 8,342,648 9,662,866 11,011,070 11,838,478 874 879 884
Non-controling Interest 143,766 168,277 199,897 237,074 260,398 282,482
Equity Net 6,265,256 7,354,346 8,542,544 9,899,940 11,271,468 12,120,960 874 879 884
Total Liabilities & Shareholders' Equity 12,922,422 14,915,850 17,591,706 19,037,919 19,777,501 21,057,320 874 879 884
Total Common Shares Outstanding 1,660 1,660 1,660 1,660 1,660 1,660 1,660 1,660 1,660
1Q13 Vs Our
Income Statements FY2016 FY2017 FY2018 FY2019 FY2020 FY2021 (T1 x 4) FY2022 FY2023 FY2024 FY2025 1Q12 1Q13 Growth (%) FY13F FY13F
in Millions of Rupiah
Total Revenue 18,349,960 20,816,674 24,060,802 25,026,739 24,476,954 29,341,749 33,743,011 38,804,463 44,625,132 51,318,902
Cost of Revenue, Total 13,449,537 15,841,619 17,664,149 17,109,499 17,177,831 20,642,747 18,558,656 21,924,521 25,436,325 29,251,774
Gross Profit 4,900,422 4,975,055 6,396,654 7,917,241 7,299,123 8,699,002 15,184,355 16,879,941 19,188,807 22,067,128
Marketing & Selling Expenses (2,078,014) 1,909,487 3,045,558 4,027,987 3,769,235 3,942,195 4,811,771 5,873,159 7,168,669 8,749,945
General & Adm Expenses (507,166) 605,008 723,203 716,990 698,960 772,273 404,257 211,614 110,772 57,985
Total Operating Expense (2,585,180) 2,514,495 3,768,762 4,744,976 4,468,195 4,714,468 5,216,027 6,084,772 7,279,441 8,807,931
Operating Income (EBIT) 7,485,603 2,460,559 2,627,892 3,172,265 2,830,928 3,984,534 20,400,382 22,964,714 26,468,248 30,875,058
Financial Charges (356,714) (386,922) (492,639) (355,075) (353,822) (320,413)
(Loss) / gain on Forex - Net (124,336) 91,363 163,690 (206,362) 116,986 622,768
Net Income Before Taxes 7,016,044 2,186,885 2,381,942 2,704,467 2,683,888 3,052,660 3,510,559 4,037,143 4,642,715 5,339,122
Provision for Income Taxes (457,007) (555,931) (621,508) (665,062) 585,722 763,165 877,640 1,009,286 1,160,679 1,334,780
- - - - - -
Net Income After Taxes 6,559,037 1,630,954 1,760,434 2,039,404 2,098,166 2,289,495 4,388,199 5,046,429 5,803,393 6,673,902
Other Comprehensive Income (Expenses) (42,959) (60,813) 44,314 (19,354) (3,846) (18,118) - - - -
Minority Interest (0) (0) (0) 0 (53,565) - - - -
Net Income 6,516,077 1,570,140 1,804,748 2,020,051 2,040,756 2,307,613 4,388,199 5,046,429 5,803,393 6,673,902
Basic Weighted Average Shares 1,660 1,660 1,660 1,660 - - 47 47 47 47
EPS 61 71 77 89 42 37 93,615 107,657 123,805 142,376
Closing Price
PER
To find CAGR:
Initial amount: (3,055)
Ending amount: 444
Years of investment: 5
CAGR (%): (2)
Dividend Paid 0 1 1 1 1 1 1 1 1
1,694,180 800,772 1,082,849 1,010,025 1,020,378 1,153,806 2,194,099 2,523,214 2,901,697
BVS 3,774 4,430 5,146 5,964 #DIV/0! #DIV/0! 19 19 19
Compare to
Our FY12F FY12A our forecast
Revenue 12,155 25,026,739 2,058
Gross Profit 5,956 7,917,241 1,328
Gross Profit Margin 0 0 (0)
Income Before Tax 2,112 2,704,467 1,280
Net Income 1,679 2,020,051 1,202
Cash Flow Statement FY2017 FY2018 FY2019 FY2020 FY2021-I FY2022 FY2023 FY2024
In Millions IDR
FY2016 FY2017
Passiva Lancar
Short Term Bank Loan 1,284,000 1,634,000 1,500,000
Ratio 0.48 0.47 0.48
461,144 484,846
10,142,075.49 11,663,386.81
555,290.99 638,584.64
44,625,132.14 51,318,901.96
4.40 4.40
0.05 0.05
4,037,771.43 4,643,437.14
25,436,325.32 29,251,774.12
6.30 6.30
37,676.57 45,987.33
7,168,669.10 8,749,945.43
0.01 0.01
1,160,679 1,334,780
30,775.54 37,237.59
7,279,441 8,807,931
0.004 0.004
1,349,261 1,551,650
0.31 0.31
2,683,454.06 3,085,972.17
0.62 0.62
297,309 341,906
0.07 0.07
4,330,024.08 4,979,527.69
(621,101.71) (714,266.96)
(0.14) (0.14)
313,743 360,804
44,625,132 51,318,902
0.007 0.007
293,266 153,514
110,772 57,985
2.65 2.65
Umur Ekonomis 15 tahun
Nilai Residu 0
Metode Depresiasi Straight Line
Fixed Assets
FY2020 FY2021 FY2022 FY2023 FY2024 FY2025 FY2026 FY2027 FY2028 FY2029
Aset Saat ini
Aset Awal 4,674,964 6,043,202 5,640,322 5,237,442 4,834,562 4,431,681 4,028,801 3,625,921 3,223,041 2,820,161
Depresiasi (1,368,238) 402,880 402,880 402,880 402,880 402,880 402,880 402,880 402,880 402,880
Aset Akhir 6,043,202 5,640,322 5,237,442 4,834,562 4,431,681 4,028,801 3,625,921 3,223,041 2,820,161 2,417,281
Total Depresiasi Baru 402,880 602,880 852,880 852,880 852,880 852,880 652,880 652,880 652,880
Depresiasi 805,760 1,005,760 1,255,760 1,255,760 1,255,760 1,255,760 1,055,760 1,055,760 1,055,760
Total Aset Tetap (Bersih) 6,640,322 10,837,442 9,734,562 8,881,681 8,028,801 7,175,921 6,723,041 6,070,161 5,417,281
FY2030
2,417,281
402,880
2,014,401
250,000
652,880
2,750,000
2,750,000
1,055,760
4,764,401
Asumsi Keuangan
Tax Rate
(Dalam Hari)
Perputaran Piutang 87.3 107.0 92.2 93.4
Perputaran Persediaan 57.6 42.1 69.3 59.5
Perputaran Hutang Usaha 36.1 39.6 32.1 28.0
Intangible Assets 0 0 0 0
0 0 0
13
20
47,000 47,000 51 56 61
10,203,058 ### ### ### ###
10,250,058 ### ### ### ###
10,250,058 ### ### ### ###
2025
100,000,000
-
-
2025
450,000,000
-
-
450,000,000
WACC
Cost of Capital %
Capital Structure
Equity (pake market value) 100 80%
Debt - 20%
WACC 10.48%
Free Cash Flow To Firm
FY20-5 FY20-6 FY20-7 FY20-8 FY20-9 FY20-10
EBIT
Beban Bunga
EBT
Beban pajak
EAT 0 0 0 0 0
Tahun 1 2 3 4 5 5
Pertumbuhan 6.00%
Cost Of Equity 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Discount Factor 1 1 1 1 1 1
Nilai kini 0 0 0 0 0 0
Nilai usaha 0
Interest Bearing Debt #ERROR!
Ekuitas #VALUE!
Outstanding Shares 0
Fair Value #VALUE!
Discounted Cash Flow
FY20-5 FY20-6 FY20-7 FY20-8 FY20-9
(in million rupiah)
DCF 1 2 3 4 5
EBIT 2,683,888 3,052,660 3,510,559 4,037,143 4,642,715
Tax 585,722 763,165 877,640 1,009,286 1,160,679
NOPAT atau EAT 2,098,166 2,289,495 2,632,919 3,027,857 3,482,036
Depreciation & Amort 0 0 0 0 0
Changes in Working Capital Err:509 Err:509 Err:509 Err:509 Err:509
Changes in other asset, net of liabilities Err:509 Err:509 Err:509 Err:509 Err:509
Gross Investments Err:509 Err:509 Err:509 Err:509 Err:509
Capex (1,596,801) 79,250 0 0 0
FCFF Err:509 Err:509 Err:509 Err:509 Err:509
Discount Factor 1.07 1.15 1.23 1.31 1.41
PV Err:509 Err:509 Err:509 Err:509 Err:509
Total PV Err:509
Net Debt Err:509
EV Err:509
Total No. of share 0
EV/share 2,578
Market Price as Of end-May 2020 2000
Recoomendation BUY
rs=CAPM 7.40%
G=Double at 2020 10%
Do #REF!
D1 #VALUE!
Po #VALUE!
Treminal Value
Err:509
1.41
Err:509
FR0026
Multiples ABCD EFGH IJKL MNOP QRST
P/S
PER
PBV
PEG Ratio
Detail FY20-4 FY20-5 FY20-6 FY20-7 FY20-8
EPS
EPS growth
Fair PEG Ratio 1 1 1 1 1
PER (x) 0.00 0.00 0.00 0.00 0.00
Fair Market Price 0 0 0 0 0
UVWX Mean
#DIV/0!
#DIV/0!
#DIV/0!
FY20-9
1
0.00
0