Чтобы подготовить преднастроенные Excel-отчеты о расходах группы компаний в разных аналитических признаках, действуйте по плану:
- сформируйте базу исходных данных в виде таблицы, смотрите рисунок 2;
- задайте условия для выборки: наименование предприятий, подразделения, статьи затрат, периоды – и настройте структуру отчетных таблиц согласно аналитике;
- автоматизируйте расчеты.
Рисунок 1. Преднастроенные выборки из бюджета расходов
Какие данные понадобятся для автоматического Excel-отчета о затратах
Чтобы настроить автоматическую выборку данных по статьям затрат компаний группы, подготовьте таблицу с исходными данными на одном листе книги Excel, смотрите рисунок 2. Заполните таблицу унифицированными данными – в формате массива с двумя видами полей: аналитика и значения. На рисунке 2 поля аналитик – предприятие, департамент, статья, поля значений – январь, февраль и т. д. Каждое поле должно соответствовать одному столбцу, а количество строк быть одинаковым.
Избегайте пустых строк в ячейках массива. Число полей в таблице ограничивается числом столбцов в MS Excel. Каждому полю присвойте имя, в дальнейшем это облегчит работу с таблицами и придаст наглядность формулам суммирования с условием. Например, чтобы присвоить имя «Январь» полю с плановыми значениями за январь, выделите диапазон без заголовка – на рисунке 2 E$4:$E$29. Затем кликните правой клавишей мыши и выберите в контекстном меню пункт «Имя диапазона». Далее в верхней строке «Имя» укажите «Январь» и нажмите Enter.
Чтобы исключить опечатки и ошибки в названиях аналитических признаков, на этом или другом листе в книге создайте справочник списков уникальных значений по каждому аналитическому признаку, смотрите рисунок 3. Смотрите также Как автоматически выделить ошибки в отчете с помощью Excel.
Как задать условия для автоматической выборки расходов по нескольким компаниям в Excel
По унифицированным данным из базы исходных данных создавайте отчеты о плановых и фактических расходах группы компаний в необходимых аналитиках. К примеру, на рисунке 1 задействовали три представления. Первое – сводный бюджет предприятия по департаментам без аналитики по статьям. Второе – таблица контроля ключевых статей бюджета каждого предприятия без аналитики по департаментам. Третье – суммарный бюджет холдинга в разрезе предприятий по периодам с возможностью выборочного просмотра по департаментам и статьям.
Чтобы настроить автоматическую выборку данных по аналитическим признакам: наименованию компании, подразделению, статье затрат, используйте справочник, смотрите рисунок 3. В результате данными справочника заполнятся формы отчетов. Например, чтобы вставить значения из списков подразделений справочника в ячейку M4 таблицы № 1 (см. рисунок 4), выделите ячейку, перейдите во вкладку «Данные» – «Проверка данных». В условиях проверки тип данных обозначьте «Список», а источником данных укажите именованный диапазон справочника – список подразделений, смотрите рисунок 4.
После того как перенесете все данные справочника в три таблицы (см. рисунок 1), задайте формулу с использованием функции СУММЕСЛИМН, которая позволит сделать выборку расходов по компаниям автоматической. Подробнее об этом смотрите Как задать формулу для автоматической выборки данных о расходах по периодам в Excel. Эту формулу отразите в оставшихся незаполненных столбцах, именованных по месяцам, рисунок 5.
Как задать формулу для автоматической выборки данных о расходах по периодам в Excel
Формулу для автоматической выборки данных о расходах по периодам в Excel отразите в каждой таблице (на рисунке 1 в ячейках синим шрифтом выделены результаты расчетов, они отображают сумму расходов на пересечении выбранных аналитик).
Например,
чтобы заполнить первую ячейку таблицы № 1 (см.
рисунок 5), укажите:
СУММЕСЛИМН(Январь;Департамент;$M4;Предприятие;$L$4),
где:
- «Январь» – это диапазон суммирования. Это означает, что из таблицы с исходными данными складываются все расходы за январь;
- «Департамент» – диапазон первого условия выборки. Выбирает расходы за январь по всем департаментам;
- $M4 – значение первого условия. Отражает те расходы, что были только у подразделения «Продажи» в январе;
- «Предприятие» – диапазон второго условия выборки. Выбирает расходы за январь по всем департаментам продаж в трех компаниях;
- $L$4 – значение второго условия выборки. Отбирает из расходов за январь по всем департаментам продаж в трех компаниях только сумму по компании «Альфа».
Для
таблицы № 2 (см.
рисунок 1) формула
примет вид:
СУММЕСЛИМН
(Январь;Статьи;$M15;Предприятие;$L$13),
где вместо
аналитики «Департамент» фигурирует статья бюджета.
Для
таблицы № 3, в которой учтены все четыре условия, формула:
СУММЕСЛИМН(Январь;Статьи;$O$20;Предприятие;M$21;Департамент;$M$20).
Видеолекция. Посмотрите видео, в котором Игорь Николаев, финансовый директор ГК «Интернест», расскажет, как использовать инструменты Excel для управления ликвидностью и платежами.
