Основные положения

1 2

Прогнозирование средствами Microsoft Excel.

Цель работы: Изучение различных способов вычисления прогнозов в электронной таблице Excel.

ОСНОВНЫЕ ПОЛОЖЕНИЯ.

Электронная таблица Microsoft Excel предлагает различные способы вычисления прогнозных показателей:

· непосредственно с помощью функций (ЛИНЕЙН, ТЕНДЕНЦИЯ, ПРЕДСКАЗ, ЛГРФПРИБЛ, РОСТ)

· используя методы скользящего среднего и экспоненциального сглаживания;

· используя линию тренда.

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

Функция рабочего листа ЛИНЕЙН помогает определить характер линейной связи между результатами наблюдений и временем их фиксации и дать ей математическое описание, наилучшим образом аппроксимирующее исходные данные. Для построения трендовой модели она использует уравнение вида у = тх + b, где у — исследуемый показатель; х — факторный признак (независимая переменная); b, т — параметры уравнения, характеризующие соответственно y-пересечение и наклон линии тренда. Расчет параметров модели ЛИНЕЙН производят на основе метода наименьших квадратов.

Синтаксис функции ЛИНЕЙН:

=ЛИНЕЙН(<Известные значения у>; <Известные значения х>; <Константа>;<Статистика>)

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

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

Константа — логическое значение, которое указывает на необходимость расчета параметра b (свободного члена) при построении модели тренда. Если Конст имеет значение ИСТИНА, то параметр b вычисляется. Если Конст имеет значение ЛОЖЬ, то параметр b принимается равным нулю. Тогда функция ЛИНЕЙН начинает работать с уравнением вида у = тх.

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

Значение параметра m	Значение параметра b
Стандартная ошибка для параметра m	Стандартная ошибка для свободного члена b
Квадрат коэффициента корреляции	Стандартная ошибка для у
F-критерий (F набл)	Степень свободы
Сумма квадратов регрессии	Остаточная сумма квадратов

Для проверки адекватности модели используется F- критерий: сравниваются Fнабл и Fкрит. Эти значения определяют следующим образом:

- F_набл находят из таблицы значений функции ЛИНЕЙН (четвертая строка в первом столбце)

- F_крит = F(<число переменных модели>;<степень свободы>). Значение функции находят по специальной таблице (см. Приложение А)

Если |F_набл| > |F_крит|, то модель считается адекватной.

Для определения надежности параметров модели используется критерий Стьюдента: сравниваются t_набл и t_крит, значения которых находят следующим образом:

- ;

- t_крит = t(<уровень значимости>;<степень свободы>). Значение функции на ходят по специальной таблице (см. Приложение Б). Уровень значимости в экономических расчетах обычно принимают равным 0,05 или 0,01.

Если |t_набл| > |t_крит|, то параметр признается значимым.

При проверке адекватности уравнения следующие варианты (при большом количестве данных):

1) построенная модель на основе ее проверки по F-критерию в целом адекватна и все параметры уравнения регрессии значимы. Такая модель может быть использована для прогнозирования исследуемого показателя;

2) модель по F-критерию адекватна, но часть параметров регрессии не значима. В этом случае модель может быть пригодна для принятия отдельных решении, но не подходит для расчета прогнозов;

3) модель по F-критерию адекватна, но все параметры уравнения не значимы. Такая модель полностью считается неадекватной. На ее основе нельзя принимать решения и составлять прогнозы.

Функции ТЕНДЕНЦИЯ и ПРЕДСКАЗ рассчитывают прогнозные значения исследуемого показателя в соответствии с линейной моделью, но не приводит математического описания и статистических характеристик самой модели.

Синтаксис функции ТЕНДЕНЦИЯ:

=ТЕНДЕНЦИЯ(<Известные значения у>; <Известные значения х>; <Новые значения х>;<Константа>)

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

Константа — логическое значение, которое указывает на необходимость расчета параметра b (свободного члена) при построении модели тренда. Если Конст имеет значение ИСТИНА, то параметр b вычисляется. Если Конст имеет значение ЛОЖЬ, то параметр b принимается равным нулю. Тогда функция начинает работать с уравнением вида у = тх.

Синтаксис функции ПРЕДСКАЗ:

=ПРЕДСКАЗ(<Новое значение х>;<Известные значения у>; <Известные значения х>)

Функции ЛГРФПРИБЛ и РОСТ целесообразно использовать в том случае, когда исследуемые показатели не имеют линейной зависимости (поэтому часто сначала определяют вид зависимости, например, построив график).

Функция ЛГРФПРИБЛ аналогична функции ЛИНЕЙН, но определяет параметры уравнения .

Проверка адекватности модели и надежности параметров аналогично тому, как это производится для линейной модели с одним исключением – t_набл вычисляется по следующей формуле:

Функция РОСТ определяет прогнозные значения на основании модели .

Синтаксис функции РОСТ:

=РОСТ(<Известные значения у>; <Известные значения х>;<Новые значения х>;<Константа>)

Если Константа имеет значение ИСТИНА (1), то параметр b вычисляется, в противном случае он равен 1, т.е. применяется модель вида .

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

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

(1)

Коэффициент парной корреляции является безразмерной величиной и не зависит от выбора единиц обеих переменных. Значение коэффициента корреляции лежит в интервале от -1 (в случае строгой линейной отрицательной связи) до +1 (в случае строгой линейной положительной связи). Соответственно, положительное значение коэффициента корреляции свидетельствует о прямой связи между исследуемым и факторным показателем, а отрицательное — об обратной. Чем ближе значение коэффициента корреляции к 1, тем теснее связь. Качественно оценить тесноту связи позволяет специальная шкала значений коэффициентов корреляции, разработанная профессором Колумбийского университета США Чеддоком (Приложение В):

Размер коэффициента корреляции	0,1-0,3	0,3-0,5	0,5-0,7	0,7-0,9	0,9-0,99
Теснота связи	слабая	умеренная	заметная	высокая	весьма высокая

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

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

Диалоговое окно предлагает пользователю определить следующие параметры:

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

Группирование — требует установления переключателя в положение «По столбцам» или «По строкам» в зависимости от расположения данных во входном диапазоне.

Метки в первой строке/Метки в первом столбце — позволяет определить название каждого столбца (или строки) выходной таблицы. Переключатель устанавливается в положение «Метки в первой строке», если первая строка во входном диапазоне содержит названия столбцов. Когда в первом столбце входного диапазона находятся названия строк, переключатель устанавливается в положение «Метки в первом столбце». Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне создаются на основе программы автоматически.

Выходной диапазон — предполагает введение ссылки на левую верхнюю ячейку выходного диапазона.

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

Новая книга — используется, когда необходимо открыть новую книгу и вставить результаты анализа в ячейку А1 на первом листе этой книги.

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

Чтобы построить линию тренда на основе базовых данных, необходимо:

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

- с областями

- гистограмма

- график

- линейчатая

- точечная

2. Добавить линию тренда

-в контекстном меню данной линии выбрать пункт «Добавить линию тренда»

-меню Диаграмма→ Добавить линию тренда

3. Указать тип линии тренда:

- линейная ()

- логарифмическая ()

- полиномиальная ()

- степенная ()

- экспоненциальная ()

4. Указать параметры линии тренда:

-название

-прогноз

-пересечение кривой с осью Y в точке (свободный коэффициент)

-показать уравнение на диаграмме

-поместить на диаграмму величину достоверности аппроксимации (коэффициент детерминации – квадрат коэффициента корреляции. Чем ближе к единице, тем лучше построенная модель согласуется с базовым рядом).

2. Практическая часть

Задание. Некоторый банк вкладывает средства в некоторое предприятие. В нижеприведенной таблице приведены данные за последний год.

Таблица 1. Значения вклада средств и полученная прибыль

Номер месяца	Вложенные средства, млн. руб.	Полученная прибыль, тыс. руб.
	125-N	150+N
	130-N	170+N
	135-N	180+N
	147-N	185+N
	150-N	190+N
	155-N	195+N
	160-N	200+N
	175-N	203+N
	180-N	210+N
	190-N	220+N
	200-N	250+N
	250-N	300+N
	300-N
	320-N
	340-N
	360-N
	500-N

где N – номер варианта.

Сделать прогноз на последующий (последующие) вложения.

Последовательность действий:

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

2. Проверить скоррелированность данных: если связь высокая или весьма высокая, то данные оставить без изменения; в противном случае изменить данные до высокой степени корреляции. Коэффициент корреляции вычислить как встроенными средствами Excel, так и по формуле.

3. На каждом новом листе спрогнозировать на последующие 5 месяцев или на один месяц (в зависимости от функции) прибыль с помощью функций ЛИНЕЙН, ТЕНДЕНЦИЯ, ПРЕДСКАЗ, ЛГРФПРИБЛ, РОСТ.

4. На новом листе построить диаграмму и добавить все возможные линии тренда. Результаты записать в отчет, заполнив таблицу2.

3. Содержание отчета:

1. Название работы.

2. Цель работы.

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

4. Заполнить следующую таблицу:

Таблица 2. Прогнозные значения прибыли с использованием различных линий тренда.

Вид тренда (название)	Уравнение	Значение коэффициента детерминации	Прогноз прибыли по месяцам

Приложение А

Критические точки распределения F (уровень значимости 0,05)

	Число переменных

Степень свободы		161,45	199,50	215,71	224,58	230,16	233,99	236,77	238,88	240,54	241,88	242,98	243,90
	18,51	19,00	19,16	19,25	19,30	19,33	19,35	19,37	19,38	19,40	19,40	19,41
	10,13	9,55	9,28	9,12	9,01	8,94	8,89	8,85	8,81	8,79	8,76	8,74
	7,71	6,94	6,59	6,39	6,26	6,16	6,09	6,04	6,00	5,96	5,94	5,91
	6,61	5,79	5,41	5,19	5,05	4,95	4,88	4,82	4,77	4,74	4,70	4,68
	5,99	5,14	4,76	4,53	4,39	4,28	4,21	4,15	4,10	4,06	4,03	4,00
	5,59	4,74	4,35	4,12	3,97	3,87	3,79	3,73	3,68	3,64	3,60	3,57
	5,32	4,46	4,07	3,84	3,69	3,58	3,50	3,44	3,39	3,35	3,31	3,28
	5,12	4,26	3,86	3,63	3,48	3,37	3,29	3,23	3,18	3,14	3,10	3,07
	4,96	4,10	3,71	3,48	3,33	3,22	3,14	3,07	3,02	2,98	2,94	2,91
	4,84	3,98	3,59	3,36	3,20	3,09	3,01	2,95	2,90	2,85	2,82	2,79
	4,75	3,89	3,49	3,26	3,11	3,00	2,91	2,85	2,80	2,75	2,72	2,69
	4,67	3,81	3,41	3,18	3,03	2,92	2,83	2,77	2,71	2,67	2,63	2,60
	4,60	3,74	3,34	3,11	2,96	2,85	2,76	2,70	2,65	2,60	2,57	2,53
	4,54	3,68	3,29	3,06	2,90	2,79	2,71	2,64	2,59	2,54	2,51	2,48
	4,49	3,63	3,24	3,01	2,85	2,74	2,66	2,59	2,54	2,49	2,46	2,42
	4,45	3,59	3,20	2,96	2,81	2,70	2,61	2,55	2,49	2,45	2,41	2,38