Загальні положення

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

У MS Excel спосіб вирішення подібних задач реалізований як пошук значення параметра формули, що задовольняє її конкретному значенню. Цю процедуру використовують для пошуку такого значення ячейки, при якому значення іншої ячейки, що обчислюється за формулою, заздалегідь задано. У формулі повинне бути посилання на цільову ячейку.

Пошук значення параметра формули, що задовольняє її конкретному значенню, - це чисельне вирішення рівняння. Іншими словами, використовуючи Excel, можна вирішувати будь-які рівняння з однією змінною.

Вирішення задач оптимізації у ms Excel

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

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

Складання штатного розкладу. Вирішення подібних задач розглянемо на прикладі розробки штатного розкладу. Нехай кількість співробітників, необхідна для нормальної роботи установи, знаходиться в деякому діапазоні значень. Загальний місячний фонд зарплати повинний бути мінімальний. Необхідно визначити, якими повинні бути оклади співробітників установи, за умови, що оклад співробітника з мінімальною зарплатою не повинний бути менше 180 грн.

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

Рис. 1.

Вирішення задачі виконується з використанням команди Поиск решения з меню Сервис. Вікно Поиск решения показане на рис. 2. Обмеження описують діапазон припустимої кількості співробітників установи і значення найменшого окладу.

Рис. 2.

Результати вирішення задачі наведені на рис. 3.

Рис. 3.

Складання плану вигідного виробництва. Випускається продукція типів "A", "B" і "C". Відомий прибуток від реалізації одиниці продукції. Продукцію можна робити в будь-яких кількостях (збут забезпечений), але запаси сировини обмежені. Необхідно визначити, яку продукцію і якої кількості потрібно випустити, щоб загальний прибуток від реалізації був максимальним. Норми витрат сировини на виробництво одиниці продукції кожного виду задані.

Вихідні дані наведені на рис. 4.

Рис. 4.

Задача вирішується за допомогою Поиск решения (рис. 5). Обмеження накладаються на значення ячейок, що містять дані про витрати сировини; крім того, кількість виробів не повинна бути від’ємною.

Рис. 5.

Розв’язок показує, що оптимальний план випуску передбачає виготовлення 6 одиниць продукції "B" і 4 одиниці продукції "C". Продукцію "А" виробляти не варто. Отриманий прибуток становитиме 339грн.

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

Вихідні дані задачі складаються з 2-х блоків: "Плановані показники" і "Дані про продукцію". Так, у 1-му кварталі заплановані такі показники реалізації: сезонний фактор (СФ) - 0,9; витрати на заробітну плату персоналу -5 тис. грн.; витрати на рекламу (Р) - 15 тис. грн. Дані про продукцію: ціна реалізації - 50 грн.; собівартість продукції - 33 грн. Розрахунок планованих показників роблять з урахуванням того, що: обсяг збуту продукції (З) нелінійно залежить від сезонного фактора і витрат на рекламу З = 35 ^. СФ ; доход з обороту визначається як добуток очікуваної кількості проданих одиниць продукції і собівартості продукції; собівартість реалізованої продукції знаходиться, як добуток обсягу збуту продукції на собівартість продукції; валовий прибуток – це різниця доходу з обороту та собівартості реалізованої продукції; накладні витрати фірми обчислюються в обсязі 15% доходу з обороту; валові витрати розраховуються як сума витрат на заробітну плату персоналу, рекламу і накладні витрати; прибуток від продукції визначається як валовий прибуток мінус валові витрати; рентабельність знаходиться, як відношення прибутку від реалізації продукції до доходу з обороту. Дані для пошуку максимуму в задачі маркетингу показані на рис. 6.

Рис. 6.

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

Результат оптимізації прибутку від реалізації товару в задачі маркетингу показаний на рис. 7. Як видно з отриманого рішення, при витратах на рекламу 19 388 грн. прибуток від реалізації товару буде максимальний, видно, що витрати на рекламу більше запланованих. Однак слід зазначити, що максимізація прибутку не обов'язково відповідає найвищому значенню рентабельності: її значення зменшилося до 9 %.

Рис. 7.

