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

Пошаговое видео как с помощью Excel построить прогноз продаж на будущие периоды с учетом сезонности и создать красивый график прогноза. Решение можно применить в Excel 2003 и выше. Так же отдельно показано как можно быстро построить готовую модель прогноза в Excel 2016

[@tatianakostyuzheva]

Спасибо огромное! Видео 6 лет, а до сих пор судя по комментариям многим пригождается! Очень доступно рассказываете- показываете! Как только вам терпения хватает😄 Спасибо!

[@irinakarakuyan9555]

Дмитрий, огромное спасибо! Очень полезное видео, и очень доступным языком! Благодарю!

[@excel-vba_ru]

Спасибо!

[@alicebrassy]

Потрясающе! Очень помогло ваше видео! Большое спасибо за ваш труд!

[@maksim0933]

Спасибо Дмитрий! Пожалуйста продолжайте снимать такие видео)) они очень полезны

[@lumenone1698]

Очень полезная информация, благодарю Вас!

[@stellasr3707]

Спасибо огромное! Это, действительно, полезная информация и моя палочка-выручалочка 🙂

[@OleksiiBabych-e3e]

Спасибо. Отличное видео. Всё по делу, без лишней воды, но и не сухо.

[@kimirina.irina.]

Супер! Спасибо большое за такую ценную информацию!

[@MrTrylobeat]

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

[@user-ns4kj9uh2g]

Оочень информативное и полезное видео!!! Спасибо автору🙏

[@lanaguseynova8721]

спасибо человечище)))

[@madebykame]

Дмитрий, спасибо огромное за урок - завтра буду пробовать на практике :)

[@ИванЖигалев-й1ъ]

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

[@biweb13]

отличный лайфхак !!!

[@lme918]

Про доверительный интервал, это тоже самое, что вы использовали функцию доверт в первом варианте. Это ваша альфа, которую вы выбирали в функции доверт. (0,05 - это 100% - 95%=5%). Это связано с ст. отклонением и с тем, что 95% данных падает на отклонение от -2 и 2 (нормальное распределение, не помню как на русском). Соответсвенно, оно корректирует также как и в вашем первом варианте.

[@artetl]

Спасибо! Очень здорово, и без подключения языков типа питона, или R.

[@ИгорьХадеев-ч1н]

Очень помогло, спасибо большое…

[@ozz5939]

Полезная информация, спасибо

[@АлександрРябцов-э7у]

Круто! Спасибо! Было очень полезно!

[@grimplay5893]

Черт возьми, мой диплом спасен!!

[@sabinakopbossinova8734]

Спасибо вам огромное 💓💓💓

[@azaguljigitov]

Огромное спасибо .

[@samloik]

Спасибо, очень полезно!

[@AS_mund]

спасибо, Ви очень помогли!!

[@nvanastasiya]

Спасибо! Помогли.

[@user-rb9nr2qt5c]

большое тебе спасибо

[@АсельКарайганова]

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

[@ОльгаКозлова-ю7у]

Добрый день! Дмитрий , скажите почему у Вас в русном расчете по формулам одни данные, а в автоматическом расчете абмалютно другие? Как так получается? И где же верные данные?

[@Ayakimov]

Если бы он вставил факт, было бы понятно 😅

[@LeibstandarteBorisYeltsin]

о, уважаю!

[@СЕМЕНПОЛЯКОВ-х1к]

Круто

[@pavlovrmn]

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

[@LAFFlowers-c5e]

Очень полезное видео, спасибо! Подскажите, если периоды не года, а недели, как рассчитать в таком случае коэффициент сезонности (записать формулу)?

[@Bazi1i0]

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

[@excel-vba_ru]

Полностью согласен :) В видео об этом тоже вроде бы упоминаю. Это очень упрощенная модель.

[@Sven8807]

Как тогда строить наиболее верно прогноз для сезонного товара, когда история есть только за 2-,1,5-1-0,5 года, потому, что новая фирма?

[@Bazi1i0]

@@Sven8807 Вначале общая схема, а потом конкретно на Ваш вопрос. Итак: 1. Очищаете фактические данные от сезонности и делаете прогноз нормализованных продаж. 2. Да, для этого нужны данные как минимум за 2 года. Чем больше - тем лучше. Вы сможете выделить: a. случайные колебания коэффициентов сезонности и учесть их в прогнозе b. динамику коэффициентов сезонности. Например, может оказаться, что сезонность декабря последние 3 года снижается. В таком случае обоснованно будет предположить снижение декабрьского коэффициента сезонности и в прогнозе 3. Собственно построение прогноза: строите прогноз нормализованных продаж, а у же на них накладываете сезонность. Для этого отложите в сторону эксель и хитрые модели - поговорите с менеджерами, отвечающими за продажи. Поговорите с маркетологами, с категорийными, с производственниками. Получите ответы на такие вопросы: какие планируются маркетинговые мероприятия, планируется ли ввод в ассортимент новых товаров, вводятся ли новые производственные мощности, планируется ли изменение ценовой политики и т.д. Исходя из полученных ответов стройте ваш прогноз. Теперь про ситуацию когда нет данных для расчёта сезонности за год. Например, у вас новый бизнес. И в таком случае есть несколько проверенных подходов: 1. Если это новый бизнес, то велика вероятность, что среди руководства есть человек, который уже работал в этом секторе и у него есть данные по продажам такого товара в другой компании. Поговорите, узнайте, получите эти данные. 2. Поищите нужные данные у Минстата. Это могут быть, например, данные по импорту/экспорту или по продажам. По этим данным можно построить коэффициенты сезонности. 3. В самом глухом случае воспользуйтесь экспертными оценками. Опять же опросите доступных вам менеджеров. Например, по рынку дрогери/фарм, я бы по памяти поставил бы такие коэффициенты (по месяцам года): 0.8; 0.9; 1.2; 1.0; 0.9; … и т.д. Конечно у конкретной компании из такой отрасли коэффициенты будут другие, но достаточно близкие.

[@Sven8807]

@@Bazi1i0 Благодарю за ответ, Игорь. Буду пробовать.

[@vadymm.1153]

Спасибо

[@ExcelStore]

Учет сезонности в расчетах, как правило, всегда сложно. Поэтому влияние сезоннности зачастую вовсе игнорируют. Спасибо!

[@Ольга-м6я8я]

Дмитрий, почему расчеты Ваши ручные кардинально отличаются от расчетов автоматических в екселе? У Вас в ручном Вы показываете одни цифры итоговые расчетные , а в конце вообще другие цифры в автоматическом расчете екселя . Как так prnt.sc/praq9a и prnt.sc/prao1i

[@Ayakimov]

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

[@Андрей444-м4я]

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

[@excel-vba_ru]

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

[@АлинаТемендерова]

Здравствуйте, хотела узнать вы репетиторством не занимаетесь ?

[@dfcaafaf943]

Пессимистический прогноз получился отрицательным как фиксить?)

[@МихайлоДорош-е5й]

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

[@cartoonsforchildrenandadul6999]

Пожалуйста ответьте мне как для чайника этот анализ является статистическим или больше эконометрическим?

[@feminicratos294]

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

[@excel-vba_ru]

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

[@ПравдаЧиБрехня]

Очень хороший урок. Но данные в двух вариантах разные. Я так понимаю должны быть одтнаковые?

[@excel-vba_ru]

Олександр, нет. Там и формулы используются разные и в общем несколько отличается принцип расчетов. поэтому одинаковые они не должны быть, хотя и могут. Для 2016 Excel автоматический прогноз использует более "умные" функции и алгоритмы.

[@ПравдаЧиБрехня]

