Як компанії використовувати надбудову "Пошук розв'язання", щоб визначити, за які проекти їй слід братися?
Щороку такій компанії, як Eli Lilly, потрібно визначити, які препарати розробити; така компанія, як Microsoft, які програми для розробки програмного забезпечення; таку компанію, як Proctor & Gamble, які нові споживчі продукти розробляти. Функція "Пошук розв'язання" в Excel може допомогти компанії приймати такі рішення.
Як компанії використовувати надбудову "Пошук розв'язання", щоб визначити, за які проекти їй слід братися?
Більшість корпорацій хочуть реалізовувати проекти, які приносять найбільшу чисту поточну вартість (NPV) за умови обмеженості ресурсів (як правило, капіталу і робочої сили). Припустимо, що компанія-розробник програмного забезпечення намагається визначити, яким з 20 програмних проектів їй слід займатися. NPV (у мільйонах доларів), внесених кожним проектом, а також капітал (у мільйонах доларів) і кількість програмістів, необхідних протягом кожного з наступних трьох років, наведені на аркуші Basic Model у файлі Capbudget.xlsx, який показаний на малюнку 30-1 на наступній сторінці. Наприклад, «Проєкт 2» приніс 908 млн грн. Для цього потрібно 151 мільйон доларів протягом 1-го року, 269 мільйонів доларів протягом 2-го року та 248 мільйонів доларів протягом 3-го року. Проект 2 потребує 139 програмістів протягом 1-го року, 86 програмістів протягом 2-го року та 83 програмістів протягом 3-го року. Клітинки E4:G4 відображають капітал (у мільйонах доларів), доступний протягом кожного з трьох років, а клітинки H4:J4 відображають кількість доступних програмістів. Наприклад, протягом 1-го року доступно до $2,5 млрд капіталу та 900 програмістів.
Компанія повинна вирішити, чи варто їй братися за кожен проект. Припустимо, що ми не можемо взяти на себе частину програмного проекту; Наприклад, якщо ми виділимо 0,5 необхідних ресурсів, то отримаємо неробочу програму, яка принесе нам $0 доходу!
Хитрість моделювання ситуацій, коли ви щось робите або не робите, полягає у використанні двійкових змінюваних клітинок. Двійкова змінювана клітинка завжди дорівнює 0 або 1. Якщо двійкова змінювана клітинка, яка відповідає проекту, дорівнює 1, ми виконуємо проект. Якщо двійкова змінювана клітинка, що відповідає проекту, дорівнює 0, проект не виконуватиметься. Ви налаштували надбудову "Пошук розв'язання" на використання діапазону двійкових змінюваних клітинок, додавши обмеження: виберіть потрібні змінювані клітинки, а потім у діалоговому вікні "Додавання обмеження" у списку виберіть бін.
З таким бекграундом ми готові вирішити проблему вибору програмного проекту. Як завжди в моделі "Пошук розв'язання", ми починаємо з визначення цільової клітинки, змінюваних клітинок і обмежень.
- Цільова клітинка. Ми максимізуємо NPV, згенерований обраними проектами.
- Змінювані клітинки. Ми шукаємо двійкову змінну клітинку 0 або 1 для кожного проекту. Я знайшов ці клітинки в діапазоні A6:A25 (і назвав діапазон doit). Наприклад, цифра 1 у клітинці A6 означає, що ми здійснюємо проект 1; 0 у клітинці C6 означає, що ми не беремося за Проект 1.
- Обмеження. Ми повинні забезпечити, щоб для кожного року t (t = 1, 2, 3) Рік t використаного капіталу був меншим або рівним Року t капіталу, а Рік t використаної праці був меншим або рівним Року t доступної праці.
Як бачите, наш робочий аркуш повинен обчислювати для будь-якого вибору проектів NPV, капітал, який використовується щорічно, і програмісти, які використовуються щороку. У клітинці B2 я використовую формулу SUMPRODUCT(doit;NPV) для обчислення загального значення NPV, створеного вибраними проектами. (Ім'я діапазону NPV відноситься до діапазону C6:C25.) Для кожного проекту з 1 у стовпці A ця формула отримує NPV проекту, а для кожного проекту з числом 0 у стовпці A ця формула не отримує NPV проекту. Таким чином, можна обчислити NPV усіх проектів, а цільова клітинка є лінійною, оскільки обчислюється шляхом підсумовування доданків, які мають форму (змінювана клітинка)*(константа). Подібним чином я обчислюю капітал, який витрачається щороку, і працю, яку витрачають щороку, копіюючи з клітинки E2 до F2:J2 формулу SUMPRODUCT(doit;E6:E25).
Тепер я заповнюю діалогове вікно «Параметри розв'язувача», як показано на рисунку 30-2.
Наша мета – максимізувати NPV вибраних проектів (клітинка B2). Змінювані клітинки (діапазон doit) – це двійкові змінювані клітинки для кожного проекту. Обмеження E2:J2<=E4:J4 гарантує, що протягом кожного року використаний капітал і праця будуть меншими або дорівнюватимуть наявному капіталу та робочій силі. Щоб додати обмеження, яке робить змінювані клітинки бінарними, я натискаю кнопку "Додати" в діалоговому вікні "Параметри розв'язувача", а потім у центрі діалогового вікна зі списку виберіть "Бін". Діалогове вікно "Додати обмеження" має з'явитися, як показано на рисунку 30-3.
Наша модель є лінійною, оскільки цільова клітинка обчислюється як сума доданків, які мають форму змінюваної клітинки)*(константа), а обмеження використання ресурсів обчислюються шляхом порівняння суми (змінюваних клітинок)*(констант) з константою.
Після заповнення діалогового вікна «Параметри розв'язувача» натисніть кнопку «Розв'язати», і ми отримаємо результати, показані вище на рисунку 30-1. Компанія може отримати максимальний NPV у розмірі 9 293 мільйонів доларів США (9,293 мільярда доларів США), вибравши проекти 2, 3, 6-10, 14-16, 19 і 20.
Обробка інших обмежень
Іноді моделі вибору проекту мають інші обмеження. Припустімо, що якщо ми вибираємо Проект 3, ми повинні вибрати також Проект 4. Оскільки наше поточне оптимальне рішення передбачає вибір Проекту 3, але не Проекту 4, ми знаємо, що наше поточне рішення не може залишатися оптимальним. Щоб вирішити цю проблему, просто додайте обмеження, згідно з яким двійкова змінювана клітинка проекту 3 менша за двійкову змінювану клітинку проекту 4 або дорівнює їй.
Ви можете знайти цей приклад на аркуші якщо 3, то 4 у файлі Capbudget.xlsx, який наведено на рисунку 30-4. Клітинка L9 посилається на двійкове значення, пов'язане з проектом 3, а клітинка L12 – на двійкове значення, пов'язане з проектом 4. Якщо додати обмеження L9<=L12, якщо ми виберемо Проект 3, L9 дорівнює 1, а наше обмеження змушує L12 (двійкове подання Проекту 4) дорівнювати 1. Якщо не вибрати Проект 3, наше обмеження також має залишити двійкове значення в змінюваній клітинці Проекту 4 необмеженим. Якщо ми не вибираємо Проект 3, L9 дорівнює 0, і наше обмеження дозволяє двійковій системі Проекту 4 дорівнювати 0 або 1, що ми і хочемо. Нове оптимальне рішення показано на малюнку 30-4.
Нове оптимальне рішення буде обчислене, якщо вибір Проекту 3 означає, що ми також повинні вибрати Проект 4. Тепер припустимо, що ми можемо виконати лише чотири проекти з числа проектів 1–10. (Див. аркуш «Не більше 4» з P1–P10, показаний на малюнку 30-5.) У клітинці L8 ми обчислюємо суму двійкових значень, пов'язаних із проектами 1–10, за допомогою формули SUM(A6:A15). Потім ми додаємо обмеження L8<=L10, яке гарантує, що буде вибрано не більше 4 з перших 10 проектів. Нове оптимальне рішення показано на малюнку 30-5. NPV впав до $9,014 млрд.
Розв'язування задач бінарного та цілочисельного програмування
Моделі лінійного розв'язувача, у яких деякі або всі змінювані клітинки мають бути двійковими чи цілими числами, зазвичай важче розв'язати, ніж у лінійних моделях, де всі змінювані клітинки можуть бути дробами. З цієї причини ми часто задоволені майже оптимальним розв'язком задачі бінарного або цілочисельного програмування. Якщо модель «Пошук розв'язання» запускається довго, можна змінити параметр «Допуск» у діалоговому вікні «Параметри розв'язувача». (Див. Малюнок 30-6.) Наприклад, значення допуску 0,5 % означає, що надбудова "Пошук розв'язання" припинить роботу, коли знайде можливе розв'язання в межах 0,5 відсотка від теоретично оптимального цільового значення клітинки (теоретично оптимальне цільове значення клітинки – це оптимальне цільове значення, яке можна знайти, якщо двійкове та ціле обмеження не вказано). Часто ми стоїмо перед вибором між пошуком відповіді в межах 10 відсотків від оптимального за 10 хвилин або пошуком оптимального рішення за два тижні комп'ютерного часу! Стандартне значення толерантності становить 0,05 %. Це означає, що надбудова «Пошук розв'язання» припиняє роботу, коли знаходить значення цільової клітинки в межах 0,05 відсотка від теоретично оптимального цільового значення.
Проблеми
- На розгляді компанії дев'ять проектів. NPV, доданий кожним проектом, і капітал, необхідний кожному проекту протягом наступних двох років, показані в наступній таблиці. (Усі цифри обчислені мільйонами.) Наприклад, Проект 1 додасть $14 млн NPV і вимагатиме витрат у розмірі $12 млн протягом 1-го року та $3 млн протягом 2-го року. Протягом 1-го року 50 мільйонів доларів капіталу доступні для проектів, а 20 мільйонів доларів – протягом 2-го року.
| NPV | Витрати першого року | Видатки 2-го року | |
|---|---|---|---|
| Проєкт 1 | 14 | 12 | 3 |
| Проєкт 2 | 17 | 54 | 7 |
| Проєкт 3 | 17 | 6 | 6 |
| Проєкт 4 | 15 | 6 | 2 |
| Проєкт 5 | 40 | 30 | 35 |
| Проєкт 6 | 12 | 6 | 6 |
| Проєкт 7 | 14 | 48 | 4 |
| Проєкт 8 | 10 | 36 | 3 |
| Проєкт 9 | 12 | 18 | 3 |
- Якщо ми не можемо виконати частину проекту, але повинні виконати або весь проект, або жодну його частину, як ми можемо максимізувати NPV?
- Припустимо, що якщо здійснюється Проект 4, то і Проект 5 повинен бути реалізований. Як ми можемо максимізувати NPV?
Видавництво намагається визначити, яку з 36 книг вона має видати цього року. Файл Pressdata.xlsx надає такі відомості про кожну книгу:
- Прогнозований дохід і витрати на розробку (у тисячах доларів)
- Сторінок у кожній книзі
- Чи орієнтована книга на аудиторію розробників програмного забезпечення (позначено цифрою 1 у стовпці E).
Видавництво може видати книги загальним обсягом до 8500 сторінок цього року і має видати щонайменше чотири книги, орієнтовані на розробників програмного забезпечення. Як компанії максимізувати прибуток?
Про статтю
Цю статтю адаптовано з книги Уейна Л. Вінстона " Аналіз даних і бізнес-моделювання Microsoft Office Excel 2007 ".
Ця книга в класному стилі була розроблена на основі серії презентацій Уейна Вінстона, відомого статистика та професора бізнесу, який спеціалізується на творчому, практичному застосуванні Excel.