Как спрогнозировать бизнес-показатели по методу линейного тренда с учетом сезонности: пошаговый мануал
Содержание статьи
В любом бизнесе возникает необходимость прогнозирования ключевых показателей деятельности: объема продаж, ожидаемой прибыли, количества лидов с формы заказа звонка или посетителей магазина. В данной статье я описала простой и быстрый способ прогнозирования таких показателей, на основе минимальных данных за предыдущие периоды.
Прежде чем приступить к описанию метода, необходимо понять суть прогнозирования. Прогноз — это не точное значение показателя в будущем, это определенная планка, которую нужно достичь. Или, в случае если прогноз неутешительный, толчок к поиску новых решений для избежания негативной тенденции. Недостаточно просто рассчитать будущие показатели и ждать той самой цифры, прогноз — это фундамент для постановки целей и, пожалуй, единственный способ (помимо гадания и вангования) избежать неопределенности в будущем.
📉Определение тренда | Первым шагом в построении модели является выбор линии тренда. |
📈 Определение величин сезонной компоненты: | Необходимо учитывать также ошибки сезонных колебаний. |
📉 Расчет ошибок модели: | Ошибки рассчитываются по формуле E=F-T-S. |
📈 Построение прогноза: | Переходим на этап прогнозирования. |
Что собой представляет метод линейного тренда?
Думаю, всем знакомо понятие «тренда». Тренд — это закономерность подъема или падения показателя в динамике. Если построить модель, описывающую это явление, то получается довольно простой и очень удобный инструмент для прогнозирования, не требующий каких-либо сложных вычислений и временных затрат на проверку значимости и адекватности влияющих факторов.
Линейная модель тренда — самая простая, интуитивно понятная и часто встречающаяся из всех существующих. Она описывает равномерное изменение показателя во времени. С линейным трендом справится каждый, достаточно лишь уметь пользоваться стандартными формулами Excel.
Уравнение линейного тренда имеет такой вид — y(x)=a+bx, где:
- y — это последовательность значений, которые мы анализируем (например, продажи по месяцам);
- x — номер периода (порядковый номер месяца);
- a – точка пересечения с осью y на графике (минимальный уровень);
- b – это значение, на которое увеличивается следующее значение временного ряда.
В данной статье рассмотрим разные способы расчета линейного тренда с помощью встроенных excel-функций. А также учтем индекс сезонности при расчете прогнозных значений.
Для наглядности предлагаю сразу перейти в Excel или в Google Spreadsheets, функционала которых, для данного метода, более, чем достаточно.
Способ 1. Расчет линейного тренда с помощью функции ЛИНЕЙН
Наиболее наглядный метод, чтобы разобраться в механике расчета.
Шаг 1. Представим исходные данные в виде простой таблицы
Справа добавим столбцы для расчета значения тренда и индекса сезонности, выбранного в качестве дополнительного фактора влияния.
Шаг 2. Рассчитываем значение линейного тренда — y(x)=a+bx
Из таблицы выше:
- у — это объем продаж за предыдущий год;
- х — № периода.
Найдем значения параметров а и b с помощью функции ЛИНЕЙН, где первый параметр функции — диапазон значений y, второй — диапазон значений x. В третий и четвертый параметры вносим 1 и 0 соответственно:
Примечание для тех, кто работает в Exсel: чтобы рассчитать сразу 2 коэффициента линейного тренда (a) и (b), необходимо установить курсор в ячейку с формулой, выделить соседнюю справа и нажать клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД. Google Таблицы справляются сами по умолчанию.
Шаг 3. Для каждого периода рассчитываем значение линейного тренда
В известное уравнение y(x)=a+bx подставляем рассчитанные коэффициенты:
Шаг 4. Определяем индекс сезонности для каждого месяца (отношение продаж месяца к средней величине)
Фактически, нужно каждый объем продаж за месяц разделить на средний объем продаж за год:
Мы подготовили все необходимые данные для составления прогноза.
Шаг 5. Создаем таблицу для прогнозных значений на следующий год и рассчитываем значения тренда для них
Последовательность нумерации периодов продолжаем:
Шаг 6. На основе полученных данных, составляем прогноз по продажам на следующий год с учетом сезонности
Готово!
Способ 2. Быстрый расчет прогноза, с помощью функций ТЕНДЕНЦИЯ и ПРЕДСКАЗ
Если вам показалось, что расчет первым способ долгий, вы можете воспользоваться функциями ТЕНДЕНЦИЯ и ПРЕДСКАЗ, которые моментально рассчитают значения тренда для будущих периодов.
Примечание. С функцией ЛИНЕЙН мы познакомились не зря, дальше в этом убедитесь.
Шаг 1. Рассчитываем значения линейного тренда с помощью стандартной функции ТЕНДЕНЦИЯ
Параметры функции:
- известные значения y — это объемы продаж за анализируемый период;
- известные значения x — это номера периодов x для известных значений объемов продаж y;
- новые значения x — это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
- константа — ставим 1. Необходимо для того, чтобы значения тренда рассчитывались с учетом коэффициента (a) для линейного тренда y=a+bx.
Примечание для тех, кто работает в Excel. Чтобы рассчитать значения тренда для всего временного диапазона, выделяем диапазон ячеек равный диапазону с новыми значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.
Шаг 2. Рассчитываем значения линейного тренда с помощью стандартной функции ПРЕДСКАЗ
Параметры функции:
- значение x — номер периода, для которого рассчитываем значение тренда;
- известные значения y — объемы продаж за анализируемый период;
- известные значения x — это номера периодов x для известных значений объемов продаж y.
В данном случае формулу необходимо протянуть на все периоды, для которых нужно рассчитать значение.
Как можно исправить прогнозные значения тренда?
Иногда возникает необходимость внести коррективы в прогнозные значения. Такое случается, например, если вас не устраивает прогнозируемый рост и при этом вы понимаете, что есть факторы, которые на него повлияют (рекламная кампания, расширение сети сбыта и т.п.).
Скорректировать будущие значения тренда — возможно. Сделать это достаточно просто, на основе расчета первым способом, — с помощью функции ЛИНЕЙН.
Мы можем влиять на параметры a и b линейного уравнения y = a + bx, тем самым изменяя значения:
- если изменяем значение «a» линейного тренда, то меняется уровень тренда (поднимается параллельно);
- если изменяем значение «b» линейного тренда, то увеличиваем наклон тренда;
Таким образом, мы можем изменять наклон и уровень тренда, как в отдельности, так и одновременно (подробнее в примере):
Выводы
- Метод, который я описала в статье, прост и удобен в применении. Сложно сделать ошибку и получить далекие от реальности данные.
- Данный метод не привязан к специализированным программам и ссылается на ваши реальные данные.
- Для прогноза требуется небольшая статистика. Но помните, чем больше данных, тем точнее прогноз.
- Данный метод учитывает сезонность бизнеса, как важный фактор влияния на будущие показатели эффективности.
- Есть возможность самостоятельно корректировать тренд, учитывая возможные сторонние факторы.
Часто задаваемые вопросы о прогнозировании бизнес-показателей:
1️⃣ Как рассчитать линию тренда?
Уравнение линейного тренда excel y=ax+b, где y — это объёмы продаж, а x — месяцы.
2️⃣ Как рассчитать прогноз продаж по формуле?
Чтобы рассчитать линейный прогноз продаж на один месяц, необходимо среднее количество продаж за три месяца умножить на средний коэффициент сезонности к трем месяцам.
3️⃣ Что показывает уравнение тренда, формула?
Уравнение линии тренда, формула линейного тренда, которая находит линию, соответствующей точкам данных. Величина достоверности аппроксимации (R^2) измеряет надежность линии тренда. Чем ближе R2 к 1, тем лучше линия тренда соответствует данным.
4️⃣ Как добавить среднее значение на график?
Если вам нужно добавить горизонтальную среднюю линию к столбчатой диаграмме в Excel, обычно вам нужно добавить столбец среднего значения к исходным данным, затем добавить ряд данных средних значений в диаграмму, а затем изменить тип диаграммы для нового добавленного ряд данных.
Популярные запросы: как посчитать тренд, линейный прогноз, линейный тренд excel, линейный тренд формула, линейный прогноз это, как рассчитать линию тренда