Расчетное задание 1 «Построение уравнений парной регрессии и оценка их значимости»

По территориям Уральского и Западно-Сибирского регионов известны данные за ноябрь 1997 г. по двум признакам: потребительские расходы на душу населения,тыс.руб. и средняя заработная плата и выплаты социального характера,тыс.руб. (табл. 1)

Таблица 1

Район	Потребительские расходы на душу населения,тыс.руб.,у	Средняя заработная плата и выплаты социального характера,тыс.руб.,х
Уральский
Респ.Башкортостан	461	912
Удмуртская Респ.	524	809
Курганская обл.	298	748
Оренбургская обл.	351	847
Пермская обл.	624	1087
Свердловская обл.	584	1074
Челябинская обл.	425	1008
Западно-Сибирский
Респ.Алтай	277	682
Алтайский край	321	697
Кемеровская обл.	573	1251
Новосибирская обл.	576	967
Омская обл.	588	898
Томская обл.	497	1263
Тюменская обл.	863	3027

Задание:

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнений линейной, степенной, гиперболической парной регрессии.

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

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

5. Оцените статистическую надежность результатов регрессионного моделирования с помощью F-критерия Фишера. По значениям характеристик, рассчитанных в пп. 3, 4 и данном пункте, выберите лучшее уравнение регрессии и дайте его обоснование.

6. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 5% от его среднего уровня.

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

Решение

Решение задачи производим с помощью пакета прикладных программ (ППП) Excel.

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

1. Введите в столбцы В и С таблицы данные по признакам y и x, на основании которых постройте поле корреляции, для чего в главном меню выберите Сервис/Анализ данных/Корреляция (рис. 2).

Значит, r_xy=0,753473 – линейный коэффициент парной корреляции. Чем больше r_xy≈ 1, тем теснее связь , в нашем случае r = 0,75 то можно предположить, что в нашем случае связь между параметрами очень тесная.

2. Рассчитайте параметры уравнений парной регрессии.

а) Для расчета параметров a и b линейной регрессии y=a+b*x решаем систему нормальных уравнений относительно a и b:

n*a + b*∑x=∑y;

a*∑x + b∑x²=∑y*x;

Рис.1. Подключение к Пакету анализа

Рис. 2. Результат применения инструмента Корреляция

По исходным данным, внесенным в таблицу ППП Excel, рассчитываем

Далее рассчитываем:

; ;

;

.

Тесноту связи изучаемых явлений оценивает линейный коэффициент парной корреляции для линейной регрессии : .

Долю дисперсии, объясняемую регрессией, в общей дисперсии результативного признака y характеризует коэффициент (индекс) детерминации – квадрат коэффициента корреляции.

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

Для определения средней ошибки аппроксимации – среднего отклонения расчетных значений от фактических используют формулу:

.

– теоретические значения признака.

Все значения, рассчитанные в таблице приведены на рис. 3.

Рис. 3. Параметры линейной регрессии для Уральского региона

Тогда уравнение регрессии для уральского региона имеет вид: .

В ППП “Excel” есть встроенная статистическая функция ЛИНЕЙН, которая позволяет определить параметры линейной регрессии . Порядок вычисления следующий:

1) выделите область пустых ячеек 5x2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики;

2) активизируйте Мастер функций;

3) в окне Категория (рис. 4) выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните Ok;

4) заполните аргументы функции (рис. 5):

Известные_ значения_y – диапазон, содержащий данные результативного признака;

Известные_ значения_x– диапазон, содержащий данные факторов независимого признака;

Рис. 4. Диалоговое окно “Мастер функций”

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа=1, то свободный член рассчитывается обычным образом, если Константа=0, то свободный член равен 0;

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. если Статистика=1, то дополнительная информация выводится, если Статистика=0, то выводятся только оценки параметров уравнения.

Щелкните по кнопке Ok;

5) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу F2, а затем на комбинацию клавиш Ctrl+Shift+Enter.

Рис. 5. Диалоговое окно ввода аргументов функции ЛИНЕЙН

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

Значение коэффициента b	Значение коэффицента a
Среднеквадратическое отклонение b	Среднеквадратическое отклонение a
Коэффициент детерминации R2	Среднеквадратическое отклонение y
F-статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

На рис. 3 получена дополнительная регрессионная статистика для Уральского региона, она представлена в таблице 2.

Таблица 2

Значение коэффициента b 0,675	Значение коэффициента a -158,79
Среднеквадратическое отклонение b 0,263	Среднеквадратическое отклонение b 246,25
Коэффициент детерминации R 0,568	Среднеквадратическое отклонение y 85,93
F-статистика 6,567	Число степеней свободы 5,00
Регрессионная сумма квадратов 48485,427	Остаточная сумма квадратов 36918,00

Аналогичный расчет для Западно-Сибирского региона представлен на рис. 6, а дополнительная регрессионная статистика для Западно-Сибирского региона представлена в таблице 3.

Таблица 3

Значение коэффициента b 0,20482	Значение коэффициента a 276,86
Среднеквадратическое отклонение b 0,05464	Среднеквадратическое отклонение b 80,02807
Коэффициент детерминации R 0,737	Среднеквадратическое отклонение y 109,14872
F-статистика 14,0512	Число степеней свободы 5
Регрессионная сумма квадратов 167398	Остаточная сумма квадратов 5967,216767

б) Построению степенной модели предшествует процедура линеаризации переменных. В примере линеаризация производится путем логарифмирования обеих частей уравнения:

