Коэффициенты ковариации Vij, ( ; ) – функция КОВАР().

Вид листа электронной таблицы EXCEL после внесения исходных данных и расчета значений характеристик ценных бумаг в режиме просмотра формул представлен на рисунке 5.2.2.

Рис. 5.2.2. Фрагмент оформления условия задачи в режиме просмотра формул.

Далее следует внести модель Марковица из примера 5.2.4. путем выполнения следующей последовательности действий:

1) В диапазон ячеек B15: B17 вносим названия соответствующих переменных, а в ячейки С15, С16, С17 заносим 0 для определения первоначального решения модели.

2) В ячейку С20 заносим целевую функцию:

=66, 67*C15*C15+66, 67*C16*C16+16, 67*C17*C17+

(-133, 34)*C15*C16+(-33, 34)*C15*C17+33, 34*C16*C17

3) В ячейку B26 заносим левую часть первого ограничения (формулу расчета ожидаемой доходности портфеля ценных бумаг):

=20*C15+D6*C16+E6*C17

4) В ячейку D26 заносим требуемое значение ожидаемой доходности портфеля: 25

5) В ячейку B29 заносим левую часть второго ограничения (суммируем все доли распределяемого капитала инвестора): =СУММ(C15: C17)

6) В ячейку D29 заносим правую часть второго ограничения: 1

7) Выбрать ячейку целевой функции C20

8) Вызвать функцию Поиск решения, которая находится в пункте меню Сервис.

9) На экране появится диалоговое окно Поиск решения, в котором напротив надписи Установить целевую будет указана ссылка на ячейку C20

10) Напротив надписи Равной нужно выбрать минимальному значению

11) Далее следует щелкнуть мышкой на белом поле под надписью Изменяя ячейки и внести следующую ссылку: С15: С17

12) Затем следует перейти в белое поле под надписью Ограничения либо с помощью мышки, либо нажав дважды клавишу Tab

13) Теперь надо нажать кнопку Добавить, после чего появится новое диалоговое окно Добавление ограничения

14) В поле под надписью Ссылка на ячейку следует внести B26

15) В следующем белом поле вместо знака «< =» следует указать знак «=»

16) А в поле под надписью Ограничение надо внести =D26

17) Для завершения ввода первого ограничения нужно нажать кнопку Добавить

18) Диалоговое окно Добавление ограничения останется, но все поля примут свои первоначальные значения, после чего можно приступать к вводу второго ограничения

19) В поле под надписью Ссылка на ячейку следует внести B29

20) В следующем белом поле вместо знака «< =» следует указать знак «=»

21) А в поле под надписью Ограничение надо внести =D29

22) Для завершения ввода ограничений нужно нажать кнопку ОК

23) Теперь модель задачи внесена для решения (рис. 5.2.3).

Рис. 5.2.3. Окно Поиск решения с моделью задачи.

23) Перед решением модели необходимо проверить установленные параметры, вызвав окно Параметры поиска решений нажав кнопку Параметры

24) В появившемся окне Параметры поиска решений необходимо, чтобы не было отмечено поле Линейная модель, так как это показано овалом на рисунке 5.2.4.

Рис. 5.2.4. Окно Параметры поиска решения.

25) Далее можно находить решение модели, для этого надо нажать кнопку Выполнить

26) Если система ограничений и целевая функция построены правильно и модель имеет решение, то на экране появится окно Результаты поиска решения с сообщением «Решение найдено. Все ограничения и условия оптимальности выполнены». Далее следует нажать кнопку ОК. В противном случае надо еще раз проверить все внесенные формулы, значения, ограничения и параметры функции Поиск решения, а затем повторить процесс решения.

27) В случае нахождения оптимального решения можно выписывать решение и анализировать полученные результаты. В ячейке С15 находится величина доли первой ценной бумаги, в ячейке С16 – второй, а в ячейке С17 – третей. А в ячейке С20 находится минимальное значение риска портфеля ценных бумаг содержащего данные ценные бумаги в соответствующих пропорциях.

В случае решения модели Марковица с условием запрещения операций короткой продажи, следует учесть условие неотрицательности значений долей капитала инвестора. Данное условие можно установить в окне Параметры поиска решения пометить поле Неотрицательные значения.

На рис. 5.2.5 представлен вид листа электронной таблицы EXCEL в режиме просмотра формул с внесенной моделью Марковица для использования функции Поиск решения.

Рис. 5.2.5. Модель Марковица в режим просмотра с формулами