Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Прогнозирование продаж на основании предшествующих периодов с учетом ошибки
 
#1
08.10.2019 13:01:34
Добрый день!

Подскажите пожалуйста, может кто нибудь знает максимально точные способы прогнозирования (продаж).
Поискал в интернете, попробовал формулы ТЕНДЕНЦИЯ, ПРЕДСКАЗ, и некоторые другие, но результат к сожалению далек от идеала (проверял я самым прямолинейным образом, попытался спрогнозировать с помощью формул уже известные величины в исходных данных, например 2018 год или 2017) разлет большой получился. Поэтому необходимо их наверное чем то дополнять или модифицировать.

Задача не новая, поэтому решил спросить, может кто то ее уже решал
 
 
 
#2
08.10.2019 13:07:28
Опять название никакое.
Погнозирование погоды - по такому названию Вы можете сказать, какая сегодня погода?
Расчет - по такому названию можно понять, что и зачем считается?
Kentavrik7 - по такому Вы поймете, что от Вас хотят?
 
 
 
#3
08.10.2019 14:22:39
vikttur, "Прогнозирование продаж на основании предшествующих периодов с учетом ошибки"
Может такое?
 
 
 
#4
08.10.2019 14:42:42
На основе только этих данных можно сделать только то, что в файле во вложении. Для поверхностного прогноза достаточно данных прошлого года. Берете тенденцию изменения продаж между месяцами прошлого года. К примеру, разница между январем 2018 и декабрем 2017 в процентах. Для определения января 2019 нужно взять декабрь 2018 и прибавить коэффициент прошлого года.

декабрь 2017  январь 2018  декабрь 2018  
1000                 1100               2000

коэффициент будет 10%. К декабрю 2018 прибавляете 10%. Итого, январь 2019=2200
В файле я взял данные со всех прошлых лет и средний коэффициент изменения по месяцам. Для более точного прогноза нужно намного больше информации. Что за товар, портрет потребителя, увеличение/уменьшение компании продавца, данные прошлых лет желательно знать по неделям, а в идеале по дням, сезонность товара, если товар сезонный, то прогноз погоды в помощь. Это все не просто по формуле в экселе делается)
Изменено: Hellmaster - 08.10.2019 14:43:22
 
 
 
#5
08.10.2019 14:54:21
Мне кажется, что это называется учет сезонности при прогнозировании.
Можно нагуглить или просто посмотреть, например здесь
 
 
 
#6
08.10.2019 15:09:43
Hellmaster, Интересный метод кстати вы предложили для поверхносного прогнозирования. Вроде и простой, а вроде и получается значения близкие. Товар зависит от сезонности (кстати Вы наверное правильно заметили в большей степени от температуры конечно). Интересная, задумка добавить сюда среднемесячную температуру
Спасибо, Вам за идею)
Хотя нет значения близкие не получаются, Я попробовал посмотреть на примере 2017 года (спрогнозировать его) разлет к сожалению колоссален
IKor, Спасибо  
Изменено: Kentavrik7 - 08.10.2019 15:20:39
 
 
 
#7
08.10.2019 16:44:19
Hellmaster, подскажите пожалуйста, как бы вы делали прогноз если известна дополнительно температура средняя по месяцам?
 
 
 
#8
08.10.2019 17:14:33
Kentavrik7
Как аналитик, прогноз по формулам - предложенные экселем, подходит обычно для первичного, общего, не самого точного анализа.
Вопрос стоит другой. Что именно Вы хотите спрогнозировать? (от этого будет зависеть метод)
Пример
У Вас есть товар, который имеет сезонность. Функции экселя, в этом случае могут помочь отчасти, но более верным будет рассчитать ср. скорость продаж (желательно иметь признак - со скидкой/без скидки), возможно понадобятся стандартные ABC и XYZ + трендами и декомпозицию по постоянной, переменной и сезонной составляющей. Трендовую составляющую сверить с той, что получите через формулы, возможно внести правки. А далее уже простой расчет, исходя например из бюджета и процесса(сроков) поставки
P.S Не нашел способа декомпозицию делать в эксель, делаю через R. Даже тему на данном форуме оставлял, увы...  Если пойдете по этому пути и найдете решение, пжлст, оставьте ссылку=)
C2-C4
 
 
 
#9
08.10.2019 17:17:44
Михаил Комиссаров, Потребление энергии, грубо говоря, товар специфичен, скидок нет, цена постоянна  
 
 
 
#10
08.10.2019 17:23:16
Kentavrik7
У есть ли у Вас смежные показатели? например - кол-во подкл квартир?
1 - Идея - найти показатель, с которым коррелирует расходы (формула коррел).
2 - Вариант со ср. может правда подойти. Для этого возьмите ср. значение за прошедший период и умножте на цену. Постройте такую модель за уже прошедший период. После того как получите данные за прошедший период, наложите реальные данные, получите вариацию (коридор отклонения прогноза). Спрогнозируйте будущую цифру по методу среднего и добавьте вариацию
Пример (упрощенный)
1 2 3 1 2 3 1 2 3
Ср - число 2. По прогнозу может быть (условно) 2-ка. Смотрим разницу между план фактом получаем +-1. Соответственно для получения вариации берем 1 и делим на 2, получаем 50%
Как итог, при прогнозе цифры 2 у нас возможное отклонение +-50%  
C2-C4
 
 
 
#11
08.10.2019 17:52:51
Михаил Комиссаров, Нашел кол-во потребителей, определил среднее потребление 1-го, теперь нужно как то это привязать к температуре, и попробовать спрогнозировать
 
 
 
#12
08.10.2019 17:53:35
Kentavrik7,
тема давняя и проблемная в плане точности построения прогноза. В открытых источникам много информации как раз с Вашей спецификой.
И вопросов достаточно
- это потребление юридических/физических лиц
- календарные эффекты нужно учитывать/или без них можно
- какая точность прогнозирования считается отличной/приемлемой/недопустимой

Из общих рекомендаций. Excel тут вряд ли хороший помощник. Если только в сторону сезонного экспоненциального сглаживания смотреть (ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ) и связанные функции.

А так я бы в сторону или специализированного софта смотрел, или в R/Питон.
Из бесплатного Demetra можно рекомендовать, но программа уж очень специфическая
 
 
 
#13
08.10.2019 18:00:50
Цитата
Stics написал: это потребление юридических/физических лиц
Суммарно
Цитата
- календарные эффекты нужно учитывать/или без них можно
Нет
Цитата
- какая точность прогнозирования считается отличной/приемлемой/недопустимой
Тут, сложно сказать точно, хотелось бы конечно при прогнозировании 2017 года, или 2018 получить приближенные данные к фактическим, ну это наверное мечта многих)
 
 
 
#14
09.10.2019 09:13:43
Kentavrik7

Если смотреть более подробно, замеченные закономерности - Каждый год (за искл 2018) в среднем, итоговая сумма уменьшается на 2% в среднем (по отношению к предыдущему). Ярко выражена сезонность. Отсутствуют смежные показатели (в файле примере по крайней мере).
Если строить модель, именно по данным с исходника (1 сообщение), то продолжая логику данных за прошедший период, сделал 3 прогноза на 2019. Проверьте на текущих данных у себя.
Отдельно, Вам необходимо разобраться в январе 2016 - там присутствует пиковый выброс. Необходимо узнать причину, либо не исп. эти данные
в 2018 выбросов было больше, но сами выбросы были слабее. (февраль, март, май). Благодаря данным выбросам - нарушился ежегодный ниспадающий тренд (сумма за год, по отношению к предыдущему, постоянно уменьшалась на 2 %). Т.е. из за  данных месяцов, сумма за год в 2018 = сумме за год в 2017. Необходимо изучить данные месяца, уточнить причину.
Если подобные явления прогнозируемые (например акции проводили), то зная будущие акции, можете скорректировать значения в 2019. Файл во вложении=)
C2-C4
 
 
 
#15
09.10.2019 10:03:29
Михаил Комиссаров,Спасибо большое!
А могли бы еще посмотреть мою последнюю табличку, я взял среднемесячную температуру и примерное кол-во покупателей
 
 
 
#16
09.10.2019 10:58:43
Kentavrik7
Пример простого прогноза с влияющими кофф.
На листе Данные, построил пример построение модели. Общий итог по модели - отклонение от факта на 4% (график будет представлен)

Что сделали.
Взял все данные с листа - Лист1 (данные которые переносил подсветил цветом, логику можно будет отследить)
Далее - нашли корреляцию (т.е. показатели, которые лучше всего влияют на итоговую сумму). Далее, благодаря этим показателям, формулой тенденция, построили прогноз. Для экселя - результат довольно не плохой=)
Собственно можете посмотреть на лист данные, что бы понять пример
Попробуйте, как совет, метод МНК. Скачайте gretl. Она на русском языке и более чем понятная. Если находитесь в Москве, или есть скайп, то могу продемонстрировать как через нее создавать модели прогноза (либо ютуб в помощь с гуглом)
C2-C4
 
 
 
#17
09.10.2019 11:08:22
Kentavrik7
На тестовой версии, ту что в сообщении выше, модель получилась довольно точная, но есть НО
Если посмотреть на модель прогноза, станет очевидно, коф. подобрали хорошо, есть прямое совпадение с фактом, однако
с 1 по 11 ряд попадание прогноза в факт, точность около 98 - 99%
с 12 по 15 ряд - просадка
с 15 по 24 ряд - ряд попадание прогноза в факт, точность около 98 - 99%

Соответственно, что то проищошло в эти месяца, что повлияло в прошлом на цифры факта. Непредвиденный параметр. Пришелся он я янв 17 по июнь 17. Причем в 2016г, в тот же период - попадание прогноза с суммой факта - 99% повторюсь. Значит это не сезонное явление. Это или выброс или временное изменение тренда, что поовлекло за собой изменение веса кофф. Возможно присутствует непредвиденный параметр? неучтенный. (Это кол-во потребителей).
В файле ниже, добавили этот параметр, посмотрите как изменилась модель прогноза=) (в 1 случае - отклонение 4% в текущем только 1%)
C2-C4
 
 
 
#18
09.10.2019 11:57:25
Михаил Комиссаров,метод МНК это ведь формула Тенденции?
Цитата
Михаил Комиссаров написал:
На тестовой версии, ту что в сообщении выше, модель получилась довольно точная, но есть НО
Согласен, получается очень хорошо, но мы опираемся постоянно на известные величины: суммарный объем, температуру, и кол-во, если строить прогноз на 2020 год эти данные будут не известны, и тут уже, я не совсем понимаю как пользоваться данными методами, могли бы вы рассказать?
 
 
 
#19
09.10.2019 12:18:14
МНК - метод наименьших квадратов

По данных
Оф открытые источники или такой же прогноз

На реальном примере (упрощенном), это выглядит так
На тек месте работы, есть показатели по ЗП сборщиков (сдельная зп, зависит от того, сколько шт соберут)
Изначально, на будущий месяц, известен только плановый Товарооборот
Далее алгоритм следующий
Изучаем ретроспективно План и факт (история. статистика за прошлые периоды). Смотрим, в среднем выполняют или недовыполняют, изучаем показатели, которые могут влиять на выполнение плана.
Далее, прогнозируем, % выполнения плана исходя из полученных данных (с учетом коридора отклонения)
Далее берем историю по кол-ву собранных шт, выводим прогнозное значение (сколько руб стоит 1 шт) и проводим интерполяцию на предполагаемый товарооборот (т.е. делим предпл ТО на предпл стоим 1 шт) - получаем прогнозное кол-во шт.
Затем умножаем кол-во шт полученных на цену сборки (цена за собранную 1 шт одним сотрудником) - получаем прогнозную сумму
Спустя месяц, когда появились реальные фактические цифры по прогнозируемому месяцу, проводим план факт, на предмет отклонений. Уточняем, какие из показателей отклонились и по какой причине.

По поводу источников данных, вот открытые данные по темепратуре по Москве. Скорее всего, есть подобные источники с температурой и по другим городам.
http://weatherarchive.ru/Pogoda/Moscow

Это как пример. В Вашем случае, понятно алгоритм будет другой, но сама логика, возможно, применима и к Вашей орг.
Пробуйте доставать самые различные данные и играться с ними. Крутить и вертеть. Рано или поздно, у Вас получится создать подходящую под требования, модель прогноза.
Но повторюсь, не всегда это может получится силами Экселя (если планируете копать действительно глубоко). Варианты, что выкладывал выше, - это простые расчеты, по стандартным формулам эксель. Если нужен более точный результат, на это может уйти больше Ваших сил и времени. Все должно быть соизмеримо=)
C2-C4
 
 
 
Страницы: 1
Наверх