Прогнозирование объемов продаж с учетом сезонности. Прогнозирование объемов продаж с учетом сезонности Цель научиться прогнозировать данные с учетом сезонности Инструменты Функции Предсказ
 Скачать 0.78 Mb.
|
|
Прогнозирование объемов продаж с учетом сезонности Цель: научиться прогнозировать данные с учетом сезонности Инструменты: Функции: «Предсказ», «Индекс», «Месяц», «Доверит»; «Стандотклон», «Счёт», абсолютная адресация, массивы, графики, автозаполнение, сценарный анализ. Исходные данные: Объемы сбыта ПРОГНОЗИРОВАНИЕ С ИСПОЛЬЗОВАНИЕМ ФУНКЦИИ «ПРЕДСКАЗ» Прогнозирование объемов сбыта будет осуществляться на основе исходных данных за предыдущие периоды. Для начала осуществим прогноз на основе уже известной Вам функции «Предсказ», а затем усложним прогноз, добавив в модель фактор сезонности. На листе Excel создадим таблицу с исходными данными (можно скопировать данные из таблицы ниже, вставить их начиная с ячейки А1). Прогноз будет тем точнее, чем больше исходных данных удастся собрать: пример включает данные по месяцам за два года и 4 месяца. Таблица 1
В ячейке С30 введем функцию «Предсказ»: =ПРЕДСКАЗ(A30;$B$2:$B$29;$A$2:$A$29). Диапазоны с известными значениями х (даты) и y (объемы продаж) необходимо закрепить, используя клавишу F4, чтобы при копировании функции в ячейки ниже - диапазон исходных данных оставался неизменным. Подробно эта функция разбиралась на предыдущих занятиях.  Рисунок 1 Протяните значения на весь прогнозный период и сравните полученные результаты с рисунком ниже.  Рисунок 2 Визуализируем полученный прогноз на графике. Чтобы на графике не было разрыва в линиях исходных данных и прогнозных, в ячейку С29 вставьте значение исходных данных за апрель 2020 года (=В29). При построении графика выделите диапазон А1:С45.  Рисунок 3 Для прогнозирования значений объемов продаж при помощи функции «Предсказ» используется линейная регрессия, что видно на графике. РАСЧЕТ КОЭФФИЦИЕНТА СЕЗОННОСТИ Для ввода поправки на сезонность столбец F назовем «Коэффициент сезонности». Расчет его будет производиться следующим образом: в ячейке F2 складываем значение объема продаж за январь первого и второго года, делим на сумму объемов продаж за 2 ПОЛНЫХ года и умножаем полученное значение на 12 (количество месяцев в году). Закрепляем значения в знаменателе формулы, используя клавишу F4, и протягиваем формулу до конца первого года:  Рисунок 4 ВВОД В ПРОГНОЗ ПОПРАВКИ НА СЕЗОННОСТЬ Для учета фактора сезонности в нашем прогнозе необходимо умножить полученные прогнозные значения на соответствующие каждому месяцу коэффициенты сезонности. С этой целью будет использоваться функции ИНДЕКС и МЕСЯЦ. Функция Индекс – возвращает значение, соответствующее номеру строки или столбца в выделенном диапазоне. Первый аргумент – диапазон ячеек, второй аргумент – номер строки в выделенном диапазоне, который нужно взять. Функция Месяц – возвращает номер месяца от 1 (январь) до 12 (декабрь) в выделенном диапазоне с датами. Аргумент один – дата в числовом формате. Введем корректировки в формулу, находящуюся в ячейке С30 для первого прогнозного значения. Умножим имеющуюся в ячейке формулу на функцию «Индекс». Первый аргумент функции – диапазон с данными коэффициента сезонности (F2:F13). Второй аргумент должен быть номер строки из этого диапазона, на цифру из которой будем умножать объемы сбыта. Так как наш прогноз начинается с мая, то в качестве аргумента необходимо указать номер 5 (номер месяца май по порядку), однако при протягивании формулы на последующие прогнозные значения придется вводить номера строк вручную. Чтобы избежать этого, в качестве второго аргумента введем функцию «Месяц», ее аргумент – дата, для которой осуществляется прогноз (Ячейка А30). Функция «Месяц» укажет номер месяца по порядку (от 1 до 12). Закрепите диапазон F2:F13 при помощи клавиши F4, протяните формулу на сеть прогнозные период и сравните полученные результаты:  Рисунок 5 Оцените изменения прогнозов на графике. СЦЕНАРНЫЙ АНАЛИЗ Сценарный анализ применяется для прогнозов данных, обладающих высоким уровнем неопределенности. К таковым относится и прогноз объемов продаж. В примере рассмотрим помимо наиболее вероятного, реализацию еще двух сценариев: оптимистичного и пессимистичного. Сначала рассчитаем возможное отклонение от полученных значений объема продаж. Для этого будем предполагать, что данные подчиняются нормальному распределению, а это значит, что согласно правилу 3 сигма 95% распределения выборочных средних находится в пределах 1,96 стандартных отклонений. На основании данных прогноза рассчитаем доверительный интервал значений объемов сбыта, в пределах которого с доверительной вероятностью 95% находится среднее генеральной совокупности. Доверительный интервал — это такой интервал, построенный с помощью случайной выборки из распределения с неизвестным параметром, который содержит данный параметр с заданной вероятностью. Столбец G назовем «Отклонение, тыс. руб.». В ячейку G2 вставим функцию «Доверит». Функция Доверит (альфа;стандартное_откл;размер) - возвращает доверительный интервал для среднего генеральной совокупности с нормальным распределением. Аргументы функции: Альфа — уровень значимости, используемый для вычисления доверительного уровня. Доверительный уровень равен 100*(1 - альфа) процентам или, значение аргумента "альфа", равное 0,05, означает 95-процентный доверительный уровень. Стандартное_откл — стандартное отклонение генеральной совокупности для диапазона данных, предполагается известным. Размер — Размер выборки1. В нашем примере в ячейку G2 вводим функцию «Доверит». Первый аргумент – 0,05, то есть с доверительной вероятностью 95% в этом интервале находится среднее генеральной совокупности. Второй аргумент - стандартное отклонение для диапазона данных. Чтобы не рассчитывать его вручную, вместо этого аргумента введем функцию «Стандотклон», а в качестве аргумента укажем диапазон (С29:С45). Третий аргумент – размер выборки: чтобы не считать количество наблюдений вручную, вместо третьего аргумента введем функцию «Счёт», а в качестве аргумента укажем диапазон (С29:С45).  Рисунок 6 Полученное значение 19,1 тыс. руб. можно интерпретировать так: с вероятностью 95% значение объемов сбыта может колебаться в диапазоне 19,1 тыс. руб. В ячейку D30 вставим формулу для расчета объема сбыта при реализации пессимистичного сценария: из объемов сбыта по наиболее вероятному сценарию (ячейка С30) вычтем отклонение (ячейка G2), Ячейку G2 нужно закрепить и скопировать формулу на весь диапазон ячеек с прогнозом. Аналогичным образом рассчитать значения объемов сбыта для оптимистичного сценария – к объемам по наиболее вероятному сценарию добавить отклонение. Оцените результаты:  Рисунок 7 Визуализируем полученные прогнозы на графике. Чтобы на графике не было разрыва в линиях исходных данных и прогнозных, в ячейки D29 и Е29 вставьте значение исходных данных за апрель 2020 года (=В29). При построении графика выделите диапазон А1:Е45.  Рисунок 82 1 https://support.office.com/ru-ru/article/%D0%A4%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%94%D0%9E%D0%92%D0%95%D0%A0%D0%98%D0%A2-75ccc007-f77c-4343-bc14-673642091ad6 2Материал подготовлен на основе https://www.youtube.com/watch?v=r7acmsUU7ek |