Сформулируйте модель | Метод проб и ошибок | Решите модель
Используйте решатель в Excel найти комбинацию капитальные вложения что максимизирует общую прибыль.
Сформулируйте модель
Модель, которую мы собираемся решить, выглядит в Excel следующим образом.
1. Чтобы сформулировать эту модель двоичного целочисленного программирования (BIP), ответьте на следующие три вопроса.
а. Какие решения нужно принимать? Для этой задачи нам понадобится Excel, чтобы выяснить, какие капитальные вложения нужно сделать (Да = 1, Нет = 0).
б. Что сдерживает эти решения? Во-первых, сумма капитала, используемого для инвестиций, не может превышать ограниченный размер доступного капитала (50). Например, «Investment One» использует 12 единиц капитала. Во-вторых, могут быть сделаны только инвестиции Один или два. В-третьих, можно сделать только вложение Три или четыре. В-четвертых, вложение Шесть и Вложение Седьмое могут быть сделаны только в том случае, если сделано вложение Пять.
c. Каков общий показатель эффективности этих решений? Общий показатель эффективности - это общая прибыль от сделанных капитальных вложений, поэтому цель состоит в том, чтобы максимизировать это количество.
2. Чтобы облегчить понимание модели, создайте следующие именованные диапазоны.
| Название диапазона | Клетки |
|---|---|
| Выгода | C5: I5 |
| Да нет | C13: I13 |
| Общая прибыль | M13 |
3. Вставьте следующие пять функций СУММПРОИЗВ.
Объяснение: ячейка K7 (сумма использованного капитала) равна суммированному произведению диапазона C7: I7 и Да Нет, ячейка K8 равна суммированному произведению диапазона C8: I8 и Да Нет и т. Д. Общая прибыль равна сумме Прибыли и Да Нет.
Методом проб и ошибок
С такой формулировкой становится легко анализировать любое пробное решение.
1. Например, если мы инвестируем один и два, второе ограничение нарушается.
2. Например, если мы делаем инвестиции шесть и семь, не вкладывая пять, четвертое ограничение нарушается.
3. Тем не менее, делать инвестиции раз в пять и шесть - это нормально. Все ограничения выполнены.
Необязательно использовать метод проб и ошибок. Далее мы опишем, как Решатель Excel можно использовать для быстрого поиска оптимального решения.
Решите модель
Чтобы найти оптимальное решение, выполните следующие действия.
1. На вкладке «Данные» в группе «Анализировать» щелкните «Решатель».
Примечание: не можете найти кнопку Решатель? Щелкните здесь, чтобы загрузить надстройку Solver.
Введите параметры решателя (читайте дальше). Результат должен соответствовать изображенному ниже.
2. Введите TotalProfit для цели.
3. Щелкните Макс.
4. Введите YesNo в поле Changing Variable Cells.
5. Щелкните Добавить, чтобы ввести следующее ограничение.
6. Щелкните Добавить, чтобы ввести следующее ограничение.
Примечание: двоичные переменные равны 0 или 1.
7. Отметьте «Сделать неограниченные переменные неотрицательными» и выберите «Simplex LP».
8. Наконец, нажмите «Решить».
Результат:
Оптимальное решение:
Вывод: оптимально делать вложения Два, Четыре, Пять и Семь. Это решение дает максимальную прибыль 146. Все ограничения соблюдены.
