Параметры условных активов (для учебного примера)

Название	Доходность, %	Срок выкупа, год	Надежность, баллы
А	5,5	2001	5
B	6,0	2005	4
C	8,0	2010	2
D	7,5	2002	3
E	5,5	2000	5
F	7,0	2003	4

3. Более половины всех средств должны быть вложены в долгосрочные активы (допустим, на рассматриваемый момент к таковым относятся активы со сроком погашения после 2004 года).

4. Доля активов, имеющих надежность менее чем 4 балла, не может превышать трети от суммарного объема.

Дадим теперь описание экономико-математической модели для данной ситуации.

В рассматриваемом примере в качестве переменных выступают объемы средств, вложенных в активы той или иной фирмы. Обозначим их как x_a, x_b, x_с, x_d, x_e, x_f. Тогда суммарная прибыль от размещенных активов, которую получит инвестор, может быть представлена в виде

P = 0,055 x_a+0,06 x_b+0,08 x_c+0,075 x_d+0,055 x_e+0,07 x_f.

На следующем этапе моделирования мы должны формально описать перечисленные выше ограничения 1 – 4 на структуру портфеля.

1. Ограничение на суммарный объем активов:

x_a+ x_b + x_c + x_d + x_e + x_f = 100000.

2. Ограничение на размер доли каждого актива:

x_a ≤ 25000, x_b ≤ 25000, x_c ≤ 25000

x_d ≤ 25000, x_e ≤ 25000, x_f ≤ 25000.

3. Ограничение, связанное с необходимостью вкладывать половину средств в долгосрочные активы:

x_b + x_c ≥ 50000.

4. Ограничение на долю ненадежных активов:

x_c + x_d ≤ 33333.

Наконец, система ограничений в соответствии с экономическим смыслом задачи должна быть дополнена условиями не отрицательности для искомых переменных:

x_a ≥ 0, x_b ≥ 0, x_c ≥ 0, x_d ≥ 0, x_e ≥ 0, x_f ≥ 0.

Перечисленные условия образуют математическую модель поведения инвестора. В рамках этой модели может быть поставлена задача поиска таких значений переменных x_a, x_b, x_c, x_d, x_e, x_f, при которых достигается наибольшее значение прибыли и одновременно выполняются ограничения на структуру портфеля активов.

Перейдем к решению сформулированной задачи с помощью инструментов, предоставляемых программным обеспечением MS Excel. Оно распадается на следующие шаги:

1. На выбранном рабочем листе задать ячейки, которые будут предназначены для сохранения переменных решаемой задачи (x_a, x_b, x_c, x_d, x_e, x_f), как это сделано на рис.34, где переменная x_a содержится в ячейке А2, x_b – в В2 и т.д.

Заметим, что хорошим стилем работы является использование смежных ячеек для хранения имён переменных (на рис.34 для этого служат ячейки с Al по F1). Очевидно, что ни в коем случае не следует путать ячейки с переменными с ячейками со смысловыми именами.

Рис. 34. Заполнение листа Excel перед вызовом Поиска решения

2. Задать ячейку, содержащую формулу целевой функции решаемой задачи. В нашем примере ячейка Н2 содержит формулу

=0,055 * А2 + 0,06 * B2 + 0,08 * С2 + 0,075 * D2 + 0,055 * Е2 + 0,07 * F2.

3. Заполнить ячейки для формул «сложных» ограничений (типа x_b + x_c < 50000. и т. п.). На рис.34 для этого использованы ячейки A4 – А18, а ячейки D4 – D18 содержат числовые значения ограничений. На рис.34 формулы в ячейках А4 – А18 показаны только в качестве иллюстрации. Они будут видны только в строке команд. В столбце С для иллюстрации показаны соответствующие знаки ограничений.

Интерфейс надстройки Поиск решения устроен таким образом, что для учета условия вида x_b + x_c ≤ 50000 нужно в некоторую вспомогательную ячейку ввести формулу с левой частью неравенства и уже для нее задать ограничение ≤ 50000.

4. Выполнить команду меню Сервис > Поиск решения....

