Разработка и автоматизация бизнес-процесса с помощью MS Excel

Размещено на http://www.allbest.ru/

Разработка и автоматизация бизнес-процесса с помощью MS Excel

М.В. Алтухова



Наверное, наиболее актуальные для экономистов задачи -- обеспечение эффективности финансовой деятельности и оценка целесообразности реализуемых компанией проектов. Но разработка бизнес-процесса -- занятие трудоемкое, требующее времени. А если специалисты еще и вручную проводят расчеты, есть вероятность, что полученные данные будут некорректными. Чтобы этого избежать, можно автоматизировать данный процесс. Это позволит снизить риск ошибочного ввода и предоставления информации. Как это сделать в 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
Проектная мощность, тыс. руб.	39000	63750	78188	96009	118028	141832	141832	678638
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	Итого
Низковольтные комплектные устройства	17550	28688	35184	43204	53113	63824	63824	305387
Комплектные распределительные устройства	3900	6375	7819	9601	11803	14183	14183	67864
Устройства безопасности	5850	9563	11728	14401	17704	21275	21275	101796
Итого	27300	44625	54731	67207	82620	99282	99282	475047
IV. Выручка от реализации без НДС, тыс. руб.
Номенклатурная группа	2015	2016	2017	2018	2019	2020	2021	Итого
Низковольтные комплектные устройства	14873	24311	29817	36614	45011	54088	54088	258803
Комплектные распределительные устройства	3305	5403	6626	8136	10002	12020	12020	57512
Устройства безопасности	4958	8104	9939	12205	15004	18029	18029	86268
Итого	23136	37818	46382	56955	70017	84137	84137	402582

Лист «Доходы»

Формируем кадровую политику компании (лист «Персонал»)

На этом листе будут сформированы таблицы:

· «Кадровая политика»;

· «Штатное расписание»;

· «ФОТ, налоги и отчисления, тыс. руб.».

Для наглядности задаем значения следующим показателям: инфляция по заработной плате, НДФЛ, страховые взносы (СВ).

Для удобства расчета присваиваем значениям имена:

инфляция по заработной плате -- 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	Итого
Выручка от реализации, с НДС	27300	44625	54731	67207	82620	99282	99282	475047
Страховой запас, t1	2730	4463	5473	6721	8262	9928	9928	47505
Расходы на приобретение сырья, материалов и покупных комплектующих	4505	7363	9031	11089	13632	16382	16382	78383
Расходы по предоставлению услуг сторонними организациями	546	893	1095	1344	1652	1986	1986	9501
II. Расчет себестоимости реализованной продукции, тыс. руб.
Статья	2015	2016	2017	2018	2019	2020	2021	Итого
Выручка от реализации, без НДС	23136	37818	46382	56955	70017	84137	84137	402582
Сырье и материалы, покупные комплектующие	5784	9454	11596	14239	17504	21034	21034	100645
Заработная плата производственных рабочих	4650	4697	4743	4791	4839	4887	4936	33543
Страховые взносы	1395	1409	1423	1437	1452	1466	1481	10063
Прочие производственные расходы	694	1135	1391	1709	2100	2524	2524	12077
Себестоимость реализации	12523	16694	19154	22175	25895	29912	29975	156329
Статья	2015	2016	2017	2018	2019	2020	2021	Итого
Накладные расходы	14415	14559	14705	14852	15000	15150	15302	103983
IV. Амортизация ОС и НА, тыс. руб.
Показатель	2015	2016	2017	2018	2019	2020	2021
Первоначальная стоимость ОС и НА, t1	30900	30900	30900	30900	30900	0	0
CAPEX	30900	0	0	0	0	0	0
Остаточная стоимость ОС и НА, t1	27810	24720	21630	18540	15450	15450	15450
Амортизация	3090	3090	3090	3090	3090	0	0

excel финансовый информация ошибочный

Рассмотрим порядок заполнения каждой таблицы подробно.

В таблице «Расходы на закупку товарно-материальных ценностей» важно рассчитать:

· страховой запас;

· расходы на приобретение сырья, материалов и покупных комплектующих;

· расходы по предоставлению услуг сторонними организациями.

Для этого создаем 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

Как видим, для обоих вариантов перечень работ и затрат одинаковый, отличается только стоимость. Для автоматического расчета суммы капитальных расходов по годам в разрезе работ задаем следующую формулу:

=ЕСЛИ(ЕНД(ВЫБОР($C$17;ВПР($B19;$B$10:$D$12;3;0);ВПР($B19;$F$10:$H$12;3;0);ВПР($B19;$J$10:$L$12;3;0)));0;ВЫБОР($C$17;ВПР($B19;$B$10:$D$12;3;0);ВПР($B19;$F$10:$H$12;3;0);ВПР($B19;$J$10:$L$12;3;0)))*D$17.

Планируем финансовую деятельность (лист «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

Рассчитываем налоги (лист «Тах»)

Для расчета НДС и налога на прибыль запишем макросы, и тогда при нажатии кнопок «Рассчитать НДС» и «Рассчитать налог на прибыль» в таблицах появятся готовые значения. Это достаточно удобно, в том числе при внесении соответствующих корректировок в модели.

Макрос для кнопки «Рассчитать НДС»:

Private Sub CommandButton1_Click()

'Расчет НДС к уплате'

Range("C11:I12").Select

Selection.ClearContents

Calculate

Application.Calculation = xlManual

Range("C11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2015*Data0)"

Selection.AutoFill Destination:=Range("C11:C12"), Type:=xlFillDefault

Calculate

Range("D11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2016*Data0)"

Selection.AutoFill Destination:=Range("D11:D12"), Type:=xlFillDefault

Calculate

Range("E11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2017*Data0)"

Selection.AutoFill Destination:=Range("E11:E12"), Type:=xlFillDefault

Calculate

Range("F11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2018*Data0)"

Selection.AutoFill Destination:=Range("F11:F12"), Type:=xlFillDefault

Calculate

Range("G11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2019*Data0)"

Selection.AutoFill Destination:=Range("G11:G12"), Type:=xlFillDefault

Calculate

Range("H11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2020*Data0)"

Selection.AutoFill Destination:=Range("H11:H12"), Type:=xlFillDefault

Calculate

Range("I11").Select

Selection.FormulaArray = "=SUM(($A11=Содержание)*Data_2021*Data0)"

Selection.AutoFill Destination:=Range("I11:I12"), Type:=xlFillDefault

Calculate

Range("C11:I12").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Calculate

Range("C11:I12").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.Borders(xlEdgeLeft).LineStyle = xlNone

Selection.Borders(xlEdgeTop).LineStyle = xlNone

Selection.Borders(xlEdgeBottom).LineStyle = xlNone

Selection.Borders(xlEdgeRight).LineStyle = xlNone

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("A10:J10").Select

Selection.Copy

Range("A10").Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Range("A13:J13").Select

Selection.Copy

Range("A13").Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Range("A1").Select

Application.Calculation = xlAutomatic

Calculate

MsgBox "Расчет произведен успешно" & n, vbInformation

End Sub

Макрос для кнопки ««Рассчитать налог на прибыль»:

Private Sub CommandButton2_Click()

'Расчет налога на прибыль'

Sheets("Tax").Select

Range("C17:I17").Select

Selection.ClearContents

Calculate

Sheets("Tax").Select

Range("C17").Select

Selection.Formula = "=IF(PL!D19>0,PL!D19*НП,0)"

Range("C17").Select

Selection.Copy

Range("C17:I17").Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Calculate

Range("C17:I17").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Tax").Select

Range("B1").Select

Application.Calculation = xlAutomatic

Calculate

MsgBox "Расчет произведен успешно" & n, vbInformation

End Sub

Лист «Тах»

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	Итого
+	Выручка от реализации продукции, товаров и услуг	23136	37818	46382	56955	70017	84137	84137	402582
-	Себестоимость реализованной продукции	-12523	-16694	-19154	-22175	-25895	-29912	-29975	-156329
-	Сырье и материалы, покупные комплектующие	-5784	-9454	-11596	-14239	-17504	-21034	-21034	-100645
-	Заработная плата производственных рабочих	-4650	-4697	-4743	-4791	-4839	-4887	-4936	-33543
-	Страховые взносы	-1395	-1409	-1423	-1437	-1452	-1466	-1481	-10063
-	Прочие производственные расходы	-694	-1135	-1391	-1709	-2100	-2524	-2524	-12077
+	Валовая прибыль	10613	21123	27229	34779	44121	54226	54162	246253
	Рентабельность по валовой прибыли, %	46%	56%	59%	61%	63%	64%	64%	61%
-	Накладные расходы	-14415	-14559	-14705	-14852	-15000	-15150	-15302	-103983
+	EBITDA	-3802	6564	12524	19927	29121	39075	38860	142270
	Рентабельность по EBITDA, %	-16%	17%	27%	35%	42%	46%	46%	35%
-	Амортизация	3090	3090	3090	3090	3090	3090	3090	21630
+	EBIT	-712	9654	15614	23017	32211	42165	41950	163900
	Рентабельность по EBIT, %	-3%	26%	34%	40%	46%	50%	50%	41%
-	Проценты по кредитам к уплате	0	0	0	0	0	0	0	0
+	Прибыль до налогообложения	-712	9654	15614	23017	32211	42165	41950	163900
	Налог на прибыль	0	-1931	-3123	-4603	-6442	-8433	-8390	-32922
+/-	Чистая прибыль	-712	7723	12491	18414	25769	33732	33560	130978
	Рентабельность по NP, %	-3%	26%	34%	40%	46%	50%	50%	41%



Отчет о движении денежных средств

№	Статья	2015	2016	2017	2018	2019	2020	2021
	Остаток на начало периода	0	-27716	-18440	-3573	18232	48643	88371
+/-	Денежные потоки от текущих операций	3185	9275	14867	21805	30411	39727	39566
+	Поступления -- всего	27300	44625	54731	67207	82620	99282	99282
+	Выручка от реализации продукции, товаров и услуг	27300	44625	54731	67207	82620	99282	99282
-	Платежи -- всего	-24116	-35350	-39864	-45402	-52208	-59555	-59716
-	Расходы на приобретение сырья, материалов и покупных комплектующих	-4505	-7363	-9031	-11089	-13632	-16382	-16382
-	Заработная плата	-8490	-8575	-8661	-8747	-8835	-8923	-9012
-	Расходы по предоставлению услуг сторонними организациями	-546	-893	-1095	-1344	-1652	-1986	-1986
-	Накладные расходы без оплаты труда управленческого персонала	-10575	-10681	-10788	-10895	-11004	-11114	-11226
-	Расчеты с бюджетом	0	-7838	-10290	-13326	-17084	-21150	-21111
+/-	Денежные потоки от инвестиционных операций	-30900	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
-	Платежи -- всего	-30900	0	0	0	0	0	0
-	Расходы в связи с приобретением, созданием, модернизацией, реконструкцией и подготовкой к использованию внеоборотных активов	-30900	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
+/-	Чистый денежный поток	-27716	9275	14867	21805	30411	39727	39566
	Остаток на конец периода	-27716	-18440	-3573	18232			127936

Согласно отчету о финансовых результатах в 2015 г. компания понесет убыток в размере 712 тыс. руб. Однако уже в 2016 г. проект будет приносить доход: прибыль составит 7723 тыс. руб., а рентабельность -- 26 %. Это достаточно высокий показатель.

Представленные в отчете о движении денежных средств данные позволяют сделать вывод, что в 2016 г. чистый денежный поток ожидается со знаком «+». Однако рост денежных средств прогнозируется только к концу 2018 г.

Оцениваем инвестиционную привлекательность проекта (лист «IP»)

Сначала рассчитываем чистый денежный поток:

Статья	2015	2016	2017	2018	2019	2020	2021
Поток по основной деятельности	3185	9275	14867	21805	30411	39727	39566
Поток по инвестиционной деятельности	-30900	0	0	0	0	0	0
Поток по финансовой деятельности	0	0	0	0	0	0	0
Чистый денежный поток (NCF)	-27716	9275	14867	21805	30411	39727	39566
NCF накопленным итогом	-27716	-18440	-3573	18232	48643	88371	127936
NCF в периоде дисконтированный	-25544	7879	11640	15734	20225	24351	22352
NCF в периоде дисконтированный накопленным итогом	-25544	-17665	-6026	9708	29933	54284	76636

Как видим, в 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)	-27716	9275	14867	21805	30411	39727	39566
Чистая приведенная стоимость (NPV)	76636	110865	111013	105582	92752	70224	36466
Чистая терминальная стоимость (NTV)	418589	418589	418589	418589	418589	418589	418589
Инвестиционная стоимость (EVD)	495225	529454	529603	524172	511341	488814	455056



В Excel чистая приведенная стоимость (NPV) определяется формулой ЧПС (d;ЧДПIC), где d -- ставка дисконтирования (рис. 2).

Если значение NPV положительное, то проект является прибыльным.

В нашем примере чистая приведенная стоимость в 2015 г. составила 76 636 тыс. руб.

Рис. 2. Пример расчета чистой приведенной стоимости (NPV)

Внутренняя норма доходности (IRR) определяется по формуле ВСД (ЧДПIC;0), где ВСД -- внутренняя ставка доходности, и обозначает процентный порог, затраты на капитал выше которого нецелесообразны.

Инвестиционная стоимость бизнеса (EVD) представляет собой сумму чистой приведенной и терминальной стоимости. В нашем примере инвестиционная стоимость составляет 495 225 тыс. руб. (76 636 + 418 589).

Обратите внимание: значение чистой терминальной стоимости зависит от ставки капитализации. Поэтому на листе «IP» пользователь может выбрать ставку капитализации. Изменение ставки капитализации приводит соответственно к изменению чистой приведенной стоимости и, в конечном счете, инвестиционной стоимости бизнеса.

Предложенная модель полностью автоматизированная, что позволяет быстро, а главное -- безошибочно проанализировать инвестиционный проект и оценить его. Изменяя те или иные параметры, можно выбрать оптимальный вариант его реализации.

Размещено на Allbest.ru

...