Линейная модель планирования оптимальной системы портфелей банка
Представим баланс банка в следующей упрощенной форме:
ЦБ + КР = ДВ + СД + К, ( 6.2)
где ЦБ — ценные бумаги; КР — кредиты; ДВ — депозиты до востребования; СД — срочные депозиты; К — собственный капитал.
Прибыль на ценные бумаги и прибыль по кредитам обозначены Пцб и Пкр соответственно. Издержки по привлечению депозитов и по капиталу предполагаются равными нулю. Отсюда прибыль банка
Пр = Пцб ЦБ + Пкр КР. (6.3)
Уравнение называется целевой функцией. Проблема заключается в том, чтобы максимизировать эту функцию при следующих ограничениях:
1. Балансовое ограничение
ЦБ + КР < 100. (6.4)
2. Ликвидное ограничение
ЦБ > 0,30 (ЦБ + КР) или (6.5)
ЦБ > 0,43 КР.
3. Кредитное ограничение
КР > 35. (6.6)
Цифровые значения заданы условно в млн. долл.
Нужно достичь наивысшего значения целевой функции, совместимой с областью допустимых решений. Сформулированная задача относится к классу задач линейного программирования. Рассмотрим последовательность действий, выполняемых при решении данной задачи с помощью Excel.
Алгоритм 1. Ввод данных для решения задачи линейного программирования
1. Для решения задачи сделать форму (рис. 6.1) и ввести текст, являющийся комментарием.
A B C D E F G H
1
2 Имя
3 Значение
4 Нижн. Гр.
5 Верхн. Гр.
6 Коэф. ЦФ
7
8 Вид
9 Балансовое
10 Ликвидное
11 Кредитное
12
13
Рис. 6.1
2. Ввести исходные данные в форму (рис. 6.1).
3. Ввести зависимости из математической модели (6.3–6.6).
3.1. Ввести зависимости для целевой функции:
- Курсор в F6.
- Курсор на кнопку Мастер функций.
- М1.
На экране диалоговое окно Мастер функций – шаг 1 из 2.
- Курсор в окно Категория на категорию Математические.
- М1.
- Курсор в окно функции на СУММПРОИЗВ.
- М1.
- Далее.
На экране диалоговое окно Мастер функций – шаг 2 из 2.
- В массив 1 ввести B$3:E$3.
Заметим, что адреса ячеек удобно вводить не с клавиатуры, а протаскивая мышь по ячейкам, чьи адреса следует ввести.
- В массив 2 ввести B6:E6.
- Готово.
Ввести зависимости для левых частей ограничений:
- Курсор в F6.
- Копировать в буфер.
- Курсор в F9.
- Вставить из буфера.
- Скопировать F9 в F10.
3.3. Ввести дополнительные зависимости для левых частей:
- Курсор в F11.
- Согласно п. 3.1 c использованием функции СУММПРОИЗВ ввести выражение: =0,3*СУММПРОИЗВ(B$3:E$3;B10:E10)-B$3:
- Курсор в F13.
- Согласно п. 3.1 с использованием функции СУММПРОИЗВ, ввести выражение: =СУММПРОИЗВ(D$3:E$3;B10:E10)-B17:
- Курсор в F14.
- Ввести: =D3.
- Курсор в F15.
- Ввести: =E3.
3.3. Ввести значения и зависимости для правых частей ограничений:
- Курсор последовательно устанавливать в ячейки B10, B11, B14, B15 и ввести в них значения согласно таблице (рис. 6.1):
- В ячейку B9 ввести =B17;
- В ячейку B13 ввести =H14+H15+B17.
Алгоритм 2. Работа в диалоговом окне Поиск решения
1. Сервис, Поиск решения…
На экране: диалоговое окно Поиск решения.
2. Назначить целевую функцию:
- Курсор в окно Установить целевую ячейку.
- Ввести адрес F6: $F$6.
- Ввести направление целевой функции: Максимальному значению.
3. Ввести адреса искомых переменных:
- Курсор в поле Изменить.
- Ввести адреса B3:E3: $B$3:$E3.
4. Добавить…
На экране: диалоговое окно Добавление ограничения.
5. Ввести ограничения:
В окне Ссылка на ячейку установить курсор и ввести мышкой адрес ячейки F9:$F$9.
Курсор на стрелку.
М1.
Курсор на знак. Установить знак ограничения < согласно таблице (рис. 6.1).
М1.
Курсор в правое окно.
Ввести адрес ячейки H9: =$H$9.
Добавить…
Аналогично ввести остальные ограничения.
После ввода последнего ограничения вместо слова Добавить… ввести ОК.
На экране: диалоговое окно Поиск решения с введенными условиями.
Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений, то это делается с помощью команд Изменить…, Удалить.
На этом ввод условий задачи заканчивается.
2. Решение задачи
Решение задачи производится сразу же после ввода данных по предыдущему алгоритму, когда на экране находится диалоговое окно Поиск решения.
Алгоритм 3. Решение задачи линейного программирования
1. Параметры…
На экране: диалоговое окно Параметры поиска решения.
Параметры – Максимальное время, Предельное число итераций, Относительная погрешность, Допустимое отклонение – можно установить по умолчанию, что подходит для большинства решаемых задач.
2. Установить флажок Линейная модель, что обеспечивает применение симплекс-метода.
3. ОК.
На экране: диалоговое окно Поиск решения.
4. Выполнить.
На экране: диалоговое окно Результаты поиска решения. Решение найдено и результаты оптимального решения приведены в таблице (рис. 6.1).
Однако решение задачи находится не всегда. Если условия задачи несовместны, то на экране появляется диалоговое окно с надписью Поиск не может найти подходящего решения. Если целевая функция не ограничена, то на экране появится диалоговое окно с надписью Значения целевой ячейки не сходятся. В этих случаях необходимы специальные действия по корректировке исходной модели.
В других случаях (нарушение условий линейности и т. п.) следует искать ошибки в модели или на этапах ввода зависимостей и исходных данных модели.
3. Графическое представление результатов решения
Важным фактором, помогающим принять оптимальное решение, является наглядное представление полученных результатов.
Алгоритм 4. Построение встроенной диаграммы.
1. Выделить B3:E3, т. е. те ячейки, значения которых должны быть представлены на диаграмме.
2. Курсор на кнопку Мастер диаграмм.
3. М1.
4. Курсор в угол области, выделяемой для размещения диаграммы.
МН протащить для создания областей, в которых будет построена диаграмма.
На экране: диалоговое окно Мастер диаграмм – шаг 1.
5. Далее.
На экране: диалоговое окно Мастер диаграмм – шаг 2.
6. Курсор на выбранный тип диаграмм. В нашем случае – гистограммы.
7. Далее.
На экране: диалоговое окно Мастер диаграмм – шаг 3. Назначается формат типа диаграмм.
8. Выбираем формат 2.
9. Далее.
На экране: диалоговое окно Мастер диаграмм – шаг 4. На этом шаге назначается вариант представления рядов данных. Кроме того, назначается число строк и столбцов, данные в которых являются метками по осям.
10. Далее.
На экране: диалоговое окно Мастер диаграмм – шаг 5. На этом шаге можно ввести легенду, а также названия диаграммы и осей.
11. Готово.
На экране: встроенная диаграмма с маркерами, показанная на рис. 2.
4. Анализ оптимального решения
Анализ оптимального решения начинается после успешного решения задачи, когда на экране появляется окно Результаты поиска решения. Решение найдено. С помощью этого диалогового окна можно вызвать отчеты трех типов:
- Результаты.
- Устойчивость.
- Пределы.
Отчет по результатам
Отчет состоит из трех таблиц:
Таблица 1 приводит сведения о целевой функции до начала вычислений и в результате решения задачи.
Таблица 2 приводит значения искомых переменных, полученных в результате решения задачи.
Таблица 3 показывает результаты оптимального решения для ограничений.
Отчет по устойчивости
Отчет по устойчивости состоит из двух таблиц.
В таблице 1 приводятся следующие значения для переменных:
- результат решения задачи;
- редуцируемая стоимость, т. е. дополнительные двойственные переменные, которые показывают, насколько изменяется целевая функция при принудительном включении единицы этой продукции в оптимальное решение;
- коэффициенты целевой функции;
- предельные значения приращения коэффициентов целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение.
В таблице 2 приводятся аналогичные значения для ограничений:
- величина использованных ресурсов;
- теневая цена, т. е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу;
- значения приращения ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение.
Отчет по пределам
В нем показано, в каких пределах могут изменяться искомые переменные, вошедшие в оптимальное решение, при сохранении структуры оптимального решения.
При заданных Пкр = 0,15 и Пцб = 0,10 и отсутствии резервных требований (Р = 0) получим оптимальное решение [ЦБ*, КР*], равное [30, 70]. Оптимальный доход при этом будет: Пр* = 0,15 (70) + 0,10 (30) = 13,5.
Заметим, что пока Пкр > Пцб эффективная совокупность решений будет определятся ликвидным ограничением (т. е. 0,3 [100] = 30). Если Пкр < Пцб, тогда оптимальное решение будет подчинено кредитному ограничению [65, 35]. (Задание – проверить)
Ви переглядаєте статтю (реферат): «Линейная модель планирования оптимальной системы портфелей банка» з дисципліни «Моделювання банківської діяльності»
