Размещено на http://www.allbest.ru/

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

Множинна лінійна регресія (мультиколінеарність)

Для виконання лабораторної роботи №3

Cтудент повинен знати:

мету і зміст даної роботи, порядок її проведення;

як знайти кореляційну матрицю;

алгоритм методу Фаррара-Глобера перевірки мультиколінеарності;

формулу обчислення визначника 2-го та 3-го порядків;

матричну форму системи нормальних рівнянь;

матричну формулу оцінки параметрів системи регресій;

формулу частинних коефіцієнтів кореляції;

формулу коефіцієнта множинної кореляції;

формулу t-статистики значущості параметрів регресії;

формули прогнозу і його довірчого інтервалу;

частинні коефіцієнти еластичності і їх економічну інтерпретацію.

Студент повинен уміти: Користуватися пакетом Excel:

1) знаходити: транспоновану, обернену матрицю та добуток матриць;

2) використовуючи матричні операції, знаходити розв'язок системи нормальних рівнянь;

користуватися вбудованими статистичними функціями: СРЗНАЧ - середнє значення; СТАНДОТКЛОНП - зміщене середньоквадратичне відхилення; СУММПРОИЗВ - скалярний добуток; СУММ -- сума;

користуватися вбудованою функцією ЛИНЕЙН із категорії «Статистика»;

використовуючи категорію «Математика», знаходити довірчі інтервали прогнозу;

на основі розрахунків робити висновки.

Студент повинен підготувати:

-- алгоритм розв'язання задачі з використанням електронних таблиць Excel.

Завдання

Економічний показник Х залежить від трьох факторів (вихідні дані -- блок ВЗ:Е17), на основі статистичних даних за 15 періодів побудувати кореляційну матрицю. Використовуючи ч_i² -критерій, з надійністю С = 0,95 оцінити наявність загальної мультиколінеарності. Якщо існує загальна мультиколінеарність, то, використовуючи t-статистику з надійністю С = 0,95, виявити пари факторів, між якими існує мультиколінеарність. Якщо такі пари існують, то один із факторів цієї пари виключити із розгляду. Використовуючи сервіс: математика\матрицы, знайти оцінки параметрів лінійної регресії. Результат отриманих оцінок перевірити, використовуючи сервіс: статистика\линейн.

Використовуючи F-критерій з надійністю P=0,95, перевірити статистичну значущість коефіцієнта детермінації (оцінити адекватність прийнятої математичної моделі статистичним даним на основі критерію Фішера).

Якщо математична модель із заданою надійністю адекватна експериментальним даним, то використовуючи t-статистику, з надійністю Р=0,95 оцінити значущість параметрів регресії, знайти значення прогнозу показника для заданих значень факторів (блок А18:С18) (табл. 3.1), його довірчий інтервал із надійністю P=0,95, частинні коефіцієнти еластичності для точки прогнозу. На основі отриманих розрахунків зробити економічний аналіз.

Хід роботи

Вихідні дані факторів розміщуємо в блоці B3:D18, а показники у стовпці ЕЗ:Е18. Для знаходження кореляційної матриці нормалізуємо статистичні дані за формулою



де п - число розглянутих періодів,

т - число факторів,

- середнє значення фактора X_i,

у_чЯ - середньоквадратичне відхилення фактора X_i.

При нормалізації статистичних даних використовуємо вбудовані статистичні функції СРЗНАЧ (середнє значення) блок В21:Е21 (табл. 3.2), СТАНДОТКЛОНП (змішене середньоквадратичне відхилення) блок I27:I29 і вбудовану математичну функцію КОРЕНЬ (корінь квадратний). Нормалізовані статистичні дані формуємо у блоці F13:H17. Слід відзначити, що нормалізовані статистичні дані можна знайти за формулою

Для розрахунків елементів кореляційної матриці можна використовувати вбудовану статистичну функцію СУММПРОИЗВ. Елемент кореляційної матриці, який знаходиться в i-му рядку і j-му стовпці, знаходимо таким чином: СУММПРОИЗВ (стовпець нормалізованих статистичних даних i-го фактора, стовпець нормалізованих статистичних даних j-го фактора).

Кореляційну матрицю можна знайти, використовуючи послідовно вбудовані функції: ТРАНС (блок матриці) та МУМНОЖ (блок даних першої матриці; блок даних другої матриці) за формулою

Де [K] -- кореляційна матриця;

- матриця нормалізованих статистичних даних факторів блоку

F3:H17;

транспонована матриця по відношенню до матриці .

Кореляційна матриця знаходиться у блоці А23:С25. Кореляційну матрицю можна знайти, не нормалізуючи статистичні дані, а використовуючи команду СТАТИСТИКАМ КОРРЕЛ (коефіцієнт кореляції між двома однорідними множинами даних). Припустимо, що для факторів Х1, Х2 коефіцієнт кореляції знаходиться в комірці В23 за формулою =КОРРЕЛ(ВЗ:В17;СЗ:С17)

Для знаходження x_i² знаходимо визначник матриці в комірці E24. Для обчислення визначника кореляційної матриці використовуємо вбудовану математичну функцію: МОПРЕД (блок кореляційної матриці). Розрахункове значення x_i² розміщуємо в комірці E25 і знаходимо його за формулою

Для знаходження логарифма використовуємо вбудовану математичну функцію LN. Для довірчої ймовірності С = 0,95 і числа ступенів свободи

Оскільки розрахункове значення x_i²=56,19 і воно більше критичного, то з надійністю С = 0,95 можна вважати, що існує загальна мультиколінеарність. Використовуючи t-статистику, знайдемо пари факторів, між якими існує мультиколінеарність. Для цього знайдемо обернену матрицю (блок А27:С29) по відношенню до кореляційної матриці (блок A23:С25). Для знаходження оберненої матриці (позначимо її через [R]) використовуємо вбудовану статистичну функцію: МОБР (блок кореляційної матриці). t-статистику пари факторів розрахуємо за формулою

- елементи матриці [R].

Значення r_ij i t_ij знаходяться в стовпцях відповідно E27:29 і G27:29.

Для ступенів вільності k = n-m -1 = 15-4 = 11 і С = 0,95 критичне значення t_0.95;12 = 2, 18. Звідси виходить, що лише для пари факторів Х1 і Х2 t_1:3 > t_0.95;12, тобто з надійністю С = 0,95 між факторами Х1 і Х2 існує мультиколінеарність. Виключаємо із розгляду один із факторів, нехай це буде ХЗ.

У блоці F23:G24 знаходимо кореляційну матрицю факторів Х1, Х2. Обернена матриця знаходиться у блоці Н23:І24.

Визначник цієї матриці D[Х2] обчислюється у комірці В39. Значення x_i² мультиколінеарність між факторами X1, Х2 відсутня. На відсутність мультиколінеарності вказує і t-статистика, значення якої обчислюється у комірці I25.

Допустимо, що між показником Х і факторами Х1, Х2

існує лінійна залежність

Y=a₀+a₁X₁+a₂X₂.

Знайдемо оцінки параметрів, використовуючи матричні операції.

Запишемо систему нормальних рівнянь у матричній формі.



Якщо помножити матричне рівняння зліва на матрицю

то для оцінки параметрів вектора отримаємо формулу

Порядок знаходження оцінок параметрів регресії:

1. Знаходимо транспоновану матрицю [X]^T в блоці А31:ОЗЗ по відношенню до матриці [X] в блоці АЗ:С17, використовуючи в категорії «Ссылки и массивы» вбудовану функцію ТРАНСП (А2:С17).

2. Знаходимо добуток матриць в блоці А35:С37, використовуючи вбудовану математичну функцію МУМНОЖ (блок даних першої матриці А31:033; блок даних другої матриці АЗ:С17).

3. Обернену матрицю знаходимо в блоці D35:F37, використовуючи вбудовану математичну функцію МОБР (А35:С37).

4. Добуток матриць [X]^T Y знаходимо в блоці Н35:Н37, використовуючи вбудовану математичну функцію

=МУМНОЖ(А31:ОЗЗ;ЕЗ:Е17).

5. Оцінки вектора знаходимо в блоці G39:G41, використовуючи вбудовану математичну функцію =МУМНОЖ (блок даних матриці (D35:F37); блок даних матриці [Х]^ТY (Н35:Н37)).

Оцінки параметрів регресії можна знайти, використовуючи вбудовану статистичну функцію ЛИНЕЙН.

Опишемо порядок знаходження оцінок параметрів регресії з використанням функції ЛИНЕЙН:

Відмічаємо блок, де мають знаходитись розрахункові дані: ширина блоку дорівнює числу оцінюваних параметрів, а висота дорівнює п'яти рядкам.

Відкриваємо діалогове вікно Macmep функций, вибираємо функцію ЛИНЕЙН у полі категорії СТАТИСТИЧЕСКИЕ і натискаємо на кнопку Далее> для переходу в наступне діалогове вікно.

У другому діалоговому вікні вводимо: в перший рядок (в перше поле) блок даних показника, вказуючи діапазон комірок ЕЗ:Е17 або ім'я блоку даних; у другий рядок - блок даних факторів АЗ:С17 або ім'я цього блоку; в третій рядок вводиться слово ИСТИНА, якщо а₀ не дорівнює нулю, і слово ЛОЖЬ, якщо а₀ дорівнює нулю; в четвертий рядок вводиться слово ИСТИНА, якщо необхідно знайти не лише параметри лінії регресії, а й додаткову регресійну статистику.

Якщо необхідно знайти лише параметри лінії регресії, то вводимо слово ЛОЖЬ і натискаємо на кнопку ГОТОВО для отримання розрахункових даних.

Для того щоб у блоці розрахункових даних було видноне лише значення першої комірки, натискуємо клавішу F2, потім Ctrt+Shfft+Enter.

Опишемо розрахункові дані:

У першому рядку справа наліво знаходяться оцінки параметрів множинної лінійної регресії відповідно a₀, a₁, a₂.

У другому рядку справа наліво знаходяться середні квадратичні відхилення оцінок параметрів у_a0, у_б1, у_б2 .

У третьому рядку в першій комірці знаходиться коефіцієнт детермінації, а в другій комірці -- середнє квадратичне відхилення показника.

У четвертому рядку в першій комірці знаходиться розрахункове значення F-статистики, в другій комірці знаходиться k -- число ступенів вільності.

У п'ятому рядку в першій комірці знаходиться сума квадратів відхилень розрахункових значень показника від його середнього значення, в другій комірці - залишкова сума квадратів.

Таблиця розрахункових значень додаткової регресійної статистики має вигляд:

a2	a1	a0
уa2	уa1	уa0
r2	S	#Н/Д
Fr1	К	#Н/Д
		#Н/Д

Порівняємо розрахунки, отримані різними методами. Оцінки параметрів, отримані з використанням матричної алгебри і вбудованої статистичної функції ЛИНЕЙН, співпадають (блок G39:G41, блок С39:Е39). Середньоквадратичні відхилення параметрів (блок C40:ё40 та блок B44:B46) також співпадають. Співпадають сума квадратів відхилень (комірки К19 та D43) і розрахункові значення F-статистики для індексу кореляції (комірки С42 та D43). Звідси можна зробити висновки, що, на відміну від електронних таблиць Quattro Pro, електронні таблиці Excel 7.0 дають правильні розрахунки параметрів множинної регресії та додаткової регресійної статистики.

Перевіримо адекватність прийнятої моделі експериментальним даним. Для цього скористаємось критерієм Фішера.

