Мета: набути навички побудови загальної лінійної економетричної моделі та її дослідження засобами MS EXCEL

Тема: побудова загальної лінійної економетричної моделі

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

Завдання для самостійної роботи

На основі даних таблиці 1.5 побудувати економетричні моделі витрат на харчування: лінійну, параболічну, показову. За принципом мінімальності незміщеного стандартного відхилення вибрати найбільш адекватну економетричну модель (N – номер варіанту).

Таблиця 1.5

Дані до задачі

№ п/п
	131,1	19+N/10
	135,2	19,4
	140,3+N/10	19,7
	145,5	20,5+N/10
	150,6	21,5
	155,8	22,5
	160,9	22,9
	165,8	23,7
	170,7+N/10	24,6
	175,6	26,2+N/10
	180,5	27,5
	185,4	28,9
	190,4	30,4
	195,6+N/10	31,7
	200,3	32,5
	205,4	33,1+N/10
	210,3	33,9
	215,8	34,6
	220,1+N/10	35,1
	225,4	36,1

Завдання. Побудувати економетричну модель, яка характеризує залежність між витратами на харчування, загальними затратами та складом сім’ї на основі даних, наведених у табл. 2.1. Проаналізувати зв’язок, визначений на основі побудованої моделі. Розрахувати економічні характеристики.

Таблиця 2.1

Вихідні дані

№ п / п	Витрати на харчування, тис. грн..	Загальні затрати, тис. грн..	Склад сім’ї, осіб
			1,5
			1,6
			1,9
			1,8
			3,4
			3,6
			3,5
			5,5
			5,4
			5,4
			5,3
			8,5
			8,3
			8,1
			7,3
			8,4

Ідентифікуємо змінні моделі: — витрати на харчування (залежна змінна); — загальні витрати (незалежна змінна); — склад сім’ї (незалежна змінна).

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

Оцінимо параметри моделі на основі методу 1МНК.

1-й спосіб (шляхом розв’язування системи нормальних рівнянь).

Завантажуємо програму MS EXCEL. За допомогою буферу обміну переносимо дані таблиці 2.1 на аркуш MS EXCEL (рис. 2.1).

Рисунок 2.1 – Дані до задачі

Складемо розрахункову таблицю.

У рядку 1 аркуша MS EXCEL записуємо заголовки стовпчиків: у комірці E1 записуємо «Х1*Х1», у комірці F1 записуємо «Х1*Х2», у комірці G1 записуємо «Х2*Х2», у комірці Н1 записуємо «Х1*Y», у комірці І1 записуємо «Х2*Y», у комірці J1 записуємо «Y(x)», у комірці K1 записуємо «u**2», у комірці L1 записуємо «(Y-Y(cp))**2». У комірці А18 робимо запис «Сума» – у рядку 18 будуть обчислюватися суми відповідних стовпчиків.

Заповнюємо стовпчики E, F, G, H, I розрахункової таблиці шляхом множення елементів відповідних стовпчиків.

Заповнюємо рядок 18 розрахункової таблиці, використовуючи піктограму АВТОСУМА, або команду «=СУММ()».

Результати представлено на рис. 2.2.

Рисунок 2.2 – Розрахунок коефіцієнтів системи нормальних рівнянь

Об’єднаємо комірки В20:Е20 та запишемо в них заголовок «Система нормальних рівнянь».

Заповнюємо розширену матрицю системи нормальних рівнянь. У комірку В21 запишемо число 16 – кількість спостережень. Розташуємо курсор у комірці С21, набираємо «=», клацаємо лівою кнопкою миші на комірці С18 та натискаємо <Enter>. Теж саме робимо у комірці В22. Решту комірок розширеної матриці заповнюємо згідно з таблицею 2.2.

Таблиця 2.2

Елементи системи нормальних рівнянь

Адреса комірки розширеної матриці системи нормальних рівнянь	Набрана у комірці команда
В21
В22	=C18
В23	=D18
С21	=C18
С22	=E18
С23	=F18
D21	=D18
D22	=F18
D23	=G18
E21	=B18
E22	=H18
E23	=I18

