Використання надбудови "Пошук розв'язання" для бюджетування капіталу

Більшість корпорацій хочуть здійснювати проекти, які вносять найбільшу чисту поточну вартість (NPV), що підлягає обмеженню ресурсів (зазвичай капіталу і праці). Припустімо, що компанія-розробник програмного забезпечення намагається визначити, які з 20 програмних проектів вона має взяти на себе. NPV (у мільйонах доларів), внесений кожним проектом, а також столицею (у мільйонах доларів), а кількість програмістів, необхідних протягом кожного з наступних трьох років, надається на аркуші Basic Model у файлі Capbudget.xlsx, що показано на рисунку 30-1 на наступній сторінці. Наприклад, project 2 дає 908 мільйонів доларів США. Він вимагає $ 151 млн протягом року 1, $ 269 млн протягом 2 року, і $ 248 млн протягом року 3. Project 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; a 0 у клітинці C6 вказує на те, що ми не беремося за Project 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.

Іноді моделі вибору проекту мають інші обмеження. Припустімо, наприклад, що якщо вибрати Project 3, потрібно також вибрати Project 4. Оскільки наше поточне оптимальне рішення вибирає project 3, але не Project 4, ми знаємо, що наше поточне рішення не може залишатися оптимальним. Щоб вирішити цю проблему, просто додайте обмеження, що двійкова змінювана клітинка для Project 3 менша або дорівнює двійковій змінній клітинці для Project 4.

Цей приклад можна знайти на аркуші Якщо 3, а потім 4 у файлі Capbudget.xlsx, який відображається на рисунку 30-4. Клітинка L9 – це двійкове значення, пов'язане з Project 3, а клітинка L12 – на двійкове значення, пов'язане з Project 4. Додавши обмеження L9<=L12, якщо вибрати Project 3, L9 дорівнює 1, а обмеження змушує L12 (двійковий файл Project 4) дорівнювати 1. Наше обмеження також має залишити двійкове значення в змінюваній клітинці Project 4 необмеженим, якщо не вибрати Project 3. Якщо не вибрати project 3, L9 дорівнює 0, і наше обмеження дає змогу двійковому проекту 4 дорівнювати 0 або 1, що потрібно. Нове оптимальне рішення показано на рисунку 30-4.

Нове оптимальне рішення обчислюється, якщо вибрати Project 3, це означає, що також потрібно вибрати Project 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 відсотка від теоретичного оптимального значення цільової клітинки.

1. Компанія має дев'ять проектів, що розглядаються. NPV, доданий кожним проектом і капіталом, потрібним для кожного проекту протягом наступних двох років, відображається в наведеній нижче таблиці. (Усі числа в мільйонах.) Наприклад, Project 1 додасть 14 мільйонів доларів США в NPV і потребуватиме витрат у розмірі 12 мільйонів доларів протягом 1 року та 3 мільйони доларів протягом 2 року. Протягом 1 року$ 50 млн капіталу доступно для проектів, а $ 20 млн доступно протягом 2 року.

	NPV	Витрати за 1 рік	Витрати 2 року
Project 1	14	12	3
Project 2	17	54	7
Project 3	17	6	6
Project 4	15	6	2
Project 5	40	30	35
Project 6	12	6	6
Project 7	14	48	4
Project 8	10	36	3
Project 9	12	18	3

• Якщо ми не можемо взяти на себе частину проекту, але повинні взяти на себе або всі, або жоден проект, як ми можемо максимізувати NPV?

• Припустімо, що в разі здійснення Проекту 4 необхідно виконати Проект 5. Як ми можемо розгорнути протокол NPV?

• Видавнича компанія намагається визначити, які з 36 книг вона повинна опублікувати в цьому році. Файл Pressdata.xlsx надає такі відомості про кожну книгу:

  • Прогнозовані витрати на прибуток і розвиток (у тисячах доларів)

  • Сторінки в кожній книзі

  • Чи спрямована книга на аудиторію розробників програмного забезпечення (позначена 1 у стовпці E)

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

Цю статтю адаптовано з аналізу даних і моделювання даних Microsoft Office Excel 2007 вейном Л. Вінстоном.

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