Решение задач математического программирования с помощью надстройки «Поиск решения» ЭТ Excel

Задачи линейного программирования, целочисленного программирования и ряд задач нелинейного программирования могут быть решены с помощью стандартного прикладного программного обеспечения. Например, в ЭТ MS Excelдля этого имеется модуль «Поиск решения», вызываемый командой меню «Сервис/Поиск решения». Для активизации данного модуля необходимо выполнить команду «Сервис/Надстройки» и установить флажок напротив строки меню «Поиск решения».

Рассмотрим пример применения «Поиска решения» на основе решения задачи оптимизации портфеля ценных бумаг – одной из классических задач управления финансовыми средствами.

Постановка задачи. Перед инвестором стоит задача на основе информации, представленной в таблице 1, разместить имеющиеся средства так, чтобы получить максимальную прибыль за 1 период планирования (1 год), при этом должны быть выполнены следующие условия:

1. Суммарный объем капитала составляет 100 000 $;

2. доля средств, вложенная в один из объектов, не может превышать 25%;

3. более 40% всех средств должны быть вложены в долгосрочные активы;

4. доля высокорисковых активов не может превышать трети от суммарного объема.

Таблица 1 – Информация об объектах инвестирования

Объект	Доходность, p (%)	Срок выкупа, год	Надежность (1-5 баллов)
A	5.5
B
C
D	7.5
E	5.5
F

Построим экономико-математическую модель задачи.

Искомые переменные – объемы средств, вложенные в активы: х₁, х₂, …, х₆.

Прибыль, которую получит инвестор, задается целевой функцией:

Сформируем ограничения:

Ограничения на суммарный объем активов -

Ограничение на размер доли каждого актива

Необходимость долгосрочного инвестирования (например, более 3 лет)

Учет необходимости снижения риска –

Естественное экономическое ограничение – неотрицательность искомых переменных -

Для решения задачи выполним следующие шаги.

1. На рабочем листе представим необходимую для решения информацию, согласно рисунку 1.
Ячейки В13, Н9-Н11 должны содержать формулы, отражающие зависимость между искомыми переменными и условиями задачи. В данном случае целесообразно использовать функцию Суммпроизв(…), аргументами которой являются диапазоны B4-G4 и диапазоны соответствующих параметров.

Рисунок 1 – Исходные данные для решения ЗЛП

2. Выполнить команду Сервис/Поиск решения и заполнить все поля диалогового окна:

Указать адрес ячейки (В13), содержащей целевую функцию, указать тип целевой функции,

В поле «изменяя ячейки» указать адреса всех искомых переменных (от B4 до G4).

Затем последовательно заполнить все ограничения (Пример на рисунке 2.)

Рисунок 2 – Заполнение диалогового окна Поиск решения

Если возникли ошибки ввода, то изменить или добавить ограничение можно с помощью командных кнопок «Добавить, изменить, удалить».

Рисунок 3 – Диалоговое окно для определения ограничений задачи

Далее, если это необходимо, устанавливаются особые значения параметров (кнопка «Параметры»).

Рисунок 4 – Информационное окно о результатах решения

Результаты отражаются на рабочем листе.

Результаты решения представлены на рисунке 5.

Рисунок 5 – Результаты решения задачи

На рисунке 6 представлена структура инвестиционного портфеля.

На основе решения проводится анализ, и принимаются соответствующие управленческие решения.