Печать
PDF

Как рассчитать прогноз продаж с учетом роста и сезонности в Excel?

Автор: Алексей Батурин.

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

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

Процесс расчета прогноза разделим на 3 части:

  1. Расчет значение тренда;
  2. Определение коэффициентов сезонности;
  3. Прогнозирование продаж;

Рассчитаем прогноз по месяцам на 2 года и 3 месяца на основании продаж за 5 лет (см. вложенный файл).

Для расчета значений тренда:

  1. Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0). функция Excel линейн Выделяем 2 ячейки, в левой - формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.  
  2. Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).
О различных вариантах расчета значений линейного тренда вы можете почитать в статье "5 способов расчета значений линейного тренда" и выбрать для себя максимально удобный.

Для расчета коэффициентов сезонности:

  1. Рассчитываем отклонение фактических значений от значений тренда. Для этого фактические значения делим на значения тренда;
  2. Для каждого месяца определяем среднее отклонение за последние 5 лет.
  3. Определяем общий индекс сезонности — среднее значение коэффициентов, рассчитанных в 4 пункте;
  4. Рассчитываем коэффициенты сезонности; каждый коэффициент из пункта 4 делим на коэффициент из пункта 5 (см. вложенный файл);
Более подробно о коэффициентах сезонности вы можете почитать в статье "Как рассчитать коэффициенты сезонности, очищенные от роста?"

Рассчитываем прогноз продаж с учетом роста и сезонности:

  1. Задаём период, на который мы хотим рассчитать прогноз. Для этого продлеваем номера периодов временного ряда на 2 года и 3 месяца.
  2. Рассчитываем значения трена для будущих периодов. В уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде (от 61 до 87). Получаем y-значения линейного тренда для каждого будущего периода (см. вложенный файл).
  3. Рассчитываем прогноз. Для этого значения линейного тренда умножаем на коэффициенты сезонности.

Прогноз с учетом роста и сезонности готов.

Вы также можете руками корректировать прогноз, изменяя коэффициенты a и b линейного тренда y=bx+a, об этом подробно вы можете почитать в статье «О линейном тренде».

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

  • реклама,

  • мероприятия по стимулированию сбыта,

  • ввод новых продуктов,

  • открытие новых направлений продаж,

  • спец. клиенты с разовыми значительными закупками

и т.д., но об этом в следующих статьях.

Точных вам прогнозов!

С помощью программы Forecast4AC PRO вы сможете рассчитывать прогноз с учетом роста и сезонности для более, чем 5000 строк одновременно одним нажатием клавиши. Легко и быстро!

Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

Статья полезная? Поделитесь с друзьями

Ваши комментарии и вопросы:

Comments  

 
#43 Алексей Батурин 12.09.2016 08:59
Цитирую Иван:
Цитирую Алексей Батурин:
Автоматически определить базы можно с помощью ретроспектисного тестирования и анализ ряда.

Спасибо за ответ. Можете указать каких авторов, книги можно почитать именно по базе прогноза, выборе периода упреждения? В большинстве случаев этому моменту уделяется мало внимания.

Иван, добрый день. Рад бы вам помочь, но, к сожалению, не встречал таких книг. Почти все делают упор на различные математические модели.
Quote
 
 
#42 Иван 09.09.2016 12:28
Цитирую Алексей Батурин:
Автоматически определить базы можно с помощью ретроспектисного тестирования и анализ ряда.

Спасибо за ответ. Можете указать каких авторов, книги можно почитать именно по базе прогноза, выборе периода упреждения? В большинстве случаев этому моменту уделяется мало внимания.
Quote
 
 
#41 Алексей Батурин 07.09.2016 16:08
Цитирую Иван:
Цитирую Алексей Батурин:
лучше выбрать базу, которая будет соответствовать текущим тенденциям - корреляция и тестирование.

Не совсем понятно что значит текущим тенденциям - корреляция и тестирование??

Иван, используйте модель скользящей средней с сезонностью http://4analytics.ru/prognozirovanie/raschet-prognoza-prodaj-po-metodu-skolzyasheie-sredneie-v-excel.html
под описанную вами задачу она лучше всего подходит. - будет учитывать последние тенденции и сезонность прошлых лет - если она есть. База для прогноза - это подготовка данных к прогнозу, а блок подготовки данных к прогнозу это статей на 10. По простому вам надо выбрать данные для прогноза, которые отвечают текущему поведению ряда с точки зрения сезонности и тренда, причем база для сезонности и база для тренда может отличатся, в модели скользящей средней она всегда отличается.
Автоматически определить базы можно с помощью ретроспектисног о тестирования и анализ ряда.
Quote
 
 
#40 Иван 07.09.2016 11:46
Цитирую Алексей Батурин:
лучше выбрать базу, которая будет соответствовать текущим тенденциям - корреляция и тестирование.

