Как сделать прогноз в excel

Обновлено: 03.07.2024

Каким в ближайшее время станет объем продаж? Упадет ли трафик или же внезапно вырастет? Чтобы ответить на эти вопросы, аналитиком быть не обязательно. Ниже вы узнаете, что нужно делать.

Способ прогнозирования, о котором пойдет речь, занимает максимум 30 минут. Этим отчетом удобно пользоваться в различных случаях.

Примеры использования

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

Давайте посмотрим на простой пример — прогноз по продажам. Можно пользоваться Google Таблицами, однако и простой Excel тоже вполне подойдет.

Шаг №1 — собираем данные

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

Нужно создать Таблицу в Google и указать необходимые данные за прошедшие, к примеру, 20 месяцев. В нашем случае в первую очередь мы выставляем доход:

Шаг №2 — пишем формулу Forecast

В русской локализации эта формула называется “Предзаказом”. Её можно определить по предполагаемому значению Y, если исходить из имеющегося параметра X, где:

X — точка, для которой требуется сделать прогноз;
известное Y — сумма продаж за прошедший период;
известное X — дата этих периодов.

К примеру, требуется выяснить, какими окажутся продажи в следующие 4 месяца. Для это в Таблицу нужно добавить такое же количество строчек и указать порядковые номера месяцев. Там, где прогнозируется доход, необходимо вставить формулу:

Round — способно округлить полученное значение;
Forecast — сам прогноз;
A22 — ячейка, для которой делается прогноз.

$B$2:$B$21 — известные данные от переменной Y;
$A$2:$A$21 — диапазон дат за прошлые месяцы.

Шаг №3 — делаем график

Сначала нужно зайти во вкладку “Вставка”. После этого:

перейдите в раздел “Диаграмма”;
пройдите в “Настройки”;
в качестве типа визуализации выберите “График”.

После этого в настройках зайдите в “Дополнительные”, а оттуда — в “Серии”. Поставьте галочку рядом с линиями тренда. Вы увидите линию, которая будет выступать продажами. На нижнем графике заметно, что продажи скорее всего обвалятся.

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

Итоги

Такой отчет можно составить, не обладая даже должными навыками по аналитике. Так вы можете понять, есть ли какие-то риски.

Функция ПРЕДСКАЗ в Excel позволяет с некоторой степенью точности предсказать будущие значения на основе существующих числовых значений, и возвращает соответствующие величины. Например, некоторый объект характеризуется свойством, значение которого изменяется с течением времени. Такие изменения могут быть зафиксированы опытным путем, в результате чего будет составлена таблица известных значений x и соответствующих им значений y, где x – единица измерения времени, а y – количественная характеристика свойства. С помощью функции ПРЕДСКАЗ можно предположить последующие значения y для новых значений x.

Примеры использования функции ПРЕДСКАЗ в Excel

Функция ПРЕДСКАЗ использует метод линейной регрессии, а ее уравнение имеет вид y=ax+b, где:

Коэффициент a рассчитывается как Yср.-bXср. (Yср. и Xср. – среднее арифметическое чисел из выборок известных значений y и x соответственно).
Коэффициент b определяется по формуле:

Пример 1. В таблице приведены данные о ценах на бензин за 23 дня текущего месяца. Согласно прогнозам специалистов, средняя стоимость 1 л бензина в текущем месяце не превысит 41,5 рубля. Спрогнозировать стоимость бензина на оставшиеся дни месяца, сравнить рассчитанное среднее значение с предсказанным специалистами.

Вид исходной таблицы данных:

Чтобы определить предполагаемую стоимость бензина на оставшиеся дни используем следующую функцию (как формулу массива):

A26:A33 – диапазон ячеек с номерами дней месяца, для которых данные о стоимости бензина еще не определены;
B3:B25 – диапазон ячеек, содержащих данные о стоимости бензина за последние 23 дня;
A3:A25 – диапазон ячеек с номерами дней, для которых уже известна стоимость бензина.

Рассчитаем среднюю стоимость 1 л бензина на основании имеющихся и расчетных данных с помощью функции:

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

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

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

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

Для сравнения, произведем расчет с использованием функции линейного тренда:

И для визуального сравнительного анализа построим простой график.

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

Прогнозирование будущих значений в Excel по условию

Пример 3. В таблице Excel указаны значения независимой и зависимой переменных. Некоторые значения зависимой переменной указаны в виде отрицательных чисел. Спрогнозировать несколько последующих значений зависимой переменной, исключив из расчетов отрицательные числа.

Вид таблицы данных:

Для расчета будущих значений Y без учета отрицательных значений (-5, -20 и -35) используем формулу:

Excel, как универсальный табличный редактор, давно и неплохо справляется с большинством задач прогнозирования (см. список литературы в конце заметки). Однако, не всегда вычисления в Excel являются простыми и понятными. И вот в версии 2016 года разработчики Microsoft добавили семейство функций ПРЕДСКАЗ (FORECAST), которые позволяют в несколько кликов решать большой круг задач прогнозирования на основе экспоненциального сглаживания.

Рис. 1. Прогнозирование продаж в Excel с помощью семейства функций ПРЕДСКАЗ

Об экспоненциальном сглаживании

Экспоненциальное сглаживание также известно, как метод ETS: ошибки (Errors), тренд (Trend), сезонный фактор (Seasonal). Для составления прогноза используются все исторические данные, но коэффициенты, определяющие вклад, убывают в прошлое по экспоненте (отсюда и название). Это позволяет, с одной стороны, чутко реагировать на свежие данных, с другой стороны, сохранять информацию об историческом поведении всего временного ряда. Если данным присущ тренд, он вычисляется в каждой точке данных (а не на основе регрессии всего временного ряда). Наконец, с помощью автокорреляции в данных выявляется сезонность.

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

Собственно, оптимизируются три коэффициента:

α – разброс относительно среднего

Разработчики Microsoft не предоставили пользователям возможность влиять на выбор коэффициентов, за исключением периода сезонности (об этом ниже).

Обзор функций семейства ПРЕДСКАЗ

В Excel представлено 5 функций:

Рис. 2. Семейство функций ПРЕДСКАЗ в Excel

ПРЕДСКАЗ.ETS рассчитывает будущее значение на основе существующих (ретроспективных) данных методом экспоненциального сглаживания. Т.е., дает прогноз одним числом.

ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ возвращает доверительный интервал для прогнозной величины. Доверительный интервал следует отложить по обе стороны от среднего значения. Вместе с ПРЕДСКАЗ.ETS позволяет построить «коридор» прогноза.

ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ возвращает длину повторяющегося фрагмента, обнаруженного в заданном временном ряду. Например, 12, если исторические данные представляют из себя продажи за месяц.

ПРЕДСКАЗ.ETS.СТАТ возвращает восемь статистических значений, являющихся результатом прогнозирования временного ряда. Вряд ли вы будете использовать эту функцию. Она нужна для более тонкого исследования параметров прогнозной модели.

ПРЕДСКАЗ.ЛИНЕЙН вычисляет будущее значение с помощью линейной регрессии исторических данных. До версии 2016 в Excel вместо семейства функций была единственная функция ПРЕДСКАЗ, которая работала также, как и ПРЕДСКАЗ.ЛИНЕЙН. Функция ПРЕДСКАЗ оставлена для обратной совместимости, но скоро перестанет поддерживаться. Далее в заметке ПРЕДСКАЗ.ЛИНЕЙН не рассматривается, так как не относится к функциям, использующим алгоритм экспоненциального сглаживания.

ПРЕДСКАЗ.ETS

В качестве примера рассмотрим месячный пассажиропоток в аэропорту (пример от MS). Исторические данные были собраны за период с января 2009 по декабрь 2912 г.

Рис. 3. Исторические данные

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

Рис. 4. Прогнозные значения на основе функции ПРЕДСКАЗ.ETS

Подробнее о формуле в ячейке С50:

Первый аргумент – целевая_дата = А50 – янв.13, т.е., в ячейке С50 ищется прогноз пассажиропотока для января 2013 г. Ссылка относительная, что позволит при протягивании функции вниз по столбцу ссылаться на новое значение: в С51 – на А51, в С52 – на А52 и т.д.

Второй аргумент – значения = $B$2:$B$49. Здесь расположены исторические данные пассажиропотока. Ссылка абсолютная, чтобы при протягивании формулы ячейки, на которые ссылаются не изменились.

Третий аргумент – временная_шкала = $A$2:$A$49. Здесь расположены даты временной шкалы или номера периодов. Важно чтобы они отстояли друг от друга на фиксированный интервал. Если интервал не будет фиксированным, Excel всё еще будет исходить из гипотезы, что интервал фиксированный, а некоторые данные пропущены. Как обрабатываются такие ситуации описано ниже. Сортировать массив по значениям временной шкалы не обязательно, так как ПРЕДСКАЗ.ETS сама отсортирует данные прежде, чем выполнить расчеты.

