Постройте график сезонности продаж по трем товарным группам вашей компании

Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.

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

Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.

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

Пример прогнозирования продаж в Excel

Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).

Уравнение линейного тренда:

y = bx + a

• y — объемы продаж;
• x — номер периода;
• a — точка пересечения с осью y на графике (минимальный порог);
• b — увеличение последующих значений временного ряда.

Допустим у нас имеются следующие статистические данные по продажам за прошлый год.

1. Рассчитаем значение линейного тренда. Определим коэффициенты уравнения y = bx + a. В ячейке D15 Используем функцию ЛИНЕЙН:



2. Выделяем ячейку с формулой D15 и соседнюю, правую, ячейку E15 так чтобы активной оставалась D15. Нажимаем кнопку F2. Затем Ctrl + Shift + Enter (чтобы ввести массив функций для обеих ячеек). Таким образом получаем сразу 2 значения коефициентов для (a) и (b).



3. Рассчитаем для каждого периода у-значение линейного тренда. Для этого в известное уравнение подставим рассчитанные коэффициенты (х – номер периода).



4. Чтобы определить коэффициенты сезонности, сначала найдем отклонение фактических данных от значений тренда («продажи за год» / «линейный тренд»).



5. Рассчитаем средние продажи за год. С помощью формулы СРЗНАЧ.



6. Определим индекс сезонности для каждого месяца (отношение продаж месяца к средней величине). Фактически нужно каждый объем продаж за месяц разделить на средний объем продаж за год.



7. В ячейке H2 найдем общий индекс сезонности через функцию: =СРЗНАЧ(G2:G13).
8. Спрогнозируем продажи, учитывая рост объема и сезонность. На 3 месяца вперед. Продлеваем номера периодов временного ряда на 3 значения в столбце I:



9. Рассчитаем значения тренда для будущих периодов: изменим в уравнении линейной функции значение х. Для этого можно просто скопировать формулу из D2 в J2, J3, J4.
10. На основе полученных данных составляем прогноз по продажам на следующие 3 месяца (следующего года) с учетом сезонности:

Общая картина составленного прогноза выглядит следующим образом:

График прогноза продаж:

График сезонности:

Алгоритм анализа временного ряда и прогнозирования

Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:

1. Выделяем трендовую составляющую, используя функцию регрессии.
2. Определяем сезонную составляющую в виде коэффициентов.
3. Вычисляем прогнозные значения на определенный период.

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

• бланк прогноза деятельности предприятия

Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:

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

Особенности расчета коэффициента сезонности:

1. Сезонность учитывает влияние внешних факторов на изменение продаж. Основным признаком показателя сезонности является то, что мы не можем оказать влияние на его изменения. Например, увеличение продаж на цветы и конфеты в праздники 8 марта или рост объемов продаж мороженного в летний период. Мы не можем повлиять с вами на жаркую погоду или наличие праздников. Следовательно, все эти факторы являются внешними и относятся к сезонным колебаниям.
2. Расчет сезонности исключает внутренние факторы изменения продаж Исключаем все колебания продаж в текущем периоде связанные с деятельностью магазина, которая не планируется в будущем. Например, проведение промо акций или отсутствием товара на полке.
3. Используются только сопоставимые продажи по магазинам – показатель LFL

Совет:

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

Факт продаж искомого периода Факт продаж равносильного периода в прошлом

Как рассчитать сезонность продаж? Формулы и таблица сезонности в Excel.

Давайте рассмотрим пример расчета. Предположим, что сейчас январь месяц и нам надо рассчитать сезонность продаж на следующий год по месяцам.

1. Исключаем все внутренние факторы, которые не планируются в будущем (акции, недопоставки и т.д.)
2. Учитываем только магазины и объекты LFL.
3. Возьмем продажи за прошлый период –  за год. Так как на дворе январь, то нам необходимо посмотреть на сколько продажи последующих месяцев отличаются от января.
4.  Делим продажи февраля прошлого периода на январь прошлого периода.  И так последующие месяцы.
5.  В результате мы получаем коэффициент, перемножив который на текущие продажи января мы получим прогноз продаж на будущий период.

