Электронная версия журнала

Как сбалансировать параметры в финансовых моделях с помощью Excel

«Справочник экономиста» №11 2014 / Информационные технологии

Сбалансировать финансовые показатели, найти оптимальные значения коэффициентов, упростить подбор параметров и пропорций при составлении бюджета — все это можно сделать в Excel с помощью полосы прокрутки (ScrollBar), одного из элементов ActiveX. Это удобно, когда руководство требует предоставить в наглядной форме информацию, например, о том, как при повышении цены на выпускаемую продукцию изменится рентабельность продаж. Как это сделать, мы расскажем в данной статье.

 

Для начала разрабатываем финансовую модель, то есть выбираем влияющие показатели, определяем их предельные значения, устанавливаем для каждого показателя специальный элемент управления — полосу прокрутки. В качестве примера рассмотрим модель точки безубыточности. Напомним, что объем продаж в этой точке в натуральном выражении (Q, шт.) определяется по формуле:

Q = Зпост / (Ц – C),

где Зпост — постоянные затраты, руб.;

      Ц — цена реализации единицы продукции, руб./шт.;

      С — переменные расходы на единицу продукции (себестоимость), руб./шт.

Также в модели будут задействованы еще два показателя: прибыль (П) и рентабельность продаж (Р):

П = Ц × Q – (Зпост + C × Q);

Р = П / (Ц × Q).

 

Далее выбираем показатели, влияющие на модель. В нашем случае это цена, себестоимость единицы продукции, постоянные расходы, отклонение планируемого объема продаж от объема продаж в точке безубыточности (∆Q).

Предельные значения перечисленных показателей компании «Альфа», выпускающей электротехническое оборудование, представлены в табл. 1 (напоминаем, что все расчеты ведутся в Excel).

 

Таблица 1. Предельные значения показателей компании «Альфа»

Показатель

Предельное значение

минимальное

максимальное

Цена реализации (Ц), руб.

10 000 000

20 000 000

Себестоимость единицы продукции (С), руб./шт.

5 000 000

9 000 000

Постоянные расходы (Зпост), руб.

20 000 000

110 000 000

Отклонение планируемого объема продаж от объема продаж в точке безубыточности (∆Q), %

0

100

 

Размещаем для каждого показателя полосу прокрутки. На вкладке «Разработчик» в группе «Элементы управления» нажимаем кнопку «Вставить», далее в раскрывшемся списке в разделе «Элементы ActiveX» кликаем по значку полосы прокрутки и устанавливаем в любое удобное место рабочего листа (рис. 1).

 

Рис. 1. Полосы прокрутки показателей

 

Так, для показателя «Себестоимость единицы продукции» в контекстном меню выбираем «Свойства» и далее в строках Min и Max открывшегося окна Properties указываем соответствующие цифры — 5 000 000 и 9 000 000. В строке LinkedCell записываем ячейку В19: там будет отображаться значение 5 150 345, установленное в строке Value (рис. 2).

Рис. 2. Свойства полосы прокрутки, характеризующей показатель «Себестоимость единицы продукции»

 

Далее переходим к автоматизированному управлению ограничениями. Для этого стоит создать макрос и кнопку (ее можно назвать, например, «Установка ограничений»). Каждому максимальному и минимальному значению присваиваем имя. Так, показателю «Цена» в ячейках С16 и Н16 присваиваем соответственно имена Цmin и Цmax. Затем создаем саму кнопку. Для этого переходим на вкладку «Разработчик» и выбираем один из элементов ActiveX — значок, который обозначает кнопку (CommandButton). Далее в контекстном меню выбираем «Свойства» и в открывшемся окне Properties в строке Caption присваиваем кнопке имя «Установка ограничений» (рис. 3).

Рис. 3. Свойства для кнопки «Установка ограничений»

 

Затем для кнопки создаем макрос: заходим в редактор Visual Basic и задаем правила для автоматического изменения полос прокрутки:

