В этой статье представлено пошаговое руководство о том, как выполнять простое, но эффективное прогнозирование в Microsoft Excel с использованием модели линейной регрессии.

Мы рассмотрим три варианта модели и сравним результаты на конкретном одномерном наборе данных — ежемесячные продажи продукта за 5 лет. Будут три варианта модели;

  • Оригинальная модель линейной регрессии (LR).
  • Модель LR с сезонностью — определение того, как колеблются продажи в определенные месяцы, и реагирование на эту информацию путем корректировки наших прогнозов (надеюсь, повышение производительности по сравнению с предыдущей моделью).
  • Модель LR с сезонностью иростом — если продажи имеют тенденцию увеличиваться/уменьшаться с течением времени (по мере роста/сокращения бизнеса), то мы также можем учитывать эту информацию о «факторе роста», опять же, мы надеемся улучшить производительность. модели.

Я должен пояснить, что не для каждой модели LR требуются факторы сезонности и роста, чтобы получить наиболее эффективную модель. Аналитик данных / ученый должен наблюдать за данными и искать эти закономерности, прежде чем решить, какой вариант модели применить — в качестве альтернативы вы можете поэкспериментировать со всеми тремя моделями, сравнить и оценить производительность, прежде чем сделать свой выбор. .

Линейная регрессия (LR) — реализация Excel

Чтобы разработать модель LR, мы сначала настроим нашу электронную таблицу, как показано ниже.

  • Крайний левый столбец будет отслеживать наш счетчик периода времени (он всегда должен начинаться с 1 и просто увеличиваться на единицу для каждой строки).
  • Второй столбец предназначен для описания, чтобы помочь нам понять данные (в данном случае сообщая нам, к какому году относится месяц).
  • Третий столбец — это фактический период времени (в этом примере мы используем календарные месяцы, вы можете использовать дни, недели, годы для своей работы). Они должны быть последовательными
  • Четвертый столбец содержит наши фактические данные (то есть то, на чем мы строим нашу модель и на основе чего основываем наши будущие прогнозы). Мы сможем наблюдать сезонность и тенденцию в данных, когда построим график ниже.
  • Пятый, шестой и седьмой столбцы будут содержать прогнозы модели после их построения. Мы также будем использовать эти столбцы для сравнения вариаций прогноза с «фактическими значениями», чтобы оценить, какая из наших трех моделей работает лучше всего в этом конкретном наборе данных.

А вот графические данные за период с 2017 по конец 2021 года.

Мы будем использовать данные о продажах за 2021 год, чтобы оценить эффективность наших трех моделей. Затем наиболее эффективная модель будет делать прогнозы на каждый месяц в 2022 году.

Построение модели

Простая модель линейной регрессии требует трех значений для создания прогноза/прогноза. На изображении ниже;

  • Бета (0) — это точка пересечения, где линия пересекает ось Y.
  • Бета (1) — это наклон — направление и угол, на которые будет указывать линия.
  • X(i) — входные данные, на которых основан прогноз.

В нашей настройке у нас уже есть X(i) — наши входные данные — это список продаж продуктов за каждый месяц с 2017 по 2021 год.

Чтобы получить значения Betas (Intercept и Slope), мы можем использовать встроенные функции Excel с указанными ниже параметрами (наши данные о продажах выделены синим цветом, а индикатор периода — красным).

Затем нам просто нужно воссоздать приведенную выше формулу, используя всю информацию, которую мы теперь получили. Эту формулу необходимо ввести в столбец LR и скопировать для каждой строки/месяца, который мы хотели бы спрогнозировать.

Знаки доллара в приведенной ниже формуле гарантируют, что указанная ячейка не изменится при копировании формулы между ячейками.

Копирование формулы для всех периодов позволяет получить следующее.

Результаты

После первоначального изучения приведенных выше результатов для простой модели LR мы можем быстро сделать вывод, что для этого конкретного набора данных модель вообще не соответствует данным (из-за больших различий между прогнозами и фактическими данными). Ниже представлена ​​визуализация фактических и прогнозных данных вместе с таблицей результатов по данным оценки за 2021 год.

Общая абсолютная разница между фактическими продажами и прогнозируемыми продажами составляет 18 393 продажи. Это невероятно много и делает модель непригодной для использования в бизнес-контексте.

В следующем разделе мы значительно улучшим модель, введя индекс сезонности.

Линейная регрессия с сезонностью (LR S) — реализация Excel

Сезонность в контексте прогнозирования - это когда ряд данных резко колеблется в значениях в зависимости от временных интервалов.

Например, если вы думаете о продажах мороженого в течение года в Великобритании, логически вы ожидаете, что объем продаж будет низким в период с января по март, поскольку температура около 0 градусов по Цельсию, а с апреля по июнь продажи должны увеличиться. по мере того, как температура начнет повышаться, с конца июня до начала сентября, вероятно, будет пик продаж, когда температура достигнет середины конца 20-х годов (если нам повезет!), а затем можно ожидать, что с середины сентября продажи будут снижаться по мере температура тоже снижается. Это один из примеров того, как наша сезонность может повлиять на объемы продаж.

Чтобы включить эту дополнительную информацию в нашу модель, нам сначала нужно создать индекс сезонности. Поскольку наши данные месячные, индекс будет содержать список названий каждого месяца и значение коэффициента (масштабатора) для каждого месяца, на которое будет умножаться исходный прогноз.

Масштабаторы сезонности создаются путем взятия средних продаж для каждой из групп месяцев и последующего деления на средние продажи за данный период — например, среднее значение всех значений за январь (январь 2017 г., январь 2018 г., январь 2019 г.…) разделить на средний объем продаж за период с января 2017 г. по декабрь 2021 г. Это говорит нам о том, насколько месяц «вносит вклад» в прогноз, т. е. должны ли продажи быть намного выше, если прогнозируемый месяц равен X.

Вот столбцы сезонности, которые мы создали, и соответствующая формула.

Если масштаб сезонности очень мал — возьмем, к примеру, ноябрь на уровне 0,16, то модель говорит нам, что объем продаж в этом месяце, как правило, низкий. В качестве альтернативы, если значение велико — февраль на уровне 4,59 — тогда объем продаж в этом месяце, как правило, велик. Мы можем проверить эту логику с фактическими данными о продажах ниже.

(Статистически мы просто умножаем исходный линейный прогноз на масштабатор сезонности, чтобы скорректировать прогнозы)

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

Результаты

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

Вот оценка прогнозов по сравнению с фактическими данными 2021 года.

Абсолютная дисперсия снизилась с ~ 18 000 до ~ 8 000 — ошибка уменьшилась примерно на 10 000.

Линейная регрессия с сезонностью и ростом (LR S G) — реализация Excel

Последний вариант модели, который мы хотели бы изучить, — это добавление фактора «роста» к модели с поправкой на сезонность из предыдущего раздела. «Фактор роста» в этом контексте подразумевает, что бизнес, вероятно, будет либо расширяться, либо контрастировать с течением времени, и поэтому их продажи также должны либо расти, либо снижаться. Ежемесячные данные могут подойти для этой модели, поскольку мы должны четко видеть, как проходят месяцы/годы, растут или падают данные.

Добавить эту информацию в наш прогноз довольно просто — мы перечисляем все месяцы, как мы это делали в предыдущем разделе, а затем для каждой пары последовательных месяцев находим рост/снижение в процентах и ​​берем среднее значение этих месяцев. ценности.

Например, за март у нас есть следующие продажи;

Март 2017 г .: 2122

Март 2018: 1589

Март 2019 г.: 2940

Март 2020: 645

Март 2021: 1855

Процент роста/снижения между мартом 2017 г. и мартом 2018 г. составляет: -25,1178%.

Процент роста/снижения с марта 2018 г. по март 2019 г. составляет: 85,022 %.

Рост/снижение в % с марта 2019 г. по март 2020 г. составляет: -78,0612%.

Процент роста/снижения в период с марта 2020 г. по март 2021 г. составляет: 187,597%.

Среднее значение этих процентов равно 42,36%. Поэтому наш коэффициент роста на март будет равен 0,4236.

Чтобы получить окончательные прогнозы, нам нужно умножить наш прогноз с учетом сезонных колебаний на соответствующий индекс роста, а затем добавить исходный прогноз. Вот список полученных новых значений.

Результаты

Сопоставление всех моделей друг с другом дает нам следующий график

Здесь мы видим, что новая регрессия (зеленая) способна предсказывать пики намного лучше, чем предыдущая регрессия (красная), но не может так же хорошо предсказывать значения минимума.

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

Наш последний регрессор уменьшает дисперсию на 400, что является хорошим улучшением по сравнению с предыдущим. Поэтому мы можем решить использовать это на практике. Вот прогноз продаж на 2022 год для каждой модели.

— — — — — — — —

Нравится этот контент? Пожалуйста, рассмотрите возможность подписаться на меня и поделиться историей! Пожалуйста, оставьте любые вопросы или комментарии ниже, и я свяжусь с вами. Спасибо, хорошего дня :)

Свяжитесь со мной в LinkedIn: https://www.linkedin.com/in/thomas-staite-msc-bsc-ambcs-55474015a/