Спрогнозировать бизнес-показатели по методу линейного тренда с учетом сезонности

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

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

Что собой представляет метод линейного тренда?

Думаю, всем знакомо понятие «тренда». Тренд — это закономерность подъема или падения показателя в динамике. Если построить модель, описывающую это явление, то получается довольно простой и очень удобный инструмент для прогнозирования, не требующий каких-либо сложных вычислений и временных затрат на проверку значимости и адекватности влияющих факторов.

Линейная модель тренда — самая простая, интуитивно понятная и часто встречающаяся из всех существующих. Она описывает равномерное изменение показателя во времени. С линейным трендом справится каждый, достаточно лишь уметь пользоваться стандартными формулами 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. Метод, который я описала в статье, прост и удобен в применении. Сложно сделать ошибку и получить далекие от реальности данные.


2. Данный метод не привязан к специализированным программам и ссылается на ваши реальные данные.


3. Для прогноза требуется небольшая статистика. Но помните, чем больше данных, тем точнее прогноз.


4. Данный метод учитывает сезонность бизнеса, как важный фактор влияния на будущие показатели эффективности.


5. Есть возможность самостоятельно корректировать тренд, учитывая возможные сторонние факторы.