Наглядный способ балансировки финансовых показателей в Excel

11391
Сапунов Сергей
финансовый директор ООО «Сибагрохолдинг»
Цели: упростить подбор параметров и пропорций при составлении бюджета, визуализировать анализ «что – если», а также настройку коэффициентов и их весов в мотивационных моделях. Как действовать: выбрать влияющие показатели, определиться с их предельными значениями, установить для каждого специальный элемент управления – полосу прокрутки.

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

Если для каждого из рассматриваемых параметров или аргументов задать по такой полосе, получится своего рода финансовый «эквалайзер». Передвигая любой из его ползунков, можно сразу увидеть, как влияет изменение соотвествующего параметра на итоговый результат. Все просто, доступно и наглядно.

Рассмотрим, как воспользоваться этим инструментом в Excel 2007, на примере модели определения точки безубыточности. Объем продаж в этой точке в натуральном выражении определяется по формуле:

Qbep = FC : (P – C),  

где FC – постоянные затраты, руб.; P – цена реализации единицы продукции, руб/шт.; С – переменные расходы на единицу продукции (себестоимость), руб/шт.

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

Pr = P Q – (FC + C Q),

ROS = Pr : (P Q),

где Q – объем продаж в натуральном выражении, шт. Он может совпасть с безубыточным (Qbep) или отклониться от него на заданный процент (ΔQ).

Для начала выберем факторы, влияющие на модель. Очевидно, что это цена (P), переменные расходы на единицу продукции (С), постоянные расходы (FC), а также отклонение планируемого объема продаж от объема продаж в точке безубыточности (ΔQ). Исход­ные параметры известны, приступим к их визуализации.

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

Следом определяем минимальное и максимальное значение цены, в пределах которой она будет меняться с помощью нового инструмента. Допустим, в компании решили, что будут продавать свою продукцию по цене не ниже 50, но и не больше 70 рублей за штуку. Кликаем правой клавишей мышки по полосе прокрутки, в контекстном меню выбираем «Свойства» и далее в строках Min и Max открывшегося окна Properties указываем соотвествующие цифры – 50 и 70 (см. рис. 2).

Наглядный способ балансировки финансовых показателей в Excel

И это еще не все. Зададим в том же окне, на сколько рублей будет меняться цена при клике мышкой по ползунку, а также по самой полосе слева или справа от него (шаг обычного и страничного изменения). Предположим, на рубль и на 10 рублей. Впишем эти значения в строки SmallChange и LargeChange соответственно. Остается указать, в какой именно ячейке будет отображаться цена, заданная с помощью полосы прокрутки (в примере это B17). Аналогично настраиваются полосы и для других факторов.

Если понадобится пересмотреть предельные значения того или иного фактора, вовсе не обязательно каждый раз менять свойства соотвествующей полосы прокрутки. Можно сделать так, чтобы минимум и максимум подхватывались из определенных ячеек. И поможет в этом небольшой макрос и назначенная ему кнопка. Но прежде чем их создать, укажем нижнюю и верхнюю границы диапазона точно под полосой прокрутки. К примеру, для цен в ячейках С17 и H17 (см. рис. 1). Последним присвоим уникальные имена Pmin и Pmax (в окне «Имя» слева от строки формул).

Далее снова перейдем на вкладку «Разработчик» и выберем из известных уже элементов ActiveX значок, обозначающий кнопку. На листе она по­явится со своим первоначальным названием CommandButton. Чтобы ее переименовать, нужно кликнуть по ней правой кнопкой мышки, выбрать «Свойства» и в открывшемся окне в поле Caption ввести новое название. Например, «Установка ограничений» (если название не умещается в области кнопки и требуется включить перенос, установить флажок WordWrap в положение True).

Теперь откроем редактор Visual Basic (одноименный пункт в группе «Код» панели «Разработчик» или «Исходный текст» в контекстном меню) и зададим для кнопки специальный макрос. Он совсем небольшой, и его написание не потребует особых навыков в программировании.

Поскольку кнопка была создана первой, программа автоматически присвоила ей код CommandButton1. В окне редактора курсор будет стоять между двумя строками, обозначающими начало макроса [Private Sub CommandButton1_Click()] и его конец [End Sub]. Как раз именно здесь и нужно вписать две строчки для автоматического изменения полосы прокрутки «Цена реализации»:

ScrollBar1.Min = Range("Pmin")

ScrollBar1.Max = Range("Pmax")

где ScrollBar1 – порядковый номер и код соответствующей полосы, Pmin и Pmax – ранее заданные имена ячеек с минимальной и максимальной ценой.

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

«Финансовая» панель управления готова. Можно отключить режим конструктора – отжать одноименную кнопку на вкладке «Разработчик» – и приступить к работе над самой моделью.

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

рисунок 1. Анализ точки безубыточности

Наглядный способ балансировки финансовых показателей в Excel

Начнем заполнять таблицу. Первое, что нужно сделать, – задать объем продаж в точке безубыточности. В Excel формула будет такой: «=B23/(B17-B20)», где B23 – постоянные затраты, руб.; B17 – цена реализации, руб/шт.; B20 – удельные переменные затраты, руб/шт. (все значения заданы с помощью полос прокрутки).

Переходим к первому столбцу таблицы. В нем отражается процентное отклонение объема продаж от аналогичного показателя в точке безубыточности (ΔQ). Причем максимальное отклонение (в примере – A13) должно быть приравнено к значению ΔQ на полосе прокрутки (B26). Но поскольку показатель ΔQ определяется в процентах, а значение в полосе прокрутки – в единицах, последнее необходимо разделить на 100. Отсюда формула в ячейке A13 выглядит так: «=B26/100». В ячейке, соответствующей точке безубыточности (A9), значение должно быть равно 0. В диапазон A5:A8 вводим формулу «=A[j]-($A$13-$A$9)/(СТРОКА($A$13)-СТРОКА($A$9))», где j – номер строки последующей ячейки. Для диапазона A10:A12 формула аналогична, только после A[j] ставится минус, а сама переменная j обозначает номер строки предыдущей ячейки. Данные в других графах таблицы рассчитываются по приведенным в заголовках формулам

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

Параметры такие:

  • нижнее значение (в примере задается в ячейке B28 формулой «=ОКРУГЛВНИЗ(МИН (E5:F13)/1000;0)*1000», ячейке присваивается имя «Мин» );
  • верхнее значение (B29, «=ОКРУГЛВВЕРХ(МАКС(E5:F13)/1000;0)*1000», «Макс»);
  • масштаб делений (B30, «=МАКС((C29- C28)/10;1)», «Дел»).

Cнова включим режим конструктора и добавим в макрос следующий текст (в пределах открывающей и закрывающей строк, содержащих слово Sub, и над блоком, который был создан ранее):

With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)

.MinimumScale = Range("Мин")

.MaximumScale = Range("Макс")

.MajorUnit = Range("Дел")

End With

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

Дополнительный материал: файл Excel с моделью расчета точки безубыточности при помощи элементов управления. Внимание: для корректной работы файла необходимо разрешить доступ макросам и элементам ActiveX.

Вложенные файлы

Доступно только подписчикам
  • Файл Excel с моделью расчета точки безубыточности при помощи элементов управления.zip


Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Рассылка



© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет всего 1 минуту.

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Внимание! Вы читаете профессиональную статью для финансиста.
Зарегистрируйтесь на сайте и продолжите чтение!

Это бесплатно и займет всего 1 минут.

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль