Простой прогноз продаж в Excel с учетом сезонности
Вставка
- Опубліковано 22 бер 2017
- Пошаговое видео как с помощью Excel построить прогноз продаж на будущие периоды с учетом сезонности и создать красивый график прогноза. Решение можно применить в Excel 2003 и выше. Так же отдельно показано как можно быстро построить готовую модель прогноза в Excel 2016
Спасибо огромное! Видео 6 лет, а до сих пор судя по комментариям многим пригождается! Очень доступно рассказываете- показываете! Как только вам терпения хватает😄 Спасибо!
Спасибо. Отличное видео. Всё по делу, без лишней воды, но и не сухо.
Спасибо Дмитрий! Пожалуйста продолжайте снимать такие видео)) они очень полезны
Спасибо огромное! Это, действительно, полезная информация и моя палочка-выручалочка 🙂
Потрясающе! Очень помогло ваше видео! Большое спасибо за ваш труд!
Супер! Спасибо большое за такую ценную информацию!
Дмитрий, спасибо огромное за урок - завтра буду пробовать на практике :)
Очень полезная информация, благодарю Вас!
Дмитрий, огромное спасибо! Очень полезное видео, и очень доступным языком! Благодарю!
Спасибо!
Оочень информативное и полезное видео!!! Спасибо автору🙏
Спасибо! Очень здорово, и без подключения языков типа питона, или R.
Круто! Спасибо! Было очень полезно!
про прогноз с помощью вкладки данные очень полезная штука
спасибо человечище)))
Полезная информация, спасибо
отличный лайфхак !!!
Про доверительный интервал, это тоже самое, что вы использовали функцию доверт в первом варианте.
Это ваша альфа, которую вы выбирали в функции доверт. (0,05 - это 100% - 95%=5%). Это связано с ст. отклонением и с тем, что 95% данных падает на отклонение от -2 и 2 (нормальное распределение, не помню как на русском). Соответсвенно, оно корректирует также как и в вашем первом варианте.
Спасибо. Отличная информация доступно рассказана.
Вопрос: можно ли сделать такой прогноз в разрезе по неделям ?
Очень помогло, спасибо большое…
спасибо, Ви очень помогли!!
Спасибо вам огромное 💓💓💓
Огромное спасибо .
Спасибо, очень полезно!
Очень полезное видео, спасибо! Подскажите, если периоды не года, а недели, как рассчитать в таком случае коэффициент сезонности (записать формулу)?
Дмитрий, здравствуйте. Спасибо большое за видео. Хотел бы уточнить один момент - можно ли данные доверительного интервала использовать в качестве страхового запаса? Или же нужно обратиться к показателям ошибок расчета?
Спасибо! Помогли.
большое тебе спасибо
Спасибо
Подскажите, пожалуйста, как мне посчитать прогноз, в разрезе кварталов за несколько лет?
Учет сезонности в расчетах, как правило, всегда сложно. Поэтому влияние сезоннности зачастую вовсе игнорируют. Спасибо!
А как провести цыфровой Ряд??
Скажите пожалуйста Дмитрий!!
Здравствуйте, хотела узнать вы репетиторством не занимаетесь ?
Круто
о, уважаю!
Пожалуйста ответьте мне как для чайника этот анализ является статистическим или больше эконометрическим?
Очень понравилось видео, но хотелось бы попросить автора все таки уделить несколько минут для объяснения терминов доверительный интервал и статистических показателей в финальной таблице своими словами. Тогда было бы вообще здорово. А так остались вопросы после видео,так как доверительный интервал напрямую как вы сказали влияет на точность прогнозов. А после видео еще копаться в интернете и искать дополнительные данные как то не очень извините
Добрый день. У меня есть данные за 53 дня по группам товаров и магазинам.Как лучше построить прогноз.
Дмитрий здравствуйте. Подскажите, если требуется планирование на неделю,с учетом 2-3 предыдущих,нумерацию как выполнять 1-21 или три раза по 1-7?
От 1 до 21
Спасибо огромное!
Но почему отклонение считается для прогнозируемых значений, а не реальных?
У реальных значений разброс ведь больше. Вы уверены что так правильно, либо это вариативно?
На мой взгляд, у реальных значений уже не может быть отклонений - потому что они уже свершились и никаких отклонений для них быть не может. Поэтому отклонения рассчитываются только для прогноза, т.к. прогноз это вещь неточная и всегда есть отклонение предполагаемых нами значений от тех, которые будут в реальности на эти периоды.
Черт возьми, мой диплом спасен!!
Добрый день! Дмитрий , скажите почему у Вас в русном расчете по формулам одни данные, а в автоматическом расчете абмалютно другие? Как так получается? И где же верные данные?
Если бы он вставил факт, было бы понятно 😅
Дмитрий, почему расчеты Ваши ручные кардинально отличаются от расчетов автоматических в екселе? У Вас в ручном Вы показываете одни цифры итоговые расчетные , а в конце вообще другие цифры в автоматическом расчете екселя . Как так prnt.sc/praq9a и prnt.sc/prao1i
Сезонность во втором случае не учтена, нужно было хотя бы 12 поставить (для каждого месяца)
а как учитывается в данной функции Предсказ - 1) рост или падение текущего ассортимента и наличия товаров по причинам взбредшим в голову начальника , добавление или удаление новых сервисов в компании которые повышают продажи в неизвестной степени, подключение новых регионов продаж, новых методов рекламы. а как учитывается насыщение рынка, ибо у него есть емкость. Много ли таких фирм у которых все эти параметры с годами неизменны, для того чтобы пользоваться данной функцией?
Андрей, ну придраться можно и к намного более крутым моделям прогноза. Никто не спрогнозирует появление коронавируса и всяких других форс-мажорных факторов. Любой подобный прогноз дает лишь примерное понимание на будущие периоды
Очень хороший урок. Но данные в двух вариантах разные. Я так понимаю должны быть одтнаковые?
Олександр, нет. Там и формулы используются разные и в общем несколько отличается принцип расчетов. поэтому одинаковые они не должны быть, хотя и могут. Для 2016 Excel автоматический прогноз использует более "умные" функции и алгоритмы.
Это я понял. Наверное суть моего вопроса в том, какой метод более точный (в отдельных случаях разница колосальная). По этому возникает вопрос в актуальности такого прогноза. Если первый то как прийти к нему формулами 2010 версии. И еще, есть прогноз в диаграммах - линии тренда, он еще другой. Хорошо бы понять природу этих прогнозов, понять какой самый точный и интересно как посчитать линию тренда в формулах (показать эту линию в цифрах).
Олександр, если нужен прям точный прогноз - такие модели есть в сети, можно поискать. Цель сего видеорука - показать как можно сделать простой прогноз на скорую руку. Он много чего не учитывает. И кстати, первый вариант реализуется и в 2010 и даже в 2003 Excel. А вот второй, который будет более точным - только начиная с 2016.
Добрый вечер, как можно рассчитывать прогноз на каждый день? мне необходимо в рамках одного месяца спрогнозировать выручку на каждый день недели(т.е. спрогнозировать ежедневную выручку)
Алёна, по сути так же, как и для месяцев. Только прописываете фактические и прогнозные даты одна за другой на каждый день. Если нужно что-то вроде сезонности, то определите период из кол-ва дней и используйте его вместо 12. Хотя не уверен, что для прогноза на каждый день имеет смысл вообще сезонность учитывать и поэтому можете её вообще не использовать.
Здравствуйте! Очень понравилось. Скажите пожалуйста как с вами связаться? Есть группа Вконтакте?
vk.com/excel_vba_ru
@@excel-vba_ru подписался
Дмитрий, здравствуйте! Пытаюсь разобраться в проблеме - при использовании ПРЕДСКАЗ на агрегированных данных работает нормально, а если строить прогноз детально по продуктам она возвращает отрицательные значения продаж. Можете подсказать, в чем может быть проблем? Та же проблема при использовании ПРЕДСКАЗ.ETS
Светлана, без примера данных проблему не понять и не решить. Возможно, не агрегированные данные имеют тенденцию к снижение, что и показывает ПРЕДСКАЗ. А когда Вы их агрегируете - отрицательные значения "схлопываются" положительными.
Попробуйте написать в форум (www.excel-vba.ru/forum/index.php?board=3.0) - там можно прикладывать файлы. Можете и здесь, конечно, попробовать ссылку кинуть - но конф.информацию лучше заранее удалить в любом случае.
А якщо встановити сезонность 12, хіба це не зробить прогноз точнішим?
Пессимистический прогноз получился отрицательным как фиксить?)
Здравствуйте Дмитрий ! А при расчете коэф сезонности если бы мы брали три года то все равно умножали бы на 12? объясните плиз почему умножаем на 12?
Надежда, да, умножать надо все равно на 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/
Спасибо Дмитрий !!! А ещё скажите если бы мы коэффициент сезонности считали за один год то на 12 уже умножать не надо получается?
Надежда, на 12 умножать надо в любом случае, т.к. без этого не получить коэффициент за месяц.
Дмитрий, спасибо! 😊
Добрый день! У меня почему-то при вводе формулы для коэффициента сезонности когда нажимаю сочетание клавиш cntrl+shift+enter для массива появляется ошибка: «В таблицах нельзя использовать формулы для массивов с несколькими ячейками. Хотя делаю аналогично как показывают на видео
Потому что Вы пытаетесь ввести формулу массива в ячейки умной таблицы. А там этого действительно нельзя делать сразу для нескольких строк. Преобразуйте таблицу в обычную
Для студенческой курсовой сгодится. В реальном бизнесе за такой прогноз получите по шапке ))
Полностью согласен :) В видео об этом тоже вроде бы упоминаю. Это очень упрощенная модель.
Как тогда строить наиболее верно прогноз для сезонного товара, когда история есть только за 2-,1,5-1-0,5 года, потому, что новая фирма?
@@Sven8807 Вначале общая схема, а потом конкретно на Ваш вопрос. Итак:
1. Очищаете фактические данные от сезонности и делаете прогноз нормализованных продаж.
2. Да, для этого нужны данные как минимум за 2 года. Чем больше - тем лучше. Вы сможете выделить:
a. случайные колебания коэффициентов сезонности и учесть их в прогнозе
b. динамику коэффициентов сезонности. Например, может оказаться, что сезонность декабря последние 3 года снижается. В таком случае обоснованно будет предположить снижение декабрьского коэффициента сезонности и в прогнозе
3. Собственно построение прогноза: строите прогноз нормализованных продаж, а у же на них накладываете сезонность. Для этого отложите в сторону эксель и хитрые модели - поговорите с менеджерами, отвечающими за продажи. Поговорите с маркетологами, с категорийными, с производственниками. Получите ответы на такие вопросы: какие планируются маркетинговые мероприятия, планируется ли ввод в ассортимент новых товаров, вводятся ли новые производственные мощности, планируется ли изменение ценовой политики и т.д. Исходя из полученных ответов стройте ваш прогноз.
Теперь про ситуацию когда нет данных для расчёта сезонности за год. Например, у вас новый бизнес. И в таком случае есть несколько проверенных подходов:
1. Если это новый бизнес, то велика вероятность, что среди руководства есть человек, который уже работал в этом секторе и у него есть данные по продажам такого товара в другой компании. Поговорите, узнайте, получите эти данные.
2. Поищите нужные данные у Минстата. Это могут быть, например, данные по импорту/экспорту или по продажам. По этим данным можно построить коэффициенты сезонности.
3. В самом глухом случае воспользуйтесь экспертными оценками. Опять же опросите доступных вам менеджеров. Например, по рынку дрогери/фарм, я бы по памяти поставил бы такие коэффициенты (по месяцам года): 0.8; 0.9; 1.2; 1.0; 0.9; … и т.д. Конечно у конкретной компании из такой отрасли коэффициенты будут другие, но достаточно близкие.
@@Bazi1i0 Благодарю за ответ, Игорь. Буду пробовать.
как называеться эта модель ?
Когда я умножаю предсказ на индекс получается ноль, что делать?
Дмитрий, подскажите, пожалуйста, делаю как Вы рассказываете, но при формуле предсказ выходит ошибка #знач！почему это может быть и как тогда исправить？
Анастасия, убедитесь, что числа это реальные числа, а даты - даты. Сначала убедитесь, что нигде нет текстовых значений, лишних пробело, точек и других символов, которые делают значение текстовым(можно в отдельном столбце проверить через функцию ЕЧИСЛО). Если лишних символов не нашли, то значит числа или даты просто записаны как текст. Тогда скопируйте любую пустую ячейку, затем выделите столбцы с суммами и датами -Правая кнопка мыши -Специальная вставка -операция "Сложить". Ок. После этого назначьте при необходимости заново форматы в ячейках.
Дмитрий Щербаков показывает ошибку на колонку с датами, отредактировала, это именно даты... мне нужно отчет сделать понедельно за 4 года. прописано :”неделя с 05.04.2010” и т.д....
Анастасия, "неделя с 05.04.2010" - это не дата, это текст. Как вариант, где-нибудь в стороне просто пронумеруйте эти строки(от 1 до конца данных) и ссылайтесь на столбец с этими номерами.
Дмитрий Щербаков спасибо
А как предсказать продажи для нового бизнеса?
Как то можно ?
Не думаю, что можно предсказать вообще без каких-либо данных. Нужны как минимум данные похожего бизнеса, чтобы было от чего оттолкнуться. А без данных с тем же успехом можно просто наугад цифр накидать.
🆘🆘🆘ПОДСКАЖИТЕ, ПОЖАЛУЙСТА, как просчитать если в некоторых месяцах отсутствуют показали в связи с отсутствием товара на складе? Если брать за ноль, то прогноз выходит неверным 😢
Если делаете формулой - то придется кардинально менять расчет коэффициента сезонности и без данных сказать сложно как именно. Если делаете через лист прогноза - то удалите нулевые показатели(просто клавишей Del) и тогда в параметрах можно выбрать расчет отсутствующих значений методом интерполяции.
Знаете, как то у меня очень странно получилось, вроде все делала по видео а все прогнозы получились совсем не оптимистичные
на 8:30, после перемножения индекса у меня получаются такие значения: ################### и до бесконечности. Как быть?
Сергей, это значит, что получившееся число нельзя отобразить в назначенном формат даты-времени. Обычно для отрицательных чисел так получается.
При расчёте продаж вышла какая-то не суразица с отрицательным значениями, такого быть не может. Делала ровно все по Вашему видео, продажи рваные. В итоге в общем расчёте годового оборота ушли в минус 75% в продажах. Такого не может быть. Перепроверил все формулы несколько раз, не сходится.....
Людмила, сказать особо нечего - проблема либо в исходных данных, либо все же где-то ошибка в расчетах. Других вариантов нет.
Очень жаль что вы не показали как суммировать последовательно. Допустим: 1столбец+ 2столбец=результат. 1столбец+2столбец+3столбец=результат и т.д. Это так важно для динамики графика((( но так мало об этом пишут.
Дмитрий, а почему я должен был это показывать, если это не применяется в данном видео? :) Здесь идет разбор совсем другого примера и линейного сложения данных столбцов нет вообще...
@@excel-vba_ru Да, понимаю что тут о другом, но даже не знаю как назвать то что мне нужно)))вот благодаря вам уже знаю))) линейное сложение. а1=b1. а1+а2=b2. а1+а2+а3=b3. это же не сложно для экселя...уверен. просто нет знания(((
@@excel-vba_ru задача решена. Спасибо)
там грустно-Предсказ почему то не работает , в самом начале и уже не раблотает(
Вы вообще в курсе , что функция ПРЕДСКАЗ использует в своем расчете метод линейной регрессии? Как в качестве параметра может использоваться ДАТА??? Как могут коррелировать точная дата и продажи???
моделирование временных рядов чекни