У комірках G21, G22 та G23 записуємо заголовки «А0=», «А1=» та «А2=» відповідно.

Розрахунок цих оцінок параметрів лінійної моделі здійснюємо у комірках Н21:Н23.

Для цього розташовуємо курсор у комірці Н21. Розв’язуємо систему нормальних рівнянь методом оберненої матриці (матричним методом). Набираємо команду «=МУМНОЖ(МОБР(B21:D23);E21:E23)» та натискаємо <Enter>. Виділяємо комірки H21:H23, натискаємо F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

Рисунок 2.3 – Розрахунок параметрів двохфакторної лінійної економетричної моделі

Маємо:

Таким чином, економетрична модель запишеться так:

.

2-й спосіб (матричний)

Об’єднуємо комірки N1:Р1 та записуємо в них заголовок «Матриця Х».

У комірки N2:N17 заносимо одиниці – перший стовпчик матриці Х. У комірки O1:Р17 переносимо зміст комірок С2:D17 – це 2-й та 3-й стовпчики матриці Х.

Об’єднуємо комірки N20:P20 та записуємо в них заголовок «Матриця моментів».

У комірці N21 набираємо команду:

«=МУМНОЖ(ТРАНСП(N2:P17);N2:P17)», натискаємо <Enter>. Виділяємо комірки N21:P23, натискаємо F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

Рисунок 2.4 – Матриця Х та матриця моментів

Об’єднуємо комірки N25:P25 та записуємо в них заголовок «Матриця помилок С».

У комірці N26 набираємо команду «=МОБР(N21:P23)», натискаємо <Enter>. Виділяємо комірки N26:P28, натискаємо F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

Рисунок 2.5 – Матриця помилок

У комірці R25 записуємо заголовок «Добуток Х(трансп)*Y».

У комірці R26 набираємо команду «=МУМНОЖ(ТРАНСП(N2:P17);B2:B17)», натискаємо <Enter>. Виділяємо комірки R26:28, натискаємо F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

Об’єднуємо комірки N30:O30 та записуємо в них заголовок «Параметри лінійної моделі». У комірках N31:N33 записуємо заголовки «А0=», «А1=» та «А2=» відповідно.

У комірці О31 набираємо команду «=МУМНОЖ(N26:P28;R26:R28)», натискаємо <Enter>. Виділяємо комірки О31:О33, натискаємо F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

Рисунок 2.6 – Параметри лінійної двохфакторної моделі, розраховані матричним способом

3-й спосіб (з використанням функції «=ЛИНЕЙН()»)

Розташовуємо курсор у комірці S2, набираємо команду «=ЛИНЕЙН(B2:B17;C2:D17;1;1)» та натискаємо <Enter>.

Виділяємо комірки S2:U6, натискаємо F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>. Оформляємо заголовки рядків та стовпчиків так, як показано на наступному рисунку.

Рисунок 2.7 – Параметри лінійної двохфакторної моделі, розраховані за допомогою функції «=ЛИНЕЙН()»

Як бачимо, результати розрахунків, отримані усіма трьома способами, співпадають.

Отже, побудована двох факторна економетрична лінійна модель:

.

Проаналізуємо побудовану модель.

У стовпчику J розрахункової таблиці за допомогою побудованої моделі обчислюємо теоретичні значення витрат на харчування. Для цього розташовуємо курсор у комірці J2, набираємо у цій комірці команду «=$H$21+$H$22*C2+$H$23*D2» та натискаємо <Enter>. Вміст комірок J3:J17 обчислюємо за допомогою АВТОЗАПОВНЕННЯ.

Для перевірки: сума комірок J2:J17, обчислена у комірці J18, повинна дорівнювати вмісту комірки В18. Тобто, сума залишків моделі, побудованої за допомогою 1МНК, рівна нулю.

У стовпчику К розрахункової таблиці обчислюємо квадрати залишків лінійної моделі та їх суму. Для цього розташовуємо курсор у комірці К2, набираємо у цій комірці команду «=(B2-J2)^2» та натискаємо <Enter>. Вміст комірок К3:К17 обчислюємо за допомогою АВТОЗАПОВНЕННЯ. У комірці К18 обчислюємо суму комірок К2:К17.

У стовпчику L виконаємо розрахунки для обчислення емпіричної дисперсії результативного показника (витрат на харчування). Для цього розташовуємо курсор у комірці L2, набираємо у цій комірці команду «=(B2-СРЗНАЧ(B$2:B$17))^2» та натискаємо <Enter>. Вміст комірок L3:L17 обчислюємо за допомогою АВТОЗАПОВНЕННЯ. У комірці L18 обчислюємо суму комірок L2:L17.

Рисунок 2.8 – Дисперсійні розрахунки

Подальші розрахунки оформлюються заголовками (рис. 2.9).

У комірці L20 обчислюємо незміщену дисперсію залишків за формулою «=K18/(16-3)». У комірці L21 обчислюємо незміщену емпіричну дисперсію результативної (залежної) змінної за формулою «=L18/(16-1)».

У комірках J24:L26 обчислюємо елементи коваріаційної матриці. Для цього розташовуємо курсор у комірці J24, набираємо у цій комірці команду «=$L$20*N26» та натискаємо <Enter>. Вміст решти комірок з діапазону J24:L26 обчислюємо за допомогою АВТОЗАПОВНЕННЯ.

Знаходимо стандартні помилки оцінок параметрів у комірках L28:L30 відповідно за формулами «=КОРЕНЬ(J24)», «=КОРЕНЬ(K25)» та «=КОРЕНЬ(L26)».

У комірках L31:L33 обчислюємо відношення стандартних помилок оцінок до їх модулів (у відсотках). Для цього у комірці L31 набираємо команду «=L28/ABS(H21)*100» та нитискаємо <Enter>. Вміст комірок L32 та L33 обчислюємо за допомогою АВТОЗАПОВНЕННЯ.

Рисунок 2.9 – Дисперсійні розрахунки

Розрахунок економічних характеристик

Цю частину роботи виконуємо у комірках діапазону А26:G32. заголовки оформляємо так, як показано на рис. 2.10.

Обчислюємо середню ефективність показників. Для цього у комірці F28 набираємо команду «=В18/С18» та натискаємо <Enter>. У комірці G28 обчислення виконуємо за формулою «=В18/D18».

Обчислюємо граничну ефективність показників. Для цього у комірці F29 набираємо команду «=Н22» та натискаємо <Enter>. Комірку G29 заповнюємо вмістом комірки Н23 за допомогою команди «=Н23».

Обчислюємо коефіцієнти еластичності. Для цього у комірці F30 набираємо команду «==F29/F28» та натискаємо <Enter>. У комірці G30 обчислення виконуємо АВТОЗАПОВНЕННЯМ.

Об’єднуємо комірки F31:G31 та обчислюємо тут норму заміщення чинників за формулою «= –F29/G29»

Обчислюємо міру ефективності використаних ресурсів. Для цього у комірці F32 набираємо команду «=F29/(C18/16)» та натискаємо <Enter>. У комірці G32 обчислення виконуємо АВТОЗАПОВНЕННЯМ.

Рисунок 2.10 – Результати розрахунків економічних характеристик моделі

Висновки:

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

Ця модель має вигляд:

.

Отже, при збільшенні загальних витрат на 1 тис. грн., при незмінному складі сім’ї, витрати на харчування зростають на 0,16753 тис. грн.; при збільшенні складу сім’ї на 1 особу, при незмінних загальних витратах, витрати на харчування зростають на 8,17526 тис. грн..

Співвідношення стандартної помилки й абсолютного значення параметра становить 78,6%, параметра — 13,3%, параметра — 19,6%. Перше співвідношення (воно більше 30%) свідчить про те, що оцінка параметра моделі може мати зміщення, а друге та третє співвідношення підтверджує незміщеність оцінок параметрів та .

Розраховані економічні характеристики лінійної моделі.

Примітка: При оформленні звіту про виконання лабораторної роботи зробити висновки щодо розрахованих економічних характеристик, як це зроблено у тексті лекції 2.

Дата добавления: 2014-10-23