;

,

где .

;

.

Тогда уравнение регрессии для Западно-Сибирского региона имеет вид:

Индекс корреляции рассчитаем по следующей формуле:

.

Параметры степенной регрессии для Уральского и Западно-Сибирского регионов приведены на рисунках 7, 8 соответственно.

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

где .

; .

Рис. 6. Параметры линейной регрессии для Западно-Сибирского региона

Индекс корреляции рассчитаем по следующей формуле:

.

Параметры показательной регрессии для Уральского и Западно-Сибирского регионов приведены на рисунках 9, 10 соответственно.

Рис. 7. Параметры степенной регрессии для Уральского региона

Тогда уравнение регрессии для уральского региона имеет вид:

=10^-1,466*x^1,391=0,034*x^1,391

Рис. 8. Параметры степенной регрессии для Западно-Сибирского региона

Тогда уравнение регрессии имеет вид:

Рис. 9. Параметры показательной регрессии для Уральского региона

Тогда уравнение регрессии для уральского региона имеет вид:

Рис. 10. Параметры показательной регрессии для Западно-Сибирского региона

Тогда уравнение регрессии имеет вид:

г) Уравнение гиперболической парной регрессии

линеаризуется при замене: , тогда .

; ;

.

Параметры гиперболической регрессии для Уральского и Западно-Сибирского регионов приведены на рисунках 11, 12 соответственно. На рисунке 11 получена дополнительная регрессионная статистика для Уральского региона, она представлена в таблице 4, а на рисунке 12 получена дополнительная регрессионная статистика для Западно-Сибирского региона, она представлена в таблице 5.

Таблица 4

Значение коэффициента b -553572,272	Значение коэффицента a 1075,110
Среднеквадратическое отклонение b 223184,581	Среднеквадратическое отклонение b 247,508
Коэффициент детерминации R 0,552	Среднеквадратическое отклонение y 87,511
F-статистика 6,152	Число степеней свободы 5
Регрессионная сумма квадратов 47112,988	Остаточная сумма квадратов 38290,441

Таблица 5

Значение коэффициента b -449755,431	Значение коэффицента a 972,710
Среднеквадратическое отклонение b 88006,326	Среднеквадратическое отклонение b 93,451
Коэффициент детерминации R 0,838	Среднеквадратическое отклонение y 85,885
F-статистика 25,770	Число степеней свободы 5
Регрессионная сумма квадратов 190083,810	Остаточная сумма квадратов 36881,047

Рис. 11. Параметры гиперболической регрессии для Уральского региона

Тогда уравнение регрессии для уральского региона имеет вид:

ŷ=1075,110-553572,272*1/x;

Рис. 12. Параметры гиперболической регрессии для Западно-Сибирского региона

Тогда уравнение регрессии для Западно-Сибирского региона примет вид:

y= 972,710-446755,431*1/x.

3. Для выбора лучшей модели построим сводную таблицу результатов. Для уральского региона она представлена на рис. 13, а для Западно-Сибирского – на рис. 14.

Рис. 13. Сводная таблица результатов для Уральского региона

Рис. 14. Сводная таблица результатов для Западно-Сибирского региона

4. Все модели для Уральского региона имеют примерно одинаковые характеристики, но большее значение F-критерия Фишера и большее значение коэффициента детерминации R² имеет показательная модель. Ее можно взять в качестве лучшей для построения прогноза.

Для Западно-Сибирского региона большее значение F-критерия Фишера и большее значение коэффициента детерминации R² имеет гиперболическая модель. Ее можно взять в качестве лучшей для построения прогноза.

5. Статистическую надежность результатов регрессионного моделирования оценим применив инструмент Регрессия ППП Excel. Результаты регрессионного анализа для Уральского и Западно-Сибирского регионов приведены соответственно на рис. 15, 17, таблицы остатков на рис. 16, 18, а графики остатков на рис.19, 20.

Рис. 15. Результат применения инструмента Регрессия для Уральского региона

Рис. 16. Таблица остатков для Уральского региона

Рис. 17. Результат применения инструмента Регрессия для Западно-Сибирского региона

Рис. 18. Таблица остатков для Западно-Сибирского региона

Рис.19. График остатков для Уральского региона

Рис. 20. График остатков для Западно-Сибирского региона

6. Расчет прогнозного значения результативного показателя для Уральского региона:

Прогнозное значение результативного признака определим по уравнению показательной модели, подставив в него планируемую (заданную по условию) величину:

X_ПР=X*1,05=926,43*1,05=972,7515

=775,9024 тыс.руб.

Расчет прогнозного значения результативного показателя для Западно-Сибирского региона:

Прогнозное значение результативного признака определим по уравнению гиперболической модели, подставив в него планируемую (заданную по условию) величину:

X_ПР=X*1,05=926,43*1,05=972,7515

972,710-446755,431*1/X_ПР=972,710-446755,431*1/1255= =616,7296 тыс. руб.

7. Было рассчитано поле корреляции, сформулирована гипотеза о форме связи – связь между параметрами очень тесная.

Были рассчитаны следующие модели уравнений регрессии: линейная, степенная, показательная, экспоненциальная, гиперболическая, полулогарифмическая и обратная. Для них были рассчитаны соответствующие параметры: индекс корреляции (r_xy), коэффициент детерминации (r²_xy), средняя ошибка аппроксимации (А), F-критерий Фишера. На основе этих показателей была выбрана лучшая модель уравнения регрессии и рассчитано прогнозное значение результата.