Дисперсія відхилень знаходиться у комірці F21 і обчислюється з використанням вбудованої статистичної функції СУММПРОИЗВ(НЗ:Н17,НЗ:Н17)/12. У стовпці I3:I17 знаходяться розрахункові значення показника, а в стовпці J3:J17 їх відхилення від експериментальних (спостережуваних) значень. Розрахункове значення критерію дорівнює 885, а критичне для С =0,95, К₁=2, К₂=12 дорівнює 3,89 Оскільки F_розр. > F_крит., то з надійністю С = 0,95 можна вважати, що прийнята математична модель адекватна експериментальним даним. Розрахункове значення F-критерію знаходиться у комірках D45 та С42.

Розглянемо значущість параметрів регресії.

Для цього розрахуємо t-статистику кожного із параметрів за формулою

 де

S_i -- середньоквадратичне відхилення статистичних даних від розрахункових,

Ж_ij - діагональний елемент матриці .

Розрахункові значення знаходяться у стовпці В47:В49.

Оскільки t_розр. > t_крит. (комірка B50), то з надійністю С = 0,95 можна вважати, що вплив факторів Х1, Х2 на показник Y значний і їх потрібно враховувати при розрахунках.

Точкову оцінку значення прогнозу для Х1=9, Х2=30 знаходимо у комірці I18. Довірчий інтервал цієї точкової оцінки знаходимо у стовпці З48:№49 і обчислюємо за формулою

де

Алгоритм розрахунку довірчого інтервалу прогнозу

1. Використовуючи вбудовану математичну функцію МУМНОЖ (блок вектора А18:С18, блок матриці [Z] D35:F37), знаходимо добуток (блок H44:J44).

2. Використовуючи вбудовану математичну функцію СУММПРОИЗВ(Н44:М;А18:С18), знаходимо в комірці Н45 значення

3. Використовуючи вбудовану математичну функцію КОРЕНЬ, знаходимо в комірці Н46 значення S_№, а потім у комірці Н47 -- t*S_yp .

4. Довірчі межі прогнозу знаходимо в блоці Н48:Н49.

Частинні коефіціенти еластичності для прогнозу знаходимо за формулами

, у комірках, відповідно, B42, B43.

Таблиця 3.1.

Множинна лінійна регресія

А

В

С

D

Е

F

G

З

I

J

К

L

1

2

Х0

Х1

Х2

ХЗ

Х

Ч1n

Х2n

Ч3n

Yr

L

L^2

3

1

3.82

10.11

23.2

26.02

-0.46

-0.28

-0.44

27.51

-1.49

2.22

-16.49

4	1	4.33	12.34	24.49	33.1	-0.38	-0.17	-0.39	32.60	0.50	0.25	-9.41
5	1	4.82	18.45	26.8	46.15	-0.30	0.13	-0.30	45.40	0.75	0.57	3.64
6	1	5.23	15.78	28.09	41.15	-0.23	0.00	-0.25	40.58	0.57	0.32	-1.36
7	1	5.77	20.2	30.3	51.46	-0.15	0.22	-0.16	50.08	1 .38	1. 92	8.95
8	1	5.92	9.56	31.97	28.67	-0.12	-0.31	-0.09	29.04	-0.37	0. 14	-13.84
9	1	6.53	22.56	33.93	55.76	-0.03	0.33	-0.02	55.73	0.03	0.00	13.25
10	1	6.57	12.36	35.22	34.11	-0.02	-0.17	0.03	35.44	-1.33	1.77	-8.40
11	1	7.47	17.98	36.19	47.37	0.12	0.11	0.07	47.78	-0.41	0.17	4.86
12	1	7.56	15.36	36.87	42.29	0.14	-0.02	0.10	42.66	-0.37	0.14	-0.22
13	1	7.97	13.45	38.99	41	0.20	-0.12	0.19	39.37	1.63	2.66	-1.51
14	1	8.3	18.14	40.75	48.06	0.26	0.11	0.26	49.14	-1.08	1. 16	5.55
15	1	8.54	11.34	41.41	35.91	0.29	-0.22	0.28	35.87	0.04	0.00	-6.60
16	1	8 .77	10.45	42.96	35.27	0.33	-0.26	0.34	34.39	0.88	0.78	-7.24
17	1	8.9	29.26	43.98	71.33	0.35	0.66	0.38	72.07	-0.74	0.54	28.82
18	1	9	З0						73.87
19	15	100.5	237.3 4	515,15	637.65	-ЗЕ- 15	2E- 15	-9E- 16	637.6 5	000	12.64
20	SQ(N)	X1S	X2S	X3S	YS	S^2	D[Y]	Fr	SX1	SX2	SX3
21	3.87	6.70	15.82	34.34	42.51	1.05	34.05	127.29	1.62	5.25	6.48

	A	B	C	D	E	F	G	З	I	J	К	L	М	N	O
31	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
32	3.82	4.33	4.82	5.23	5.77	5.92	6.53	6.57	7.47	756	7.97	8.3	8.5	9	9
33	10.11	12.34	18.45	15.78	20.2	9.56	22.56	12.36	17.98	1536	13.45	18. 14	11	10	29

Таблиця 3.2.

Оцінки параметрів множинної лінійної регресії в Excel

	A	B	C	D	E	F	G	H
