Роздрукувати сторінку

Главная \ Методичні вказівки \ Методичні вказівки \ 1021 Практична робота №4 Оптимізація портфелю цінних паперів засобами табличного процесора Excel

Практична робота №4 Оптимізація портфелю цінних паперів

« Назад

Практична робота №4

Тема: Оптимізація портфелю цінних паперів засобами табличного процесора Excel.

Мета: Засвоїти методику формування оптимальних портфелів цінних паперів. Отримати досвід оптимізації проектів в табличному процесорі Excel за допомогою програми Поиск решения.

Порядок виконання роботи

Завдання: виконати формування оптимального портфелю з максимізацією дохідності при обмеженому риску, який вимірюється дисперсією портфелю і мінімізацією ризику при заданій величині дохідності.

1. За допомогою табличного процесора Excel побудувати табличну модель. Вхідні дані для формування портфелю цінних паперів наведено в таблиці 4.1:

- В клітинку С6 увести дохідність без ризикових активів – 6%;

- В клітинку С7 увести дохідність ринку – 15%;

- В клітинку G6 увести значення дисперсії ринку 3%;

- Діапазон клітин А10:А13 заповнити назвами акцій компанії, діапазон клітин В10:В13 – даними по Бета акцій Bi, С10:С13 – даними по залишковій дисперсії акцій Vi;

- В клітини Е10:Е14 занести процентні долі кожного цінного паперу в складі портфелю. В клітині Е16 обчислюється сума долей паперів в портфелі. Вона повинна дорівнювати 100%.

Таблиця 4.1

	A	B	C	D	E	F	G
5
6	Дохідність без ризику		6%		Дисперсія ринку 3%
7	Дохідність ринку		15%
8
9		Бета	ЗалДисп		Доля
10	Акція А	0,8	0,04		20%
11	Акція В	1,00	0,20		20%
12	Акція С	1,80	0,12		20%
13	Акція D	2,20	0,40		20%
14	Казн. векселю	0,00	0,00		20%
15
16	Всього				100%

2. В діапазоні клітин F10:F14 розрахувати портфельні Бета для кожного паперу з врахуванням його долі в портфелі. Це добуток елементів векторів Бета і Доля: Bpi=Wi*Bi. Формули наведені і числові значення вхідних даних подано у таблиці 4.2.

3. Визначити в клітинці F16 Бета портфелю (Bp) як суму скалярного добутку даних векторів.

4. Обчислити Долю дисперсії кожного паперу в портфелі в діапазоні клітин G10:G14, як добуток квадратів долей паперів у портфелі і вхідних даних залишкової дисперсії Vi. Vpi=Wi2*Vi

5. Обчислити загальну портфельну дисперсію Vp в клітині G18. Дохідність портфелю – в клітинці Е18. Дисперсія і Дохідність є цільовими (критеріальними) клітинками у варіантах задач вибору найкращого (оптимального) портфелю цінних паперів.

Таблиця 4.2

	A	B	C	D	E	F
6	Дохідність без ризику		0,06		Дисперсія ринку
7	Дохідність ринку		0,15
8
9		Бета	Зал Дисп		Доля	Бета
10	Акція А	0,8	0,04		0,2	=E10*B10
11	Акція В	1	0,2		0,2	=E11*B11
12	Акція С	1,8	0,12		0,2	=E12*B12
13	Акція D	2,2	0,4		0,2	=E13*B13
14	Казн.векселя	0	0		0,2	=E14*B14
15
16				Всього:	=СУММ (F10:F14)	=СУММ( F10:F14)
17					Дохідність
18				Всього по портфелю:	=C6+(C7-C6)*F16
19
20	Макс.знач. дохідності			Мін.знач. дохідності
21	=МАКС($E$18)			=МИН($G$18)
22	=СЧЁТ ($E$10:$E$14)			=СЧЁТ ($E$10:$E$14)
23	=$E$10>=0			=$E$10>=0
24	=$E$11>=0			=$E$11>=0
25	=$E$12>=0			=$E$12>=0
26	=$E$13>=0			=$E$13>=0
27	=$E$14>=0			=$E$14>=0
28	=$E$16=1			=$E$16=1
29	=$G$18<=0,071			=$E$18>=0,164

