Економіко-математичне моделювання світогосподарських процесів: контрольна робота 01.12.2014 14:26
МЕТОДИЧНІ РЕКОМЕНДАЦІЇ ЩОДО ВИКОНАННЯ
ЗАВДАННЬ КОНТРОЛЬНОЇ РОБОТИ
Завдання 6. Статичні та динамічні моделі міжгалузевого балансу.
Балансові моделі, як статистичні, так і динамічні широко застосовуються в математичному моделюванні економічних систем і процесів. В основі створення цих моделей лежить балансовий метод, тобто метод співставлення матеріальних, трудових, фінансових ресурсів та потреб в них. Якщо описувати економічну систему в цілому, то під балансовою моделлю розуміють систему рівнянь, кожна з яких виражає вимогу балансу між обсягом продукції, яку виробляють окремі економічні об’єкти та сукупною потребою в цій продукції. При такому підході система, яка складається з економічних об’єктів, кожен з яких випускає деякий продукт (ресурс), частина його споживається (використовується) іншими об’єктами системи. Основу інформаційного забезпечення балансових моделей в економці складають матриці коефіцієнтів витрат ресурсів за конкретними напрямками їх використання.
Приклад 6.1. На розрахунковий період задано матрицю міждержавних зв’язків типу „витрати-випуск” (таблиця 6.1.).
Таблиця 6.1.
Країна Імпортер Кінцевий продукт Разом
1 2 3 4 5 Експорт Спожив. Накоп.
Експортер 1 13,5 95,2 44,2 12,2 9,3 76,0 78,2 5,0 333,6
2 46,3 96,3 69,7 36,8 68,6 72,1 54,3 11,0 455,1
3 62,7 53,0 77,2 41,8 68,6 79,1 54,3 73,9 510,6
4 40,2 83,3 16,2 57,5 76,6 59,8 86,3 25,2 445,1
5 86,1 92,0 42,2 60,3 43,8 57,5 23,3 43,4 448,6
Імпорт 20,4 8,5 62,7 56,8 43,6
Оплата праці 13,6 5,6 41,8 37,8 29,1
Прибуток 50,9 21,2 156,7 141,9 109,0
Разом 333,6 455,1 510,6 445,1 448,6 2193,0
Припустимо, що прогнозуються зміни в експортній політиці країн такі, що нові значення експорту дорівнюють (таблиця 6.2.):
Таблиця 6.2.
Країна 1 2 3 4 5
Експорт 79,0 69,3 84,1 55,2 50,7
Знайти нові значення валового випуску та виробничих витрат .
Розв’язання.
Для розрахунку змін обсягів виробництва продукції країнами внаслідок змін у експорті, імпорті, невиробничому споживанні необхідно розв’язати відносно систему лінійних рівнянь:
де – змінений кінцевий продукт і-ї країни-експортера внаслідок змін величину експорту від до , – величина споживання, – величина накопичення і-ї країни-експортера. Тут – невідомі (n невідомих, n рівнянь), – коефіцієнти прямих витрат. Розв’язок цієї задачі можна знайти з матричної рівності
,
де – вектор-стовпчик валових випусків, – змінений вектор кінцевого продукту, – матриця коефіцієнтів прямих витрат, ¬– одинична матриця розмірності .
Розмістимо умову задачі в діапазоні A1:K11 робочого аркушу, а нові значення експорту – у вигляді стовпчика у діапазоні M3:M7 (рис.6.1.).
Рис. 6.1.
Знайдемо послідовно
1. матрицю коефіцієнтів прямих витрат (рис.6.2.);
Рис.6.2.
2. матрицю (рис.6.3.);
Рис.6.3.
3. За допомогою функції МОБР() знайдемо обернену матрицю (рис.6.4.);
Рис.6.4.
4. нові значення кінцевого продукту (рис.6.5.);
Рис. 6.5.
5. За допомогою функції МУМНОЖ() знайдемо добуток матриці та вектора кінцевого продукту (рис.6.6.);
Рис. 6.6.
6. Матрицю нових виробничих витрат (рис.6.7.);
Рис. 6.7.
Зауваження 6.1. Функції МОБР() та МУМНОЖ() є функціями масивів, тому перед їх застосуванням слід виділити діапазон відповідного розміру, та відповідно, а після вибору параметрів натиснути комбінацію Ctrl+Shift+Enter.
Отримаємо відповідь:
• вектор валового випуску
;
• матриця виробничих витрат:
.
Динамічні балансові моделі
В статичному балансі потоки капіталовкладень не розглядаються у розрізі галузей-споживачів, а відображаються загальною величиною у складі кінцевого продукту , де – номер галузі. В динамічній схемі кінцевий продукт містить продукцію -ї галузі, що йде на приватне та суспільне використання, накопичення невиробничої сфери, приріст оборотних фондів, незавершеного будівництва, на експорт. Таким чином, сума потоків капіталовкладень та кінцевого продукту динамічної моделі дорівнює кінцевому продукту статичного балансу:
тому рівняння розподілу продукції в динамічному балансі матиме вигляд:
Міжгалузеві потоки поточних витрат можна подати через валову продукцію галузей за допомогою коефіцієнтів прямих матеріальних витрат:
.
На відміну від потоків поточних витрат, міжгалузеві потоки капіталовкладень пов’язані не з усією величиною випуску продукції, а обумовлюють приріст продукції. При цьому у розглядуваній моделі припускається, що приріст продукції поточного періоду обумовлений вкладеннями, проведеними в цьому ж періоді. Якщо поточний період позначити через , то приріст продукції дорівнює різниці абсолютних рівнів виробництва в період та в попередній -й період:
Вважаючи, що приріст продукції є пропорційним приросту виробничих фондів, можна записати:
З останньої рівності випливає, що
і економічний зміст цих коефіцієнтів полягає в тому, що вони показують яку кількість продукції -ї галузі необхідно вкласти в -у галузь для збільшення виробничої потужності -ї галузі на одиницю продукції. Припускається, що виробничі потужності використовуються повністю і приріст продукції дорівнює приросту потужності. Коефіцієнти називаються коефіцієнтами капіталовкладень.
За допомогою коефіцієнтів прямих матеріальних витрат і коефіцієнтів капіталовкладень систему рівнянь для можна подати у вигляді:
Ця система є системою лінійних різницевих рівнянь першого порядку. Її можна звести до звичайної системи лінійних рівнянь якщо врахувати той факт, що обсяги валової та кінцевої продукції відносяться до деякого періоду , а приріст валової продукції визначено у порівнянні з -им періодом:
Звідси можна записати наступні співвідношення
Нехай відомі рівні валової продукції усіх галузей у попередньому періоді (величини ) і кінцевий продукт галузей у періоді (величини . Тоді останні співвідношення будуть системою лінійних рівнянь з невідомими рівнями виробництва -го періоду . Таким чином, розв’язок динамічної системи лінійних рівнянь дозволяє визначити випуск продукції у наступному періоді в залежності від рівня, досягнутого у попередньому періоді. Зв’язок між періодами встановлюється через коефіцієнти капіталовкладень , які характеризують фондоємність одиниці приросту продукції.
Приклад 6.2. Нехай в динамічній балансовій моделі задано матрицю коефіцієнтів прямих витрат ( ), таблиця 6.3., та коефіцієнтів капіталовкладень ( ), таблиця 6.4., а також значення валового випуску за попередній період та кінцевого продукту на поточний період , таблиця 6.5.
Таблиця 6.3.
0,106 0,025 0,034 0,127 0,012
0,118 0,015 0,074 0,092 0,031
0,069 0,022 0,070 0,044 0,017
0,143 0,034 0,028 0,004 0,008
0,111 0,101 0,161 0,164 0,126
Таблиця 6.4.
0,0029 0,0037 0,0057 0,0032 0,0098
0,0066 0,0052 0,0028 0,0088 0,0042
0,0017 0,0061 0,0050 0,0071 0,0045
0,0089 0,0046 0,0064 0,0028 0,0042
0,0023 0,0062 0,0016 0,0046 0,0053
Таблиця 6.5.
Країна 1 2 3 4 5
Валовий випуск за
попередній період,
298,0 436,0 105,0 140,0 265,0
Кінцевий випуск на
поточний період,
177,9 131,5 146,7 131,6 199,3
Знайти значення валового випуску на поточний період .
Розв’язання.
Для моменту часу введемо наступні позначення: вектор валового випуску галузей , вектор кінцевого продукту . Також позначимо матрицю коефіцієнтів прямих витрат та матрицю коефіцієнтів капіталовкладень . Тоді систему рівнянь динамічного балансу можна записати у матричному вигляді:
.
Звідси
.
На робочому аркуші розташуємо умову задачі так, як показано на рис. 6.8.
Рис. 6.8.
У діапазоні B16:F20 обчислимо матрицю (рис.6.9.).
Рис. 6.9.
За допомогою функції МОБР() знайдемо в діапазоні B23:F27обернену матрицю (рис.6.10.).
Рис. 6.10.
Скористаємося функцією МУМНОЖ() для відшукання значення у діапазоні I9:I13 (рис.6.11.).
Рис. 6.11.
Після цього в діапазоні L9:L13 розрахуємо значення (рис.6.12).
Рис. 6.12.
Нарешті знову скориставшись функцією МУМНОЖ() у діапазоні O2:O6 знайдемо відповідь на питання задачі (рис. 6.13.).
Рис.6.13.
Відповідь: .
Завдання 7. Графічний метод розв’язання задачі лінійного програмування. Симплекс-метод.
Розглянемо графічний метод розв’язку наступної задачі лінійного програмування:
Знайти вектор , координати якого задовольняють системі обмежень
для якого цільова функція досягає максимального значення.
Графічний метод розв’язку задачі лінійного програмування виконується у два етапи:
побудова області допустимих розв’язків задачі;
пошук серед усіх точок області допустимих розв’язків такої точки , що значення є максимальним.
Геометрична інтерпретація множини розв’язків лінійної нерівності.
Розглянемо лінійну нерівність . Множина точок площини , для яких виконується рівність є прямою, яку називають граничною прямою. Гранична пряма розбиває площину на дві півплощини. Координати точок з однієї півплощини задовольняють нерівності , а з іншої – нерівності . Отже, геометричною інтерпретацією множини розв’язків лінійної нерівності є півплощина, що знаходиться з однієї сторони граничної прямої, включаючи граничну пряму.
Для того, щоб визначити яка саме півплощина складає множину розв’язків лінійної нерівності, слід обрати довільну точку координатної площини , яка не належить граничній прямій, і підставити її координати у нерівність . Якщо координати точки задовольняють нерівності , тоді уся півплощина, що містить точку ,є шуканою множиною розв’язків; якщо нерівність не виконується, тоді множиною розв’язків є півплощина, що не містить точку .
Наприклад, геометричною інтерпретацією множини розв’язків нерівності є півплощина, що зображена на рис. 7.1. штрихованими лініями. Покажемо це.
Рис. 7.1.
Передусім, побудуємо граничну пряму . Ця пряма ділить площину на дві півплощини. Оскільки точка їй не належить, і координати точки задовольняють нерівність , тому множиною розв’язків даної нерівності є півплощина, що містить початок координат.
Геометрична інтерпретація множини розв’язків системи лінійних нерівностей.
Розглянемо систему лінійних нерівностей
відносно змінних . Спільна частина (перетин) всіх множин розв’язків окремих нерівностей системи визначатиме множину розв’язків системи лінійних нерівностей. На рис. 7.2. зображено приклади множин розв’язків системи лінійних нерівностей.
Рис. 7.2.
Наприклад, знайдемо множину розв’язків системи
.
Для кожної нерівності системи побудуємо відповідну граничну пряму. Підставимо координати точки в кожну нерівність і знайдемо півплощину, що є множиною розв’язків цієї нерівності. Нарешті знайдемо перетин усіх півплощин і отримаємо множину розв’язків системи (рис 7.3.).
Рис. 7.3.
Пошук точки максимуму цільової функції задачі лінійного програмування.
Припустимо, що область допустимих розв’язків задачі лінійного програмування вже знайдено. Покажемо як серед усіх точок області допустимих розв’язків знайти точку таку, що цільова функція досягає максимального значення. Без обмеження загальності припустимо, що і розглянемо функцію .
При фіксованому значенні рівняння визначає пряму на площині , а при зміні значень – сімейство паралельних прямих. Множина точок , для яких величина приймає фіксоване значення , називається лінією рівня для функції . Вектор називається градієнтом функції і позначається . Градієнт, обчислений в точці , є перпендикулярним до лінії рівня, що проходить через точку . Оскільки , , то .
Зобразимо на одному рисунку область допустимих розв’язків, градієнт та лінію рівня так, щоб вона перетинала область допустимих розв’язків. Будемо переносити паралельно лінію рівня вздовж напрямку, що визначається вектором доти, доки вона не пройде через останню спільну з областю допустимих розв’язків точку. Координати цієї точки і будуть оптимальним розв’язком задачі лінійного програмування. Припустимо, що це точка з координатами . Для знаходження координат точки необхідно розв’язати систему лінійних рівнянь, яким геометрично відповідають прямі, що перетинаються в точці . Оптимальне значення цільової функції дорівнює . На рис. 7.4. наведено приклад знаходження точки для різних варіантів розташування вектора та вибору лінії рівня .
Зауваження 7.1. У випадку розгляду задачі на області допустимих розв’язків лінію рівня слід паралельно переносити проти напрямку вектора доти, доки вона не пройде через останню спільну з областю допустимих розв’язків точку.
Приклад 7.1. Фірма випускає продукцію двох типів, тип та тип , для чого використовують два види взаємозамінного обладнання. Час використання кожного типу обладнання обмежено значеннями 120 та 260 год. В таблиці 7.1. наведено норми витрат часу на виготовлення одиниці кожного типу продукції. План випуску продукції складає 50 одиниць, продукції – 70 одиниць.
Визначити яку кількість продукції обох типів необхідно випустити на кожному з типів обладнання щоб витрати часу були мінімальними.
Рис. 7.4.
Таблиця 7.1.
Тип
продукції
Тип
обладнання
Максимальний час використання обладнання
1 2 4 120
2 4 2 260
План випуску 50 70
Розв’язання.
Позначимо – кількість продукції -го виду, яку повинен випустити -й тип обладнання, , . Побудуємо математичну модель задачі. Цільова функція, що описує витрати часу, матиме вигляд
.
Обмеження на використання робочого часу:
Обмеження на виконання плану:
Враховуючи також, що величини є невід’ємними, отримаємо математичну модель задачі:
Для того, щоб розв’язати задачу графічним методом, перетворимо модель. Виразимо змінні та через змінні та відповідно,
і підставимо ці значення в цільову функцію та обмеження. В результаті отримаємо задачу
На площині побудуємо граничні прямі, які відповідають обмеженням задачі: , , , . Після цього знайдемо область допустимих розв’язків з урахуванням того, що , (рис. 7.5.).
Далі побудуємо вектор . Оскільки суттєвим є напрямок вектора , то можна зобразити його на рисунку з довільною довжиною. Перпендикулярно до вектора проведемо лінію рівня , або , і будемо переміщувати її проти напрямку вектора , оскільки розглядається задача пошуку мінімального значення функції . В результаті, отримаємо точку , яка є перетином прямих та , тобто . Знайдемо значення змінних та :
Рис. 7.5.
та відповідне значення функції :
год.
Запишемо результати у вигляді таблиці 7.2.
Таблиця 7.2.
Тип
продукції
Тип
обладнання
Час використання обладнання
1 50 0 100
2 0 70 140
Разом 50 70 240
У випадку залежності цільової функції від більше ніж двох змінних застосовують алгоритм симплекс-методу. У табличному процесорі MS Excel оптимізаційні задачі розв’язуються за допомогою надбудови „Поиск решения”. Зазвичай в меню „Сервис” відсутня команда „Поиск решения”. В цьому випадку необхідно встановити цю надбудову. Оберіть команду „Сервис” -> „Надстройки” і активізуйте надбудову „Поиск решения”. (рис. 7.6.).
Рис 7.6.
Якщо ж цієї надбудови немає у наведеному діалозі, то необхідно звернутися до панелі керування Windows, обрати пункт „Установка и удаление программ” і за допомогою програми установки Excel (або Office) встановіть надбудову „Поиск решения”.
Після вибору команди „Сервис” -> „Поиск решения” з’явиться діалогове вікно „Поиск решения” (Рис. 7.7.).
В діалоговому вікні „Поиск решения” є три основних параметра:
• «Установить целевую ячейку»
• «Изменяя ячейки»
• «Ограничения»
Рис 7.7.
Спочатку необхідно вказати поле „Установить целевую ячейку”. В усіх задачах надбудови „Поиск решения” оптимізується результат в одній з комірок робочого листа (надалі результуюча комірка). Ця комірка зв’язана з іншими комірками робочого листа за допомогою формул. Надбудова „Поиск решения” використовує формули, які впливають на значення в результуючій комірці, для перевірки отриманих розв’язків. Можна обирати пошук найменшого або найбільшого значення для результуючої комірки, або вста¬новити у результуючій комірці наперед задане значення.
Наступним важливим параметром надбудови „Поиск решения” є параметр „Изменяя ячейки”. Значення в цих комірках повинні впливати на результуючу комірку. Можна вказувати до 200 змінюваних комірок. До змінюваних комірок є дві основні вимоги: 1) вони не повинні містити формул, і 2) зміна їх значень повинна змінювати значення результуючої комірки. Іншими словами, результуюча комірка залежить від змінюваних комірок.
Третій параметр, який необхідно вказати, – це перелік обмежень. Усі обмеження мають вигляд, як показано на рис. 7.8., і складаються з 3-х частин: 1) посилання на комірку з формулою, яка представляє обмеження; 2) відношення ( , ціле або двійкове значення); 3) значення обмеження у вигляді числа або посилання на комірку з відповідним значенням обмеження. Якщо задача містить декілька обмежень, то для спрощення їхнього внесення використовується кнопка „Добавить”.
Рис. 7.8.
Таким чином, для розв’язку задачі лінійного програмування необхідно зробити наступні кроки:
1) Вказати адреси комірок, в яких буде розміщено результат розв’язку (змінювані комірки).
2) Внести початкові дані.
3) Внести залежності для результуючої функції.
4) Внести залежності для обмежень.
Запустити надбудову „Поиск решений”.
5) Встановити посилання на результуючу та змінювані комірки.
6) Внести обмеження.
7) За необхідності вказати додаткові параметри розв’язку задачі лінійного програмування.
Розглянемо цю послідовність дій на конкретному прикладі.
Приклад 7.2. Підприємство експортує 5 видів продукції, використовуючи для цього 4 типи сировини. Витрати сировини на одиницю кожного типу продукції, кількість сировини та прибуток від одиниці продукції задано у таблиці 7.3.
Таблиця 7.3.
Сировина Витрати сировини на одиницю продукції Запаси сировини
3 2 4 2 2 800
2 5 5 3 3 1500
3 5 3 2 5 1800
5 3 4 3 4 2000
Прибуток від одиниці продукції 200 280 350 210 180
Яку кількість продукції кожного виду необхідно експортувати, щоб отримати максимальний прибуток?
Розв’язання. Запишемо економіко-математичну модель задачі. Нехай позначають кількість -го типу продукції, яку експортує підприємство, . Тоді цільова функція задачі матиме вигляд
з системою обмежень
1. Оберемо адреси комірок, у яких буде розташовано результат (змінювані комірки) та значення цільової функції (результуюча комірка). В нашій задачі це буде діапазон B11:F11 для змінюваних комірок та G11 для результуючої комірки.
2. У діапазоні A1:G7 створимо таблицю з умовою задачі (рис. 7.9.)
Рис. 7.9.
3. Цільову функцію можна подати у вигляді скалярного добутку векторів та . Скалярний добуток векторів можна отримати за допомогою вбудованої функції СУММПРОИЗВ(), як показано на рис. 7.10.
Рис. 7.10.
4. Аналогічно створимо у діапазоні G12:G15 формули, які представлятимуть обмеження нашої задачі без символу і правої частини нерівностей системи обмежень, рис. 7.11.
Рис. 7.11.
Виконаємо команду „Сервис”->”Поиск решения”.
5. Вказуємо посилання на результуючу комірку (G11), в області „Равной:” обираємо опцію „максимальному значению”. В поле „Изменяя ячейки” вказуємо діапазон змінюваних комірок B11:F11 (рис. 7.12.):
Рис. 7.12.
6. Вказуємо обмеження задачі. Наприклад, перша нерівність системи обмежень буде внесена так, як показано на рис. 7.13.
Рис. 7.13.
Аналогічно вносимо решту обмежень задачі. Умову про невід’ємність значень вкажемо наступним чином (рис. 7.14.):
Рис. 7.14.
Остаточно, отримаємо наступний вигляд діалогу пошуку розв’язку задачі лінійного програмування (рис. 7.15.).
Рис. 7.15.
7. За потреби можна змінити параметри розв’язку здачі, клікнувши на кнопці „Параметры” (рис. 7.16.). Наприклад, в цьому діалозі можна вказати максимальний час роботи алгоритму, максимальну кількість ітерацій, величину похибки тощо. Також в цьому діалозі можна встановити опцію „Неотрицательные значения”. Тоді для значень всіх змінюваних комірок, для яких у списку обмежень не вказано нижню межу, у якості останньої буде встановлено число 0.
Рис. 7.16.
Натиснувши кнопку „Выполнить” в основному діалозі, почне виконуватися алгоритм і через деякий час з’явиться вікно „Результаты поиска решения” (рис. 7.17.). В цьому діалозі програма повідомляє про результат роботи алгоритму (розв’язок знайдено чи ні), а також пропонує обрати тип звіту для отримання більш детальних результатів роботи алгоритму. Обравши опцію „Сохранить найденное решение” і клікнувши кнопку „ОК”, отримаємо у змінюваних комірках значення змінних , а у результуючій комірці – шукане максимальне значення цільової функції (рис. 7.18.).
Рис. 7.17.
Таким чином, розв’язком даної задачі буде вектор і .
Рис. 7.18.
Завдання 8. Транспортна задача.
Під терміном транспортна задача зазвичай розуміють тип задач, у яких мова йде про раціональне переміщення певного «товару» від «виробника» до «споживача». При цьому під «товаром» розуміється довільний продукт, як матеріальний, так і нематеріальний; «виробником» може бути довільне джерело «товару», не обов’язково його фізичний виробник, а «споживачем» – довільний кінцевий пункт руху «товару» від «виробника». Рух товару від виробника до споживача вважається одностороннім.
Припустимо, що існує джерел певного однорідного товару («виробники») та пунктів споживання цього товару («споживачі»). Нехай потужності виробництва товару складають одиниць товару, а потреби споживачів – одиниць товару. Відомі величини витрат на перевезення одиниці товару від -го виробника до -го споживача. Задача полягає у відшуканні такого плану перевезень товару від виробників до споживачів, за якого виконувалися б обмеження на виробництво товару, повністю задовольнялися потреби споживачів, а загальна вартість перевезення товару була б мінімальною.
Запишемо математичну модель задачі. Нехай позначає кількість товару, відправленого від -го виробника до -го споживача. За умови односторонності руху товару
Витрати на перевезення товару від -го виробника до -го споживача складатимуть , тому задача зводиться до мінімізації функції загальних витрат
за обмежень
Матрицю
називають планом перевезень, а матрицю
– матрицею витрат. Якщо виконується умова
тоді задача називається закритою, інакше задача називається відкритою. Відкриту задачу завжди можна звести до закритої наступним чином:
• якщо , тоді вводиться -й фіктивний споживач з потребами та витратами на перевезення
• якщо , тоді вводиться -й фіктивний виробник з потужностями виробництва та витратами на перевезення
Приклад 8. Компанія має 3 заводи з виробництва продукції, які експортують її у 5 країн. Задано максимальну потужність виробництва кожного заводу, вартість виробництва та транспортування одиниці товару від кожного заводу до країни-імпортера та мінімальні потреби кожної країни імпортера, таблиця 8.1. Розв’язати задачу засобами MS Excel.
Таблиця 8.1.
Максимальна потужність Імпортери
1 2 3 4 5
Заводи 1 538 12 16 17 11 17
2 756 10 18 15 19 18
3 526 19 17 13 16 19
Потреби 178 181 211 228 243
Розв’язання.
Розташуємо умову задачі в діапазоні A1:H6 так, як показано на рис. 8.1.
Рис. 8.1.
Оскільки сумарні потреби складають 1041 одиниць, а сумарна потужність заводів – 1820 одиниць, то маємо відкриту задачу. Додамо шосту фіктивну країну-імпортер з нульовими вартостями перевезення (рис. 8.2.)
Рис. 8.2.
Для застосування засобу «Поиск решения» слід обрати діапазон, у якому знаходитимуться значення , та створити формули для обмежень задачі. Нехай таким діапазоном буде діапазон D9:I11 (рис.8.3.), тоді у діапазонах C9:C11 та D12:I12 внесемо формули для обчислення сум по рядкам та стовпчикам (рис. 8.4.).
Рис.8.3.
Рис. 8.4.
У комірку C12 внесемо функцію =СУММПРОИЗВ(D3:I5;D9:I11), значення якої необхідно мінімізувати (рис. 8.5.). Виконаємо команду „Сервис”->”Поиск решения”. Вказуємо посилання на результуючу комірку (С12), в області „Равной:” обираємо опцію „минимальному значению”, а в області „Изменяя ячейки” вказуємо діапазон змінюваних комірок D9:I11 (рис. 8.6.).
Рис. 8.5.
Рис. 8.6.
Тепер вказуємо обмеження задачі. Наприклад, умова невід’ємності розв’язку задачі може бути внесена так, як показано на рис. 8.7.
Рис. 8.7.
Аналогічно вносимо обмеження на потужність заводів (рис. 8.8.) та на потреби країн-імпортерів (рис. 8.9.).
Рис. 8.8.
Рис. 8.9.
Остаточний вигляд діалогу пошуку розв’язку задачі лінійного програмування має вигляд такий, як показано на рис. 8.10.
Рис. 8.10.
Виконаємо пошук розв’язку, оберемо «Сохранить найденное решение» та отримаємо розв’язок задачі (рис. 8.11.). Таким чином, оптимальний план перевезень задається матрицею
,
а мінімальна вартість перевезень складає .
Рис. 8.11.
Зауваження 8.1. Припустимо, що в задачі необхідно знайти розв’язок у цілих числах. У цьому випадку необхідно додати обмеження вигляду (рис. 8.12.)
Рис. 8.12.
Тоді розв’язком задачі буде план перевезень
.
При цьому загальна вартість перевезень не зміниться: .
Зауваження 8.2. За допомогою засобу «Поиск решения» можна розв’язувати також і відкриті задачі. Для цього обмеження слід задавати у вигляді
Наприклад, умова, діапазон з розв’язком та діалог «Поиск решения» для попереднього прикладу у випадку пошуку цілочисельного розв’язку матимуть вигляд, представлений на рис. 8.13. та 8.14.
Рис. 8.13.
Рис. 8.14.
Оптимальний план перевезень задається матрицею
.
Завдання 9. Гравітаційна модель.
Гравітаційна модель – це модель макрорівня, яка призначена для дослідження впливу змін економічної динаміки на товарообіг між певними країнами та для пояснення існуючих співвідношень між товарообігом для певних країн. Згідно з цією моделлю товарообіг між країнами та буде прямо пропорційним до добутку ВВП цих країн та обернено пропорційним до квадрату середньої відстані перевезення товару при здійсненні торгівельних операцій між цими країнами. Якщо позначити товарообіг як , ВВП країни як , ВВП країни як , середню відстань перевезень як , то одержимо рівність:
яка, власне, і утворює «гравітаційну» модель. Коефіцієнт є, строго кажучи, індивідуальним для будь-якої пари країн, бо він має враховувати:
- тарифно-митний режим, обмінно-курсову валютну політику (наприклад, застосування різних офіційних курсів обміну валют при здійсненні різних зовнішньо-торгівельних операцій), загальноекономічну політику країн-партнерів та інші складові умов торгівлі між ними;
- особливості структури внутрішнього виробництва, що впливають на склад експортного попиту та пропозиції (наприклад, внаслідок цього товарообіг між близько розташованими нафтовидобувними країнами Середнього Сходу набагато менший за їх товарообіг з розвиненими постіндустріальними країнами);
- специфіку ментальності та історичних традицій партнерів;
- інші чинники.
Коли зазначені чинники зазнають суттєвих змін у часі, застосування «гравітаційної» моделі стає проблемним.
Параметр може бути визначений із статистичних даних. Наприклад, якщо протягом ряду років відомі обсяги товарообігу ( – номер року, ), та ВВП країн за ті ж роки (позначимо їх як , ), для визначення можна застосувати метод найменших квадратів, згідно з яким буде точкою мінімуму функції
.
Знайшовши похідну за цієї функції та прирівнявши її до 0, одержимо
.
Звідси
.
Для одержаного значення можна обчислити прогнозні значення товарообігу. Для оцінки точності прогнозу визначимо за допомогою моделі величину товарообігу для попередніх років та порівняємо з відомим товарообігом за ті ж роки. Маємо:
– прогнозований товарообіг, який можна порівняти з реальним товарообігом за ті ж роки, обчислюючи наступні характеристики:
а) середня відносна похибка оцінки:
б) коефіцієнт детермінації
де – середній річний фактичний товарообіг, – середній річний прогнозований товарообіг. Саме ці показники використовуються для оцінки точності та достовірності моделі. Вона буде тим більшою (кращою), чим менше та чим більше . Зауважимо, що завжди .
Приклад 9. У таблиці 9.1. наведено інформацію про обсяги зовнішньої торгівлі товарами між країнами та за 2005-2009 рр. (у млн. дол. США), у таблиці 9.2. – значення ВВП країн (у млрд. дол. США). Середня відстань перевезень складає .
Використовуючи MS Excel:
1) знайти оцінку коефіцієнта гравітаційної моделі
2) Обчислити середню відносну похибку
та коефіцієнт детермінації .
Таблиця 9.1.
Зовнішня торгівля товарами за 2005-2009 рр., млн. дол. США, .
Країна 2005 2006 2007 2008 2009
2416.3 3453.7 4005.2 4130.9 4406.5
Таблиця 9.2.
ВВП країн за 2005-2009 рр., млрд. дол. США, .
Країна 2005 2006 2007 2008 2009
100.8 124.9 122.0 140.5 116.2
263.7 290.8 324.9 380.1 430.2
Розв’язання. Позначимо За допомогою такої заміни гравітаційна модель зводиться до вигляду
тобто оцінка параметра є оцінкою методу найменших квадратів простої лінійної регресії без вільного коефіцієнта, див. завдання 2 «Парна лінійна регресія».
В діапазоні A1:D6 розташуємо умову задачі (рис. 9.1.), а у стовпчику E обчислимо значення величин .
Рис. 9.1.
Для знаходження значення скористаємось функцією ЛИНЕЙН() з параметром «Конст» рівним 0, тобто вкажемо, що наша модель не містить вільного коефіцієнта (рис. 9.2., 9.3.).
Рис.9.2.
Рис. 9.3.
Таким чином, у комірках A8 та A9 знайдено значення коефіцієнта та його стандартної похибки . Довірчий інтервал для справжнього значення параметра з довірчою ймовірністю 95% має вигляд:
де – значення -критерію, який визна¬чається за заданим значенням рівня значущості і числом ступенів вільності . Оскільки шукається 95%й довірчий інтервал, то , а кількість ступеней вільності . Для відшукання значення скористаємось вбудованою функцією СТЬЮДРАСПОБР() (рис. 9.4.).
Отримаємо довірчий інтервал
,
або
.
Рис. 9.4.
Оскільки число 0 не належить отриманому довірчому інтервалу, то на рівні можна стверджувати, що .
Знайдемо в діапазоні F2:F6 значення скориставшись для цього вбудованою функцією ТЕНДЕНЦИЯ() (рис.9.5.)
Рис. 9.5.
Приклад обчислення значення для показано на рис. 9.6. Для обчислення середньої відносної похибки у діапазоні G2:G6 знайдемо відносні похибки для кожного (рис.9.7.)
Рис. 9.6.
Рис. 9.7.
Усереднивши отримані значення, отримаємо
Для обчислення значення коефіцієнта детермінації помітимо, що
де – вибірковий коефіцієнт кореляції між спостережуваними значеннями обсягів експорту та оціненими за моделлю . Для обчислення вибіркового коефіцієнта кореляції скористаємося вбудованою функцією КОРРЕЛ() (рис. 9.8.),
Рис. 9.8.
звідки і
