Лабораторна робота №1
Тема роботи: Використання МНК
Мета роботи: Навчитися за допомогою МНК знаходити коефіцієнти лінії регресії.
ЗАВДАННЯ
На основі статистичних даних показника Y і фактора Х вашого варіанту знайти:
-
оцінки параметрів лінії регресії =b0+b1x;
На основі отриманих значень зробити висновки.
Приклад виконання роботи
Бюро економічного аналізу фабрики “Світоч” оцінює ефективність відділу маркетингу з продажу цукерок. Для такої оцінки вони мають досвід праці у 5 географічних зонах з майже однаковими умовами (потенціальні клієнти, ставлення до товарного знака і т. ін.).У цих зонах вони зафіксували протягом однакового періоду обсяги продажів (млн. коробок), витрати (млн. грн.) фірми та просування товару на ринку. Дані наведені в таблиці 1.1.
Таблиця 1. 1 Початкові дані.
Витрати на рекламу (млн. грн), Хi |
5 |
6 |
9 |
12 |
18 |
20 |
Обсяг продажу (млн. коробок), Yi |
25 |
30 |
35 |
45 |
65 |
? |
На основі статистичних даних показника Y і фактора Х знайти:
-
оцінки параметрів лінії регресії =b0+b1x;
На основі отриманих значень зробити висновки.
Хід роботи
-
За умовою задачі припускаємо, що між даними є лінійна залежність, тобто їх можна апроксимувати прямою лінією. Взагалі, існує необмежена кількість прямих y=b0+b1x, які можна провести через множину спостережувальних точок. З множини можливих прямих оберемо “найкращу”. Для цього скористаємося методом найменших квадратів. Проведені попередні розрахунки подамо у вигляді таблиці 1.2:
Таблиця 1. 2 Допоміжні розрахунки.
|
Витрати на рекламу (млн. грн), Хi |
Обсяг продажу, (млн. коробок), Yi |
X2i |
XiYi |
|
5 |
25 |
25 |
125 |
|
6 |
30 |
36 |
180 |
|
9 |
35 |
81 |
315 |
|
12 |
45 |
144 |
540 |
|
18 |
65 |
324 |
1170 |
|
20 |
? |
|
|
|
50 |
200 |
610 |
2330 |
Для знаходження невідомих параметрів b0 і b1 необхідно послідовно здійснити такі розрахунки:
b0=–b1=40-3•10=10.
Знаючи параметри b0 і b1, отриману пряму запишемо у вигляді:
Прокоментуємо, яким чином виконуються розрахунки за допомогою пакету Excel.
Для зручності розрахунків початкові статистичні дані розташуємо по стовпчикам у вигляді таблиці, яка обов’язково має свої заголовки і в кожному розмістимо у якості помітки змінну Х для незалежного фактора і Y для залежного (результату).
Для розрахунків значень параметрів b0 і b1 необхідно знайти значення наступних сум , , середні значення і .
Для цього введемо два нових стовпчика для додаткових розрахунків XiYi і , а потім у відповідні комірки введемо формули розрахунків:
D2: =B2^2;
E2: =B2*C2.
Використовуючи можливості копіювання зробимо копії цих формул у області D3:D6 і E3:E6 відповідно.
В комірці B8 розрахуємо суму значень фактору Х. Для цього:
-
виділимо цю комірку;
-
введемо знак =, а потім використавши список функцій викличемо функцію СУММ() (рис.1.1):
Рис 1. 1 Список функцій
-
введемо параметри функції СУММ() (Рис.1.2):
Рис 1. 2 Введення параметрів функції СУММ()
-
натиснемо Enter.
Виконаємо копіювання формули з комірки B8 в сусідні комірки C8:E8.
Для розрахунку середніх значень факторів X і Y використаємо функцію СРЗНАЧ(). Формулу, що містить цю функцію для Х помістимо в комірку C11, а для Y в C12:
C11: =СРЗНАЧ(B2:B6);
C12: =СРЗНАЧ(C2:C6).
В комірку C6 помістимо формула, що містить функцію для розрахунку кількості елементів в вибірці:
C6: =СЧЕТ(B2:B6).
Оскільки всі необхідні додаткові розрахунки є, в комірках C13 і С14 розрахуємо відповідні значення параметрів моделі b1і b0 відповідно (Див. Додаток 1 Додаток 2):
C13: =(C10*E8-B8*C8)/(C10*D8-B8^2);
C14: =C12-C11*C13.