ЕКОНОМІКО-МАТЕМАТИЧНЕ МОДЕЛЮВАННЯ

КАФЕДРА ЕКОНОМІЧНОЇ КІБЕРНЕТИКИ

Лабораторна робота №7

Тема: Обчислення параметрів парної лінійної регресії засобами MS Excel

Мета роботи: навчитися будувати лінійну економіко-математичну модель засобами MS Excel, перевіряти її статистичну значущість та виконувати прогноз.

Приклад розв’язання задачі

На основі статистичних даних (таблиця 1) про витрати на одиницю продукції Y_i та рівень фондомісткості продукції X_i, побудувати економетричну модель залежності витрат на одиницю продукції від рівня фондомісткості.

Таблиця 1

№ п/п	Yi	Xi
1	50	90
2	40	75
3	65	120
4	55	100
5	45	80
6	42	78
7	56	110
8	60	115
9	64	115
10	65	125
прог		123

Знайти оцінки коефіцієнтів моделі, коефіцієнту кореляції. Оцінити адекватність моделі статистичним даним. Оцінити значущість коефіцієнтів моделі та коефіцієнту кореляції. Якщо отримана модель відповідає статистичним даним, то побудувати точковий та інтервальний прогнози, значення коефіцієнту еластичності. На основі отриманої моделі зробити висновки.

Розв’язання:

Ідентифікуємо дані:

Y – витрати на одиницю продукції (залежний фактор);

Х – рівень фондомісткості (незалежний фактор).

Специфікуємо економетричну модель лінійною функцією виду

,

де а₀, а₁ – коефіцієнти лінійної моделі, е – випадкова складова.

Для того, щоб обчислити параметри лінійної регресії, в MS Excel передбачена вбудована функція ЛИНЕЙН, яка обчислює основні параметри регресії (коефіцієнти регресії, стандартні похибки коефіцієнтів, коефіцієнт детермінації, стандартну похибку, критерій Фішера, сума квадратів різниць між фактичним та середнім значенням фактора Y та суму квадратів залишків). Всі інші параметри можуть бути обчислені згідно означень.

Застосувавши функцію ЛИНЕЙН, одержимо:

a1	0,4996	3,8444	a0
Sa1	0,0323	3,3112	Sa0
R2	0,9675	1,8234	E
F	238,5020	8	n-k
S2рег	793,0006	26,5994	S2зал

В результаті обчислень отримано:

a₁=0,4996 а₀=3,8444

S_a1=0,0323 S_a0=3,3112

R²=0,9675 Е=1,8234

F=238,50 n-k=8

S²_рег=793,001 S²_зал=26,599

Для обчислення табличного значення критерію Фішера скористаємося вбудованою в MS Excel функцією FРАСПОБР, де:

Вероятность =0,05;

Степени_свободы1 =k-1=1;

Степени_свободы2 =n-k=8.

Одержимо табличне значення критерію Фішера F_таб = 5,3176.

Так як F_роз>F_таб, то отримана економетрична модель достовірна, або така, що відповідає статистичним даним. Згідно критерію Фішера, отримана модель достовірна.

Оцінимо згідно t-критерію Стьюдента значущість коефіцієнтів моделі а₁ та а₀.

За означенням t-критерії для коефіцієнтів а₁ та а₀:

=15,444;

=1,161.

Порівняємо одержані t-критерії для коефіцієнтів моделі з табличним значенням критерію Стьюдента. Для обчислення табличного значення можна скористатися вбудованою функцією СТЬЮДРАСПОБР на рівні значимості 0,95 та числом ступенів вільності n-k=8: t_таб=2,3060.

Так як t_a1>t_таб, то отримане значення коефіцієнта а₁ достовірне з ймовірністю 0,95.

Так як t_a0<t_таб, то отримане значення коефіцієнта а₀ статистично не відрізняється від 0. Це значить, що отримана оцінка коефіцієнта а₀ є не точною, або зміщеною, що свідчить, можливо, про недостатню кількість спостережень (n=10).

Достовірним є значення коефіцієнта а₁ згідно критерію Стьюдента. Згідно цього ж критерію коефіцієнт а₀ статистично не відрізняється від 0.

Розглянемо коефіцієнт детермінації R² та коефіцієнт кореляції r.

З масиву даних, одержаних в результаті обчислень за допомогою функції ЛИНЕЙН, одержимо R²=0,9675, або 96,75% вибіркових даних описуються знайденою регресією.

Коефіцієнт кореляції r знайдемо використовуючи вбудовану функцію КОРРЕЛ (массив1; массив2), де массив1 – це фактичні значення вихідного масиву Х, массив2 – фактичні значення вихідного масиву Y. Отримаємо r=0,9836. Значення коефіцієнту кореляції близькі до 1, що свідчить про тісний зв’язок між факторами Х та Y.

Знайдемо прогнозне значення вибіркових даних в точці X_пр=123 та обчислимо 95% надійний інтервал отриманого прогнозу. Середнє значення прогнозу (точковий прогноз) обчислимо, підставляючи в рівняння регресії прогнозне значення фактора Х:

Y_пр=65,2902

Для знаходження 95% надійного інтервалу прогнозу (інтервальний прогноз), обчислимо похибку прогнозу: Y_пр=4,7107. Тоді Y_min=60,5795; Y_max=70,001.

Обчислимо коефіцієнт еластичності: k=0,9291.