Закрываем отчетный год с помощью Excel

Чтобы отчитаться перед руководством по итогам работы компании за год, важно консолидировать и проанализировать фактические показатели. Уделяют внимание детализированной аналитике, заранее подготавливают объяснение причин отклонений, формулируют предложения, которые помогут улучшить результаты в следующем году.

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

ПОДГОТОВКА ДАННЫХ

В компаниях, которые имеют широкую филиальную сеть, подготовка отчетности всегда усложнена. Выход — организовать подготовку отчетов в Excel, запросить в территориальных подразделениях строго структурированную отчетность, консолидировать и проанализировать показатели. Как это реализовать, рассмотрим на примере отчета о расходах ООО «Альфа», в котором есть три филиала — «Восточный», «Западный» и «Центральный».

Во-первых, разрабатывают таблицу отчета в Excel. Главные требования:

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

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

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

Предварительный анализ данных табл. 1 показал, что на главные статьи расходов потратили 1170 тыс. руб.:

• 1013 «Мероприятия по противопожарной безопасности» — 760 тыс. руб.;
• 1010 «Текущий ремонт помещений» — 410 тыс. руб.

Причина — предписания контролирующих органов, без выполнения которых работу филиала приостановят. Высокие затраты обусловлены тем, что здание, в котором находится филиал «Восточный», 1950-х годов постройки, современными средствами сигнализации и пожаротушения не оборудовано.

Такие высокозатратные направления, как энергоснабжение (459 тыс. руб.) и теплоснабжение (380 тыс. руб.), обусловлены спецификой бизнеса компании.

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

Чтобы консолидировать фактические показатели за год, используют надстройку Excel Power Query.

НА ЗАМЕТКУ

Если надстройка Power Query не установлена, ее можно скачать для Excel 2010 и 2013 бесплатно: https://www.microsoft.com.

Открывают файл «Консолидированные итоги года» и переходят на вкладку Power Query → Получение внешних данных → Из файла → Из папки. Указывают путь к папке и выполняют команду Изменить (рис. 2).

Power Query покажет все файлы с плановыми и фактическими показателями в виде запросов (рис. 3). В окне редактора запросов разворачивают двойной стрелкой столбец Binary, выбирают лист, который нужно взять из каждого файла (лист «Показатели»), и нажимают кнопку «ОК».

В диалоговом окне «Параметры запроса» выбирают «Свойства» и в текстовом поле «Имя» присваивают имя запросу: «Итоги года Консолидированные». Запрос готов к работе с годовыми итогами. Выполняют команду: Закрыть и загрузить в → Только создать подключение.

Запрос «Итоги года Консолидированные» используют для построения сводных таблиц: вкладка Вставка → Сводная таблица. В окне «Создание сводной таблицы» выбирают «Использовать внешний источник данных» → Выбрать подключение. Выбирают запрос «Итоги года Консолидированные» → Открыть. Отчет сводной таблицы помещают на новый лист.

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

ПЛАН-ФАКТНЫЙ АНАЛИЗ

Консолидированные фактические показатели анализируют. Основная цель план-фактного анализа — выявить факторы, которые повлияли на отклонение фактических значений от плановых.

План-фактный анализ решает следующие задачи:

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

К СВЕДЕНИЮ

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

Уровни существенности отклонений устанавливают в зависимости от специфики компании. Например:

• отклонение менее 10 % — несущественное, не требует детального анализа;
• отклонение в пределах 50 % — умеренно существенное, требует детального анализа;
• отклонение более 50 % — существенное, требует немедленного обоснования и принятия мер.

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

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

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

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

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

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

ОБРАТИТЕ ВНИМАНИЕ

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

Вернемся к план-фактному анализу по итогам года в ООО «Альфа». Для корректного сопоставления важно, чтобы плановые и фактические показатели были приведены в одинаковых аналитических разрезах. Это мы обеспечили на подготовительном этапе. План-фактный анализ годовых показателей рассматриваемой компании представлен в табл. 2.

Таблица 2 сформирована в виде сводной таблицы (рис. 4), столбцы с показателями подразделений скрыты. Информация относительно графы «План ООО “Альфа”»: данные скопированы из аналогичной сводной таблицы, которая содержит план. Отклонения заданы формулами вручную.

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

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

Согласно данным табл. 2 плановый бюджет расходов по компании выполнен на 44,62 %. По всем статьям значительная экономия (от 25,45 до 51,26 %), вызванная недостаточностью финансирования запланированных расходов. Это поверка оборудования — 342 тыс. руб., техобслуживание котельных — 223 тыс. руб., техобслуживание газовых систем — 182 тыс. руб. и др. (перечисленные статьи не являются первоочередными для компании). Причина: в июле отчетного года было принято решение сократить финансирование запланированных расходов ввиду значительного сокращения поступления выручки из-за задержки в открытии филиала «Западный».

Исходя из маркетинговых оценок для компании было важно арендовать помещение именно по ул. Университетская. На поиск оптимального помещения и приведение его в надлежащее состояние потребовалось больше времени и финансовых ресурсов.

Вторая причина — падение объемов продаж и производства ввиду сокращения спроса на продукцию из-за активности конкурентов. Отсюда экономия по следующим обязательным статьям:

• 1002 «Связь и Интернет» — 597 тыс. руб.;
• 1017 «Газ» — 700,5 тыс. руб.;
• 1016 «Энергоснабжение» — 2861 тыс. руб.

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

• 1005 «Поверка счетчиков» — 157 тыс. руб.;
• 1006 «Поверка оборудования» — 342 тыс. руб.;
• 1012 «Обучение по вопросам охраны труда» — 227 тыс. руб.;
• 1013 «Мероприятия по противопожарной безопасности» — 1755 тыс. руб.

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

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

Согласно прайс-листу охранной организации стоимость услуг охраны составляет 12 тыс. руб. в месяц за одно помещение. В филиале «Восточный» стоимость услуг составила за двенадцать месяцев 144 тыс. руб. (12 тыс. руб. × 12 мес.), в филиале «Центральный» — 720 тыс. руб. (5 помещений × 12 тыс. руб. × 12 мес.).

Филиал «Западный» функционирует только с ноября, охране подлежит одно помещение, поэтому стоимость услуг за 2 месяца составила 24 тыс. руб. Фактическая стоимость услуг охраны (888 тыс. руб.) в годовом отчете обоснована и соответствует договорным ценам.

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

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

Как видно из табл. 4, удельный вес статей затрат в каждом из филиалов отличается. Так, в филиале «Западный» 53,23 % (700 тыс. руб.) приходится на текущий ремонт помещений. Эта же статья в филиале «Центральный» — 10,04 %, однако ввиду более значительных физических объемов помещений стоимость составила 1285 тыс. руб.

Информативно в данном случае сравнить один показатель по нескольким филиалам. Поскольку филиал «Западный» новый, отработал только два месяца в году, проанализируем показатели по филиалам «Восточный» и «Центральный», которые выполняют идентичные виды деятельности. Так, банковские услуги примерно на одном уровне — 4,46 и 5,74 %.

Филиалы должны объяснить разный уровень потребления электроэнергии — 14,93 % от общего уровня расходов в филиале «Восточный» и 17,85 % в филиале «Центральный». Такого рода отклонения руководитель или собственник точно заметит. Конечно, разница в процентном соотношении могла сформироваться из-за разного объема финансирования по другим статья расходов. Если же все статьи расходов финансируются на одинаковом уровне, это может свидетельствовать:

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