Вирішимо попередню задачу за умови, що витрати на рекламу не можуть перевищувати 15 000 грн. Розв’язок задачі - 20 149 грн. При цьому зростає рентабельність до 10%.

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

Визначимо, скільки можна заробити, придбавши 10 акцій підприємства, якщо задані коливання курсу акцій. Підраховуємо вартість 10 акцій на підставі їхнього відомого курсу (рис. 8), далі будуємо діаграму залежності вартості акцій від декади, апроксимуємо отриману криву за допомогою поліноміальної ступінної залежності. Для цього з контекстного меню діаграми обирається команда Добавить линию тренда. Виберемо як тип лінії тренда поліноміальну апроксимацію 6-го ступеня.

Рис. 8.

Результат апроксимації та апроксимуюча функція показані на рис. 9. На вкладці Параметри задаємо Прогноз вперед на 6 периодов.

Рис. 9.

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

Задача нелінійного програмування – планування структури виробництва з врахуванням зменшення норми прибутку при збільшенні обсягів виробництва

Підприємство випускає телевізори, стерео- і акустичні системи, використовуючи загальний склад комплектуючих. У зв'язку з обмеженістю запасу необхідно знайти оптимальне співвідношення обсягів випуску виробів. Варто враховувати зменшення питомого прибутку при збільшенні обсягів виробництва у зв'язку з додатковими витратами на збут (зменшення коефіцієнта віддачі).

Задача включає дані по кількох виробах (рис. 10), у яких використані загальні комплектуючі, кожному з виробів відповідає своя норма прибутку (НП). Запас комплектуючих обмежений і задача зводиться до визначення кількості кожного виду виробів (КВ) для одержання найбільшого прибутку, що обчислюється, як сума прибутків по виробам (П).

Рис. 10.

У формулу прибутку по виробах П = НП ^. КВ^0,9 входить зведення у ступінь з показником 0,9, що враховує зменшення прибутку з ростом обсягу(зменшення коефіцієнта віддачі). Це робить задачу нелінійною. Зміна показника на 1,0 означає, що прибуток не залежить від обсягу виробництва. Подібна задача є лінійною.

Для вирішення задачі використовується Поиск решения (рис. 11). Мета - одержання найбільшого прибутку. Змінювані дані - кількість виробів кожного виду, що випускаються. Обмеження показують, що кількість використаних комплектуючих не повинна перевищувати їхнього запасу на складі, і кількість виробів, що випускаються, повинна бути додатньою.

Рис. 11.

Розв’язок показує, що оптимальний план випуску з отриманням максимального прибутку передбачає виготовлення 200 телевізорів, 4 стерео- та 229 акустичних систем. Прибуток по виробах становитиме відповідно 8 830,56 у. о., 172,67 у. о. і 9 956,85 у. о., загальний прибуток: 18 960,07 у. о.

Задача нелінійного програмування – задача перевезення вантажів (транспортна задача).

Розглянемо задачу доставки товарів із трьох заводів на п'ять регіональних складів (рис. 12). Товари можуть доставлятися з будь-якого заводу на будь-який склад, вартість доставки на більшу відстань буде більшою. Потрібно визначити обсяги перевезень між кожним заводом і складом відповідно до можливостей складів і заводів, при яких транспортні витрати мінімальні. При вирішенні задачі потрібно мінімізувати витрати на перевезення товарів від підприємств-виробників на торгові склади. При цьому необхідно врахувати можливості постачання кожного з виробників при максимальному задоволенні запитів споживачів.

Вартість перевезення у кожне місто визначається, як сума добутків витрат на перевезення на кількість перевезень.

Використовується Поиск решения. Мета - мінімізація всіх транспортних витрат (C19), змінювані дані - обсяги перевезень від кожного з заводів до кожного складу (C4:G6). Обмеження показують, що кількість перевезених вантажів не може перевищувати виробничих можливостей заводів (I4:I6<=I13:I15), кількість доставлених вантажів не повинна бути менше потреб складів (C8:G8>=C10:G10), і також число перевезень не може бути від’ємним (C4:G6>=0). Найшвидше одержати розв’язок даної задачі можна, якщо вибрати використання лінійної моделі перед початком пошуку рішення. Для задачі такого виду оптимальне ціле рішення для цілих значень обсягів перевезень виходить, якщо задані обмеження - також цілі числа.

