Капитальные вложения в Excel
Используйте решатель в Excel, чтобы найти сочетание капитальных вложений, которая максимизирует общую прибыль.
Сформулируйте модель
Модель, которую мы будем решать выглядит следующим образом в Excel.
-
Для того чтобы сформулировать эту модель двоичного целочисленного программирования (BIP), ответить на следующие три вопроса.
\ А. Какие решения должны быть сделаны? Для решения этой проблемы нам нужно Excel, чтобы выяснить, какие капитальные вложения, чтобы сделать (Да = 1, Нет = 0).
\ Б. Каковы ограничения на эти решения? Во-первых, объем капитала, используемый инвестиций не может превышать ограниченное количество доступного капитала (50). Например, инвестиции One использует 12 единиц капитала. Во-вторых, только инвестиции Один или два инвестиции могут быть сделаны.
В-третьих, только инвестиции Три или четыре инвестиции могут быть сделаны. В-четвертых, инвестиции Шесть и инвестиции Семь могут быть сделаны только если инвестиции Пять производится.
\ С. Какова общая мера производительности этих решений? Общая мера работы является общей прибылью от капитальных вложений, так что цель состоит в том, чтобы максимально увеличить это количество.
-
Для того, чтобы сделать модель легче понять, назвать следующие диапазоны.
Range Name |
Cells |
Profit |
C5:I5 |
YesNo |
C13:I13 |
TotalProfit |
M13 |
-
Вставьте следующие пять функций SUMPRODUCT.
Объяснение: К7 клеток (размер капитала используется) равно SUMPRODUCT диапазона C7: I7 и YesNo, клетка К8 равно SUMPRODUCT диапазона C8: I8 и YesNo и т.д.
Общая прибыль равна SUMPRODUCT прибыли и YesNo.
Метод проб и ошибок
При такой постановке становится легко проанализировать любое пробное решение.
-
Например, если мы делаем инвестиций с одной и двумя, то второе ограничение нарушается.
-
Например, если мы делаем инвестиции шесть и семь, не делая инвестиции Five, четвертое ограничение нарушается.
-
Тем не менее, это нормально, чтобы сделать инвестиции один, пять и шесть. Все ограничения удовлетворены.
Не обязательно, чтобы методом проб и ошибок. Опишем следующий, как Excel Solver можно использовать, чтобы быстро найти оптимальное решение.
Решить Модель
Для того, чтобы найти оптимальное решение, выполните следующие действия.
-
На вкладке Данные в группе Анализ выберите пункт Поиск решения.
Примечание: не может найти кнопку Solver? Нажмите здесь, чтобы загрузить Solver надстройку.
Введите параметры решателя (читайте дальше). Результат должен соответствовать картинке ниже.
-
Введите TotalProfit для этой цели.
-
Нажмите кнопку Макс.
-
Введите YESNO для меняющаяся переменная клеток.
-
Нажмите кнопку Добавить, чтобы ввести следующее ограничение.
-
Нажмите кнопку Добавить, чтобы ввести следующее ограничение.
Примечание: двоичные переменные являются либо 0, либо 1.
-
Проверить «Make Неограниченные переменные неотрицательные» и выберите «Simplex LP».
-
Наконец, нажмите кнопку Выполнить.
Результат:
Оптимальное решение:
Вывод: Оптимально делать инвестиции в два, четыре, пять и семь.
Это решение дает максимальную прибыль 146. Все ограничения удовлетворены.