Технология решения задач корреляционного и регрессионного анализа с помощью пакета анализа

Пакет анализа - это надстройка, которая представляет широкие возможности для проведения статистического анализа.

Установка средств Пакет анализа.

В стандартной конфигурации программы EXCEL вы не найдете средства Пакет анализа. Даже если установить их с компакт-диска EXCEL'97 (или Office'97), они не появятся в меню до тех пор, пока вы не установите их в качестве надстройки Excel. Для этого выполните следующие действия:

1. Выберите команду Сервис=>Надстройки.

2. В диалоговом окне Надстройки установите флажок Пакет анализа.

3. Щелкните на кнопке ОК.

После этого в нижней части меню Сервис появится новая команда Анализ данных. Эта команда предоставляет доступ к средствам анализа, которые есть в EXCEL.

Пример 2.2.1. Задача состоит в построении модели для предсказа­ния объема реализации одного из продуктов фирмы.

Объем реализации - это зависимая переменная Y. В качестве независимых, объясняющих переменных выбраны: время – Х₁, расходы на рекламу Х₂, цена товара Х₃, средняя цена конкурентов X₄, индекс потребительских расходовX₅.

1. Построение системы показателей (факторов). Анализ матрицы коэффициентов парной корреляции

Статистические данные по всем переменным приведены в табл. 2.2.1. В этом примере n = 16, m = 5.

Таблица 2.2.1

Использование инструмента Корреляция. Для проведения корреляционного анализа выполните следующие действия:

1) данные для корреляционного анализа должны располагаться в смежных диапазонах ячеек;

2) выберите команду Сервис =>Анализ данных;

3) в диалоговом окне Анализ данных выберите инструмент Корреляция (рис. 4.2.1). а затем щелкните на кнопке ОК;

4) в диалоговом окне Корреляция в поле «Входной интервал» необходимо ввести диапазон ячеек, содержащих исходные данные. Если выделены и заголовки столбцов, то установить флажок «Метки в первой строке» (рис. 4.2.2);

5) выберите параметры вывода. В данном примере - установите переключатель «Новый рабочий лист»;

6) ОК.

В табл. 2.2.2 приведены промежуточные результаты при вычисле­нии коэффициента корреляции по формуле (2.1.1)

Таблица 2.2.2

t	Y	X2	)	)2	)	)2	)× )
		4.8	-180.813 -169.813	32693.16 28836.29	-5.29375 -4.49375	28.02379 20.19379	957.1762 763.0949
		3.8	-158.813	25221.41	-5.49375	30.18129	872.4762
		8.7	-115.813	13412.54	-0.59375	0.352539	68.76367
		8.2	-32.8125	1076.66	-1.09375	1.196289	35.88867
		9.7	63.1875	3992.66	0.40625	0.165039	25.66992
		14.7	125.1875	15671.91	5.40625	29.22754	676.7949
		18.7	138.1875	19095.79	9.40625	88.47754	1299.826
		19.8	60.1875	3622.535	10.50625	110.3813	632.3449
		10.6	60.1875	3622.535	1.30625	1.706289	78.61992
		8.6	14.1875	201.2852	-0.69375	0.481289	-9.84258
		6.5	0.1875	0.035156	-2.79375	7.805039	-0.52383
		12.6	24.1875	585.0352	3.30625	10.93129	79.96992
		6.5	38.1875	1458.285	-2.79375	7.805039	-106.686
		5.8	57.1875	3270.41	-3.49375	12.20629	-199.799
		5.7	77.1875	5957.91	-3.59375	12.91504	-277.393
Сумма		148.7		158718.4		362.0494	4896.381
Среднее значение	306.8125	9.29375

Таблица 2.2.3

Анализ матрицы коэффициентов парной корреляции (табл. 2.2.3) показывает, что зависимая переменная, т.е. объем реализации, имеет тесную связь с индексом потребительских расходов (r_yx5 = 0.816), с расходами на рекламу (r_yx5=0.646) и со временем (r_yx1 =0.678). Однако факторы X₂; и X₅ тесно связаны между собой (r_yx5= 0.96), что свидетельствует о наличии мультиколлинеарности. Из этих двух переменных ос­тавим в модели X₅ - индекс потребительских расходов. В этом примере n= 16, m = 5, после исключения незначимых факторов п = 16, k = 2.

2. Выбор вида модели и оценка ее параметров

Оценка параметров регрессии осуществляется по методу наимень­ших квадратов по формуле (2.1.6), с использованием данных, приведен­ных в табл. 2.2.4.

Таблица 2.2.4

Уравнение регрессии зависимости объема реализации от затрат на рекламу и индекса потребительских расходов можно записать в сле­дующем виде:

Y= -1471.314 + 9.568Х₁ + 15.754Х₂.

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

Применение инструмента Регрессия. Для проведения регрессионного анализа выполните следующие действия:

1) выберите команду Сервис ÞАнализ данных;

2) в диалоговом окне Анализ данных выберите инструмент Регрессия, а затем щелкните на кнопке ОК;

3) в диалоговом окне Регрессия в поле «Входной интервал Y» вве­дите адрес одного диапазона ячеек, который представляет зависимую переменную. В поле «Входной интервал X» введите адреса одного или нескольких диапазонов, которые содержат значения независимых пере­менных;

4) если выделены и заголовки столбцов, то установить флажок Метки в первой строке;

5) выберите параметры вывода. В данном примере - установите пе­реключатель «Новая рабочая книга»',

6) в поле «Остатки» поставьте необходимые флажки;

7) ОК.

Таблица 2.2.5

Пояснения к табл. 2.2.5.

Регрессионная статистика
№	Наименование в отчете EXCEL	Принятые наименования	Формула
	Множественный R	Коэффициент множественной корреляции, индекс корреляции
	R-квадрат	Коэффициент детерминации, R2
	Нормированный R-квадрат	Скорректированный R2
	Стандартная ошибка	Стандартная ошибка оценки
	Наблюдения	Количество наблюдений, n	n

Таблица 2.2.6

Пояснения к табл. 2.2.6.

	Df- число степеней свободы	SS - сумма квадратов	MS	F - критерий Фишера
Регрессия	k=2		/k
Остаток	n-k-1=l3		/(n-k-1)
Итого	n-1 =15

Таблица 2.2.7

Во втором столбце табл. 2.2.7 содержатся коэффициенты уравнения регрессии a₀, a₁, a₂. В третьем столбце содержатся стандартные ошибки ко­эффициентов уравнения регрессии (2.1.12), а в четвертом - г-статистика (2.1.11), используемая для проверки значимости коэффициентов уравне­ния регрессии.

Уравнение регрессии зависимости объема реализации от затрат на рекламу и индекса потребительских расходов, полученное с помощью EXCEL, как было указано ранее, имеет вид:

Y =-1471.314+9.568Х₁.+15.754Х₂.

Таблица 2.2.8

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

В табл. 2.2.8 приведены вычисленные по модели значения Y и зна­чения остаточной компоненты.

Проверку независимости проведем с помощью rf-критерия Дарбина-Уотсона.

В качестве критических табличных уровней при N = 16, двух объясняющих факторах при уровне значимости 5% возьмем величины d₁ =0,98 и d₂ = 1,54.

Так как расчетное значение попало в интервал от d₁ до d₂, то нельзя сделать окончательный вывод по этому критерию. Для определения сте­пени автокорреляции вычислим коэффициент автокорреляции и прове­рим его значимость при помощи критерия стандартной ошибки.

Стандартная ошибка коэффициента корреляции рассчитывается следующим образом:

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

Если r₁находится в интервале:

-0,96×0.25£r₁ £1.96×0.25,

то можно считать, что данные не показывают наличие автокорреляции первого порядка, так как

-0.49£ r₁ =0.305£0.49,

и свойство независимости выполняется.

Вычислить для модели коэффициент детерминации

=

=1-22360.104/158718.44=136358.3/158718.44=0.859.

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

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

Табличное значение F-критерия при доверительной вероятности 0,95v₁=k=2 и v₂ =n-k- 1 = 16-2- 1 = 13 составляет 4.81.

Поскольку Fрас > Fтабл. уравнение регрессии следует признать адекватным.

Значимость коэффициентов уравнения регрессии a₁, a₂ оценим с использованием t-критерия Стьюдента.

b₂₂=0.00299,

b₃₃=0.00354,

