Економетричне моделювання та прогнозування в Excel

325 Pages·8.648 MB·Ukrainian

by  Кузьмичов А.І.

Економетричне
моделювання та
прогнозування
в Excel

Київ АМУ 2010
Міністерство освіти і науки України

Академія муніципального управління

Економетричне
моделювання та
прогнозування
в Excel

Київ АМУ 2010
1
ББК 32.973.26
УДК 681.3.07

Кузьмичов А.І.
Економетричне моделювання та прогнозування в Excel. Навч. пос.
/Бишовець Н.Г., Кузьмичов А.І., Медведєв М.Г., Омецинська Н.В. – К.: ВПЦ АМУ,
2010. – 324 с.

У посібнику наведено апарат побудови економетричних моделей задач економічного про-
гнозування та прийняття рішень на цій основі. Економетрія як сучасний науковий на-
прям у вигляді класичної трійці «економіка + математика + статистика» застосовує
для кількісного економічного аналізу науково-обгрунтовані засоби, що сформувалися у фу-
ндаментальних й прикладних напрямках наукових досліджень, зокрема, в математичній
статистиці, математичній економіці та математичному програмуванні і дослідженні
операцій. У наші часи ці засоби стали значно потужнішими й ефективнішими на рівні
інструментів практичного застосування завдяки можливості реалізації відповідних еко-
номіко-математичних моделей на платформі ПК, зокрема, в середовищі надзвичайно по-
пулярного табличного процесора MS Excel.
Запропоновано процедури математичного та статистичного моделювання типових за-
дач економічного прогнозування для прийняття рішень із застосуванням вбудованих в
Excel аналітичних інструментів. Разом із дотриманням усталеного математико-
статистичного підходу до побудови економетричних моделей, в практикумі активно й
продуктивно застосовується апарат лінійної алгебри, обчислювальної математики і
математичної оптимізації, що вивчався у попередніх дисциплінах, який має значно бі-
льше можливостей щодо побудови моделей економічних об’єктів із врахуванням певних
зовнішніх умов. Саме ця властивість відрізняє цю розробку від аналогічних видань, ро-
бить цей практикум оригінальним й одночасно потужним і простим для модельного дос-
лідження реальних економічних процесів.
Практикум розрахований, у першу чергу, на студентів з економіки та менеджменту, що
вивчають дисципліну «Економетрія», яка завершує цикл фундаментальних дисциплін з
економіко-математичного моделювання (перші дві частини циклу – це дисципліни
«Математичне програмування» та «Дослідження операцій»), може бути корисним для
студентів інженерних спеціальностей та практичних працівників, які приймають
управлінські рішення на основі побудови економетричних моделей.

Рецензенти:
Грехов А.М., д.ф.-м.н., професор (Національний авіаційний університет)
Євдокимов В.Ф., чл.-кор. НАН України, д.т.н., професор (Інститут проблем моде-
лювання в енергетиці ім. Г.Є. Пухова Національної академії наук України)
Іванова Т. В., к.е.н., професор (Академія муніципального управління)

© Академія муніципального управління, 2008
© Бишовець Н.Г., Кузьмичов А.І., Медведєв М.Г.,
Омецинська Н.В. , 2010
2
Зміст

Вступ. Економетричні моделі для прийняття рішень в економіці та органі-
заційному управлінні ………………………………………………………………….. 6
Глава 1. Організація даних для прогнозування ………………………………… 25
1.1. Дані, інформація та моделювання ………………………………………. 26
1.2. Генеральна сукупність. Вибірка ………………………………………….. 30
1.3. Бази даних і списки ………………………………………………………….. 31
1.4. Засоби сортування й пошуку в ОС MS Windows ………………………. 36
1.5. Засоби сортування й пошуку в MS Word ………………………………… 38
1.6. Табличні БД (списки) в MS Excel …………………………………………... 40
1.7. Зв’язані таблиці. Технологія OLE ………………………………………….. 44
1.8. Сортування БД в Excel ………………………………………………………. 50
1.9. Пошук в Excel …………………………………………………………………. 53
1.9.1. Фільтрація списків Excel ………………………………………………. 54
1.9.2. Пошук з використанням форми даних …………………………….. 55
1.9.3. Пошук з використанням Автофильтра …………………………….. 57
1.9.4. Пошук з використанням засобу Расширенный фильтр ………… 64
1.10. Перевірка даних, що уводяться …………………………………………... 68
1.11. Узагальнення економічних даних у зведених таблицях …………….. 70
Глава 2. Візуалізація даних і прогнозування засобами машинної графіки 76
2.1. Машинна графіка ……………………………………………………………. 77
2.2. Ділова графіка ………………………………………………………………... 78
2.3. Прогнозування за допомогою діаграм …………………………………… 86
2.4. Наукова графіка ……………………………………………………………… 88
2.5. Функція ………………………………………………………………………… 90
2.6. Графік функції ……………………………………………………………….. 97
2.6.1. Діаграма График ………………………………………………………. 98
2.6.2. Діаграма Точечная …………………………………………………….. 102
2.6.3. Техніка побудови точкового графіка (діаграма Точечная) …….. 104
 Лінійна функція ……………………………………………………. 104
 Обернена функція …………………………………………………. 107
 Нелінійна функція …………………………………………………. 108
