МЕТОДИЧНІ ВКАЗІВКИ ДО САМОСТІЙНОЇ РОБОТИ

Таблиця 4 – Початковий вигляд допоміжної електронної таблиці

вихідних даних на відкритому листі редактора Excel

Спочатку в 1-й рядок табл. 4 уводяться позначення Х1 – Х6, Z (відмічено бірюзовим кольором). Комірки, що знаходяться в 2-му рядку під позначеннями,

резервуються для результатів розрахунків. Так, комірки A2-F2 призначені для майбутнього оптимального плану виробництва продукції.

У комірках 3-гоу рядка А3-F3, а також А5-А10, I5-І13 табл. 4 записуються константи – коефіцієнти цільової функції, умови не негативності та ліміти випуску продукції (за попитом й потужністю), а також виробничих ресурсів з математичної моделі (15), які виділені жовтим кольором. При цьому комірка для розрахунку цільової функції Z з координатами G2 задається у вигляді функції

=СУММПРОИЗВ(A2-F2;A3-F3). Оскільки на цьому етапі оптимальний план поки що не знайдений (Х1 = Х2 = … = Х6 = 0), то в комірці G2 з'являється 0.

Далі заповнюється система обмежень. У комірках С5-C10 i G5-G10

повторюються результати розрахунку оптимального плану виробництва продукції, тобто С5=А2, С6=В2, … , С10=F2. А також G5=А2, G6=В2, … ,

G10=F2. У комірках В5-В10 та Н5-Н13 ставляться нерівності з другої і третьої системи обмежень моделі (15).

21

Останні три рядки табл. 4 відображають витрати кожного ресурсу на оптимальний випуск продукції кожного виду. Наприклад, А11=8478,1*А2, В11=

238,61*В2, … , F11=1587,59*F2. У комірці G11 відображається вся фактична витрата першого ресурсу (кошти за спожиті матеріальні ресурси) як сума комірок А11-F11: G11=СУММ(А11-F11). За аналогію задаються комірки G12, G13, що характеризують витрати другого і третього ресурсів підприємства – коштів на оплату праці та електроенергію. Очевидно, що згідно моделі (15) значення комірок G11, G12, G13 не може перевищувати запаси наявних трьох ресурсів на виробництво продукції – комірки І11, І12, І13.

У результаті отримаємо наступний кінцевий вигляд допоміжної електронної таблиці вихідних даних (табл. 5).

Таблиця 5 – Кінцевий вигляд допоміжної електронної таблиці вихідних даних

Після заповнення допоміжної електронної табл. 5 вихідних даних переходять до другого етапу використання програми «Поиск решения» –

застосування керуючої панелі «Параметры поиска решения», яка з'являється при натисканні на поле «Поиск решения» в розділі «Анализ» того ж листа редактора

Excel (рис. 2).

22

Рис. 2 – Вихідний вигляд керуючої панелі «Параметры поиска решения»

Дана панель дозволяє задати важливіші взаємозв’язки і параметри майбутнього рішення задачі лінійного програмування, що з’явиться в електронній табл. 5. Так, у верхній частини панелі є поле «Оптимизировать целевую функцию», в якій указується адреса комірки табл. 5 для розрахунку цільової функції Z (G2). Надалі виставляється критерій оптимальності рішення економічної задачі – Максимум, Мінімум чи конкретне Значення. Поле «Изменяя

23

ячейки переменных» призначене для вказівки адреси майбутнього рішення задачі оптимізації – комірки А2-F2 табл. 5.

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

(лівий бік системи обмежень) можна задати автоматично, поставивши прапорець в полі «Сделать переменные без ограничений неотрицательными». Нерівності правого боку табл. 5 формуються за допомогою опцій «Добавить», «Изменить», «Удалить» тощо. Тут відображаються 9 нерівностей: 6 стосуються обмежень зверху на випуск продукції (комірки G5-I5, G6-I6, … , G10-I10), а 3 – обмеження на ресурси підприємства (комірки G11-I11, G12-I12, G13-I13).

