- — , . , , . , . . Excel?
- MS Excel:
.
, «» . , , , . , , , (, , ), ; — .
« » :
:
=(($D4);0;$E4*(1+$H4)^$G4),
$D4 — ;
$E4 — ;
$H4 — ;
$G4 — .
:
=(($D$4);0;(($B10=$D$4;$B10>$D$4);1;0)),
$B10 — .
, . 1. «» :
- « »;
- « »;
- « , . .» (. 1).
. 1.
. 1, 2015 . , 39 000 . ., 119 019 . . — 5 .
( «»)
:
- « »;
- «, % V »;
- « , . .».
, . — 45 %. ( ) — 63 824 . . (141 832 x 45 / 100) — 2020 .
«»
I. |
|
|
|
|
|
|
||
|
, % |
, . |
|
|
|
|
|
|
|
100% |
12 |
|
|
|
|
|
|
|
100% |
12 |
|
|
|
|
|
|
|
100% |
12 |
|
|
|
|
|
|
|
70% |
12 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
, . . |
39 000 |
63 750 |
78 188 |
96 009 |
118 028 |
141 832 |
141 832 |
678 638 |
|
|
|
|
|
|
|
|
|
II. , % V |
|
|
|
|
|
|
|
|
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
45% |
45% |
45% |
45% |
45% |
45% |
45% |
45,00% |
|
10% |
10% |
10% |
10% |
10% |
10% |
10% |
10,00% |
|
15% |
15% |
15% |
15% |
15% |
15% |
15% |
15,00% |
|
70% |
70% |
70% |
70% |
70% |
70% |
70% |
70% |
|
|
|
|
|
|
|
|
|
III. , . . |
|
|
|
|
|
|
|
|
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
17 550 |
28 688 |
35 184 |
43 204 |
53 113 |
63 824 |
63 824 |
305 387 |
|
3900 |
6375 |
7819 |
9601 |
11 803 |
14 183 |
14 183 |
67 864 |
|
5850 |
9563 |
11 728 |
14 401 |
17 704 |
21 275 |
21 275 |
101 796 |
|
27 300 |
44 625 |
54 731 |
67 207 |
82 620 |
99 282 |
99 282 |
475 047 |
|
|
|
|
|
|
|
|
|
IV. , . . |
|
|
|
|
|
|
|
|
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
14 873 |
24 311 |
29 817 |
36 614 |
45 011 |
54 088 |
54 088 |
258 803 |
|
3305 |
5403 |
6626 |
8136 |
10 002 |
12 020 |
12 020 |
57 512 |
|
4958 |
8104 |
9939 |
12 205 |
15 004 |
18 029 |
18 029 |
86 268 |
|
23 136 |
37 818 |
46 382 |
56 955 |
70 017 |
84 137 |
84 137 |
402 582 |
( «»)
:
- « »;
- « »;
- «, , . .».
: , , ().
:
— Sindex;
— _;
— _.
, . ( , — 2015 .):
=($C4*(1+SIndex)^(C$15))*$D4*C9/1000,
$C4 — (40 000 .);
SIndex — (1 %);
C$15 — (2015 0);
$D4 — (12 );
C9 — (8 .).
, , (. 1).
1. ,
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
3840 |
3878 |
3917 |
3956 |
3996 |
4036 |
4076 |
|
4650 |
4697 |
4743 |
4791 |
4839 |
4887 |
4936 |
|
8490 |
8575 |
8661 |
8747 |
8835 |
8923 |
9012 |
+ |
4196 |
4238 |
4281 |
4323 |
4367 |
4410 |
4454 |
|
12 686 |
12 813 |
12 941 |
13 071 |
13 201 |
13 333 |
67 |
«» :
- « - , . .»;
- « , . .»;
- « , . .»;
- « () (), . .».
«»
I. - , . . |
||||||||
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
, |
27 300 |
44 625 |
54 731 |
67 207 |
82 620 |
99 282 |
99 282 |
475 047 |
, t1 |
2730 |
4463 |
5473 |
6721 |
8262 |
9928 |
9928 |
47 505 |
, |
4505 |
7363 |
9031 |
11 089 |
13 632 |
16 382 |
16 382 |
78 383 |
|
546 |
893 |
1095 |
1344 |
1652 |
1986 |
1986 |
9501 |
II. , . . |
||||||||
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
, |
23 136 |
37 818 |
46 382 |
56 955 |
70 017 |
84 137 |
84 137 |
402 582 |
, |
5784 |
9454 |
11 596 |
14 239 |
17 504 |
21 034 |
21 034 |
100 645 |
|
4650 |
4697 |
4743 |
4791 |
4839 |
4887 |
4936 |
33 543 |
|
1395 |
1409 |
1423 |
1437 |
1452 |
1466 |
1481 |
10 063 |
|
694 |
1135 |
1391 |
1709 |
2100 |
2524 |
2524 |
12 077 |
|
12 523 |
16 694 |
19 154 |
22 175 |
25 895 |
29 912 |
29 975 |
156 329 |
III |
|
|
|
|
|
|
|
|
|
1 |
2 |
2 |
2 |
2 |
2 |
2 |
|
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
14 415 |
14 559 |
14 705 |
14 852 |
15 000 |
15 150 |
15 302 |
103 983 |
IV. , . . |
||||||||
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
, t1 |
30 900 |
30 900 |
30 900 |
30 900 |
30 900 |
0 |
0 |
|
CAPEX |
30 900 |
0 |
0 |
0 |
0 |
0 |
0 |
|
, t1 |
27 810 |
24 720 |
21 630 |
18 540 |
15 450 |
15 450 |
15 450 |
|
|
3090 |
3090 |
3090 |
3090 |
3090 |
0 |
0 |
|
.
« - » :
- ;
- , ;
- .
2 , .
, 10 2 % . , 2015 . 27 300 . ., 2730 . . (27 300 x 10 % / 100 %), — 546 . .
, — 15 % . « , » (27 300) (2730), 0,15 (15 % / 100 %), 4505 . .
« ». :
- ;
- ;
- ;
- ;
- ;
- .
, , 25 3 % , 2015 . 5784 (23 136 x 25 % / 100 %) 694 . .
(310 % ). 2015 . — 14 415 . . (4650 x 310 % / 100 %).
— , «CE». :
=B$25*(1/_)*100%,
B$25 — (30 900);
_ — (10 ).
, 2015 . 3090 . .
( «CapEx»)
. :
- 1 — ;
- 2 — .
( ).
|
1 |
|
|
2 |
|
, . . |
|
|
, . . |
- - |
5500 |
|
- - |
5500 |
|
25 400 |
|
|
10 500 |
|
30 900 |
|
|
16 000 |
( «FinEx»)
— . :
- ;
- .
2015 . :
=((($D$10=Data_RE)*($E10=)*Data_2015*Data0)<0;-((($D$10=Data_RE)*($E10=)*Data_2015*Data0);0);0),
Data_RE — «» «»;
— Q «» «»;
Data_2015 — G «2015» «»;
Data0 — O «» «».
, (WACC), :
=(*$D$4*(1-);*$D$5*(1-);*$D$6),
— $C$4;
— $L$6;
— $C$5;
— $C$6
( «»)
, « » « » . , .
«»
I. , . . |
||||||||
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
612 |
544 |
476 |
408 |
340 |
340 |
340 |
3059 |
|
|
|
|
|
|
|
|
0 |
|
|
|
|
|
|
|
|
0 |
, . . |
612 |
544 |
476 |
408 |
340 |
340 |
340 |
3059 |
II. , . . |
||||||||
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
4164 |
6807 |
8349 |
10 252 |
12 603 |
15 145 |
15 145 |
72 465 |
|
9936 |
5682 |
5938 |
6261 |
6667 |
7178 |
7218 |
48 879 |
/, . . |
–5771 |
1126 |
2411 |
3991 |
5936 |
7967 |
7927 |
23 586 |
III. , . . |
||||||||
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
0 |
1931 |
3123 |
4603 |
6442 |
8433 |
8390 |
32 922 |
|
|
|
|
|
|
|
|
0 |
, . . |
0 |
1931 |
3123 |
4603 |
6442 |
8433 |
8390 |
32 922 |
, 2015 . «» 612 . . (27 810 x 2,2 % / 100 %, 27 810 — ; 2,2 % — ). — 5771 . . (4164 – 9936), .
( «PL») ( «CF»).
|
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
+ |
, |
23 136 |
37 818 |
46 382 |
56 955 |
70 017 |
84 137 |
84 137 |
402 582 |
- |
|
–12 523 |
–16 694 |
–19 154 |
–22 175 |
–25 895 |
–29 912 |
–29 975 |
–156 329 |
- |
, |
–5784 |
–9454 |
–11 596 |
–14 239 |
–17 504 |
–21 034 |
–21 034 |
–100 645 |
- |
|
–4650 |
–4697 |
–4743 |
–4791 |
–4839 |
–4887 |
–4936 |
–33 543 |
- |
|
–1395 |
–1409 |
–1423 |
–1437 |
–1452 |
–1466 |
–1481 |
–10 063 |
- |
|
–694 |
–1135 |
–1391 |
–1709 |
–2100 |
–2524 |
–2524 |
–12 077 |
+ |
|
10 613 |
21 123 |
27 229 |
34 779 |
44 121 |
54 226 |
54 162 |
246 253 |
|
, % |
46% |
56% |
59% |
61% |
63% |
64% |
64% |
61% |
- |
|
–14 415 |
–14 559 |
–14 705 |
–14 852 |
–15 000 |
–15 150 |
–15 302 |
–103 983 |
+ |
EBITDA |
–3802 |
6564 |
12 524 |
19 927 |
29 121 |
39 075 |
38 860 |
142 270 |
|
EBITDA, % |
–16% |
17% |
27% |
35% |
42% |
46% |
46% |
35% |
- |
|
3090 |
3090 |
3090 |
3090 |
3090 |
3090 |
3090 |
21 630 |
+ |
EBIT |
–712 |
9654 |
15 614 |
23 017 |
32 211 |
42 165 |
41 950 |
163 900 |
|
EBIT, % |
–3% |
26% |
34% |
40% |
46% |
50% |
50% |
41% |
- |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
–712 |
9654 |
15 614 |
23 017 |
32 211 |
42 165 |
41 950 |
163 900 |
|
|
0 |
–1931 |
–3123 |
–4603 |
–6442 |
–8433 |
–8390 |
–32 922 |
+/- |
|
–712 |
7723 |
12 491 |
18 414 |
25 769 |
33 732 |
33 560 |
130 978 |
|
NP, % |
–3% |
26% |
34% |
40% |
46% |
50% |
50% |
41% |
|
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
|
0 |
–27 716 |
–18 440 |
–3573 |
18 232 |
48 643 |
88 371 |
+/– |
|
3185 |
9275 |
14 867 |
21 805 |
30 411 |
39 727 |
39 566 |
+ |
— |
27 300 |
44 625 |
54 731 |
67 207 |
82 620 |
99 282 |
99 282 |
+ |
, |
27 300 |
44 625 |
54 731 |
67 207 |
82 620 |
99 282 |
99 282 |
– |
— |
–24 116 |
–35 350 |
–39 864 |
–45 402 |
–52 208 |
–59 555 |
–59 716 |
– |
, |
–4505 |
–7363 |
–9031 |
–11 089 |
–13 632 |
–16 382 |
–16 382 |
– |
|
–8490 |
–8575 |
–8661 |
–8747 |
–8835 |
–8923 |
–9012 |
– |
|
–546 |
–893 |
–1095 |
–1344 |
–1652 |
–1986 |
–1986 |
– |
|
–10 575 |
–10 681 |
–10 788 |
–10 895 |
–11 004 |
–11 114 |
–11 226 |
– |
|
0 |
–7838 |
–10 290 |
–13 326 |
–17 084 |
–21 150 |
–21 111 |
+/– |
|
–30 900 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
— |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
— |
–30 900 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
, , , |
–30 900 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+/– |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
— |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+ |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
— |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
– |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
+/– |
|
–27 716 |
9275 |
14 867 |
21 805 |
30 411 |
39 727 |
39 566 |
|
|
–27 716 |
–18 440 |
–3573 |
18 232 |
|
|
127 936 |
2015 . 712 . . 2016 . : 7723 . ., — 26 %. .
, 2016 . «+». 2018 .
( «IP»)
:
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
|
3185 |
9275 |
14 867 |
21 805 |
30 411 |
39 727 |
39 566 |
|
–30 900 |
0 |
0 |
0 |
0 |
0 |
0 |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
(NCF) |
–27 716 |
9275 |
14 867 |
21 805 |
30 411 |
39 727 |
39 566 |
NCF |
–27 716 |
–18 440 |
–3573 |
18 232 |
48 643 |
88 371 |
127 936 |
NCF |
–25 544 |
7879 |
11 640 |
15 734 |
20 225 |
24 351 |
22 352 |
NCF |
–25 544 |
–17 665 |
–6026 |
9708 |
29 933 |
54 284 |
76 636 |
, 2015 . NCF –27 716 . . (3185 – 30 900), NCF — –25 544 . . (–27 716) / (1 + 0,085)).
( , , ) .
KPI |
|
|
Δ, +/– |
|
|
8,5% |
|
|
|
3% |
|
WACC |
|
8,5% |
|
(IRR) |
|
60,0% |
|
(NPV) |
|
76 636 |
|
(NTV) |
|
418 589 |
|
(), |
|
1 |
|
(), |
|
3 |
|
, |
|
1 |
|
(EVD) |
|
495 225 |
|
(EVD)
|
2015 |
2016 |
2017 |
2018 |
2019 |
2020 |
2021 |
(NCF) |
–27 716 |
9275 |
14 867 |
21 805 |
30 411 |
39 727 |
39 566 |
(NPV) |
76 636 |
110 865 |
111 013 |
105 582 |
92 752 |
70 224 |
36 466 |
(NTV) |
418 589 |
418 589 |
418 589 |
418 589 |
418 589 |
418 589 |
418 589 |
(EVD) |
495 225 |
529 454 |
529 603 |
524 172 |
511 341 |
488 814 |
455 056 |
Excel (NPV) (d;IC), d — (. 2).
NPV , .
2015 . 76 636 . .
. 2. (NPV)
(IRR) (IC;0), — , , .
(EVD) . 495 225 . . (76 636 + 418 589).
: . «IP» . , , .
, , — . , .