Задача 1.3. Оптимизация инвестиционного портфеля
Постановка экономической задачи:
Частный инвестор предполагает вложить 500 тыс. руб. в различные ценные бумаги (см. таблицу).

После консультаций со специалистами фондового рынка он отобрал 3 типа акций и 2 типа государственных облигаций. Часть денег предполагается положить на срочный вклад в банк.
Имея в виду качественные соображения диверсификации портфеля и неформализуемые личные предпочтения, инвестор выдвигает следующие требования к портфелю ценных бумаг:
Все 500 тыс. руб. должны быть инвестированы;
По крайней мере 100 тыс. руб. должны быть на срочном вкладе в банке;
По крайней мере 25% средств, инвестированных в акции, должны быть инвестированы в акции с низким риском;
В облигации нужно инвестировать по крайней мере столько же, сколько в акции;
Не более чем 125 тыс. руб. должно быть вложено в бумаг с доходом менее 10%.
Определить портфель бумаг инвестора, удовлетворяющий всем требованиям и
максимизирующий годовой доход. Какова величина этого дохода?
Если инвестор вносит дополнительные средства в портфель бумаг, сохраняя
сформулированные выше ограничения, как изменится ожидаемый годовой доход?
Зависит ли изменение ожидаемого годового дохода от величины дополнительного
инвестированных средств? Почему?
Ожидаемый годовой доход по той или иной бумаге (особенно по акциям) – это не
более чем оценка. Насколько оптимальный портфель и ожидаемая величина дохода
от портфеля выбранных бумаг чувствительны к этим оценкам? Какая именно
бумага портфеля наиболее сильно влияет на оценку суммарного ожидаемого дохода?






Экономико-математическая модель:
M1 – один щелчок левой кнопки мышки;
М2 – двойной щелчок левой кнопкой мыши.
Обозначим через:
Х1 – Акции А
Х2 – Акции В
Х3 – Акции С
Х4 – Долгосрочные облигации
Х5 – Краткосрочные облигации
Х6 – Срочный вклад
Надо найти такие значения переменных Xi >= 0, которые обеспечивают максимум целевой функции.
В результате целевая функция будет иметь вид:
0,15Х1 + 0,12Х2 + 0,09Х3 + 0,11Х4 + 0,08Х5 + 0,06Х6 ? max
Ограничения:
Х1 + Х2 + Х3 + Х4 + Х5 + Х6 = 500
Х6 >= 100
0, 25 (Х1 + Х2 + Х3) <= X3
X1 + X2 + X3 <= X4 + X5
X3 + X5 + X6 <= 125
Xi >=0
После простых преобразований система ограничений принимает вид:
Х1 + Х2 + Х3 + Х4 + Х5 + Х6 = 500
Х6 >= 100
X1 + X2 – 3X3 <= 0
X1 + X2 + X3 – X4 – X5 <= 0
X3 + X5 + X6 <= 125
Xi >= 0








Решение экономической задачи:
Приведённая ЭММ является моделью задачи линейного программирования
1. Создадим таблицу для ввода условий задачи (рас. 1)

Рис.1. Таблица для ввода условий задачи
2. Введём зависимость для целевой функции (рис.2).
Поместим курсор в ячейку I4
Курсор на кнопку «Мастер функций», расположенную на панели инструментов
М1. На экране появляется диалоговое окно Мастер функций шаг 1 из 2 (рис.2;3)

Рис.2. Мастер функций

Курсор в окно «Категория» на категорию «Математические». М1
Курсор в окно Функции на СУММПРОИЗВ
М1. На экране появляется диалоговое окно СУММПРОИЗВ
В строку «Массив 1» ввести B$2:G$2
В строку «Массив 2» ввести В4:G4

Рис.3. Аргументы функции

Кнопка «ОК». На экране: в ячейку I4 введена функция.(рис.4)

Рис.4. Введена зависимость для целевой функции
3. Введём зависимость для ограничений (рис.5).
Курсор в ячейку I4
На панели инструментов кнопка Копировать в буфер ? M1
Курсор в ячейку А8
На панели инструментов кнопка Вставить из буфера ? M1
Курсор в ячейку А9
На панели инструментов кнопка Вставить из буфера ? M1
Курсор в ячейку А10
На панели инструментов кнопка Вставить из буфера ? M1
Курсор в ячейку А11
На панели инструментов кнопка Вставить из буфера ? M1
Курсор в ячейку А12

Рис.5. Введена зависимость для ограничений

Поиск решения (рис.6)
В строке «Меню» указатель мыши поместить на имя «Сервис» ? M1. В развёрнутом меню выбрать команду «Поиск решения» ? M1. Появится диалоговое окно.

Рис.6. Меню «Поиск решения»

Назначить ячейку для целевой функции (рис.7).
(установить целевую ячейку, указать адрес изменяемых ячеек)
Поместить курсор в строку «Установить целевую ячейку»
Вводим адрес ячейки «$I$4»
Вводим тип целевой функции. Целевая функция по условию задачи равна
максимальному значению
Поместим курсор в строку «Изменяя ячейки»
Вводим искомых переменных «$B$2:$G$2»

Рис.7. Ввод адресов исходных данных
Вводим ограничения (рис. 8;9)
Поместим указатель мыши на кнопку «Добавить» ? M1. Появляется диалоговое окно «Добавление ограничения»
В строке «Ссылка на ячейку» вводим адрес «$A$8»
Знак ограничения «=»
В строке «Ограничение» вводим «$I$8»

Рис.8. Добавление ограничения
Курсор мыши наводим на кнопку «Добавить» ? M1. На экране вновь появляется
диалоговое окно «Добавление ограничения»
Вводим остальные ограничения «$A$9»
Знак ограничения «>=»
В строке «Ограничение» вводим «$I$9»
Курсор мыши наводим на кнопку «Добавить» ? M1. На экране вновь появляется
диалоговое окно «Добавление ограничения»
Вводим остальные ограничения «$A$10»
Знак ограничения «<=»
В строке «Ограничение» вводим «$I$10»
Курсор мыши наводим на кнопку «Добавить» ? M1. На экране вновь появляется
диалоговое окно «Добавление ограничения»
Вводим остальные ограничения «$A$11»
Знак ограничения «<=»
В строке «Ограничение» вводим «$I$11»
Курсор мыши наводим на кнопку «Добавить» ? M1. На экране вновь появляется
диалоговое окно «Добавление ограничения»
Вводим остальные ограничения «$A$12»
Знак ограничения «<=»
В строке «Ограничение» вводим «$I$12»
После введения последнего ограничения кнопка «ОК». На экране появляется
диалоговое окно «Поиск решения с введёнными условиями»

Рис.9. Введены все условия задачи
4. Вводим параметры для решения задачи (рис.10;11)
В диалоговом окне направляем указатель мышки на кнопку «Параметры».
На экране появится диалоговое окно «Параметры поиска решения»
Установим флажки в окнах «Линейная модель» (это обеспечит применение симплекс-метода) и «Неотрицательные значения»

Рис.10. Ввод параметров

Поместим указатель мыши на кнопку «ОК». На экране появится диалоговое окно «Поиск решения»
Поместить указатель мышки на кнопку «Выполнить»
Появится диалоговое окно «Результаты поиска решения» и исходная таблица с заполненными ячейками «B2:G2» для значений Xi и ячейка «I4» с максимальным значением целевой функции

Рис.11. Результаты поиска решения и исходная таблица с заполненными ячейками
5. Типы отчётов (рис.12).
В диалоговом окне «Результаты поиска решения» направляем указатель мыши на окно «Тип отчёта»
Выбираем тип отчёта (Результаты, Устойчивость и Пределы)
Нажимаем «ОК»

Рис.12. Типы отчётов
При решении задачи на компьютере с использованием программы «Поиск решения» пакета программы «Excel» получаем, что для того чтобы заказ от вложений денег был максимальный и составил 52,5 тыс. руб. необходимо вложить:
В акции А – 75 тыс. руб.
В акции В – 25 тыс. руб.
В долгосрочные облигации – 300 тыс. руб.
На срочный вклад – 100 тыс. руб.
При влиянии дополнительных средств в портфель бумаг с сохранением тех же ограничений годовой доход будет увеличиваться и с ростом дополнительных инвестиций он будет расти.
Так как, в условиях задачи, исходя из заданных ограничений инвестиций, все дополнительные средства будут вложены в долгосрочные облигации доход, по которым составляет 11%, то эта бумага портфеля наиболее сильно будет влиять на оценку суммарного ожидаемого дохода.

































Задача 2.3. Распределение самолётов по маршрутам
Постановка экономической задачи:
Требуется распределить самолёты трёх типов по авиалиниям так, чтобы при минимальных эксплуатационных расходах перевезти по каждой из четырёх авиалиний соответственно не менее 300, 200, 900 и 600 ед. груза.
Ниже в таблицах приведены исходные данные.
Необходимо так распределить самолёты по авиалиниям, чтобы суммарные эксплуатационные расходы были минимальны.









Экономико-математическая модель:
Необходимо найти такие значения переменных Xij >= 0, которые минимизируют целевую функцию (суммарные расходы).
Пусть Xij – количество самолётов j – типа выделяемых по I – маршруту, Cij – расходы на один рейс самолёта j – типа по i– маршруту, Dij – месячный объём перевозок самолёта ji – типа по I - маршруту.
При заданных ограничениях:
а) по числу имеющихся самолётов.
Х11 + Х12 + Х13 + Х14 = 40
Х21 + Х22 + Х23 + Х24 = 25
Х31 + Х32 + Х33 + Х34 = 30
б) по минимальному объёму перевозок
X11d11 + X21d21 + X31d31 >= 300
X12d21 + X22d22 + X32d32 >= 200
X13d13 + X23d23 + X33d33 >= 900
X14d14 + X24d24 + X34d34 >= 600
Решение:
1. Создание формы для решения задачи (рис.1) предполагает создание матрицы перевозок.
Для этого необходимо выполнить резервирование изменяемых ячеек «В2:Е4» вводится «1».
Таким образом, резервируется место, где после решения задачи будет находиться распределение самолётов по авиалиниям при минимальных эксплуатационных расходах.

Рис. 1. Форма для решения задачи
2. Ввод граничных условий
Введение условий маршрутов самолёта, т.е.
Ai = ? Xij,
Где Ai - маршрут i-го самолёта;
Xij – количество самолётов i – типа выделяемых по j – маршруту;
n – количество типов
Для этого необходимо выполнить следующие операции:
Курсор в ячейку «А2»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2 (рис.2)

Рис.2. Мастер функций
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «В2:Е2» (рис.3)

Рис.3. Аргументы функции
Аналогичные действия выполняются для ячеек «А3, А4» (рис.4)
Курсор в ячейку «А3»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «В3:Е3»
Курсор в ячейку «А4»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «В4:Е4»
Введение условия типов самолёта, т.е. (рис.4)
Bj = ? Xij,
где Bj – тип j-го самолёта
m - количество маршрутов
Для этого необходимо выполнить следующие операции:
Курсор в ячейку «В5»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «В2:В4»
Аналогичные действия выполняются для ячеек «В5, С5, D5, Е5»
Курсор в ячейку «С5»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «С2:С4»
Курсор в ячейку «D5»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «D2:D4»
Курсор в ячейку «Е5»
Курсор на кнопку «Мастер функций», расположенную на панели инструментов. На экране появиться диалоговое окно Мастер функций шаг 1из2
Курсор в окно «Категория» на категорию «Математические»
курсор в окно Функции на СУММ
На экране появляется диалоговое окно СУММ
В строку «Число 1» ввести «Е2:Е4»
Таким образом, введены ограничения для всех типов и всех маршрутов

Рис.4. Ввод граничных условий
3. Ввод исходных данных (рис.5)
В конкретном примере осуществляется распределение числа самолётов трёх типов по авиалиниям (ячейки «А9:А11»), перевоза по каждой из четырёх авиалиний (ячейки «В8:Е8»), эксплуатационные расходы на один рейс по данному маршруту. дол. ( блок «В9:Е11»). А также Месячный объём перевозок одним самолётом по авиалиниям (блок «В17:Е19»)

Рис.5. Ввод исходных данных и граничных условий
4. Назначение целевой функции
Для вычисления значения целевой функции, соответствующей минимальным суммарным эксплуатационным расходам, необходимо зарезервировать ячейку и ввести формулу для её вычисления:
Пусть Xij – количество самолётов j – типа выделяемых по I – маршруту, Cij – расходы на один рейс самолёта j – типа по i– маршруту, Dij – месячный объём перевозок самолёта ji – типа по I - маршруту.
F = ? ? Xij * Cij
Для этого:
Поместим курсор в ячейку В12(рис.7)
Курсор на кнопку «Мастер функций», расположенную на панели инструментов.
На экране появляется диалоговое окно Мастер функций шаг 1 из 2