Коэффициент сезонности в феврале относительно января 

Сезонностьфевраля=

Факт продажфевраля Факт продажянваря

Коэффициент сезонности товаров по месяцам в продажах по категориям

Каждая категория товаров имеет свое сезонное изменение продаж. Индекс сезонности в таблице рассчитан:

• • на основе факта продаж 456 магазинов формата Супермаркет
• • исключены все продажи промо акций
• • Учтены только сопоставимые магазины, присутствующие в течении всего периода (LFL)
• • Анализ построен на основе трех лет продаж

КатегорииБакалеяВодаДиабетическое питаниеКондитеркаМолокоМясоОвощи и фруктыПивоПтицаРыба

Индекс сезонности в сентябре относительно января больше на 23%

Наибольшие продажи по воде приходятся на май- на 36% больше продаж января

Коэффициент сезонности отражает динамику изменения продаж в продуктовых магазинах. Снижение продаж на овощи и фрукты с мая  по сентябрь связанно со снижением стоимости в летний период.

 

Продажи по дням недели

День недели играет важную роль в изменении продаж в розничной торговле. Самые низкие продажи приходятся на понедельник, а пик выручки приходится на субботу. Следовательно, выручка магазинов в субботу в 1,5 раза выше выручки понедельника.

Поделись или поставь Like, чтобы не потерять информацию:

Читайте также:

• Сокращаем товарный запас магазина.
• Анализ LFL показателей в торговле – метод сопоставимых продаж
• Доступность товара на полке 
• Как пошагово сделать заказ на поставку товара
• Инфляция и рост цен  — каким данным можно верить?
• Фронт маржа, Бэк маржа, Гросс маржа, Наценка

Если статья была для вас полезна, ставьте Лайк или поделитесь информацией со своими друзьями.

Один из основных показателей, влияющих на торговый оборот — это сезонность продаж. Его учёт, правильное использование данных и прогнозирование позволяет рационально распределять ресурсы, вовремя создавать запасы и, в конечном счёте, увеличивать прибыль. В нашей новой статье мы расскажем , что такое сезонность продаж, как её проанализировать и что делать с полученными данными.

Что такое сезонность продаж

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

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

• Прохладительные напитки. Продажи вырастают летом и падают зимой.
• Мороженое. Аналогичная ситуация. Конечно, продукт продаётся и зимой, но объёмы продаж несоизмеримы.
• Замороженные полуфабрикаты. Как ни странно на первый взгляд, это тоже летний продукт. Продажи резко возрастают с началом дачного сезона — быстрое приготовление, не надо возиться на даче и по приезде домой. Другие периоды падения спроса — посты, особенно великий. Примечание. Сезонность продаж здесь не так однозначна, как в предыдущих примерах. Некоторые небольшие предприятия, попавшие в нишу, вообще не замечают связанных с погодой изменений.
• Летняя и зимняя одежда. Обычно покупается в начале соответствующего сезона. В качестве подтверждающего тезис примера можно привести весенне-осенние распродажи. Магазины стремятся освободить склады от непроданного товара, который иначе будет лежать мёртвым грузом 3-4 месяца.
• Автомобили. Можно дискутировать о динамике продаж в автосалонах, но есть бесспорный факт: в конце года автосалоны стремятся быстрее распродать остатки. Дело не только в выполнении месячного, квартального, годового плана. После нового года машина станет прошлогодней, и это серьёзно повлияет на стоимость её .
• Курортные отели, гостиницы, дома отдыха. Сезон окончен, поток туристов иссяк.

Ещё существует связь динамики продаж с конкретными датами:

• Цветы. Продаются в течение года, в праздники больше, между ними меньше. Возможно, присутствует рост в конце недели, когда пары ходят на свидания. Есть один пик, когда продажи возрастают многократно — 8 марта. Ходит даже шутка, что Клара Цеткин и Роза Люксембург придумали Международный женский день, потому что у них был бизнес по торговле цветами.
• Носки и пена для бритья плюс разные мужские принадлежности. Резкий рост, как уже догадались, приходится на 23 февраля.
• Яйца. Пик приходится на Пасху. Разумеется, только в регионах, где преобладает христианство.

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

Как проводить анализ динамики продаж

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

Чтобы планирование было достоверным, надо обработать данные минимум одного года. Можно, конечно, взять и меньшие интервалы, например, полугодие, квартал, месяц. Ввести индикаторы объёмов продаж минимального — неделя и максимального — две недели или месяц, и, исходя из них, формировать складские запасы.

Такие критерии смогут показать небольшие колебания спроса, но с резким сезонным ростом и падением они не справятся, т. к. по определению будут отставать от событий.

Более полную картину покажут данные за год. Для полноценного же анализа потребуется информация за 3…5 и больше лет.

Почему требуется многолетний период для достоверного прогноза продаж

На спрос может повлиять множество случайных факторов. Чтобы их учесть, надо сравнить данные за аналогичные периоды разных лет. Кроме собственно реализованных объёмов, сравнивают дополнительные факторы.

Пример: продажа мороженого. Эта продукция продаётся преимущественно в жаркое время года — лето. Если оно выдалось холодным, продажи заметно проседают. Значит, для анализа собирают не только количество реализованного товара, но и информацию о погоде. Например, температуру воздуха конкретно рассматриваемого регионе. Если погода и сбыт коррелируют, учитывают при планировании оба фактора.

Если при одинаковых условиях продажи существенно различаются, надо искать дополнительные факторы — нераскрученность торговой точки, наличие конкурентов и т. д.

Примечание. Погодные данные за прошлые годы — информация точная. Прогноз же на будущее весьма приблизителен. При планировании продаж следует это учитывать и предусматривать «План Б». Если не оправдалось ожидание плохой погоды, быть готовым заказать дополнительные объёмы продукции, в том числе у альтернативных поставщиков. Если же вместо ожидаемой жары пошли дожди, возможно сократить закупки.

Расчёт коэффициентов

Для аналитических расчётов объёмы в натуральном выражении могут оказаться неудобными. Для одного товара придётся оперировать единицами, десятками и сотнями тысяч, для другого — просто штуками, для третьего — килограммами. Чтобы свести все данные к единообразию, их преобразуют в коэффициенты. При этом за единицу берут среднегодовое значение.

Пример. Объём продаж некоторого товара по месяцам составил

20, 25, 37, 39, 45, 51, 55, 57, 46, 32, 27, 23

Вычисляют среднее значение, оно равно 38,083.

Затем вместо каждого из 12 чисел подставляют коэффициент равный этому значению, делённому на среднее: 0,53, 0,66, 0,97, 1,02, 1,18, 1,34, 1,44, 1,50, 1,21, 0,84, 0,71, 0,60. Для удобства здесь и дальше оставляем только 2 знака после запятой.

Теперь сразу видно, если коэффициент больше единицы, продажи выше среднего, меньше — ниже.

Если добавить в ряд 13 значение, получатся данные, например с января одного года по январь следующего:

20, 25, 37, 39, 45, 51, 55, 57, 46, 32, 27, 23, 22.

В ряду коэффициентов соответственно будет

0,53, 0,66, 0,97, 1,02, 1,18, 1,34, 1,44, 1,50, 1,21, 0,84, 0,71, 0,60, 0,58.

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

Обычно, разовой погрешностью до 20% пренебрегают, считая её случайностью. При большей разнице приходится выяснять, почему это произошло, чтобы понять, закладывать большие или меньшие объёмы в прогноз или нет. При падении сбыта — нужно ли искать его причины.

Учёт тренда

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

Предположим, в следующие месяцы показатели были такими (выделены жирным):

20, 25, 37, 39, 45, 51, 55, 57, 46, 32, 27, 23, 22, 28, 41.

Имеем в течение трёх месяцев (квартала) увеличение продаж на 10 %.

Вносим поправки в годовой прогноз:

22, 28, 41, 43, 50,56, 61, 63, 51, 35, 30, 25.

Или в коэффициентах:

0,58, 0,72, 1,07, 1,13, 1,30, 1,47, 1,59, 1,65, 1,33 0,92, 0,78, 0,66, 0,64.

Примечание. При переводе коэффициентов в натуральные единицы, следует брать данные предыдущего года, т. к. увеличение на 10% — по отношению к нему, а не к текущему.

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

Использование медианного значения вместо среднего

Часто при расчётах и прогнозировании вместо среднего значения используют медианное.

Расположим данные продаж в течение года в порядке возрастания. Выберем среднеарифметическое средних (5 и 6) значений.

Примечание. Если количество чисел в ряду нечётное, берут срединное из них. Поскольку в рассматриваемом случае — чётное, 12, за основу берутся 5-ое и 6-ое.

20, 23, 25, 27, 32, 37, 39, 45, 46, 51, 55, 57.

(32+37)/2= 34,5.

Теперь построим ряд коэффициентов по отношению к полученному медианному значению (объёмы продаж базового года, делённые на медиану):

0,58, 0,72, 1,07, 1,13, 1,30, 1,48, 1,59, 1,65, 1,33, 0,93, 0,78, 0,67, 0,64.

Сопоставим полученные данные с коэффициентами, вычисленными по среднему:

0,53, 0,66, 0,97, 1,02, 1,18, 1,34, 1,44, 1,50, 1,21, 0,84, 0,71, 0,60.

Видим, что в 3 месяце (марте) по средним данным коэффициент меньше 1 — падение продаж ниже средней величины. По медианным коэффициент больше 1 — рост, продажи выше срединного значения.

Построение графиков сезонности продаж

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

Рекомендуется использовать следующие индикаторы:

• Скользящие средние. Усредняют значение показателей за заданный период времени. Обычно используют неделю, 2 недели, месяц, квартал. Кроме отсева случайных величин они могут показать изменение тенденций. Если средняя с меньшим периодом (например, недельная) пересекает месячную снизу вверх, идёт увеличение продаж. Если сверху вниз — падение. Построить индикатор можно на графике в Exel во вкладке «анализ данных».
• RSI – относительная сила тренда. Кроме общей тенденции показывает, насколько продажи близки к своему верхнему или нижнему пределу. Пример. График продаж имеет два пика, второй выше первого. Индикатор находится в зоне выше 70 или 80%% от максимума (перекупленность) и тоже имеет два пика, но второй меньше первого. Это называется дивергенцией — расхождением и свидетельствует о развороте тренда — смене роста падением. Аналогичная ситуация в зоне минимальных значений индикатора — перепроданности. Здесь дивергенция предсказывает смену падения ростом. Это более сложный индикатор. Узнать о его построении можно, например, здесь.

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

Влияние маркетинговых мероприятий на продажи

В холодные периоды, когда спрос на разные позиции падает, его можно частично стимулировать. Общего рецепта, скорее всего, не существует, для каждой группы товаров нужен свой подход. При этом надо учитывать возможные последствия.

Автопокрышки

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

Плюсы стимулирования динамики продаж — снижение складских запасов и повышение выручки в мёртвый сезон. Минусы — автопокрышки приобретаются раз в несколько лет, потребность в них постоянна. Значит, купивший их сейчас со скидкой не придёт за ними в сезон. В целом снизится общая выручка и рентабельность товарной группы. Исключение — скидками увести покупателя у конкурента.

Для принятия решения требуется просчитать все варианты, сопоставить плюсы и минусы мероприятия.

Цветы

Продажа цветов, как уже было сказано, имеет выраженный пик — 8 марта. В остальное время они продаются в гораздо меньших количествах. Стимулировать объёмы предложениями 5 по цене 3 не имеют коммерческого смысла, поскольку практически не влияют на выручку. Исключение — дамы, покупающие цветы для себя.