Private Sub CommandButton1_Click()
    ScrollBar1.Min = Range("Цmin")
    ScrollBar1.Max = Range("Цmax")
    ScrollBar2.Min = Range("Cmin")
    ScrollBar2.Max = Range("Cmax")
    ScrollBar3.Min = Range("Зпостmin")
    ScrollBar3.Max = Range("Зпостmax")
    ScrollBar4.Min = Range("dQmin")
    ScrollBar4.Max = Range("dQmax")
End Sub

 

Поясним, что ScrollBar1, ScrollBar2, ScrollBar3, ScrollBar4 — это порядковые номера и коды соответствующих полос;

Цmin и Цmax, Cmin и Cmax, Зпостmin и Зпостmax, dQmin и dQmax — заданные имена ячеек с минимальным и максимальным значениями.

 

Обратите внимание!
Текст кода повторяется, меняются только порядковые номера элементов (отражаются в окне «Имя» слева от строки формул при выборе объекта в режиме конструктора) и адреса ячеек. После введения новых минимальных или максимальных значений исходных параметров остается только нажать кнопку, и диапазон значений на полосах прокрутки автоматически поменяется.

 

Далее переходим к построению модели определения точки безубыточности, которая состоит из таблицы и графика.

Таблица состоит из восьми столбцов:

  • Отклонение от точки безубыточности (∆Q);
  • Объем продаж (Q);
  • Переменные затраты (Зпер.);
  • Постоянные затраты (Зпост.);
  • Общие затраты (Зобщ.);
  • Выручка (В);
  • Прибыль (П);
  • Рентабельность продаж (Р).

Начнем заполнять таблицу. Сначала задаем объем продаж в точке безубыточности. В Excel формула будет такой:

=B22/(B16-B19),

где B22 — постоянные затраты, руб.;

B16 — цена реализации, руб./шт.;

B19 — удельные переменные затраты, руб./шт. (все значения заданы с помощью полос прокрутки).

Переходим к первому столбцу таблицы. В нем отражается процентное отклонение объема продаж от аналогичного показателя в точке безубыточности (∆Q). Причем максимальное отклонение (в примере — A12) должно быть приравнено к значению ∆Q на полосе прокрутки (B25). Формула в ячейке A12 выглядит так:

=B25/100.

В ячейке, соответствующей точке безубыточности (A8), значение должно быть равно 0. В диапазон A4:A7 вводим формулу:

=A[j]-($A$12-$A$8)/(СТРОКА($A$12)-СТРОКА($A$8))»,

где j — номер строки последующей ячейки.

Для диапазона A10:A12 формула аналогична, только после A[j] ставится минус, а сама переменная j обозначает номер строки предыдущей ячейки. Данные в других графах таблицы рассчитываются по приведенным в заголовках формулам (рис. 4).

 

Рис. 4. Формулы для модели точки безубыточности

 

В нашем примере прибыль будет равна 0 при условии, что объем продаж составит 4 шт., переменные затраты на единицу выпускаемой продукции — 5 150 345 руб./шт., а постоянные затраты — 27 849 899 руб. (табл. 2). Если же отклонение от точки безубыточности составляет 35 %, например, это значит, что компания выпустит продукцию в количестве 6 шт., при этом выручка составит 66 717 580 руб., а рентабельность продаж — 15 %.

 

Таблица 2. Анализ точки безубыточности продукции, выпускаемой компанией «Альфа»

Отклонение от точки безубыточности (∆Q), %

Объем продаж (Q), шт.

Переменные затраты (Зпер.), руб.

Постоянные затраты (Зпост.), руб.

Общие затраты (Зобщ.), руб.

Выручка (В), руб.

Прибыль (П), руб.

Рентабельность продаж (Р), %

-

 = Q0 * (1+∆Q)

 = C * Q

 = Зпост.

 = Зпост. + C * Q

 = Ц * Q

 = В - Зобщ.

 = П / В

–35 %

3

14 020 845

27 849 899

41 870 744

32 123 279

-9 747 465

-30%

–26 %

3

15 908 266

27 849 899

43 758 165

36 447 567

-7 310 598

-20%

–18 %

3

17 795 688

27 849 899

45 645 587

40 771 855

-4 873 732