1
2	X0	X1	X2	X3	Y	X1n	X2n	X3n
3	1	3,82	10,11	23,2	26,02	=(B3-B$21)/ I$21/$A$21	=(C3-C$21)/ J$21/$A$21	=(D3-D$21)/ K$21/$A$21
4	1	4,33	12,34	24,49	33,1	=(B4-B$21)/ I$21/$A$21	=(C4-C$21)/ J$21/$A$21	=(D4-D$21)/ K$21/$A$21
5	1	4,82	18,45	26,8	46,15	=(B5-B$21)/ I$21/$A$22	=(C5-C$21)/ J$21/$A$21	=(D5-D$21)/ K$21/$A$21
6	1	5,23	15,78	28,09	41,15	=(B6-B$21)/ I$21/$A$21	=(C6-C$21)/ J$21/$A$21	=(D6-D$21)/ K$21/$A$21
7	1	5,77	20,2	30,3	51,46	=(B7-B$21)/ I$21/$A$21	=(C7-C$21)/ J$21/$A$21	=(D7-D$21)/ K$21/$A$21
8	1	5,92	9,56	31,97	28,67	=(B8-B$21)/ I$21/$A$21	=(C8-C$21)/ J$21/$A$21	=(D8-D$21)/ K$21/$A$21
9	1	6,53	22,56	33,93	55,76	=(B9-B$21)/ I$21/$A$21	=(C9-C$21)/ J$21/$A$21	=(D9-D$21)/ K$21/$A$21
10	1	6,57	12,36	35,22	34,11	=(B10-B$21)/ I$21/$A$21	=(C10-C$21)/ J$21/$A$21	=(D10-D$21)/ K$21/$A$21
11	1	7,47	17,98	36,19	47,37	=(B11-B$21)/ I$21/$A$21	=(C11-C$21)/ J$21/$A$21	=(D11-D$21)/ K$21/$A$21
12	1	7,56	15,36	36,87	42,29	=(B12-B$21)/ I$21/$A$21	=(C12-C$21)/ J$21/$A$21	=(D12-D$21)/ K$21/$A$21
13	1	7,97	13,45	38,99	41	=(B13-B$21)/ I$21/$A$21	=(C13-C$21)/ J$21/$A$21	=(D13-D$21)/ K$21/$A$21
14	1	8,3	18,14	40,75	48,06	=(B14-B$21)/ I$21/$A$21	=(C14-C$21)/ J$21/$A$21	=(D14-D$21)/ K$21/$A$21

15	1	8.54	11.34	41.41	35.91	=(В15-B$21)/ І$21/$А$21	=(С15-21)/ J$21/$A$21	=(D15-$21)/ К$21/$А$21
16	1	8.77	10.45	42.96	35.27	=(В16-B$21)/ I$21/$A$21	=(С16-$21)/ J$21/$A$21	=(D16-$21)/ К$21/$А$21
17	1	8.9	29.26	43.98	71.33	=(В17-B$21)/ І$21/$А$21	=(С17-$21)/ J$21/$A$21	=(D17-$21)/ К$21/$А$21
18		9	30

	І	J	К
1
2	Yr	L	L^2
3	=$D$39*B3+$C$39*C3+$E$39	=E3-I3	=J3^2
4	=$D$39*В4+$С$39*С4+$Е$39	=E4-I4	=J4^2
5	=$D$39*B5+$C$39*C5+$E$39	=E5-I5	=J5^2
6	=$D$39*B6+$C$39*C6+$E$39	=E6-I6	=J6^2
7	=$D$39*B7+$C$39*C7+$E$39	=E7-I7	=J7^2
8	=$D$39*B8+ $С$39*С8+$Е$39	=E8-I8	=J8^2
9	=$D$39*B9+$C$39*C9+$E$39	=E9-I9	=J9^2
10	=$D$39*B10+$C$39*C10+$E$39	=E10-I10	=J10^2
11	=$D$39*B11+$C$39*C11+$E$39	=E11-111	=J11^2
12	=$D$39*B12+$C$39*C12+$E$39	=E12-I12	=J12^2
13	=$D$39*B13+$C$39*C13+$E$39	=E13-I13	=J13^2
14	=$D$39*B14+$C$39*C14+$E$39	=E14-114	=J14^2
15	=$D$39*B15+$C$39*C15+$E$39	=E15-I15	=J15^2
16	=$D$39*B16+$C$39*C16+$E$39	=E16-116	=J16^2
17	=$D$39*B17+$C$39*C17+$E$39	=E17-I17	=J17^2
18	=$D$39*B18+ $C$39*C18+$E$39

	A	B	C	D	E	F
19	=СУММ (А3:А17)	=СУММ (B3:B17)	=СУММ (C3:C17)	=СУММ (D3:D17)	=СУММ (E3:E17)	=СУММ (F3:F17)
20	SQ(N)	X1s	X2s	X3s	Ys	S^2
12	=КОРЕНЬ (A19)	=СРЗНАЧ (B3:B17)	=СРЗНАЧ (C3:C17)	=СРЗНАЧ (D3:D17)	=СРЗНАЧ (E3:E17)	=K19/12

	G	H	I	J	K
19	=СУММ (G3:G17)	=СУММ (H3:H17)	=СУММ (I3:I17)	=СУММ (J3:J17)	=СУММ (K3:K17)
20	D[Y]	Fr	SX1	SX2	SX3
12	=ДИСП (E3:E17)	=G21/F21	=СТАНД ОТКЛОНП (B3:B17)	СТАНД ОТКЛОНП (C3:C17)	СТАНД ОТКЛОНП (D3:D17)

	A	C	D
22			[Kor]

23	=СУММПРОИЗВ ($F$3:$F$17,F3:F17)	=СУММПРОИЗВ ($F$3:$F$17,G3:G17)	=СУММПРОИЗВ ($F$3:$F$17,H3:H17)
24	=КОРРЕЛ(B3:B17; $C$3:$C$17)	=КОРРЕЛ(C3:C17; $C$3:$C$17)	=КОРРЕЛ(D3:D17; $C$3:$C$17)
25	=КОРРЕЛ(B3:B17; $D$3:$D$17)	=КОРРЕЛ(C3:C17; $D$3:$D$17)	=КОРРЕЛ(D3:D17; $D$3:$D$17)
26		[Kor]^-1
27	=МОБР(A23:C25)	=МОБР(A23:C25)	=МОБР(A23:C25)
28	=МОБР(A23:C25)	=МОБР(A23:C25)	=МОБР(A23:C25)
29	=МОБР(A23:C25)	=МОБР(A23:C25)	=МОБР(A23:C25)
30

	D	E
22
23
24	D[Kor] =	=МОПРЕД(A23:C25)
25	Xi^2=	= -(14-11/6)*LN(E24)
26
27	r12=	=-B27/КОРЕНЬ(A27*B28)
28	r13=	=-C27/ КОРЕНЬ(A27*C29)
29	r23=	=-C28/ КОРЕНЬ(B28*C29)
30

	F	G
