Използване на Solver за бюджетиране на главни букви

Повечето корпорации искат да започнат проекти, които имат най-голяма нетна настояща стойност (NPV), подлежащи на ограничени ресурси (обикновено капитал и труд). Да речем, че фирма за разработка на софтуер се опитва да определи кои от 20 софтуерни проекта трябва да предприеме. NPV (в милиони долари) е допринесла за всеки проект, както и за капитала (в милиони долари), а броят на програмистите, необходими за всяка от следващите три години, се дава в работния лист Основен модел в 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, ние не изпълняваме проекта. Настройвате Solver да използва диапазон от двоични променливи клетки чрез добавяне на ограничение – изберете променящите се клетки, които искате да използвате, и след това изберете Bin от списъка в диалоговия прозорец Добавяне на ограничение.

С този фон сме готови да решим проблема за избор на софтуерен проект. Както винаги с модела Solver, започваме, като идентифицираме нашата целева клетка, променящите се клетки и ограниченията.

• Целева клетка. Максимизираме NPV, генерирана от избрани проекти.

• Променящи се клетки. Търсим 0 или 1 двоична променяща се клетка за всеки проект. Намерих тези клетки в диапазона A6:A25 (именувах диапазона doit). Например едно 1 в клетка A6 показва, че изпълняваме Project 1; a 0 в клетка C6 показва, че не изпълняваме Project 1.

• Ограничения. Трябва да гарантираме, че за всяка година t (t=1, 2, 3), използваният т капитал година е по-малък или равен на наличния капитал Year t, а използваният year t труд е по-малко или равно на наличния труд Year 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 и след това избирам Bin от списъка в средата на диалоговия прозорец. Диалоговият прозорец Добавяне на ограничение трябва да се покаже, както е показано на фигура 30-3.

Нашият модел е линеен, защото целевата клетка се изчислява като сума от термините, които имат формуляра (променяща се клетка)*(константа) и защото ограниченията за използване на ресурси се изчисляват чрез сравняване на сумата от (променящи се клетки)*(константи) с константа .

С попълнен диалоговия прозорец Параметри на Solver щракнете върху Решаване и резултатите са показани по-рано на Фигура 30-1. Компанията може да получи максимална NPV от $9,293 милиона ($9.293 милиарда), като избере Projects 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 (двоичен проект 4) да е равно на 1. Нашето ограничение трябва да остави двоичната стойност в променящата се клетка на Project 4 неограничена, ако не изберем Project 3. Ако не изберем Project 3, L9 е равно на 0 и нашето ограничение позволява двоичният файл на Project 4 да е равен на 0 или 1, което е, което искаме. Новото оптимално решение е показано на фигура 30-4.

Ако изберете Project 3, се изчислява ново оптимално решение, което означава, че трябва също да изберем Project 4. Да предположим, че можем да изпълняваме само четири проекта от проекти от 1 до 10. (Вижте работния лист Най-много 4 от P1–P10 , показан на фигура 30-5.) В клетка L8 изчисляваме сумата от двоичните стойности, свързани с Projects от 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 процента от теоретичната оптимална стойност на целевата клетка.

1. Фирмата има девет проекта в процес на разглеждане. NPV, добавена от всеки проект, и капиталът, необходим за всеки проект през следващите две години, е показан в следващата таблица. (Всички числа са в милиони.) Например Project 1 ще добави $14 милиона в NPV и ще изисква разходи от 12 милиона долара през година 1 и 3 милиона долара през година 2. През година 1, 50 милиона долара в капитал е наличен за проекти, и $20 милиона е на разположение през година 2.

	NPV	Разходи за година 1	Разходи за втора година
Проект 1	14	12	3
Проект 2	17	54	7
Project 3	17	6	6
Проект 4	15	6	2
Project 5	40	30	35
Проект 6	12	6	6
Project 7	14	48	4
Project 8	10	36	3
Проект 9	12	18	3

• Ако не можем да поемем част от проект, но трябва да поемем или целия, или нито един проект, как можем да увеличим NPV?

• Да предположим, че ако се изпълнява Project 4, трябва да се предприеме Project 5. Как можем да увеличим NPV?

• Една издателска компания се опитва да определи коя от 36-те книги трябва да публикува тази година. Файлът Pressdata.xlsx дава следната информация за всяка книга:

  • Прогнозни приходи и разходи за развитие (в хиляди долари)

  • Страници във всяка книга

  • Дали книгата е насочена към аудитория от разработчици на софтуер (посочено с 1 в колона E)

    Издателската фирма може да публикува книги с обща стойност до 8500 страници тази година и трябва да публикува поне четири книги, насочени към разработчиците на софтуер. Как може компанията да увеличи максимално печалбата си?

Тази статия е адаптирана от анализа на данни на Microsoft Office Excel 2007 и моделирането на бизнеса от Wayne L. Winston.

Тази книга в стил класна стая е разработена от поредица презентации от Уейн Уинстън – добре познат статистик и бизнес професор, който специализира в творчески, практически приложения на Excel.