Bootstrapping 2 - анализ нормального распределения в Excel

Ранее мы рассмотрели, как рассчитать прогноз редких продаж в Excel по методу Bootstrap подробности вы можете прочитать в статье по этой ссылке. Прогноз мы рассчитали с учетом того, что распределение нормально.

В данной статье мы выделим фактическое распределение и за счет этого повысим точность прогноза.

Сегодня вы узнаете, как в Excel быстро проанализировать распределение и уточнить прогноз.

---

Итак, в предыдущей статье мы:

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

Теперь, в продолжение предыдущих шагов, для увеличения точности прогноза проанализируем фактическое распределение и скорректируем прогноз.
Для это:

• Выделим среднее значение по 10 000 рядам выборки;
• Построим сводную таблицу по выделенным средним;
• Сгруппируем ряд средних;
• Выведем в сводную таблицу по группам следующие показатели: количество, среднее, сумму, максимум, минимум.
• Рассчитаем прогноз на 6 месяцев.

 

Далее ставим галочку «на существующий лист» и нажимаем «ОК»

3. Сгруппируем ряд средних.

Для этого перетянем средние в область «Строк» сводной таблицы:

Нажимаем правой кнопкой мыши на сводной таблице в столбце со средними и выберем пункт «Группировать»

В появившемся окне мы можем задать параметры группировки – с какого по какое значение группировать с каким шагом. Берем значения по умолчанию и нажимаем «ОК»:

Получаем такую табличку с диапазонами средних:

4. Выводим в сводную таблицу в сгруппированные диапазоны следующие показатели: количество, среднее, сумму, максимум, минимум.

Перетаскиваем среднее 5 раз в область значений сводной таблицы:

Задаем показатели (количество, среднее, сумму, максимум, минимум) в «параметрах полей значений» 

Выбираем нужный показатель для расчета по каждому полю - количество, среднее, сумму, максимум, минимум. 

Получаем следующую таблицу:

Видим, что 5 375 рядов в диапазоне от 1-2:

Сделаем еще раз группировку, только шаг поставим 2

Видим, что 7 379 рядов из 10 000 в диапазоне от 0-2 – 73% наблюдений. И так мы от нормального распределения перешли к фактическому и опираясь на него сделаем прогноз.

Теперь мы получили 96% наблюдений в диапазоне от 0 до 3.

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

Опираясь на среднее этой группы, уточним прогноз, который рассчитали в статье «Bootstrap метод - прогноз нерегулярных продаж».

Среднее значение ряда по группе с 96% наблюдений у нас получился 1,49, умножим на 6 месяцев, получаем уточненный прогноз на 6 месяцев 8,96, т.е. можем планировать, что нам понадобится 9 шт. данного товара:

Столбцы «Максимум» и «Минимум» — границы прогноза.

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

Точных вам прогнозов!

Программа Forecast4AC PRO (начиная с версии 4.7) умеет автоматически использовать модель Bootstrap при расчете прогноза на больших массивах данных! Если один из рядов с нерегулярными продажами, программа это понимает и строит прогноз, используя метод Bootstrap!