2. Рішення типових задач

Приклад 1. По 15 підприємствам галузі (табл. 1.1) відомі: - об'єм виготовленої продукції (тис. од.) і - витрати на випуск цієї продукції (тис. грош. од.). Необхідно:

1) визначити залежність від ;

2) побудувати кореляційні поля і графік рівняння лінійної регресії на ;

3) зробити висновок про якість моделі і розрахувати прогнозне значення при прогнозному значенні , що складає 107% від середнього рівня.

Таблиця 1.1

i	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
x	2,7	1,5	8,2	4,5	3,3	5,8	3,0	7,1	1,2	10,4	4,9	5,2	11,5	9,4	6,5
y	110	70	310	120	75	170	100	180	30	440	190	150	390	310	230

Рішення:

1) У Excel складемо допоміжну таблицю 1.2.

Таблиця 1.2

Обчислюємо кількість вимірів . Для цього в комірку В19 помістимо =СЧЁТ(A2:A16).

За допомогою функції ∑ (Автосумма) на панелі інструментів Стандартная знайдемо суму усіх (комірка В17) і (комірка С17).

Обчислюємо вибіркові середні: ; . Таким чином, середній об'єм виготовленої продукції по 15 підприємствам галузі складає 5,68 тис. од., а середні витрати на випуск цієї продукції - 191,67 тис. грош. од.

Заповнимо стовпці D, E, F. Наприклад, в комірку D2 помістимо =B2*C2, потім на клавіатурі натиснемо ENTER. Клацнемо лівою кнопкою миші по комірці D2 і, схопивши за правий нижній кут цієї комірки (чорний хрестик), потягнемо вниз до комірки D16. Станеться автоматичне заповнення діапазону D3 - D16.

Для обчислення вибіркової коваріації між та використовуємо формулу

тобто в комірку B21 помістимо =D18 - B18*C18 і отримаємо 345,5 (рис. 1.1).

Вибіркову дисперсію для знайдемо за формулою тобто в комірку B22 помістимо =E18-B18*B18 і отримаємо 9,37 (рис. 1.1). Аналогічно визначаємо

Вибірковий коефіцієнт кореляції дуже високий, що вказує на пряму і дуже сильний зв'язок між та , тобто з ростом об'єму виготовленої продукції збільшуються витрати на випуск цієї продукції .

Вибірковий коефіцієнт лінійної регресії

Параметр . Значить, рівняння парної лінійної регресії має вигляд .

Коефіцієнт показує, що при збільшенні об'єму виготовленої продукції на 1 тис. од. витрати на випуск цієї продукції в середньому збільшаться на 36,87 тис. грош. од.

2) Підставляючи в знайдене рівняння регресії фактичні значення визначимо теоретичні (розрахункові) значення в стовпці G.

За допомогою Мастера диаграмм будуємо кореляційні поля (виділяючи стовпці зі значеннями та ) і рівняння лінійної регресії (виділяючи стовпці зі значеннями і ). Вибираємо тип діаграми - Точечная і, наслідуючи рекомендації Мастера диаграмм, вводимо потрібні параметри (назва, підписи до осей, легенду і тому подібне). В результаті отримаємо рис. 1.2.

Рис. 1.1. Рішення прикладу 1.1 в Excel

Рис. 1.2. Графік залежності об'єму зробленої продукції

від теоретичних і фактичних витрат на випуск цієї продукції

3) Для оцінки якості побудованої моделі регресії обчислимо:

• коефіцієнт детермінації , який показує, що зміна витрат на випуск продукції на 92% пояснюється зміною об'єму виготовленої продукції , а 8% припадає на частку неврахованих в моделі факторів, що вказує на якісність побудованої регресійної моделі;

• середню помилку апроксимації. Для цього в стовпці H обчислимо , а в стовпці I – вираз (рис. 1.1). При множенні середнього значення (комірка I18) на 100% отримаємо . Отже, в середньому теоретичні значення відхиляються від фактичних на 18,2%.

За допомогою - критерію Фішера оцінимо значимість рівняння регресії в цілому:

На рівні значущості 0,05 визначаємо по таблиці - критерію Фішера (табл. 1 додатку) або за допомогою вбудованої статистичної функції FРАСПОБР (рис. 1.3).

Оскільки , то рівняння регресії значиме при .

Рис. 1.3. Діалогове вікно функції FРАСПОБР

Середній коефіцієнт еластичності показує, що з ростом об'єму виготовленої продукції на 1% витрати на випуск цієї продукції в середньому по сукупності зростуть на 1,09%.

Розрахуємо прогнозоване значення шляхом підстановки в рівняння регресії прогнозованого значення фактора Отримаємо . Отже, при випуску продукції у кількості 6,08 тис. од. витрати на виробництво цієї продукції складуть 206,33 тис. грош. од.

Знайдемо помістивши в комірку F23=КОРІНЬ(J17/(B19 - 2)).

Середня стандартна помилка прогнозу:

На рівні значущості або по таблиці -критерія Стьюдента (табл. 2 додатку) або за допомогою вбудованої статистичної функції СТЬЮДРАСПОБР визначимо і вичислимо граничну похибку прогнозу, яка в 95% випадків не перевищуватиме

Довірчий інтервал прогнозу:

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