text
Читать свежий номер бесплатно >>>
Финансовый директор

Как с помощью Excel анализировать расходы группы компаний

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

Для этого потребуется:

Какие данные понадобятся для настройки автоматической выборки затрат в Excel

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

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

Следует избегать пустых строк в ячейках массива. Число полей в таблице ограничивается числом столбцов в MS Excel. При этом каждому полю должно быть присвоено свое имя, в дальнейшем это облегчит работу с таблицами и придаст наглядность формулам суммирования с условием. Например, чтобы присвоить имя «Январь» полю с плановыми значениями за январь, нужно выделить соответствующий диапазон без заголовка (E$4:$E$29), а затем, кликнув левой клавишей мыши, в контекстном меню – пункт «Имя диапазона». Далее в верхней строке «Имя» указать «Январь», нажать Enter (см. рисунок 2).

По имеющимся исходным данным (см. рисунок 2) должны получиться три таблицы с выборками по бюджетным статьям на одном листе книги (см. рисунок 1).

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

Как задать условия для автоматической выборки расходов по нескольким компаниям в Excel

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

Например, отчеты трех видов (см. рисунок 1):

Для настройки автоматической выборки данных по наименованию компании, подразделению, статье затрат (по аналитическим признакам) пригодится справочник (см. рисунок 3). Его данными заполняют созданные формы отчетов. Например, чтобы вставить значения из списков подразделений справочника в ячейку M4 таблицы № 1 (см. рисунок 4), предстоит выделить ячейку, перейти во вкладку «Данные» – «Проверка данных». В условиях проверки тип данных обозначить как «Список», а источником данных указать именованный диапазон справочника – список подразделений (см. рисунок 4).

После переноса всех данных справочника в три таблицы (см. рисунок 1) можно приступить к заключительному этапу настройки, в рамках которого предстоит задать формулу с использованием функции СУММЕСЛИМН, которая позволит сделать выборку расходов по компаниям автоматической (подробнее об этом см. Как задать формулу для автоматической выборки данных о расходах по периодам в Excel). Эту формулу отражают в оставшихся незаполненных столбцах, именованных по месяцам ( рисунок 5).

Как задать формулу для автоматической выборки данных о расходах по периодам в Excel

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

Например, при заполнении первой ячейки таблицы № 1 (см. рисунок 5) нужно указать:
СУММЕСЛИМН(Январь;Департамент;$M4;Предприятие;$L$4), где:

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

Школа

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

Записаться

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

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

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

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