Результати розрахунків наведені в таблиці 4.3.

Таблиця 4.3

	A	B	C	D	E	F	G
6	Дохідність без ризику		6%		Дисперсія ринку		3%
7	Дохідність ринку		15%
8
9		Бета	Зал Дисп		Доля	Бета	Дисперсія
10	Акція А	0,80	0,04		20%	0,160	0,002
11	Акція В	1,00	0,2		20%	0,200	0,008
12	Акція С	1,80	0,12		20%	0,360	0,005
13	Акція D	2,20	0,4		20%	0,440	0,016
14	Казн.векселя	0,00	0		20%	0,000	0,000
15
16				Всього:	100%	1,160	0,030
17					Дохідність		Дисперсія
18				Всього по портфелю:	16,4%		7,1%
19
20	Макс.знач. дохідності А21:А29			Мін.знач. дохідності D21:D29
21	0,1644			0,070768
22	5			5
23	истина			истина
24	истина			истина
25	истина			истина
26	истина			истина
27	истина			истина
28	истина			истина
29	истина			истина

6. Скласти оптимальні портфелі акцій вручну. Скопіювати таблицю 3 на новий лист.

7. Сформувати портфель з максимальною дохідністю і мінімальним ризиком: змінюючи питому вагу акцій в портфелі, інакше дані в діапазоні Доля, добитись максимальної дохідності портфелю в клітинці Е18 і одночасного мінімального ризику (дисперсії) в клітинці G18. Одночасно слідкувати щоб сума ваги портфелю в клітинці Е16 завжди дорівнювала 100%.

8. Сформувати портфель з максимальною дохідністю і обмеженим ризиком: задати довільне числове значення ризику портфеля (дисперсії), змінювати питому вагу акцій в портфелі, інакше дані в діапазоні Доля, добитися максимальної дохідності портфелю в клітинці Е18 і одночасно не перевищувати заданого значення ризику (дисперсії) в клітинці G18. Контролювати щоб сума ваги (долей) портфелю в клітинці Е16 завжди дорівнювала 100%.

9. Сформувати портфель з заданим значенням дохідності і мінімальним ризиком: задати довільне числове значення дохідності портфеля, змінювати питому вагу акцій в портфелі, інакше дані в діапазоні Доля, добитися мінімального значення ризику (дисперсії) в клітинці G18 і одночасно дохідності портфелю в клітинці Е18, яке не перевищує задане. Сума ваги портфелю в клітинці Е16 завжди повинна дорівнювати 100%.

10. Провести комп’ютерний пошук портфелю з максимальною дохідністю і обмеженим ризиком. Скопіювати таблицю 3 на новий лист.

11. Скласти оптимальний план за допомогою програми оптимізації, для чого виконати команду меню Поиск решения. У діалоговому вікні Поиск решения, в полі цільова клітинка, потрібно задати адреси діапазону Доля шуканої ваги активів в портфелі Е10:Е14. В полі Ограничения увести 3 рядки умов, які обмежують область допустимих рішень даної задачі:

- Перше обмеження забороняє від’ємні значення долей активів у портфелі: Е10:Е14>=0;

- Друге обмеження вимагає, щоб сума долей активів у портфелі складала 100%: Е16=1;

- Третя нерівність обмежує портфельний ризик на рівні 7,1%.

Діалогове вікно Поиск решения с координатно-математичною моделлю задачі вибору оптимального портфелю показано на рис. 4.1.

Рис. 4.1 - Діалогове вікно Поиск решения

Зведені параметри моделі подані в таблиці 4.4.

Таблиця 4.4.

Параметр задачі
Результат	Е18	Мета – отримання найбільшої дохідності
Змінні дані	Е10:Е14	Доля кожної акції
Обмеження	Е10:Е14>=0	Долі не повинні бути від’ємними
	Е16=1	Сума долей повинна дорівнювати 1
	Е18<=0,071	Дисперсія портфелю не повинна перевищувати 0,071
Вхідні дані	В10:В13	Бета для кожної акції
	С19:С13	Дисперсія для кожної акції

