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

ЛАБОРАТОРНА РОБОТА №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, використовуючи вбудовану функцію СРЗНАЧ, у комірках В19 і В20 відповідно.

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.

Date: 2015-10-19; view: 529; Нарушение авторских прав