Как быстрее составлять бюджеты в Excel, содержащие большое количество аналитик

Предположим, есть незаполненная форма плана продаж на три квартала, созданная в Excel. В ней всего две аналитики (или иначе – измерения) – «Месяц» (столбцы по горизонтали) и «Товар» (строки по вертикали). Если бы все этим и ограничивалось, то ее заполнение не составляло бы труда.

Но что если компания, которой предстоит составить план продаж, представлена в трех регионах – Центр, Юг и Север, а реализацией ее продукции занимаются две коммерческие дирекции (№ 1 и № 2). Чтобы учесть эти условия, при бюджетировании в форму плана продаж нужно добавить две новые аналитики – «Дирекция» и «Регион». Двухмерная таблица превращается в матрицу, состоящую из четырех измерений, – период, товар, дирекция и регион (см. рисунок 1. План продаж с расширенным составом аналитик).

Рисунок 1. План продаж с расширенным составом аналитик

Рисунок 1. План продаж с расширенным составом аналитик

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

Какие вспомогательные таблицы нужно составить

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

Результат доработки бюджета представлен на рисунке 2. Обратите внимание, что в план продаж был добавлен аналитический признак «Показатель», имеющий всего два значения: «Количество» и «Доход». Он позволит составить бюджет не только в стоимостном (аналитика «Доход»), но и в натуральном выражении (аналитика «Количество»). Это важно, так как планирование будущих объемов реализации всегда начинается с ответа на вопрос, сколько продадим в штуках (килограммах, литрах и т. д.).

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

Например, коммерческая дирекция № 2 не работает в регионе Центр и соответственно не реализует там никаких товаров. Но в плане продаж для нее все равно резервируются строки с аналитиками «Центр – Товар А», «Центр – Товар B», «Центр – Товар С» (см. рисунок 2. Дополнительные таблицы для составления бюджета продаж). Хотя на результат дальнейших расчетов эти строки не повлияют (в них проставлены нули), они нужны, чтобы Excel правильно обрабатывал данные. Еще одно ограничение: в таблице не должно быть никаких промежуточных вычислений (например, «Итоги по региону»), только количественные и стоимостные планы по реализации.

Рисунок 2. Дополнительные таблицы для составления бюджета продаж

Рисунок 2. Дополнительные таблицы для составления бюджета продаж

Следом создаются таблицы с исходными данными для построения плана.

Допустим, в компании из примера продажи планируются так. Сначала для каждой дирекции устанавливается норматив по объему реализации в натуральном выражении – сколько в среднем нужно продать товара за месяц в том или ином регионе (среднемесячная норма продаж, шт.). Этот норматив умножается на коэффициент сезонности определенного месяца и плановую цену реализации – бюджет продаж готов.

Значит, в Excel нужно составить три вспомогательные таблицы: «Цена товаров», «Месячная норма продаж» и «Коэффициенты сезонности».

В первых двух таблицах задействованные аналитики раскрываются в том же порядке, что и в плане продаж (слева направо: дирекция, регион, товар). Например, в таблице «Цена товаров» используются только два аналитических признака – «Регион», «Товар» (цены отличаются по регионам и продуктам, но не по дирекциям) и располагаются в очередности: «Регион», потом «Товар». Те же правила применяются и для формы «Месячная норма продаж» (см. рисунок 2. Дополнительные таблицы для составления бюджета продаж). В таблице «Коэффициент сезонности» месяцы должны быть расположены так же (по горизонтали), как и в плане продаж. Когда все три формы будут составлены, останется заполнить их вручную.

Как рассчитать плановые значения

После создания дополнительных таблиц вся подготовительная работа закончена. Можно переходить к вычислениям плана продаж в натуральном и стоимостном выражении.

Чтобы упростить последующую работу с формулами, стоит присвоить собственные имена диапазонам ячеек с исходными данными. Выделим в таблице «Цена товаров» ячейки с ценами (на рисунке 2. Дополнительные таблицы для составления бюджета продаж это диапазон ячеек F3–F11), нажимаем правую кнопку мыши, выбираем из списка «Имя диапазона» и в появившемся окне в верхней строке «Имя» введем название «Тариф». Аналогичные операции выполняются и для диапазонов, содержащих среднемесячные нормы продаж для дирекций. Назовем их «Норма_Дир_1» (диапазон ячеек L3–L11) и «Норма_Дир_2» (диапазон ячеек L12–L20) соответственно. Наконец диапазон с сезонными коэффициентами обозначим как SF (диапазон ячеек D24–L24).

