Что показывает диаграмма водопад
Водопад: разнообразим аналитику для финансиста
Все мы знаем, что графики и диаграммы воспринимаются гораздо лучше таблиц, но иногда без таблиц просто не обойтись. Например, в финансовой аналитике. Но ведь финансисты просят прямые и сводные таблицы для любых данных и любых типов задач – никакой эргономики интерфейса. Сегодня я расскажу, как разнообразить визуализацию для финансового департамента при помощи диаграммы «водопад» и дам краткое руководство по ее созданию в QlikView.
QlikView: О диаграмме «Водопад»
Каскадная диаграмма (она же – waterfall, водопад, bridge, мост) довольно часто используется в финансовой аналитике. Из-за внешнего вида (столбцов, как бы зависающих в воздухе) диаграмму еще называют “летающие кирпичи”:
Своей популярностью «водопад», во многом, обязан McKinsey&Company, которые активно использовали каскадные диаграммы в презентациях для клиентов. С помощью этого графика можно наглядно показать влияние и положительных, и отрицательных факторов на кумулятивный (общий) бизнес-результат. При визуальной простоте и сходстве с обычной столбчатой диаграммой, «водопад» одновременно показывает несколько различных групп данных:
Диаграмма «водопад» часто используется в анализе:
Диаграмма «Водопад»: пример в QlikView
Вы ожидали, что доход от продаж в 2013 году составит 123 млн, но в итоге прибыль составила всего 81 млн. В связи с этим возникает закономерный вопрос: кто виноват и что делать? Какие факторы оказали влияние на результат?
Для начала, стоит составить перечень возможных гипотез и протестировать их. В результате, часть предположений будет опровергнута, а оставшиеся будут иметь разные степени влияния. Как в примере ниже:
Таким образом, мы можем выделить три фактора, которые и стали причиной того, что наша компания недополучила прибыли на 42 миллиона:
Иногда, данных немного и для визуализации можно использовать линейную (Line Chart) или секторную диаграмму (Pie Chart):
Однако обе эти диаграммы не отражают в полной мере влияние факторов и требуют дополнительных пояснений. Именно здесь нам пригодится «водопад». С его помощью мы сможем не только показать степень влияния каждого фактора, но и наглядно отобразить разницу между заявленной целью и полученным результатом.
Диаграмма «Водопад»: инструкция по разработке в QlikView
Теперь по шагам разберем, как создать в QlikView диаграмму, описанную в примере. Пример я взяла тут.
Но для начала немного о простых правилах построения диаграммы «водопад»:
Итак, приступим к созданию «водопада» в QlikView:
Диаграмма Водопад в EXCEL
history 29 марта 2015 г.
Построим диаграмму Водопад, которая используется для визуализации динамики показателя (за одинаковые периоды), а также для демонстрации влияния различных факторов на показатель . Диаграмма Водопад также часто называют «каскадной диаграммой».
Сначала научимся создавать диаграмму Водопад только для положительных значений (проще), затем рассмотрим более общий случай (сложнее).
Диаграмма водопад. Динамика показателя (+)
Пусть дана таблица со значениями показателя в каждый период (остатки на складе на конец месяца, столбец В). Предполагается, что остатки на складе не могут быть отрицательными.
Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.
Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).
Диаграмма водопад. Динамика показателя (+/-)
Используем исходную таблицу, в которой приведено значение прибыли в конце каждого периода (столбец В). Обратите внимание, что в отличие от предыдущего случая, значения прибыли может быть отрицательным. Изменения, которые соответствуют отрицательным значениям, будем выделять на диаграмме более светлым цветом, чтобы подчеркнуть, что эти изменения привели к убыткам.
Вычислим изменение за период (столбец С). Для построения диаграммы также потребуются вспомогательные столбцы (см. файл примера Лист ВсеЗначения ).
В остальном построение диаграммы аналогично предыдущей задаче.
Чтобы вывести подписи данных использован специальный столбец, значения которого выводятся на диаграмме с помощью вспомогательной оси (скрыта). В качестве подписей данных используются не сами значения, а имена категорий (в качестве категорий (ось х) указаны изменения за период).
Анализ влияния факторов
В отличие от предыдущих 2-х задач, где анализировалось изменение значения показателя за несколько периодов, в этом разделе визуализируем влияние каждого фактора на полученное фактическое значение.
Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).
Используя подходы, указанные в этой статье выше, можно построить диаграмму Водопад для анализа влияния факторов.
Диаграмма «Водопад»: что это такое и как использовать
Как сравнить начальное и конечное значение, сделать анализ факторов, влияющих на итог, и при этом не запутаться в цифрах? Как данные из таблиц превратить в максимально понятные отчёты?
Чтобы продемонстрировать зависимость фактического результата от влияния промежуточных значений на начальное, применяют водопадную диаграмму. То есть с её помощью показывают взаимосвязь ключевых факторов. Многие крупные компании используют метод для визуального представления итогов в своих презентациях.
Waterfall или каскадная диаграмма
Водопадная или каскадная диаграмма состоит из вертикальных столбцов. Первый и последний столбцы показывают начальные и конечные значения (план/факт), а промежуточные – положительные или отрицательные изменения, то есть те самые факты. Для большей наглядности их принято окрашивать разными цветами. Промежуточные колонки обычно плавающие и делают диаграмму похожей на мост.
Прародителем водопадной диаграммы считают американского ученого-информатика Уинстона Уокера Ройса, директора Lockheed Software Technology Center в Остине (штат Техас, США), пионера в области разработки программного обеспечения. В августе 1970 года в журнале ІЕЕЕ он опубликовал статью «Управление разработкой крупных программных систем», где показал модель для разработки программного обеспечения похожую на каскадную. Лишь в 1976 году ученые Томас Белл и Томас Тэйер дали ей название Waterfall.
Использовать водопадную диаграмму не для разработки ПО, а именно для анализа и визуализации в презентациях для клиентов впервые начала консалтинговая фирма McKinsey & Company, специализирующаяся в стратегическом менеджменте.
Специальных инструментов и приложений для создания диаграмм тогда ещё не было, всё рисовалось и анализировалось вручную. Сейчас же уйма способов создать водопадную диаграмму. Как с помощью обычного Excel, так и применяя специальные плагины и надстройки.
Например, компания Apple с помощью водопадной диаграммы показывает, за счёт чего изменился объем продаж, какие продуктовые линейки повлияли на снижение выручки, а какие, наоборот, были более востребованными в конкретный период времени. В результате анализа становится понятно, над чем стоит более усердно поработать, чтобы сменить минус на плюс.
По статистике доход от продаж за второй квартал 2020 года по сравнении с тем же периодом 2019 года практически не изменился – 58,015 млрд. долларов против 58,313 млрд. соответственно. Рост составил всего лишь 0,5%.
Если взять данные конкретно по каждой продуктовой линейке, можно увидеть, что уровень продаж снизился на 6,7 % у iPhone, на 10,3 % – у iPad, на 2,9% – у Mac. Зато результат от продаж прочих устройств, таких как Apple TV, Apple Watch, Beats, iPod и фирменных аксессуаров вырос на 22,5%, а сервисов на 16,6%. За счет этого итоговая сумма в плюсе, хоть и незначительном.
Исследование влияния промежуточных показателей называют факторным анализом, для его визуализации и применяют водопадную диаграмму.
Что собой представляет факторный анализ и каковы его задачи
Основные идеи факторного анализа разработаны английским психологом Ф. Гальтоном. В настоящее время этот метод широко используется не только в психологии, но и в нейрофизиологии, социологии, политологии, экономике, статистике и других науках.
Задача факторного анализа выявить причины (факторы), которые влияют на фактические результаты промышленной, хозяйственной и финансовой деятельности.
Взаимосвязь факторов и результатов обычно представляют в виде математического расчета. Численно определяют, какие показатели и на сколько провоцируют рост, а какие – падение.
Чаще всего метод используют для анализа выполнения производственных планов, инвестиций и финансовых потоков.
Давайте рассмотрим еще один простой пример факторного анализа. Допустим, есть некое значение плановой прибыли – 140 тыс. рублей. На конец года фактический доход оказался 171 тыс. рублей.
Первый и последний столбцы – это план и факт. Промежуточные – факторы влияющие на итог. Зеленым цветом изображены показатели роста, красным – снижения.
Как видим, из-за роста курса доллара подскочили цены на комплектующие, также значительно выросли прочие расходы. Но за счет увеличения объемов выпуска продукции и открытия новых филиалов, которые принесли большую прибыль, расширения линейки товаров и роста спроса на новую продукцию – фактический доход на конец года превысил плановый.
Согласитесь, цифры гораздо проще воспринимаются визуально, в виде диаграммы.
Простые каскадные диаграммы можно построить в обычном Excel или использовать надстройку Think-cell. Для продвинутых отчётов и больших объемов данных гораздо удобнее работать с платформой Power BI.
Как построить водопадную диаграмму в Exсel
В Exсel более полусотни различных диаграмм, но самую популярную водопадную вы найдете лишь в Microsoft Office 2016 года и старше.
А как же быть со старыми версиями? Можно обойтись подручными средствами.
Каскадная диаграмма строится на основе обычной классической гистограммы. Эффект моста достигается вертикальными плюсующимися столбцами, нижний ряд которых делается прозрачным. Не совсем удобно, но по-другому никак.
В новых версиях всё гораздо проще. В функционал уже добавлена водопадная диаграмма. Для построения достаточно выделить диапазон данных в таблице, на основе которых необходимо построить график. На главной панели выбрать значок водопадной диаграммы.
На своё усмотрение изменить цвет столбцов, отредактировать, подправить легенду. И каскадная диаграмма готова.
Но, к сожалению, Microsoft все равно не смог обеспечить удобство использования новых возможностей. Чтобы установить итоговые столбцы, задать масштаб, подогнать размер нужно сделать много лишних телодвижений.
Для построения сложных сводных графиков всё-таки гораздо удобнее использовать дополнительные приложения и надстройки.
Надстройка Think-cell
Сотни крупных компаний для создания каскадных диаграмм используют Think-cell – инструмент, который позволяет без особых усилий строить двухмерные графики в PowerPoint прямо на странице с презентацией из данных Excel. Профессионально, максимально точно и в соответствии со стандартами компании. При этом делать это можно в 3-5 раз быстрее, чем в обычном Excel.
Первый пакет надстроек презентовали немецкие разработчики в 2003 году. Их клиентом стал филиал ведущей консалтинговой фирмы по стратегическим вопросам. На сегодняшний день более 800 000 клиентов пользуются платной версией Think-cell.
Think-cell имеет простую настройку параметров. Диаграммы синхронизируются с данными из Excel. Подписи и суммарные значения распознаются автоматически. Фигуры и текстовые блоки легко связываются между собой, превращаясь в сложную диаграмму.
Диаграмма Водопад в Power BI: как построить
Но как же быть с обширными массивами данных, сводными таблицами и необходимостью быстро и безболезненно формировать отчеты? Для продвинутых аналитиков и компаний, которые ценят свое время, в Microsoft создали аналитическую платформу Power BI. Комплекс программ и сервисов, которые позволяют быстро выгружать информацию из любых источников, приводить её к единой модели данных и на их основе производить необходимые расчеты, строить графики и создавать отчёты и дашборды.
Работа над проектами доступна с любого устройства и одновременно для нескольких пользователей. Все функции автоматизированы. Можно кликнуть на любой показатель в отчёте, посмотреть, как он изменится, и проанализировать информацию под другим углом.
Водопадную диаграмму в Power BI построить достаточно быстро. Данные из таблицы Excel необходимо загрузить на платформу. Далее нажать на диаграммы на панели «Визуализации» и определить некоторые значения. Добавить в область «Распределение» фактор, по которому нужно произвести распределение – и детализация готова.
У новичков могут возникать сложности при подготовке и хранении данных. На курсе мы подробно разбираем все нюансы. Я рассказываю, как уменьшить трудозатраты на подготовку, каким вариантом водопадных диаграмм лучше воспользоваться в конкретной ситуации –строить на платформе Power BI или использовать дополнительные интерфейсы Simple Waterfall и Zebra BI.
Диаграмма водопад (waterfall chart) в Excel
Диаграмма водопад используется для визуализации факторного анализа, когда необходимо показать изменение каждого фактора от начального значения до конечного.
Диаграмма водопад (также называют waterfall chart) выглядит следующим образом:
Диаграмма водопад (waterfall chart)
Построение водопадной диаграммы
Предположим, что у нас есть таблица с данными для факторного анализа:
Чтобы построить диаграмму добавим в таблицу вспомогательные элементы:
Построение вспомогательной таблицы
Расшифровка формул вспомогательной таблицы
Построение накопительной гистограммы
Меняем цвет рядов диаграммы
Выставляем боковой зазор для рядов равным 0
Добавляем подпись данных на ряды
Далее форматируем диаграмму на свое усмотрение и получаем окончательный вид (убираем легенду, подпись вертикальной оси и т.д.):
Диаграмма водопад (waterfall chart)
Особенности диаграммы водопад
В случае, если факторы диаграммы переходят через ноль (т.е. меняют положительное значение на отрицательное), то данный способ построения, к сожалению, не срабатывает.
Excel при построении накопительной гистограммы откладывает столбцы либо от 0, либо от положительно значения. Поэтому могут возникнуть ошибки следующего вида:
Создание диаграммы Водопад с помощью надстройки EXCEL Waterfall Chart Studio
history 27 февраля 2018 г.
Диаграмма Водопад (или каскадная диаграмма) является стандартным средством визуализации изменения финансовых показателей компании. В этой статье для построения каскадной диаграммы воспользуемся надстройкой Waterfall Chart Studio от компании fincontrollex.com.
До версии MS EXCEL 2016 года каскадная диаграмма или диаграмма Водопад отсутствовала среди встроенных типов диаграмм. К сожалению, не все пользователи довольны ее реализацией в MS EXCEL 2016, поэтому многие продолжают самостоятельно строить этот тип диаграмм или приобретать специализированные надстройки.
Виды Каскадных диаграмм
Каскадная диаграмма хорошо известна специалистам, которые занимаются анализом экономических показателей компании.
На рисунке ниже приведена диаграмма Водопад для анализа причин отклонения фактической выручки от плановой.
Также каскадная диаграмма может быть использована для анализа структуры показателя. Например, с помощью приведенной ниже диаграммы показана структура выручки от реализации.
В этом случае все факторы отражают вклад в разницу между валовой и чистой выручкой. Понятно, что все факторы вносят «негативный» вклад. Как правило, все факторы сортируются в порядке убывания своего вклада. Факторы, оказавшие наибольшее влияние, расположены левее относительно других факторов.
Наконец, с помощью диаграммы Водопад можно проанализировать динамику интересующего показателя.
Как видно из диаграммы выше, вклад каждого месяца в прибыль был различным в течение года, например, из-за сезонности продаж.
Сначала установим и активируем надстройку, затем научимся ее использовать в версии MS EXCEL 2010.
Надстройка Waterfall Chart Studio
На сайте также можно найти подробную справку к надстройке ( ]]> http://help.fincontrollex.com/Waterfall_Chart_Studio_2.0/ru/obshchie_svedeniya.htm ]]> ) и видеоурок, который быстро позволит составить первое впечатление о надстройке ( ]]> http://fincontrollex.com/?page=products&id=1&lang=ru ]]> ).
Запустите файл надстройки. После установки в MS EXCEL появится новая вкладка fincontrollex.com.
Будет открыта страница бесплатной активации сайта fincontrollex.com. После ввода Вашего адреса электронной почты, Вам через несколько минут будет прислан ключ активации.
Ключ активации нужно скопировать в буфер обмена (CTRL+C) и, нажав поле «Активация продукта»,
вставить ключ в соответствующее поле (CTRL+V):
Нажмите кнопку Активировать. Установка надстройки завершена.
Построение простейшей диаграммы Водопад
Все мы живем в очень динамичном мире, где все постоянно меняется и регулярно не хватает времени. Поэтому, естественно, нам хочется получить результат быстро и желательно без потерь времени на изучение нового.
В качестве первого простого примера рассмотрим таблицу влияния факторов на NPV (Net Present Value, Чистая Приведенная Стоимость ) проекта.
Для построения диаграммы выделим числовые значения второго столбца и на вкладке fincontrollex.com нажмем кнопку Создать.
Вот и все! Диаграмма Водопад построена.
Теперь посмотрим, что собой представляет только что созданная диаграмма.
На мой взгляд, это преимущество надстройки, т.к. нам доступен весь функционал MS EXCEL по настройке макета и формата диаграммы (как видно на рисунке выше, при выделении диаграммы Водопад активируются соответствующие стандартные вкладки «Работа с диаграммами»).
3. Диаграмма содержит 8 рядов данных, содержащих в качестве значений массивы констант вида = <0;680;680;705;630;630;0>(ссылки на исходную таблицу отсутствуют).
Из анализа этих рядов становится понятна трудоемкость создания диаграммы (при самостоятельном создании простейшей диаграммы Водопад потребуется как минимум 3 ряда данных).
Сделаем несколько настроек диаграммы с помощью стандартного функционала MS EXCEL.
Во-первых, избавимся от фонового рисунка. Для этого выделите диаграмму, во вкладке Макет выберите Формат выделенного. В появившемся окне, во вкладке Заливка установите Сплошная заливка, цвет заливки – белая.
При обновлении или перестроении диаграммы фоновый рисунок появляется вновь, что неудобно, но справедливо – обновите надстройку до версии Pro и рисунок исчезнет.
Во-вторых, сделаем шрифт подписей категорий (факторов) поменьше, чтобы они разместились горизонтально. Также добавим рамку по краю диаграммы.
В итоге получим вот такую диаграмму.
Если внимательно присмотреться к подписям данных, то можно заметить, что у первого и последнего столбцов диаграммы (синие столбцы) отсутствуют подписи. На самом деле подписи есть, но они также синего цвета и поэтому не видны.
Чтобы поместить подписи над синими столбцами их необходимо сначала найти. Это можно сделать 2-мя способами: либо кликайте по синему столбцу, пока не попадете на подпись (на самом деле там несколько подписей, нужно выбрать ту, которая длиннее, остальные содержат пустые значения от вспомогательных рядов), либо во вкладке Макет для ряда Totals выберите подписи данных.
Передвиньте нужную подпись выше синего столбца. В результате получите диаграмму, которую не стыдно показать руководству.
Прежде чем перейти к построению более сложной диаграммы Водопад с промежуточными итогами, произведем ее настройку, чтобы протестировать весь функционал Надстройки Waterfall Chart Studio.
Настройка диаграммы Водопад
Выделите диаграмму, чтобы произвести настройку диаграммы средствами самой Надстройки.
На вкладке fincontrollex.com нажмите кнопку Настройки. Ответив утвердительно на появившийся вопрос о Вашей уверенности насчет внесения изменений в диаграмму, будет открыто диалоговое окно настроек.
Самым значительным изменением вида диаграммы является изменение направления оси построения: с горизонтальной на вертикальную. В результате также получим диаграмму стандартного типа, а именно линейчатую с накоплением.
Этот нестандартный метод построения диаграммы Водопад позволяет разместить на диаграмме более длинные названия факторов, что часто бывает довольно удобно.
Остальные настройки не столь радикальные и относятся скорее к форматированию, нежели к макету:
Настройка свойств диаграммы может работать в 2-х различных режимах:
Наконец, скажем несколько слов о кнопке Обновить, расположенной на вкладке Надстройки.
Если вы изменили данные в исходной таблице (не важно, текст или значения), то ВСЕГДА нажимайте кнопку Обновить. Дело в том, что не смотря на то, что ряды данных не связаны с данными исходной таблицы, подписи данных – связаны. Таким образом, при изменении числовых значений в таблице, подписи на диаграмме будут также соответственно изменяться, но это не будет приводить к увеличению или уменьшению высоты столбцов диаграммы.
Теперь построим более сложную диаграмму с промежуточными итогами.
Диаграмма Водопад с промежуточными итогами
В качестве исходных данных возьмем информацию о прибыли компании по месяцам.
Как видно из таблицы, вклад в прибыль, которую компанию получит в конце года, распределен неравномерно в течение года. Например, за исключением мая и июня, деятельность компании приносила прибыль.
Промежуточные итоги в данном случае отражают полученную прибыль по итогам деятельности компании за полгода.
В правом столбце приведен относительный вклад каждого месяца в прибыль.
Примечание : Поскольку Надстройка Waterfall Chart Studio использует формат исходных значений при построении диаграммы, то стоит аккуратно относиться к форматированию данных в таблице. Так, для относительного вклада использован пользовательский числовой формат :
Для наглядности ежемесячное изменение прибыли дополнено стрелочками, показывающие направление изменения. Стрелочки можно вставить используя вкладку Вставка: в группе Символы нажмите кнопку Символ.
Теперь перейдем к построению диаграммы.
Напомним, что для построения первой диаграммы мы использовали кнопку Создать, что позволило нам быстро создать диаграмму без дополнительных настроек.
Для построения более сложной диаграммы Водопад с промежуточными итогами используем другой подход. Для этого:
Надстройка выведет окно, в котором будут заполнены все поля.
Как видно из рисунка выше, программа правильно определила ячейку, в которой содержатся промежуточные итоги, а также корректно расставила ссылки на названия категорий и процент отклонений. Изменим только ссылку на Заголовок диаграммы, укажем ячейку ‘Пример2’!A6.
В итоге получим следующую диаграмму:
На диаграмме синим цветом выделены итоговый столбец и столбец с промежуточным итогом. Значения относительных изменений указаны внутри столбца, пользовательский формат сохранен.
Построить такую диаграмму без помощи надстройки было бы достаточно трудоемко.
Прежде чем двинуться дальше, рассмотрим более подробно использованные нами подходы по созданию диаграмм.
Как мы убедились, Надстройка позволяет создавать диаграммы двумя способами:
Если Надстройка обнаружит эти столбцы и ячейки в указанных диапазонах, то она автоматически включит их в диаграмму.
Этот режим также позволяет изменить диапазон исходных данных для уже созданной диаграммы. Для этого нужно выделить диаграмму и нажать кнопку Выбрать данные. Будет выведено диалоговое окно «Вы действительно хотите изменить исходные данные?». После нажатия кнопки Да, откроется окно со ссылками на исходные данные выбранной диаграммы и пользователь сможет их изменить.
Наконец, построим еще более сложную диаграмму с промежуточными итогами.
В исходной таблице, приведенной ниже, рассмотрен пример анализа выручки компании Apple от 2-х категорий факторов: продуктового набора и месяца продаж.
Таким образом, таблица с исходными данными имеет 2 измерения: по столбцам указан вклад в прибыль в разрезе каждого продукта, а по строкам – вклад в зависимости от месяца.
Для построения каскадной диаграммы нам потребуется преобразовать таблицу к одномерному виду (расположим данные в один столбец).
После преобразования таблицы выделите столбец со значениями и нажмите кнопку Создать на вкладке fincontrollex.com.
Примечание : Для наглядности на диаграмме изменено минимальное значение итоговых показателей по вертикальной оси (с 0 до 32 000).
Подведем итоги
Мы на примерах убедились, что Надстройка Waterfall Chart Studio поддерживает различные варианты построения диаграмм типа Водопад :