text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

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

Чтобы подготовить преднастроенные Excel-отчеты о расходах группы компаний в разных аналитических признаках, действуйте по плану:

 

Рисунок 1. Преднастроенные выборки из бюджета расходов

 

Скачать Excel-файл

 

Какие данные понадобятся для автоматического 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), где:

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

Школа

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

Записаться

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

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

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

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

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