Лабораторная работа № 12. Нелинейное моделирование

Нелинейное моделирование

Цель работы: ознакомиться с методами решения прикладных задач с помощью построения нелинейных математических моделей в табличном процессоре Excel.

Задача: Инвестор хочет вложить денежные средства в ценные бумаги. На данный момент на российском финансовом рынке лидирующие позиции занимают следующие акционерные общества: ОАО «Газпром», ОАО «МТС» и ОАО «Сбербанк». После анализа инвестиционной привлекательности этих компаний биржевой маклер предоставил инвестору следующие данные о доходности ценных бумаг (рис. 12.1). Инвестору требуется составить портфель ценных бумаг с минимальным риском банкротства.

Рис. 12.1. Исходные данные.

Портфель ценных бумаг – это совокупность различных инвестиционных инструментов, которые собраны воедино для достижения конкретной инвестиционной цели. Мы рассмотрим рискованные ценные бумаги, каждая из которых характеризуется ожидаемой доходностью и средним квадратичным отклонением доходности, которое принято считать количественной характеристикой риска.

Решение: Минимизация риска.

На листе Excel формируем таблицу исходных данных. Заполняем таблицу решения как показано на рис. 12.2.

Рис. 12.2. Таблица решения задачи.

В ячейки D8:D10 вводим нули (т.к. количества ценных бумаг в портфеле пока неизвестны). В ячейку Е8 вводим ссылку: =D9, в ячейку F8 вводим: =D10.

Для вычисления вектора ковариации в ячейки D12:F12 вводим формулу: =МУМНОЖ(D8:F8;D3:F5) и нажимаем Ctrl+Shift+Enter, т.к. нам нужно заполнить весь массив данных.

Для вычисления значения целевой функции в ячейку D14 вводим формулу: =МУМНОЖ(D12:F12;D8:D10).

Для вычисления ограничений на доходность ценных бумаг в ячейку D16 вводим формулу: =B6-МУМНОЖ(D8:F8;B3:B5).

Для вычисления ограничений на доли ценных бумаг в ячейку D18 вводим формулу: =СУММ(D8:D10)-1.

Для вычисления общей доходности портфеля ценных бумаг в ячейку D20 вводим формулу: =МУМНОЖ(D8:F8;B3:B5).

Для вычисления среднего квадратичного отклонения доходности портфеля ценных бумаг (т.е. разброса вероятных значений от минимальной доходности до максимальной) в ячейку D22 вводим формулу: =КОРЕНЬ(D14).

Далее нужно поставить курсор в ячейку целевой функции D14 и вызвать процедуру «Поиск решения». Заполняем поля формы «Поиска решения» как показано на рис. 12.3.

Рис. 12.3. Поиск решения.

Результаты расчётов приведены на рис. 12.4.

Рис. 12.4. Результаты расчётов.

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

1) Акции ОАО «Сбербанк» составляют 49%;

2) Акции ОАО «МТС» составляют 27%;

3) Акции ОАО «Газпром» составляют 24%.

При данном распределении ценных бумаг в портфеле риск банкротства инвестора минимален и составляет 20,6% (которые относятся на влияние случайных факторов).