У полі «Выберите метод решения» задається один із трьох можливих варіантів розрахунку оптимального плану поставленої задачі: 1. «Поиск решения линейных задач симплекс-методом». 2. «Поиск решения нелинейных задач методом ОПГ». 3. «Эволюционный поиск решения».

Як відомо, симплекс-метод розроблений саме для розв'язку задач лінійної оптимізації і широко описаний в літературі з математичного програмування.

Щодо методу ОПГ («нелинейный метод обобщенного понижающего градиента»),

то він використовується при розв'язку гладких нелінійних задач. Еволюційний метод застосовується при пошуку рішення негладких нелінійних задач [2; 3; 4; 5; 6].

Повністю заповнена панель «Параметры поиска решения» на базі табл. 5

має вигляд, наведений на рис. 3.

Наприкінці перевірте ще раз усі заповнені комірки та обмеження

(нерівності) на заповненій панелі «Параметры поиска решения». Якщо щось не сходиться, потрібно переглянути вихідні дані досліджуваної задачі оптимізації,

введені формули та обмеження.

Тепер щоб знайти розв'язок поставленої задачі оптимізації виробничої програми підприємства достатньо в полі «Выберите метод решения» обрати опцію «Поиск решения линейных задач симплекс-методом» і натиснути на кнопку «Найти решение».

24

Рис. 3 – Повністю заповнена керуюча панель «Параметры поиска решения»

На робочому листі редактора Excel з'являються діалогове вікно «Результаты поиска решения» (рис. 4).

Якщо тепер у діалоговому вікні натиснути опцію «Сохранить найденное решение» (рис. 4), то в кінцевому варіанті допоміжної електронної табл. 5

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

25

Рис. 4 – Діалогове вікно «Результаты поиска решения»

Таблиця 6 – Результати рішення симплекс-методом задачі оптимізації виробничого плану підприємства1

1Червоним кольором виділено розраховані параметри

Попередній візуальний аналіз даних табл. 6 показує, що підприємство отримає максимальну виручку від реалізації продукції А – F у плановому періоді в розмірі 754787226,6 грн. (комірка G2), якщо буде випускати продукцію А у

розмірі 5700,53 од., В – 130000 од., С – 2500 од., D – 45000 од., Е – 0 од., F –

100000 од. (комірки А2-F2).

26

4. Післяоптимізаційний економічний аналіз отриманих результатів

програми «Поиск решения» редактора Excel

Редактор Excel дозволяє подати результати пошуку рішення задачі лінійного програмування у формі звітів. Існує три типи таких звітів:

Результати (Answer). До звіту включаються вихідні та кінцеві значення цільової функції та впливових комірок, додаткові відомості про обмеження.

Стійкість (Sensitivity). Звіт містить відомості про чутливість рішення до малих змін у комірках, що змінюються, або у формулах обмежень.

Межі (Limits). Крім вихідних і кінцевих значень змінюваних і цільових комірок у звіт включаються верхні і нижні межі значень, які можуть приймати комірки, що впливають на дотримання обмежень.

Для виведення вказаних звітів необхідно виділити їх назви у діалоговому вікні «Результати пошуку рішення» (рис. 4). Розглянемо докладніше кожен із них на прикладі обговорюваної задачі визначення оптимального виробничого плану підприємства.

Звіт за результатами містить три таблиці:

у першій наведено відомості про цільову функцію;

у другій – значення шуканих змінних, отримані в результаті розв'язання задачі;

у третій – результати оптимального рішення для обмежень (рис. 5).

Цей звіт також містить інформацію про такі параметри кожного обмеження,

як «Состояние» і «Допуск». «Состояние» може приймати три стани: 1) зв'язаний

(«Привязка»); 2) незв'язаний («Без привязки»); 3) невиконаний.

«Допуск» – це різниця між значенням, що виводиться в комірці обмеження при отриманні рішення, і числом, заданим у правій частині формули обмеження

(нерівності).

27

Ячейка целевой функции (Максимум)

Рис. 5 – Звіт за результатами вирішеної оптимізаційної задачі

Зв'язане обмеження – це обмеження, за яким значення допуску дорівнює нулю. Воно зазвичай вказує на дефіцитні ресурси та види продукції. Незв'язане обмеження – це обмеження, що спостерігається з ненульовим значенням допуску і визначає недефіцитні ресурси та види продукції.