22	[Kor2]
23	=КОРРЕЛ(B3:B17;B3:B17)	=КОРРЕЛ(B3:B17;C3:C17)
24	=КОРРЕЛ(B3:B17;C3:C17)	=КОРРЕЛ(C3:C17;C3:C17)
25
26
27	t12=	=E27*КОРЕНЬ(13/(1-E27^2))
28	t13=	=E28*КОРЕНЬ(13/(1-E28^2))
29	t23=	=E29*КОРЕНЬ(13/(1-E29^2))
30

	H	I
22	[Kor2]^-1
23	=МОБР(F23:G24)	=МОБР(F23:G24)
24	=МОБР(F23:G24)	=МОБР(F23:G24)
25
26
27	S[x1]=	=СТАНДОТКЛОНП(B3:B17);
28	S[x2]=	=СТАНДОТКЛОНП(C3:C17);
29	S[x3]=	=СТАНДОТКЛОНП(D3:D17);
30	[X]^t

	A	В	С	D	Е
31	=ТРАНСП (A3:СІ7)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
32	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
33	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
	F	G	З	1	J
31	=ТРАНСП (AЗ:С17)	=ТРАНСП (A3:С17)	=ТРАНСП (A3:С17)	=ТРАНСП (A3:С17)	=ТРАНСП (АЗ:С17)
32	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
33	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
	К	L	М	N	0
31	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
32	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)
33	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)	=ТРАНСП (АЗ:С17)

	A	B	C
34		[X^t*X]
35	=МУМНОЖ (A31:O33;A3:C17)	=МУМНОЖ (A31:O33;A3:C17)	=МУМНОЖ (A31:O33;A3:C17)
36	=МУМНОЖ (A31:O33;A3:C17)	=МУМНОЖ (A31:O33;A3:C17)	=МУМНОЖ (A31:O33;A3:C17)
37	=МУМНОЖ (A31:O33;A3:C17)	=МУМНОЖ (A31:O33;A3:C17)	=МУМНОЖ (A31:O33;A3:C17)

	D	E	F	G	H
34		[Xt*X]^-1			[X^t*Y]
35	=МОБР (A35:C37)	=МОБР (A35:C37)	=МОБР (A35:C37)		=МУМНОЖ(A31:O33;E3:E17)
36	=МОБР (A35:C37)	=МОБР (A35:C37)	=МОБР (A35:C37)		=МУМНОЖ(A31:O33;E3:E17)
37	=МОБР (A35:C37)	=МОБР (A35:C37)	=МОБР (A35:C37)		=МУМНОЖ(A31:O33;E3:E17)

	A	B	C	D	E	F
38
39	D[K2]	=МОПРЕД (F23:G24)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C17; ИСТИНА;ИСТИНА)
40	Xi^2(2)=	=-(14-9/6)*LN (B39)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C17; ИСТИНА;ИСТИНА)
41			=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C17; ИСТИНА;ИСТИНА)
42	KX1	=D39*B18/ I18	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C17; ИСТИНА;ИСТИНА)
43	KX2	=C39*C18/ I18	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C 17;ИСТИНА;И СТИНА)	=ЛИНЕЙН (E3:E17;B3:C17; ИСТИНА;ИСТИНА)
44	S1=	=КОРЕНЬ ($F$21*D35)
45	S2=	=КОРЕНЬ ($F$21*E36)
46	S3=	=КОРЕНЬ ($F$21*F37)
47	Tao=	=E39/B44
48	Ta1=	=D39/B45
49	Ta2=	=C39/B46
50	Tt=	2,2
51	Yr=	=D39	*X1+	=C39	*X2+	=E39

	F	G
38		[Z*Xt*Y]
39	=МУМНОЖ(D35:F37;H35:H37)
40	=МУМНОЖ(D35:F37;H35:H37)
41	=МУМНОЖ(D35:F37;H35:H37)

Висновки

Між факторами Х1 і Х2 існує мультиколінеарність, тому один із факторів (ХЗ) не включаємо в множинну лінійну регресію.

Оскільки F_розр. > F_крит., то з надійністю Р = 0,95 можна вважати математичну модель

Y = 1,21 + 1,45X₁+1,98X₂

адекватною експериментальним даним. На основі цієї моделі можна робити економічні висновки.

З надійністю Р = 0,95 можна вважати, що вплив факторів Х1, Х2 на показник Y значний.

Прогнозне значення показника з надійністю Р = 0,95 буде знаходитись у проміжку (70,82; 76,52).

При зміні факторів у точці прогнозу Х1_Р на І % показник зміниться на 0,17% при незмінних значеннях фактора Х2_Р.

При зміні фактора у точці прогнозу Х2_Р на 1% показник зміниться на 0,8% при незмінних значеннях фактора Х1_Р.

алгоритм кореляція інтерпретація мультиколінеарність

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

Аналіз індивідуального ринку

Для виконання лабораторної роботи №4 студент повинен знати:

-- мету і зміст запропонованої роботи, порядок її виконання;

-- систему нормальних рівнянь для оцінки нелінійної парної регресії у вигляді многочлена другого порядку;

-- як дослідити функцію;

--визначення і формули оцінки коефіцієнта еластичності;

-- критерій Фішера; Студент повинен уміти: Користуватися пакетом Excel:

будувати електронні таблиці;

складати і копіювати формули;

працювати з масивами даних;

використовуючи статистичні функції СУММ і СУММПРОИЗВ, складати симплекс-таблицю для нормальної системи рівнянь;

будувати графіки вихідних даних і результатів розрахунків.

Студент повинен підготувати:

- алгоритм розв'язування даної задачі з використанням електронних таблиць.

Завдання

На певний вид товару таблиця попиту має вигляд:

