fbpx

Використання лінійного програмування для оптимального розподілу обмежених ресурсів між конкуруючими напрямами діяльності (розв’язок задачі в MS Excel)

Печать

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

Більшість корпорацій хочуть реалізовувати проекти, які приносять найбільшу чисту приведену вартість при обмежених ресурсах (зазвичай фінансових і трудових). Припустимо, що деяка ІТ компанія  намагається визначити, які з 20 проектів заслуговують на увагу. Чиста приведена вартість (в тис. грн), отримана від кожного проекту, а також кошти (в тис. грн) і кількість спеціалістів (програмістів), які необхідні протягом наступних трьох років показані на рис. 1.

*Джерело: "Старченко Г. В. Управління проектами: теорія та практика : навч. посіб. / Г. В. Старченко. – Чернігів : видавець Брагинець О. В., 2018. – 306 с. " 

Рис.1. Вихідні дані для розрахунку 

Наприклад, проект 2 приносить 908 тис. грн. Для його реалізації необхідно 151 тис. грн у 1-й рік, 269 тис. грн у 2-й рік і 248 тис. грн у 3-й рік. Крім цього буде потрібно 139 програмістів в 1-й рік, 86 – у 2-й і 83 – в 3-й рік. В клітинках E4:G4 вказані кошти (в тис. грн), доступні протягом кожного з трьох років, а в клітинках H4:J4 – доступне число спеціалістів. Наприклад, в 1-й рік компанія має в своєму розпорядженні 2500 тис. грн і 900 програмістів.

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

Особливість моделювання, коли щось виконується або ні, полягає в використанні бінарних змінних. Бінарна змінна може приймати тільки значення 0 або 1. Якщо значення бінарної змінної відповідного проекту дорівнює 1 – проект виконується, якщо 0 – проект не виконується.

Для визначення оптимальних проектів сформулюємо наступну задачу лінійного програмування:

NPV = 928×X1+908×X2 +...+ 655×X20 → max

398×X1+151×X2+...+152×X20 ≤ 2500

180×X1+269×X2+...+211×X20 ≤ 2800

368×X1+248×X2+...+134×X20 ≤ 2900

111×X1+139×X2+...+85×X20 ≤ 900

108×X1+86×X2+...+59×X20 ≤ 900

123×X1+83×X2+...+70×X20 ≤ 900

При побудові моделі пошуку рішення в MS Excel 2016, ми повинні вказати цільову клітинку, змінні клітинки і обмеження. Для цього потрібно в меню Данные обрати Поиск решения (див. рис. 2).

 

 Рис. 2. Меню «Данные» в MS Excel 2016 

В діалоговому вікні  «Параметри пошуку рішення» необхідно задати наступні параметри та обмеження (див. рис. 3).

– цільова клітинка – B2, тут визначається максимальне значення чистої приведеної вартості (ЧПВ) для обраних проектів (формула для цільової клітинки =СУММПРОИЗВ(A6:A25;C6:C25));

– змінювані клітинки – ці клітинки знаходяться в діапазоні А6:А25. Наприклад, 1 в клітинці А7 буде вказувати на те, що проект 2 буде реалізований;

– обмеження – E2:J2 <= E4:J4, ми повинні бути впевнені, що в кожному році використовуються кошти і трудові ресурси, що не перевищують доступні.

 

Рис.3. Діалогове вікно  «Параметры поиска решения» в MS Excel 2016 

Після вводу параметрів необхідно натиснути кнопку «Найти решение», після розрахунків отримуємо значення цільової функції (див. рис. 4).

 

Рис.4. Результати розрахунків в MS Excel 2016

Висновок:

В клітинках E2:J2 розраховані значення використаних ресурсів які не перевищують наявні (формула розрахунку для клітинки Е2 =СУММПРОИЗВ($A6:$A25;E6:E25). Одиниці в стовпці А позначають реалізовані проекти, нулі – нереалізовані проекти. 9269 тис. грн – максимальне значення ЧПВ.

Застосування інших обмежень

Іноді моделі вибору проекту мають інші обмеження. Припустимо, якщо ми виберемо проект 3, то також повинні вибрати і проект 4. Так як отримане раніше рішення включає проект 3, але не враховує проект 4, то воно тепер не може вважатися оптимальним. Для вирішення цієї проблеми, просто додаємо обмеження X3 ≤ X4 (див. рис. 5).

 Рис.5. Діалогове вікно «Добавление ограничения» в MS Excel 2016 

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

Рис.6. Результати розрахунків в MS Excel 2016 

Висновок:

У новому оптимальному рішенні ЧПВ зменшується до 9157 тис. грн.

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

Рис.7. Діалогове вікно  «Параметры поиска решения» в MS Excel 2016 

Для клітинки D26 встановлюємо формулу =СУММ(A6:A15). Після вводу додаткового обмеження необхідно натиснути кнопку «Найти решение», після розрахунків отримуємо значення цільової функції (див. рис. 8).

Рис.8. Результати розрахунків в MS Excel 2016 

Висновок:

 

У новому оптимальному рішенні ЧПВ зменшується до 8975 тис. грн.


При використанні матеріалів сайту обов'язково вказуйте джерело!