text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

Как создать наглядный инструмент для проведения анализа «что – если» в Excel

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

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

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

Формула 1. Расчет объема продаж в точке безубыточности в натуральном выражении

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

Формула 2. Расчет прибыли

Формула 3. Расчет рентабельности продаж

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

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

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

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

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

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

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

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

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

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

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

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

При заполнении таблицы первое, что нужно сделать, – задать объем продаж в точке безубыточности. В 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). При перемещении бегунка в полосах прокрутки график будет меняться автоматически. В дополнение можно также настроить и его автомасштабирование, чтобы пересечение прямых всегда располагалось по центру оси значений. Для этого понадобится определить ее основные параметры и внести небольшое дополнение в ранее созданный макрос.

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

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

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

Обратите внимание, что для корректной работы описанного инструментария необходимо разрешить доступ макросам и элементам ActiveX.

Рекомендации по теме

Школа

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

Записаться

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

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

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

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

Это бесплатно и займет меньше минуты!

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

Зарегистрируйтесь или войдите на сайт:

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

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

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

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

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

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

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

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

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

Зарегистрируйтесь или войдите на сайт:

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

Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.