Студентам ИТ / 3 ЛП_ИТ / ИТ_автоматизир / ИТ_автоматизир_Excel / 5_Опт_инв_портф
.pdfЛабораторная работа №5
Оптимизация инвестиционного портфеля
Пример. Имеется шесть проектов для потенциального инвестирования. На каждый проект инвестор планирует выделить 80000 р., 60000 р., 70000 р., 100000 р., 40000 р., 110000 р. Планируемые чистые современные стоимости проектов составляют 15000 р., 19000 р., 42000 р., 45000 р., 12000 р., 16000 р. соответственно. Требуется определить набор (портфель) проектов, при котором суммарные инвестиции I не превышают ограниченного бюджета инвестора в 250000 р.
Для решения задачи необходимо подготовить шаблон рабочего листа Excel, как показано в таблице 1.
Для решения задачи выберите в главном меню тему Сервис, опцию Поиск решения и заполните поля диалога появившегося «висящего» окна Поиск решения, как показано на рис. 1.
Обратите внимание на то, что в окне Ограничения: введены ограничения целочисленности на параметры xm, что соответствует инвестированию (xm = 1) или не инвестированию (xm = 0) проекта.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A |
|
|
B |
|
|
С |
|
|
D |
|
|
E |
|
|
F |
|
1 |
|
Отбор инвестиционных проектов в условиях ограниченного бюджета |
|
|||||||||||||||
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
Коэфф-ты |
|
|
Коэфф-нты |
|
|
|
|
|
|
|
|
|
|
|
|
Сп-ок |
|
|
NPVm |
|
|
Im |
|
|
|
|
|
Ц |
|
|
О |
|
|
|
проек |
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
|
Целевого |
|
|
крит-рия |
|
|
xm |
|
|
|
|
|
|
|
|
|
|
-тов: |
|
|
крит-рия |
|
|
огран-ния О |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
Ц |
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
|
A |
|
|
15 000р. |
|
|
80 000р. |
|
0 |
|
|
=B5*D5 |
|
|
=C5*D5 |
|
|
6 |
|
B |
|
|
19 000р. |
|
|
60 000р. |
|
0 |
|
|
=B6*D6 |
|
|
=C6*D6 |
|
|
7 |
|
C |
|
|
42 000р. |
|
|
70 000р. |
|
0 |
|
|
=B7*D7 |
|
|
=C7*D7 |
|
|
8 |
|
D |
|
|
45 000р. |
|
|
100 000р. |
|
0 |
|
|
=B8*D8 |
|
|
=C8*D8 |
|
|
9 |
|
E |
|
|
12 000р. |
|
|
40 000р. |
|
0 |
|
|
=B9*D9 |
|
|
=C9*D9 |
|
|
10 |
|
F |
|
|
16 500р. |
|
|
110 000р. |
|
0 |
|
|
=B10*D10 |
|
|
=106*D10 |
|
|
11 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
12 |
|
|
|
|
Бюджет = |
|
|
250 000р |
|
|
|
|
|
I = |
|
|
=CУММ(F5:F10) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
13 |
|
|
|
|
|
|
|
NPV = |
|
|
=CУММ(E5:E10) |
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Поиск решения |
|
|
? |
|
Установить целевую ячейку: |
$E$13 |
|
Выполнить |
|
|
|
|
|
|
Равной: |
максимальному значению |
значению: |
0 |
|
|
минимальному значению |
|
Закрыть |
|
Изменяя ячейки: |
|
|
|
|
$D$5:$D$10 |
|
Предположить |
|
|
Ограничения: |
|
|
Параметры |
|
|
|
|
|
|
$D$5:$D$10 <=1 |
|
Добавить |
|
|
$D$5:$D$10 = целое |
|
Изменить |
|
|
$D$5:$D$10 >= 0 |
|
|
||
|
|
|
||
$F$12 <= $C$12 |
|
Удалить |
Восстановить |
|
|
|
|
||
|
|
|
|
|
|
|
|
|
Справка |
|
Рис. 1. Окно Поиск Решения |
|
||
Полученное в результате решение показано в таблице 2. Согласно полученному решению, оптимальный портфель должен
состоять из проектов B, C и D. Суммарная величина NPV при этом составитNPV =106 000 р., а суммарная величина инвестиций I = 230 000 р., т.е. почти равна бюджету инвестора.
|
|
|
|
|
|
|
|
Таблица 2 |
|
|
|
|
|
|
|
|
|
|
A |
B |
С |
D |
|
E |
|
F |
5 |
A |
15 000р. |
80 000р. |
0 |
|
0,00р. |
|
0,00р. |
6 |
B |
19 000р. |
60 000р. |
1 |
|
19 000р. |
|
60 000р. |
7 |
C |
42 000р. |
70 000р. |
1 |
|
42 000р. |
|
70 000р. |
8 |
D |
45 000р. |
100 000р. |
1 |
|
45 000р. |
|
100 000р. |
9 |
E |
12 000р. |
40 000р. |
0 |
|
0,00р. |
|
0,00р. |
10 |
F |
16 500р. |
110 000р. |
0 |
|
0,00р. |
|
0,00р. |
11 |
|
|
|
|
|
|
|
|
12 |
|
Бюджет = |
250 000р. |
|
|
I = |
|
230 000р. |
|
|
|
|
|
|
|
|
|
13 |
|
|
NPV = |
|
106 000р. |
|
|
|
|
|
|
|
|
|
|
|
|
Сдайте работу преподавателю!