Как сделать динамику продаж в excel
Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.
В данном примере будем использовать линейный тренд для составления прогноза по продажам на бушующие периоды с учетом сезонности.
Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.
Excel – это лучший в мире универсальный аналитический инструмент, который позволяет не только обрабатывать статистические данные, но и составлять прогнозы с высокой точностью. Для того чтобы оценить некоторые возможности Excel в области прогнозирования продаж, разберем практический пример.
Пример прогнозирования продаж в Excel
Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).
Уравнение линейного тренда:
- y — объемы продаж;
- x — номер периода;
- a — точка пересечения с осью y на графике (минимальный порог);
- b — увеличение последующих значений временного ряда.
Допустим у нас имеются следующие статистические данные по продажам за прошлый год.
- Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a . В ячейке D15 Используем функцию ЛИНЕЙН:
- Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).
- Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).
- Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).
- Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.
- Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.
- В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
- Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:
- Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
- На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:
Общая картина составленного прогноза выглядит следующим образом:
График прогноза продаж:
Алгоритм анализа временного ряда и прогнозирования
Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:
- Выделяем трендовую составляющую, используя функцию регрессии.
- Определяем сезонную составляющую в виде коэффициентов.
- Вычисляем прогнозные значения на определенный период.
Нужно понимать, что точный прогноз возможен только при индивидуализации модели прогнозирования. Ведь разные временные ряды имеют разные характеристики.
Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:
Как создать интерактивный отчет в Excel? Все просто: размещаем на одном листе сводные таблицы, диаграммы и элементы управления – временную шкалу и срезы.
В статье подробно написано о создании такого отчета, как на рисунке ниже.
Прежде чем начать читать, скачайте отчет и данные.
Исходные данные
Раз речь идет о сводных таблицах, значит нам потребуется таблица в формате базы данных или, как это еще называют, – организованный список. В нашем примере таблица – это обычный отчет по Валовой прибыли из 1С, созданный с помощью расширенных настроек. А вообще вы можете попросить программиста сделать вам автовыгрузку в таком формате, или создать выгрузку другими инструментами – в зависимости от конфигурации 1С.
Итак, нам нужен отчет в виде таблицы с такими столбцами:
В отчете выгружены данные о продажах за 2 года с детализацией по периодам, подразделениям, контрагентам, менеджерам и номенклатуре. Выгрузка получилась небольшой – всего 2 670 строк. В средних и крупных компаниях в таких выгрузках строк намного больше.
Проанализируем продажи, представив все показатели отчета в понятном виде и на одном листе.
Структура отчета
А теперь давайте посмотрим, из каких частей будет состоять интерактивный отчет по валовой прибыли.
Элементы управления
Чтобы управлять данными отчета, сверху разместим элементы управления – срезы и временную шкалу.
Карточки с показателями
Под элементами управления – карточки с итоговыми показателями. Показатели в карточках также будут изменяться в зависимости от выбранного периода и значений в срезах.
Графики и диаграммы
Чтобы наглядно показать динамику и структуру продаж, проиллюстрируем их с помощью графиков и диаграмм. Всего в информационной панели будет пять диаграмм.
Сводная таблица с деталями
В самой нижней части отчета находится сводная таблица. Цифры в таблице также изменяются в зависимости от выбранного периода и значений в срезах – эта таблица будет показывать детали продаж.
Итак, в интерактивном отчете четыре блока:
А теперь подробно, по шагам. Начнем создавать отчет со сводной таблицы.
Сводная таблица
Если вы раньше никогда не создавали сводные таблицы в Excel, то прочитать об этом можно здесь: “Как построить сводную таблицу в Excel”.
Откройте отчет о валовой прибыли в Excel и удалите из него сроку с итогами – мы рассчитаем их с помощью сводных таблиц. Еще лучше будет, если использовать форматированные (smart – умные) таблицы Excel. Для этого выделите мышкой любую ячейку отчета и перейдите в меню Главная -> Форматировать как таблицу -> выберите любой понравившийся вид таблицы.
Нажмите Ок. В появившемся меню Конструктор введите имя форматированной таблицы — «продажи»:
Добавьте в отчет сводную таблицу. Нажмите на любую ячейку форматированной таблицы, выберите Вставка -> Сводная таблица. Разместите сводную таблицу на новом листе, где-нибудь с 40-ой строки, т.к. эта таблица будет расположена в нижней части нашего отчета – для пояснений к графикам. Назовите лист «отчет».
В области строк сводной таблицы укажите поля Контрагент и Номенклатура, в области столбцов – даты, а продажи без НДС поместите в область значений. При этом в новых версиях Excel к датам в столбцах автоматически добавятся годы, кварталы и месяцы.
Отформатируйте сводную таблицу. Щелкните правой кнопкой мышки по любой ячейке с цифрами таблицы, в появившемся меню выберите пункты:
-
Параметры сводной таблицы -> убрать галочку «Автоматически изменять ширину столбцов при обновлении».
После всех манипуляций должна получиться такая таблица:
Сводные диаграммы и графики
Небольшое пояснение: в Excel при добавлении на лист сводной диаграммы вместе с ней автоматически добавится и сводная таблица. Это не очень удобно, ведь мы создаем отчет, где на одном листе много диаграмм и только одна таблица. Поэтому все дополнительные таблицы разместим на отдельном листе. Назовем этот лист «вспомогательный».
Кстати, из Power Pivot сводные диаграммы можно добавить без сводных таблиц.
Всего в отчете будет пять графиков и диаграмм:
1. График с динамикой продаж по периодам
Выберите таблицу «продажи» и перейдите в меню Вставка -> Сводная диаграмма -> разместите таблицу на листе «вспомогательный». В область строк перенесите даты, а в область значений – показатели продаж без НДС и валовой прибыли.
Измените тип диаграммы: щелкните по графику мышкой и перейдите на вкладку Конструктор -> Изменить тип диаграммы -> выберите График.
- Щелкните правой кнопкой мышки по серым кнопкам на графике, в появившемся меню выберите «Скрыть все кнопки поля значения на диаграмме».
- Удалите легенду.
- Добавьте заголовок: Конструктор -> Добавить элемент диаграммы -> Название диаграммы -> Над диаграммой. Назовите диаграмму «Динамика продаж».
После выполнения этих шагов должен получиться такой график:
Выберем для отчета другие цвета вместо ярких синего и оранжевого. Перейдите на вкладку Разметка страницы -> Цвета -> выберите понравившиеся цвета (нам понравились пастельные цвета в настройке «Бумажная»). Здесь же в меню можно настроить корпоративные цвета компании.
График готов, перенесите его на лист «отчет».
Кстати, чтобы подписи в оси Y были с разделителями разрядов, нужно задать числовой формат в самой сводной таблице.
2. Гистограмма продаж по покупателям
Создаем следующий график. Вставьте сводную диаграмму: выделите отчет с данными из 1С (или любую его ячейку), перейдите в меню Вставка -> Сводная диаграмма. На самом деле, способов добавить сводную диаграмму несколько. Мы с вами рассматриваем самый очевидный – аналогичный добавлению простой (не сводной) диаграммы.
Поместите сводную таблицу на лист «вспомогательный», справа от уже добавленной таблицы. Здесь обязательно размещать сводные таблицы рядом, а не друг под другом – чтобы таблицы не «перекрыли» друг друга. В область строк помещаем данные о контрагентах, а в область значений – продажи без НДС и валовую прибыль.
Отформатируйте цифры в таблице — задайте числовой формат (выделить ячейки правой кнопкой мышки -> Числовой формат). Должна получиться такая диаграмма:
- Щелкните правой кнопкой мышки по любой серой кнопке на диаграмме, выберите в появившемся меню «Скрыть все кнопки полей на диаграмме».
- Удалите легенду.
- Чтобы сделать диаграмму нагляднее, представим в ней столбцы в порядке убывания. Отсортируйте сводную таблицу по полю Продажи без НДС: выделите ячейку с столбце «Сумма по полю Продажи без НДС», перейдите в меню Данные -> Сортировка по убыванию. Теперь диаграмма выглядит так:
- Покажем соотношение валовой прибыли и выручки: выделите правой кнопкой мышки столбец диаграммы с валовой прибылью. В появившемся меню выберите «Формат ряда данных»:
Задайте параметры ряда – перекрытие столбцов 100%, боковой зазор 40%.
Итак, все продажи на диаграмме отсортированы в порядке убывания, показано соотношение валовой прибыли и выручки.
Назовите диаграмму «Продажи по покупателям» и перенесите ее на лист «отчет».
Подготовили подборку разработанных нами когда-то интерактивных управленческих отчетов в Excel для экономистов и финансистов. Эти отчеты (дашборды) можно скачать, заполнять данные и вносить изменения, а еще разобраться с тем, как они построены и «прокачать» свои навыки в Excel (но не обещаем, что это будет легко ))
Скачивайте файлы — это бесплатно. А если вы хотите научиться строить такие отчеты самостоятельно, приходите к нам на курсы .
Анализ бюджета доходов и расходов (БДР) по месяцам
Отчет показывает динамику изменения основных показателей БДР по месяцам: чистая прибыль, валовая прибыль, расходы и выручка, а также позволяет сравнить план и факт.
Переключение рисунков с помощью срезов и сводных таблиц
С помощью сводных таблиц, срезов и связанных рисунков, сделали для вас отчет, в котором можно переключать изображения и данные в диаграммах.
Если вы хотите научиться консолидировать бюджеты с использованием Power Query и выполнять сложные расчеты на основе больших массивов данных с помощью DAX-формул, приходите к нам на курсы « Бюджетирование с Business Intelligence »
Dashboard в Excel с кроссфильтрацией
Подготовил для вас Dashboard с надстройкой Power Pivot. Он замаскирован под Power BI с кроссфильтрацией, подсветкой элементов, переключением показателей и другими полезными штуками. Подробнее >>
Диаграммы с переключением для БДР
Предлагаю вам с одной стороны компактный, а с другой – универсальный вариант визуализации данных БДР (бюджета доходов и расходов).
Анализ ключевых показателей и EVA
В отчете проиллюстрированы изменение ключевых показателей, ликвидности, рентабельности собственного капитала и факторы, влияющие на изменение EVA.
Панель мониторинга 13 месяцев в году
Как показывать данные, чтобы было понятно, запоминалось и чтобы был план факт, прошлый месяц и тот же месяц прошлого года? Для этого нужно построить диаграмму за 13 месяцев.
Анализ бюджета доходов и расходов (БДР) за период
Отчет поможет проанализировать бюджет доходов и расходов за период, сравнить план и факт, провести факторный анализ прибыли и сравнить продажи с безубыточной выручкой.
Программа Excel предлагает маркетологам гораздо больше возможностей, чем просто создание таблиц, форматирование графиков или использование основных арифметических формул для расчётов.
Зачастую маркетологи работают в условиях многозадачности и незаслуженно забывают о простых инструментах Excel, способных значительно оптимизировать рабочий процесс.
В этом обзоре расскажем о шаблонах таблиц, горячих клавишах и других не всегда очевидных, но тем не менее полезных функциях Excel, которые помогают экономить время специалиста.
Анна Винник
Полезные сочетания клавиш
Горячие клавиши — это определённые комбинации кнопок на клавиатуре, нажатие которых запускает какую-либо функцию программы. Excel не является исключением и имеет собственный набор горячих клавиш.
Общие действия
- Выделение таблицы: Ctrl (cmd) + A.
- Сохранить: Ctrl + S.
- Сохранить как: Alt + F2 или F12.
- Копировать: Ctrl (cmd) + C.
- Вставить: Ctrl (cmd) + V.
- Вставить новый лист: Alt + Shift + F1.
- Повторить последнюю команду: F4.
Работа с таблицами
- Перемещение к краю таблицы: Ctrl (cmd) + (стрелки).
- Перемещение к краю таблицы с выделением: Ctrl + Shift + (стрелки).
- Перемещение выделенного диапазона: удерживать Ctrl для копирования.
- Смещение диапазона: перетаскивая, удерживать Shift.
- Вставка диапазона со смещением: удерживать Ctrl + Shift.
- Вставить гиперссылку: Ctrl (Cmd) + K.
Набор текста
- Для перехода к соседней ячейке справа: Tab.
- Для перехода к соседней ячейке слева: Shift + Tab.
- Для перехода на следующую ячейку: Enter.
- Для перехода к предыдущей ячейке: Shift + Enter.
- Правка содержимого активной ячейки: F2.
Шаблоны Excel-таблиц
Помимо горячих клавиш, в Excel есть ряд уже готовых к применению шаблонов для создания планов и бюджетов мероприятий, ведения списков участников и учёта ежедневных заданий.
На основе базовых Excel-шаблонов несколько специализированных компаний также разрабатывают варианты для диджитал-маркетологов, которые учитывают цели и особенности именно этой области маркетинга.
Встроенные Excel-шаблоны для маркетологов
Маркетолог должен не только иметь общее видение стратегии и полный обзор мероприятий в рамках этой стратегии, но и регулярно отслеживать ход реализации этих процессов, получать информацию о текущих затратах и своевременно реагировать на изменение рынка. Excel предлагает множество встроенных шаблонов для этих целей.
Например, есть несколько таблиц для ведения бюджета различных маркетинговых мероприятий. Как правило, в каждую из них уже заложены основные статьи расходов и формулы расчёта, которые можно легко модифицировать под собственные нужды и проекты.
Бюджет маркетингового плана
Ольга Базалева, руководитель аналитического отдела в РБК, создатель онлайн-школы Excellent, автор книги «Мастерство визуализации данных» и автор блога, поделилась с Нетологией советами, как делать более удобные и понятные коллегам отчёты в Excel.
Сегодня не обойтись без умения работать с информацией: аналитикам, финансистам, маркетологам без этого совсем никуда, но даже в гуманитарных специальностях навык обрабатывать информацию всё больше и больше ценится. Взять хотя бы журналистику данных, которая набирает популярность.
Хотите, чтобы руководитель вас заметил и в глазах коллег вы стали классным профессионалом? Научитесь делать качественные отчёты.
ольга базалева
Создатель онлайн-школы визуализации данных Excellent
курс
Excel
Узнать больше
- Научитесь работать с любыми отчётами в Excel
- Разберётесь, как работать с формулами
- Сможете сортировать, анализировать и визуализировать данные
Встаньте на место начальника. Кому вы готовы платить больше? Специалисту, который присылает ему данные в таком виде?
1. Помните о наглядности
- Располагайте данные логично: обычно это от большего к меньшему.
- Всегда разделяйте разряды в больших числах: 10 000 000, а не 10000000.
- Убирайте лишние знаки после запятой.
- Убирайте яркую сетку, это визуальный мусор. Делайте линии светлыми, а иногда можно обойтись вовсе без сетки.
- Дизайн таблиц должен быть лаконичным и не отвлекать от сути.
- Выделяйте шапку и строки/столбцы с итогами.
- Выделяйте отрицательные значения красным, а второстепенные значения — серым. В Excel это можно сделать автоматически с помощью условного форматирования.
- Визуализируйте данные в таблицах. Можно строить гистограммы непосредственно в ячейках при помощи условного форматирования и создавать миниграфики, которые располагаются в одной ячейке (спарклайны).
Подборка курсов, бесплатных лекций и материалов по Excel
2. Используйте диаграммы
Визуально представленная информация в сравнении с обычным текстом и таблицами воспринимается быстрее и запоминается легче. Графики и диаграммы позволяют сразу увидеть тренды, взаимосвязи, всплески и падения.
3. Пишите выводы в сопроводительном письме
Если руководитель получил ответ на свой вопрос сразу из письма, ему даже не надо тратить время на открытие приложенного файла. Поверьте, это дорогого стоит.
Читать также
4. Делайте отчёты удобными
Если ваш отчёт большой и занимает 10−20 листов в Excel, сделайте навигацию по нему. Это можно реализовать одним из следующих способов:
- создать лист Summary, показать на нём главные цифры и сделать ссылки «Подробнее» с переходом на листы, содержащие все данные;
- сделать оглавление, куда добавить ссылки на каждый лист с аналитикой.
Читать также
5. Автоматизируйте обновление регулярных отчётов
Когда ваши отчёты станут обновляться в считаные минуты, высвободится масса дополнительного времени. Это время можно использовать для своего профессионального развития, а можно взять на себя дополнительную функциональность, что позволит обоснованно попросить повышения.
В Excel есть множество инструментов, которые помогут автоматизировать отчёты:
- настройте выгрузку всех необходимых данных на один лист в Excel, назовём этот лист «Source»;
- создайте необходимые аналитические таблицы на других листах;
- формулами в эти аналитические таблицы подтяните нужные данные с листа Source;
- сделайте связку Power Point — Excel. Тогда при изменении диаграмм в Excel, в презентации Power Point они будут обновляться автоматически.
Так вы создадите отчёт один раз и сможете быстро обновлять его в последующем, просто добавляя новые данные на лист Source, а все остальные таблицы и диаграммы тут же обновятся.
Когда ваши отчёты преобразятся, они станут в разы ценнее. Заказчики данных будут быстро получать ответы на свои вопросы, а набор сухих таблиц превратится в удобные и наглядные аналитические отчёты. Специалистов, которые умеют обрабатывать информацию и помогают принимать бизнес-решения ценят гораздо больше, чем тех, кто занимается простым заполнением непонятных таблиц.
Читайте также: