Как быстро делать отчеты в Excel

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

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

1) Выгрузка данных из учетной программы, например бухгалтерских данных из программы 1 С в Excel;

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

3) Сопоставление плана с фактом и расчет отклонений в Excel.

Если у вас не настроен план-факт анализ в разрезе статей управленческого учета прямо в учетной программе (например, в 1 С), то так или иначе необходимо будет выполнить выше указанные действия.
Для наглядности приведу пример, как это порою выполняется.

В программе 1С формируется отчет о затратах, например, оборотно-сальдовые ведомости по 20, 25, 26, 44 и 91 счетам за некий период и выгружается в Excel. Далее, в ручную, копированием или при помощи ссылок данные переносятся в файл с планом с одновременной реклассификацией, как говориться «на лету».

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

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

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

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

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

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

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

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

Все. Теперь наши манипуляции для построения отчета будут сводиться лишь к следующим действиям:

2) Копирование и вставка данных в базу данных отчета;

3) Обновление сводной таблицы и просмотр готового отчета.

Это существенно быстрее и производительнее чем предыдущий вариант.

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

1) Наименование статьи – наименование статьи, такое же, как в учетной программе;

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

5) Доход – Расход – признак отношения статьи бюджета соответственно к доходам или расходам.

Естественно, база данных должна содержать все столбцы классификатора плюс такие стобцы – признаки как:

1) Год – год, к которому относятся затраты.
База данных может содержать данные за несколько лет, что позволит нам легко проводить сравнительный анализ за несколько периодов.

3) Подразделение – место возникновения затрат (отдел, цех, филиал, МВЗ, ЦФО и т.д.).

4) Сумма – сумма затрат относящихся к определенной статье бюджета, МВЗ, периоду.
Для плановых и фактических затрат создаются отдельные столбцы. Такая организация таблицы позволит в дальнейшем использовать вычисляемые поля в сводном отчете для расчета отклонений.

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

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

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

=СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];»Доход»)-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];»Расход»)+СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];»Перевод на»)-СУММЕСЛИМН(records[Сумма];records[Счет];[@Счёт];records[Тип];»Перевод с»)+[@[Начальный баланс]]

P&L Statement (Отчет о прибылях и убытках / доходах и расходах)

О том, как меняется спрос на корпоративное дистанционное обучение и о тенденциях в бизнес-образовании, рассказала Сообществу менеджеров портала Executive.ru директор Школы бизнеса «Альфа» Алла Уварова.