ЛАБОРАТОРНА РОБОТА № 1

 

Тема: Побудова та аналіз однофакторної лінійної моделі залежності між фактором та показником.

 

Мета заняття: На основі статистичних даних показника Y і фактора X знайти оцінки:

ü  коефіцієнта кореляції;

ü  параметрів лінійної моделі .

ü  Використовуючи критерій Фішера, з надійністю Р=0,95 оцінити адекватність прийнятої економічної моделі статистичним даним.

ü  Використовуючи t-статистику, з надійністю Р=0,95, оцінити значущість коефіцієнта кореляції.

Якщо модель адекватна статистичним даним, знайти:

ü  з надійністю Р=0,95 надійні зони базисних даних;

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

ü  коефіцієнт еластичності для базисних даних і прогнозу.

 

Хід роботи

Висувається гіпотеза, що між фактором X показником  Y існує лінійна стохастична залежність .

Оцінки параметрів а і в, як відомо, можна обчислити за формулами

, .

1.    Завантажити програму EXCEL.

2.    Сформувати таблицю вихідних даних, заповнивши діапазон комірок А3:В15 (рис.1).

3.    Зайти добуток x_1*y₁ у комірці С3, ввівши у комірку С3 формулу С3:=A3*B3.

4.    Тепер треба скопіювати одержану формулу в інші комірки стовпця С. Нагадаємо, що для того, щоб заповнити блок комірок однотипними формулами, необхідно виконати наступну послідовність дій. Після того, як формулу введено до комірки С3, клацаємо ще раз по цій комірці, підводимо курсор миші у лівий нижній куток до утворення знаку «+» і, зачепивши за нього та затримуючи натиснутою ліву кнопку миші, протягнемо курсором до кінця блоку С3:С15. Якщо відпустити кнопку миші, в блоці С3:С15 буде отримано потрібний діапазон формул з відповідними посиланнями. Таку послідовність дій далі будемо називати операцією автозаполнення блоку клітинок.

5.    Зайти значення x₁²  у комірці D3, ввівши у комірку D3 формулу D3:=B3^2. Скопіювати одержану формулу в інші комірки стовпця D (дивись попередній пункт).

6.    Розрахувати суму стовбців, використовуючи вбудовану функцію СУММ або кнопку на панелі інструментів (Автосума). Введена у комірку А17 формула копіюється в необхідні комірки 17-го рядка.

7.    Розрахувати середнє значення величин X та Y, використовуючи вбудовану функцію СРЗНАЧ, у комірках D18 і D19 відповідно.

8.    У комірці В18 розрахувати значення n (об’єм вибірки). Це можна зробити різними способами, один з яких є використання вбудованої функції ЧСТРОК, аргументом якої треба вказати діапазон клітинок. Тобто у комірку В18 введемо формулу :=ЧСТРОК(А3:В15).

9.    Для визначення оцінок параметрів а і в до комірок В19 та В20 ввести формули: В19:=(B18*C17-A17*B17)/(B18*D17-B17^2) та В20: =D19-B19*D18.

10.    Для обчислення розрахункового значення y - y_розрi (i=1,...,13) у комірку Е3 ввести формулу Е3:=$B$19*B3+$B$20. Нагадуємо, що знак «$» вказує на абсолютну адресацію до відповідної комірки_. Скопіювати одержану формулу у блок Е4:Е15.

Оскільки математичне очікування відхилень фактичних даних показника від розрахункових дорівнює нулю, то при правильному виконанні розрахунків значення комірок А17 та Е17 співпадатимуть.

11.    Далі обчислимо:

-    значення (y_i - y_розрi)² у блоці F3:F15, ввівши у комірку F3 формулу F3:=(A3-E3)^2 та скопіюємо одержану формулу у блок F4:F15;

-    значення  у блоці G3:G15, ввівши у комірку G3 формулу G3:=(A3-$D$19)^2 та скопіювати одержану формулу у блок G4:G15 (з абсолютними посиланнями координати  і відносним посиланням координати y₁);

-    значення  у блоці Н3:Н15, ввівши у комірку Н3 формулу Н3:=(B3-$D$18)^2 та скопіювати одержану формулу у блок Н4:Н15 (з абсолютними посиланнями координати і відносним посиланням координати x₁);

-    їх суми розрахувати у комірках F17,G17 та Н17 відповідно.

12.    Обчислити значення  (де m – число факторів моделі ) у комірці D20, ввівши у комірку формулу D20: =КОРЕНЬ(F17/(B18-2)).

13.    У комірку Н19 внести табличне значення t(0,95,11)=2,18 (табличне значення критерію Стьюдента, можна обчислити використовуючи вбудовану функцію СТЬЮДРАСПОБР (ймовірність a=0,05, число ступенів вільності k=n-2=13-2=11).

14.    Значення ∆ (i=1,…,13) обчислити у блоці I3:I15, ввівши у комірку I3 формулу I3:=H$19*D$20*КОРЕНЬ(1/B$18+H3/H$17). Скопіювати отриману формулу у діапазон I4:I15.

15.    Обчислити значення ,  і розмістити їх відповідно у блоках J3:J15 і K3:K15, ввівши у комірки формули J3:=E3-I3, К3:=E3+I3. Скопіювати отримані формули у діапазони J4:J15 та К4:К15 відповідно.

Розрахуємо коефіцієнт кореляції за формулою: .

16.     Для цього розрахувати значення  у комірці L3:=(B3-D$18)*(A3-D$19) та скопіювати отриману формулу у діапазон L4:L15.

17.    Обчислити суму блока L3:L15 у комірці L17.

18.    Значення коефіцієнта кореляції  обчислити у комірці F20:=L17/КОРЕНЬ(G17*H17). (Зазначимо, що значення коефіцієнта кореляції можна розрахувати, використовуючи вбудовану функцію КОРРЕЛ).

19.    Зробити висновок про зв’язок між фактором і показником.

20.    Обчислити коефіцієнт детермінації , ввівши в комірку F19 формулу F19:=КОРЕНЬ(1-F17/G17). (Коефіцієнт детермінації можна розрахувати як квадрат коефіцієнта кореляції).

21.     Розрахувати коефіцієнт еластичності за формулою  для базисний значень у блоці М3:М15, ввівши у комірку М3 формулу М3:=B$19*B3/E3. Скопіювати отриману формулу у діапазон М4:М15.

Для перевірки адекватності (тобто ступеня відповідності побудованого рівняння регресії наявним статистичним даним) застосувати критерій Фішера.

22.    Обчислити розрахункове значення  критерію Фішера, використовуючи формулу . Для цього ввести у комірку J19 формулу J19:=F19^2/(1-F19^2)*(B18-2).

23.    В комірці J20 необхідно обчислити табличне значення  критерію Фішера, використовуючи вбудовану функцію FРАСПОБР (ймовірність a=0,05, число ступенів вільності k₁=m=1 й k₂=n-m-1=13-2=11).

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

25.   
Після проведених дій робочий лист EXEL виглядає як на рис.1.

Значення прогнозу показника занести у комірку В16, у комірці Е16 - Y прогнозне. Оцінку довірчого пів інтервалу для прогнозу обчислити у комірці I16, а межі довірчого інтервалу перебувають відповідно у комірках J16 і K16.

Для наочного уявлення одержаних розрахунків побудувати графіки фактичних даних Y, лінії регресії для базисних даних та прогнозу Yр, довірчу зону і для базисних даних і прогнозу, коефіцієнта еластичності.

Для цього:

26.  Наводимо курсор на кнопку Мастер диаграмм, що знаходиться на панелі інструментів, і натискаємо лівою кнопкою миші.

27.  У діалоговому вікні Мастер диаграмм шаг 1 из 4 вибираємо тип та вид діаграми.

28.  У діалоговому вікні Мастер диаграмм шаг 2 из 4 у полі діапазон, тримаючи клавішу Ctrl., відмічаємо мишею потрібні для побудови графіка  діапазони  A3:A15, B3:В16, Е3:Е16, J3:J16, К3:К16, М3:М16 та вказуємо назви рядків.

29.  У діалоговому вікні Мастер диаграмм шаг 3 из 4 вказуємо Добавить легенду та її розміщення, вводимо назву графіка і назву координатних вісей.

30.  У діалоговому вікні Мастер диаграмм шаг 4 из 4 вибираємо розміщення графіку.

31.  При натисненні клавіші Готово на робочому аркуші з’явиться графік.

32.  Проаналізувати отримані графіки (рис.2).

 

 

 

 

 

 

 

 

 

 

 

 

 

33.  Підвести підсумки лабораторної роботи і зробити висновки.

34.  Зберегти книгу у своїй робочій папці під ім'ям Лаб.1.

 

Висновки:

 

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

2.      Для значення фактора Хр=8,5 прогноз для середнього значення показника складає ; з надійністю р=0,95 він буде знаходитися у межах від 28,50 до 33,89.

3.      При зміні фактора на одиницю показник зміниться на 3,89.

4.      Для прогнозного значення середнє значення коефіцієнта еластичності дорівнює 1,06. Це означає, що при зміні фактора на 1%, показник зміниться на 1,06%.

5.      Значення коефіцієнта еластичності під час зростання фактора від 1 до 13 змінюється від 1,32 до 1,07.

6.      Коефіцієнт кореляції r=0,99, що говорить про тісний зв’язок фактора і показника.

 

Завдання до лабораторної роботи №1

 

Значення фактора Х та показника Y

Індекс фактора Х вибирається по передостанній цифрі залікової книжки, а індекс показника Y – по останній цифрі залікової книжки.