Bootstrap метод - прогноз нерегулярных, редких продаж

В этой статье вы узнаете о модели прогнозирования нерегулярных, редких продаж Bootstrapping.

В статье мы по шагам рассмотрим, как рассчитать прогноз продаж по методу Bootstrapping в Excel.

Суть модели на примере:

Продажи у нас по месяцам, в году 12 месяцев, поэтому из ряда мы случайным образом возьмем 12 значений и сделаем это 10 000 раз. Далее по каждому из 10 000 рядов со случайными значениями в выборке рассчитаем среднее значение каждого ряда, и по средним значениям каждого ряда рассчитаем еще раз среднее, получим ср. месячные продажи.

Пример:

Скачайте Excel - файл с примером расчета здесь 

Возьмем продажи запчастей в штуках, и по шагам рассмотрим расчет в Excel:

1. Присвоим имя диапазону с продажами;
2. Введем формулу для выборки случайных значений из диапазона продаж;
3. Сделаем 10 000 рядов с 12 случайными значениями;
4. Рассчитаем среднее для каждого ряда;
5. Рассчитаем среднее по средним.
6. Сделаем прогноз на 3 месяца.

1. Присвоим имя диапазону с объемом продаж.

Для этого:

• Выделим ряд с объемом продаж;
• Нажимаем правую кнопку мыши
• Выбираем пункт «Присвоить имя…»

Назовем диапазон «продажи», жмем «ОК»!

2. Введем формулу для выборки случайных значений из массива «продажи»:

Для создания случайной выборки вводим формулу:

=ИНДЕКС(продажи;ЧСТРОК(продажи)*СЛЧИС()+1;ЧИСЛСТОЛБ(продажи)*СЛЧИС()+1),
где

• =ИНДЕКС() – формула для возврата объема продаж из нашего ряда «продажи»;
• «продажи» – именованный диапазон с продажами;
• ЧСТРОК(продажи) – считает количество строк в массиве «продажи»
• ЧИСЛСТОЛБ(продажи) – возвращает количество столбцов в массиве «продажи»
• СЛЧИС() – формула для генерации случайного числа от 0 до 1

3. Создаем выборку из 12 значений и 10 000 рядов:

Протягиваем формулу на 12 столбцов

И на 10 000 рядов (пронумеруем строки до 10 000 и нажмем 2 раза левой кнопкой мыши на квадратик правом нижнем углу):

Получаем таблицу со случайной выборкой 12 периодов 10 000 раз:

4. Рассчитываем среднее для каждого ряда:

 5. Рассчитываем среднее по средним:

 6. Рассчитаем прогноз на 3 месяца.

Среднее по средним умножим на 3, получаем прогноз на 3 месяца:

Скачайте Excel - файл с примером расчета здесь 

Готово!

Данный метод подходит для нормального распределения, но фактическое может значительно отличаться, а может не отличаться.  Как нам его оценить в Excel быстро, без графиков, с границами…? - рассмотрим в следующей статье – “Анализ нормального распределения в Excel – Bootstrapping – 2».

Программа для прогнозирования Forecast4AC PRO (начиная с версии 4.7) умеет автоматически выбирать данную модель для временных рядов с редкими продажами.