Хід роботи.

1. Для побудови корелфційної матриці в Excel будемо використовувати інструмент аналізу данихКорреляция. Для цього:

1. в головному меню послідовно виконаємо команди Сервис – Анализ данных – Корреляция – Ок (якщо інструмент аналізу даних не встановлений, то спочатку виконати послідовність командСервис – Надстройки – Пакет анализа – Ок).

2. заповнити діалогове вікно Корреляция необхідними параметрами введення мал. 3.1:

Малюнок 3. 1 Діалогове вікно Корреляция

,

де Входной интервал – вказується вся область, яка містить початкові статистичні дані,Метки в первой строке – прапорець вказує, що в першому рядку даних знаходяться пояснювальні заголовки,Выходной интервал– вказується адрес комірки, починаючи з якої будуть показані результати.

3. результати розрахунків – матриці коефіцієнтів парної кореляції – наведені на мал. 3.2:

Малюнок 3. 2 Матриця коефіцієнтів парної кореляції (Кореляційна матриця)

4. для проведення розрахунків кореляційну матрицю необхідно добудувати. Це можна зробити, використовуючи аппарат Excel копіювання і спеціальну вставку: виділити необхідні комірки –Правка – Копировать – виділити необхідну комірку, починаючи з якої будуть вставлені дані –Правка – Специальная вставка – флажок Транспонировать – Ок мал. 3.3:

Малюнок 3. 3 Кореляційна матриця.

Кореляційна матриця має наступний вигляд:

.

Факторна кореляційна матриця:

.

Кожний елемент цієї матриці характеризує щільність зв’язку однієї незалежної змінної з іншою. Оскільки діагональні елементи характеризують щільність зв’язку кожної незалежної змінної з самою собою, то вони дорівнюють одиниці.

Визначник факторної кореляційної матриці можна отримати за допомогою функції МОПРЕД(), в якості аргументів якої необхідно виділяти область значень парних лінійних міжфакторних коефіцієнтів (для приклада, що рішається C26:E28):

0,02182.

2. Користуючись елементами факторної кореляційної матриці можна зробити висновок, що між факторами Х₁, Х₂, Х₃існує зв’язок. Перевіримо, чи є цей зв’язок виявленням мультиколінеарності.

Визначник кореляційної матриці r: detr₁₁=0,022. Оскільки він наближається до 0, то в масиві пояснюючих змінних може існувати мультиколінеарність.

Обчислимо χ²=-[20-1-(2∙3+5)/6]∙Ln(0,022)=65,66.

При ступені свободи m(m-1)=3 і рівні значущості α=0,05 знаходимо критичне значення, використовуючи функцію ХИ2ОБР() χ²_кр=7,8. Оскільки χ²> χ²_кр, то в масиві пояснювальних змінних (продуктивність праці, питомі інвестиції та фондовіддача) існує мультиколінеарність.

Знайдемо матрицю, обернену до факторної кореляційної матриці, використовуючи функцію МОБР(). Для використання цієї функкції виконують наступні кроки:

1. Виділяють область комірок, де будуть розташовані значення результату виконання цієї функції, скільки значень – стільки комірок;

2. Використовуючи Мастер функций (Вставка – Функция) викликають функцію МОБР() і заповнюють її параметри, виділяючи область комірок, де знаходяться значення факторної кореляційної матриці;

3. Натискають послідовність клавіш Ctrl – Shift – Enter,

C=.

Використовуючи діагональні елементи матриці С обчислимо F – критерії:

F₁=(c₁₁-1)=(10,67-1)=82,21;

F₂=(c₂₂-1)=(9,39-1)=71,34;

F₃=(c₃₃-1)=(5,169-1)=35,44.

Для рівня значущості α=0,05 і ступені вільності m-1=2 іn-m=17 критичне (табличне) значення критерію (функціяFРАСПОБР())F_кр.=3,59. Оскільки кожне з розрахованих значеньF_iбільше ніж критичне, то кожна з пояснювальних змінних мультиколінеарна з іншими.

Використовуючи елементи матриці С, обчислимо часткові коефіцієнти кореляції:

r₁₂==0,737;

r₁₃==0,411;

r₂₃==0,237.

Порівнявши часткові коефіцієнти кореляції з парними (кореляційна матриця), можна помітити, що частинні коефіцієнти значно менш за парні. Це ще раз показує, що на підставі парних коефіцієнтів кореляції не можна зробити висновків про наявність або відсутність мультиколінеарності.

Визначимо t – критерії на основі частинних коефіцієнтів кореляції:

t₁₂==4,49;

t₁₃==1,86;

t₂₃==1,005.