Рис. 35. Вид листа перед входом в надстройку Поиск решения

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

5. Заполнить параметры диалогового окна Поиск решения: адрес ячейки целевой функции (Установить целевую ячейку), тип оптимизации (искать максимум или минимум), адреса ячеек с переменными (Изменяя ячейки).

6. Задать систему ограничений, для чего используется кнопка Добавить (группа Ограничения). По ее нажатию вызывается вспомогательное диалоговое окно, в поля которого вводятся адреса или значения, образующие выражение для условия, накладываемого на переменные решаемой задачи. Как видно, сформированную систему ограничений в дальнейшем можно редактировать. Для этого служат кнопки Изменить и Удалить.

Рис. 37. Заполнение диалогового окна Параметры поиска решения

7. Нажать клавишу Параметры и установить в диалоговом окне Линейная модель, Неотрицательные значения и ОК (рис.37).

Рис. 38. Вид рабочего листа с найденным решением

8. Нажать кнопку Выполнить, после чего будет осуществлена процедура поиска решения, по результатам которой выводится сообщение о найденном решении (или о невозможности его обнаружить). Как видно из рисунка 38, полученные результаты можно сохранить (кнопка ОК), изменив, таким образом, содержимое ячеек с переменными, можно от них отказаться (не сохранять), наконец, можно сформировать отчет с более подробной информацией о том, как проходил процесс поиска решения. Таким образом, мы получили, что при оптимальном распределении прибыль инвестора составит $6791,7.

Пример расчетов с использованием Таблиц подстановки

Зачастую при проведении финансово-экономических расчетов возникает необходимость провести вычисления по одним и тем же формулам, но для различных серий данных. Конечно, с данной проблемой можно справиться с помощью простого копирования формул, однако в Excel предусмотрен и более удобный способ ее решения с помощью так называемой таблицы подстановки. Ее идея состоит в связывании некоторой формулы с сериями значений, которые должны быть подставлены вместо некоторых переменных, входящих в данную формулу. Таблицы подстановки в Excel могут содержать одну или две подстановочных переменных, или, другими словами, быть векторными или матричными. Применение таблицы подстановки с двумя переменными продемонстрируем на простом, но наглядном примере – для построения таблицы умножения.

Рис. 39. Ввод расчетной формулы и выделение диапазона

для таблицы подстановки

Необходимо проделать следующие операции:

1. Определить две ячейки, содержащие переменные. В нашем случае (рис. 39) это ячейки А1 и А2. Начальное содержимое данных ячеек может быть произвольным, так как они нужны для того, чтобы определить переменные, от которых будет зависеть целевая формула.

2. Задать в «матричной» форме, как это показано на рис.39 целевую формулу, зависящую от ячеек, определенных на этапе 1 (для нашего примера – это формула = А1*А2 в ячейке В4), а также серии значений, предназначенных для подстановки вместо переменных. Серии значений должны располагаться в левой колонке (В) и верхней строке (4). При определении таблицы необходимо соблюдать очевидное правило – ячейки с переменными не должны попасть в ее внутреннюю область.

3. Выделить область таблицы, как это показано на рис.39.

4. Выполнить команду меню Данные > Таблица подстановки....

Рис.40. Задание соответствия между переменными и сериями

значений в столбцах и строках Таблицы подстановки

Рис.41. Результат заполнения Таблицы подстановки

5. Заполнить параметры в появившемся диалоговом окне (рис.40). Первое значение – Подставлять значения по столбцам в – задает адрес ячейки с той переменной, вместо которой в целевую формулу будут подставляться значения из крайней верхней строки таблицы подстановки. В нашем случае вместо переменной из ячейки А1 последовательно будут подставлены в формулу значения из интервала C4:L4. Аналогично, второе значение – Подставлять значения по строкам в – задает адрес ячейки той переменной, вместо которой в целевую формулу будут подставляться значения из крайнего левого столбца таблицы подстановки. В нашем примере вместо переменной из ячейки А2 в формулу будут подставлены значения из интервала В5:В14.

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

Результат заполнения таблицы подстановки показан на рис.41.