Методы вычислений и моделирование в экономике (стр. 5 )

Рис. 4. Ввод исходных данных для лабораторной работы № 2

2. Расчеты:

2.1. Расчет ожидаемой доходности портфеля:

В верхнюю ячейку справа от таблицы «Портфель» (ячейку F6) ввести формулу (функцию найти в списке функций):

=СУММПРОИЗВ

Рис. 5. Диалоговое окно функции «СУММПРОИЗВ»

В открывшемся диалоговом окне функции «СУММПРОИЗВ»:

- поставить курсор в поле «Массив 1» и выделить диапазон ячеек «Ожидаемые доходности» (только значения!) из таблицы 1;

- поставить курсор в поле «Массив 2» и выделить диапазон ячеек «Доля» из таблицы 2;

- нажать «ОК».

Таким образом, в ячейке F6 будет находиться формула:

=СУММПРОИЗВ(В3:Е3;В7:Е7).

Значение ее будет равняться нулю, поскольку пока неизвестны доли портфеля.

2.2. Расчет риска портфеля:

2.2.1. Вычислить промежуточную строку, которую можно разместить правее последнего столбца таблицы 2:

Первый элемент промежуточной строки (ячейка G6) рассчитывается с помощью функции СУММПРОИЗВ. В поле «Массив 1» окна функции указать диапазон ячеек «Доля» из таблицы 2; в поле «Массив 2» внести первую строку таблицы 3.

Второй элемент промежуточной строки (ячейка H6): выбрать также функцию СУММПРОИЗВ; в поле «Массив 1» указать диапазон ячеек «Доля» из таблицы 2; в поле «Массив 2» внести вторую строку таблицы 3.

Аналогично рассчитать все 4 элемента промежуточной строки.

2.2.2. Вычислить риск портфеля:

Во вторую ячейку справа от таблицы 2 (ячейку F7) внести формулу, выбрав функцию СУММПРОИЗВ. В диалоговом окне функции в поле «Массив 1» ввести диапазон ячеек «Доля» из таблицы 2; в поле «Массив 2» ввести промежуточную строку, которая была вычислена в п. 2.2.1.

2.3. В третью ячейку справа от таблицы 2 (ячейку F8) ввести формулу, выбрав функцию СУММ: в диалоговом окне функции в поле «Число 1» указать диапазон «Доля» из таблицы 2.

Формулы для расчетов приведены на рисунке 6.

Рис. 6. Пример расчетов лабораторной работы № 2

3. Вычисление оптимального портфеля:

3.1. Выбрать наибольший элемент из второй строки таблицы 1 с помощью функции «Максимум» (МАКС). В окне функции указать диапазон «Ожидаемые доходности» из таблицы 1.

3.2. Выбрать наименьший элемент из второй строки таблицы 1 с помощью функции «Минимум» (МИН).

3.3. Вычисление шага:

Из максимального значения вычесть минимальное и разделить на число вариантов для значения , равное 10.

3.4. Составить диапазон значений «TAB»: от наименьшего значения ожидаемой доходности до наибольшего. В каждой ячейке диапазона «ТАВ» доходность будет изменяться на величину шага (ссылка на шаг должна быть абсолютной).

Рис. 7. Вычисление диапазона «ТАВ»

4. Решение задачи:

Войти в пункт меню Сервис / Поиск решения. Открывшееся окно заполнить следующим образом:

Установить целевую ячейку: поставить ссылку на ячейку, в которой находится риск портфеля;

Равной: минимальному значению;

Изменяя ячейки: поставить курсор в это поле и указать диапазон «Доля» из таблицы 2;

Ограничения: поместить курсор в это поле и нажать кнопку Добавить. Откроется окно (рис. 8):

Рис. 8. Окно «Добавление ограничения»

В первом поле Ссылка на ячейку указать диапазон «Доля» из таблицы 2;

Во втором поле выбрать знак ;

В третьем поле Ограничение поставить 0.

Нажать кнопку Добавить. Опять появится такое же окно:

В первом поле Ссылка на ячейку указать ссылку на ячейку, где находится сумма долей портфеля;

Во втором поле выбрать знак = ;

В третьем поле Ограничение поставить 1.

Нажать кнопку Добавить. Внести третье ограничение:

В первом поле Ссылка на ячейку указать ссылку на ячейку, где находится рассчитанная доходность портфеля;

Во втором поле выбрать знак ;

В третьем поле Ограничение поставить ссылку на первую ячейку диапазона «ТАВ».

Нажать кнопку ОК.

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

Рис. 9. Заполненное окно средства Поиск решения

5. Занести доли портфеля, ожидаемую доходность портфеля и риск портфеля в таблицу «Результаты вычислений» (рис. 10).

Рис. 10. Результирующая таблица

6. Команду Поиск решения выполнить 10 раз и каждый раз изменить только одно последнее ограничение (в окне Поиск решения оно указано вторым), перебрав в его правой части последовательно все ячейки диапазона «ТАВ»: выделить это ограничение, нажать кнопку Изменить и поменять содержимое третьего поля.

Результаты поместить в таблицу «Результаты вычислений», которая при завершении лабораторной работы будет содержать 10 строк.

Варианты

Вариант № 1	Вариант № 2
R=(16,2; 24,6; 22,8; 18,2) Ковариационная матрица:	R=(26; 22,5; 15; 13,2) Ковариационная матрица:
Вариант № 3	Вариант № 4
R=(15,7; 17,1; 16; 27) Ковариационная матрица:	R=(17; 18,2; 20; 14,5) Ковариационная матрица:
Вариант № 5	Вариант № 6
R=(11,3; 19; 32; 28,9) Ковариационная матрица:	R=(20,1; 24; 26,2; 15,7) Ковариационная матрица:
Вариант № 7	Вариант № 8
R=(16,2; 24,6; 22,8; 18,2) Ковариационная матрица:	R=(26; 22,5; 15; 13,2) Ковариационная матрица:
Вариант № 9	Вариант № 10
R=(15,7; 17,1; 16; 27) Ковариационная матрица:	R=(17; 18,2; 20; 14,5) Ковариационная матрица:
Вариант № 11	Вариант № 12
R=(11,3; 19; 32; 28,9) Ковариационная матрица:	R=(20,1; 24; 26,2; 15,7) Ковариационная матрица:
Вариант № 13	Вариант № 14
R=(16,2; 24,6; 22,8; 18,2) Ковариационная матрица:	R=(26; 22,5; 15; 13,2) Ковариационная матрица:
Вариант № 15	Вариант № 16
R=(15,7; 17,1; 16; 27) Ковариационная матрица:	R=(17; 18,2; 20; 14,5) Ковариационная матрица:
Вариант № 17	Вариант № 18
R=(11,3; 19; 32; 28,9) Ковариационная матрица:	R=(20,1; 24; 26,2; 15,7) Ковариационная матрица:
Вариант № 19	Вариант № 20
R=(16,2; 24,6; 22,8; 18,2) Ковариационная матрица:	R=(26; 22,5; 15; 13,2) Ковариационная матрица:
Вариант № 21	Вариант № 22
R=(15,7; 17,1; 16; 27) Ковариационная матрица:	R=(17; 18,2; 20; 14,5) Ковариационная матрица:
Вариант № 23	Вариант № 24
R=(11,3; 19; 32; 28,9) Ковариационная матрица:	R=(20,1; 24; 26,2; 15,7) Ковариационная матрица:
Вариант № 25	Вариант № 26
R=(16,2; 24,6; 22,8; 18,2) Ковариационная матрица:	R=(26; 22,5; 15; 13,2) Ковариационная матрица:
Вариант № 27	Вариант № 28
R=(15,7; 17,1; 16; 27) Ковариационная матрица:	R=(17; 18,2; 20; 14,5) Ковариационная матрица:
Вариант № 29	Вариант № 30
R=(11,3; 19; 32; 28,9) Ковариационная матрица:	R=(20,1; 24; 26,2; 15,7) Ковариационная матрица:

3. Задача планирования производства