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

«Планово-экономический отдел» №4 2019 / Руководство к действию

 

ПОСТАНОВКА ЗАДАЧИ

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

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

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

 

 

Поставленную задачу решают в четыре этапа.

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

Этап 2. Прорабатывают алгоритмы трансляции данных из источников данных. В нашем случае сделаем это с помощью надстройки Excel Power Query.

Этап 3. На основании источников данных строят сводные таблицы. В статье рассмотрим именно такой вариант решения задачи — воспользуемся инструментом сводных таблиц и сводных диаграмм Excel. Это обеспечит обновление данных на интерактивной панели автоматизированно при изменении данных в подключенных источниках. Альтернативным решением может быть использование 1С, Power BI и других платформ.

Этап 4. На основании сводных таблиц на отдельном листе Excel с помощью вставки диаграмм создают интерактивную панель.

ИСТОЧНИКИ ДАННЫХ

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

 

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

Группа

Статья расхода

Январь

Февраль

Март

Квартал

план

факт

план

факт

план

факт

план

факт

Затраты на производство

 

7262

7714

7307

8156

7733

7960

22 302

23 830

 

Натуральные кожи

1039

1410

1208

900

889

1100

3136

3410

 

Экокожа

841

840

1091

1300

1270

1000

3202

3140

 

Фурнитура

860

1000

1020

1100

1080

1080

2960

3180

 

ФОТ производственного персонала

2140

2000

1950

2200

1750

1990

5840

6190

 

Содержание и ремонт оборудования

744

647

524

970

927

800

2195

2417

 

Электроэнергия

1052

1134

1023

1148

1197

1300

3272

3582

 

Общепроизводственные расходы

586

683

491

538

620

690

1697

1911

Управленческие расходы

 

2280

2430

2417

2069

2071

2072

6768

6571

Коммерческие расходы

 

1389

1326

1178

1244

1384

1430

3951

4000

 

ФОТ коммерческого персонала

659

606

518

534

634

600

1811

1740

 

Аренда

200

200

200

200

200

230

600

630

 

Транспортные расходы

250

270

250

280

300

295

800

845

 

..

30

30

30

 

30

50

90

80

 

Прочие коммерческие расходы

100

70

100

120

100

140

300

330

Операционные расходы

 

10 931

11 470

10902

11469

11 188

11 462

33 021

34 401

Прочие расходы

 

50

120

80

35

70

74

200

229

Итого

 

10 981

11 590

10 982

11 504

11 258

11 536

33 221

34 630


Важное условие: таблица должна быть пригодной для построения сводной таблицы. Оптимально, если таблица плоская. Поэтому в табл. 1 (рис. 2) предусмотрены настройки:

  • столбец «Аналитика» позволит сформировать сводную таблицу по группе (Расходы 1 уровень) или статье расходов (Расходы 2 уровень);
  • графа «Группа» заполнена полностью. Для лаконичного представления информации лишние данные в ячейках скрыты белым шрифтом;
  • вспомогательная графа (столбец Е) содержит сцепку данных: =A2&" "&C2. Это позволит сделать сортировку данных по порядку.

 

На основе табл. 1 формируют сводные таблицы с данными, которые интересуют руководство:

  • расшифровка коммерческих расходов по статьям затрат. Сумма значительная — 4000 тыс. руб. за первый квартал;
  • выполнение плана завоза основных материалов (натуральные кожи, экокожа, фурнитура) в стоимостном выражении за март. Общая сумма фактических расходов за квартал — 9730 тыс. руб.;
  • план-факт по группам расходов укрупненно. Планировали 33 221 тыс. руб., фактические расходы составили 34 630 тыс. руб. Руководству важно показать виновников такого роста.

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

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

 

Таблица 2. Сальдо ДЗ (фрагмент)

№ заказа

Категория

Продажи с отсрочкой платежа, руб.

Оплаты, руб.

Сальдо ДЗ, руб.

Плановая дата погашения

501

Обувь

56 662

56 662

0

01.02.2019

502

Обувь

129 477

129 477

0

02.02.2019

503

Обувь

51 463

51 463

0

10.02.2019

504

Сумки

86 232

86 232

0

10.02.2019

505

Сумки

27 387

27 387

0

10.02.2019

506

Сумки

161 834

161 834

0

17.02.2019

507

Обувь

117 539

117 539

0

17.02.2019

508

Обувь

141 122

 

141 122

17.02.2019

509

Обувь

176 118

 

176 118

17.02.2019

510

Обувь

53 836

 

53 836

05.03.2019

511

Обувь

26 260

 

26 260

05.03.2019

512

Обувь

169 783

 

169 783

05.03.2019

 

 

 

 

 

 

Таблица 2 отформатирована как «умная», ей присвоено имя «ДЗ». Для решения задачи нам потребуется версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010–2013 с установленной бесплатной надстройкой Power Query от Microsoft.

Открываем вкладку Power Query (на примере Excel 2010) → Получение внешних данныхИз файлаИз Excel (рис. 3). В появившемся окне Навигатор (рис. 4) в левой части выбираем требуемый лист (в нашем случае это «ДЗ») и нажимаем кнопку «Изменить». Важная деталь: если нажать кнопку «Загрузить» (см. рис. 4), таблица сразу будет импортирована на лист в исходном виде, что не всегда удобно.

После нажатия кнопки «Изменить» в отдельном окне отобразится редактор запросов Power Query с данными из книги. Это позволит преобразовать таблицу в нужный вид. Функции Power Query позволяют:

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

Преобразуем дату погашения (см. табл. 2) из формата 01.02.2019 в месяц «Февраль» (рис. 5).

После завершения преобразований данные формируем в запрос. На вкладке Главная раскрываем пункт «Закрыть и загрузить». В появившемся окне «Загрузить в» выбираем «Только создать подключение» (рис. 6). Мы сформировали запрос, который на следующем этапе используем для создания сводных таблиц.

Рассмотрим третий источник данных — папку с файлами. Действуем аналогично. Открываем вкладку Power Query → Получение внешних данных → Из файла → Из папки (см. рис. 3). Используем папку «Дистрибьюторы». В этой папке три файла с идентичными таблицами (это важно!) по трем разным компаниям группы.

В. Власова, бизнес-консультант

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 4, 2019.

Получить статью целиком бесплатно

Подпишитесь на нашу рассылку

Рассылка о новых материалах в блоге и новых номерах журналов. Отправляется в среднем 1 письмо в 2 недели.