Методичні рекомендації для побудови моделей лінійного програмування

Розв’язування оптимізаційних завдань. У завданнях такого типу розв’язують проблеми, обернені до розглянутих вище, тобто як отримати заданий результат, наприклад, максимум прибутку, або звести до мінімуму витрати на виробництво продукції.

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

Приклад. Підприємство може випускати чотири види однотипної продукції (позначимо їх умовно як А, Б, В, Г). Прибуток з одиниці продукції, відповідно, 500, 540, 570 і 600 ум. од. Потрібно визначити таку програму випуску продукції підприємства, за якої воно матиме максимальний прибуток. При цьому слід ураховувати такі обмеження. Загальна кількість випуску обмежується потужністю виробничої лінії та не може перевищувати 800 шт. за місяць. Кожного типу продукції потрібно виготовити не менш як 100 шт.

Максимально можливий фонд робочого часу на підприємстві становить 96 тис. год. Витрати часу на виготовлення кожного типу продукції відповідно становлять: 100, 110, 120, 130 год. Як бачимо, на завдання максимізації прибутку накладається кілька обмежень. їх можна записати у вигляді системи нерівностей:

А + Б + В + Г<= 800;

А + Б + В + Г>=400;

100 А + 110 Б + 120 В + 130 Г <= 96 000.

Такі обмеження у таблицю слід заносити у рядки, а типи продукції — у стовпчики (рис. Д1).

Етапи виконання завдання. Перший рядок заповнити «шапкою»: «Тип продукції» (стовпчик А), по стовпчику на кожен тип продукції, «Обмеження» (стовпчик F), «Разом на програму» (стовпчик Н). Кожний наступний рядок таблиці — дані для кожного з обмежень.

У рядок 6 ввести перше обмеження — на випуск продукції. У клітинку А4 ввести текст «Випуск продукції, од.». Клітинки В4:Е4 мають значення «0», а у клітинку «Обмеження» F4 ввести значення 800. Рядок 4 буде результатним, тобто міститиме програму виробництва, за якої отримають максимальний прибуток. У клітинку ввести G4 текст «Програма виробництва». Клітинки в результаті вирішення задачі оптимізації В4:Е4 міститимуть значення випуску для кожного типу продукції.

 

а) модель лінійного програмування

Рис. Д1. Приклад застосування моделі лінійного
програмування в Microsoft Excel

 

 

б) оформлення модель лінійного програмування

Рис. Д1. Приклад застосування моделі лінійного
програмування в Microsoft Excel

 

У рядок 5 ввести обмеження на витрати часу. В клітинці A5 подати текст «Витрати робочого часу, год.». У клітинки В5:Е5 занести значення витрат для кожного типу продукції, а у клітинку «Обмеження» G5 ввести значення 96 000.

У рядок 6 ввести дані про прибуток. У клітинку А6 подати текст «Прибуток з одиниці», а у клітинки В6:Е6 — значення прибутку для кожного типу продукції.

У рядок 7 ввести дані про мінімальний випуск, який для кожного типу продукції за умовами має становити не менше ніж «100». Тому клітинки В7:Е7 заповнити числом «100».

Ввести розрахункові формули за стовпчиком «Разом на програму» для рядків 4—6. Для кожного з них з цією метою потрібно перемножити значення кожного з типів продукції на майбутній випуск продукції і підсумувати їх. Наприклад, у рядку 6 сумарний прибуток визначимо за формулою =СУММПРОИЗВ(B6:E6;$B$4:$E$4), де В6 — клітинка, що містить дані про прибуток за одиницю товару А, а В4 — його випуск.

Виконати команду «Сервис» ► «Поиск решения..». Якщо цього пункту немає, то виконати команду «Сервис» ► «Надстройки» і встановити позначку біля пункту «Поиск решения». Це пов’язано з тим, що модуля для розв’язування завдання лі­нійного програмування у стандартній конфігурації Excel немає. З’явиться вікно «Поиск решения» (рис. Д2).Всю подальшу роботу проведемо саме у ньому.

 

Рис. Д2. Приклад застосування надстройки
«Поиск решения» в Microsoft Excel

 

