Как компания может использовать надстройку "Поиск решения" для определения проектов, за которые ей следует взяться?
Каждый год такая компания, как Eli Lilly, должна определять, какие препараты разрабатывать; такая компания, как Microsoft, какие программы разрабатывать; такая компания, как Proctor & Gamble, которая разрабатывает новые потребительские продукты. Надстройка "Поиск решения" в Excel может помочь компании принять эти решения.
Как компания может использовать надстройку "Поиск решения" для определения проектов, за которые ей следует взяться?
Большинство корпораций хотят осуществлять проекты, которые вносят наибольший вклад в чистую приведенную стоимость (ЧПС) при условии ограниченных ресурсов (обычно капитала и труда). Предположим, что компания-разработчик программного обеспечения пытается определить, за какой из 20 проектов программного обеспечения она должна взяться. NPV (в миллионах долларов), вносимые каждым проектом, а также капитал (в миллионах долларов) и количество программистов, необходимых в течение каждого из следующих трех лет, приведены на листе Basic Model в файле 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, проект не выполняется. Вы настраиваете надстройку "Поиск решения" на использование диапазона двоичных изменяющихся ячеек, добавляя ограничение: выделите необходимые ячейки и выберите пункт "Ячейка" в списке в диалоговом окне "Добавить ограничение".
Имея этот опыт, мы готовы решить проблему выбора программного проекта. Как и всегда в модели поиска решения, мы начинаем с определения целевой ячейки, изменяющихся ячеек и ограничений.
- Целевая ячейка. Мы максимизируем NPV, генерируемую выбранными проектами.
- Изменение ячеек. Мы ищем ячейку 0 или 1 двоичное изменение для каждого проекта. Эти ячейки расположены в диапазоне A6:A25 (и присвоено значение диапазону). Например, 1 в ячейке A6 указывает, что мы беремся за Проект 1; 0 в ячейке C6 означает, что мы не беремся за проект 1.
- Ограничения. Мы должны обеспечить, чтобы для каждого года t (t=1, 2, 3) использованный капитал в течение года t был меньше или равен доступному капиталу в течение года t , а в течение года t использованный труд был меньше или равен доступному труду в течение года t .
Как видите, наш рабочий лист должен рассчитывать для любого выбора проектов ЧПС, капитал, используемый ежегодно, и программистов, используемых каждый год. В ячейке B2 я использую формулу СУММПРОИЗВ(doit,ЧПС) для вычисления общей функции ЧПС, сгенерированной выбранными проектами. (Имя диапазона NPV относится к диапазону C6:C25.) Для каждого проекта с числом 1 в столбце A эта формула получает функцию NPV проекта, а для каждого проекта со значением 0 в столбце A эта формула не определяет функцию NPV проекта. Таким образом, мы можем вычислить функцию NPV всех проектов, а наша целевая ячейка является линейной, поскольку она вычисляется путем суммирования членов в следующем формате (изменяющаяся ячейка)*(константа). Аналогичным образом я вычисляю капитал, затрачиваемый каждый год, и труд, затрачиваемый каждый год, копируя из E2 в F2:J2 формулу СУММПРОИЗВ(doit,E6:E25).
Теперь я заполняю диалоговое окно Solver Parameters, как показано на рисунке 30-2.
Наша цель — максимизировать NPV выбранных проектов (ячейка B2). Наши изменяющиеся ячейки (диапазон с именем doit) являются двоичными изменяющимися ячейками для каждого проекта. Ограничение E2:J2<=E4:J4 гарантирует, что в течение каждого года использованный капитал и труд будут меньше или равны имеющемуся капиталу и труду. Чтобы добавить ограничение, которое делает изменяемые ячейки двоичными, я нажимаю кнопку Add (Добавить) в диалоговом окне Solver Parameters (Параметры решателя), а затем выбираю Bin из списка в середине диалогового окна. Диалоговое окно Add Constraint должно выглядеть, как показано на рисунке 30-3.
Наша модель является линейной, поскольку целевая ячейка вычисляется как сумма членов, имеющих вид (изменяющаяся ячейка)*(константа), а также потому, что ограничения на использование ресурсов вычисляются путем сравнения суммы (изменяющихся ячеек)*(констант) с константой.
Заполнив диалоговое окно Solver Parameters, нажмите кнопку Solve, и мы получим результаты, показанные ранее на рисунке 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, если мы выберем Project 3, L9 будет равно 1, и наше ограничение принуждает L12 (двоичный файл Project 4) равняться 1. Наше ограничение также должно оставлять неограниченным двоичное значение в изменяющейся ячейке Проекта 4, если мы не выберем Проект 3. Если мы не выберем Project 3, L9 будет равно 0, и наше ограничение позволяет двоичному файлу Project 4 быть равным 0 или 1, что мы и хотим. Новое оптимальное решение показано на рисунке 30-4.
Новое оптимальное решение вычисляется, если выбор проекта 3 означает, что мы также должны выбрать проект 4. Теперь предположим, что мы можем сделать только четыре проекта из числа проектов с 1 по 10. (См. рабочий лист «Максимум 4 из P1–P10», показанный на рисунке 30-5.) В ячейке L8 вычисляем сумму двоичных значений, связанных с проектами с 1 по 10, с помощью формулы СУММ(A6:A15). Затем мы добавляем ограничение L8< = L10, которое гарантирует, что будут отобраны максимум 4 из первых 10 проектов. Новое оптимальное решение показано на рисунке 30-5. NPV упал до $9,014 млрд.
Решение задач двоичного и целочисленного программирования
Линейные модели решателя, в которых некоторые или все изменяющиеся ячейки должны быть двоичными или целочисленными, обычно труднее решить, чем линейные модели, в которых все изменяющиеся ячейки могут быть дробями. По этой причине мы часто довольствуемся почти оптимальным решением задачи двоичного или целочисленного программирования. Если модель надстройки "Поиск решения" выполняется в течение длительного времени, можно рассмотреть возможность настройки параметра "Допуск" в диалоговом окне "Параметры решения проблемы". (См. Рисунок 30-6.) Например, значение допуска 0,5 % означает, что надстройка "Поиск решения" остановится при первом нахождении осуществимого решения, которое находится в пределах 0,5 процента от теоретического оптимального значения целевой ячейки (теоретическое оптимальное значение целевой ячейки — это оптимальное целевое значение, найденное при опущении двоичных и целочисленных ограничений). Часто мы сталкиваемся с выбором между поиском ответа в пределах 10 процентов от оптимального за 10 минут или поиском оптимального решения за две недели компьютерного времени! Значение допуска по умолчанию равно 0,05 %, что означает, что надстройка «Поиск решения» останавливается при обнаружении значения целевой ячейки в пределах 0,05 процента от теоретического оптимального значения целевой ячейки.
Проблемы
- У компании на рассмотрении девять проектов. ЧПС, добавленная каждым проектом, и капиталовложения, необходимые для каждого проекта в течение следующих двух лет, показаны в следующей таблице. (Все цифры исчисляются миллионами.) Например, Проект 1 добавит 14 миллионов долларов США в ЧПС и потребует затрат в размере 12 миллионов долларов США в течение 1-го года и 3 миллионов долларов США в течение 2-го года. В течение 1-го года для проектов доступно 50 миллионов долларов капитала, а 20 миллионов долларов — для 2-го года.
| ЧПС | Расходы за 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 ".
Эта книга была разработана на основе серии презентаций Уэйна Уинстона, известного статистика и профессора бизнеса, который специализируется на творческом практическом применении Excel.