p	1	2	3	4	5	6	7	8	9	10	11
d	8.3	7.68	6.38	6.3	5.49	4.7	3.7	3.2	2.5	1.96	1.56

де p_i - ціна в у.г.о. за одиницю товару; d_i - кількість товару, проданого за певний період по ціні pi.



На основі статистичних даних знайти оцінки параметрів регресії попиту, якщо допустити, що вона має таку структуру: Використовуючи критерій Фішера з надійністю Р=0,95, оцінити адекватність припущеної структури регресії статистичним даним. Якщо прийнята математична модель адекватна експериментальним даним, то для даного проміжку знайти:

- коефіцієнт еластичності для всіх значень цін;

- проміжки цін зростання та спадання товарообігу в грошовому вираженні;

ціну на товар, за якої товарообіг у грошовому вираженні буде максимальним;

проміжки цін зростання та спадання прибутку;

оцінку ціни на товар, за якої прибуток буде максимальним, та його значення.

Потім побудувати графіки:

статистичних даних;

лінії регресії;

товарообігу в грошовому вираженні для статистичних даних;

лінії еластичності;

товарообігу в грошовому вираженні для розрахункових значень;

собівартості товару в залежності від обсягу випуску;

прибутку в залежності від обсягу випуску.

Хід роботи

Вводиться гіпотеза, що між фактором С (ціна в умовних грошових одиницях за одиницю товару) та показником D (кількість товару, проданого за певний період по ціні Р) існує стохастична залежність



Для розв'язування задачі складаємо електронну таблицю в Excel. Вхідні дані вводяться у блок A3:D13 (табл. 3.3). У стовпці СЗ:С13 обчислюються значення . Для складання системи нормальних рівнянь використовуємо вбудовані статистичні функції СУММ і СУММПРОИЗВ.

Для скороченого запису ці вбудовані функції позначимо, відповідно, через С і СП.

Симплекс-таблиця для розв'язування системи нормальних рівнянь в електронних таблицях записується у такому вигляді:

A	B	C	D	E
17	a0	a1	a2	1
18	n	C(p)	C(p^2)	-C(d)
19	C(p)	СП(p,p)	СП(p,p^2)	-СП(p,p)
29	C(p^2)	СП(p^2,p)	СП(p^2,p^2)	-СП(p^2,d)

Для розв'язання системи нормальних рівнянь використовуємо звичайні жорданові виключення, вибираючи за розв'язувальні елементи діагональні елементи. Значення симплекс-таблиці після першого кроку ЗЖВ знаходяться у блоці В23:Е25 (табл. 3.4). Опишемо процедуру обчислення одного кроку ЗЖВ:

І) Елементи, що не належать розв'язувальному рядку та розв'язувальному стовпцю, обчислюються за формулою , так для комірки С24:=С19 - С$18*$В19 / $В$18. Формулу копіюємо в решту комірок симплекс-таблиці.

Елементи розв'язувального стовпця ділять на розв'язувальний елемент, так для комірки В24:=В19/В$18. Формулу копіюємо у комірку розв'язувального стовпця В25.

Елементи розв'язувального рядка змінюють знак на протилежний та ділять на розв'язувальний елемент, так для комірки С23:= -С18/$В18. Введену формулу копіюємо в інші комірки розв'язувального рядка.

Замість розв'язувального елемента береться його обернене значення

В23:=1/В18.

Таким же чином робляться інші кроки ЗЖВ. У блоці B33:D35 буде знаходитись зворотна матриця, а в блоці ЕЗЗ:Е35 вектор оцінок параметрів регресії.

Розрахункове значення кількості проданого товару d₁, по
ціні p_i, обчислюється

D3:=$Е$33+$Е$34*АЗ+$Е$35*СЗ.

Одержану формулу копіюємо у блок D4:D14. Коефіцієнт еластичності для р₁, обчислюється у комірці E3 за формулою (E$34+2*$E$35*A3)*A3/D3. Одержану формулу копіюємо у комірки Е4:Е14.

Зауваження: Заміна відносних посилань на абсолютні може здійснюватись двома способами:

- безпосереднім введенням символу "$" перед відповідною координатою;

- у режимі редагування (F2) переводиться курсор на адресу посилання і натискається клавіша F4 (клавіша -- зміни абсолютного посилання).

Середній товарообіг у грошовому вираженні обчислюється в блоці F3:F14. Квадрат відхилень розрахункових значень від експериментальних обчислюється в блоці G3:G13, а сума квадратів відхилень обчислюється у комірці G14 з використанням вбудованої функції СУММ. Собівартість продукції залежно від обсягу випуску визначається в блоці НЗ:Н14. Прибуток підприємства у залежності від ціни реалізації визначається у блоці

У комірці Н17 обчислюється розрахункове I3:I14.значення критерію Фішера. Для обчислення незміщеної дисперсії використовуємо вбудовану статистичну функцію СТАНДОТКЛОНП. Функція СТАНДОТКЛОНП знаходить незміщене середньоквадратичне відхилення за вказаним полем бази даних.

Для оцінки параметрів еластичності регресії попиту у комірках Н18, Н19 знаходяться критичні значення за формулою

Область досліджуваних значень цін належить Р_2k. Коефіцієнт еластичності при зміні ціни від Р₁ до Р_2k спадає від значення -0,09 до -1, а товарообіг у грошовому вираженні P*D2 на цьому проміжку зростає. На проміжку Р_2k , Р₁₁ коефіцієнт еластичності змінюється у межах від -1 до -4,59. Товарообіг у грошовому вираженні на цьому проміжку спадає. Значить у точці Р_2k товарообіг у грошовому вираженні максимальний. Значення для Р_2k знаходяться в останньому рядку таблиці. З таблиці видно, що при максимальному товарообігу коефіцієнт еластичності дорівнює -1 .

