Постановка экономической задачи:

Задача 1.3. Оптимизация инвестиционного портфеля

Частный инвестор предполагает вложить 500 тыс. руб. в различные ценные бумаги (см. таблицу).

Вложение	Доход, %	Риск
Акции А	15	Высокий
Акции В	12	Средний
Акции С	9	Низкий
Долгосрочные облигации	11	-
Краткосрочные облигации	8	-
Срочный вклад	6	-

После консультаций со специалистами фондового рынка он отобрал 3 типа акций и 2 типа государственных облигаций. Часть денег предполагается положить на срочный вклад в банк.

Имея в виду качественные соображения диверсификации портфеля и неформализуемые личные предпочтения, инвестор выдвигает следующие требования к портфелю ценных бумаг:

Все 500 тыс. руб. должны быть инвестированы;
По крайней мере 100 тыс. руб. должны быть на срочном вкладе в банке;
По крайней мере 25% средств, инвестированных в акции, должны быть инвестированы в акции с низким риском;
В облигации нужно инвестировать по крайней мере столько же, сколько в акции;
Не более чем 125 тыс. руб. должно быть вложено в бумаг с доходом менее 10%.

I. Определить портфель бумаг инвестора, удовлетворяющий всем требованиям и

максимизирующий годовой доход. Какова величина этого дохода?

II. Если инвестор вносит дополнительные средства в портфель бумаг, сохраняя

сформулированные выше ограничения, как изменится ожидаемый годовой доход?

Зависит ли изменение ожидаемого годового дохода от величины дополнительного

инвестированных средств? Почему?

III. Ожидаемый годовой доход по той или иной бумаге (особенно по акциям) – это не

более чем оценка. Насколько оптимальный портфель и ожидаемая величина дохода

от портфеля выбранных бумаг чувствительны к этим оценкам? Какая именно

бумага портфеля наиболее сильно влияет на оценку суммарного ожидаемого дохода?

Экономико-математическая модель:

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. Введена зависимость для ограничений

1. Поиск решения (рис.6)

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

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

2. Назначить ячейку для целевой функции (рис.7).

(установить целевую ячейку, указать адрес изменяемых ячеек)

Ø Поместить курсор в строку «Установить целевую ячейку»

Ø Вводим адрес ячейки «$I$4»

Ø Вводим тип целевой функции. Целевая функция по условию задачи равна

максимальному значению

Ø Поместим курсор в строку «Изменяя ячейки»

Ø Вводим искомых переменных «$B$2:$G$2»

Рис.7. Ввод адресов исходных данных