12. Провести комп’ютерний пошук портфелю з заданою дохідністю і мінімальним ризиком. В рядках 21:29 збережені дві моделі оптимізації портфелів: модель максимізації дохідності і модель мінімізації ризику.

13. Для рішення потрібно завантажити модель мінімізації і завантажити команду меню Данные - Поиск решения. У діалоговому вікні оптимізатора потрібно натиснути кнопку Загрузить/Сохранить. З’явиться вікно Загрузить или сохранить модель (рис. 4.2), де необхідно увести діапазон моделі мінімального ризику D21:D29 і натиснути кнопку Загрузить.

М1021, Рис. 4.2 - Вікно Загрузить или сохранить модель

Рис. 4.2 - Вікно Загрузить или сохранить модель

14. Далі з’явиться вікно для підтвердження завантаження нової моделі. Для підтвердження натиснути Заменить.

М1021, Рис. 4.3 - Вікно для підтвердження завантаження нової моделі

Рис. 4.3 - Вікно для підтвердження завантаження нової моделі

15. Перевірити модель у вікні Поиск решений. (рис.4.4).

Рис. 4.4 - Перевірка моделі у вікні Поиск решений

16. Для запуску оптимізатора у вікні Поиск решения потрібно натиснути кнопку Найти решение. По закінченню розрахунків з’являється діалогове вікно Результаты поиска решения (рис. 4.5).

М1021, Рис. 4.5 - Діалогове вікно Результаты поиска решения

Рис. 4.5 - Діалогове вікно Результаты поиска решения

17. У полі Отчеты задати тип звіту, який записується на окремий лист книги:

- звіт Результаты використовується для створення звіту, який складається з цільової клітини в списку впливових клітин моделі, їх вхідних і кінцевих значень, а також формул обмежень і додаткових відомостей про накладенні обмеження;

- звіт Устойчивость використовується для створення звіту, який містить відомості про чутливість рішення до малих змін в формулі моделі або в формулах обмежень. В звіт по нелінійним моделям включаються обмежені затрати, фіктивні ціни, а також діапазони обмежень;

- звіт Пределы використовується для створення звіту, який складається з цільової клітини і списку впливових клітин моделі, їх значень, а також нижніх і верхніх обмежень.

18. Вибрати ти звіту Результаты. Встановити прапорець Сохранить найденное значение і натисніть кнопку ОК, в результаті буде створено автоматично на новому листі від назвою Отчет по результатам як показано в таблиці 4.5.

Таблиця 4.5.

Microsoft Excel 10.0 Отчет по результатам
Рабочий лист: [Приклад1.xls]Лист1
Отчет создан: 16.03.2006 22:29:42


Целевая ячейка (Минимум)
	Ячейка	Имя	Исходное значение	Результат
	$G$18	Всього по портфелю Дисперсія	7,08%	6,23%


Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$E$10	Акція А Доля	20%	38%
	$E$11	Акція В Доля	20%	10%
	$E$12	Акція С Доля	20%	29%
	$E$13	Акція D Доля	20%	11%
	$E$14	Казн.векселю Доля	20%	13%


Ограничения
	Ячейка	Имя	Значение	Формула	Статус
	$E$16	Всього Доля	100%	$E$16=1	не связан.
	$E$18	Всього по портфелю Дохідність	16,40%	$E$18>=0.164	связанное
	$E$18	Всього по портфелю Дохідність	16,40%	$E$18<=0.164	связанное
	$E$10	Акція А Доля	38%	$E$10>=0	не связан.
	$E$11	Акція В Доля	10%	$E$11>=0	не связан.
	$E$12	Акція С Доля	29%	$E$12>=0	не связан.
	$E$13	Акція D Доля	11%	$E$13>=0	не связан.
	$E$14	Казн.векселю Доля	13%	$E$14>=0	не связан.

19. Зберегти результати на сервері у власній папці. Закрити програму.

20. Оформити звіт, зробити висновки і відповісти на контрольні питання

Контрольні питання

1. Перерахуйте фінансові активи.

2. Поясніть призначення елементи вікна Пошуку рішень.

3. Для чого слугує цільова комірка у вікні Пошуку рішень.

4. Як і для чого встановлюються параметри у вікні Пошуку рішень.

З повагою ІЦ "KURSOVIKS"!