Простые методы прогнозирования спроса : Математика и методы анализа товарных ресурсов

Петр, так это не сложно сделать и самостоятельно.
Я консолидировал все формулы Валеры в одну мегаформулу (см. лист Мегаформула Валерия Разгуляева). Так же я добавил ее в список формул Шрайбфедера, чтобы можно было делать прогноз дополнительно при помощи методы Валеры, выбирая способ с наименьшей ошибкой.
Есть тонкость: если планируете делать прогноз на несколько периодов вперед, то формулу Валеры в своем проекте надо вводить в самую последнюю ячейку прогноза и протягивать ее к первой ячейке, иначе будет ошибка #ССЫЛКА! Эта ошибка внутри формулы будет в любом случае, но если протягивать формулу из конца в начало, то формула будет вычислять и показывать результат, а если наоборот - не будет.
В общем, при помощи этих формул можно уверенно строить прогноз в Excel, имея более менее адекватную историю спроса.

Вложения

Формулы_прогнозирования_Inventor.rar (167.7 Кб, 1047 просмотров)

Андрей, привет.
Посмотрел файл, есть вопросы:
1. Почему ошибку прогноза в данном файле ты оцениваешь именно так. До этого думал, что ошибка прогноза = (План-факт)/факт*100% (цифра по модулю). Имеется ли какой-то здесь замысел: экономический или математический? Кстати, в книжке Шрайбфедера тоже не нашел объяснения.
2. Понравилось решение вычисление в ошибке прогноза: медиана, среднее и мода. Имеется ли какой-то здесь замысел: экономический или математический?

Понимаю, что могу ошибаться в этих моментах, но не могу использовать то, что не понимаю.

Вы можете с помощью функций Excel "Вырезать" и "Вставить" организовать любое положение ячеек - все формулы при этом сохраняться и будут ссылаться, куда надо. ;) А текущее расположение было сделано, чтобы было проще понять как работают формулы, и зашить их к себе в информационную систему или свой файлик Excel, что и сделал Андрей - а сами-то формулы - простейшие: сложить, умножить, разделить...

Договаривались же, что предупредишь, чтобы я мог везде рекламировать твою надстройку ещё и как инструмент для вычисления прогноза по моему методу. В первую очередь - вставлю эту информацию к себе в статью и этот файлик Excel с примером - только мне нужна корректная ссылка. Можно ссылаться на основную надстройку "Инвентор" или именно эту, которую ты привёл здесь?

А чему будет равна ошибка, если план будет равен единице, а факт - нулю? И почему ошибка будет другой, если план равен нулю, а факт - единице? Я использую для оценки такую формулу: (факт - план) / (факт + план). Такая оценка будет одинаковой в обе стороны ошибки, и никогда не будет больше 100% и никогда не будет деления на ноль: если и план и факт - равны нулю, то ошибка - тоже 0%.

Привет, Вадим.
1. В общем случае ошибку считают так как ты указал, только в знаменателе не факт, а план, т.к. отклонение считается к плану. У Шрайбфедера формула немного другая, он обосновывает это так: если мы запланировали 100, в продали 90, то эта ошибка такая же как если мы запланировали 90, а продали 100. Поэтому в знаменатель ставится минимум из плана и факта. Лично я не вижу в этом противоречия здравому смыслу, считаю что это можно использовать. Да, формула не такая как пишут в книгах, но формула Валеры еще более необычная ;-) При этом все эти формулы работают, главное выбрать какую-то одну и сравнивать все модели по одной формуле, а не по разным.
2. Я увидел, что по одному из средних показателей не всегда удается выбрать действительно лучшую формулу, а вот по среднему из трех средних - это можно сделать почти всегда. С математической точки зрения средняя из средних особого значения не имеет (по крайней мере, мне не известно название такого показателя), но и ошибкой не является, т.к. мы усредняем несколько средних значений и имеем на это право. Опять же противоречия здравому смыслу не вижу.
Вопрос интересный, спасибо.

Валера, привет. Я помню про нашу договоренность, у меня твоя задача одна из первых в списке доработок, вот только надстройку пока обновлять не собираюсь - никак до этого не доберусь, все задачи надо решать наскоком, а столько времени разом выделить пока не могу. На самом деле, мне повезло в том, что удалось сделать программу без единой ошибки (это реально круто), благодаря этому я имею возможность не обновлять надстройку до появления свободного времени.
В данном случае, я всего лишь сделал из нескольких твоих формул одну мегаформулу стандартными средствами Excel. В принципе ее и так можно использовать, но удобнее будет сделать для нее оболочку в VBA, которая повысит удобство и минимизирует риск ошибок. Так же отображение на динамическом графике совместно с другими формулами прогноза повысит наглядность и удобство.
Т.е. если ссылаться, то пока только на этот файл, т.к. в самом Inventor еще данной формулы нет, но это в большей степени "бантики", т.к. функционал формулы можно использовать и в том виде, в котором я ее сейчас выложил в данном файле. Плюс еще в том, что при помощи него можно оценить какую формулу лучше применять к конкретным данным. В общем, файлик получился довольно интересный, он хорошо дополняет модуль прогноза Inventor.

Интересный метод, спасибо. Числитель без модуля используешь? В принципе это удобно, видишь сколько раз в одну сторону отклонился, сколько - в другую. А при подсчете средней ошибки можно уже и модуль использовать при необходимости.

Валерий, Спасибо! Я просто пытался копировать/вставить - это не дало результата.
Я понял смысл методики так:
*мы находим долю (или среднюю долю, если данные за несколько лет), которую занимал месяц в сумме продаж всех месяцев, идущих до него в прошлые годы (это учет сезонности). Например, строя прогноз на Июнь нужно посмотреть какую долю в прошлые годы занимал Июнь в сумме продаж с Января по Май.
*умножаем сумму продаж имеющихся месяцев на результат первого действия и получаем прогноз (это учет тренда, если в этом году продаем больше и прогноз больше, если меньше - прогноз меньше). В нашем примере умножаем сумму продаж с Января по Май этого года на коэффициент из первого пункта.
Очень здорово получилось представить все расчеты в виде одной формулы в 8 строк, можно вести расчет в эксель сразу по большому количеству позиций. Ваша методика хорошо работает на товарах с большим жизненным циклом и статистикой за длительный срок.

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

Андрей, привет!
1. Затупил с формулой. Спасибо за ответ, прочитал материал Шрайбфедера и там есть объяснение как раз этого вопроса.
2. Спасибо, будем вооружаться твоими наработками. :)
3. Валера, спасибо также.

Валерий, у меня появился вопрос: почему у вас каждый блок формул рассчитан на 13 месяцев, а не на 12? В первом случае понятно - у нас нет статистики и мы вынуждены отталкиваться не от 12 месяцев, а от 13. Но потом все формулы сгруппированы по 13 месяцев (26,39,52,65), причем в каждой следующей формуле используется статистика на 1 месяц больше, чем в прошлой формуле, какой в этом замысел?

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