Визначити адресу цільової клітинки, формула в якій відповідає поставленій меті. Значення саме цієї клітинки і підлягає оптимізації. Така клітинка може мати максимальне чи мінімальне значення або дорівнювати будь-якому іншому значенню. Цього досягаємо встановленням у вікні перемикача у положення «Установить значение целевой ячейки», яке відповідає максимальному значенню; будь-якому значенню; мінімальному значенню. Одночасно встановлюємо перемикач у положення «Равной максимальному значению».

Визначити, які клітинки потрібно змінювати для пошуку оптимального розв’язку, у нашому прикладі — це отримання максимального прибутку. їх вибирають за допомогою дії «Изменяя ячейки». Ці клітинки мають бути пов’язані з результатом та обмеженнями. У нашому випадку це дані про майбутній випуск продукції, тобто програма виробництва (клітинки $B$4:$E$4).

Введемо обмеження. Для цього у вікні є список «Ограничения». Для введення нового обмеження натиснути кнопку «Добавить». З’явиться вікно «Добавление ограничения»(рис. Д3). Кожне обмеження складається з трьох частин: клітинка обмеження, тобто ліва частина формули; оператор відношення (як правило, це логічна умова між першою і другою частинами); значення обмеження, тобто права частина формули. Ввести перше обмеження, згідно з яким значення у клітинках $G$4<=$F$4, $G$5<=$F$5 (сумарний випуск та сумарні витрати на програму повинні бути меншими, ніж їх значення у клітинках стовпчика «Обмеження»).

 

Рис. Д3. Приклад введення обмежень в надстройці
«Поиск решения» в Microsoft Excel

 

Другою умовою є вимога, за якою кожного типу продукції потрібно виготовити не менш як 100, третьою – обмеження невід’ємності змінних (обмеження матиме вигляд В4:Е4 >= 0).

Електронні таблиці виконують завдання оптимізації методом ітерацій, тобто послідовних наближень, кількість яких обмежена за замовчуванням числом «100». Розв’язок може бути і не знайдений. У цьому разі застосовують такі поправки:

збільшують кількість ітерацій пошуку. Для цього у вікні «Поиск решения» слід натиснути кнопку «Параметры» і збільшити значення у полі «Предельное число итераций»;

збільшують відносну похибку. Для цього виконують ті самі дії і збільшують значення у полі «Относительная погрешность».

Зверніть увагу, що задача може не мати розв’язку, якщо параметри виходять за межі допустимих значень.

Багато корисної інформації можна отримати зі сформованого звіту («Отчет по результатам», «Отчет по устойчивости», «Отчет по пределам»).

Аналогічно використовуючи сиплекс-метод, можна знайти максималь­не значення функції прибутку сільськогосподарського підприємства при заданих обмеженнях (табл. Д1).

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

Область допустимих розв’язків дістаємо так. Задачу представляють в канонічній формі. Для цього в якості невідомих розглядатимемо різновиди сировини. Складемо обмеження, що відображає виконання завдання. Для прикладу розв’яжемо задачу щодо оптимального кормового раціону для курчат.

 

 

Таблиця Д1

Дані про господарську діяльність СТОВ «Україна»

Показник	Озима пшени­ця	Цукрові буряки	Продуктивність корів, кг	Кормо­ві куль­тури	Ре­сурс, тис.
Урожайність, т/га
Собівартість, грн./т
Ціна, грн./т
Вихід кормів, т. к. од./га	0,8	2,0
Витрати живої праці, люд.-днів/га
Витрати меха­ні­зо­ва­ної праці, люд.-днів/га
Частка корів у стаді			0,1	0,2	0,3	0,4
Потреба в кормах, т/гол.				4,7	4,4	4,1

 

 

Таблиця Д2

Значення вихідних параметрів для моделі оптимального кормового раціону*

Поживні речовини	Норма поживних речовин різних видів у готовій кормовій суміші	Вміст поживних речовин у різних кормах, %
в зерні	в соєвих бобах
Білок
Клітковина
Вартість 1 кг корму (в у. о.)	0,4	0,9