Это я понял. Наверное суть моего вопроса в том, какой метод более точный (в отдельных случаях разница колосальная). По этому возникает вопрос в актуальности такого прогноза. Если первый то как прийти к нему формулами 2010 версии. И еще, есть прогноз в диаграммах - линии тренда, он еще другой. Хорошо бы понять природу этих прогнозов, понять какой самый точный и интересно как посчитать линию тренда в формулах (показать эту линию в цифрах).

[@excel-vba_ru]

Олександр, если нужен прям точный прогноз - такие модели есть в сети, можно поискать. Цель сего видеорука - показать как можно сделать простой прогноз на скорую руку. Он много чего не учитывает. И кстати, первый вариант реализуется и в 2010 и даже в 2003 Excel. А вот второй, который будет более точным - только начиная с 2016.

[@margarita2451]

А как провести цыфровой Ряд?? Скажите пожалуйста Дмитрий!!

[@81mIX]

Добрый день. У меня есть данные за 53 дня по группам товаров и магазинам.Как лучше построить прогноз.

[@Edil.D]

Добрый день! У меня почему-то при вводе формулы для коэффициента сезонности когда нажимаю сочетание клавиш cntrl+shift+enter для массива появляется ошибка: «В таблицах нельзя использовать формулы для массивов с несколькими ячейками. Хотя делаю аналогично как показывают на видео

[@excel-vba_ru]

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

[@SvetlanaAnokhina-on8ig]

Дмитрий, здравствуйте! Пытаюсь разобраться в проблеме - при использовании ПРЕДСКАЗ на агрегированных данных работает нормально, а если строить прогноз детально по продуктам она возвращает отрицательные значения продаж. Можете подсказать, в чем может быть проблем? Та же проблема при использовании ПРЕДСКАЗ.ETS

[@excel-vba_ru]

Светлана, без примера данных проблему не понять и не решить. Возможно, не агрегированные данные имеют тенденцию к снижение, что и показывает ПРЕДСКАЗ. А когда Вы их агрегируете - отрицательные значения "схлопываются" положительными. Попробуйте написать в форум (www.excel-vba.ru/forum/index.php?board=3.0) - там можно прикладывать файлы. Можете и здесь, конечно, попробовать ссылку кинуть - но конф.информацию лучше заранее удалить в любом случае.

[@АлёнаБоеску]

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

[@excel-vba_ru]

Алёна, по сути так же, как и для месяцев. Только прописываете фактические и прогнозные даты одна за другой на каждый день. Если нужно что-то вроде сезонности, то определите период из кол-ва дней и используйте его вместо 12. Хотя не уверен, что для прогноза на каждый день имеет смысл вообще сезонность учитывать и поэтому можете её вообще не использовать.

[@DmitrijVictorovich]

Здравствуйте! Очень понравилось. Скажите пожалуйста как с вами связаться? Есть группа Вконтакте?

[@excel-vba_ru]

vk.com/excel_vba_ru

[@DmitrijVictorovich]

@@excel-vba_ru подписался

[@АлександрК-и7с]

Дмитрий здравствуйте. Подскажите, если требуется планирование на неделю,с учетом 2-3 предыдущих,нумерацию как выполнять 1-21 или три раза по 1-7?

[@excel-vba_ru]

От 1 до 21

[@DmitryT108]

