Дашборд для финансового анализа продаж IT-бизнеса в Excel

Дашборд для финансового анализа продаж в IT бизнесе с широким функционалом сегментирования и поиска по нескольким условиям. Особенно полезно применять для предприятий с большой номенклатурой наименований и широким ассортиментом продаваемых товаров. Чем шире ассортимент товаров, тем сложнее структура сегментирования базы данных в ERP-системах учета. Часто важно проанализировать и визуально презентовать не только общую картину в целом, но и детально по конкретному артикулу товарной единицы. Скачать готовый шаблон дашборда в конце статьи. Но сначала рассмотрим краткую инструкцию по использованию интерактивной визуализации данных в Excel с широкими возможностями сегментирования отчета.

Пример использования дашборда для финансового анализа продаж в Excel

Для наглядного примера традиционно смоделируем ситуацию с поставленной практической задачей. Допустим предприятие работает в сфере торговли программным обеспечением для IT-бизнеса, а конкретно для проведения финансовых анализов, учетов, составления бизнес-планов, маркетинговых инструментов и др. Из статистических данных ERP-системы экспортирована история продаж программного обеспечения за учетный период 6-лет (2021-2026):

Ассортимент предприятия относительно широк, поэтому его товары структурированы по категориям и брендам. Структура каталога продаваемой продукции выглядит следующим образом:

Сводная таблица с исходными данными для наглядного примера:

Алгоритм составления кода товаров с лаконичным смыслом:

1. Первые два символа – это первые символы слов в наименовании категорий (например: AB – это Accounting & Bookkeeping).
2. Третий символ в коде – это первый символ названия бренда, к которому относится данный код товара (например: A – это Avanquest или I – это Individual Software).
3. Четвертый символ в коде – это первый символ непосредственно в названии товара, то есть продаваемого программного обеспечения в сфере IT-бизнеса (например: M – это MyInvoices & Estimates Deluxe).
4. Остальные символы в коде состоят из трехзначного числа, которое обозначает порядковый номер текущего товара в данной категории от текущего бренда (например: 001, 002, 003 и т.д.).

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

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

1. По годам.
2. По месяцам.
3. По категориям.
4. По брендам.
5. По коду товаров.

При этом на панели управления визуализацией для последних трех фильтров должна предоставляется функция поиска (по категориям, брендам и кодам). Кроме того, должна быть возможность поиска по наименованию товара для аналитического отслеживания его движения за выбранный учетный период. Фактически дашборд должен уметь управлять базой данных перед выводом на презентацию.

Блоки визуализации данных должны визуально информировать о показателях:

1. Количество купленного товара за учетный период.
2. Количество проданного товара.
3. Суммы закупочных затрат.
4. Объемы продаж за выбранный период.
5. Сравнение доходов и расходов.
6. Рейтинг топ 5 бестселлеров в конкретный период, контрой категории и бренда:

• рейтинг 5-ти бестселлеров по количеству продаж;
• рейтинг топ-5 по объему продаж.

Все это умеет данный дашборд, поэтому у него такая сложная панель управления с большим количеством кнопок. Далее рассмотрим более детально его структуру.

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

Дашборд для финансового анализа продаж IT-бизнеса в Excel

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

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

1. По годам – для финансового анализа общей картины IT-бизнеса за весь период.
2. По месяцам – для детальной аналитики выбранных данных по месяцам за указанный год (или одновременно за несколько лет – по желанию пользователя).

Дальше две вертикальные гистограммы динамики закупок и реализации товаров в штуках:

Один вертикальной блок на каждой гистограмме отличается цветом. Так наглядно информируется о том, что итоговые значения на дашборде отображаются только за 2023-й год, а не за весь период (6 лет). Динамика изменений прослеживается на вспомогательном графике с метками данных изменений в процентном соотношении к предыдущему значению.

Ниже в левом углу блок с визуализацией об объемах финансовых затрат на закупку товаров:

Здесь также цветной точкой выделен выбранный 2023-й год. И также прослеживается динамика в относительных процентных значениях изменения сумм.

Блок справа – это объемы продаж (ежегодных или ежемесячных – в зависимости от переключателя на дашборде в верхнем правом углу):

Выбранный 2023-й год для итоговых значений также экспонируется другим цветом. Метки значений динамических изменений сумм продаж – присутствуют на этом графике.

В крайнем правом углу показан график (типа радар) для сравнения соотношения объемов закупок товаров и их реализации (по годам или по месяцам – в зависимости от переключателя):

И наконец последний блок с линейчатой гистограммой для отображения ТОП-5 товаров бестселлеров за указанный период:

В данном блоке имеется свой внутренний переключатель, для переключения показателей количества и объема продаж. Обратите внимание что кода товаров в рейтинге ТОП-5 по объему продаж и по количеству естественно отличается.

Кроме того, стоит отметить, что если на панели дашборда выполнить поиск по коду товара из ТОП-5, тогда он сразу подсветится другим цветом для экспонирования в этом блоке. А также это будет свидетельствовать о том, что итоговые значения отображаются исключительно по данному товару:

Скачать дашборд для финансового анализа продаж в Excel

Также важно заметить, что в самом низу панели выводится краткая сводная информация о выбранном товаре для визуализации на дашборде его движения за учетный период. Например, при нажатии на кнопку выпадающего списка возле кода на панеле управления в появившемся окне был введен код из рейтинга топ-5 «BPIB0005». Он был найден в базе Excel и подсвечен цветом на дашборде в блоке бестселлеров. А также из общей базы была выведена по данному товару сводная информация, соответствующая товару с этим кодом BPIB0005. А конкретно:

• наименование товара;
• краткое описание;
• цена.

С помощью кнопок напоминающего элемента выпадающего списка можно осуществлять поиск по категориям, по брендам, по кодам товаров (как в текущем примере), а также непосредственно по наименованию товара (с помощью кнопки на против «Product Search»). Весь функционал на дашборде реализован без использования макросов. Для создания интерактивности и автоматизации применялись исключительно стандартные инструменты Excel и формулы на листе «Processing». Исходные данные, как и на других дашбордах на этом сайте, заполняются на листе «Data». Но в этот раз еще требуется заполнить информацию об ассортименте на листе «Catalog».