Для оцінки ціни на товар, при якій буде максимальний прибуток, знаходимо критичні точки Р_3k і Р_4k. Області досліджуваних цін належать Р_4k. Для визначення характеру поведінки функції на проміжках (Р₁ , Р_4k) та (Р_4k , Р₁₁) знаходимо в точках Р₁ та Р₁₁ значення dF/dP відповідно у комірках Н26, Н27. Припустимо, що у комірці

H26:=3*E34*A3^2+2*(E34-H16*E35)*A3+E33-H16*E34.

Оскільки похідна при переході через точку Р_4k змінює знак з плюса на мінус, то при ціні Р_4k прибуток буде максимальним. Значення максимального прибутку обчислюється у комірці Н24, а оцінка кількості реалізованого товару при максимальному товарообігу в грошовому вираженні визначається у комірці Н25.

Для наявності побудуємо два графічні зображення. Порядок побудови графіків наведений у лабораторній роботі №1.

На рис. 3.10 D (ВЗ:В13) - статистичні дані, D_r (D3:D13) - регресія попиту, Ж (F3:F13) - товарообіг у грошовому вираженні, К (ЕЗ.Е13) - еластичність попиту.

Отже, у першому діалозі Мастера диаграмм повинен бути такий запис:

=$A$2:$A$13;$B$2:$B$13;$D$2:$D$13; $F$2:$F$13;$E$2:$E$13.

На рис. 3.11 V (НЗ:Н13) - собівартість продукції, Z(F3:F13) - товарообіг у грошовому вираженні, Ж V (13:113) -- прибуток від обсягу реалізованої продукції.

Рис. 3.10. Попит, товарообіг та еластичність

Рис. 3.11. Залежність собівартості продукції товарообігу у грошовому виразі та прибутку від обсягу випущеної продукції

Таблиця 3.3

Залежніть товарообігу від еластичності попиту

	A	B	C	D	E	F	G	H	I
1
2	p	D	p^2	Dr	Kel	p*D	(D-Dr)^2	V	P*D-V
3	1	8,3	1	8,19	-0,09	8,19	0,01	26,39	-18,19
4	2	7,28	4	7,44	-0,2	14,87	0,03	24,87	-10
5	3	6,38	9	6,7	-0,33	20,09	0,1	23,39	-3,3
6	4	6,3	16	5,97	-0,48	23,89	0,11	21,95	1,95
7	5	5,49	25	5,27	-0,66	26,34	0,05	20,54	5,8
8	6	4,7	36	4,58	-0,89	27,49	0,01	19,16	8,32
9	7	3,7	49	3,91	-1,18	27,38	0,04	17,82	9,56
10	8	3,2	64	3,26	-1,58	26,08	0	16,52	9,56
11	9	2,5	81	2,63	-2,14	23,64	0,02	15,25	8,39
12	10	1,96	100	2,01	-3,02	20,11	0	14,02	6,09
13	11	1,56	121	1,41	-4,59	15,54	0,02	12,83	2,72
14	6,4138	0,61	41,137	4,3	-1	27,49	0,4	18,6	8,99
	A	B	C	D	E	F	G	H
15							C=	10
16			Симплекс-таблиця			V1=	2
17		a0	a1	a2	1		Fроз.=	89,0202
18	0=	11	66	506	-51,37		P1=	52,3812
19	0=	66	506	4256	-233,63		P2=	6,4138
20	0=	506	4356	39974	-1475,6		D=	0,3766
21			Перший крок ЗЖВ			Dv=	1,4517
22		0	a1	a2	1		P3=	52,6274
23	a0=	0,09	-6	-46	4,67		P4=	7,501
24	0=	6	110	1320	74,59		F(P4)=	9,7118
25	0=	46	1320	16698	887,47		Dop=	3,5833
26			Другий крок ЗЖВ
27		0	0	a2	1
28	a0=	0,42	-0,05	26	8,74
29	a1=	-0,05	0,01	-12	-0,68
30	0=	-26	12	858	-7,64
31			Третій крок ЗЖВ
32		0	0	0	1
33	a0=	1,21	-42	0,03	8,97
34	a1=	-0,42	0,18	-0,01	-0,78
35	a2=	0,03	-0,01	0	0,01
36

Таблиця 3.4

Залежність товарообігу від еластичності попиту

	A	B	C	D
1
2	p	D	p^2	Dr
3	1	8,3	=A3^2	=$E$33+$E$34*A3+$E$35*C3
4	2	7,28	=A4^2	=$E$33+$E$34*A4+$E$35*C4
5	3	6,38	=A5^2	=$E$33+$E$34*A5+$E$35*C5
6	4	6,3	=A6^2	=$E$33+$E$34*A6+$E$35*C6
7	5	5,49	=A7^2	=$E$33+$E$34*A7+$E$35*C7
8	6	4,7	=A8^2	=$E$33+$E$34*A8+$E$35*C8
9	7	3,7	=A9^2	=$E$33+$E$34*A9+$E$35*C9
10	8	3,2	=A10^2	=$E$33+$E$34*A10+$E$35*C10
11	9	2,5	=A11^2	=$E$33+$E$34*A11+$E$35*C11

	A	B	C	D
1
2	p	D	p^2	Dr
3	1	8,3	=A3^2	=$E$33+$E$34*A3+$E$35*C3
4	2	7,28	=A4^2	=$E$33+$E$34*A4+$E$35*C4
5	3	6,38	=A5^2	=$E$33+$E$34*A5+$E$35*C5
6	4	6,3	=A6^2	=$E$33+$E$34*A6+$E$35*C6
7	5	5,49	=A7^2	=$E$33+$E$34*A7+$E$35*C7
8	6	4,7	=A8^2	=$E$33+$E$34*A8+$E$35*C8
9	7	3,7	=A9^2	=$E$33+$E$34*A9+$E$35*C9
10	8	3,2	=A10^2	=$E$33+$E$34*A10+$E$35*C10
11	9	2,5	=A11^2	=$E$33+$E$34*A11+$E$35*C11
	A	B	C	D
