Използването на Solver за проектни предложения

Забележка:  Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

Как да фирма използвате Solver, за да определите кои проекти, трябва да предприеме?

Всяка година, фирма като Eli Lilly трябва да определите коя комбинация да се развиват; фирма като Microsoft, които софтуерни програми да се развиват; фирма като Проктър & Гембъл, кои нови потребителски продукти за разработване на. Функцията "Решател" в Excel може да ви помогне фирма правят тези решения.

Повечето компании искат да предприемат проекти, които допринасят най-настоящата нетна стойност (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 да използвате диапазон от двоично променящи се клетки чрез добавяне на ограничение – изберете променливи клетки, които искате да използвате и след това изберете клетка от списъка в диалоговия прозорец Добавяне на ограничение.

Book image
Фигура-30-1 данни ще използваме със Solver да определи кои проекти да се извърши

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

  • Целевата клетка. Увеличаване на NPV, генерирани от избраните проекти.

  • Променливи клетки. Очакваме за 0 или 1 двоични промяната клетка за всеки проект. Съм намира тези клетки в диапазона A6:A25 (и наименувани диапазона дреболия). Например 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 на всички проекти и нашите целевата клетка е линейна, защото той се изчислява чрез сумиране условия, които следват формата (промяната cell)*(constant). По подобен начин да изчисли капитал използва всяка година и трудов използва всяка година чрез копиране от E2 F2:J2 формулата SUMPRODUCT(doit,E6:E25).

Сега запълване в диалоговия прозорец параметри на Solver, както е показано в фигура 30-2.

Book image
Параметри на Solver фигура-30-2 набор от диалоговия прозорец поле за избор на модел на проект

Нашата цел е да увеличите NPV на избрани проекти (клетка B2). Нашите променливи клетки (диапазона с име doit) са двоично променящи се клетки за всеки проект. Ограничението E2:J2 < = E4:J4 гарантира, че всяка година капитал и трудов използва по-малко или равно на главни и трудов налични. За да добавите ограничение, което прави двоични променливи клетки, щракнете върху Добави в диалоговия прозорец параметри на Solver и след това изберете клетка от списъка в средата на диалоговия прозорец. Диалоговия прозорец Добавяне на ограничение трябва да се показва, както е показано в илюстрацията 30-3.

Book image
Използване на фигура-30-3 СК опция в диалоговия прозорец Добавяне на ограничение, за да настроите двоично променящи се клетки – клетки, които ще се показва 0 или 1.

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

С диалоговия прозорец параметри на 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. L 9 служи клетка препраща към двоична стойност, свързани с проект 3 и клетка L12 двоична стойност, свързани с проекта 4. Чрез добавяне на ограничението l 9 служи < = L12, ако изберете проект 3, l 9 служи е равно на 1 и нашите ограничение принуждава L12 (проект 4 двоично) на равно на 1. Нашите ограничение също трябва да оставите двоични стойността в клетката на промяната на проекта 4 неограничен, ако не изберете проект 3. Ако не изберете проект 3, l 9 служи е равно на 0 и нашите ограничението позволява проект 4 двоични на равно на 0 или 1, което е това, което искате. Нов оптимално решение е показана в фигура 30-4.

Book image
Нова фигура-30-4 оптимално решение, ако не изпъкват 3 и след това проект 4

Нов оптимално решение се изчислява ако изберете проект 3 означава, че трябва също да изберете проект 4. Сега, да предположим, че можем да направим само четири проекти измежду проекти 1 до 10. (Вижте At най-много 4 от P1 – P10 лист, показани в фигура 30-5). В клетка ниво 8 ние изчисли сумата на двоични стойности, свързани с проекти 1 до 10 с формула SUM(A6:A15). И след това добавяме ограничението ниво 8 < = L10, която гарантира, че най-много 4 от първите 10 проекти са избрани. Нов оптимално решение е показана в фигура 30-5. NPV има пропускат до $9.014 милиарда.

Book image
Фигура-30-5 оптимално решение, когато можем да изберете само 4 от 10 проекти

Линейни Solver модели, в които някои или всички променливи клетки се изисква да бъдат двоични или цяло число, обикновено са по-труден за решаване от линейни модели, в която всички променливи клетки могат да бъдат дроби. Поради тази причина често сме доволни близо до оптимално решение двоично или цяло число програмиране проблема. Ако модел на Solver се изпълнява дълго време, може да искате да опитате да регулирате позиционирането на отклонение настройката в диалоговия прозорец Опции за Solver. (Вижте фигура 30-6). Например, настройка на отклонение от 0,5 % означава, че Solver ще спре за първи път се намира възможно решение, което се намира в 0,5 процента от стойността на теоретичната оптимална целевата клетка (стойността на теоретичната оптимална целевата клетка е оптимална целева стойност, намерени, когато ограничения за двоично и цяло число, са пропуснати). Често ние сме изправени пред избор между намиране на отговор до 10 процента от оптимална 10 минути или намиране на оптимално решение в две седмици от компютър време! Стойността по подразбиране отклонение е 0,05 %, което означава, че Solver спира, когато го намери стойността на целевата клетка в рамките на 0,05 % от стойността на теоретичната оптимална целевата клетка.

Book image
Фигура-30-6 промените отклонение опцията

  1. 1. фирма има девет проекта под внимание. 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

Проект 8

10

36

3

Проект 9

12

18

3

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

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

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

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

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

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

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

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

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

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×