Проведение вычислений с использованием инструмента «Регрессия» Microsoft Excel

Оценку коэффициентов уравнения множественной линейной регрессии, проверку значимости каждого коэффициента, значимости всего уравнения и построение интервальных оценок можно выполнить автоматически, используя средство «Пакет анализа» Microsoft Excel (инструмент «Регрессия»). Использование данного средства подробно описано в гл. 3, параграф 3.7.

Возьмем исходные данные примера 4.3 и введем их в таблицу Microsoft Excel (рис. 4.1).

Введение исходных данных в программу Microsoft Excel

Рис. 4.1. Введение исходных данных в программу Microsoft Excel

Задав все параметры диалогового окна «Регрессия» и нажав кнопку ОК, получим результат, приведенный на рис. 4.2.

Вывод итогов регрессионного анализа

Рис. 4.2. Вывод итогов регрессионного анализа

В результате компьютерных расчетов с помощью диалогового окна «Регрессия» получены следующие результаты (все данные округлены до трех знаков после запятой):

• уравнение множественной линейной регрессии

  • • коэффициент детерминации R2 = 0,826;
  • • скорректированный коэффициент детерминации R2 = 0,739;
  • • статистическая значимость уравнения F = 9,505;
  • • коэффициент Ь{) стандартная ошибка = 1,979, /-статистика: -1,759, доверительный интервал при уровне значимости а = 0,05: (-8,327; 1,361);
  • • коэффициент Ь{. стандартная ошибка Shi = 0,412, /-статистика: 1,481, доверительный интервал при уровне значимости а = 0,05: (-0,398; 1,62);
  • • коэффициент Ь2: стандартная ошибка Slh = 0,629, /-статистика: -0,066, доверительный интервал при уровне значимости а = 0,05: (-1,58; 1,498);
  • • коэффициент Ь-л: стандартная ошибка = 0,868 , /-статистика: 0,708, доверительный интервал при уровне значимости а = 0,05: (-1,51; 2,742).

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

Вывод. Результаты работы инструмента «Регрессия» полностью совпадают с ранее полученными аналитическими вычислениями.

Пример 4.8

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

Таблица 4.9

Исходные данные к примеру 4.8

Выручка

Время

Накладные

расходы

Средняя цена у конкурентов

Цена

Издержки

производства

У

*2

*3

*5

134

1

4,8

18,0

17,9

95,0

137

2

4,0

18,1

18,5

96,5

19

3

3,8

18,2

18,9

97,5

142

4

3,9

18,3

18,4

96,0

145

5

4,3

18,2

18,3

97,0

148

6

4,3

18,0

18,5

98,5

156

7

4,5

18,9

18,8

97,0

157

8

4,6

17,9

18,5

96,5

152

9

4,7

18,2

18,8

98,5

148

10

4,9

18,3

18,9

98,0

151

11

4,7

19,1

18,9

97,5

153

12

4,9

18,4

18,7

96,5

160

13

5,0

17,9

18,3

98,5

161

14

5,1

18,5

18,5

99,0

162

15

5,3

18,9

18,8

97,5

159

16

5,5

19,0

19,1

98,0

163

17

5,7

18,7

18,7

96,5

172

18

5,2

18,9

19,0

97,5

174

19

.5,9

18,6

19,0

98,5

179

20

6,1

19,0

19,2

97,5

Решение. Для проведения регрессионного анализа используем инструмент Регрессия» (надстройка «Анализ данных» в Microsoft Excel).

1. Построение уравнения регрессии.

На первом шаге строим модель линейной регрессии но всем пяти факторам: Фрагмент протокола регрессионного анализа приведен в табл. 4.10.

Модель регрессии по пяти факторам

Таблица 4.10

Коэффициенты

Стандартная

ошибка

t-стати - стика

Р-значение

Нижние

95%

Верхние

95%

К-пересечеиие

744,44

965,35

0,77

0,45

-1326,03

2814,90

*1

3,432

3,32

1,03

0,32

-3,69

10,56

х2

10,64

24,90

0,43

0,67

-42,76

64,05

х3

20,24

25,09

0,80

0,43

-33,59

74,06

ХА

-60,75

32,31

-1,88

0,08

-130,06

8,55

*5

0,80

7,98

0,10

0,92

-16,31

17,92

На первом шаге все коэффициенты уравнения регрессии незначимы при 5%-ном уровне значимости. Необходимо исключить один из факторов и вновь построить уравнение регрессии. Из модели исключаем тот фактор, который имеет наименьшую по абсолютной величине значимость t, а именно х5.

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

Таблица 4.11

Модель регрессии по четырем факторам

Коэффициенты

Стандартная

ошибка

t-статистика

Р-зна-

чение

Нижние

95%

Верхние

95%

К-пересечение

825,68

510,64

1,62

0,13

-262,73

1914,09

*1

3,59

2,84

1,26

0,22

-2,45

9,63

*2

9,82

22,71

0,43

0,67

-38,59

58,23

*3

19,21

22,16

0,86

0,39

-28,02

66,44

ХА

-59,78

29,79

-2,01

0,06

-123,27

3,72

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

Таблица 4.12

Модель регрессии по трем факторам

Коэффициенты

Стандартная

ошибка

t-стати-

стика

Р-зна-

чение

Нижние

95%

Верхние

95%

Y- пересечение

928,32

440,43

2,11

0,05

-5,33

1861,98

*

4,66

1,32

3,52

0,01

1,86

7,47

*3

20,32

21,44

0,95

0,36

-25,13

65,78

ХА

-64,43

27,07

-2,38

0,03

-121,81

-7,04

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

Таблица 4.13

Модель регрессии по двум факторам

Коэффициенты

Стандартная

ошибка

f-стати-

стика

Р-зна-

чение

Нижние

95%

Верхние

95%

У-пересечение

1047,29

420,90

2,49

0,02

159,26

1935,32

*1

5,09

1,24

4,09

0,00

2,47

7,71

ХА

-50,96

22,97

-2,22

0,04

-99,42

-2,49

Получено уравнение линейной множественной регрессии, все коэффициенты которого значимы при 5%-ном уровне значимости:

2. Оценка качества модели.

Для оценки качества модели вычисляем коэффициент детерминации R2 и коэффициент множественной корреляции (индекс корреляции) R. Чем ближе к единице значение этих коэффициентов, тем выше качество модели.

В таблице «Регрессионная статистика» значение коэффициента множественной корреляции приводится в первой строке, во второй строке — значение коэффициента детерминации, в третьей строке — значение скорректированного коэффициента детерминации (формула (4.13)) (рис. 4.3). Остальные результаты программы «Регрессия» разобраны в гл. 3.

Вывод результатов решения примера 4.8

Рис. 4.3. Вывод результатов решения примера 4.8

Значения этих коэффициентов можно вычислить по следующим формулам.

Коэффициент детерминации:

Он показывает долю разброса результативного признака под воздействием изучаемых факторов Л'! и х2. Следовательно, только половина вариации зависимой переменной учтено в модели и обусловлено влиянием Факторов, включенных в модель.

Коэффициент множественной корреляции:

Он показывает тесноту связи зависимой переменной у с двумя включенными в модель объясняющими факторами.

Точность модели оцепим с помощью средней ошибки аппроксимации:

Модель неточная. Фактические значения объема реализации продукции отличаются от расчетных на 28,2%.

3. Оценка значимости модели.

Проверку значимости модели произведем на основе /''-критерия Фишера:

Это же значение можно взять из таблицы «Дисперсионный анализ» (см. рис. 4.3). Табличное значение /^-критерия при доверительной вероятности у = 0,95 и числах степеней свободы = т =2 и v2 = п - т - 1 = 17 составляет 3,591.

Поскольку F> FKр, уравнение регрессии у{ = 1047,29 +5,09^ -50,96дг4 следует признать значимым, т.е. его можно использовать для анализа и прогнозирования.

4. Оценка значимости коэффициентов модели.

Оценку значимости коэффициентов модели можно непосредственно взять из таблицы «Дисперсионный анализ» (см. рис. 4.3), где получено ?rj =4,095, tXA =-2,218. Табличное значение ^-критерия при доверительной вероятности у = 0,95 и числе степеней свободы v = п - т - 1 = 17 составляет 2,109. Оба коэффициента значимы.

Значимость коэффициентов регрессии можно получить расчетным путем:

где — несмещенная оценка дисперсии; а}] диагональный элемент

матрицы А = (ХТХ) К

5. Доверительные интервалы коэффициентов регрессии.

Зная дисперсии коэффициентов и ?кр = 2,109, можно найти доверительные интервалы для коэффициентов при объясняющих переменных ху и хл:

Нижние и верхние 95%-ные границы имеют одинаковые знаки, следовательно, коэффициенты 6, и 64 значимы.

6. Экономическая интерпретация коэффициентов модели.

В результате применения всех подходов к выбору значимых факторов регрессии получено, что в модели остаются два фактора: х{ время и .г4 — цена. Последний имеет отрицательный знак. Экономически очевидно, с ростом цены спрос на продукцию падает.

Коэффициенты b} (b{ и bA) показывают, на какую величину в среднем изменится результативный признак У, если переменную х-} увеличить на единицу измерения, т.е. bj является нормативным коэффициентом.

Вывод. По условию задачи на результативный признак влияют дата выпуска и отпускная цена продукции.

Пример 4.9

Проверим значимость уравнения множественной линейной регрессии по данным, приведенным в табл. 4.14.

Таблица 4.14

Исходные данные к примеру 4.9

i

1

2

3

4

5

6

7

8

9

10

И

12

Х

4

5

6

54

7

8

3

5

6

4

8

7

х2

6

7

4

8

3

5

4

6

5

7

6

9

У

10

15

20

15

25

40

35

65

45

35

50

45

Решение. Уравнение множественной линейной регрессии, полученное но МНК:

Коэффициент детерминации R2 = 0,126, скорректированный коэффициент детерминации R2 = -0,067, наблюдаемое значение статистики Фишера F = 0,65.

Уравнение незначимо: коэффициент детерминации имеет малую величину, ^кр(0’05; 2; 9) = 4,256. Обращаем внимание па то, что скорректированный коэффициент детерминации имеет отрицательный знак.

* * *

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

 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ     След >