Табличне значення t– критерію приn-m=17 ступенях вільності і рівні значущості 0,05 дорівнює 2,12 (функція СТЬЮДРАСПОБР()). Звідси виходить, що лише для пари факторів Х₁і Х₂ існує мультиколінеарність. Розрахував вибіркові коефіцієнти кореляції, що пов’язують пояснювальні фактори з залежнимY, знайдемо=0,944,=0,934, оскільки>, з моделі виключаємо факторX₂.

Знайдемо кореляційну матрицю факторів Х₁і Х₃:

r=.

Обернена матриця:

C=.

Значення χ²=27,74 більше ніж χ²_кр= 3,8. Це значить, що між факторамиX₁іX₃існує мультиколінеарність. Це підтверджує і значенняt – статистики:

r₁₃=0,8917, t₁₃=8,356,

оскільки воно більш ніж 2,11. Тобто з моделі необхідно виключити ще один з факторів. Оскільки =0,916<=0,944, то таким фактором є факторX₃.

Отже з моделі виключили два фактори X₂іX₃.

Зауваження: Якщо при перевірці наявності мультиколінеарності визначається зв’язок, наприклад, між парами факторів Х₁і Х₂та Х₂і Х₃, то з розгляду виключається той фактор, який приймає участь одночасно в першій та другій парах. Так для нашого приклада таким фактором є Х₂.

3. Припустимо, що між показником Y і факторомX₁ існує лінійна залежність:

Знайдемо оцінки параметрів, використовуючи матричні операції: Перш ніж проводити розрахунки добавимо до залишившихся факторних значень стовпчик, який містить одиниці. Їх кількість повинна дорівнювати обсягу вибірки, тобтоn:

Малюнок 3. 4 Вигляд матриці кінцевої матриці Х

Для розрахунків викоритсовуються функціїї:

МОБР() – для знаходження зворотньої матриці,

МУМНОЖ() – для знаходження добутку двох матриць.

Дії з цими функціїї відповідні діям, що описані вище для функції МОБР().

Всі необхідні дії виконані нижче:

Малюнок 3. 5 Розрахунки параметрів моделі.

Для матричних розрахунків в Excel використовувались наступні формули:

МУМНОЖ(ТРАНСП(P27:Q46);P27:Q46);

МОБР(U27:V28);

МУМНОЖ(ТРАНСП(P27:Q46);S27:S46);

B=МУМНОЖ(U31:V32;U34:U35).

Отже економетрична модель має вигляд: 25,336+1,192X₁.

4. Коефіцієнт детермінації знайдемо на основі матриці оберненої до розширеної кореляційної матриці (кореляційної матриці) (вона містить парні вибіркові коефіцієнти кореляції не тільки між пояснювальними змінними, але й У):

=0,89.

Для розрахунку множинного коефіцієнта детермінації можна використовувати формулу:

=0,89.

Множиний коефіцієнт кореляції =0,943.

Скорегований коефіцієнт (індекс) множинної детермінації розраховується за формулою:

0884,

4. Розрахуємо значення F – критерію:

F_R=(c₁₁-1)=(9,1 - 1)=146 або

.

Побудована модель є адекватною, оскільки обчислене за F– критерієм значення більш ніж критичнеF_(0,05;1;18)=4,41). Це означає також, що коефіцієнт множинної детермінації значущ.

4. Розрахуємо значення незміщеної оцінки дисперсії залишків у матричному вигляді:

1,833.

Обчислимо дисперсійно – коваріаційну матрицю

var()==.

Малюнок 3. 6 Розрахунок дисперсійно-кореляційної матриці

Стандартні помилки оцінок параметрів моделі =1,828,=0,0987, знайдено обчисливши квадратні корні від чисел 3,34235 і 0,00974 відповідно.

Знайдемо значення tстатистики:

t_b0===13,8584, =12,0753, тобто параметри є статистично значущими, оскільки ці значення більш ніжt_кр=2,101, яке знайдено при рівні значущості α=0,05 і ступені вільностіn-m-1=18, деn– обсяг вибірки,m– кількість незалежних факторів.

4. Знайдемо точкове значення прогнозу для , що дорівнює 19,85, для цього це значення підставимо в рівняння отриманої моделі:

.

4. Щоб отримати інтервальний прогноз необхідно розрахувати стандартну помилку прогнозу індивідуального значення:

=3,964.

Звідси 49–3,96449+3,964, тобто 45,0452,96.

4. У нас залишився тільки один незалежний фактор Х₁. Значення часткового коефіцієнта еластичності для точки прогнозу знаходиться за формулою:

=0,48.

Всі розрахунки в Excel наведені нижче:

Малюнок 3. 7 Розрахунки для точкового і інтервального прогнозного значення тачастковго коефіцієнта еластичності.