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

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

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

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


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

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

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

  • Выделим среднее значение по 10 000 рядам выборки;
  • Построим сводную таблицу по выделенным средним;
  • Сгруппируем ряд средних;
  • Выведем в сводную таблицу по группам следующие показатели: количество, среднее, сумму, максимум, минимум.
  • Рассчитаем прогноз на 6 месяцев.
  1. Выделим среднее значение по 10 000 рядам выборки. Для этого устанавливаете курсор в заголовок «Среднее», и нажимаете Ctrl+Shift+стрелочку вниз:
нормальное распределение
2. Добавляем сводную таблицу
 Меню «Вставка» - «Сводная таблица»:

нормальное распределение в Excel

 

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

таблица нормального распределения

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

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

таблица в Excel нормальное распределение

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

фактический метод распределения

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

фактический метод распределения

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

нормальное распределение

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

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

таблица нормального распределения

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

фактический метод распределения

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

нормальное распределение расчет

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

нормальное распределение Excel

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

фактический метод распределения

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

фактический метод распределение

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

Группировка в Excel для анализа распределения

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

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

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

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

прогноз по методу бутстрапинга и с анализом фактического распределения

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

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

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

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

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Добавить комментарий