Разработка бизнес-процесса — занятие трудоемкое, требующее времени. А если специалисты еще и вручную проводят расчеты, есть вероятность, что полученные данные будут некорректными. Чтобы этого избежать, можно автоматизировать данный процесс. Это позволит снизить риск ошибочного ввода и предоставления информации. Как это сделать в Excel?
Эффективный управленческий учет в Excel! Освойте управленческий учет в Excel с нашим курсом и научитесь использовать этот мощный инструмент для анализа и отчетности. Запишитесь на курс за 6 900 ₽!
Предлагаем следующий алгоритм разработки и автоматизации бизнес-процесса с помощью MS Excel:
Рассмотрим эти этапы подробнее.
Задаем основные параметры проекта
Предположим, компания «Альфа» планирует построить производственный цех и покрасочное помещение. Устанавливаем для данных объектов начальную проектную мощность, сроки выхода на проектную мощность, годовой темп прироста, выбираем год открытия. Изменяя начальное значение проектной мощности, темпы роста, срок, мощности (старт, темп, срок), получим график выхода на проектную мощность; изменяя год открытия по каждому объекту — календарный план развития данного объекта.
Управленческий учет: с нуля до настройки в 1С, Excel и Google-таблицах
Хотите стать самым ценным сотрудником для бизнеса? С курсом «Управленческий учет» каждый бухгалтер научиться понимать, откуда приходят и куда уходят деньги, и сможет предлагать руководителю правильные управленческие решения. После обучения вы получите удостоверение о повышении квалификации на 140 акад. часов. Сейчас научиться управленческому учету можно со скидкой 71% за 6 900 руб.
На примере объекта «Производственный цех» задаем формулы:
для расчета максимальной проектной мощности:
=ЕСЛИ(ЕПУСТО($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 %).
Далее планируем амортизационные отчисления — линейным методом по первоначальной стоимости, которая импортируется в расчет из вкладки «CарEх». Для расчета амортизации вводим следующую формулу:
=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» пользователь может выбрать ставку капитализации. Изменение ставки капитализации приводит соответственно к изменению чистой приведенной стоимости и, в конечном счете, инвестиционной стоимости бизнеса.
Предложенная модель полностью автоматизированная, что позволяет быстро, а главное — безошибочно проанализировать инвестиционный проект и оценить его. Изменяя те или иные параметры, можно выбрать оптимальный вариант его реализации.
Начать дискуссию