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

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

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

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

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

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

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

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

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

Р = П / (Ц × Q).

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

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

Размещаем для каждого показателя полосу прокрутки. На вкладке «Разработчик» в группе «Элементы управления» нажимаем кнопку «Вставить», далее в раскрывшемся списке в разделе «Элементы 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 и задаем правила для автоматического изменения полос прокрутки:

Поясним, что 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 %.

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

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

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

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

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

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

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

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

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

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

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

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

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