Рис.7. Мастер функций
Курсор в окно «Категория» на категорию «Математические».
Курсор в окно Функции на СУММПРОИЗВ
На экране появляется диалоговое окно СУММПРОИЗВ (рис.8)
В строку «Массив 1» ввести «В9:Е11»
В строку «Массив 2» ввести «В2:Е4»

Рис.8. Аргументы функции
«ОК» подтверждение окончания ввода адресов массивов.
В поле ячейки «В12» появится некоторое числовое значение, равное произведению единичных распределений самолётов на эксплуатационные расходы на один рейс по данному маршруту. дол. (число 442 в данной задаче). (рис.9).

Рис.9. Назначение целевой функции

1. Поиск решения (рис.10).
В строке «Меню» указатель мыши поместить на имя «Сервис». В развёрнутом меню выбрать команду «Поиск решения». Появится диалоговое окно.

Рис.10. Меню «Поиск решения»
2. Назначить ячейку для целевой функции (рис.11)
(установить целевую ячейку, указать адрес изменяемых ячеек)
Поместить курсор в строку «Установить целевую ячейку»
Вводим адрес ячейки «$B$12»
Вводим тип целевой функции. Целевая функция по условию задачи равна минимуму значению
Поместить курсор в строку «Изменяя ячейки»
Вводим искомых переменных «$B$2:$E$4»

Рис.11. Ввод адресов исходных данных
3. Вводим ограничения (рис.12;13)
Поместим указатель мыши на кнопку «Добавить». Появляется диалоговое окно «Добавление ограничения»
В строке «Ссылка на ячейку» вводим адрес «$A$2:$A$4»
Знак ограничения «=»
В строке «Ограничение» вводим «$A$9:$A$11»

Рис.12. Добавление ограничения
Курсор мышки наводим на кнопку «Добавить». На экране вновь появляется диалоговое окно «Добавление ограничения»
Вводим остальные ограничения «$B$2:$E$4»
Знак ограничения «цел»
В строке «Ограничение» автоматически вводится «целое»
Курсор мышки наводим на кнопку «Добавить». На экране вновь появляется диалоговое окно «Добавление ограничения»
Вводим остальные ограничения «$B$8:$E$8»
Знак ограничения «<=»
В строке «Ограничение» автоматически вводится «$B$20:$E$20»
После введения последнего ограничения кнопка «ОК». На экране появляется диалоговое окно «Поиск решения» с введёнными условиями.

Рис.13. Введены все условия задачи
4. Вводим параметры для решения задачи (рис.14;15)
В диалоговом окне направляем указатель мыши на кнопку «Параметры». На экране появится диалоговое окно «Параметры поиска решения».
Установим флажки в окнах «Линейная модель» (это обеспечит применение симплекс-метода) и «Неотрицательные значения»


Рис.14. Ввод параметров
Поместим указатель мыши на кнопку «ОК». На экране появится диалоговое окно «Поиск решения»
Поместить указатель мышки на кнопку «Выполнить»
Появится диалоговое окно «Результаты поиска решения» и исходная таблица с заполненными и изменёнными ячейками.

Рис.15.Результаты поиска решения и исходная таблица с заполненными ячейками
5. Типы отчётов (рис.16)
В диалоговом окне «Результаты поиска решения» направляем указатель мыши на окно «Тип отчёта»
Выбираем тип отчёта (Результаты, Устойчивость и Пределы)
Нажимаем «ОК»

Рис.16.Типы отчётов

Для того чтобы при заданных условиях суммарные эксплуатационные расходы были минимальными и составили 2390 д.ед. самолёты надо распределить следующим образом:
а) из 40 самолётов I типа выделить
30 на первый маршрут
10 на четвёртый маршрут
б) все 25 самолётов II типа на третий маршрут
в) из 30 самолётов III типа выделить
4 на второй маршрут
23 на третий маршрут
3 на четвёртый маршрут.