Простое решение для безошибочной и быстрой консолидации бюджетов

7252
Сухарев Павел
начальник отдела планирования, бюджетного контроля и финансовой отчетности ЗАО «Глобус Телеком»
Если компании нужно составить консолидированный бюджет, например, продаж по нескольким своим магазинам, это можно сделать за считаные минуты с помощью «Сводных таблиц» сервиса Excel. Все, что понадобится в качестве исходных данных, – унифицированные планы реализации товаров через торговые точки.

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

Каждая дирекция составляет полугодовой бюджет продаж в отдельном файле Excel. Данные о будущей реализации товаров представлены в разрезе наименований товаров и с разбивкой по месяцам, также указывается натуральный объем продаж и сумма.

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

  • календарный период (месяц);
  • дирекция (оптовая или розничная);
  • регион;
  • натуральные или стоимостные показатели.

А теперь пошагово соберем сводный бюджет с помощью Excel.

Шаг 1. Доработка исходных данных

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

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

Рисунок 1. Доработанный бюджет продаж оптовой коммерческой дирекции

Простое решение для безошибочной и быстрой консолидации бюджетов

По аналогии придется доработать и все остальные бюджеты продаж, а именно план реализации оптовой коммерческой дирекции по региону № 2 и план розничной коммерческой дирекции по региону № 1. После этого можно приступать к консолидации.

Шаг 2. Консолидация бюджетов

Сводный бюджет сделаем в отдельном файле Excel*. Создаваемая в нем таблица будет ссылаться на бюджеты коммерческих дирекций (они тоже представлены двумя отдельными файлами), доработанные на предыдущем шаге. Такая схема при необходимости поз¬волит коммерческим дирекциям независимо друг от друга корректировать исходные данные, и они будут автоматически учтены в консолидированном плане (достаточно кликнуть на сводной таблице правой кнопкой мыши и выбрать опцию «Обновить»).

Итак, в Excel открыт новый пустой файл, а также еще два файла: один с планом продаж оптовой коммерческой дирекции по регионам № 1 и № 2, другой –розничной дирекции по региону № 1. Переходим в пока еще пустой файл, открываем меню «Данные» и кликаем по команде «Сводная таблица». Откроется окно мастера построения сводной таблицы. Здесь важно не просто нажать кнопку «Далее», а указать, что сводная таблица будет создаваться на основе данных, расположенных в нескольких диапазонах консолидации.

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

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

Рисунок 2. Выбор полей для сводной таблицы

Простое решение для безошибочной и быстрой консолидации бюджетов

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

  • дирекция продаж («Розница» или «Опт»);
  • регион. Обозначим первый регион продаж как «Регион 1», а второй – «Регион 2»;
  • измерение. В примере данные приводятся в натуральном и денежном выражениях. Присвоим соответственно им такие метки – «Количество» и «Сумма».

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

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

Рисунок 3. Первый вариант консолидированного бюджета

Простое решение для безошибочной и быстрой консолидации бюджетов

Шаг 3. Настройка сводного бюджета

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

Рисунок 4. Финальный вариант консолидированного бюджета

Простое решение для безошибочной и быстрой консолидации бюджетов

Обратите внимание, что по умолчанию сводная таблица суммирует все исходные данные, невзирая на то, указаны они в натуральных единицах измерения или в рублях. Чтобы избежать этого, необходимо выбрать в поле «Измерение» (сверху над сводным бюджетом) нужный режим представления продаж (в примере на рис. 5 выбрано измерение «Сумма» – в стоимостном выражении) и работать только с натуральным или денежным планом.

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

Заголовок появившейся группы автоматически получает имя «Группа 1». Чтобы изменить его на «Трубы», нужно просто ввести в ячейку, содержащую это имя, новое название. На этом сводный бюджет можно считать законченным (см. рис. 5). Или почти законченным.

Шаг 4. Добавление расчетных полей

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

Предположим, что в торговой компании, о которой шла речь ранее, 60 процентов продукции отпускается клиентам на условиях предоплаты, 30 процентов – с отсрочкой платежа до месяца и оставшиеся 10 процентов – с отсрочкой до двух месяцев. К примеру, выручка от реализации в январе будет поступать на счета компании следующим образом: 60 процентов средств в том же месяце (в январе), 30 процентов в феврале, 10 процентов в марте.

На это распределение можно посмотреть и с другой стороны – относительно месяца поступления денег. К примеру, в марте на счета компании должны поступить: 60 процентов от выручки марта, 30 процентов от выручки февраля, 10 процентов от выручки января. Соответственно формула следующая:

Поступление денежных средств в марте = (0,6 Выручка в марте + 0,3 Выручка в феврале + + 0,1 Выручка в январе) (1 + НДС).

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

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

Рисунок 5. Добавление формул в сводную таблицу

Простое решение для безошибочной и быстрой консолидации бюджетов

В появившемся диалоговом окне «Вставка вычисляемого объекта» в поле «Месяц» присвоить имя новому столбцу, в котором будут выводиться суммы поступлений денежных средств (в примере имя – «Март_ДС») и ввести формулу, приведенную выше. Обратите внимание: в качестве аргументов формулы будут использоваться названия столбцов сводной таблицы, содержащих помесячные данные о продажах (в примере они обозначены как «Май», «Июнь» и пр.). В диалоговом окне они перечислены под заголовком «Элементы». Аналогично задается расчет входящих денежных потоков и для других месяцев.

Стоит отметить, что в Excel вычисляемые объекты сводных таблиц (те самые дополнительные расчетные значения) по умолчанию участвуют в подведении общих итогов по строкам или столбцам. Иначе говоря, в примере выручка и поступления денежных средств попросту окажутся просуммированными. Если оставить это без внимания, есть риск получить некорректные плановые данные. Убрать эти элементы из расчета итогов нельзя, гораздо проще отключить сам подсчет итогов (команда меню «Данные» – «Сводная таблица» – «Параметры», убрать галочку рядом с надписью «Общая сумма по строкам»).

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

Методические рекомендации по управлению финансами компании



Ваша персональная подборка

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

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

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

    Школа

    Школа

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

    Записаться

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

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

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

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

    А еще...




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

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

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

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

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

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

    Внимание!
    Вы читаете профессиональную статью для финансиста.
    Зарегистрируйтесь на сайте и продолжите чтение!

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

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