Четвертый аргумент – [сезонность] = 1. Это необязательный аргумент. Значение по умолчанию равно 1. Для него Excel автоматически определяет сезонность и использует положительные целые числа в качестве длины сезонного шаблона. Значение 0 предписывает не использовать фактор сезонности, в результате чего прогноз будет линейным. Если для этого параметра задано положительное целое число, алгоритм использует его в качестве длины шаблона сезонности. Например, вы знаете, что сезонность равна 4 (квартальная периодичность), но предполагаете, что она слабая, и автоматический алгоритм Excel может ее не выявить, и будет считать, что сезонности нет. Для начала я рекомендовал бы использовать значение по умолчанию.

Пятый аргумент – [заполнение_данных] = 1. Это необязательный аргумент. Хотя временная шкала требует постоянный шаг между точками данных, FORECAST.ETS поддерживает до 30% отсутствующих данных и автоматически настраивает их. 0 указывает, что алгоритм учитывает отсутствующие точки в качестве нулей. Если задано значение 1 (вариант по умолчанию), функция определяет отсутствующие значения как среднее между соседними точками.

Шестой аргумент – [агрегирование] – в нашем примере опущен. Это необязательный аргумент. Он нужен, если даты временной шкалы или номера периодов содержат дубли. Функция ПРЕДСКАЗ.ETS выполнит агрегирование точек с одинаковой меткой времени. Параметр агрегирования — это числовое значение, определяющее способ агрегирования нескольких значений с одинаковой меткой времени. Для значения по умолчанию 0 используется метод СРЗНАЧ; также доступны варианты СУММ, СЧЁТ, СЧЁТЗ, МИН, МАКС и МЕДИАНА.

Это первая статья из серии "Как самостоятельно рассчитать прогноз продаж с учетом роста и сезонности", из которой вы узнаете о 5 способах расчета значений линейного тренда в Excel.

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
2. Расчет сезонности;
3. Расчет прогноза;
После изучения данного материала вы сможете выбрать оптимальный способ расчета значений линейного тренда, который будет удобен для решения вашей задачи, а в последствии, и для расчета прогноза наиболее удобным для вас способом.

Линейный тренд хорошо применять для временного ряда, данные которого увеличиваются или убывают с постоянной скоростью.

Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

Временной ряд продажи по месяцам (см. вложенный файл).

В этом временном ряду у нас есть 2 переменных:

Уравнение линейного тренда y(x)=a+bx, где

y — это объёмы продаж

x — номер периода (порядковый номер месяца)

a – точка пересечения с осью y на графике (минимальный уровень);

b – это значение, на которое увеличивается следующее значение временного ряда;

1-й способ расчета значений линейного тренда в Excel с помощью графика

Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У - объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

При расчете значений линейного тренде нам будут известны:
1. Время - значение по оси Х;
2. Значение "a" и "b" уравнения линейного тренда y(x)=a+bx;
Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

27-го y=135134*27+4594044=8242686

2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН

1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:

=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

Для расчета коэффициентов в формулу вводим

известные значения y (объёмы продаж за периоды),

известные значения x (номера периодов),

вместо константы ставим 1,

вместо статистики 0,

Получаем 135135 - значение (b) линейного тренда y=a+bx;

Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо

Получаем 135135, 4594044 - значение (b) и (a) линейного тренда y=a+bx;

2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов - x, для которых хотим рассчитать значения линейного тренда.

2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также быстрее.

3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

Подставляем в формулу
1. известные значения y - это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
2. известные значения x - это номера периодов x для известных значений объёмов продаж y;
3. новые значения x - это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
4. константа - ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;
Для того чтобы рассчитать значения тренда для всего временного диапазона, в "новые значения x" вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.

4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

=ПРЕДСКАЗ(x; известные значения y; известные значения x)

Вместо X поставляем номер периода, для которого рассчитываем значение тренда.

Вместо "известные значения y" - объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

"известные значения x" - это номера периодов для каждого выделенного объёма продаж.

3-й и 4-й способ расчета значений линейного тренда быстрее, чем 1 и 2-й, однако с его помощью невозможно управлять коэффициентами тренда, как описано в статье "О линейном тренде".

5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO
1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:
- Что рассчитываем - значения тренда;
- Тренд - Линейный тренд;
- Временной ряд - месячный;
и сохраняем;
2. Заходим в меню программы и нажимаем "Start_Forecast". Значения линейного тренда рассчитаны.

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

В следующих статье "Как самостоятельно сделать прогноз продаж с учетом роста и сезонности" мы:

О том, что еще важно знать о линейном тренде, вы можете узнать в статье "Что важно знать о линейном тренде".

Точных вам прогнозов!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite - автоматический расчет прогноза в Excel .
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Читайте также: