ЛИНЕЙН для второго (линейного многофакторного) прогноза показан на рис. 14. Чтобы выполнить эти вычисления, сначала выделите диапазон F4:I8 (4 столбца в соответствии с количеством коэффициентов уравнения регрессии и 5 строк), не снимая выделения, введите формулу
=ЛИНЕЙН(D4:D33;A4:C33;;1)
и затем нажмите клавиши .
Значение коэффициента детерминации записано в ячейке F6. Как видите, его значение 0,672035 не очень велико.
60
Рис. 14. Вычисление статистических характеристик для линейного многофакторного уравнения регрессии
Вычисление аналогичных статистических характеристик для оставшихся двух прогнозов показано на рис. 15, 16. Соответствующие формулы приведены в строке формул рабочего листа.
Рис. 15. Вычисление статистических характеристик для экспоненциального многофакторного уравнения регрессии
61
Рис. 16. Вычисление статистических характеристик для полиномиального уравнения регрессии третьей степени
Запишем все найденные коэффициенты детерминации в одну таблицу
(табл. 10)
Коэффициенты детерминации
Т а б л и ц а 10
Модель уравнения регрессии
Коэффициент детерминации
Линейная однофакторная
0,7365
Линейная многофакторная
0,672035
Экспоненциальная многофакторная
0,726559
Полиномиальная третьей степени
0,681266
Как видно из таблицы, значения коэффициентов детерминации не велики, поэтому трудно отдать предпочтение тому или иному прогнозу. Это означает, что все модели данных не адекватно описывают поведение прогнозируемой переменной Y. Это происходит потому, что не учитываются сезонные изменения. Однако выделить сезонную составляющую не просто, и методы ее выделения не входят в быстрые методы получения прогноза.
62 4.5. Построение доверительного интервала Доверительным интервалом для прогнозного значения называется случайный интервал, который с заданной вероятностью α содержит неизвестное точное значение функции F(X). Вероятность называется α доверительным уровнем или надежностью.Быстрое вычисление доверительных интервалов имеет некоторые ограничения. • Вычисление доверительных интервалов возможно только в случае, если прогнозируемая переменная зависит от одного фактора. • Предполагается, что прогнозируемая переменная зависит от этого фактора в виде полиномиальной регрессии: kkmmmbX X X Y 2 2 1 0 + + + + = • Для построения доверительных интервалов должны выполняться статистические условия (независимость и нормальное распределение остатков с нулевыми математическими ожиданиями и одинаковыми дисперсиями), которые необходимо проверить до построения доверительных интервалов и которые не выполняются, если функция прогнозирования плохо аппроксимирует прогнозируемую переменную. Несмотря на это покажем, как строятся простейшие доверительные интервалы. Предположим, что прогнозируемая переменная Y (объемы продаж) зависит только от временных периодов. Выразим эту зависимость с помощью многочлена второй степени: 2 2 1 0 Y tmtmb+ + = , где коэффициенты и необходимо вычислить. Для вычисления этих 1 0 , mb2 mкоэффициентов и других статистических показателей используем функцию ЛИНЕЙН
63
Рабочий лист, на котором вычисляются коэффициенты функции регрессии и
, статистические показатели этой функции, прогнозные
1 0
,m
b
2
m
значения и доверительные интервалы для них, показан на рис. 17.
Так как используется квадратичная регрессия, то для вычислений необходимо подсчитать значения
2
t
, что сделано в столбце В.
С помощью функции ЛИНЕЙН вычислим коэффициенты функции регрессии и статистические показатели с помощью формулы массива
{=ЛИНЕЙН(F2:F31;В2:С31;;1)}, записанной в диапазоне Е10:G14. Значения коэффициентов и записаны в ячейках E10:G10.
1 0
,m
b
2
m
Вычислим значения прогноза на 31-36 периоды. Это сделано в ячейках
F2:F7. В ячейку F2 записана формула, показанная на рис. 17 в строке формул, которая затем копируется вниз до ячейки F7.
Рис. 17. Вычисление доверительных интервалов для прогнозных значений
64
Вычислим границы доверительных интервалов прогнозных значений.
Приведем для этого необходимую последовательность формул. Обозначим через множество значений фактора X ( – количество исходных
n
x
x
x
,...,
,
2 1
n
данных), через
– значение фактора, для которого необходимо
0
x
спрогнозировать значение переменной Y. За значение переменной Y в точке
0
x
принимается величина
)
(
0
x
f
y
=
.
Для построения доверительного интервала для величины с
)
(
0
x
f
y
=
заданным доверительным уровнем
, необходимо выполнить следующую
α
последовательность вычислений.
1. Вычислить значение
)
(
0
x
f
y
=
.
2. Вычислить среднее
∑
=
=
n
i
i
x
n
x
1 1
значений
n
x и сумму
x
x
,...,
,
2 1
∑
=
−
=
n
i
i
x
x
x
SS
1 2
)
(
3. Найти стандартную ошибку прогноза по формуле:
x
SS
x
x
n
s
s
2 0
ε
0
)
(
1 1
−
+
+
=
, где
– среднеквадратическое отклонение остатков (вычисляется с
ε
s
помощью функции ЛИНЕЙН).
4. Определить квантиль t порядка
α)/2 1
(
+
распределения Стьюдента с
)
1
степенью свободы (k – степень многочлена функции регрессии).
(
−
− k
n
5. Вычислить нижнюю
0
s
t
y
⋅
−
и верхнюю
0
границы
s
t
y
⋅
+
доверительного интервала.
Квантилем порядка р случайной величины X называется такое число
p
ξ
, для которого выполняется вероятностное равенство
p
P
p
=
<
)
X
(
ξ
.
Величина квантиля t распределения Стьюдента зависит от заданного доверительного уровня
α
, который чаще всего задается величиной 95%. Из приведенных в п. 5 формул видно, что квантиль t определяет длину
65
доверительного интервала в зависимости от заданного доверительного уровня.
Чем больше значение t,тем больше (по длине) доверительный интервал. В качестве параметра, определяющего длину доверительного интервала, квантиль распределения Стьюдента выступает только при выполнении статистических условий, налагаемых на модель регрессии (независимость и нормальное распределение остатков с нулевыми математическими ожиданиями и одинаковыми дисперсиями).
В нашем случае при заданном доверительном уровне 95% вместо квантиля t распределения Стьюдента рекомендуется брать число 3 согласно эмпирическому правилу «трех сигм» (или правилу 3S).
В приведенной выше схеме вычисления доверительного интервала п. 4 пропускаем, а в п. 5 нижнюю и верхнюю границы доверительного интервала вычисляем по формулам и
0 3
s
y
−
0 3
s
y
+
соответственно.
Соответствующие формулы вычисления доверительных интервалов на рабочем листе Excel показаны на рис. 16. Придерживаясь последовательности вычислений доверительных интервалов, вычислим:
• среднее x (ячейка F16);
• сумму
x
SS (ячейка F17). Сумму
∑
=
−
=
n
i
i
x
x
x
SS
1 2
)
(
можно вычислить разными способами (например, использовав функцию СУММКВ);
• в ячейках F18:F23 вычисляются стандартные отклонения прогнозов для каждого периода 31, 32, ..., 36;
• значение среднеквадратического отклонения остатков
ε
s вычисляется функцией ЛИНЕЙН и содержится в ячейке F12.
Для линейной регрессии значение среднеквадратического отклонения остатков вычисляет функция CTOШYX.
ε
s
Границы доверительных интервалов вычисляются в диапазонах G2:G7
(нижние границы) и H2:H7 (верхние границы). Для их вычисления в ячейки G2 и H2 вводятся формулы, показанные на рис. 16, которые затем копируются в оставшиеся ячейки этих диапазонов.
66 Все вычисления доверительных интервалов завершены. Построим график, на котором отобразим исходные данные (значения переменной Y), линию регрессии, вычисленные прогнозные значения, а также верхние и нижние границы доверительных интервалов. Такой график показан на рис. 18. Рис. 18. Графическое представление доверительных интервалов Как видно из графика, доверительные интервалы охватывают практически весь диапазон возможных значений переменной Y. Это подтверждает, что выбранная модель данных, основанная только на уравнении регрессии, зависящей от времени, плохо соответствует исходным данным. Для выполнения качественного прогнозирования необходим полный анализ данных. 67 Заключение Мы рассмотрели быстрые способы прогнозирования с помощью встроенных средств, предоставляемых Excel. Какие же выводы можно сделать из описания быстрых способов прогнозирования? • Во-первых, в Excel имеется достаточно средств для быстрого построения разнообразных функций регрессии т.е. для выделения трендовой составляющей модели данных и для построения на этой основе прогноза. • Во-вторых, графические средства Excel позволяют строить линию тренда для прогнозируемой переменной, зависящей только от одного фактора. Для перевода графического представления прогноза в числовой формат необходимо выполнить дополнительные вычисления. • В-третьих, с помощью функций Excel сравнительно просто построить доверительные интервалы для прогнозных значений только в случае, если прогнозируемая переменная зависит от одного фактора. • В-четвертых, поскольку в Excel нет отдельных средств для выделения сезонной составляющей, с помощью встроенных средств Excel можно построить более-менее точный прогноз только в том случае, если исходные данные не содержат сезонных изменений. Для получения качественного прогноза необходим полный анализ имеющихся данных. Анализ данных включает в себя несколько этапов. Первый этап – это подготовка данных к прогнозированию. На этом этапе рассматриваются такие вопросы, как восстановление пропущенных данных, анализ значительно отличающихся значений данных (анализ выбросов), отбор влияющих факторов и введение в модель данных новых факторов. На втором этапе необходимо решить проблему выбора функции прогнозирования, от которой зависит качество будущего прогноза. Чтобы из нескольких моделей данных выбрать наилучшую, чтобы быть уверенным в качестве выбранной модели и чтобы определить точность вычисленных прогнозных значений, необходимо также оценить статистические свойства случайного процесса.
68
На третьем этапе проводят сглаживание данных. Это способ уменьшения влияния на данные случайных факторов. В результате применения к исходным данным методов сглаживания получают новые данные, в которых в значительной степени уменьшено присутствие случайной составляющей, и лучше прослеживаются общие тенденции, заложенные в исходных данных.
После всех этих приготовлений приступают непосредственно к построению моделей данных – это выделение трендовой составляющей и выделение сезонной составляющей, а затем непосредственно к получению прогнозных значений. Как видите построение качественного прогноза – процесс весьма трудоемкий. Качественный прогноз может дать только качественная модель данных. Прогнозирование действительно помогает заглянуть за горизонт завтрашнего дня и тем приносит несомненную пользу в процессах принятия решений.
69
Контрольные вопросы
1. Дайте определения понятия «прогноз».
2. В чем состоит сущность прогнозирования?
3. Что представляет собой наука прогностика, предмет ее изучения?
4. Покажите роль прогнозирования в функционировании предприятия или организации при принятии управленческих решений.
5. Что может выступать в качестве объектов прогнозирования?
6. Укажите основные отличия и сходства прогноза и гипотезы; прогноза и плана. Как изменяется уровень определенности информации в зависимости от вида предсказания?
7. Определите признаки классификации прогнозов.
8. Опишите суть поискового и нормативного способов прогнозирования.
9. Назовите и дайте краткую характеристику основных подходов к прогнозированию.
10. Перечислите и укажите существенные особенности основных функций и принципов прогнозирования.
11. Выделите основные этапы процедуры прогнозирования.
12. Приведите основные типы прогнозов социально-экономических процессов.
13. Что такое метод прогнозировании?
14. Назовите характерные черты, присущие методам прогнозирования.
15. Какие бывают методы прогнозирования и в чем их отличия?
16. Каков общий алгоритм построения прогнозов?
17. Какие средства для построения математических моделей и прогнозирования состояния моделируемого объекта содержит Excel?
18. Что такое уравнение регрессии?
19. Какие виды регрессии различают? Приведите примеры.
20. Что показывает коэффициент детерминации?
70 21. В чем заключается задача построения регрессионной зависимости? 22. Какие графически средства Excel применяются для получения прогнозов? 23. Изложите процедуру построения линии тренда. 24. Можно ли, используя графические средства построения прогнозов, определить числовые величины прогнозных значений? 25. Какая статистическая функция применяется для нахождения коэффициентов линейной однофакторной регрессии? 26. С помощью какой функции можно найти прогнозные значения, если функция прогнозирования экспоненциально зависит от нескольких факторов? 27. С помощью какой функции можно определить коэффициент детерминации 2 R для полиномиальной аппроксимации наблюдаемой величины? 28. Что такое доверительные интервалы? Изложите алгоритм построения доверительных интервалов. 29. Какие статистические функции используются для вычисления доверительных интервалов? 30. Существуют ли в Excel встроенные функции для выделения сезонной составляющей?
71
Задачи для самостоятельного решения
1. В таблице приведена средняя урожайность с/х культуры (центнеров с га) в зависимости от числа лет, прошедших с момента внесения удобрений.
Построить экспоненциальную регрессионную модель, дать прогноз урожайности через 6 лет с момента внесения удобрений с помощью функций Excel. Построить графики теоретического и реального рядов.
Срок
1 2 3 4 5
Урожайность
40 25 20 17,5 16 2. По данным бюджетного обследования семи случайно выбранных семей изучалась зависимость накоплений y от дохода x
1
и стоимости имущества
x
2
.
Исходные данные:
x
1
40 55 45 30 30 60 50
x
2
60 40 40 15 90 30 30
y
2 7 5 4 2 7 6
Определить:
•
Коэффициенты уравнения линейной многофакторной регрессии.
•
Прогнозируемые накопления семьи с доходом 40 усл. ед. и имуществом стоимостью 25 усл. ед., используя уравнение регрессии и встроенную функцию Excel.
•
Определить коэффициент детерминации.
3. Используя графические средства прогнозирования, составить прогноз продаж мяса на будущий период (III и IV кварталы текущего года) на основеинформации о продаже мяса, приведенной в таблице.
Объем продаж мяса, тыс. т
Период продаж Объем продаж
Период продаж Объем продаж
I квартал 2005 г. 1834 II квартал 2006 г. 1486
II квартал 2005 г. 1641 III квартал 2006 г.
1617
III квартал 2005 г. 1791 IV квартал 2006 г.
3045
IV квартал 2005 г. 3332 I квартал 2007 г. 1546
I квартал 2006 г. 1658 II квартал 2007 г. 1404
72 4. Застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе. Застройщик может использовать множественный регрессионный анализ для оценки цены офисного здания в заданном районе на основе следующих переменных.
Y – оценочная цена здания под офис;
Х
1
– общая площадь в квадратных метрах;
Х
2
– количество офисов;
Х
3
– количество входов (0,5 входа означает вход только для доставки корреспонденции);
Х
4
– время эксплуатации здания в годах.
Предполагается, что существует линейная зависимость между независимыми переменными (X
1
, X
2
, X
3
и X
4
) и зависимой переменной
(Y). Определите коэффициенты линейного многофакторного уравнения регрессии. Запишите вид этой зависимости. Исходные данные показаны на рисунке.
73 5. В результате применения функции для нахождения коэффициентов уравнения линейной регрессии получили следующую таблицу значений
-0,03376 0,129512 0,449811 0,017384 0,036573 2,064993 0,862941 0,96945 #Н/Д
12,59226 4 #Н/Д
23,66924 3,759332 #Н/Д
Запишите общий вид этой зависимости.
6. Имеем следующие экспериментальные данные:
t
X1
X2
X3
X4
X5
Y
1 164.70 86.00 32.00 112.70 136.20 104.94 2
183.99 73.25 37.00 110.40 126.80 147.25 3
198.31 62.00 61.00 108.10 117.80 136.00 4
203.96 59.63 59.50 108.05 113.50 146.36 5
209.61 57.25 58.00 108.00 109.20 148.50 6
218.85 44.00 85.00 106.10 101.00 173.14 7
226.58 39.25 91.00 100.40 93.20 155.08 8
229.85 34.13 104.00 99.85 89.50 180.07 9
233.13 29.00 117.00 99.30 85.80 162.60 10 238.76 28.25 113.00 95.20 78.80 179.02 11 243.62 17.00 143.00 92.90 72.20 170.36 12 245.74 13.63 149.00 92.35 69.10 198.62 13 247.85 10.25 155.00 91.80 66.00 180.41 14 251.52 9.00 172.00 90.90 10.00 206.84 15 254.73 6.25 187.00 87.80 7.80 168.72 16 256.13 3.13 189.50 85.25 7.70 200.00 17 257.52 0.00 192.00 82.70 7.60 167.69 18 259.95
-4.75 197.00 81.80 6.40 205.48 19 262.05
-10.00 228.00 77.90 5.20 182.22 20 262.96
-9.50 235.50 78.25 4.10 204.00 21 263.86
-9.00 243.00 78.60 3.00 187.56 22 265.41
-8.00 247.00 73.90 1.80 214.18 23 266.72
-7.00 267.00 70.40 0.60 189.32
74 24 267.27
-6.50 268.50 70.15 0.50 210.67 25 267.81
-6.00 270.00 69.90 0.40 196.63 26 268.69
-6.00 285.00 66.20
-0.80 213.55 27 269.40
-5.00 301.00 0.10
-0.80 167.70 28 269.67
-4.50 311.00 0.45
-0.20 186.50 29 269.93
-4.00 321.00 0.80 0.40 182.92 30 270.30
-4.00 325.00 5.30 0.60 203.75
Постройте линейную и экспоненциальную многофакторные модели данных. Определите прогнозные значения зависимой величины Y на пять периодов вперед. Определите какая модель дает более точный прогноз.
7. По территориям Центрального района известны данные за 2005 г.
•
Рассчитайте параметры уравнения линейной регрессии.
•
Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня.
Район
Доля денежных доходов, направленных на прирост сбережений во вкладах, займах, сертификатах и на покупку валюты, в общей сумме среднедушевого денежного дохода, %,
у
Среднемесячная начисленная заработная плата, тыс. руб.,
х
Брянская обл. 6,9 289
Владимирская обл. 8,7 334
Ивановская обл. 6,4 300
Калужская обл. 8,4 343
Костромская обл. 6,1 356
Орловская обл. 9,4 289
Рязанская обл. 11,0 341
Смоленская обл. 6,4 327
Тверская обл. 9,3 357
Тульская обл. 8,2 352
Ярославская обл. 8,6 381 8. Имеются данные о деятельности крупнейших компаний США.
•
Постройте линейное уравнение множественной регрессии и поясните экономический смысл его параметров.
•
Дайте оценку полученного уравнения на основе коэффициента
75
детерминации.
•
Рассчитайте прогнозное значение результата, если прогнозное значение каждого из факторов увеличится на 10% от его среднего уровня.
№
п/п
Чистый
доход,
млрд
долл.
США, Y
Оборот
капитала,
млрд долл.
США, X
1
Использова
нный
капитал,
млрд долл.
США, X
2
Численность
служащих,
тыс. чел., X
3
Рыночная
капитализация
компании, млрд
долл. США, X
4
1 0,9 313 18,9 43,0 40,9 2 1,7 13,4 13,7 64,7 40,5 3 0,7 4,5 18,5 24,0 38,9^
4, 1,7 10,0 4,8 50,2 38,5 5 2,6 20,0 21,8 106,0 37,3 6 1,3 15,0 5,8 96,6 26,5 7 4,1 137,1 99,0/
347,0 37,0 8 1,6 17,9 20,1 85,6 36,8 9 6,9 165,4 60JL
745,0 36,3 10 0,4 2,0 1,4 4,1 35,3 11 1,3 6,8 8,0 26,8 35,3 12 1,9 27,1 18,9 42,7 35,0 13 1,9 13,4 13,2 61,8 26,2 14 1,4 9,8 12,6 212,0 33,1 15 0,4 19,5 12,2 105,0 32,7 16 0,8 6,8 3,2 33,5 32,1 17 1,8 27,0 13,0 142,0 30,5 18 0,9 12,4 6,9 96,0 29,8 19 1,1 17,7 15,0 140,0 25,4 20 1,9 12,7 11,9 59,3 29,3 21 -0,9 21,4 1,6 131,0 29,2 22 1,3 13,5 8,6 70,7 29,2 23 2,0 13,4 11,5 65,4 29,1 24 0,6 4,2 1,9 23,1 27,9 25 0,7 15,5 5,8 80,8 27,2-
76
Литература
1. Лапыгин, Ю.Н., Крылов, В.Е., Чернявский, А.П. Экономическое прогнозирование: учебное пособие – М.: Эксмо, 2009. – 256 c.
2. Рабочая книга по прогнозированию. Отв.ред. И.В. Бестужев-Лада. – М.:
Мысль, 1982.
3. Янч, Э. Прогнозирование научно-технического прогресса. – М.: Прогресс,
1974.
4. Минько, А.А. Прогнозирование в бизнесе с помощью Excel – М.: Эксмо,
2007. – 208 c.
5. Гмурман, В.Е. Теория вероятностей и математическая статистика. – М.:
Высшая школа, 2001.-479 с.
6. Сигел, Э.Практическая бизнес-статистика.: Пер. с англ. – М.:
Издательский дом «Вильямс», 2002. – 1056 с.
7. Тихомиров, Н.П., Попов, В.А. Методы социально-экономического прогнозирования. – М.: Изд-во ВЗПИ, А/О «Росвузнаука», 1992.
77
Литвинчук Светлана Юрьевна
Информационные технологии в экономике
Анализ и прогнозирование временных рядов с помощью Excel
Учебное пособие
Редактор
Н.В. Викулова
Подписано в печать ___________ Формат 60х90 1/16. Бумага газетная. Печать трафаретная.
Уч.изд.л._____Усл.печ.л._____Тираж 100 экз. Заказ №_____
Государственное образовательное учреждение «Нижегородский государственный архитектурно-строительный университет»
603950, Н.Новгород, Ильинская, 65.
Полиграфцентр ННГАСУ, 603950, Н.Новгород, Ильинская, 65.
|
Скачать 1.29 Mb.