Перші дві таблиці даного звіту вже були показані вище в результатах рішення симплекс-методом задачі оптимізації виробничої програми підприємства

(див. табл. 6). Тому найбільший практичний інтерес з економічної точки зору представляє собою третя таблиця, яка містить інформацію про стан та допуск кожного обмеження.

Зв'язані обмеження з нульовим допуском сигналізують про максимально можливий за попитом і потужністю запланований випуск продукції певного виду.

28

В даній задачі це вироби В, С, D, F з нулями у відповідних рядках стовпця

«Допуск» табл. «Ограничения», а також повне використання конкретних виробничих ресурсів підприємства (в даній задачі – матеріальних ресурсів) в

другому рядку цієї ж табл. на рис. 5.

Незв'язані обмеження з ненульовим допуском, навпаки, вказують на певні види продукції (вироби А, Е в п’ятому й дев’ятому рядках табл. «Ограничения»),

які повинні випускатися не в повному обсязі за попитом і потужністю внаслідок обмеженості виробничих ресурсів – у даному випадку – матеріальних ресурсів. А

також на певний надлишок деяких виробничих ресурсів, що не будуть використані на 100 відсотків (витрати на оплату праці й електроенергії в рядках 3,

4 цієї ж таблиці).

Проведемо аналіз недефіцитних позицій табл. «Ограничения» на рис. 5

шляхом ділення допуску на величину обмеження за попитом (потужністю устаткування) або запасу виробничого ресурсу. Це надасть можливість визначити відсоток резерву задоволення реального попиту на певний виріб (ступінь використання конкретного устаткування), або ступінь застосування конкретного виробничого ресурсу. Наприклад, резерв використання потужності устаткування підприємства за виробом А складе:

4299,468631×100 : 10000 = 42,99 (%).

Це означає, що потужності устаткування з виробництва продукції А будуть завантажені в плановому періоді на 57,01 % (100 - 42,99).

Резерв задоволення підприємством реального попиту за виробом Е дорівнює:

15000×100 : 15000 = 100,00 (%),

оскільки даний вид продукції взагалі не передбачається випускати в плановому періоді.

Резерв використання коштів на оплату праці складе:

29

5141900,986×100 : 31983840 = 16,08 (%),

тобто вказаний виробничий ресурс підприємства в плановому періоді буде задіяний на 83,92 % (100 - 16,08).

Резерв використання коштів на оплату електроенергії дорівнюватиме:

268842877,2×100 : 276312200 = 97,30 (%).

Отже, кошти на енерговитрати підприємства в плановому періоді будуть застосовані лише на 2,7 % (100 - 97,3).

Проведений післяоптимізаційний аналіз Звіту за результатами отриманої виробничої програми дозволяє топ-менеджменту підприємства розробити конкретні техніко-економічні заходи щодо використання виявлених резервів недефіцитних видів продукції за потужністю устаткування (виріб А майже на 43 %), за задоволенням реального попиту (виріб Е на 100 %), та коштів на оплату праці (на 16,1 %) і електроенергії (на 97,3 %).

Звіт зі стійкості містить інформацію про те, наскільки цільова функція

(комірка G2 табл. 6) чутлива до змін у оптимальному рішенні (комірки А2-F2

табл. 6) та в обмеженнях (комірки І5-І13 табл. 6). Тому цей звіт має два відповідних розділи: один для змінних комірок цільової функції, а другий – для обмежень. Два останніх стовпця в кожному розділі містить інформацію про чутливість – припустиме збільшення і припустиме зменшення відповідних параметрів за умови незмінності оптимального значення цільової функції.

Розглянемо детальніше перший розділ, представлений у вигляді таблиці

«Ячейки переменных». Кожна змінна комірка та обмеження наводяться в окремому рядку. Стовпець «Окончательное значение» містить оптимальне рішення задачі лінійної оптимізації, яке співпадає з даними Звіту за результатами

(див. рис. 5). Стовпець «Приведенная стоимость» містить значення нормованого градієнта (множника Лагранжа), що показує, наскільки зміниться величина

30