12	10	1,96	=A12^2	=$E$33+$E$34*A12 +$E$35*C12
13	11	1,56	=A13^2	=$E$33+$E$34*A13 +$E$35*C13
14	=H19	0,61	=A14^2	=$E$33+$E$34*A14 +$E$35*C14
15
16			Симплекс-таблиця
17		a0	a1	a2
18	0=	11	=СУММ(A3:A13)	=СУММ(C3:C13)
19	0=	=СУММ(A3:A13)	=СУММПРОИЗВ	=СУММПРОИЗВ
20	0=	=СУММ(C3:C13)	=СУММПРОИЗВ	=СУММПРОИЗВ
21			Перший крок ЗЖВ
22		0	a1	a2
23	a0=	=1/B18	=-C18/$B18	=-D18/$B18
24	0=	=B19/B$18	=C19-C$18*$B19/ $B$18	=D19-D$18*$B19/ $B$18
25	0=	=B20/B$18	=C20-C$18*$B20/ $B$18	=D20-D$18*$B20/ $B$18
26			Другий крок ЗЖВ
27		0	0	a2
28	a0=	=B23-B$24*$C23/ $C$24	=C23/C$24	=D23-D$24*$C23/ $C$24
29	a1=	=-B24/$C24	=1/C24	=D24-D$24*$C24/ $C$24
30	0=	=B25-B$24*$C25/ $C$24	=C25/C$24	=D25-D$24*$C25/ $C$24
31			Третій крок ЗЖВ
32		0	0	0
33	a0=	=B28-B$30*$D28/ $D$30	=C28-C$30*$D28/ $D$30	=D28/D$30
34	a1=	=B29-B$30*$D29/ $D$30	=C29-C$30*$D29/ $D$30	=D29/D$30
35	a2=	=-B30/$D30	=-C30/$D30	=1/D30
36

	E	F	G	H	I
1
2	Kel	p*D	(d-Dr)^2	V	P*D-V
3	=($E$34+2*$E$35*A3)*A3/D3	=A3*D3	=(B3-D3)^2	=H$15+H$16*D3	=F3-H3
4	=($E$34+2*$E$35*A4)*A4/D4	=A4*D4	=(B4-D4)^2	=H$15+H$16*D4	=F4-H4
5	=($E$34+2*$E$35*A5)*A5/D5	=A5*D5	=(B5-D5)^2	=H$15+H$16*D5	=F5-H5
6	=($E$34+2*$E$35*A6)*A6/D6	=A6*D6	=(B6-D6)^2	=H$15+H$16*D6	=F6-H6
7	=($E$34+2*$E$35*A7)*A7/D7	=A7*D7	=(B7-D7)^2	=H$15+H$16*D7	=F7-H7
8	=($E$34+2*$E$35*A8)*A8/D8	=A8*D8	=(B8-D8)^2	=H$15+H$16*D8	=F8-H8
9	=($E$34+2*$E$35*A9)*A9/D9	=A9*D9	=(B9-D9)^2	=H$15+H$16*D9	=F9-H9
10	=($E$34+2*$E$35*A10)*A10/D10	=A10*D10	=(B10-D10)^2	=H$15+H$16*D10	=F10-H10
11	=($E$34+2*$E$35*A11)*A11/D11	=A11*D11	=(B11-D11)^2	=H$15+H$16*D11	=F11-H11

	E	F	G	H	I
12	=($E$34+2*$E$35*A12)*A12/D12	=A12* D12	=(B12-D12)^2	=H$15+H$16*D12	=F12-H12
13	=($E$34+2*$E$35*A13)*A13/D13	=A13* D13	=(B13-D13)^2	=H$15+H$16*D13	=F13-H13
14	=($E$34+2*$E$35*A14)*A14/D14	=A14* D14	=СУММ (G3:G13)	=10+2*D14	=F14-H14
15			C=	10
16			V1	2
17	1		Fроз.=	=СТАНДОТКЛОН (D3:D13)^2*8/(2*G14)*10
18	=-СУММ(B3:B13)		P1=	=(-E34+КОРЕНЬ(E34^2-3*E35*E33))/(3*E35)
19	=-СУММПРОИЗВ (B3:B13;A3:A13)	P2=	=(-E34+КОРЕНЬ(E34^2-3*E35*E33))/(3*E35)
20	=-СУММПРОИЗВ (B3:B13;C3:C13)	D=	=E34^2-3*E35*E33
21			Dv=	=4*((E34-H16*E35)^2+ 3*E35*(E34*H16-E33))
22	1		P3=	=(H16*E35-E34+ 0,5*КОРЕНЬ(H21)/(3*E35)
23	=-E18/$B18		P4=	=(H16*E35-E34+ 0,5*КОРЕНЬ(H21)/(3*E35)
24	=E19-E$18*$B19/$B$18		F(p4)=	=E35*H23^3+(E34-H16*E35)*H23^2+(E33-E34*H16)*H23-H15-H16*E33
25	=E20-E$18*$B20/$B$18		Dop=	=E33+E34*H23+E35*H23^2
26
27	1
28	=E23-E$24*$C23/$C$24
29	=-E24/$C24
30	=E25-E$24*$C25/$C$24
31
32	1
33	=E28-E$30*$D28/$D$30
34	=E29-E$30*$D29/$D$30
35	=-E30/$D30
36

Висновки

1. Оскільки Fpoj. > Fmac., то з надійністю Р=0,95 можна вважати, що прийнята економетрична модель відповідає експериментальним даним і її можна використовувати для економічного аналізу.

Оцінка максимального товарообігу в грошовому виразі відповідає ціні 6,41 у.г.о. і дорівнює 27,59 у.г.о.

Оцінка максимального прибутку відповідає ціні P₄=1,5 у.г.о. і дорівнює 9,71 у.г.о. при обсязі товару 4,3 у.г.о.

Максимальне значення оцінки товарообігу в грошовому виразі та максимальне значення прибутку досягаються при різних значеннях цін.

Размещено на Allbest.ru