Табличное значение /-критерия при уровне значимости 5% и степенях свободы (16-2-1=13) составляет 1,77. Так как t_pac> t_табл, то коэффициенты a₁, a₂ существенны (значимы).

4. Проанализировать влияние факторов на зависимую переменную по модели (для каждого коэффициента регрессии вычислить коэффици­ент эластичности, b-коэффициент)

Учитывая, что коэффициент регрессии невозможно использовать для непосредственной оценки влияния факторов на зависимую переменную из-за различия единиц измерения, используем коэффициент эла­стичности (Э) и b -коэффициент, которые соответственно рассчитыва­ются по формулам:

Э_i = а_i × X_ср: Y_cр;

Э₁ =9.568-9.294/306.813= 0.2898;

Э₂=15.7529- 107.231 /306.813=5.506;

b_i; =, a_i× S_xi: S_y,

где

b₁=9.568-4.913/ 102.865=0.457;

b₂= 15.7529-4.5128/ 102.865=0.691.

Коэффициент эластичности показывает, на сколько процентов из­меняется зависимая переменная при изменении фактора на 1%.

Бета-коэффициент с математической точки зрения показывает, на ка­кую часть величины среднего квадратического отклонения меняется среднее значение зависимой переменной с изменением независимой переменной на одно среднее квадратическое отклонение при фиксированном на постоян­ном уровне значении остальных независимых переменных. Это означает, что при увеличении затрат на рекламу в нашем примере на 4.91 тыс. руб. объем реализации увеличится на 47 тыс. руб. (0.457-102.865).

5. Определить точечные и интервальные прогнозные оценки объе­ма реализации на два квартала вперед (t_0.7=1.12)

Прогнозные значения X₁np(17), X_2пр(18) и Х_1np(17), X_2пр(18) можно определить или вычислить на основе экстраполяционных методов.

Для фактора Х\ Затраты на рекламу выбрана модель

X₁ = 12.83 - 11.616t + 4.319t² - 0.552t³ + 0.020t⁴ - О.ООО6t⁵, по которой получен прогноз на два месяца вперед'. Графики модели временного ряда Затраты на рекламу приведены на рис. 2.2.6.

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

Х₂ = 97.008 +1.739 t- 0.0488 t².

Для получения прогнозных оценок зависимостей переменной по модели Y = -1471.438 + 9.568Х₁ + 15.754 Х₂ подставим в нее найденные прогнозные значения факторов Х₁ и Х₂.

Y₁₌₁₇ = -1471.438 + 9.568 • 5.75 + 15.754 • 112.468 = 355.399,

Y_t=18„i8=-1471.438+9.568-4.85 + 15.754- 112.488=344.179.

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

Верхняя граница прогноза: Y_пр (N + 1) + U(l),

Нижняя граница прогноза: Y_np(N+ 1) - U(l),

S_e=41.473, t_кр=2,16*, l=1, U(2)=45.749.

Результаты прогнозных оценок модели регрессии представим в таб­лице прогнозов (р =95%), табл. 2.2.9.

Таблица 2.2.9

Задача 1.

Имеются данные о деятельности крупнейших компаний США в (2.2.10).

Таблица 2.2.10

Задание

1. Рассчитайте параметры линейного уравнения множественной рег­рессии с полным перечнем факторов.

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

3. Оцените статистическую значимость параметров регрессионной модели с помощью г-критерия; нулевую гипотезу о значимости уравнения и показателей тесноты связи проверьте с помощью F-критерия.

4. Оцените качество уравнения через среднюю ошибку аппроксимации.

5. Рассчитайте матрицы парных и частных коэффициентов корреля­ции и на их основе и по г-критерию для коэффициентов регрессии отберите информативные факторы в модель. Постройте модель только с информативными факторами и оцените ее параметры.

6. Рассчитайте прогнозное значение результата, если прогнозные значения факторов составляют 80% от их максимальных значений.

7. Рассчитайте ошибки и доверительный интервал прогноза для уровня значимости 5 или 10% (а = 0,05; а = 0,10).

8. Оцените полученные результаты, выводы оформите в аналитиче­ской записке.

Задача 2.

Имеются данные о деятельности крупнейших компаний США в 1996 г. (табл. 2.2.11).

Таблица 2.2.11

1. Расчитайте параметры линейного уравнения множественной регрессии с полным перечнем факторов.

