Сравнительный анализ продаж по дням недели в Excel

Пример визуального сравнительного анализа продаж с распределением динамики по дням недели. Скачать дашборд можно в конце статьи по ссылке. Но сначала рассмотрим принцип действия и правила заполнения шаблона с визуализацией данных для анализа выполнения планов недельных продаж на год.

Дашборд для визуального анализа недельных продаж в Excel

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

1. План продаж в деньгах.
2. План по продажам товаров в штуках.
3. План по привлечению новых клиентов.
4. План погашения дебиторской задолженности клиентов.

Второстепенные критерии анализа эффективности менеджеров это:

1. Сравнительный анализ продаж за текущую и предыдущую неделю.
2. Сравнение показателей недельных доходов и расходов распределением по дням недели.
3. Активность и популярность самых продаваемых товаров (с распределением на 4 базовые товарные группы A, B, C и D).
4. Покрытие обслуживания базы клиентов.
5. Уровень бюджетные расходов менеджера на неделю.
6. Доля успешности в общих продажах.
7. Распределение еженедельных продаж на дневные доли:

• основные расходы;
• наценка или маржа;
• прочее расходы.

Для составления такого дашборда средней сложности необходимо подготовить и тщательно обработать исходные данные. На рабочем листе «Data» заполняется исходными значениями большая таблица с большим количеством столбцов:

Список столбцов таблицы входящих данных:

1. Date – Дата дня выполнения планов продаж.
2. Quantity, pcs. – Суммарное количество проданного товара в штуках.
3. Income – Общие доходы с реализованных товаров.
4. Costs – Основные расходы реализации.
5. A – Количество проданного товара группы A.
6. B – Продано товаров группа B в штуках.
7. C – Продано товаров группы C в шт.
8. D – Количество реализованных товаров в группе D.
9. Coating – Процент покрытия обслуживанием клиентской базы.
10. New clients – Количество привлеченных новых клиентов.
11. Receivables – Сумма погашения дебиторской задолженности.
12. Other Costs – Разные сопутствующие бюджетные расходы.
13. Manager – Порядковый номер топ-менеджера (в данном примере 1-3).

Вся таблица полностью обрабатывается и сегментируется на отдельные промежуточные таблички с помощью сложных формул выборки данных. Весь процесс обработки исходных показателей происходит на листе «Processing»:

В диапазон ячеек выделенный цветом B18:D26 следует заполнить значениями таблицу для всех установленных видов планов по каждому менеджеру. На данном листе находятся все таблицы на основе которых построенные графическое представление визуализации данных для интерактивного дашборда. Далее рассмотрим графическую часть шаблона на листе «DASBOARD» и принципы взаимодействия с ним при помощи элементов управления.

Интерактивная презентация продажи по дням недели на дашборде в Excel

Дашборд для сравнительного анализа продаж с распределением по дням недели в Excel состоит из 5-ти основных блоков.

Первый блок – это календарь с элементами управления:

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

1. Выпадающий список для переключения между менеджерами.
2. Счетчик выбора номера недели для текущего месяца. Фактически – это элемент управления курсором. С помощью курсора подсвечивается римскими цифрами номер недели в текущем выбранном месяце и арабскими числами даты выбранной недели в месяце.
3. Выпадающий список для выбора наименования месяца.
4. Счетчик для удобного переключения между месяцами текущего года.
5. Переключатель формата календаря для американского стандарта (когда отсчет недели начинается с воскресенья) и европейского стиля (первый день недели - понедельник).

При переключении переключателем стиля форматирования календаря изменяются подписи дней недели, а также перерисовывается подсветка выходных дней.

Второй блок «ПЛАНЫ ПРОДАЖ» - общие показатели выполнения планов как основные критерии анализа. Блок состоит из 4-х диаграмм с подписями выполнения каждого плана в процентах для текущего менеджера по состоянию на выбранную неделю:

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

В верхнем левом углу находится кнопка «MONTHLY PLAN ON» переключена режимов отображения процентного выполнения планов. Если она активна в таком случае на диаграммах выводятся значения выполнения месячного плана продаж по состоянию на текущую неделю. Если данный режим отображения отключен, тогда выводятся проценты выполнения недельных планов.

Как уже упоминалось выше значения месячных и недельных планов по каждому менеджеру заполняются и устанавливаются на листе «Processing».

Третий блок с графиками распределения показателей эффективности продаж менеджеров по дням недели:

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

На втором графике попарно сравниваются уровни доходов – первый столбец и общие расходы (основные + разные) – второй столбец в паре.

Третий график показывает распределение уровней трех долей в общей сумме продажи за каждый день недели:

1. Основные расходы – нижний уровень.
2. Наценка (или маржа) – средний уровень, розовый цвет.
3. Прочие расходы – верхний уровень, серый прозрачный цвет.

Справа от данного графика находится переключатель с помощью счетчика между режимами отображения значений на среднем уровне: наценка или маржа. Все значения среднего уровня имеют подписи данных. Формулы расчета наценки и маржи находятся на листе «Processing».

При изменении стиля формата календаря переключателем «US/EU» подписи дней недели оси X, а также значения на всех трех графиках обновляются соответственно и автоматически. То есть в зависимости от выбранного режима первый день выбранного номера недели начинается с воскресенья как в США или с понедельника как в Евросоюзе.

Четвертый блок предназначен для отображения показателей по трем независимыми и вспомогательным критериям:

1. COVERAGE – уровень покрытия клиентской базы при обслуживании менеджером на текущей неделе.
2. BUDGET – уровень расходов бюджетных средств, запланированных для текущего менеджера на текущую неделю.
3. SUCCESS – уровень успешности продаж выбранного, текущего менеджера по отношению к общим суммарным показателям по всем трем менеджерам.

И последний пятый блок используется для графического представления наиболее активно продаваемых групп товаров выбранным менеджером за текущую неделю:

Все вместе блоки гармонично сочетаются на одной красивой и функциональной общей картине дашборда недельных продаж:

Скачать сравнительный анализ продаж по дням недели в Excel

Шаблон с анализом полностью открыт и доступен для публичного использования. Он не содержит макросов, весь интерактивный функционал реализован с помощью формул. При желании пользователь может использовать свои исходные значения, а также изменять и настраивать шаблон под свои конкретные потребности. Например, добавить большее количество менеджеров, использовать историю данных за несколько лет или устанавливать разные планы продаж для каждого месяца/недели. Свободно пользуйтесь, развивайтесь и наслаждайтесь результатами.