Немного отвлечемся от новостей. Сегодня обратимся к теме визуализации данных. Качественная инфографика в финансовых моделях не просто иллюстрирует цифры, а ускоряет анализ и помогает донести ключевые выводы до руководства или инвесторов.
Начнем с одного из наиболее эффективных инструментов в арсенале финансового аналитика — каскадной диаграммы, также известной как водопадная диаграмма (Waterfall Chart). Именно она лучше всего показывает, как итоговый результат, будь то прибыль или денежный поток, формируется под влиянием последовательного ряда факторов. Это делает ее незаменимой для декомпозиции финансовых результатов, анализа отклонений и представления структуры отчетов.
Сегодня рассмотрим применение каскадных диаграмм для иллюстрации Отчета о прибылях и убытках (P&L) и подробно разберем методологию построения такой диаграммы в версиях Microsoft Excel до 2016 года, где отсутствовал встроенный функционал построения каскадных диаграмм.
Применение каскадной диаграммы для Отчета о прибылях и убытках (P&L)
Отчет о прибылях и убытках по своей структуре идеально ложится на логику каскадной диаграммы. Он представляет собой последовательный расчет финансового результата, где от первоначальной выручки отнимаются затраты и налоги для получения итоговой чистой прибыли.
Для начала вспомним основные компоненты структуры P&L, которые будем анализировать:
- Выручка (Revenue): Общий доход от продажи товаров или услуг (начальная точка).
- Себестоимость продаж (Cost of Goods Sold, COGS): Прямые затраты на производство или закупку проданных товаров.
- Валовая прибыль (Gross Profit): Разница между выручкой и себестоимостью продаж, демонстрирующая эффективность производства (промежуточный итог).
- Операционные расходы (Operating Expenses): Затраты на управление и администрирование бизнеса (включая зарплаты, маркетинг и другие операционные издержки).
- Операционная прибыль (Operating Income): Прибыль от основной операционной деятельности компании до учета процентов и налогов.
- Прочие доходы и расходы: Включают процентные доходы и расходы, валютную переоценку и другие непрямые статьи.
- Прибыль до налогообложения (Profit Before Tax): Прибыль после вычета операционных расходов и прочих доходов/расходов.
- Налог на прибыль (Profit Tax): Государственные налоги, вычитаемые из прибыли до налогообложения.
- Чистая прибыль (Net Income): Итоговая прибыль компании, которая остается после вычета всех расходов и налогов.
Метод №1: Гистограмма с «Невидимой Базой» (Подходит для положительного итога)
До появления встроенного типа каскадной диаграммы в Excel 2016, ее создание было нетривиальной задачей, требующей подготовки данных и использования «обходных» методов. Наиболее распространенным методом является использование гистограммы с накоплением и «невидимых» столбцов-подпорок.
Шаг 1: Подготовка таблицы с данными
Ключ к успеху — правильная структура исходных данных. Нам понадобятся вспомогательные столбцы для корректного отображения всех элементов диаграммы. Преобразуем исходную таблицу Отчета о прибылях и убытках, добавив три вспомогательных столбца: «База» (невидимая основа), «Минус», и «Плюс».
Шаг 2: Расчет значений во вспомогательных столбцах
Значения в этих столбцах рассчитываются по следующим правилам:
- «Плюс»: В этот столбец переносятся значения для итоговых и начальных показателей (Выручка, Валовая прибыль, Операционная прибыль, Прибыль до налогообложения, Чистая прибыль), а также положительных изменения, не являющихся итогами (в нашем случае это «прочие доходы») — ЕСЛИ(Значение > 0; Сумма; 0).
- «Минус»: Для отрицательных изменений (значение берется по модулю) — ЕСЛИ(Значение < 0; -Значение; 0).
- «База»: Это самый сложный столбец. Он рассчитывает «невидимую» подпорку. Для строки n формула будет: База(n-1) + Плюс(n-1) — Минус(n). Проще говоря, это накопленный итог после применения этой строки. Например, для «Себестоимости» базой будет Плюс(«Выручка») — Минус(«Себестоимость») = 350 — 280 = 70. Для промежуточных итогов столбец «База» не заполняется.
Шаг 3: Создание гистограммы с накоплением
Выделите диапазон, включающий названия категорий и три вспомогательных столбца («База», «Минус», «Плюс»).
Перейдите на вкладку «Вставка» -> «Гистограмма» и выберите тип «Гистограмма с накоплением».
Вы получите основу для будущей диаграммы.
Шаг 4: Форматирование диаграммы
Теперь необходимо преобразовать гистограмму в каскадную диаграмму.
Сделать «Базу» невидимой: Щелкните правой кнопкой мыши по ряду данных «База» (нижние сегменты столбцов доходов/расходов, в моем случае они получились с синей заливкой) и выберите «Формат ряда данных». В параметрах заливки установите «Нет заливки» и «Нет линий» для границы. Таким образом наши «подпорки» станут невидимыми и диаграмма станет похожа на каскад.
Настроить цвета и добавить значения: Поочередно выберите ряды «Плюс» и «Минус» и задайте им соответствующие цвета (например, зеленый для доходов и красный для расходов). Ряд с выручкой и промежуточными итогами обычно окрашивают в нейтральный цвет (серый или синий). Добавим подписи данных к столбцам и удалим лишние нули.
Очистить от «визуального мусора»: Для улучшения читаемости удалите лишние элементы: легенду (информация уже понятна из цветов), линии сетки, а иногда и вертикальную ось, если подписи данных используются для всех ключевых точек. Уменьшите зазор между столбцами, чтобы диаграмма выглядела более цельной, например сделайте его 20%. Должно получиться примерно вот так:
Метод №2: График с «Полосами повышения/понижения» (Универсальный)
К сожалению, этот способ корректно работает только до тех пор, пока накопленный итог остается положительным. Как только происходит переход через ноль (например, операционные расходы превышают валовую прибыль), логика расчета «Базы» нарушается, и визуальная связь между столбцами теряется.
Попробуем «сломать» диаграмму и сделать прочие расходы, например, 91,чтобы прибыль до налогообложения стала отрицательной. И видим, что на диаграмме получилась ерунда. Когда Excel видит в одном столбце и положительные, и отрицательные значения для столбца (в нашем случае невидимая «База» стала отрицательной и оказалась снизу оси Х, а «Снижение» осталось положительным) и ступеньки нашего каскада разъехались:
Чтобы исправить это, необходимо сделать более универсальной модель подготовки данных. Новый метод корректно обрабатывать переходы через ноль в любую сторону. Он использует не гистограмму, а график с так называемыми «полосами повышения/понижения» и гарантированно работает при переходе через ноль.
Шаг 1: Новая, более простая структура данных
Для этого метода нам нужна иная логика подготовки таблицы. Мы создаем столбцы, которые будут служить «начальной» и «конечной» точкой для каждого «плавающего» элемента.
Логика заполнения:
- «Итог»: Сюда заносим значения только для итоговых столбцов.
- «Начало»: Это значение, от которого начинается изменение. Для «Себестоимости» это «Выручка» (350). Для «Опер. расходов» это «Валовая прибыль» (70).
- «Конец»: Это значение, которым заканчивается изменение, т.е. Начало + Значение. Для «Себестоимости» это «Валовая прибыль» (70). Для «Опер. расходов» это «Операционная прибыль» (54,5).
Как видите, подготовка данных стала даже проще и логичнее.
Шаг 2: Создание комбинированного графика
- Выделите диапазон, включающий названия категорий и три вспомогательных столбца («Начало», «Конец», «Итог») вместе с заголовками.
- Перейдите на вкладку «Вставка». Вместо гистограммы выберите «График» -> «График с маркерами».
- У вас появятся три графика («Начало», «Конец», «Итог»). Нам нужно изменить тип диаграммы для столбца «Итог».
- Щелкните правой кнопкой мыши по диаграмме -> «Изменить тип диаграммы».
- Внизу окна выберите «Комбинированная».
- Для рядов «Начало» и «Конец» оставьте тип «График с маркерами».
- Для ряда «Итог» измените тип на «Гистограмма с накоплением». Нажмите ОК.
Должно получиться примерно такое безобразие:
Шаг 3: Превращение графика в каскадную диаграмму
Теперь самое главное — магия форматирования.
Добавление коридоров колебаний:
- Выделите на диаграмме один из рядов (например, «Начало»).
- Перейдите в меню «Конструктор» -> «Добавить элемент диаграммы» -> «Полосы повышения и понижения».
- На диаграмме между двумя линиями появятся столбцы.
Скрытие линий:
- Щелкните правой кнопкой мыши по линии «Начало» -> «Формат ряда данных». В параметрах линии выберите «Нет линии». В том же меню в параметрах маркера выберите «Нет маркера»
- Повторите то же самое для линии «Конец».
Настройка цветов:
- Щелкните по одной из появившихся полос (столбцов) правой кнопкой мыши. Откроется меню «Формат полосы понижения».
- Здесь вы можете задать заливку для понижающих (красный) и повышающих (зеленый) полос.
- Также отформатируйте цвет для столбцов «Итог» (обычно серый или синий).
Финальная настройка:
- Уменьшите боковой зазор у рядов, чтобы столбцы стали шире и диаграмма выглядела как классический Waterfall. Чтобы войти в меню форматирования у полос понижения/повышения, надо щелкать не по столбцу, а по точке на его границе, где собственно находится значение.
- Удалите лишние элементы (легенду, линии сетки). Ось X можно сделать потолще
Шаг 4: Добавляем подписи данных
Добавить подписи со значениями изменений на такой диаграмме — это задача, требующая небольшого «хака», так как сами полосы повышения/понижения не являются рядом данных и не поддерживают подписи напрямую.
Процесс делится на две части: добавление подписей к итоговым столбцам (простая часть) и к «плавающим» столбцам (основная задача).
Часть 1: Подписи для итоговых столбцов
Это делается стандартным способом:
- Щелкните правой кнопкой мыши по одному из итоговых столбцов (синих) на диаграмме.
- В появившемся меню выберите «Добавить подписи данных».
- У итоговых столбцов появятся их значения (350, 70, 54,5 и т.д.). Расположите их как вам нравится.
Часть 2: Подписи для «плавающих» столбцов (значения изменений)
Здесь, как и при форматировании зазоров, понадобится точный глаз и твердая рука.
- Выбрать «Добавить подписи данных».
- В подписях появились значения из столбца «Начало», но это не то, что нам нужно.
- Щелкнем по появившимся подписям снова правой кнопкой мыши, выберем «Формат подписей данных». В поле «Параметры подписи» (в самой крайней справа вкладке, где нарисованы 3 столбика), в поле «Включить в подписи» нажмем поле с флажком «Значения из ячеек».
- В появившемся окне выберем диапазон из столбца таблицы со значениями. Все прочие галки отожмем
- Там же выберем положение подписи, например, «Снизу»
Подписи данных станут корректными. При желании их можно подвигать вверх/вниз.
Ну и файл с примером можно скачать тут.
Впервые статья была опубликована на моем сайте "Финансовые модели для оценки проектов"