-12%

–9 %

4

19 683 109

27 849 899

47 533 008

45 096 142

-2 436 866

-5%

0 %

4

21 570 531

27 849 899

49 420 430

49 420 430

0

0%

9 %

5

23 457 952

27 849 899

51 307 851

53 744 717

2 436 866

5%

18 %

5

25 345 374

27 849 899

53 195 273

58 069 005

4 873 732

8%

26 %

5

27 232 795

27 849 899

55 082 694

62 393 293

7 310 598

12%

35 %

6

29 120 217

27 849 899

56 970 116

66 717 580

9 747 465

15%

 

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

 

Рис. 5. График точки безубыточности

 

На оси Х фиксируются значения объема продаж (по рис. 4 это диапазон В4:В12). В дополнение можно также настроить и его автомасштабирование, чтобы пересечение прямых всегда располагалось по центру оси значений. При перемещении бегунка в полосах прокрутки график будет меняться автоматически. Для этого понадобится определить ее основные параметры и внести небольшое дополнение в ранее созданный макрос:

  • нижнее значение, в примере задается в ячейке B27 формулой:

=ОКРУГЛВНИЗ(МИН(E4:F12)/1000;0)*1000, ячейке присваивается имя «Мин»;

  • верхнее значение задается в ячейке B28:

=ОКРУГЛВВЕРХ(МАКС(E4:F12)/1000;0)*1000, ячейке присваивается имя «Макс»);

  • масштаб делений задается в ячейке B29:

=МАКС((C28- C27)/10;1), присваивается имя Дел.

Далее включаем режим конструктора (рис. 6). После добавления в макрос следующего текста (в пределах открывающей и закрывающей строк, содержащих слово Sub, и над блоком, который был создан ранее) должен получиться в целом такой макрос:

 

Private Sub CommandButton1_Click()

With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
      .MinimumScale = Range("Мин")
      .MaximumScale = Range("Макс")
      .MajorUnit = Range("Дел")
   End With

   ScrollBar1.Min = Range("Цmin")
   ScrollBar1.Max = Range("Цmax")
   ScrollBar2.Min = Range("Cmin")
   ScrollBar2.Max = Range("Cmax")
   ScrollBar3.Min = Range("Зпостmin")
   ScrollBar3.Max = Range("Зпостmax")
   ScrollBar4.Min = Range("dQmin")
   ScrollBar4.Max = Range("dQmax")
End Sub

 

Рис. 6. Включение Режима конструктора

 

Таким образом, мы создали динамическую модель с настраиваемыми значениями показателей и их ограничений. Передвигая любой из ползунков, можно увидеть, как влияет изменение соответствующего показателя на конечно-целевой результат. Например, при цене выпускаемой продукции 19 129 554 руб., себестоимости единицы продукции 8 002 012 руб., постоянных затратах 54 137 931 руб. и рентабельности продаж 12 % прибыль должна составить 14 211 207 руб., а объем реализации — 6 шт. (табл. 3).

 

Таблица 3. Показатели продаж в компании «Альфа» при реализации продукции по цене 19 129 554 руб.

Отклонение от точки безубыточности (∆Q), %

Объем продаж (Q), шт.

Переменные затраты (Зпер.), руб.

Постоянные затраты (Зпост.), руб.

Общие затраты (Зобщ.), руб.

Выручка (В), руб.

Прибыль (П), руб.

Рентабельность продаж (Рпродаж), %

 

= Q0 * (1+∆Q)

= C * Q

= Зпост.

= Зпост. + C * Q

= Ц*Q

= В – Зобщ.

= П / В

26 %

6

49 151 072

54 137 931

103 289 003

117 500 210

14 211 207

12 %

 

Использование на практике такого рода инструмента позволит владеть всей информацией в достаточно простой, доступной и наглядной форме, представленной в финансовых моделях, изменяя необходимые параметры.

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

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

Купить этот номер в электронном виде

Подпишитесь на нашу рассылку

Рассылка о новых материалах в блоге и новых номерах журналов. Отправляется в среднем 1 письмо в 2 недели.