* Невелика птахоферма має розрахувати оптимальний кор­мо­раціон для 1000 курчат, яких вирощує до 8-тижневого віку. Нехтуючи тим, що тижневі витрати кормів для курчат залежать від їхнього віку, вважатимемо, що в середньому за 8 тижнів вони досягнуть 500 г. З цією метою кормовий раціон має задовольняти вимоги поживності. Сформулюємо ці вимоги в спрощеному вигляді, враховуючи лише дві поживні речовини: білок і клітковину, які містяться в кормах двох видів – зерні та соєвих бобах. Необхідно скласти математичну модель задачі з визначення маси кожного з кормів, що утворюють кормову суміш мінімальної вартості, задовольняючи вимоги до загальних витрат кормової суміш та її поживності.

 

Нехай – маса зерна в кормовій суміші (кг), – маса соєвих бобів в кормовій суміші (кг). Загальна кількість суміші має становити не менше як 1000*0,5=500 (кг), тобто . Останнє обмеження визначається кількістю та масою курчат. Аналогічно формуємо обмеження щодо поживності кормової суміші: (місткість білку в суміші не менше 20%), (місткість клітковини в суміші не менше 5%). Таким чином, математична модель задачі має такий вигляд.

Знайти

при обмеженнях

.

Сценарний аналіз, реалізованим засобами Excel. Для варіантних фінансово-економічних розрахунків, які ґрунтуються на введенні різних значень аргументів функції, доцільно користуватися сценарним підходом.

Сценарій — це іменована сукупність значень змінюваних комірок. Наприклад, для комірок, які є аргументами фінансових функцій, можна задава­ти різні значення. Команда «Сервис/Сценарий» викликає діалогове вікно «Диспетчер сценариев» для створення, редагування, об’єднання, вилучення і переглядання створених сценаріїв-розрахунків для комірок поточного робочого аркуша (рис. Д4).

 

Рис. Д4. Приклад сценарного підходу засобами Microsoft Excel

 

У вікні «Сценарии» поданий список сценаріїв поточного робочого аркуша. Можливе об’єднання сценаріїв, які знаходяться у відкритих книгах або на інших аркушах поточної робочої книги при натисканні кнопки «Объеденить». Для створення нового сценарію варто натиснути кнопку «Добавить», при цьому з’являється однойменне діалогове вікно. У поле «Название сценария» вводиться ім’я нового сценарію — послідовність символів, максимальна довжина імені не перевищує 255 знаків.

На робочому аркуші виділяються комірки, не обов’язково суміжні, значення яких будуть змінюватися. Як правило, це комірки, які містять початкові значення (аргументи) для обчислення функції. Для виділення несуміжних комірок використовується клавіша Ctrl. Склад змінюваних комірок для різних сценаріїв того самого робочого аркуша може змінюватися. Це дає можливість готувати аргументи і виконувати варіантні розрахунки на одному робочому аркуші для різних фінансових функцій. У вікні «Примечание» можна записати текст, який пояснює сценарій. За умовчуванням сюди заноситься ім’я користувача і дата створення сце­нарію. Ім’я користувача можна змінити за допомогою команди «Сервис/Параметры» вкладки «Общие», поле «имя пользователя».

За допомогою перемикача «Запретить изменения» реалізується захист зна­чень змінюваних комірок від редагування. Перемикач «Скрыть» дозволяє не показувати ім’я сценарію в списку (тимчасово неактивний сценарій). При натисканні кнопки ОК з’являється діалогове вікно для введення значень змінюваних комірок. Після завершення введення даних можна сформувати новий сценарій — кнопка «Добавить» (відбувається повернення в основне вікно «Додавление сценария»), або завершити роботу — кнопка ОК і перейти у вікно «Диспетчер сценариев».

Для переглядання результатів підстановки значень змінюваних комірок за певним сценарієм у діалоговому вікні «Диспетчера сценариев» варто вибрати зі списку «имя сценария» і натиснути кнопку «Вывести».

Excel виконує підстановку значень змінюваних комірок сценарію і виконує розрахунок значення функції. Всі зміни відображаються на ро­бочому аркуші в комірках, які містять формули і мають посилання на змінювані комірки сценарію. Кнопка «Закрыть» забезпечує вихід із вікна «Диспетчер сценаиев», при цьому в змінюваних комірках зберігаються значення останнього сценарію, який брав участь у перегляданні. Кнопка «Отчет» призначена для підготовки звітів зі сценаріїв, при її натисканні з’являється діалогове вікно для вибору типу підсумкового звіту. У полі «Ячейки результата» вказується адреса комірок, значення яких залежать від змінюваних комірок сценаріїв. Формується два типи звітів: результати сценарію — табличний звіт, який містить для кожного сценарію склад змінюваних комірок і значення вибраних комірок для результатів; зведена таблиця результатів підстановки значень у змінювані ко­мірки та обчислення результатів підстановки. Приклад застосування сценарного підходу наведемо в наступному прикладі.

Приклад. Фірма планує вкласти певну частину своїх коштів у роз­ви­ток економічної діяльності. Альтернативні варіанти розвитку задано певними стратегіями. Зовнішньоекономічні умови, які впли­ватимуть на показники ефективності кожної стратегії ймовірні. Виграші за реалізації кожної стратегії та ймовірності зовнішньоекономічних умов наведено в табл. Д3.

Таблиця Д3

Виграші при реалізації кожної стратегії та ймовірності зовнішньоекономічних умов

Стратегії	Прибуток за зовнішньоекономічних умов
	0,64	0,18	0,05	0,08	0,05

Необхідно визначити ефективність і ризикованість кожної стратегії розвитку фірми та зробити висновок, за якою стратегією доцільно вкладати кошти та чому.

Методичні рекомендації

1. Визначення середньої ефективності кожної стратегії:

,

де – номер стратегії; – номер зовнішньоекономічної умови; – прибуток, який може отримати фірма від стратегії за умов.

2. Кількісна оцінка ризикованості кожної стратегії на основі показників варіації:

а) за дисперсією. Дисперсія є одним з абсолютних показників кількісної оцінки ризику. Чим більша дисперсія, тим більший ризик, притаманний стратегії:

;

б) на основі стандартного відхилення (сигма). Сигма – це середнє лінійне відхилення від фактичного значення прибутку, показник мобільної ефективності (у світо­вій літературі – ризик). Звідси, чим менше сигма, тим надій­ніша стратегія:

;

в) за коефіцієнтом варіації. Чим більше значення коефіцієнта варіації, тим більш ризико­ваною та менш ефективною є стратегія:

;

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

;

;

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

;

є) за коефіцієнтом ризику. Чим менший коефіцієнт ризику (), тим менший ризик:

.

3. Інтервальна оцінка ефективності кожної стратегії та визна­чення типу ризику кожної з них. Для її визначення необхідно розрахувати граничну похибку, яка є абсолютним показником інтегральної оцінки ризику:

де – критерій Стьюдента (таблична величина, для даного випадку рівна 2,76), – рівень значущості, або ймовірність, з якою відхиляється рівень граничної похибки (для задачі ).

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

Встановимо тип ризику через підрахунок відсотка втрат для кожної стратегії. Оцінювання господарського ризику передбачає необхідність побудови кривої ризику – кривої розподілу ймовірностей виник­нення певного рівня втрат/прибутку. Це надзвичайно складна задача, тому на практиці доводиться обмежуватися спрощеними підходами, оцінюючи ризик за одним або кількома показниками. Процес побудови кривої ризику, як правило, включає такі етапи: встановлення зон ризику, в рамках яких утрати не перевищують визначеного рівня; визначення залежності ймовірності втрат від їх рівня; побудова типової кривої ймовірностей одержання певного ріння прибутку; побудова кривої ризику на основі кривої ймовірностей отримання певного рівня прибутку та зон ризику.

Основною характеристикою є коефіцієнт ризику, що визначається відношенням всього капіталу підприємства до суми його активів або відношення втрат до розміру власних коштів підприємства. Виділяють такі зони ризику підприємницької діяльності: безризикова зона ( 0,00), зона допустимого ризику ( 0 – 0,5), зона критичного ризику ( 0,5 – 0,75), зона катастрофічного ризику ( 0,75 – 1,00).

Методичні рекомендації та вимоги до виконання і захисту курсової роботи з дисципліни «Економічна діагностика» підготовлені к.е.н., доцентом кафедри економіки АПК Яценко О.М. та к.е.н., доцентом кафедри організації виробничих та інформаційних систем Плотніковою М.Ф.