Как быстро делать отчеты в Excel
Сегодня я хочу предложить вам несколько приемов, которые существенно ускоряют подготовку отчетов в Excel. В частности отчета по факту исполнения бюджета.
Если план-факт анализ проводиться не в самой учетной программе, то зачастую, процесс подготовки отчета сводиться к выполнению следующих операций:
1) Выгрузка данных из учетной программы, например бухгалтерских данных из программы 1 С в Excel;
2) Классификация бухгалтерских данных о затратах и приведение их к номенклатуре статей бюджета используемых в управленческом учете.
3) Сопоставление плана с фактом и расчет отклонений в Excel.
Если у вас не настроен план-факт анализ в разрезе статей управленческого учета прямо в учетной программе (например, в 1 С), то так или иначе необходимо будет выполнить выше указанные действия.
Для наглядности приведу пример, как это порою выполняется.
В программе 1С формируется отчет о затратах, например, оборотно-сальдовые ведомости по 20, 25, 26, 44 и 91 счетам за некий период и выгружается в Excel. Далее, в ручную, копированием или при помощи ссылок данные переносятся в файл с планом с одновременной реклассификацией, как говориться «на лету».
В зависимости от объема работы этот процесс может длиться от нескольких часов до нескольких дней, если например мы имеем несколько филиалов или предприятий холдинга и построение консолидированного отчета.
Просьба руководства подготовить отчет не по всем филиалам, а по группе из нескольких и, причем за период с апреля по октябрь, может вызвать необходимость заново начать все сначала. Как правило, такие просьбы возникают в пятницу вечером. Проверено.
Для ускорения работы я предлагаю автоматизировать процесс классификации и перекладки затратных статей из одной номенклатуры в другую, и формирование итогового отчета. Плюс к этому, мы получаем расширенные возможности комбинации вариантов для анализа. Это незаменимая вещь.
Мы будем использовать такой инструмент Excel как сводные таблицы. А также сформируем классификатор затрат, который у нас будет отвечать за методологию. При изменении в ней, мы просто перенастраиваем классификатор, не затрагивая сами отчеты. Они формируются автоматически.
Основой автоматизации у нас будет выступать дополнительная таблица Excel — классификатор затрат, которая будет добавлена в наш файл отчета и находиться в скрытом состоянии.
Таким образом, наш отчетный файл будет состоять из трех основных листов:
1) База данных. Таблица, которая содержит плановые данные с разбивкой по статьям, подразделениям и периодам, и куда будут вноситься фактические данные, выгружаемые из учетной программы с той же самой разбивкой.
Плановые данные вносятся сразу на весь год, а фактические данные вносятся ежемесячно по мере закрытия периода.
2) Классификатор. Таблица при помощи которой будет проводиться автоматическая реклассификация статей бюджета и формироваться дополнительные признаки в таблице база данных, такие как группа статей затрат, подгруппа и т.д.
3) Отчет. Собственно лист, на котором будет находиться сводная таблица-отчет, настроенная под наши аналитические нужды и которая будет требовать лишь только обновления после внесения фактических данных на лист с таблицей базы данных.
Все. Теперь наши манипуляции для построения отчета будут сводиться лишь к следующим действиям:
1) Выгрузка данных из учетной программы;
2) Копирование и вставка данных в базу данных отчета;
3) Обновление сводной таблицы и просмотр готового отчета.
Это существенно быстрее и производительнее чем предыдущий вариант.
Основой является правильное построение таблицы базы данных и классификатора. Для каждого признака, такого как период затрат, филиал, статья, группа и подгруппа доходов и расходов должен отводиться один столбец. Один признак – один столбец.
Классификатор будет иметь следующие столбцы:
1) Наименование статьи – наименование статьи, такое же, как в учетной программе;
2) Статья бюджета – наименование статьи, такое же как в бюджете (бизнес-плане);
Статья бюджета при необходимости может объединять несколько статей учетной программы. Например, статья бюджета «Аренда» может включать в себя такие статьи из учетной программы как «Аренда принимаемая в НУ» и «Аренда не принимаемая в НУ».
Каждому наименованию статьи из учетной программы соответствует определенная статья бюджета.
3) Подгруппа – подгруппа статей затрат бюджета.
По сути дела, это уже методология классификации затрат, их группировка и систематизация.
Подгрупп может быть несколько. Соответственно будет несколько уровней группировки статей бюджета.
Каждой статье бюджета должна соответствовать определенная подгруппа затрат если она есть.
В случае изменения в методике группировки затрат, изменяя соответствие определенной статьи бюджета определенной подгруппе затрат в классификаторе, мы можем легко перемещать статьи из одной подгруппы (и далее группы) в другую в итоговом отчете.
4) Группа – объединение нескольких подгрупп затрат одного уровня. Все выше изложенное касательно подгрупп относиться и к группе, только группа – это верхний уровень группировки статей бюджета.
5) Доход – Расход – признак отношения статьи бюджета соответственно к доходам или расходам.
6) В принципе это все, но можно добавить вспомогательные поля (столбцы) для таких признаков как счет БУ, код, соответствие статьи определенному ЦФО, примечание и т.д.
Это уже зависит от методологии планирования и учета действующей на предприятии и на рассмотрение общего принципа автоматизации не влияет.
База данных строиться по тому же принципу что и классификатор: один признак – один стобец.
Естественно, база данных должна содержать все столбцы классификатора плюс такие стобцы – признаки как:
1) Год – год, к которому относятся затраты.
База данных может содержать данные за несколько лет, что позволит нам легко проводить сравнительный анализ за несколько периодов.
2) Период – месяц, к которому относятся затраты.
3) Подразделение – место возникновения затрат (отдел, цех, филиал, МВЗ, ЦФО и т.д.).
4) Сумма – сумма затрат относящихся к определенной статье бюджета, МВЗ, периоду.
Для плановых и фактических затрат создаются отдельные столбцы. Такая организация таблицы позволит в дальнейшем использовать вычисляемые поля в сводном отчете для расчета отклонений.
5) В принципе этого достаточно, однако вы можете добавить еще дополнительные. Например, объем производства в натуральном выражении. Это позволит автоматически пересчитывать плановые затраты на фактический выпуск.
Ну и конечно же, как было сказано выше, база данных должна содержать столбцы классификатора затрат, для того, чтобы эту классификацию собственно и провести.
Такие признаки как «Статья бюджета», «Подгруппа», «Группа», «Доход-Расход» будут подтягиваться в нашу базу данных из классификатора при помощи функции ВПР по наименованию статьи (такому же как в учетной программе).
Вот в принципе и вся автоматическая классификация и группировка статей бюджета.
Далее мы строим на основе базы данных сводную таблицу – отчет и настраиваем его под наши нужды.
Факторный анализ продаж Решение производственной задачи в Excel
Comments are currently closed.