Как може една фирма да използва Solver, за да определи кои проекти трябва да предприеме?
Всяка година компания като Eli Lilly трябва да определи кои лекарства да разработи; компания като Microsoft, кои софтуерни програми да се разработват; компания като Proctor & Gamble, които нови потребителски продукти да разработят. Функцията за постъпкови решения в Excel може да помогне на фирмата да вземе тези решения.
Как може една фирма да използва Solver, за да определи кои проекти трябва да предприеме?
Повечето корпорации искат да предприемат проекти, които допринасят за най-голямата нетна настояща стойност (NPV), при ограничени ресурси (обикновено капитал и труд). Да кажем, че една компания за разработка на софтуер се опитва да определи кой от 20-те софтуерни проекта трябва да предприеме. NPV (в милиони долари), внесен от всеки проект, както и капиталът (в милиони долари) и броят на програмистите, необходими през всяка от следващите три години, са дадени в работния лист на основния модел във файловия 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, не вършим проекта. Можете да настроите Solver да използва диапазон от двоични променящи се клетки чрез добавяне на ограничение – изберете променящите се клетки, които искате да използвате, и след това изберете контейнер от списъка в диалоговия прозорец "Добавяне на ограничение".
С този фон сме готови да решим проблема с избора на софтуерен проект. Както винаги при модела на Solver, ние започваме с идентифициране на нашата целева клетка, променящите се клетки и ограниченията.
- Целева клетка. Максимизираме NPV, генериран от избрани проекти.
- Променящи се клетки. Търсим комбинирана променяща се клетка 0 или 1 за всеки проект. Намерих тези клетки в диапазона A6:A25 (и нарекох диапазона doit). Например 1 в клетка A6 показва, че приемаме проект 1; 0 в клетка C6 показва, че не изпълняваме проект 1.
- Ограничения. Трябва да се уверим, че за всяка година t (t=1, 2, 3) използваният капитал е по-малък или равен на наличния капитал в годината, а използваната година t труд е по-малка или равна на наличния труд в годината.
Както можете да видите, нашият работен лист трябва да изчислява за всяка селекция от проекти NPV, капитала, използван годишно, и програмистите, използвани всяка година. В клетка B2 използвам формулата SUMPRODUCT(doit;NPV), за да изчисля общата NPV, генерирана от избрани проекти. (Името на диапазона NPV препраща към диапазона C6:C25.) За всеки проект с 1 в колона A тази формула избира NPV на проекта, а за всеки проект с 0 в колона A тази формула не избира NPV на проекта. Следователно, ние сме в състояние да изчислим NPV на всички проекти, а нашата целева клетка е линейна, защото се изчислява чрез сумиране на членове, които следват формата (променяща се клетка)*(константа). По подобен начин изчислявам капитала, използван всяка година, и труда, използван всяка година, като копирам от E2 в F2:J2 формулата SUMPRODUCT(doit;E6:E25).
Сега попълвам диалоговия прозорец за параметри на Solver, както е показано на фигура 30-2.
Нашата цел е да увеличим максимално NPV на избраните проекти (клетка B2). Нашите променящи се клетки (диапазонът с име doit) са двоичните променящи се клетки за всеки проект. Ограничението E2:J2<=E4:J4 гарантира, че през всяка година използваният капитал и труд са по-малки или равни на наличния капитал и труд. За да добавя ограничението, което прави променящите се клетки двоични, щраквам върху "Добави" в диалоговия прозорец "Параметри на Solver" и след това избирам "Контейнер" от списъка в средата на диалоговия прозорец. Диалоговият прозорец "Добавяне на ограничение" трябва да се показва, както е показано на фигура 30-3.
Нашият модел е линеен, защото целевата клетка се изчислява като сума от членовете, които имат формата (променяща се клетка)*(константа) и защото ограниченията за използване на ресурсите се изчисляват чрез сравняване на сумата на (променящите се клетки)*(константи) с константа.
Когато диалоговият прозорец за параметри на Solver е попълнен, щракнете върху Решавай и имаме резултатите, показани по-рано на фигура 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. Нашето ограничение трябва също да остави неограничена двоичната стойност в променящата се клетка на Проект 4, ако не изберем Проект 3. Ако не изберем проект 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 милиарда долара.
Решаване на задачи за програмиране на двоични и цели числа
Моделите на линеен Solver, в които някои или всички променящи се клетки трябва да бъдат двоични или цели, обикновено са трудни за решаване от линейните модели, в които е позволено всички променящи се клетки да са дроби. Поради тази причина често се задоволяваме с почти оптимално решение на двоичен или целочислен проблем за програмиране. Ако вашият модел на Solver се изпълнява дълго време, може да помислите за коригиране на настройката за толеранс в диалоговия прозорец "Опции на Solver". (Вижте фигура 30-6.) Например настройка за толеранс 0,5% означава, че Solver ще спре при първото намиране на осъществимо решение, което е в рамките на 0,5 процента от теоретическата оптимална стойност на целевата клетка (теоретичната оптимална стойност на целевата клетка е оптималната целева стойност, която се намира, когато двоичните и целочислените ограничения са пропуснати). Често сме изправени пред избор между намиране на отговор в рамките на 10 процента от оптималното за 10 минути или намиране на оптимално решение за две седмици компютърно време! Стойността на толеранса по подразбиране е 0,05%, което означава, че Solver спира, когато намери стойност на целева клетка в рамките на 0,05 процента от теоретическата оптимална стойност на целевата клетка.
Проблеми
- Една компания има девет проекта, които са в процес на разглеждане. NPV, добавена от всеки проект, и капиталът, необходим за всеки проект през следващите две години, са показани в следващата таблица. (Всички числа са в милиони.) Например проект 1 ще добави 14 милиона долара за NPV и ще изисква разходи от 12 милиона лв. за година 1 и 3 милиона лв. за година 2. През година 1 е наличен капитал от 50 милиона долара за проекти, а 20 милиона долара са налични през година 2.
| NPV | Разходи за година 1 | Разходи за година 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 |
| Project 8 | 10 | 36 | 3 |
| Проект 9 | 12 | 18 | 3 |
- Ако не можем да предприемем част от проекта, но трябва да предприемем целия или нито един проект, как можем да увеличим NPV?
- Да предположим, че ако се предприеме проект 4, трябва да се предприеме проект 5. Как можем да увеличим NPV?
Издателска компания се опитва да определи коя от 36-те книги трябва да публикува тази година. Файлът Pressdata.xlsx дава следната информация за всяка книга:
- Планирани приходи и разходи за развойна дейност (в хиляди долари)
- Страници във всяка книга
- Дали книгата е насочена към аудитория от разработчици на софтуер (посочено с 1 в колона E)
Една издателска компания може да публикува книги с обща дължина до 8500 страници тази година и трябва да публикува поне четири книги, насочени към разработчици на софтуер. Как компанията може да увеличи печалбата си?
За статията
Тази статия е адаптирана от Microsoft Office Excel 2007 Анализ на данни и бизнес моделиране от Уейн Л. Уинстън.
Тази книга в стил класна стая е разработена от поредица от презентации на Уейн Уинстън, известен статистик и професор по бизнес, който специализира в творчески, практически приложения на Excel.