2020-07-12
172Лабораторна робота № 1
ПОБУДОВА І АНАЛІЗ ЕКОНОМІКО-МАТЕМАТИЧНОЇ МОДЕЛІ ЕКСПОРТУ АВІАЦІЙНОЇ ПРОДУКЦІЇ.
ПОБУДОВА І АНАЛІЗ ЕКОНОМІКО-МАТЕМАТИЧНОЇ МОДЕЛІ ЕКСПОРТУ АВІАЦІЙНОЇ ПРОДУКЦІЇ ЗА ДОПОМОГОЮ ІНТЕГРОВАНОГО ПАКЕТУ EXCEL
Порядок виконання роботи
1. Використовуючи дані, наведені в таблиці 1.1.1, ідентифікувати змінні, специфікувати модель й обчислювати:
- вибіркові середні результативної й факторної ознак;
- вибіркову коваріацію;
- вибіркову дисперсію;
- коефіцієнт кореляції.
Зробити висновки про тісний зв'язок між результативною й факторною ознаками економіко-математичної моделі.
2. Визначити невідомі параметри моделі двома способами:
- розв’язати систему нормальних лінійних рівнянь, отриману методом найменших квадратів;
- застосувати статистичну функцію Excel ¾ ЛІНІЙН. Порівняти отримані результати;
3. Записати в аналітичному вигляді рівняння теоретичної прямої й визначити теоретичні значення результативної ознаки (
).
4. Обчислити суму квадратів відхилень дійсних значень залежної змінної від теоретичних.
|
|
|
5. Побудувати графік економіко-математичної моделі, на якому слід зобразити:
- вибіркові точки (хi, уi), i=1,n;
- теоретичну пряму 
- середню точку 
.
6. Побудувати ANOVA-таблицю й виконати дисперсійний аналіз економіко-математичної моделі експорту.
7. Порівняти результати дисперсійного аналізу з компонентами масиву, одержаними з використанням вбудованої статистичної функції ЛІНІЙН;
8. Визначити коефіцієнт детермінації моделі.
9. Перевірити адекватність економіко-математичної моделі за критерієм Фішера.
10. Оцінити якість економіко-математичної моделі й зробити висновки.
11. Інтерпретувати отримані результати, пояснивши економічний зміст параметрів економіко-математичної моделі (b0, b1).
Побудова і аналіз економіко-математичної моделі експорту
Приклад. На основі даних про обсяги експорту й внутрішнього валового продукту (ВВП) у грошових одиницях побудувати економіко-математичну модель обсягів експорту.
Вихідні дані й необхідні проміжні обчислення наведено в таблиці 1.1.
Таблиця 1.1 – Вихідні дані й проміжні обчислення
| № п/п | Місяць / рік | ВВП, млрд грн, х | Експорт, млрд грн, у | (х - хсер) | (У - усер) | (х - хсер) × (У -усер) | (х - хсер)2 | (У - усер)2 | ||
| 1 | Січень 13 | 33,1100 | 3,2100 | -22,8026 | -3,8742 | 88,3422 | 519,9600 | 15,0095 | ||
| 2 | Лютий 13 | 36,2400 | 4,4100 | -19,6726 | -2,6742 | 52,6088 | 387,0124 | 7,1514 | ||
| 3 | Березень 13 | 35,7700 | 5,9100 | -20,1426 | -1,1742 | 23,6517 | 405,7256 | 1,3788 | ||
| 4 | Квітень 13 | 37,2300 | 4,2700 | -18,6826 | -2,8142 | 52,5769 | 349,0407 | 7,9198 | ||
| 5 | Травень 13 | 40,7100 | 4,0800 | -15,2026 | -3,0042 | 45,6719 | 231,1200 | 9,0253 | ||
| 6 | Червень 13 | 46,2000 | 6,0700 | -9,7126 | -1,0142
| 9,8507 | 94,3352 | 1,0286 | ||
| 7 | Липень 13 | 52,9200 | 6,4200 | -2,9926 | -0,6642 | 1,9877 | 8,9558 | 0,4412 | ||
| 8 | Серпень 13 | 54,0200 | 7,1700 | -1,8926 | 0,0858 | -0,1624 | 3,5821 | 0,0074 | ||
| 9 | Вересень 13 | 61,5600 | 8,4600 | 5,6474 | 1,3758 | 7,7696 | 31,8928 | 1,8928 | ||
| 10 | Жовтень 13 | 59,1200 | 6,9800 | 3,2074 | -0,1042 | -0,3342 | 10,2872 | 0,0109 | ||
| 11 | Листопад 13 | 57,3500 | 7,4700 | 1,4374 | 0,3858 | 0,5545 | 2,0660 | 0,1488 | ||
| 12 | Грудень 13 | 84,2400 | 8,9900 | 28,3274 | 1,9058 | 53,9860 | 802,4398 | 3,6320 | ||
| 13 | Січень 14 | 46,7200 | 7,6600 | -9,1926 | 0,5758 | -5,2930 | 84,5045 | 0,3315 | ||
| 14 | Лютий 14 | 49,7800 | 4,6900 | -6,1326 | -2,3942 | 14,6828 | 37,6092 | 5,7322 | ||
| 15 | Березень 14 | 62,0900 | 8,0000 | 6,1774 | 0,9158 | 5,6572 | 38,1599 | 0,8387 | ||
| 16 | Квітень 14 | 65,6200 | 10,0100 | 9,7074 | 2,9258 | 28,4017 | 94,2330 | 8,5602 | ||
| 17 | Травень 14 | 70,2400 | 6,2800 | 14,3274 | -0,8042 | -11,5222 | 205,2735 | 0,6468 | ||
| 18 | Червень 14 | 76,0300 | 12,5800 | 20,1174 | 5,4958 | 110,5608 | 404,7085 | 30,2037 | ||
| 19 | Липень 14 | 93,3900 | 11,9400 | 37,4774 | 4,8558 | 181,9822 | 1404,5531 | 23,5787 | ||
| Сума | 1062,3400 | 134,6000 | 660,9728 | 5115,4594 | 117,5383 | |||||
| Середнє значення | 55,9126 | 7,0842 | ||||||||
Розв’язання
1. Проідентифікуємо змінні економіко-математичної моделі експорту:
у – обсяг ВВП за місяцями 2013, 2014 років (результативна ознака або залежна змінна), млрд грн,
х – обсяг експорту за зазначені роки (факторна ознака або незалежна змінна), млрд грн.
Тоді модель обсягів експорту специфікована в лінійній формі та має вигляд
у = b0 + b1 х + e,
де b0, b1 – невідомі параметри моделі, e – помилка (стохастична складова моделі, випадкова величина).
Визначимо вибіркові середні змінні моделі, застосувавши вбудовану функцію Excel ¾ СРЗНАЧ.
Обчислимо вибіркову коваріацію за формулою
;
Виконаємо перевірку обчислень, застосувавши інструментарій Excel ¾ КОВАР.
Розрахуємо вибіркову дисперсію результативної й факторної ознак моделі за формулами:
Виконаємо перевірку обчислень, застосувавши інструментарій Excel ¾ ДИСП. Визначимо коефіцієнт кореляції за формулою
Застосуємо інструментарій Excel ¾ КОРРЕЛ для перевірки обчислень.
Наведемо розрахунки в таблицi 1.2.
Таблиця 1.2.
| № п/п | COV | ДИСП(х) | ДИСП(у) | КОРРЕЛ (расчет) | КОРРЕЛ (табл) | |||
| 1 | 4,649589226 |
| 27,36631615 |
| 0,789974063 |
|
|
|
| 2 | 2,768882024 |
| 20,36907543 |
| 0,376389576 |
|
|
|
| 3 | 1,244825791 |
| 21,35397931 |
| 0,072566861 |
|
|
|
| 4 | 2,767203076 |
| 18,37056435 |
| 0,416830573 |
|
|
|
| 5 | 2,403784517 |
| 12,16421089 |
| 0,475014783 |
|
|
|
| 6 | 0,518455431 |
| 4,965011168 |
| 0,054138052 |
|
|
|
| 7 | 0,104617758 |
| 0,471360198 |
| 0,02321977 |
|
|
|
| 8 | -0,008545677 |
| 0,188529173 |
| 0,00038736 |
|
|
|
| 9 | 0,408925791 |
| 1,678566846 |
| 0,099620878 |
|
|
|
| 10 | -0,017591661 |
| 0,54143222 |
| 0,00057157 |
|
|
|
| 11 | 0,029185348 |
| 0,108738315 |
| 0,007833343 |
|
|
|
| 12 | 2,84136845 |
| 42,23367377 |
| 0,191159659 |
|
|
|
| 13 | -0,278580026 |
| 4,447603966 |
| 0,017449133 |
|
|
|
| 14 | 0,772779531 |
| 1,979430004 |
| 0,301697055 |
|
|
|
| 15 | 0,297745736 |
| 2,008414769 |
| 0,044140545 |
|
|
|
| 16 | 1,494827176 |
| 4,959631666 |
| 0,45053916 |
|
|
|
| 17 | -0,606432658 |
| 10,80386768 |
| 0,034039714 |
|
|
|
| 18 | 5,818990611 |
| 21,30044801 |
| 1,589668523 |
|
|
|
| 19 | 9,578011109 |
| 73,92384967 |
| 1,240983759 |
|
|
|
| Σ | 34,78804155 | 34,7880416 | 269,2347036 | 343,85246 | 6,186224377 | 0,198372586 | 0,852416067 | 0,852416067 |
Коефіцієнт кореляції rx,y = 0,85 указує на те, що зв'язок між обсягами експорту й ВВП у побудованій моделі є досить сильним, оскільки 0,7 £ rx,y < 0,9. Додатне значення коефіцієнта кореляції свідчить про наявність прямого зв'язку між цими соціально-економічними показниками.
2. Визначимо невідомі параметри лінійної моделі 
методом 1МНК (методом найменших квадратів). Застосуємо статистичну функцію EXCEL ¾ ЛІНІЙН для визначення невідомих параметрів моделі і її статистичних характеристик.
B1
0,129210838
0,019222579
0,726613151
45,18294716
85,40484773
B0
-0,14030743
1,120110288
1,374845776
17
32,13341543
3. Обчислимо суму квадратів відхилень дійсних значень обсягів експорту у залежно від теоретичних його значень 
. Результати обчислень наведено в таблиці 1.3.
|
|
|
Таблиця 1.3 ¾ Проміжні дані для визначення теоретичного значення обсягів експорту.
| № п/п | Місяць / рік | ВВП, млрд грн, х | Експорт, млрд грн, у | y теор | е | е2 |
| 1 | Січень 13 | 33,1100 | 3,2100 | 4,1378634 | -0,9279 | 0,860930492 |
| 2 | Лютий 13 | 36,2400 | 4,4100 | 4,5422933 | -0,1323 | 0,017501523 |
| 3 | Березень 13 | 35,7700 | 5,9100 | 4,4815642 | 1,4284 | 2,040428751 |
| 4 | Квітень 13 | 37,2300 | 4,2700 | 4,6702121 | -0,4002 | 0,160169687 |
| 5 | Травень 13 | 40,7100 | 4,0800 | 5,1198658 | -1,0399 | 1,081320813 |
| 6 | Червень 13 | 46,2000 | 6,0700 | 5,8292333 | 0,2408 | 0,057968621 |
| 7 | Липень 13 | 52,9200 | 6,4200 | 6,6975301 | -0,2775 | 0,077022953 |
| 8 | Серпень 13 | 54,0200 | 7,1700 | 6,839662 | 0,3303 | 0,109123184 |
| 9 | Вересень 13 | 61,5600 | 8,4600 | 7,8139117 | 0,6461 | 0,417430053 |
| 10 | Жовтень 13 | 59,1200 | 6,9800 | 7,4986373 | -0,5186 | 0,268984635 |
| 11 | Листопад 13 | 57,3500 | 7,4700 | 7,2699341 | 0,2001 | 0,040026363 |
| 12 | Грудень 13 | 84,2400 | 8,9900 | 10,744414 | -1,7544 | 3,077966819 |
| 13 | Січень 14 | 46,7200 | 7,6600 | 5,8964229 | 1,7636 | 3,110204185 |
| 14 | Лютий 14 | 49,7800 | 4,6900 | 6,2918081 | -1,6018 | 2,565789073 |
| 15 | Березень 14 | 62,0900 | 8,0000 | 7,8823935 | 0,1176 | 0,013831295 |
| 16 | Квітень 14 | 65,6200 | 10,0100 | 8,3385077 | 1,6715 | 2,793886407 |
| 17 | Травень 14 | 70,2400 | 6,2800 | 8,9354618 | -2,6555 | 7,051477371 |
| 18 | Червень 14 | 76,0300 | 12,5800 | 9,6835925 | 2,8964 | 8,38917612 |
| 19 | Липень 14 | 93,3900 | 11,9400 | 11,926693 | 0,0133 | 0,000177085 |
| Сума | 1062,3400 | 134,6000 | 134,6 | 0,0000 | 32,13341543 | |
| Середнє значення | 55,9126 | 7,0842 |
|
|
| |
5. Побудуємо графік економіко-математичної моделі.
Визначимо коефіцієнт еластичності обсягів експорту залежно від обсягів виробленого ВВП:
Обчисливши коефіцієнт еластичності, можна зробити висновок, що зі збільшенням виробництва ВВП на 1% обсяги експорту зростають на 1,22%.
Рисунок 1.1 – Залежність обсягів експорту від виробництва ВВП
6. Виконаємо дисперсійний аналіз економіко-математичної моделі експорту продукції авіаційної промисловості. Для цього побудуємо таблицю 1.4.
Таблиця 1.4 – ANOVA-таблиця
| Джерело зміни | Кількість степенів вільності | Кількість степенів вільності | Сума квадратів | Середні квадрати | |||
| Обумовлене регресією (модель) | 1 | 1 | SSR =85,4048 | 85,404848 | |||
| Не пояснене за допомогою регресії (помилка) | n - 2 | 17 | SSE =32,1334 | 1,8902009 | |||
| Загальне
| n - 1 | 18 | SST =117,5383 |
| |||
Визначимо суму квадратів, пояснивши регресію (SSR), суму квадратів помилок (SSE), загальну суму квадратів (SST), середній квадрат, пояснивши регресію (MSR) і середній квадрат помилки MSE.
Слід зазначити, що загальна сума квадратів не має середнього квадрата. Між наведеними вище сумами існує таке співвідношення:
Для заповнення ANOVA-таблиці скористаємося допоміжною таблицею 1.1.5
Таблиця 1.5 – Допоміжні розрахунки для проведення дисперсійного аналізу
| № п/п | Місяць / рік | ВВП, млрд грн, х | Експорт, млрд грн, у | y теор | SSR | SSE | SST |
| 1 | Січень 13 | 33,1100 | 3,2100 | 4,1378634 | 8,6810 | 0,860930492 | 15,0095072 |
| 2 | Лютий 13 | 36,2400 | 4,4100 | 4,5422933 | 6,4613 | 0,017501523 | 7,151401939 |
| 3 | Березень 13 | 35,7700 | 5,9100 | 4,4815642 | 6,7738 | 2,040428751 | 1,37877036 |
| 4 | Квітень 13 | 37,2300 | 4,2700 | 4,6702121 | 5,8274 | 0,160169687 | 7,919780886 |
| 5 | Травень 13 | 40,7100 | 4,0800 | 5,1198658 | 3,8587 | 1,081320813 | 9,025280886 |
| 6 | Червень 13 | 46,2000 | 6,0700 | 5,8292333 | 1,5750 | 0,057968621 | 1,028622992 |
| 7 | Липень 13 | 52,9200 | 6,4200 | 6,6975301 | 0,1495 | 0,077022953 | 0,441175623 |
| 8 | Серпень 13 | 54,0200 | 7,1700 | 6,839662 | 0,0598 | 0,109123184 | 0,007359834 |
| 9 | Вересень 13 | 61,5600 | 8,4600 | 7,8139117 | 0,5325 | 0,417430053 | 1,892796676 |
| 10 | Жовтень 13 | 59,1200 | 6,9800 | 7,4986373 | 0,1717 | 0,268984635 | 0,010859834 |
| 11 | Листопад 13 | 57,3500 | 7,4700 | 7,2699341 | 0,0345 | 0,040026363 | 0,148833518 |
| 12 | Грудень 13 | 84,2400 | 8,9900 | 10,744414 | 13,3971 | 3,077966819 | 3,632033518 |
| 13 | Січень 14 | 46,7200 | 7,6600 | 5,8964229 | 1,4108 | 3,110204185 | 0,331533518 |
| 14 | Лютий 14 | 49,7800 | 4,6900 | 6,2918081 | 0,6279 | 2,565789073 | 5,732244044 |
| 15 | Березень 14 | 62,0900 | 8,0000 | 7,8823935 | 0,6371 | 0,013831295 | 0,83867036 |
| 16 | Квітень 14 | 65,6200 | 10,0100 | 8,3385077 | 1,5733 | 2,793886407 | 8,560244044 |
| 17 | Травень 14 | 70,2400 | 6,2800 | 8,9354618 | 3,4271 | 7,051477371 | 0,646754571 |
| 18 | Червень 14 | 76,0300 | 12,5800 | 9,6835925 | 6,7568 | 8,38917612 | 30,20370194 |
| 19 | Липень 14 | 93,3900 | 11,9400 | 11,926693 | 23,4496 | 0,000177085 | 23,57869141 |
| Сума | 1062,3400 | 134,6000 | 134,6 | 85,4048 | 32,13341543 | 117,5382632 | |
| Середнє значення | 55,9126 | 7,0842 | 7,0842105 | 4,4950 | 1,691232391 |
| |
7. Порівняємо стандартні помилки оцінок параметрів моделі зі значеннями цих оцінок (див. таблицю 1.2) і визначимо їхнє процентне співвідношення, тобто 
:
.
Оскільки стандартні помилки оцінок параметрів стосовно оцінок параметрів моделі становлять відповідно 85% і 15%, то це свідчить про зміщення оцінок. Це означає, що помилки моделі мають системну складову. Крім врахованого в моделі одного фактору на обсяг експорту істотно впливають й інші фактори.
8. Визначимо коефіцієнт детермінації моделі за формулою
К детерм. (R2) = 0,726613151
9. Критерієм адекватності моделі є коефіцієнт детермінації, що визначає правильну частину моделі. Для досліджуваної моделі коефіцієнт детермінації R2 = 0,73 свідчить про те, що правильна частина моделі становить 73%, а помилка – 27%. Тому для перевірки адекватності побудованої моделі застосуємо критерій Фішера. Для цього обчислимо F-відношення з 1 і n -2 (17) степенями вільності при рівні значущості, наприклад 3%, тобто з вірогідністю 97%:
F-отнош. = 45,18294716
За таблицями розподілу Фішера, застосувавши вбудовану статистичну функцію Excel ¾ FРАСПОБР, визначимо критичне значення
F-кр. = 5,607683351
Оскільки розраховане значення Fвіднош > Fкр, то базову гіпотезу статистики H0 відкидаємо. З імовірністю 97% приймаємо гіпотезу H1, яка стверджує, що кут нахилу узагальненої моделі є відмінним від нуля. Звідси робимо висновок, що побудована економіко-математична модель є адекватною реальній дійсності.
10. Оцінимо якості економіко-математичної моделі:
MSE 1,691232391
SSE 32,13341543
МАРЕ 14,83457652
Допоміжні розрахунки для обчислення критеріїв якості моделі наведено в таблиці 1.1.6.
Середня відсоткова помилка
Цей показник якості моделі характеризує незміщенність прогнозу й на практиці не перевищує 5%.
Середня абсолютна помилка
МАЕ 2,533173333
Цей показник визначає середнє значення помилки прогнозу без урахування знака. Наведені вище критерії оцінювання якості моделі використовують як додаткову інформацію при виборі кращої (самої якісної) моделі з можливих.
Таблиця 1.1.6 – Допоміжні розрахунки для проведення аналізу якості моделі експорту
| № п/п | Місяць / рік | ВВП, млрд грн, х | Експорт, млрд грн, у | y теор | SSR | SSE | SST | abs(ei) | abs(ei)/yi | ei/yi |
| 1 | Січень 13 | 33,11 | 3,21 | 4,13 | 8,68 | 0,86 | 15,009 | 0,92 | 0,28 | -0,28 |
| 2 | Лютий 13 | 36,24 | 4,41 | 4,54 | 6,46 | 0,017 | 7,15 | 0,13 | 0,02 | -0,02 |
| 3 | Березень 13 | 35,77 | 5,91 | 4,48 | 6,77 | 2,04 | 1,37 | 1,42 | 0,2 | 0,24 |
| 4 | Квітень 13 | 37,23 | 4,27 | 4,67 | 5,82 | 0,16 | 7,91 | 0,4 | 0,09 | -0,09 |
| 5 | Травень 13 | 40,71 | 4,08 | 5,12 | 3,85 | 1,08 | 9,02 | 1,04 | 0,25 | -0,25 |
| 6 | Червень 13 | 46,2 | 6,07 | 5,83 | 1,57 | 0,05 | 1,02 | 0,24 | 0,03 | 0,03 |
| 7 | Липень 13 | 52,92 | 6,42 | 6,7 | 0,14 | 0,07 | 0,44 | 0,27 | 0,04 | -0,04 |
| 8 | Серпень 13 | 54,02 | 7,17 | 6,83 | 0,05 | 0,1 | 0,007 | 0,33 | 0,04 | 0,04 |
| 9 | Вересень 13 | 61,56 | 8,46 | 7,81 | 0,53 | 0,4 | 1,89 | 0,64 | 0,07 | 0,07 |
| 10 | Жовтень 13 | 59,12 | 6,98 | 7,49 | 0,17 | 0,26 | 0,01 | 0,51 | 0,074 | -0,074 |
| 11 | Листопад 13 | 57,35 | 7,47 | 7,27 | 0,03 | 0,04 | 0,14 | 0,2 | 0,02 | 0,02 |
| 12 | Грудень 13 | 84,24 | 8,99 | 10,74 | 13,39 | 3,07 | 3,63 | 1,75 | 0,19 | -0,19 |
| 13 | Січень 14 | 46,72 | 7,66 | 5,9 | 1,41 | 3,11 | 0,33 | 1,76 | 0,23 | 0,23 |
| 14 | Лютий 14 | 49,78 | 4,69 | 6,3 | 0,62 | 2,56 | 5,73 | 1,6 | 0,34 | -0,34 |
| 15 | Березень 14 | 62,09 | 8 | 7,88 | 0,63 | 0,01 | 0,83 | 0,11 | 0,014 | 0,014 |
| 16 | Квітень 14 | 65,62 | 10,01 | 8,33 | 1,57 | 2,79 | 8,56 | 1,67 | 0,16 | 0,16 |
| 17 | Травень 14 | 70,24 | 6,28 | 8,93 | 3,42 | 7,05 | 0,64 | 2,65 | 0,42 | -0,42 |
| 18 | Червень 14 | 76,03 | 12,58 | 9,68 | 6,75 | 8,38 | 30,2 | 2,89 | 0,23 | 0,23 |
| 19 | Липень 14 | 93,39 | 11,94 | 11,92 | 23,44 | 0,0002 | 23,57 | 0,01 | 0,001 | 0,001 |
| Сума | 1062,34 | 134,6 | 134,6 | 85,4 | 32,13 | 117,5 | 18,61 | 2,81 | -0,67 | |
| Середнє значення | 55,91 | 7,08 | 7,0842105 | 4,49 | 1,69 |
| 0,97 | 0,14 | -0,03 | |
Висновки:
У результаті аналізу економіко-математичної моделі, що відображає зв'язок між обсягами експорту й виробленим ВВП, установлено, що обсяги експорту продукції авіаційних підприємств істотно залежать від кількості виробленого ВВП, оскільки коефіцієнт детермінації показав, що 73% коливань обсягів експорту пояснюється впливом на нього величини виробленого ВВП, а інші 27% – впливом інших факторів, які не досліджено в моделі експорту. Це означає, що помилки побудови моделі мають системну складову, що обумовлена неточною специфікацією моделі, а саме: побудована модель має недостатньо факторів, які впливають на обсяг експорту. На обсяг експорту істотно впливають й інші фактори, наприклад, кількість договорів про поставки, ціни на цю продукцію, капітальні витрати на виробництво продукції, ціни на сировину й матеріали, покупні вироби й ін.
Однак, незважаючи на існування системної помилки, модель визнано адекватною реальній дійсності, оскільки аналіз, проведений за допомогою коефіцієнта детермінації й критерію Фішера, показав наявність істотного зв'язку між результативною ознакою й пояснювальним фактором моделі.
