Инструменты прогнозирования в Microsoft Excel

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

Процедура прогнозирования

Цель любого прогноза — выявить текущую тенденцию и определить ожидаемый результат по отношению к исследуемому объекту в определенное время в будущем.

Способ 1: линия тренда

Один из самых популярных видов графического прогнозирования в Excel — экстраполяция, выполняемая путем построения линии тренда.

Попробуем спрогнозировать размер прибыли компании через 3 года на основе данных по этому показателю за предыдущие 12 лет.

1. Мы строим граф зависимостей на основе табличных данных, состоящих из аргументов и значений функций. Для этого выделите область таблицы, затем, находясь на вкладке «Вставка», щелкните значок нужного типа диаграммы, который находится в блоке «Диаграммы». Затем мы выбираем подходящий тип для конкретной ситуации. Лучшее решение — выбрать точечную диаграмму. Вы можете выбрать другой вид, но тогда, чтобы данные отображались правильно, вам нужно будет внести изменения, в частности, удалить строку темы и выбрать другой масштаб горизонтальной оси.
2. Теперь нам нужно провести линию тренда. Щелкаем правой кнопкой мыши по любой точке на диаграмме. В активированном контекстном меню отмените выбор пункта «Добавить линию тренда».
3. Откроется окно форматирования линии тренда. В нем вы можете выбрать один из шести типов приближения:
   • Линейный;
   • Логарифмический;
   • Экспоненциальный;
   • Уровень;
   • Полиномиальный;
   • Линейная фильтрация.

   Начнем с линейной подгонки.

   В блоке настроек «Прогноз» в поле «Вперед на» выставляем число «3.0», так как нам нужно сделать прогноз на три года вперед. Кроме того, вы можете установить флажки рядом с настройками «Показать уравнение на диаграмме» и «Поместить значение достоверности приближения (R ^ 2) на диаграмму». Последний индикатор отражает качество линии тренда. После выполнения настроек нажмите кнопку «Закрыть».

4. Линия тренда построена и по ней можно определить примерный размер прибыли за три года. Как видите, к тому времени он должен был превысить 4500 тысяч рублей. Коэффициент R2, как упоминалось ранее, отражает качество линии тренда. В нашем случае значение R2 равно 0,89. Чем выше коэффициент, тем выше надежность линии. Его максимальное значение может быть равно 1. Принято считать, что при коэффициенте больше 0,85 линия тренда является надежной.
5. Если вас не устраивает уровень достоверности, вы можете вернуться в окно формата линии тренда и выбрать любой другой тип приближения. Вы можете попробовать все доступные варианты, чтобы найти наиболее точный.

   

   Следует отметить, что эффективный прогноз с использованием экстраполяции через линию тренда может быть, если период прогноза не превышает 30% от базы анализируемого периода. То есть, анализируя 12-летний период, мы не можем сделать эффективный прогноз более чем на 3-4 года. Но даже в этом случае он будет относительно надежным, если в этот период не будет причин форс-мажора или, наоборот, крайне благоприятных обстоятельств, которых не было в предыдущие периоды.

Способ 2: оператор ПРЕДСКАЗ

Экстраполяцию табличных данных можно выполнить с помощью стандартной функции ПРОГНОЗ в Excel. Этот аргумент классифицируется как статистический инструмент и имеет следующий синтаксис:

= ПРОГНОЗ (X; известные_г, известные_x)

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

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

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

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

При расчете этого метода используется метод линейной регрессии.

Разберем нюансы использования оператора ПРОГНОЗ на конкретном примере. Возьмем всю такую ​​же таблицу. Нам нужно будет знать прогнозы прибыли на 2018 год.

1. Выделите пустую ячейку на листе, в которой вы планируете отобразить результат обработки. Нажмите кнопку «Вставить функцию».
2. Мастер откроется. В категории «Статистика» выберите название «PRECAST», затем нажмите кнопку «ОК».
3. Откроется окно темы. В поле «X» укажите значение аргумента, для которого вы хотите найти значение функции. В нашем случае это 2018 год. Поэтому мы вставляем запись «2018». Но лучше указать этот показатель в ячейке на листе и просто указать ссылку на него в поле «X». Это позволит в дальнейшем автоматизировать расчеты и при необходимости легко менять год.

   В поле «Известные значения y» укажите координаты столбца «Прибыль компании». Это можно сделать, поместив курсор в поле, затем удерживая левую кнопку мыши и выделив соответствующий столбец на листе.

   Аналогичным образом в поле «Известные значения x» введите адрес столбца «Год» с данными за прошедший период.

   После того, как вы ввели всю информацию, нажмите кнопку «ОК».

4. Оператор производит расчет на основе введенных данных и отображает результат на экране. По итогам 2018 года ожидается прибыль в районе 4564,7 тыс. Руб. На основе полученной таблицы мы можем построить диаграмму, используя инструменты построения диаграмм, описанные выше.
5. Если вы измените год в ячейке, используемой для ввода аргумента, результат изменится соответствующим образом, и диаграмма автоматически обновится. Например, по прогнозам в 2019 году размер прибыли составит 4637,8 тыс. Руб.

Но не забывайте, что, как и при построении линии тренда, период времени перед периодом прогноза не должен превышать 30% от всего периода, за который была накоплена база данных.

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозов вы можете использовать другую функцию: ТЕНДЕНЦИЯ. Он также относится к категории статистических операторов. Его синтаксис очень похож на синтаксис инструмента ПРОГНОЗ и выглядит следующим образом:

= ТЕНДЕНЦИЯ (y_known; x_know; x_new; [const])

Как видите, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРОГНОЗ, а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, TREND имеет дополнительный аргумент Constant, но он не является обязательным и используется только при наличии постоянных факторов.

Этот оператор используется наиболее эффективно, когда существует линейная зависимость функции.

Посмотрим, как этот инструмент будет работать с тем же набором данных. Чтобы сравнить результаты, давайте определим 2019 год как точку прогноза.

1. Обозначим ячейку для отображения результата и запускаем мастер обычным способом. В категории «Статистика» находим и выделяем название «ТЕНДЕНЦИЯ». Щелкните кнопку «ОК».
2. Откроется окно аргументов оператора TREND. В поле «Известные значения y», как описано выше, введите координаты столбца «Прибыль компании». В поле «Известные значения x» введите адрес столбца «Год». В поле «Новые значения x» введите ссылку на ячейку, в которой находится номер года, для которого вы хотите указать прогноз. В нашем случае это 2019 год. Оставьте поле «Константа» пустым. Щелкните кнопку «ОК».
3. Оператор обрабатывает данные и выводит результат на экран. Как видите, размер ожидаемой прибыли за 2019 год, рассчитанный методом линейной зависимости, как и в предыдущем методе расчета, составит 4637,8 тыс. Руб.

Способ 4: оператор РОСТ

Еще одна функция, с помощью которой вы можете делать прогнозы в Excel, — это оператор РОСТА. Он также относится к статистической группе инструментов, но, в отличие от предыдущих, использует не метод линейной зависимости, а экспоненциальный. Синтаксис этого инструмента выглядит так:

= РОСТ (известный_y; известный_x; новый_x; [const])

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

1. Выберите ячейку для отображения результата и вызовите мастер как обычно. В списке статистических операторов найдите «РОСТ», выберите его и нажмите кнопку «ОК».
2. Окно аргументов для указанной выше функции активируется. Мы вводим данные в поля этого окна точно так же, как мы вводим их в окно аргументов оператора TREND. После ввода информации нажмите кнопку «ОК».
3. Результат обработки данных отображается на мониторе в указанной выше ячейке. Как видите, на этот раз результат 4682,1 тыс. Руб. Отличия от результатов обработки данных оператором TREND незначительны, но есть. Это связано с тем, что в этих инструментах используются разные методы расчета: метод линейной зависимости и метод экспоненциальной зависимости.

Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН использует для вычисления метод линейного приближения. Его не следует путать с методом линейной зависимости, используемым инструментом TREND. Его синтаксис выглядит так:

= ЛИНЕЙН (известный_y, известный_x, новый_x, [const], [статистика])

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

1. Выбираем ячейку, в которой будет производиться расчет и запускаем функцию мастера. Выберите имя «ЛИНЕЙН» в категории «Статистика» и нажмите кнопку «ОК».
2. В поле «Известные значения y» открытого окна темы введите координаты столбца «Прибыль компании». В поле «Известные значения x» введите адрес столбца «Год». Остальные поля оставьте пустыми. Затем нажмите кнопку «ОК».
3. Программа рассчитывает и отображает значение линейного тренда в выбранной ячейке.
4. Теперь нам нужно рассчитать ожидаемую сумму прибыли на 2019 год. Установите знак «=» в любую пустую ячейку листа. Щелкните ячейку, содержащую фактическую сумму прибыли за последний изученный год (2016 г.). Ставим знак «+». Затем щелкните ячейку, содержащую рассчитанный ранее линейный тренд. Ставим знак «*». Поскольку между последним годом исследуемого периода (2016 г.) и годом, на который должен быть сделан прогноз (2019 г.), существует трехлетний период, мы устанавливаем в ячейке цифру «3». Чтобы выполнить расчет, нажмите кнопку Enter.

Как видите, величина ожидаемой прибыли, рассчитанная методом линейной аппроксимации, в 2019 году составит 4614,9 тыс. Руб.

Способ 6: оператор ЛГРФПРИБЛ

Последним инструментом, который мы рассмотрим, будет LGRFPRIBL. Этот оператор выполняет вычисления на основе метода экспоненциальной аппроксимации. Его синтаксис структурирован следующим образом:

= LGRFPRIBL (известный_y; известный_x; новый_x; [const], [статистика])

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

1. В списке операторов мастера выберите имя «LGRFPRIBL». Нажимаем на кнопку «ОК».
2. Откроется окно темы. Мы вставляем в него данные точно так же, как при использовании функции ЛИНЕЙН. Щелкните кнопку «ОК».
3. Результат экспоненциального тренда вычисляется и отображается в указанной ячейке.
4. Вставляем знак «=» в пустую ячейку. Раскройте скобки и выберите ячейку, содержащую доход за последний эффективный период. Ставим знак «*» и выбираем ячейку, содержащую экспоненциальный тренд. Ставим знак минус и снова нажимаем на пункт, в котором указана сумма выручки за последний период. Закройте круглые скобки и введите символы «* 3 +» без кавычек. Щелкните еще раз по той же ячейке, которая была выбрана в прошлый раз. Для выполнения расчета нажмите кнопку Enter.

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

Урок: другие статистические функции в Excel

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