2. Дайте сравнительную оценку силы связи факторов с результатом с > средних (общих) коэффициентов эластичности.

3. Оцените статистическую значимость параметров регрессионной помощью г-критерия; нулевую гипотезу о значимости 1 и показателей тесноты связи проверьте с помощью F-критерия

4. Оцените качество уравнения через среднюю ошибку аппроксимации.

5. Рассчитайте матрицы парных и частных коэффициентов корреляции и на их основе и по t-критерию для коэффициентов регрессии отберите информативные факторы в модель. Постройте модель, информативными факторами и оцените ее параметры.

6. Рассчитайте прогнозное значение результата, если прогнозные значения факторов составляют 80% от их максимальных значений.

7. Рассчитайте ошибки и доверительный интервал прогноза для уравнения значимости 5 или 10% (а = 0,05; а = 0,10).

8. Оцените полученные результаты, выводы оформите в аналитической.

Задача 3

В табл. 2.2.12 представлены данные о рынке строящегося жилья в Санкт-Петербурге (по состоянию на декабрь 1996 г.).

Задание

1. Определите факторы, формировавшие цену квартир в строящихся |Санкт-Петербурге в 1996 г. Сгенерируйте фиктивную переменную z, отражающую местоположение квартиры и позволяющую разделить всю совокупность квартир на две группы: квартиры ч севере города (Приморский район, Шувалово-Озерки, Гражданка) и на юге города (Юго-Запад, Красносельский район).

2. Составьте матрицу парных коэффициентов корреляции:

а) исходных переменных;

б) логарифмов исходных переменных (кроме фиктивных перемен­ных). Вместо переменной х₂ используйте фиктивную переменную г

3. Постройте уравнение регрессии, характеризующее зависимость цены от всех факторов, в линейной и степенной форме. Установите какие факторы мультиколлинеарны. В какой модели мультиколлинеарность проявляется сильнее?

4. Постройте модель у = f (x₃, x₆, x₇, x₈, z) в линейной и степенной форме. Какие факторы значимо воздействуют на формирование це­ны квартиры в этой модели?

5. Существует ли разница в ценах квартир, расположенных в север­ной и южной частях Санкт-Петербурга? Является ли наличие балко­на или лоджии преимуществом квартиры на рынке? Как вы объясни­те этот факт?

Задача 4.

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

X₁ - ВВП 1997 г., % к 1990 г.;

X₂ - расходы на конечное потребление в текущих ценах, % к ВВП;

х_з - расходы домашних хозяйств, % к ВВП;

X_t - валовое накопление, % к ВВП;

Х₅ - суточная калорийность питания населения, ккал на душу населе­ния;

Х₆- ожидаемая продолжительность жизни при рождении 1997г.

число лет.

Таблица 2.2.13

Задание

1. Постройте матрицу парных коэффициентов корреляции. Рассчитайте коэффициенты множественной детерминации, используя в качестве зависимой переменной каждый фактор. Установите, какие факторы мультиколлинеарны.

2. Постройте уравнение множественной регрессии в линейной форме с полным набором факторов.

3. Оцените статистическую значимость уравнения регрессии и его параметров с помощью критериев Фишера и Стьюдента.

4. Отберите информативные факторы по пп.1 и 3. Постройте уравнение регрессии со статистически значимыми факторами.

Задание 5.

Имеются данные по странам за 1997 г. (табл. 2.2.14).

Таблица 2.2.14

Задание

1. Постройте матрицу парных коэффициентов корреляции.

2. Постройте парные уравнения регрессии.

3. Оцените статистическую значимость уравнений и их параметров с помощью критериев Фишера и Стьюдента.

4. Постройте уравнение множественной регрессии.

5. Постройте графики остатков. Сделайте выводы.

6. Проведите тестирование ошибок уравнения множественной регрессии на гетероскедастичность, применив тест Гельфельда-Квандта.

7. Оцените статистическую значимость уравнения множественной регрессии. Определите, какое уравнение лучше использовать для прогноза:

¨ парную регрессию у на х₁

¨ парную регрессию у на х ₂;

¨ множественную регрессию.

Задача 6.

Изучается зависимость средней ожидаемой продолжительности y от нескольких факторов по данным за 1995 г., представленным табл. 2.21.

Таблица 2.2.15

Поиск по сайту: