Построение прогнозов с использованием возможностей Microsoft Excel

Лабораторная работа «Построение прогнозов с использованием возможностей Microsoft Excel»

Цель работы

Приобретение навыков разработки прогнозов развития экономических явлений с использованием возможностей Microsoft Excel.

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

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

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

Указания к построению прогноза

Для примера возьмем следующие исходные данные из табл. 1

Таблица 1. Исходные данные для задачи.

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

Для первых трех периодов рассчитать прогнозные данные нельзя, т.к. для них отсутствуют необходимые данные. Расчет начинается с четвертого периода. Для этого, после ввода исходных данных в таблицу Excel производим расчет прогноза за необходимый период (рис. 1).

Рисунок 1. Расчет значения среднего скользящего

Далее производится расчет по следующим периодам. Для быстрого расчета необходимо растянуть данную ячейку на следующие периоды, включая прогнозный. Далее необходимо сделать прогноз с использованием функции ТЕНДЕНЦИЯ. Данная функция находит линейную зависимость между заданными значениями функции y и значениями ее аргументов x. Данная зависимость представляется линейной функцией, а значения ее аргументов находятся в Excel по методу наименьших квадратов. В данном случае значения функции y — это данные, а значения аргументов x — номера периодов для соответствующих данных. Более подробную информацию о данной функции можно узнать с помощью помощника Excel, вызвав ее из меню или нажав клавишу «F1». Для расчета прогноза за необходимый период сначала введем в необходимой клетке таблицы «=ТЕНДЕНЦИЯ («, а далее укажем массив, в котором находятся известные значения функции y. Затем укажем массив, в котором находятся значения аргументов функции x. Последним указывается значение того периода, за который необходимо рассчитать прогноз, т. е. и необходимое значение x, для которого необходимо найти значение функции y.

Рисунок 2. Расчет прогноза с использованием функции тенденции

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

Далее сделаем прогноз с использованием функции РОСТ. Данная функция Excel рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих xи y-значений. Т. е. данная функция строит зависимость между функцией и ее аргументами в виде. О ней более подробно также можно узнать из помощника, вызвав ее из меню или нажав клавишу «F1». Для расчета прогноза за необходимый период применяется функция «РОСТ» аналогично функции «ТЕНДЕНЦИЯ» из предыдущего прогноза. Сначала необходимо указать массив, в котором находятся известные значения функции y. Затем массив, в котором находятся значения аргументов функции x, и необходимое значение x, для которого необходимо найти значение функции y.

excel экспоненциальный детерминация полиномиальный

Рисунок 3. Расчет прогноза с использованием функции роста

В итоге получаем прогноз по трем методам (рис. 4).

Рисунок 4. Расчет прогноза с использованием функции тенденции

Таким образом, можно сделать вывод, что в зависимости от используемого метода прогнозирования прогнозное значение исследуемого показателя в будущем периоде будет равняться: 1031 для средней скользящей, 1120,73 — при использовании функции тенденции, 1177,31 — при использовании функции роста. Далее, применяя «Мастер диаграмм» строим графики для данных прогнозов. Данный мастер можно вызвать из меню «Вставка» пункт меню «Диаграмма» или нажав на кнопку на панели под главным меню программы. Далее, в появившемся окне необходимо выбрать тип диаграммы. Нам необходимо выбрать тип диаграммы «График» т.к. он наиболее подходит в данном случае для отображения развития изучаемого параметра в динамике (во времени).

Рисунок 5. Построение графика прогноза по методу скользящей средней

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

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

Рисунок 6. График прогноза скользящей средней

Указания к решению второго задания

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

Рисунок 7. График развития исследуемого показателя во времени

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

Рисунок 8. Выбор типа зависимости

Также в закладке «Параметры» необходимо отметить пункты «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R²)». Первый пункт необходимо отметить для того, чтобы на диаграмме отображалось уравнение регрессии, а второй — для отображения значения коэффициента детерминации R² (рис. 9).

Рисунок 9. Изменение параметров для линии тренда

После нажатия на кнопку «ОК» добавится линия, отображающая зависимость между имеющимися данными, а также уравнение регрессии и значение R².

Рисунок 10. Линейная зависимость между исследуемыми показателями

В данном случае был выбран линейный тип зависимости. Как видно из результатов (рис. 10), уравнение регрессии линейного вида довольно точно отображает зависимость между изучаемым параметром и временем. Значение данного коэффициента может лежать в интервале от нуля от единицы. Чем ближе значение коэффициента к R² единице, тем точнее данное уравнение регрессии определяет зависимость между имеющимися данными, а чем ближе его значение к нулю — тем хуже данный тип зависимости подходит в данном случае. В данном случае R² = 0,9031, т. е. линейная зависимость достаточно точно отображает зависимость изучаемого параметра от периода времени.

Однако возможно существует зависимость другого вида, которая имеет более высокое значение коэффициента детерминации. Таким образом, необходимо подобрать наиболее лучший тип зависимости. Для этого необходимо подвести курсор мышки непосредственно к линии тренда, отображающей зависимость, нажать правую клавишу мышки и выбрать из появившегося контекстного меню команду «Формат линии тренда…». В появившемся окне необходимо выбирать другие типы зависимостей и сравнивать получаемые результаты.

В данном случае наиболее точно развитие исследуемого процесса в динамике отображает полиномиальная зависимость 3й степени (рис. 11). Коэффициент детерминации наиболее близок к единице и равен 0,9555, а уравнение регрессии имеет следующий вид:

.

Подставив в данное уравнение значение будущего периода (одиннадцатого), получим прогнозное значение:

.

Рисунок 11. Полиномиальная зависимость между исследуемыми показателями

Варианты заданий (по исходным данным построить прогноз с использованием функций Скользящая Средняя, Тенденция, Рост)