Вариант «Молодой человек, у вас нет ни жены, ни подруги. Купите цветы, раз уж вы такой счастливый!» рассматривать, скорее всего, не стоит. Это разовая акция.

Ситуация с носками и пеной для бритья ещё выразительнее. После 23 февраля продажи круто проседают. Товар куплен и подарен. Пока он не израсходуется, новых покупок можно не ждать.

Алкоголь

Покупается всегда и имеет пики в праздники. Яркий пример — шампанское к Новому Году. В остальное время стимулировать продажи можно акциями со скидками при покупке нескольких единиц товара. Плюс грамотные консультации о преимуществах того или иного сорта вин, коньяков и т. д. Главное — помнить о ст. 21 Закона о рекламе.

Курортные гостиницы

Сразу отметим, что речь идёт именно о летних, морских курортах. Здесь тоже есть ярко выраженные пики — с июня по август. В мае и сентябре, иногда в октябре номера тоже не пустуют, но не идут в сравнение с летним наплывом. Не зря цены дифференцируются в зависимости от месяца. Самые дорогие — июль и август, что вообще-то странно. В это время можно неплохо отдохнуть и в средней полосе, а к морю приехать в бархатный сезон — сентябрь.

Поздней осенью, зимой и ранней весной — затишье.

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

Подслушанный разговор владельца одной из черноморских гостиниц с потенциальным клиентом, собирающимся привезти зимой на сбор какую-то команду: «Зачем ты сразу спрашиваешь, сколько это будет стоить? Давай сядем и обсудим, сколько это ДОЛЖНО стоить, чтобы и тебе было интересно, и мне тоже».

Рекомендовать такой подход не всегда морально и законно, но и полностью исключить его тоже неправильно.

Заключение

Представленные описания приёмов анализа динамики продаж в зависимости от сезонности помогут начинающим и опытным предпринимателям научиться грамотному прогнозу на основе объективных данных.

Наиболее корректные результаты получаются при комплексном подходе — использовании и сопоставлении разных методов анализа, обработке значений нескольких лет и использовании приёмов маркетинга.
Для этих целей мы разработали сервис, который встраивается в типовые программные продукты 1С и расширяет их возможности по анализу продаж и прогнозированию спроса.

Хитрости »

23 Март 2017              155880 просмотров

---

Скачать файл, используемый в видеоуроке:

  Прогноз_продаж.xls (59,5 KiB, 34 597 скачиваний)

---

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

---

Исходные данные

Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:

Нам необходимо рассчитать прогноз продаж на будущий год: с сентября 2015 по август 2016 и отразить это на графике. Я специально беру рваный период посреди года, чтобы показать, что начало прогноза может быть с любой даты.

Чтобы дальше в статье не запутать вас столбцами и где они должны быть добавлены, сразу приведу конечную структуру:

Т.е. у нас должно быть именно в указанном порядке 7 столбцов: Период; Продажи компании, руб.; Прогноз; Оптимистичный; Пессимистичный; Коэффициент сезонности; Отклонение. И чтобы все получилось они должны идти точно в таком же порядке, как на картинке выше.

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

  Прогноз_продаж.xls (59,5 KiB, 34 597 скачиваний)

В файле два листа:

• Исходные данные — только фактические данные по продажам, без доп.столбцов, чтобы можно было самостоятельно с нуля построить модель
• Прогноз — лист с готовыми функциями и графиком прогноза

В самый низ таблицы, после последней фактической даты, я добавил даты, на которые необходимо построить прогноз(от сен.2015 до авг.2016).

---

Расчет прогноза

Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ(FORECAST). Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку

C34

записываем функцию (и распространяем на все прогнозируемые даты –

C34:C45

):

=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)

Сама функция требует указания следующих входных данных:

• х — Дата, значение для которой необходимо спрогонозировать (A34)
• Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
• Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

С одной стороны, мы уже имеем готовый прогноз, а с другой…Данная функция пока не учитывает фактор сезонности. А это в продажах в большинстве случаев немаловажный фактор. Поэтому желательно потратить еще чуточку времени и сделать так, чтобы прогноз получился еще больше приближен к реальности. Для учета фактора сезонности сначала необходимо вычислить коэффициент сезонности для каждого месяца. Для этого добавим в столбец Коэффициент сезонности следующую формулу:
=(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12
=(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года). Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!)

Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е. 24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=((56 769+68 521)/ 1 542 293)*12
=((сумма за янв.2013 + сумма за янв.2014)/ общая сумма за два года(янв.2013 – дек.2014))*12

В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т.д. Я для наглядности назначил ячейкам процентный формат(правая кнопка мыши —Формат ячеек -вкладка Число —Процентный(Format cells —Number —Percent), два знака после запятой):

Теперь добавим учет этих коэффициентов для расчета прогноза в имеющуюся функцию ПРЕДСКАЗ(ячейки C34:C45):
=ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)*ИНДЕКС($F$2:$F$13;МЕСЯЦ(A34))
=FORECAST(A34,$B$2:$B$33,$A$2:$A$33)*INDEX($F$2:$F$13,MONTH(A34))
Здесь применяется функция ИНДЕКС(INDEX), в которой первым аргументом указываем ссылку на 12 ячеек с коэффициентами сезонности($F$2:$F$13), а вторым – номер месяца, чтобы вернуть коэффициент именно для нужного месяца(для этого используем функцию МЕСЯЦ(MONTH), которая возвращает только номер месяца из указанной даты). Для сентября 2015 это будет выглядеть так:
=ПРЕДСКАЗ(A34; $B$2:$B$33; $A$2:$A$33)*ИНДЕКС({97,48%:98,99%:90,38%:94,66%:100,86%:99,02%:100,66%:110,39%:100,47%:104,82%:105,13%:97,14%}; 9)

Основную задачу выполнили – у нас есть прогноз на будущие периоды. Теперь осталось в дополнение к самому прогнозу, создать допустимые верхние и нижние границы, которые часто еще называют оптимистичный прогноз и пессимистичный(но по сути это просто возможное отклонение от прогнозных данных). Такой прогноз даст нам возможность более гибко планировать тактику на будущие периоды.
Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН(C34:C45); СЧЁТ(C34:C45))
=CONFIDENCE(0.05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ(CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

• алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
• станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН(STDEV), передавая ей для расчетов спрогнозированные данные
• размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
Оптимистичный: =$C34+$G$2
Пессимистичный: =$C34-$G$2

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

---

График

Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

Теперь выделяем все данные (A1:E45), переходим на вкладку Вставка(Insert) – группа Диаграммы(Charts) —График(Line). И получим такую картину:

Наглядно и сразу понятно что к чему и чего можно ожидать.

• Синим – фактические продажи
• Оранжевый – прогноз
• Серый – Оптимистичный прогноз
• Желтый – Пессимистичный

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

---

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

Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные(Data) -группа Прогноз(Forecast) —Лист прогноза(Forecast Sheet):

В появившемся окне раскрываем пункт Параметры(Options) и настраиваем:

• Завершение прогноза(Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
• Начало прогноза(Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
• Доверительный интервал(Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
• Сезонность(Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
• Диапазон временной шкалы(Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
• Диапазон значений(Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
• Заполнить отсутствующие точки с помощью(Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
• Объединить дубликаты с помощью(Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
• Включить статистические данные прогноза(Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).

После нажатия кнопки Создать(Create) будет создан новый лист, в котором будет создана таблица со всеми необходимыми данными и формулами и готовым графиком:

если при создании был отмечен пункт Включить статистические данные прогноза(Include Forecast Statistics), то правее таблицы основных данных будет так же создана таблица статистических данных:

Скачать файл:

  Прогноз_продаж.xls (59,5 KiB, 34 597 скачиваний)

Так же см.:
Как быстро подобрать оптимальный вариант решения
Автообновляемая сводная таблица

---

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

---

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика