Отдаем журнал бесплатно!

Как разработать и автоматизировать бизнес-процесс в 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 × 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 × 10 % / 100 %), а расходы по предоставлению услуг сторонними организациями — 546 тыс. руб.

Расходы на приобретение сырья, материалов и покупных комплектующих — это 15 % от выручки. Для расчета показателя «Расходы на приобретение сырья, материалов и покупных комплектующих»  суммируем выручку от реализации (27 300) и страховой запас (2730), а затем полученное значение умножаем на 0,15 (15 % / 100 %), получаем 4505 тыс. руб.

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

  • выручка от реализации без НДС;
  • сырье и материалы;
  • покупные комплектующие;
  • заработная плата производственных рабочих;
  • страховые взносы;
  • прочие производственные расходы.

Например, сырье и материалы, покупные комплектующие и прочие расходы занимают соответственно 25 и 3 % от выручки, или в денежном выражении за 2015 г. соответственно 5784 (23 136 × 25 % / 100 %) и 694 тыс. руб.

Рассчитываем накладные расходы (310 % от заработной платы производственных рабочих). В нашем примере накладные расходы за 2015 г. — 14 415 тыс. руб. (4650 × 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 × 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» пользователь может выбрать ставку капитализации. Изменение ставки капитализации приводит соответственно к изменению чистой приведенной стоимости и, в конечном счете, инвестиционной стоимости бизнеса.

 

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

М. В. Алтухова,
независимый консультант

Статья опубликована в журнале «Справочник экономиста» № 11, 2015.

Отдаем журнал бесплатно!

Как разработать и автоматизировать бизнес-процесс в 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 × 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 × 10 % / 100 %), а расходы по предоставлению услуг сторонними организациями — 546 тыс. руб.

Расходы на приобретение сырья, материалов и покупных комплектующих — это 15 % от выручки. Для расчета показателя «Расходы на приобретение сырья, материалов и покупных комплектующих»  суммируем выручку от реализации (27 300) и страховой запас (2730), а затем полученное значение умножаем на 0,15 (15 % / 100 %), получаем 4505 тыс. руб.

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

  • выручка от реализации без НДС;
  • сырье и материалы;
  • покупные комплектующие;
  • заработная плата производственных рабочих;
  • страховые взносы;
  • прочие производственные расходы.

Например, сырье и материалы, покупные комплектующие и прочие расходы занимают соответственно 25 и 3 % от выручки, или в денежном выражении за 2015 г. соответственно 5784 (23 136 × 25 % / 100 %) и 694 тыс. руб.

Рассчитываем накладные расходы (310 % от заработной платы производственных рабочих). В нашем примере накладные расходы за 2015 г. — 14 415 тыс. руб. (4650 × 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 × 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» пользователь может выбрать ставку капитализации. Изменение ставки капитализации приводит соответственно к изменению чистой приведенной стоимости и, в конечном счете, инвестиционной стоимости бизнеса.

 

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

М. В. Алтухова,
независимый консультант

Статья опубликована в журнале «Справочник экономиста» № 11, 2015.

Акция «50 на 50: год за полцены!»
Финансовый анализ: 5 статей + расчетные файлы Excel к ним
Управленческий учет и отчетность
Подборки
Подборки
Типичные ошибки построения системы бюджетного управления
Подарок подписчикам журнала «Справочник экономиста» или «Планово-экономический отдел» на 1 полугодие 2018 года
Подписка для физических лицДля физических лиц Подписка для юридических лицДля юридических лиц Подписка по каталогамПодписка по каталогам