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

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

Лабораторна робота 3

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

Побудувати економетричну модель, що характеризує залежність прибутку від чисельності працівників та обсягу ОВФ. Визначити стандартні помилки параметрів. Дати змістовне тлумачення взаємозв’язку. Розрахувати економічні характеристики моделі.

Варіант 1		Варіант 2
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,72	15,6	106,3			2,58	15,1	120,0
	3,04	13,5	128,5			2,64	16,1	118,4
	2,84	15,3	118,0			2,52	16,7	108,4
	2,89	14,9	121,2			2,75	15,4	110,0
	2,58	15,1	120,0			2,63	17,1	105,9
	2,64	16,1	118,4			2,48	16,8	117,7
	2,52	16,7	108,4			2,62	16,9	97,5
	2,75	15,4	110,0			2,88	16,1	113,7
	2,63	17,1	105,9			2,68	15,0	122,3
	2,65	17,0	102,3			2,69	14,9	121,9
	2,67	16,9	105,5			2,80	14,8	122,3
	2,66	16,4	105,6			2,81	15,1	123,4
	2,68	16,9	105,7			2,77	14,2	124,5
	2,71	16,8	105,9			2,68	14,0	121,7
Варіант 3		Варіант 4
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,48	16,8	117,7			2,75	16,8	110,0
	2,62	16,9	97,5			2,63	15,5	105,9
	2,88	16,1	113,7			2,48	17,0	117,7
	2,68	15,0	122,3			2,62	16,8	97,5
	2,63	17,1	105,9			2,64	16,1	118,4
	2,65	17,0	102,3			2,52	16,7	108,4
	2,67	16,9	105,5			2,75	15,4	110,0
	2,66	16,4	105,6			2,63	17,1	105,9
	2,68	16,9	105,7			2,48	16,8	117,7
	2,52	18,0	102,0			2,88	16,9	113,7
	2,74	17,2	106,7			2,68	16,1	122,3
	2,56	17,1	108,5			2,56	15,0	102,0
	2,68	16,4	114,3			2,74	18,0	106,7
	2,55	16,7	94,3			2,60	17,2	108,5

Варіант 5		Варіант 6
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,92	14,1	87,8			2,45	17,1	71,3
	2,64	17,2	72,0			2,38	19,5	61,7
	2,79	17,1	72,4			3,04	12,5	96,2
	2,67	17,8	69,5			2,67	16,5	72,9
	2,68	16,2	75,0			2,70	16,0	75,0
	2,85	17,2	70,6			2,65	16,1	74,6
	2,40	16,8	73,4			2,79	16,2	74,1
	2,91	14,8	80,7			2,49	18,0	66,9
	2,29	19,6	62,2			3,27	11,4	98,6
	2,34	19,7	62,2			3,45	16,2	95,2
	2,30	19,8	62,1			3,55	16,0	98,7
	2,25	19,8	62,0			3,87	16,1	99,7
	2,24	21,0	61,5			3,95	15,4	101,2
	2,21	21,5	61,4			4,05	15,1	105,4

Варіант 7		Варіант 8
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,85	17,2	70,6			2,93	18,1	71,2
	2,40	16,8	73,4			2,50	17,2	73,4
	2,91	14,8	80,7			2,95	14,9	81,2
	2,29	19,6	62,2			2,39	20,1	63,7
	3,27	11,4	98,6			3,25	11,4	96,6
	2,45	17,1	71,3			2,65	17,1	72,2
	2,38	19,5	61,7			2,42	19,5	61,7
	3,04	12,5	96,2			3,14	17,5	96,2
	2,67	16,5	72,9			2,75	16,7	72,9
	2,79	17,1	72,4			2,70	16,0	75,0
	2,67	17,8	69,5			2,65	16,1	74,6
	2,68	16,2	75,0			2,79	16,2	74,1
	2,85	17,2	70,6			2,49	18,0	66,9
	2,40	16,8	73,4			3,27	11,4	98,6
Варіант 9		Варіант 10
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,14	17,4	80,3			2,67	15,0	94,0
	2,94	13,8	102,5			2,45	18,6	78,0
	2,67	15,0	94,3			2,86	16,2	87,5
	2,44	18,6	76,0			2,90	15,7	90,2
	2,83	16,2	87,3			2,60	17,9	84,8
	2,92	15,7	90,1			2,72	16,3	95,9
	2,61	17,9	82,8			2,68	17,7	91,0
	2,72	15,3	96,9			2,50	16,8	84,7
	2,68	16,3	83,7			2,74	17,5	88,2
	2,89	14,2	95,8			2,88	15,8	89,5
	2,99	13,2	101,4			3,02	15,5	94,1
	3,05	12,1	104,7			3,15	14,7	105,6
	3,15	12,2	105,8			3,48	11,5	112,5
	3,24	11,5	110,2			4,07	10,2	124,8

Варіант 11		Варіант 12
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,83	13,8	68,0			2,69	14,9	69,4
	2,75	14,8	64,3			2,48	16,1	58,7
	2,40	16,9	55,1			2,11	19,7	62,3
	2,30	16,8	55,5			2,82	14,0	83,8
	2,47	14,8	63,3			2,43	17,1	68,5
	2,45	17,9	52,7			2,34	18,2	64,5
	2,48	17,6	53,7			2,48	17,4	67,6
	2,41	15,7	60,2			2,69	16,1	72,9
	2,34	15,2	62,2			2,36	18,8	62,4
	2,65	17,0	102,3			2,69	14,9	121,9
	2,64	16,9	105,5			2,80	14,8	122,3
	2,66	16,8	105,6			2,81	15,1	121,4
	2,68	16,9	105,6			2,77	15,2	124,5
	2,71	16,7	110,9			2,61	14,0	120,7

Варіант 13		Варіант 14
N n/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,35	16,8	56,5			2,46	19,0	65,4
	2,48	15,0	64,3			2,70	16,3	73,9
	2,45	17,5	53,7			2,58	17,5	68,5
	2,47	17,6	54,7			2,34	18,4	64,5
	2,42	15,7	60,2			2,43	17,2	69,3
	2,34	15,2	62,4			2,84	15,0	83,8
	2,70	14,9	69,5			2,15	19,8	62,3
	2,48	16,1	58,7			2,49	16,1	58,7
	2,15	19,7	62,3			2,60	14,9	69,4
	2,34	19,7	62,2			3,45	16,2	95,2
	2,30	19,8	62,1			3,51	16,0	98,7
	2,27	19,4	62,1			3,87	16,2	99,7
	2,24	21,0	61,8			3,95	15,4	101,3
	2,19	21,6	62,1			4,55	15,4	105,5

Варіант 15		Варіант 16
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,27	32,1	112,5			2,17	33,3	109,4
	1,94	31,0	116,4			1,80	36,1	101,1
	2,32	32,4	111,6			2,36	38,3	102,6
	2,49	33,2	108,9			2,50	30,6	128,5
	2,57	31,2	116,5			2,27	32,1	122,5
	2,01	34,8	104,5			2,33	37,6	105,2
	1,87	35,4	102,7			2,51	34,8	114,8
	2,39	33,0	110,2			2,40	34,2	116,0
	2,18	34,8	104,7			2,50	34,2	116,0
	2,12	18,7	102,0			2,82	16,9	113,2
	2,74	17,7	106,1			2,66	16,1	122,3
	2,57	17,1	107,5			2,56	15,2	102,0
	2,68	16,2	114,3			2,76	18,0	106,2
	2,52	16,2	94,1			2,66	17,6	108,2

Варіант 17		Варіант 18
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,08	32,5	81,6			2,20	34,5	83,6
	1,99	33,4	79,4			1,96	35,0	76,5
	1,96	37,8	69,5			2,25	43,7	76,9
	2,18	35,8	85,4			2,69	31,9	104,6
	1,91	34,2	84,3			2,24	37,3	72,3
	2,37	37,2	71,4			2,43	40,9	66,3
	1,92	38,2	78,1			2,32	38,8	69,6
	2,15	29,4	90,8			2,60	35,7	75,6
	2,41	37,2	72,1			2,70	43,2	62,4
	2,52	18,0	102,0			2,88	16,9	113,7
	2,74	17,2	106,4			2,68	16,1	121,3
	2,56	17,3	108,8			2,56	15,0	102,1
	2,63	16,4	114,7			2,74	18,1	106,7
	2,52	16,5	94,6			2,61	17,4	118,5

Варіант 19		Варіант 20
N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.		N п/п	Прибуток, млн. грн.	Персонал, тис. осіб	Обсяг ОВФ, млн. грн.
	2,95	29,4	152,0			2,32	38,8	114,0
	2,55	35,4	126,2			2,19	39,9	103,1
	2,26	39,7	112,6			2,83	30,1	153,8
	2,49	37,1	120,5			2,75	31,7	146,0
	2,17	35,7	125,3			2,59	37,2	124,8
	2,38	40,2	111,3			2,27	39,7	103,6
	2,22	39,4	112,2			2,05	36,9	119,0
	2,64	43,7	121,2			1,95	38,2	108,7
	2,63	38,4	126,4			2,08	40,1	106,5
	2,52	18,0	102,0			2,88	16,9	113,7
	2,74	17,2	106,7			2,68	16,1	122,3
	2,56	17,1	108,5			2,56	15,0	102,0
	2,68	16,4	114,3			2,74	18,0	106,7
	2,55	16,7	94,3			2,60	17,2	108,5

Завдання. Побудувати економетричну модель на основі даних, наведених у табл. 3.1. Провести дослідження побудованої моделі (за п. 3.6 конспекту лекцій). Здійснити точковий та інтервальній прогнози.

Таблиця 3.1

Вихідні дані

Номер спостереження,	Прибуток підприємства,	Обсяг інвестицій,	Витрати на рекламу,	Витрати на зарплату,
	15,70	17,37	5,28	1,42
	17,34	18,24	6,47	1,58
	21,57	22,47	6,98	1,98
	33,50	18,47	7,05	2,04
	32,30	16,82	7,94	2,38
	37,90	17,60	8,12	3,48
	40,78	17,12	8,69	3,07
	48,02	19,81	9,31	3,84
	43,30	18,67	10,45	4,28
	49,57	20,83	10,47	4,67
	52,14	22,84	13,48	5,98
	55,17	28,85	15,78	6,51
	59,18	29,61	17,65	7,82
	62,22	35,67	18,47	8,58
	77,58	47,87	19,64	9,47
Прогнозні значення	48,82	20,04	10,25

Виконання завдання:

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

.

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

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

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

У рядках 17 та 18 обчислюємо відповідно суму (команда «СУММ()») та середнє значення (=СРЗНАЧ()) вихідних даних та результатів усіх подальших розрахунків. Усі подальші розрахунки також оформляються відповідними заголовками (див рис. 3.2).

У комірках L2:O5 розраховуємо матрицю моментів за формулою . Для цього у комірці L2 набираємо та виконуємо команду:

«=МУМНОЖ(ТРАНСП(C2:F16);C2:F16)».

Потім виділяємо діапазон комірок L2:O5, натискаємо клавішу F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

У комірках L8:O11 розраховуємо матрицю помилок за формулою . Для цього у комірці L8 набираємо та виконуємо команду: «=МОБР(L2:O5)».

Потім виділяємо діапазон комірок L8:O11, натискаємо клавішу F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

У комірках L14:L17 обчислюємо добуток матриць . Для цього у комірці L14 набираємо та виконуємо команду:

«=МУМНОЖ(ТРАНСП(C2:F16);B2:B16)».

Потім виділяємо діапазон комірок L14:L17, натискаємо клавішу F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

У комірках N14:N17 обчислюємо параметри лінійної моделі за формулою

.

Для цього у комірці N14 виконуємо команду: «=МУМНОЖ(L8:O11;L14:L17)».

Потім виділяємо діапазон комірок N14:N17, натискаємо клавішу F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

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

Отже, маємо модель:

.

2. Перевіряємо модель на адекватність.

Спочатку виконаємо допоміжні розрахунки.

У стовпчику G розрахункової таблиці за допомогою побудованої моделі обчислюємо теоретичні значення обсягів прибутку підприємства. Для цього розташовуємо курсор у комірці G2 (у комірці G1 запишемо заголовок «Y(x)»), виконуємо у цій комірці команду «=МУМНОЖ(C2:F16;N14:N17)». Потім виділяємо комірки G2:G16, натискаємо клавішу F2, потім одночасно натискаємо клавіші <Ctrl>+<Shift>+<Enter>.

Для перевірки: сума комірок G2:G16, обчислена у комірці G17, повинна дорівнювати вмісту комірки В17.

У стовпчику Н обчислюємо залишки (у комірці Н1 записуємо заголовок «u»). Розташовуємо курсор у комірці Н2 та обчислюємо у цій комірці різницю вмісту комірок В2 та G2. Решту обчислень (комірки Н3:Н16) виконуємо за допомогою АВТОЗАПОВНЕННЯ.

Для перевірки: Сума залишків моделі (комірка Н17) рівна нулю.

У стовпчику І обчислюємо квадрати залишків (у комірці І1 записуємо заголовок «u^2»). Розташовуємо курсор у комірці І2 та обчислюємо у цій комірці квадрат вмісту комірки Н2. Решту обчислень (комірки І3:І16) виконуємо за допомогою АВТОЗАПОВНЕННЯ.

У стовпчику J обчислюємо відносні похибки теоретичних значень обсягів прибутку за формулою . Для цього у комірці J1 записуємо заголовок «». У комірці J2 обчислення виконуємо за формулою «=H2/B2*100». Решту обчислень (комірки J3:J16) виконуємо за допомогою АВТОЗАПОВНЕННЯ.

Стовпчик К використаємо для розрахунку дисперсії емпіричних значень залежної змінної. Для цього у комірці К2 записуємо заголовок «(Y-Ycp)^2». У комірці К2 обчислення виконуємо за формулою «=(B2-B$18)^2». Решту обчислень (комірки К3:К16) виконуємо за допомогою АВТОЗАПОВНЕННЯ.

Результати розрахунків представлені на рис. 3.3.

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

Усі розрахунки супроводжуємо відповідними заголовками.

а) залишки моделі обчислені раніше у стовпчику Н;

б) У комірці F21 записуємо середнє значення відносної похибки шляхом копіювання сюди вмісту комірки J18.

в) У комірці F22 обчислюємо середньоквадратичну похибку дисперсії залишків (формула ) за допомогою команди

«=КОРЕНЬ(МУМНОЖ(ТРАНСП(H2:H16);H2:H16)/(15-3-1))»;

г) У комірці F23 обчислюємо коефіцієнт детермінації;

формула

команда «=1-I17/K17».

д) у комірці F24 обчислюємо вибірковий коефіцієнт множинної кореляції (формула ) за допомогою команди «=КОРЕНЬ(F23)».

3. Перевіряємо статистичну значущість отриманих результатів.

а) У комірці F25 обчислюємо експериментальне значення F-статистики:

формула

Команда «=F23/(1-F23)*(15-3-1)/3»;

У комірці G25 записуємо табличне значення F-статистики за допомогою команди «=FРАСПОБР(0,05;3;11)» або з використанням Майстра функцій (у категорії функцій «СТАТИСТИЧЕСКИЕ» вибираємо функцію «FРАСПОБР», задаємо ймовірність 0,05 та ступені свободи 3 та 11).

б) У комірці F26 обчислюємо експериментальне значення t-статистики:

формула

команда «=КОРЕНЬ(F23/(1-F23)*(15-3-1))»;

У комірці G26 записуємо табличне значення t-статистики за допомогою команди «=СТЬЮДРАСПОБР(0,025;11)» або з використанням Майстра функцій (у категорії функцій «СТАТИСТИЧЕСКИЕ» вибираємо функцію «СТЬЮДРАСПОБР», задаємо ймовірність 0,025 та ступені свободи 11);

Запишемо довірчі межі для множинного коефіцієнта кореляції (у комірці F27 обчислимо нижню межу, у комірці Н27 – верхню межу, у комірці G27 – сам коефіцієнт кореляції):

формула для граничної похибки:

у комірці G27 набираємо команду «=F24», у комірці F27 набираємо команду «=G27-G26*(1-G27)/КОРЕНЬ(15)», у комірці Н27 набираємо команду «=G27+G26*(1-G27)/КОРЕНЬ(15)».

в) перевіримо значущість окремих коефіцієнтів регресії;

формула для обчислення експериментальних значень t-статистики:

обчислюємо ці експериментальні значення у комірках F28:F31;

у комірці F28 набираємо команду «=N14/F22/КОРЕНЬ(L8)»;

у комірці F29 набираємо команду «=N15/F22/КОРЕНЬ(M9)»;

у комірці F30 набираємо команду «=N16/F22/КОРЕНЬ(N10)»;

у комірці F31 набираємо команду «=N17/F22/КОРЕНЬ(O11)».

У комірці F32 записуємо табличне значення t-статистики за допомогою команди «=СТЬЮДРАСПОБР(0,025;11)», або скопіювавши вміст комірки G26.

4. Обчислимо коефіцієнти еластичності у комірках F33:F36.

Формула для обчислення коефіцієнтів еластичності:

У комірках F33, F34, F35 відповідно набираємо команди:

«=N15*D18/$B$18»; «=N16*E18/$B$18»; «=N17*F18/$B$18».

У комірці F36 обчислюємо загальний коефіцієнт еластичності як суму часткових коефіцієнтів.

5. Побудуємо довірчі інтервали для параметрів регресії. Довірчі інтервали для параметрів обчислюються так:

де — діагональний елемент матриці помилок

адреси комірок та набрані в них команди представлені в наступній таблиці.

Таблиця 3.2

Розрахунок меж довірчих інтервалів для параметрів регресії

Нижня межа	Верхня межа
Адреса комірки	Команда	Адреса комірки	Команда
Е38	=N14-F32*F22*КОРЕНЬ(L8)	F38	=N14+F32*F22*КОРЕНЬ(L8)
Е39	=N15-F32*F22*КОРЕНЬ(M9)	F39	=N15+F32*F22*КОРЕНЬ(M9)
Е40	=N16-F32*F22*КОРЕНЬ(N10)	F40	=N16+F32*F22*КОРЕНЬ(N10)
Е41	=N17-F32*F22*КОРЕНЬ(O11)	F41	=N17+F32*F22*КОРЕНЬ(O11)

6.Обчислимо прогнозне значення прибутку підприємства (точковий прогноз) і знайдемо межі довірчого інтер­валів цього прогнозного значення (інтервальний прогнози):

Спочатку у комірки Е42:Е44 запишемо значення незалежних змінних, які використовуються для прогнозу

а) точкове прогнозне значення обчислюємо у комірці Е45 шляхом підставляння у формулу лінійної моделі прогнозних значень незалежних змінних, тобто, за допомогою команди

«=N14+СУММПРОИЗВ(E42:E44;N15:N17)»;

б) інтервальній прогноз:

формули меж довірчого інтервалу

.

У комірці Е46 обчислюємо нижню межу довірчого інтервалу за допомогою команди «=E45-F32*F22»;

У комірці Е47 обчислюємо верхню межу довірчого інтервалу за допомогою команди «=E45+F32*F22».

Результати розрахунків представлені на наступному рисунку.

Рисунок 3.4 – Результати дослідження моделі та прогноз

Висновки:

У ході виконання лабораторної роботи за допомогою МНК побудована трьохфакторна лінійна економетрична модель, яка описує залежність прибутку підприємства від обсягу інвестицій, витрат на рекламу та витрат на зарплату; така модель має вигляд:

.

Виконана перевірка моделі на адекватність.

Експериментальне значення F-статистики є більшим від табличного при заданому рівні значущості 0,05 та числі ступенів свободи 3 та 11:

).

Отже, в цілому модель є адекватною, параметри регресії є достовірними.

Експериментальне значення t-статистики є більшим від табличного при заданому рівні значущості 0,05 та числі ступенів свободи 11: .

Отже, множинний коефіцієнт кореляції є достовірним.

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

За допомогою t-статистики перевірена значущість параметрів регресійного рівняння. Встановлено, що:

то відпо­відно оцінки є значущими, а оцінки не є значущими.

Розраховані коефіцієнти еластичності:

Отже: прибуток підприємства зменшиться на 0,1372 %, якщо інвестиції зростуть на 1%; прибуток підприємства зменшиться на 0,6997%, якщо витрати на рекламу зростуть на 1 %; прибуток підприємства збільшиться на 1,2310%, якщо заробітна плата зросте на 1 %.

Загальна еластичність показує, що прибуток підприємства збільшиться на 0,3941%, якщо одночасно збільшити на 1 % кожний з факторів (інвестиції, витрати на рекламу та заробітну плату).

Побудовані довірчі інтервали для параметрів регресії. Межі, в яких з імовірністю 0,95 знаходяться параметри регресії, представлені в наступній таблиці.

Таблиця 3.3

Межі прогнозних інтервалів параметрів моделі

Параметр моделі	Точкова оцінка	Межі прогнозних інтервалів
Нижня межа	Верхня межа
А0	26,107885	4,306240	47,909531
А1	-0,2518005	-1,225163	0,721562
А2	-2,7276709	-9,176028	3,720686
А3	11,8560238	0,455428	23,256620

Здійснено інтервальній прогноз обсягу прибутку підприємства при

Прогнозне значення прибутку підприємства з імовірністю знаходиться у межах від 65,80404 млн. грн. до 95,54938 млн. грн.

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