Перейти до основного

Використання розв'язувача для капітальних бюджетів

Використання розв'язувача компанії для визначення проектів, які він має проводити?

Щороку компанія Eli Lilly має визначити, які препарати слід розробляти; компанії, як-от Microsoft, які програмні програми для розробки; Компанія, як-от Проктор & Gamble, яка нових споживчих продуктів для розробки. Функція "розв'язувач" у програмі Excel може допомогти компанії приймати ці рішення.

Більшість корпорацій прагнуть проводити проекти, які вносять найбільшу чисту зведену вартість (NPV) за умови обмеженого ресурсу (зазвичай це капітал і праця). Припустімо, що компанія з розробки програмного забезпечення намагається визначити, які з 20 програмних проектів слід проводити. Значення NPV (у мільйонах доларів), які сприяли кожен проект, а також Капітал (у мільйонах доларів) і кількість програмістів, необхідних протягом наступних трьох років, на аркуші основної моделі у файлі capbudget. XLSX, що зображено на рисунку 30-1 на наступній сторінці. Наприклад, програма Project 2 дає $908 000 000. Вона вимагає $151 000 000 протягом року 1, $269 000 000 протягом року 2 та $248 000 000 протягом року 3. Project 2 потребує 139 програмістів протягом року 1, 86 програмістів протягом року 2 та 83 програмістів протягом року 3. Клітинки E4: G4 відображає Капітал (у мільйонах доларів), доступних протягом кожних трьох років, а клітинки H4: J4 вказують на кількість доступних програмістів. Наприклад, протягом року з 1 до $2 500 000 000 у капіталі і 900 програмісти доступні.

Компанія має вирішити, чи має вона виконувати кожен проект. Припустимо, що ми не можемо взяти на себе частину програмного проекту; Якщо ми виділяємо 0,5 необхідних ресурсів, наприклад, у нас буде програма неробочий час, який принесе нам дохід $0!

Хитрість в моделюванні ситуаціях, у яких ви робите або не виконуєте щось, – це використання бінарних змінних клітинок. Двійковий мінливий вміст завжди дорівнює 0 або 1. Якщо двійковий змінне поле, що відповідає проекту, дорівнює 1, ми робимо проект. Якщо двійковий мінливий вміст, який відповідає проекту, дорівнює 0, ми не робимо проект. Ви настроїли розв'язувач, щоб використовувати діапазон бінарних змінних клітинок, додавши обмеження, виділіть потрібні клітинки, а потім виберіть пункт bin зі списку в діалоговому вікні Додавання обмеження.

Book image

На цьому фоні ми готові вирішити проблему з виділенням програмного забезпечення проекту. Як завжди за допомогою моделі розв'язувача, ми спробуємо визначити нашу цільову клітинку, змінювані клітинки та обмеження.

  • Цільова – елемент. Максимізація функції NPV, створеної за допомогою вибраних проектів.

  • Змінення клітинок.Ми розглянемо для кожного проекту значення 0 або 1 бінарну змінну. Я розміщував ці клітинки в діапазоні A6: A25 (і називатиметьсяв діапазоні "doit"). Наприклад, 1 у клітинках A6 вказує на те, що ми беремо проект 1; 0 у клітинках C6 вказує на те, що ми не беремо проект 1.

  • Обмеження.Ми повинні гарантувати, що для кожного року t (t = 1; 2; 3), що використовується за рік, у разі використання капіталу менше або дорівнює річній робочій столиці, а також за рік, що використовується, менше або дорівнює за рік.

Як бачите, наш аркуш має обчислити для будь-якого набору проектів, що використовуються в 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.

Book image

Наша мета – максимізувати NPV вибраних проектів (клітинки B2). Ми змінюємо клітинки (діапазон « doit») – це двійкові змінювані клітинки для кожного проекту. Обмеження E2: J2<= E4: J4 гарантує, що протягом кожного року капітал і праця, що використовуються, менше або дорівнює капіталу та трудові. Щоб додати обмеження, що робить двійковий формат клітинок, натисніть кнопку Додати в діалоговому вікні параметри розв'язувача, а потім виберіть пункт bin зі списку посередині діалогового вікна. Діалогове вікно Додавання обмеження має відображатися, як показано на рисунку 30-3.

Book image

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

Щоб заповнити діалогове вікно параметри розв'язувача, натисніть кнопку вирішити, і ми маємо результати, наведені вище на рисунку 30-1. Компанія може отримати максимальну кількість NPV $9 293 000 000 ($9 293 000 000), вибравши проекти 2, 3, 6 – 10, 14 – 16, 19 та 20.

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

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

Book image

Нове оптимальне рішення обчислюється, якщо вибрати проект 3 означає, що ми також повинні вибрати Project 4. Тепер припустимо, що для роботи з проектами 1 – 10 можна виконувати лише чотири проекти. (Див. у більшості 4 аркушів P1 – P10 , показана на рисунку 30-5.) У розділі "стільниковий L8" ми обчислюємо суму бінарних значень, зв'язаних із проектами від 1 до 10 із сумою формули (A6: A15). Після цього ми додамо обмеження L8<= L10, що гарантує, що в більшості випадків вибрано 4 перших 10 проектів. Нове оптимальне рішення відображається на рисунку 30-5. У службі NPV знизився до $9 014 000 000.

Book image

Моделі лінійного розв'язувача, у яких потрібно використовувати кілька або всі змінювані клітинки, зазвичай важче вирішити, ніж лінійні моделі, у яких всі змінювані клітинки можуть бути дробами. З цієї причини ми часто задовольняємо майже оптимальне рішення для бінарної або цілочислових проблем із програмуванням. Якщо модель Solver працює протягом довгого часу, можливо, потрібно настроїти параметри допуску в діалоговому вікні параметри розв'язувача. (Див. фігуру 30-6.) Наприклад, значення допуску 0,5% означає, що розв'язувач припинить роботу під час першого виявлення в межах 0,5 відсотків теоретичного оптимального значення цільової функції (теоретичне значення оптимальної цільової мережі – це оптимальне цільове значення, яке буде знайдено в разі Двійкові та цілочислові обмеження пропущено. Часто ми стикаємося з вибором, щоб знайти відповідь у межах 10 відсотків оптимальності через 10 хвилин або знайти оптимальне рішення за два тижні часу роботи комп'ютера! Значення допуску за замовчуванням – 0,05%, а це означає, що розв'язувач припиняється, коли вона знаходить значення цільової мережі в межах 0,05 відсотка від теоретичної оптимальності значення цільової стільникової.

Book image

  1. 1. компанія має дев'ять проектів, які відповідно до уваги. У таблиці нижче наведено список, доданий кожним проектом, і капітал, який потрібно виконати кожного проекту протягом наступних двох років. (Усі числа у мільйонах.) Наприклад, у програмі Project 1 буде додано $14 000 000 в NPV і потрібно мати витрати $12 000 000 протягом року 1 і $3 000 000 протягом року 2. Протягом 1 року $50 000 000 у столиці доступний для проектів, і $20 000 000 доступний протягом року 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

Проект 8

10

36

3

Проект 9

12

18

3

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

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

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

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

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

    • Незалежно від того, чи книгу орієнтуються на аудиторію розробників програмного забезпечення (позначена 1 у стовпці E)

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

Цю статтю було пристосовано з Microsoft Office Excel 2007 для аналізу даних і бізнес-моделювання Уейн л. Уїнстона.

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

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×