Не совсем понятно что значит текущим тенденциям - корреляция и тестирование??
Quote
 
 
#39 Алексей Батурин 06.09.2016 15:36
Цитирую Иван:
Здравствуйте!
Интересует такой вопрос:
В учебной литературе по эконометрике указывают, что чем больше база прогноза, тем лучше.
Согласен с этим утверждением, но как быть при оперативном планировании?
Если нам требуется сделать прогноз на месяц вперед, т.е. нам нужно уловить текущие тенденции, и не факт что есть сезонность. Если мы возьмем базу прогноза за 3 года, простой линейный тренд покажет, допусти снижение, а если возьмем 6 мес, то повышение и это будет более верно.
Так как же выбирать базу прогноза наиболее оптимально, при том, или ином периоде упреждения?

Иван, добрый день. Тренды - показывают тенденции к росту или падению за выбранный период времени. Тренд за 6 месяцев будет описывать тенденции за 6 месяцев, но не будут учитывать сезонность, которая характерна для этого месяца. Если вы хотите учесть последние изменения, при этом учесть сезонность, используйте модель скользящей средней
Вот здесь описание:
http://4analytics.ru/prognozirovanie/raschet-prognoza-prodaj-po-metodu-skolzyasheie-sredneie-v-excel.html
По поводу базы для прогноза - больше данных, однозначно не значит лучше, лучше выбрать базу, которая будет соответствовать текущим тенденциям - корреляция и тестирование. Или модель, которая сможет используя много данных сможет учесть то что важно для прогноза.
Quote
 
 
#38 Иван 05.09.2016 17:19
Здравствуйте!
Интересует такой вопрос:
В учебной литературе по эконометрике указывают, что чем больше база прогноза, тем лучше.
Согласен с этим утверждением, но как быть при оперативном планировании?
Если нам требуется сделать прогноз на месяц вперед, т.е. нам нужно уловить текущие тенденции, и не факт что есть сезонность. Если мы возьмем базу прогноза за 3 года, простой линейный тренд покажет, допусти снижение, а если возьмем 6 мес, то повышение и это будет более верно.
Так как же выбирать базу прогноза наиболее оптимально, при том, или ином периоде упреждения?
Quote
 
 
#37 Алексей Батурин 06.07.2016 10:15
Цитирую Vusal:
Здраствуйте. Нужна срочно помощь по двум вопросам :

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

4 Рассчитать план продаж за сентябрь 2014г. при условии увеличения количества уникальных клиентов с картами на 5%

5 Описать основные показатели оценки эффективности программы лояльности

6 Составить план по внедрению ERP системы в рознечной компании с 14 торговыми точками
этапы
ресурсы
сроки
ответственные специалисты

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

Здравствуйте.
Мы готовы на безвозмездной основе отвечать на вопросы, которые касаются конкретной статьи.
У вас практическая задача, нацеленная на результат. Такие задачи мы решаем, или в рамках обучения, или в виде проекта.
http://4analytics.ru/treningi-po-prognozirovaniu-ot-4analytics.html
Quote
 
 
#36 Алексей Батурин 27.06.2016 15:34
Цитирую ольга:

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

Ольга, отправьте мне файл на ящик , посмотрю. ;-)
Quote
 
 
#35 ольга 27.06.2016 15:14
Цитирую ольга:
Цитирую ольга:

Просто в примерах преимущественно все а именно исходные данные все года идут с повышением/увеличением выручки.Относительно моей ситуации есть месяцы которые отработаны хуже и итоговая выручка ниже допустим даже того же 2013 года.И высчитывая линейный тренд допустим для января я считаю план 1400 000 а по факту этого года он вышел 900 000, но с марта месяца расхождение по данному прогнозу расходится уже не больше 30 тысяч.

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


Да, я уже перепробовала 4 метода и во всех цифры разные. :oops: хоть бы кто то понимающий мог взглянуть вообще в правильном ли я двигаюсь направлении.
Quote
 
 
#34 ольга 08.06.2016 12:00
Цитирую ольга:

Просто в примерах преимущественно все а именно исходные данные все года идут с повышением/увеличением выручки.Относительно моей ситуации есть месяцы которые отработаны хуже и итоговая выручка ниже допустим даже того же 2013 года.И высчитывая линейный тренд допустим для января я считаю план 1400 000 а по факту этого года он вышел 900 000, но с марта месяца расхождение по данному прогнозу расходится уже не больше 30 тысяч.

Ольга, сложно давать рекомендации не видя данных. Но в прогнозировании все не так просто, надо определить факторы влияющие, очистить их - подготовить данные, потом построить прогноз, а потом скорректировать прогноз планируемыми факторами. Коэффициентами это не решить.
Quote
 

Добавить комментарий


Защитный код
Обновить