1) f(x) = ax + b ……………………………………………………… 108
2) f(x) = sin(x) та F(x) = cos(x) ……………………………………. 109
3) f(x) = tg(x) ……………………………………………………….. 110
a
4) f (x) b…………………………………………………….. 111
x

5) f (x) xx3………………………………………………. 113

x4
6) f (x) ………………………………………………….. 114
x3

7) f(x) = axb …………………………………………………………. 115
8) f(x) = ax …………………………………………………………… 117
9) f(x) = logax ……………………………………………………….. 118
10) f(x) = aebx ……………………………………………………….. 120

3
a
11) f(x) = …………………………………………………..
1becx 120
Глава 3. Статистичний аналіз даних вибірки …………………………………… 121
3.1. Основні статистичні характеристики вибірки …………………………. 121
1) Середнє значення ……………………………………………………………. 121
2) Варіація ………………………………………………………………………… 123
3) Форма розподілу ……………………………………………………………... 124
3.2. Описова статистика Excel …………………………………………………… 126
3.3. Засоби статистичного аналізу Excel ………………………………………. 127
Глава 4. Прогнозування методами кореляційно-регресійного аналізу ……. 128
4.1. Кореляція ……………………………………………………………………… 128
4.2. Засоби Excel для проведення кореляційного аналізу …………………. 131
4.3. Парна кореляція ……………………………………………………………… 131
4.4. Множинна кореляція ………………………………………………………... 133
4.5. Регресійні економетричні моделі …………………………………………. 137
4.6. Прогнозування підбором кривої по точках ……………………………... 140
4.7. Парна лінійна регресія ……………………………………………………… 144
1) Математичні функції ……………………………………………………... 150
2) Статистичні функції ……………………………………………………… 152
3) Процедура ДОБАВИТЬ ЛИНИЮ ТРЕНДА …………………………... 155
4) Процедура РЕГРЕССИЯ пакету Анализ данных ……………………. 158
5) Процедура Поиск решения ……………………………………………... 163
4.8. Парна нелінійна регресія …………………………………………………... 168
1) y = aLnx + b ………………………………………………………………… 170
2) y = axb ………………………………………………………………………. 172
3) y = aebx ……………………………………………………………………… 175
4) поліноми степені 2  7 …………………………………………………… 175
4.9. Множинна регресія ………………………………………………………….. 184
4.10. Множинна лінійна регресія ………………………………………………. 185
4.11. Матричний метод Гаусса-Маркова ……………………………………… 187
4.12. Множинна нелінійна регресія ……………………………………………. 195
Глава 5. Аналіз часових рядів ……………………………………………………….. 203
5.1. Моделі часових рядів ………………………………………………………... 203
5.2. Метод ковзного середнього ………………………………………………… 205
5.3. Метод простого ковзного середнього …………………………………….. 209
5.4. Метод зваженого ковзного середнього …………………………………… 211
5.5. Модель оптимального підбору коефіцієнтів …………………………… 213
5.6. Метод експоненційного згладжування ………………………………….. 214
Глава 6. Імітаційне моделювання економіки. Метод Монте-Карло ……….. 218
6.1. Генерування випадкових чисел в Excel …………………………………... 227
6.2. Оптимальне планування інвестицій в умовах визначеності і ризику 2 3 7
6.3. Випадкові числа і музика …………………………………………………… 242
Глава 7. Комп’ютерний практикум ………………………………………………... 245
ПР-1. Формування вибірки …………………………………………………………… 246
ПР-2. Вибірка та її статистичні параметри ………………………………………… 248
ПР-3. Робота з базою даних …………………………………………………………… 250
ПР-4. Статистичні функції для обробки даних. Коваріація ……………………. 253
ПР-5. Статистичні функції для обробки даних. Кореляція …………………….. 255
4
ПР-6. Парна кореляція ………………………………………………………………… 257
ПР-7. Апроксимація табличної функції. Метод найменших квадратів ……… 259
ПР-8. Парна лінійна регресія і прогноз …………………………………………….. 261
СРС. Парна лінійна регресія і прогноз (ANOVA) ………………………………… 264
ПР-9. Парна нелінійна регресія і прогноз ………………………………………….. 267
ПР-10. Ділова графіка. Задача про оптимальний центр …………………………. 269
ПР-11. Табулювання аналітичної функції y=f(x). Наукова графіка …………… 272
СРС. Наукова графіка. Варіанти завдань …………………………………………… 287
ПР-12. Емпіричний закон розподілу випадкових чисел (за даними вибірки) .. 290
ПР-13. Нормальний розподіл ймовірностей: дослідження і моделювання ….. 292
СРС. Наукова графіка. Графіки нормального розподілу випадкових величин 295
ПР-14. Аналіз часових рядів і прогноз ………………………………………………. 296
ПР-15. Множинна лінійна регресія ………………………………………………….. 299
ПР-16. Кореляційний аналіз статистичних даних ………………………………… 301
ПР-17. Експертна оцінка показників ………………………………………………… 303
ПР-18. Дисперсійний аналіз статистичних даних ………………………………… 305
Глава 8. Теми і завдання для самостійного опрацювання (тематика НДРС) 307
Тема 1: Мультиколінеарність, її наслідки та методи встановлення …………… 308
СРС-1. Дослідження наявності мультиколінеарності на основі алгоритму
Фаррара-Глобера ……………………………………………………………………….. 309
Тема 2: Гетероскедастичність та її природа ……………………………………….. 313
СРС-2. Дослідження наявності гетероскедастичності на основі параметрич-
ного тесту Гольдфельда-Квандта ……………………………………………………. 315
Тема 3: Автокореляція ………………………………………………………………… 318
СРС-3. Перевірки наявності автокореляції залишків за критерієм Дарбіна-
Уотсона …………………………………………………………………………………… 319
Використана література ……………………………………………………………….. 322

5
Вступ.
Економетричні моделі для прийняття рішень
в економіці та організаційному управлінні

Проблема: є конкретний економічний об’єкт (процес, ситуація, явище) дос-
лідження: сімейний чи державний бюджет, стан продажів магазину, виплати зар-
плат чи пенсій, договірні зобов’язання, попит на продукти чи послуги тощо, фун-
кціонування якого задано числовою таблицею, побудованою за відповідними конк-
ретними документами. Треба визначити засоби, щоб оцінити поточний стан
такого процесу («що ми маємо») та його функціональні властивості, щоб вміти
надалі передбачати поведінку цього об’єкту на майбутнє й своєчасно приймати
певні рішення щодо управління ним. Пропонується реальний економічний об’єкт
дослідження представити відповідною математичною моделлю, яка б найкраще
відтворювала характерні риси і властивості цього об’єкту. Тож для побудови та-
кої моделі будемо застосовувати апарат сучасного наукового напряму під назвою
«економетрія», відповідно, предметом цього видання є економетричне моделюван-
ня задач прогнозування та прийняття рішень на його основі.
6
Цікаво, що економетрія має українські корені – в 1910 році у Львові (тоді це була
територія Польщі) вийшла з друку книжка під назвою «Нарис економетрії і
природничої бухгалтерії» (Przegląd ekonometrii i rzeczywistej teorii
buchalterii), її автор – економіст, професор Краківського університету Павел
Чомпа (Pawel Ciompa). Це була взагалі перша поява слова «економетрія» зі
спробою увести «вимірювальні» (математичні та обчислювальні) засоби, за
якими б можна було кількісно оцінювати абстрактні економічні теорії («по-
літичну економію») й впроваджувати їх ідеї і принципи в реальну практику
(«природничу бухгалтерію»). Тобто, вже у той період серед економістів різ-
ного рівня почало формуватися свідоме відчуття про відсутність науково об-
ґрунтованого й ефективного «містка» між абстрактною економічною теорією
й живою практикою, між теоретиками рівня Маркса чи Кейнса та рядовими
економістами і бухгалтерами, й необхідність його утворення.
Реальні соціально-економічні об’єкти надзвичайно складні, значно складніші за
будь-які природничі чи інженерні об’єкти, достатньо уявити економіку міс-
та, регіону, держави чи певного економічного союзу, тому їх дослідження
неминуче треба здійснювати на їх спрощеннях – моделях. Ясно, що побудова
моделі, яка б була простою і зрозумілою, і, з другого боку, найкраще б від-
творювала характерні риси реального процесу, є скоріше мистецтвом, ніж
наукою. Єдине, що може допомогти у цій справі – застосувати кращий досвід
і засоби інших досліджень й, можливо, з інших галузей.
Економічна теорія довгий час залишалася лише науковою розповіддю про те,
якою має бути реальна економіка, кожен з теоретиків розповідав про неї згі-
дно власних уподобань та поглядів, визначаючи при цьому роль держави та
інших чинників у розвитку суспільства. Історія світової економіки знає про
три постаті класиків економічної теорії: Адама Сміта (1723-1790), Карла Мар-
кса (1818-1883) та Джона Кейнса (1883-1946). Сучасні автори оновлених еко-
номічних теорій будують свої підходи шляхом трансформації класичних
принципів до вимог сучасності, кращі з цих підходів витримали перевірку
часом, а їх автори удостоєні Нобелівською премією з економіки.
За Смітом окремі учасники економіки діють, здається, незалежно один від од-
ного, але певна «вища сила», зокрема, ринкові відносини, координує їх по-
ведінку, роль держави тут мінімальна, розвиток суспільства і економіки ма-
ють поступовий еволюційний характер.
За Марксом рушійною силою у суспільстві є виробництво, відповідно, у своєму
розвитку суспільство і економіка, яка його супроводжує, проходять через
моменти вибухового характеру – революції, які є «локомотивами історії».
Попередні промислові й сучасна комп’ютерна революція є прикладами різ-
кого переходу виробничих процесів з однієї платформи до наступної, більш
ефективної, чому певним чином сприяє й держава.
За Кейнсом обіг ресурсів, зокрема, фінансових коштів, має надзвичайно само-
стійне значення, а не є віддзеркаленням реального обігу товарів і послуг, тож
у певні періоди роль держави може бути й визначальною, а не лише регу-
люючою й допоміжною.
Економіка наших днів є певною комбінацією цих принципів, для нас більш-менш
ближчими є принципи Адама Сміта, економіку такого типу тепер назива-
ється неокласичною.
7
Реальна економіка не може задовольнятися лише принципами, про які розпові-
дають теоретики (як мало знати лише про принцип дії двигуна, що перево-
зити вантажі), її треба організовувати і керувати нею (адже треба знати, як
будувати транспортні засоби і вміти управляти ними), щоб у конкретних си-
туаціях можна б було досягати певних успіхів й своєчасно бути готовими до
можливих змін шляхом проведення відповідних аналітичних розрахунків й
прийняття відповідальних рішень на їх основі.
Звідси виникає потреба розробки різноманітних кількісних методів для:
а) оцінювання теоретичних посилань щодо реальних ситуацій; б) здійснення
реальної економічної політики на платформі базових економічних законів.
Приклад – ще з часів Леонардо да Вінчі була відома теорія крила, але цього
було явно замало, бо ж скільки часу і зусиль людству знадобилося, скільки
було невдач і втрат, пов’язаних зі створенням літальних апаратів. Тож саме
інженерна думка і експерименти: а) довели справедливість теорії; б) теорія
справдилася в успішних проектах, що її дотримувалися.
Саме з таких позицій й виникла економетрія, теорія, моделі і методи «економіч-
ного вимірювання», яку застосовують як теоретики для експериментального
доведення своїх ідей, так і практики, показники діяльності яких мають узго-
джуватися із загально уживаною економічною політикою.

Слово «економетрія» в його сучасному розумінні увів у вжиток видатний норвезь-
кий економіст Рагнар Фріш (Ragnar Frisch) у 1926 р. З тих часів економетрія –
галузь економіки, що поступово й швидко розвивається, а її досягнення май-
же миттєво втілюються у реальну виробничу та бізнес-практику. Її завдання
– наповнити практичним змістом, дослівно, «вимірювати» існуючі економіч-
ні відносини, окреслені певними теоріями, шляхом застосування математич-
них та статистичних методів для аналізу конкретних економічних даних, що
презентують певні явища, процеси та об’єкти дослідження.
Виникнення і інтенсивний розвиток економетрії протягом одного-двох поколінь
дослідників і прогресивних практиків – яскравий приклад того, як на виклик
живої практики виникають молоді науки, які у своєму подальшому активно-
му розвитку поступово витискають в історію інші науки, що за сторіччя свого
існування стали класикою. Виходить, що й науки, як і усе інше, теж характе-
ризуються нормальним життєвим циклом, що складається з короткого «ди-
тинства» (народження із суто практичних міркувань, на початку це завжди
прикладна наука), тривалої «зрілості» (період активного розвитку, корисних
впроваджень, виникнення і накопичення нових й часто досить абстрактних
знань, як результат – з часом наступає розділення на теоретичну та приклад-
ну частини однієї науки й народження нових розділів на стику із суміжними
областями знань) й поступового «старіння» (набуття статусу класичної науки
з її авторитетними постатями, почесними відзнаками, хрестоматійними по-
діями й міфами, яка вже має мало спільного з живою практикою, й перехід в
історію науки).
Щоб зрозуміти, на якому етапі перебуває сучасна економетрія, для ілюстрації
«життя науки» візьмемо для прикладу усім відому геометрію, що народилася
у Стародавньому Єгипті, її назва (отримана пізніше, дослівно «землевимірю-
вання») ясно свідчить про її суто прикладний характер, пов’язаний із розпо-
ділом земельних ділянок, будівництвом каналів та пірамід, мореплаванням.
8
Проходять сторіччя, поступово формується самостійна, строга і красива ма-
тематична наука зі своїми законами, правилами і об’єктами (особливо за ча-
сів великих грецьких геометрів Евкліда та Піфагора). Проходять ще кілька
сторіч, аж поки геометрія починає жити своїм власним життям, поступово
відсторонюючись від реального «землевимірювання», – розвиток йде всере-
дині науки, яка стає надзвичайно абстрактною, «вищою геометрією». Науко-
ві результати і жива практика змушують час від часу виокремлювати з її се-
редовища нові теорії (типу топології, диференціальної, аналітичної чи прое-
ктивної геометрії) та зорієнтовані на практику нові суміжні науки (геодезія,
картографія), поруч з ними існує і розвивається т. зв. «елементарна геомет-
рія», що викладається в школах і базується на теоремах великих греків (прав-
да, висновкам цих теорем, буває, тепер важко знайти практичний зміст).
Так поступово класична геометрія відходить в історію науки, зате її ідеї і ба-
зові принципи відтворюються у різноманітних молодих наукових напрям-
ках, іноді досить екзотичних, як то оптоелектроніка чи комп’ютерна графіка,
та й реалізуються, відповідно, зовсім інакше, зокрема, у вигляді різноманіт-
них геоінформаційних систем, якими користуються в задачах навігації
(пам’ятаючи, що «navy» – морський флот, цими комп’ютеризованими наві-
гаційними системами уявно перекинуто місток до давньоєгипетських моря-
ків-навігаторів – одних з перших постановників геометричних задач).
Отже, задум щодо заповнення інструментальної ніші між теорією й практикою в
економіці шляхом формування нового наукового напряму наприкінці 20-их
років ХХ ст. реалізували троє молодих науковців: норвезький економіст Раг-
нар Фріш (Frisch) та двоє американців – відомий статистик Ірвінг Фішер
(Fisher) та математик Чарльз Рус (Roos), за активної підтримки майже сотні
спеціалістів з різних держав вони сформували сучасний науковий напрям
під назвою «економетрія» (економетрика, econometrics). На диво відома точ-
на дата народження економетрії – 29.12.1930, коли у м. Клівленді (США) було
засновано Економетричне товариство (Міжнародне товариство з розвитку
економічної теорії із застосуванням статистики та математики).

Рагнар Фріш, економіст Ірвінг Фішер, статистик Чарльз Рус, математик
«Батьки» сучасної економетрії

Таким чином, сучасна наука під назвою «економетрія» – це спільний «продукт»
трьох фундаментальних наук – економіки, статистики та математики та їх
«родичів»: економічної теорії, математичної економії, економічної та мате-
матичної статистики.

9