В современном бизнесе способность заглянуть в будущее и предсказать показатели — бесценное преимущество. Будь то будущий объем продаж, потребность в складских запасах или потребительские тенденции, точный прогноз позволяет принимать обоснованные решения. К счастью, для этого не нужны сложные и дорогие программы. Обычный Microsoft Excel обладает мощным арсеналом инструментов для прогнозирования. В этой статье мы подробно разберем, как осуществляется предсказание значения в Excel, какие функции использовать и как применять их на практике.
Базовые функции для предсказания значения
Не каждый способен разобраться в сложных статистических методах, поэтому Excel предлагает простые и доступные функции, которые позволяют на основании данных из прошлых периодов сформировать прогнозные значения на будущее. Основой для линейного прогнозирования служат статистические функции.
Функция ПРЕДСКАЗ (FORECAST) и ПРЕДСКАЗ.ЛИНЕЙН
Стандартная функция Excel ПРЕДСКАЗ (в новых версиях переименована в ПРЕДСКАЗ.ЛИНЕЙН или FORECAST.LINEAR) относится к категории статистических инструментов. Она вычисляет будущее значение на основе линейного тренда существующих данных.
Синтаксис функции выглядит следующим образом:
ПРЕДСКАЗ.ЛИНЕЙН(x; известные_значения_y; известные_значения_x)
- x — точка данных, для которой вы хотите предсказать значение (например, номер периода в будущем).
- Известные_значения_y — диапазон ячеек с зависимыми значениями (например, прошлые продажи или заказы).
- Известные_значения_x — диапазон ячеек со значениями независимой переменной (например, номера прошлых периодов).
Важно: Если аргумент x не является числом, функция ПРЕДСКАЗ и ПРЕДСКАЗ.ЛИНЕЙН вернут ошибку #ЗНАЧ! (#VALUE!).
Функция ТЕНДЕНЦИЯ
Если требуется предсказать сразу несколько значений, можно использовать функцию ТЕНДЕНЦИЯ. В отличие от ПРЕДСКАЗ, которая вычисляет одно значение, ТЕНДЕНЦИЯ может возвращать массив предсказанных значений. Например, если у вас есть данные по заказам за 2023 год и вы хотите спланировать заказы на 2024 год, захватив еще и ноябрь-декабрь 2023, вы можете применить формулу ТЕНДЕНЦИЯ, где известные_значения_У — это прогнозируемый параметр (заказы).
Учет сезонности: Функция ПРЕДСКАЗ.ETS
Линейный тренд хорошо работает для стабильно растущих или падающих показателей, но в реальности бизнес часто подвержен сезонным колебаниям. Продажи мороженого летом растут, а зимой падают. Для таких случаев существует функция ПРЕДСКАЗ.ETS (FORECAST.ETS).
Она рассчитывает или прогнозирует будущее значение на основе существующих (ретроспективных) данных с учетом экспоненциального сглаживания и сезонности.
Обратите внимание: Функция ПРЕДСКАЗ.ETS недоступна в Excel в Интернете, iOS или Android. Для ее использования потребуется десктопная версия программы.
Методы прогнозирования: Расчет индексов сезонности
Иногда для понимания картины требуется ручной подход к сезонности. Один из популярных методов расчета в Excel выглядит так:
- Считается, сколько суммарно продавалось каждый январь, каждый февраль и т.д. за все анализируемые периоды.
- Это значение делится на среднее значение продаж за все эти периоды.
- Таким образом, мы выясняем, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и т.д..
- Полученные коэффициенты (индексы сезонности) умножаются на линейный прогноз, что делает предсказание значения гораздо более точным.
Визуальное предсказание: «Лист прогноза»
Как по мне, так в Excel 2016 и более новых версиях вообще лучше для прогнозов использовать встроенную возможность «Лист прогноза» в меню «Данные». Это самый наглядный и простой способ.
При использовании этого инструмента в Excel создается новый лист с таблицей, содержащей статистические и предсказанные значения, и диаграммой, на которой они отражены. Вы также можете настроить доверительный интервал и задать параметры сезонности вручную или доверить их определение алгоритму программы.
Предсказание значения в Excel — задача, посильная каждому. От простых линейных прогнозов с помощью функции ПРЕДСКАЗ до сложных моделей с учетом сезонности (ПРЕДСКАЗ.ETS) и автоматизированных визуальных отчетов («Лист прогноза»), Excel закрывает подавляющее большинство бизнес-задач по аналитике. Начните с анализа исторических данных, выберите подходящий метод, и вы сможете с высокой долей вероятности предвидеть будущие тренды вашей компании.