Рис. 12.

Результати оптимізації при вирішення транспортної задачі представлені на рис. 13.

Рис. 13.

Задача нелінійного програмування – задача керування оборотним капіталом. Потрібно з найбільшою прибутковістю розмістити додатковий капітал в 1-, 3- і 6-місячних депозитах, з огляду на власні потреби в капіталі і гарантійному резерві. Однією з задач співробітника фінансового відділу є короткострокові вкладення з максимальним прибутком при збереженні достатнього резерву для покриття витрат. Більш прибутковими є довгострокові депозити, однак короткострокові депозити надають більш гнучкі можливості керування фінансовими засобами. У даній задачі кінцева сума розраховується з використанням початкової суми (з минулого місяця), плюс депозити, що погашаються, мінус нові депозити і з урахуванням щомісячних потреб самого підприємства (рис. 14).

Рис. 14.

Необхідно визначити дев'ять сум: щомісячні суми для 1-місячних депозитів; суми депозитів 1-го і 4-го місяця для квартальних депозитів; суму шестимісячного депозиту в 1-му місяці. Використовується Поиск решения. Мета - одержання найбільшого доходу по відсотках (С22). Змінювані дані - сума по кожному типу депозиту (С13:H13, F14, C14:C15). Обмеження показують, що сума кожного депозиту не може бути менше нуля (С13:H13>=0, C14:C15>=0, F14>=0), і кінцева сума не повинна бути менше $100000 (C18:H18>=100000, J18>=100000). Знайдене оптимальне рішення дає можливість одержання доходу по відсотках у розмірі $21 031,44 при вкладенні максимально можливих сум у шести- і тримісячні депозити з наступним поверненням до одномісячних депозитів. Даний розв’язок задовольняє всім поставленим обмеженням.

Припустимо, що необхідно забезпечити достатній капітал для оплати устаткування в 5 місяці. Це накладає обмеження на середній термін дії депозиту в 1-му місяці, що не повинен перевищувати чотирьох місяців. Формула в ячейці B20 (рис. 14) обчислює загальні суми вкладень у 1 місяці (С13, С14 і С15), помножені на терміни дії (1, 3 і 6 місяців), і віднімає загальну суму депозиту, помножену на 4. Якщо отримане число є від’ємним, середній термін погашення не перевищує 4 місяців. Це додає обмеження В20<=0. Отриманий розв’язок показаний на рис. 15.

Рис. 15.

Відповідно до заданого обмеження капітал вміщується, в основному, на тримісячний депозит. Термін повернення депозиту становить 4 місяці, після чого засоби знову вміщуються на тримісячний депозит. Якщо потрібен вільний капітал, гроші можуть не вміщуватися на депозит. Суми, що повертається у 4 місяці ($91562,72), досить для оплати устаткування в 5 місяці. Така можливість здобувається втратою близько $740 доходу по відсотках.

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

Рис. 16.

Кожний співробітник працює п’ять днів поспіль з двома вихідними днями. Задані щоденна потреба у персоналі (F13:L13)та денна оплата працівника (F15).Кількість зайнятих працівників по дням тижня підраховується як сума добутків кількостей робітників у кожній групі на відповідні позначки у графіку роботи (одиницю чи нуль). Одиниця у графіку роботи означає, що відповідна група у цей день працює, нуль позначає вихідний день. Загальна тижнева зарплата (F16)– це добуток загальної кількості співробітників на денну оплату та на кількість робочих днів у тижні (5).

Використовується Поиск решения. Мета – мінімізація витрат на оплату праці (F16). Змінювані дані – кількість працівників у кожній групі (D3:D9). Обмеження показують, що кількість співробітників у кожній групі повинна бути цілим числом (D3:D9=целое) та не може бути від’ємною (D3:D9>=0), а також кількість зайнятих працівників кожного дня не може бути менше щоденної потреби (F11:L11>=F13:L13). Обрання лінійної моделі у діалоговому вікні параметрів прискорить отримання результату.

Отриманий розв’язок показаний на рис. 17.

Рис. 17.