Очень жаль что вы не показали как суммировать последовательно. Допустим: 1столбец+ 2столбец=результат. 1столбец+2столбец+3столбец=результат и т.д. Это так важно для динамики графика((( но так мало об этом пишут.

[@excel-vba_ru]

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

[@DmitryT108]

@@excel-vba_ru Да, понимаю что тут о другом, но даже не знаю как назвать то что мне нужно)))вот благодаря вам уже знаю))) линейное сложение. а1=b1. а1+а2=b2. а1+а2+а3=b3. это же не сложно для экселя...уверен. просто нет знания(((

[@DmitryT108]

@@excel-vba_ru задача решена. Спасибо)

[@ОлегІльчишин]

А якщо встановити сезонность 12, хіба це не зробить прогноз точнішим?

[@lyudmilatarakanova9150]

При расчёте продаж вышла какая-то не суразица с отрицательным значениями, такого быть не может. Делала ровно все по Вашему видео, продажи рваные. В итоге в общем расчёте годового оборота ушли в минус 75% в продажах. Такого не может быть. Перепроверил все формулы несколько раз, не сходится.....

[@excel-vba_ru]

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

[@beknazaromorhanov7289]

Когда я умножаю предсказ на индекс получается ноль, что делать?

[@diii_1_]

🆘🆘🆘ПОДСКАЖИТЕ, ПОЖАЛУЙСТА, как просчитать если в некоторых месяцах отсутствуют показали в связи с отсутствием товара на складе? Если брать за ноль, то прогноз выходит неверным 😢

[@excel-vba_ru]

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

[@Егорметельков-л9р]

Здравствуйте Дмитрий ! А при расчете коэф сезонности если бы мы брали три года то все равно умножали бы на 12? объясните плиз почему умножаем на 12?

[@excel-vba_ru]

Надежда, да, умножать надо все равно на 12. Но при этом надо будет в формулу добавить диапазон данных третьего год. Т.е. вместо формулы: =(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12 должно получиться нечто вроде: =(($B$2:$B$13+$B$14:$B$25+$B$26:$B$37)/СУММ($B$2:$B$37))*12 В статье я подробнее расписывал принципы работы формул из видео: www.excel-vba.ru/chto-umeet-excel/prognoz-prodazh-v-excel/

[@Егорметельков-л9р]

Спасибо Дмитрий !!! А ещё скажите если бы мы коэффициент сезонности считали за один год то на 12 уже умножать не надо получается?

[@excel-vba_ru]

Надежда, на 12 умножать надо в любом случае, т.к. без этого не получить коэффициент за месяц.

[@Егорметельков-л9р]

Дмитрий, спасибо! 😊

[@ЛюдмилаМулина-л8д]

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

[@mgl367v4]

А как предсказать продажи для нового бизнеса? Как то можно ?

[@excel-vba_ru]

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

[@unclesam1073]

на 8:30, после перемножения индекса у меня получаются такие значения: ################### и до бесконечности. Как быть?

[@excel-vba_ru]

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

[@АнастасияДивакова]

Дмитрий, подскажите, пожалуйста, делаю как Вы рассказываете, но при формуле предсказ выходит ошибка #знач！почему это может быть и как тогда исправить？

[@excel-vba_ru]

Анастасия, убедитесь, что числа это реальные числа, а даты - даты. Сначала убедитесь, что нигде нет текстовых значений, лишних пробело, точек и других символов, которые делают значение текстовым(можно в отдельном столбце проверить через функцию ЕЧИСЛО). Если лишних символов не нашли, то значит числа или даты просто записаны как текст. Тогда скопируйте любую пустую ячейку, затем выделите столбцы с суммами и датами -Правая кнопка мыши -Специальная вставка -операция "Сложить". Ок. После этого назначьте при необходимости заново форматы в ячейках.

[@АнастасияДивакова]

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

[@excel-vba_ru]

Анастасия, "неделя с 05.04.2010" - это не дата, это текст. Как вариант, где-нибудь в стороне просто пронумеруйте эти строки(от 1 до конца данных) и ссылайтесь на столбец с этими номерами.

[@АнастасияДивакова]

Дмитрий Щербаков спасибо

[@user-zj5ht1vc1j]

как называеться эта модель ?

[@РусланДоич-х8ф]

Вы вообще в курсе , что функция ПРЕДСКАЗ использует в своем расчете метод линейной регрессии? Как в качестве параметра может использоваться ДАТА??? Как могут коррелировать точная дата и продажи???

[@stasessiya]

моделирование временных рядов чекни

[@Rozzy_long_hair]

там грустно-Предсказ почему то не работает , в самом начале и уже не раблотает(