3. Вводим ограничения (рис. 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. Типы отчётов

I. При решении задачи на компьютере с использованием программы «Поиск решения» пакета программы «Excel» получаем, что для того чтобы заказ от вложений денег был максимальный и составил 52,5 тыс. руб. необходимо вложить:

ü В акции А – 75 тыс. руб.

ü В акции В – 25 тыс. руб.

ü В долгосрочные облигации – 300 тыс. руб.

ü На срочный вклад – 100 тыс. руб.

II. При влиянии дополнительных средств в портфель бумаг с сохранением тех же ограничений годовой доход будет увеличиваться и с ростом дополнительных инвестиций он будет расти.

III. Так как, в условиях задачи, исходя из заданных ограничений инвестиций, все дополнительные средства будут вложены в долгосрочные облигации доход, по которым составляет 11%, то эта бумага портфеля наиболее сильно будет влиять на оценку суммарного ожидаемого дохода.

Microsoft Excel 11.0 Отчет по устойчивости
Рабочий лист: [СИМПЛ65.xls]Лист1
Отчет создан: 26.11.2006 17:01:17


Изменяемые ячейки
			Результ.	Нормир.	Целевой	Допустимое	Допустимое
	Ячейка	Имя	значение	стоимость	Коэффициент	Увеличение	Уменьшение
	$B$3	Значение Х1	75	0	0,15	1E+30	0,03
	$C$3	Значение Х2	0	-0,03	0,12	0,03	1E+30
	$D$3	Значение Х3	25	0	0,09	1E+30	0,1
	$E$3	Значение Х4	300	0	0,11	0,025	1E+30
	$F$3	Значение Х5	0	-0,13	0,08	0,13	1E+30
	$G$3	Значение Х6	100	0	0,06	0,15	1E+30

Ограничения
			Результ.	Теневая	Ограничение	Допустимое	Допустимое
	Ячейка	Имя	значение	Цена	Правая часть	Увеличение	Уменьшение
	$H$9	Левая часть	100	-0,15	100	25	28,57142857
	$H$8	Левая часть	500	0,11	500	1E+30	200
	$H$11	Левая часть	-200	0	0	1E+30	200
	$H$10	Левая часть	0	0,04	0	100	75
	$H$12	Левая часть	125	0,1	125	25	25

Microsoft Excel 11.0 Отчет по пределам
Рабочий лист: [СИМПЛ65.xls]Отчет по пределам 1
Отчет создан: 26.11.2006 17:01:17


		Целевое
	Ячейка	Имя	Значение
	$H$4	Коэфф. в ЦФ ЦФ	52,5


		Изменяемое		Нижний	Целевой	Верхний	Целевой
	Ячейка	Имя	Значение	предел	результат	предел	результат
	$B$3	Значение Х1	75	75	52,5	75	52,5
	$C$3	Значение Х2	0	0	52,5	0	52,5
	$D$3	Значение Х3	25	25	52,5	25	52,5
	$E$3	Значение Х4	300	300	52,5	300	52,5
	$F$3	Значение Х5	0	0	52,5	0	52,5
	$G$3	Значение Х6	100	100	52,5	100	52,5

Microsoft Excel 11.0 Отчет по результатам
Рабочий лист: [СИМПЛ65.xls]Лист1
Отчет создан: 26.11.2006 17:01:17


Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное значение	Результат
	$H$4	Коэфф. в ЦФ ЦФ	0	52,5


Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$3	Значение Х1	0	75
	$C$3	Значение Х2	0	0
	$D$3	Значение Х3	0	25
	$E$3	Значение Х4	0	300
	$F$3	Значение Х5	0	0
	$G$3	Значение Х6	0	100


Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$H$9	Левая часть	100	$H$9>=$J$9	связанное	0
	$H$8	Левая часть	500	$H$8=$J$8	не связан.	0
	$H$11	Левая часть	-200	$H$11<=$J$11	не связан.	200
	$H$10	Левая часть	0	$H$10<=$J$10	связанное	0
	$H$12	Левая часть	125	$H$12<=$J$12	связанное	0

Задача 2.3. Распределение самолётов по маршрутам

Постановка экономической задачи:

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

Ниже в таблицах приведены исходные данные.

Тип самолёта	Число самолётов	Месячный объём перевозок одним самолётом по авиалиниям
1	2	3	4
1	40	15	10	20	50
2	25	30	20	10	17
3	30	25	50	30	45

Тип самолёта	Эксплуатационные расходы на один рейс по данному маршруту, дол.
1	2	3	4
1	10	20	25	40
2	70	22	15	45
3	40	50	40	65

Необходимо так распределить самолёты по авиалиниям, чтобы суммарные эксплуатационные расходы были минимальны.

Экономико-математическая модель:

Необходимо найти такие значения переменных 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» ввести «В4:Е4»

Введение условия типов самолёта, т.е. (рис.4)

Bj = ∑ Xij,

где Bj – тип j-го самолёта

m - количество маршрутов

Для этого необходимо выполнить следующие операции:

Ø Курсор в ячейку «В5»

Ø Курсор в окно «Категория» на категорию «Математические»

Ø курсор в окно Функции на СУММ

Ø На экране появляется диалоговое окно СУММ

Ø В строку «Число 1» ввести «В2:В4»

Аналогичные действия выполняются для ячеек «В5, С5, D5, Е5»

Ø Курсор в ячейку «С5»

Ø Курсор в окно «Категория» на категорию «Математические»

Ø курсор в окно Функции на СУММ

Ø На экране появляется диалоговое окно СУММ

Ø В строку «Число 1» ввести «С2:С4»

Ø Курсор в ячейку «D5»

Ø Курсор в окно «Категория» на категорию «Математические»

Ø курсор в окно Функции на СУММ

Ø На экране появляется диалоговое окно СУММ

Ø В строку «Число 1» ввести «D2:D4»

Ø Курсор в ячейку «Е5»

Ø Курсор в окно «Категория» на категорию «Математические»

Ø курсор в окно Функции на СУММ

Ø На экране появляется диалоговое окно СУММ

Ø В строку «Число 1» ввести «Е2:Е4»

Таким образом, введены ограничения для всех типов и всех маршрутов

Рис.4. Ввод граничных условий

3. Ввод исходных данных (рис.5)

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

Рис.5. Ввод исходных данных и граничных условий

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

Для вычисления значения целевой функции, соответствующей минимальным суммарным эксплуатационным расходам, необходимо зарезервировать ячейку и ввести формулу для её вычисления:

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 на четвёртый маршрут.

Microsoft Excel 11.0 Отчет по результатам
Рабочий лист: [лабораторная по эмм № 2.xls]Лист1
Отчет создан: 27.11.2006 15:37:21


Целевая ячейка (Минимум)
	Ячейка	Имя	Исходное значение	Результат
	$B$12		442	2390


Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$2		1	30
	$C$2		1	0
	$D$2		1	0
	$E$2		1	10
	$B$3		1	0
	$C$3		1	0
	$D$3		1	25
	$E$3		1	0
	$B$4		1	0
	$C$4		1	4
	$D$4		1	23
	$E$4		1	3


Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$A$2	Матри перевозок (изменяемые ячейки)	40	$A$2=$A$9	не связан.	0
	$A$3	Матри перевозок (изменяемые ячейки)	25	$A$3=$A$10	не связан.	0
	$A$4	Матри перевозок (изменяемые ячейки)	30	$A$4=$A$11	не связан.	0
	$B$20	Исходные данные № 2	450	$B$20>=$B$8	не связан.	150
	$C$20	целевая функция	200	$C$20>=$C$8	связанное	0
	$D$20		940	$D$20>=$D$8	не связан.	40
	$E$20		635	$E$20>=$E$8	не связан.	35
	$B$2		30	$B$2=целое	связанное	0
	$C$2		0	$C$2=целое	связанное	0
	$D$2		0	$D$2=целое	связанное	0
	$E$2		10	$E$2=целое	связанное	0
	$B$3		0	$B$3=целое	связанное	0
	$C$3		0	$C$3=целое	связанное	0
	$D$3		25	$D$3=целое	связанное	0
	$E$3		0	$E$3=целое	связанное	0
	$B$4		0	$B$4=целое	связанное	0
	$C$4		4	$C$4=целое	связанное	0
	$D$4		23	$D$4=целое	связанное	0
	$E$4		3	$E$4=целое	связанное	0