Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных

Этот пример построен на финансовых данных о том, как сформировался размер чистой прибыли. Мы видим, что из валовой прибыли вычли управленческие расходы, добавили проценты к получению и вычли проценты к уплате. Потом добавили 2,8 млн прочих доходов и получили пик денежного потока: 4,1 млн из него списали в прочие расходы, а уже с оставшейся небольшой базы уплатили 340000 налога. После этого остался результирующий серый столбец в 2 млн чистой прибыли.

Чтобы получить такую визуализацию, мы также комбинируем два базовых вида анализа:

● динамику, чтобы по этапам отобразить изменения показателя от начальной точки до конечной;

● рейтинг, чтобы количественно сравнить факторы, повлиявшие на результат положительно или отрицательно.

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

Цветовая кодировка может быть инвертирована в случае анализа расходов. Здесь мы видим, что плановые расходы на оплату труда были 55 млн, а по факту составили 70,5 млн. Основная экономия вышла за счет сдвига сроков, вывода объектов и немного за счет сокращения административно-управленческого персонала. А перерасход вышел из-за пересмотра ставок, текучести и премирования. На диаграмму мы вывели самые значимые факторы, но были еще и мелкие, которые в сумме сэкономили 5 млн рублей.

Расскажу, как построить диаграмму в более новых версиях программы (спойлер – легко), а также как использовать лайфхак с «подушкой» в версиях до 2016 года.

Как построить каскадную диаграмму в Excel 2016 и выше

В Excel эта диаграмма появилась с 2016 года, но используют ее нечасто – во многом из-за логики построения: она не интуитивно понятна, и ее не отформатировать по образцу привычных нам столбцов и графиков.

Разберем на примере изменение прибыли. Исходные данные нужно записать в таблицу так, чтобы расходы шли со знаком минус. Именно по этому критерию Excel будет определять направление столбца: положительные значения – вверх, отрицательные – вниз.

Шаг 1

На ленте находим иконку с каскадной диаграммой. Нажимаем и получаем странную фигуру. На этом шаге многие окончательно разочаровываются в новинках Excel и уходят собирать водопад из прямоугольников в PowerPoint.

Сначала еще понятно: синий столбец валовой прибыли, из него вычитаются управленческие расходы… Но вместо зеленых столбцов роста – опять синие, а в конце чистая прибыль висит в воздухе. Да и в легенде еще какой-то непонятный серый «Итог».

Шаг 2

Дело в том, что «Итог» – это и есть признак столбца, который будет строиться от нуля. Чтобы «приземлить» чистую прибыль, нужно догадаться сначала нажать на этот сегмент один раз, а потом второй, чтобы он подсветился. Не путайте с быстрым двойным кликом: раз нажал левой кнопкой, выдохнул, снова нажал. После этого правой кнопкой мыши вызываем контекстное меню и выбираем «Установить в качестве итога». Аналогично делаем для начального столбца.

Шаг 3

«Опоры моста» стали нейтрально серыми, но вот «доходы» остались желтыми, а я хочу их сделать зелеными. И тут снова начинаются неочевидные нюансы. Я, конечно, могу по очереди выделять каждый столбец и проставлять ему нужный оттенок зеленого и красного. Но в Excel предусмотрена возможность настроить цвет сразу для всей категории.

Для этого нажимаем на легенду один раз, затем еще раз – на элемент «Увеличение» и выбираем ему зеленый цвет. Такая функция стабильно работает только в версиях 2019+, в более старых это окрашивает фон легенды в зеленый.

Шаг 4

Оформляем диаграмму по чек-листу из главы 3.4: убираем лишние ось Y, линии сетки, легенду. Чтобы подписи категорий уместились по горизонтали, придется уменьшить шрифт и растянуть диаграмму по ширине. Если для текста недостаточно места, Excel будет принудительно поворачивать его и размещать под углом. Так что если у вас длинные названия факторов отклонений, то они все равно не уместятся, и с этим ничего не поделать.