МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

Національний технічний університет

«Харківський політехнічний інститут»

Кафедра організації виробництва та управління персоналом

МЕТОДИЧНІ ВКАЗІВКИ

до розрахунково-графічного завдання по темі «Множинний лінійний регресійний аналіз засобами MS Excel» за курсом «Економіко-математичне моделювання» для студентів спеціальностей 6.030601 «Менеджмент організацій», 8.030609 «Менеджмент інноваційної діяльності», 8.000002 «Інтелектуальна власність», 8.050106 «Облік та аудит», 8.050107 «Маркетинг», 8.050108 «Економіка підприємств»

Затверджено

редакційно-видавничою

радою університету,

протокол №__ від __________ р.

Харків НТУ «ХПІ» 2011

Методичні вказівки до розрахунково-графічного завдання по темі «Множинний лінійний регресійний аналіз засобами MS Excel» за курсом «Економіко-математичне моделювання» для студентів спеціальностей 6.030601 «Менеджмент організацій», 8.030609 «Менеджмент інноваційної діяльності», 8.000002 «Інтелектуальна власність», 8.050106 «Облік та аудит», 8.050107 «Маркетинг», 8.050108 «Економіка підприємств» / Уклад. О.Є. Скворчевський. – Харків: НТУ «ХПІ», 2011 – 23 с.

Укладачі: О.Є. Скворчевський

Рецензент проф. __________________

Кафедра організації виробництва та управління персоналом

ВСТУП

Методичні вказівки призначені для студентів очної, заочної та дистанційної форм навчання при виконанні розрахунково-графічного завдання за розділом «Економетрія» курсу «Економіко-математичне моделювання». Метою розрахунково-графічного завдання є отримання студентами практичних навичок із виконання лінійного кореляційно-регресійного аналізу зв’язку двох економічних показників, а саме побудову та дослідження лінійної регресійної залежності об’ємів продажів підприємств (тис. грн.) від витрат на рекламу та витрат на розширення дилерської мережі. При виконання розрахунково-графічного завдання передбачається, що вихідні дані відповідають вимогам теореми Гауса-Маркова і, таким чином, для їх дослідження доцільно використовувати метод найменших квадратів.

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

Серед подібних програм великою популярністю користується Microsoft Excel, що містить у собі програмну надбудову «Пакет аналізу» і багату бібліотеку із великим числом статистичних функцій. В рамках методичних вказівок викладені підходи до побудови та дослідження моделей множинної лінійної регресії в Microsoft Excel при раціональній комбінації можливостей надбудови «Пакет аналізу» та статистичних функцій. При використанні вказаних засобів, необхідно мати на увазі, що їх термінологія неточна та часто не співпадає із загальноприйнятою вітчизняною термінологією в галузі математичної статистики та економетрики. Концентрація уваги студентів на таких невідповідностях дозволяє вірно виконати розрахунково-графічне завдання та здобути практичних навичок використання надбудови «Пакет аналізу» та статистичних функцій Microsoft Excel.

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

Актуальність засвоєння практичних навичок обробки економічних статистичних даних за допомогою комп’ютерної техніки, зокрема за допомогою програми Microsoft Excel, безумовно, є актуальною частиною підготовки майбутніх фахівців економічної та менеджерської направленості.

Розділ 1. Кореляційний аналіз

Задача. Дослідити залежність об’ємів продажів 12-х підприємств (тис. грн.) від витрат на рекламу (тис.грн.) та витрат на розширення дилерської мережі (тис.грн.) вихідні дані наведені в таблиці 1.

Таблиця 1. Статистичні дані об’ємів продажу підприємства, витрат на рекламу та розширення дилерської мережі

№	Об’єми продажів, тис. грн.	Витрати на рекламу, тис. грн.	Витрати на розширення дилерської мережі, тис. грн.
1	484	71	27
2	539	66	31
3	506	74	29
4	495	72	26
5	528	77	27
6	561	82	21
7	539	86	28
8	594	93	30
9	605	82	36
10	627	77	40
11	660	85	43
12	682	83	48

Необхідно оцінити тісноту лінійного зв’язку між змінними, побудувати модель множинної лінійної регресії, оцінити її якість та спрогнозувати об’єми продажів при витратах на рекламу 97 тис. грн. та витратах на розширення дилерської мережі 52 тис. грн.

Рішення Перш за все необхідно становити формати сторінки А4, книжну орієнтацію та необхідний розмір її полів. На першому аркуші потрібно створити таблицю вихідних даних (див. додаток.).

Перш за все побудуємо матрицю парних коефіцієнтів кореляції для цього в надбудові «Аналіз даних» обираємо інструмент аналізу «Кореляція» (рис.1). Далі натискаємо ОК та переходимо до діалогового вікна цього інструмента. Вказуємо вхідний інтервал, який беремо в таблиці вихідних даних, робимо відмітку про те, що вибірки даних у нас розташовані в стовпчиках. Якщо діапазон комірок вхідного інтервалу містить у собі заголовок таблиці, тобто назви стовпчиків, необхідно зробити відмітку «Мітки в першому рідку» (рис.2). Також в діалоговому вікні (рис.2) інструмента аналізу «Кореляція» визначають вихідний інтервал в який буде внесено матрицю парних коефіцієнтів кореляції (рис.3).

Рис. 1. Діалогове вікно обрання інструмента надбудови «Аналіз даних»

Рис. 2. Діалогове вікно інструмента аналізу «Кореляція»

Отриману матрицю парних коефіцієнтів кореляції потрібно оформити таким чином, щоб вона виглядала естетично та була зручною у користуванні (рис. 3). Коефіцієнти кореляції доцільно округлити до тисячних.

Надалі позначимо об’єми продажів, як витрати на рекламу, як витрати на розширення дилерської мережі, як

Примітка 1: Комірка – російською мовою „ячейка”.

Рис. 3. Матриця парних коефіцієнтів кореляції

та діалогове вікно «Формат комірок»

Перевіримо статистичну значущість парних коефіцієнтів кореляції. Для цього отримаємо розрахункові значення критерію Ст’юдента за формулою:

, (1)

де – парний коефіцієнт кореляції, значущість якого перевіряється;

– кількість спостережень.

Рис.4. Приклад розрахунку фактичного значення критерію Ст’юдента за формулою (1) в MS Excel

На рис. 4 показаний розрахунок фактичного значення критерію Ст’юдента для коефіцієнту кореляції між та Аналогічним чином знаходимо розрахункові значення критерію Ст’юдента для коефіцієнтів кореляції між та а також між та

При перевірці статистичної значущості коефіцієнта кореляції для отримання критичного (табличного) значення t-критерію Ст’юдента можна використати функція СТЬЮДРАСПОБР. Вона дозволяє отримати значення t-критерію, як функцію вірогідності та числа ступенів свободи (рис. 5).

Рис. 5. Діалогове вікно статистичної функції СТЬЮДРАСПОБР

Запис функції у рядку введення формули має вигляд:

= СТЬЮДРАСПОБР(рівень_значущості;ступені_свободи). (2)

При використанні цієї функції необхідно зауважити, що у рядку «Вірогідність» необхідно вказувати не вірогідність р, а рівень значущості α. Перехід від рівня значущості α до вірогідності р здійснюється за формулою:

. (3)

Функція СТЬЮДРАСПОБР повертає значення t-критерію, що відповідає двохсторонньому розподіленню Ст’юдента. Значення t-критерію, що відповідає односторонньому розподіленню Ст’юдента для тих же самих рівня значущості α та числа ступенів свободи k можна отримати вказавши в рядку «Вірогідність» значення (2·α):

= СТЬЮДРАСПОБР(2*рівень_значущості;ступені_свободи). (4)

В більшості випадків при оцінці статистичної значущості коефіцієнту кореляції чи параметрів регресії рекомендується використовувати двохстороннє розподілення Ст’юдента, як більш жорстоке. Однак потрібно мати на увазі, що використання t-критерію Ст’юдента для одностороннього розподілення надає більше можливостей визнати коефіцієнт кореляції чи параметри регресії статистично значущими. Це пов’язане із тим, що критичне значення t-критерію для одностороннього розподілення значно більше ніж для двостороннього розподілення Ст’юдента при тих же самих рівня значущості α та числа ступенів свободи

Кількість ступенів свободи визначається за формулою:

(5)

Очевидно, що некоректне введення аргументів функції СТЬЮДРАСПОБР призводить до того, що функція повертає значення помилки. Із переліком найбільш типових помилок при використанні розглянутої функції можна познайомитись із довідки MS Excel за даною функцією.

Перевіримо статистичну значущість коефіцієнтів кореляції шляхом порівняння фактичного та табличного значень критерію Ст’юдента (рис.6).

Рис. 6. Фактичні та табличне значення критерію Ст’юдента

Зробимо це за допомогою функції ЕСЛИ, категорія Логічні. У якості логічного виразу запишемо умову статистичної значущості відповідного коефіцієнту кореляції, наприклад тобто порівняємо відповідні комірки із фактичним та критичним значенням критерію Ст’юдента (рис. 7 верхній рядок діалогового вікна).

Рис. 7. Діалогове вікно функції ЕСЛИ

Якщо логічний вираз істинний, то MS Excel повертає вираз «коефіцієнт парної кореляції між та статистично значущій для та » (рис.7 середній рядок). У противному випадку MS Excel повертає вираз «коефіцієнт парної кореляції між та статистично не значущій для та » (рис. 7 нижній рядок). Аналогічним чином перевіримо статистичну значущість інших коефіцієнтів кореляції (рис. 8).

Рис. 8. Результати перевірки статистичної значущості

парних коефіцієнтів кореляції

За матрицею парних коефіцієнтів кореляції потрібно зробити наступні висновки:

1. Парний коефіцієнт кореляції між вибірками та дорівнює 0,571, тобто зв'язок між об’ємами продажів та витратами на рекламу прямий та відносно тісний.

2. Парний коефіцієнт кореляції між вибірками та дорівнює 0,857, тобто зв'язок між об’ємами продажів та витратами на розширення дилерської мережі прямий та тісний. Причому значно тісніший порівняно із зв’язком між об’ємами продажів та витратами на рекламу.

3. Парний коефіцієнт кореляції між вибірками та дорівнює 0,237 тобто мультиколінеарність між незалежними змінними відсутня. Відповідно модель множинної лінійної регресії можна будувати за допомогою МНК.

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

, (6)

, (7)

, (8)

де – парні коефіцієнти кореляції.

Таким чином використовуючи данні матриці парних коефіцієнтів кореляції побудуємо матрицю часних коефіцієнтів кореляції (рис. 9).

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

Для перевірки статистичної значущості часних коефіцієнтів кореляції розрахункове значення критерію Ст’юдента отримується за формулою:

. (9)

Критичне значення критерію Ст’юдента отримується для ступенів свободи:

(10)

де – кількість незалежних змінних в моделі.

В іншому алгоритм перевірки статистичної значущості часних коефіцієнтів кореляції (рис. 10) аналогічний перевірці статистичної значущості парних коефіцієнтів кореляції.

Рис. 10. Результати перевірки статистичної значущості

часних коефіцієнтів кореляції

При перевірці статистичної значущості необхідно враховувати, що розрахункове значення критерію Ст’юдента буде негативним. Із критичним значенням порівнюється абсолютна величина розрахункового значення критерію Ст’юдента. Для отримання модуля числа в MS Excel використовується функція ABS, категорія Математичні. Використання функції ABS, як вкладеної функції у функцію ЕСЛИ показано на рис. 10.

За результатами побудови матриці часних коефіцієнтів кореляції можна зробити наступні висновки:

1. Між залежною та незалежними змінними існує більш тісний зв’язок ніж показали парні коефіцієнти кореляції;

2. Об’єми продажів більш тісно повязані із витратами на розширення дилерської мережі, ніж із витратами на рекламу;

3. Мультиколінеарність між незалежними змінними та практично відсутня.