Итак, предстоит рассчитать помесячный план продаж в разрезе дирекций, регионов и товаров. В Excel есть специальный инструмент для одновременных вычислений по диапазону ячеек – «Формула массива». С его помощью можно без труда задать единые правила расчета для всего плана продаж сразу.

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

Начнем с коммерческой дирекции № 1. Выделим в таблице «Бюджет продаж» строки со значением «Количество» (столбец «Показатель») и «Дирекция № 1» (столбец «Дирекция») (на рисунке 2 Дополнительные таблицы для составления бюджета продаж область выделения соответствует диапазону ячеек R4–Z12), после чего в строке формул введем выражение: {= Норма_Дир_1 * SF}.

Для того чтобы появились фигурные скобки в формуле, достаточно после ее написания нажать сочетание клавиш Ctrl – Shift – Enter.

Поскольку «Норма_Дир_1» обозначает диапазон ячеек с месячными нормами реализации для дирекции № 1 в разрезе продуктов и регионов, а SF – значения коэффициентов сезонности для планируемых месяцев, получаем объем продаж дирекции № 1 с детализацией по месяцам, товарам и регионам.

Аналогично рассчитывается план продаж в натуральном выражении дирекции № 2, формула – {= Норма_Дир_2 * SF}. При вычислении плана по реализации в стоимостном выражении действия будут теми же. Отличие лишь в том, что в формуле появится еще один элемент – цена. Например, бюджет по выручке дирекции № 1 будет определяться с помощью выражения {= Норма_Дир_1 * SF * Тариф}, дирекции № 2 – {= Норма_Дир_2 * SF * Тариф}.

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

Как сделать аналитику в бюджете наглядной

Итоговая таблица несколько сложна для восприятия и анализа. Конечно, можно выбрать нужные данные с помощью автофильтра (вкладка «Главная» – «Сортировка и фильтр» – «Фильтр»), но это не оптимальное решение. Лучше оставить эту таблицу как есть и использовать ее как хранилище данных. А уже на ее основе создать многомерный настраиваемый бюджет с помощью «Сводных таблиц».

Выделим в бюджете продаж все строки (кроме шапки таблицы), а затем построим на основании этого диапазона сводную таблицу (вкладка «Вставка» – «Сводная таблица»). Останется лишь выбрать порядок размещения в таблице аналитик «Показатель», «Дирекция», «Регион» «Товар» и перетащить соответствующие поля в макет сводной таблицы (см. рисунок 3. Сводный бюджет). Их можно расположить как по вертикали, так и по горизонтали в области данных таблицы в любой удобной последовательности.

Рисунок 3. Сводный бюджет
Рисунок 3. Сводный бюджет

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

Для примера добавим в сводную таблицу две строки, в которых будут суммироваться планы продаж центрального и южного регионов в разрезе дирекций. Новый элемент аналитики «Регион» назовем «Макрорегион».

Для этого надо переместить курсор мыши в одну из строк поля «Регион» и во вкладке «Работа со сводными таблицами» – «Параметры», кликнуть «Формулы», а затем «Вычисляемый объект». В появившемся диалоговом окне указать имя «Макрорегион» и формулу для расчетов «= Центр + Юг» (см. рисунок 4. Сводный бюджет с аналитикой «Макрорегион»).

Рисунок 4. Сводный бюджет с аналитикой «Макрорегион»

Рисунок 4. Сводный бюджет с аналитикой «Макрорегион»

В сводной таблице из примера, кроме аналитических признаков (измерений), есть также и численные показатели (продажи или объем реализации в натуральном выражении). В примере они перечислены по горизонтали (в макете сводной таблицы размещены в области данных). В терминологии кубов данных (OLAP) такие показатели называются мерами. Их также можно дополнить расчетными данными. Действия те же, что и при вычислениях по «Макрорегиону». Только на этот раз добавляется вычисляемое поле (столбец), а не объект.

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

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

Рубрики: Метки: ,

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.