Сравнительный анализ продаж по годам на дашборде отчета в Excel

Истина познается в сравнении. Именно поэтому в любой деятельности следует регулярно делать сравнительные анализы, чтобы понимать текущее истинное положение ситуации. Счастье для каждого человека – это ощущение прогресса, иными словами изменения к лучшему. Но чтобы увидеть и осознать факт прогресса следует выполнить сравнение положение начальной точки А с текущей точкой Б. Все это объясняет главный принцип мотивации – ничто так не мотивирует как визуализация прогресса. Таким образом можно смело утверждать, что визуализация данных не только позволяет быстро принимать правильные решения (иными словами проявлять высокую реакцию эффективных действий в текущей ситуации), но и основательно мотивировать к действию.

Подготовка данных отчета для сравнительного анализа продаж в Excel

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

Для примера как всегда по традиции смоделируем ситуацию. Из ERP-системы была экспортирована статистическая информация о продажах в стандартном формате файла Excel. В результате чего, в нашем распоряжении имеется история продаж за учетный период 2 полных год: с прошлого 2020-го года по текущий 2021-ый год. Как показано ниже на рисунке:

Сразу стоит отметить что показатели продаж по дням весьма вола тыльные. В один день 170 в другой 8 000. Как в розничной торговле – день на день непохож. Поэтому разумно в нашем интерактивном отчете сравнительного анализа продаж предварительно усреднить все показатели, чтобы избежать «статистического шума» на некоторых графиках.

На основе полученных данных нам необходимо узнать:

1. Соотношение средних показателей продаж по месяцам текущего года относительно прошлого.
2. Изменения в процентах динамики между разных месяцами для разных годов.
3. Проанализировать ежемесячные фактические продажи по неделям и дням недели.
4. Определить на какие дни недели приходится недельный максимум продаж.

Так приблизительно выглядит наше ТЗ (техническое задание). Ведь без ТЗ результат ХЗ! Исходя из него уже можно схематически смоделировать структуру дашборда для визуализации отчета сравнительного анализа в продажах. Базовая схема для данного ТЗ состоит из 4-х блоков (блоки могут быть в любой желаемой последовательности, а не обязательно по пунктам ТЗ).

Формулы для создания визуализации данных в сравнительном анализе продаж

Как видно было уже на первом рисунке, шаблон данного дашборда состоит из 3-х листов.

1. DASHBOARD – диаграммы и графики с интерактивными элементами управления отчета при сравнительном визуальном анализе статистических исходных данных.
2. Processing – техническая часть шаблона, состоящая из сложных формул выборки данных по нескольким условиям из листа «Data». И таблиц для построения графиков или диаграмм отчета на листе «DASHBOARD».

3. Data – исходные данные полученные путем экспорта из программы ERP-системы фирмы.

Далее рассмотрим главный лист отчета и принципы его управления интерактивными элементами для удобного визуального анализа данных.

Дашборд для создания отчетов по сравнительному анализу продаж в Excel

Вашему вниманию предлагается обзор принципов использования шаблона дашборда для составления визуальных отчетов по сравнительному анализу продаж в Excel.

Самый верхний первый блок состоит из 4-х блоков подписей и числовых значений. Из графических элементов здесь всего лишь треугольники роста и снижения показателей. Они находятся на против отображения изменения их динамики в процентном соотношении:

Интересный факт, явно выраженный в данной ситуации примера. Чтобы нам увеличить показатель продаж с 1 725 (апрель 2020-го) до 2 884 (апрель 2021-го) нам нужно добавить аж +67,2%. Но если наоборот, чтобы ухудшить показатели прогресса с 2 884 и до 1725 нам уже достаточно потереть лишь -40,2%. Будьте внимательны с отрицательными значениями в процентах в отчетах. Не стоит пренебрегать их небольшими значениями. Ведь чтобы увеличить число в 2 раза потребуется аж 100%, но, чтобы потерять половину достаточно всего лишь 50%. Относительные значения коварны, но без них не обойтись, нужно лишь научится правильно ими пользоваться.

Второй самый большой блок под названием «ПРОДАЖИ В СРЕДНЕМ» отображает динамику изменений усредненных показателей продаж за текущий (желтая кривая) и предыдущий (фиолетовая кривая) – года:

Чтобы визуально анализировать изменения по месяцам разных годов можно использовать интерактивный элемент «Полоса прокрутки». Сразу стоит отметить, что все интерактивные элементы в Excel доступны из выпадающего меню на вкладке «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»:

Теперь важно отметить особенность управление полосой прокрутки. Данный элемент управления управляет лишь только курсором для отображения значений месяцев текущего года. Если галочкой отмечет элемент «Флажок» напротив пиктограммы «замок», тогда второй курсор значений предыдущего года будет привязан к курсору текущего.

Далее переходим к следящим элемента управления – выпадающие списки. Благодаря им мы имеем возможность сравнивать показатели разных месяцев разных годов. НО! Сначала должен быть снят флажок на против замка, чтобы разблокировать курсоры и сделать их независимыми по отношению друг к другу. Вот как это работает:

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

Третий блок «ТЕКУЩИЙ МЕСЯЦ (факт)» отображает ежедневные продажи для выбранного текущего месяца текущего года:

На втором плане графика данного блока находится выделенная фиолетовым цветом область, которая служит курсором выделения дней выбранного номера недели (в данном случае №3).

Ети же дни недели детально отображаются на четвертом блоке под названием «НЕДЕЛЬНЫЙ МАКСИМУМ»:

Также здесь детально экспонируется день недели со самым высоким показателем продаж (в данном случае – это Четверг). Третий и четвертый блоки, связанные между собой и управляются одним и тем же элементом управления (полосой прокрутки) на 3-м блоке. Как показано ниже на рисунке:

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

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

Данный дашборд представленный в виде шаблона. Таким образом, вы имеете возможность его использовать в собственных целях для своих задач, всего лишь подставив свои исходные данные на листе «Data». Предварительно скачайте его по выше указанной ссылке. Шаблон не содержит макросов и незаблокированный паролями, а полностью доступный для редактирования формул или графиков. Свободно: скачивайте, пользуйтесь, улучшайте, анализируйте, поделитесь с друзьями.