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

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

Для наглядного предоставления результатов исполнения бюджета по окончании отчетного периода можно создать в Excel модель «Светофор». Для этого необходимо:

Как подготовить исходные данные для анализа отклонений в Excel

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

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

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

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

Таким образом, исходные данные, показанные на рисунке 2, представляются сводной таблицей с рисунка 3.

Как задать диапазон отклонений для анализа бюджета в Excel

Чтобы оформить план-факт отклонения от бюджета в виде индикатора-светофора, необходимо свести характеризующие его числовые значения в три группы в зависимости от степени критичности отклонения (см. рисунок 4. Группирование показателей исполнения плана):

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

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

Правило пересчета определяется в два этапа.

1. Каждому состоянию присваивается числовой номер – код состояния:

2. Коды состояний сопоставляются с границами интервалов, как это представлено в таблице ниже.

Как создать индикатор-светофор для иллюстрации отклонений от бюджета в Excel

Индикатор-светофор можно добавить в виде дополнительного столбца в стандартную таблицу управленческого отчета вместе с набором элементов управления им. Такая таблица представляет собой информационную панель, характеризующую исполнение бюджета в целом (см. рисунок 1. Таблица анализа план-факт отклонений).

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

Выпадающий список. Для каждого выпадающего списка сначала необходимо составить справочник значений (подробнее см.  Как исключить ошибки при вводе данных в Excel). Важно, чтобы элементы, перечисленные в каждом из справочников, соответствовали множеству уникальных значений одного из столбцов исходной таблицы. Для компании «Альфа» в информационной панели (см. рисунок 1. Таблица анализа план-факт отклонений) можно выбрать:

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

Распределение аналитик сводной таблицы между списками и осями отчета определяется потребностями пользователя. Иначе говоря, если отчет составляется в разрезе отдельных дирекций (наименования подразделений перечисляются как заголовки строк таблицы), пользователь может выбирать годы, кварталы и регионы. Если же важно оценить ситуацию по всей компании в динамике по периодам, отчет можно перенастроить – перенести в заголовки строк годы и кварталы, а все остальные параметры сделать варьируемыми.

Работу с отчетом можно существенно упростить, если:

Плановые и фактические данные. Готовая форма должна автоматически заполняться данными в соответствии с выбранными значениями из выпадающих списков. Данные извлекаются из сводной таблицы – плановые (столбец I) и фактические (столбец J) – при помощи функции
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных, сводная_таблица, [поле1, элем1, поле2, элем2],...), где:

Эту функцию необходимо прописать в ячейке I5 в следующем виде:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";$O$4;"Год";Год;"План_Факт";"План"; "Регион";Регион;"Дирекция";$H5;"Квартал";Квартал).

В этой формуле:

В ячейку J5 заносится аналогичная формула, но в третьем элементе (координате) значение «План» заменяется на «Факт»:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";$O$4;"Год";Год;"План_Факт";"Факт"; "Регион";Регион;"Дирекция";$H5;"Квартал";Квартал).

План-факт отклонения. Для отображения отклонений в таблице в формате индикатора «Светофор» нужно воспользоваться двумя функциями:

Итоговое выражение будет представлено следующим образом:
=ИНДЕКС($E$13:$E$17;ПОИСКПОЗ(J5/I5*100;$D$13:$D$17;1)).

Теперь, выделив ячейки столбца К, нужно перейти во вкладку меню «Условное форматирование» и создать новое правило «Форматировать все ячейки на основании их значений». В качестве стиля выбрать «Набор значков», а правила задать для пороговых уровней отклонений, отмеченных в таблице.

Павел Сухарев , начальник отдела планирования, бюджетного контроля и финансовой отчетности ЗАО «Глобус Телеком»

В отчете о план-факт отклонениях, например, типа модели «Светофор», и исходной (сводной) таблице обозначения временных периодов могут различаться.

В сводной таблице кварталы могут быть представлены кодами – 1Q, 2Q, 3Q и 4Q. Именно на них будет ссылаться формула: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ().

А пользователь в своей форме отчета должен видеть полный формат:

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

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

Школа

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

Записаться

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

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

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

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

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

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

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

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

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

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

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

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

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