Диаграмма «Водопад» как элемент факторного анализа

Хитрости Excel

«Водопад» представляет собой диаграмму, на которой отображается входящий параметр, исходящий параметр и факторы, которые повлияли на изменение входящего параметра. В Excel 2016 есть подобные диаграммы — «каскадные», но у них меньше настроек, в отличие от самостоятельного их построения. Как же их создавать — читайте в материале

ВЫПУСК №3 (МАРТ, 2018)

 

Яна Нудельман,

тренер DATA ANALYSIS SYSTEMS

Некоторые компании требуют определенных знаний по построению «Водопада» даже на этапе прохождения собеседования. Рассмотрим конкретный пример с анализом статей расходов, представленных на итоговой диаграмме (рисунок 1).

 

РИСУНОК 1

Диаграмма «Водопад» как элемент факторного анализа

 

На данной диаграмме:

1. 2016 и 2017 — это уровни затрат соответствующих годов.

2. Элементы зеленого цвета — статьи, которые повлияли на увеличение затрат.

3. Элементы красного цвета — статьи, которые повлияли на уменьшение затрат.

 

Изначальные данные выглядят следующим образом (рисунок 2):

 

РИСУНОК 2

Диаграмма «Водопад» как элемент факторного анализа

 

В колонке D — названия статей, колонки А и В — суммы по соответствующей статье в 2016 и в 2017 годах соответственно. В колонке С — разница между 2017 и 2016 годом. Обратите внимание, что разница разделена на 1000 для удобства отображения данных.

 

Первое, что нам понадобится для построения — это сумма затрат за 2016 и 2017 годы (рисунок 3).

 

РИСУНОК 3

Диаграмма «Водопад» как элемент факторного анализа

 

Очень часто в диаграммах отображаются данные, которые в действительности могу отличаться. Для построения диаграммы «Водопад» надо создать четыре ряда данных. Первый — это база или основа, на которой «висят» все остальные ряды (рисунок 4).

 

РИСУНОК 4

Диаграмма «Водопад» как элемент факторного анализа

 

Как видно из рисунка 4 — это гистограмма с накоплением. Для создания базы необходимо применить одну унифицированную формулу, но прежде следует уяснить ее суть.

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

Общая концепция следующая:

1. Для каждого элемента базы есть надстройки предыдущие и текущие.

2. База бывает предыдущая и текущая.

3. Для создания базы текущей, если надстройка предыдущая положительная (зеленая), то эта надстройка добавляется к базе предыдущей.

4. Для создания базы текущей, если надстройка предыдущая отрицательная (красная), то база остается равной предыдущему значению.

5. Для создания базы текущей, если надстройка текущая отрицательная (красная), то эта надстройка вычитается от базы текущей.

6. Для создания базы текущей, если надстройка текущая положительная (зеленая), то эта надстройка не влияет на базу текущую.

 

Рассмотрим пример (рисунок 5).

 

Читать дальше