Задача оптимизации финансовых инвестиционных ресурсов

Имеются два типа инвестиционных портфелей I и II, исходные данные и обозначения для постановки задачи приведены в таблице 3.4 (цифры условные). Необходимо определить долю каждого в общем объеме инвестиций таким образом, чтобы инвестиционный риск был минимальным.

Таблица 3.4 - Структура инвестиционных портфелей

Показатель	Обозначение	Значение (в долях)
Уровень средней доходности инвестиционных портфелей	Р₀	0,045
Математическое ожидание доходности инвестиционного портфеля I	ξ₁	0,037
Математическое ожидание доходности инвестиционного портфеля II	ξ₂	0,068
Ковариация доходности инвестиционных портфелей	Ω₁₂	0,00623

Обозначим x₁, x₂ – доля инвестиционных портфелей соответственно I и II.

Причем (сумма долей инвестиционных портфелей должна составлять 1). Общий доход инвестиционных портфелей не должен превышать уровень средней доходности инвестиционных портфелей:

Переменные неотрицательные x₁³0, x₂³0.

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

Здесь ковариация представляет собой статистическую характеристику, иллюстрирующую меру сходства (или различий) двух инвестиционных ресурсов в динамике, амплитуде и направлении изменений. С помощью данного показателя оценивается уровень инвестиционного риска, чем меньше значение показателя ковариации, тем меньше уровень инвестиционного риска.

В качестве показателя уровня риска может быть использован показатель дисперсии. Дисперсия характеризует средний квадрат отклонений значений показателя от его среднего значения (или меру рассеивания).

Также уровень инвестиционного риска можно оценить с помощью показателя среднего квадратического (стандартного) отклонения. Данный показатель представляет собой положительное значение корня квадратного из дисперсии.

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

Математическая модель задачи оптимизации финансовых инвестиционных ресурсов в общей постановке примет вид. Найти доли инвестиционных портфелей x_i удовлетворяющие ограничениям:

и условию

при котором функция

принимает минимальное значение.

Здесь приняты следующие обозначения:

- доля i – го финансового инструмента,

- показатель дохода i – го финансового инструмента,

- средняя норма доходности инвестиционных ресурсов,

- инвестиционный риск i – го финансового инструмента.

3.2 Реализация задач планирования инвестиционных процессов финансовых организаций средствами Microsoft Excel

Для реализации построенных оптимизационных моделей инвестиционных процессов финансовых организаций в Microsoft Excel существует надстройка «Поиск решения».

«Поиск решения» доставляет не заранее известный конкретный результат для целевой функции, а отыскивает оптимальное (минимальное или максимальное) из возможных решение. Для сложных задач «Поиск решения» может генерировать множество различных решений. Шаблон задач планирования инвестиционных процессов, для решения которых можно воспользоваться надстройкой, должен иметь ряд общих свойств:

1. Существует единственная целевая ячейка, содержащая формулу.

2. Формула в целевой ячейке содержит ссылки (прямые или косвенные) на ряд изменяемых ячеек.

3. Может быть задано некоторое количество ограничений.

Приведем решение задачи оптимизации распределения средств между инвестиционными объектами. Рассмотри первый пример.

Первым действием необходимо ввести исходные данные. Введем на рабочий лист исходные данные как это показано на рис.3.1.

Рисунок 3.1- Шаблон решения задачи распределения инвестиционных средств

Вводим в диапазон С4:F6 коэффициенты инвестиционных затрат, в диапазон С3:F3 вводим инвестиционный спрос, в диапазон В4:В6 вводим мощности инвесторов.

Для формирования шаблона решения задачи необходимо ввести следующие расчетные формулы.

Вводим в ячейку G9 формулу =СУММ(C9:F9), в ячейки G10:G11 копируем данную формулу методом протягивания (таким образом сформировано ограничение по мощностям инвесторов).

Вводим в ячейку С12 формулу =СУММ(С9:С11), в ячейки D12:F12 копируем данную формулу методом протягивания (таким образом сформировано ограничение по инвестиционному спросу).

Вводим в ячейку С14 формулу для расчета значения целевой функции (=СУММПРОИЗВ(C4:F6;C9:F11)).

Для ввода формулы необходимо выполнить команды: Вставка/Функция/Математические/СУММПРОИЗВ. После чего заполнить окно диалога (рис. 3.2) по образцу.

Рисунок 3.2 - Окно диалога формирования целевой функции

Далее обращаемся к надстройке Поиск решения с помощью команды меню Сервис. Но может быть так, что команда Поиск решения в этом меню отсутствует. Тогда необходимо выполнить следующую команду: Сервис / Надстройки, напротив Поиск решения поставить галочку. В результате произойдет активация команды Поиск решения.

Заполняем окно диалога как показано на рис. 3.3.

Рисунок 3.3 - Окно диалога «Поиск решения» задачи распределения инвестиционных средств

Устанавливаем целевую ячейку С14 равной минимальному значению. Изменяя ячейки C9:F11. Далее преступаем к вводу ограничений. Нажимаем кнопку Добавить и вводим ограничения как показано на рис. 3.4 – 3.8. Ввод следующего ограничения осуществляется с помощью кнопки Добавить.

Рисунок3.4- Добавление ограничения

Рисунок 3.5 - Добавление ограничения

Рисунок 3.6 - Добавление ограничения

Рисунок 3.7 - Добавление ограничения

Рисунок 3.8 - Добавление ограничения

Оптимальное решение получено после нажатия кнопки Выполнить. На рис. 3.9 в диапазоне C9:F11 указано оптимальное распределение инвестиционных вложений для каждой пары «инвестор – инвестиционный объект».

Рисунок3.9- Оптимальное распределение инвестиционных вложений

Рассмотри второй пример. Введем на рабочий лист исходные данные как это показано на рис.3.10.

Рисунок 3.10- Шаблон решения задачи распределения вложений в акции

Вводим в диапазон С4:F8 коэффициенты прибыли, в диапазон С3:F3 вводим инвестиционный спрос на акции, в диапазон В4:В8 вводим мощности инвесторов.

Здесь добавлена фиктивная мощность 5-го инвестора в виде 5-й строки с нулевыми коэффициентами прибыли. Таким образом открытая задача приведена к закрытой.

Для формирования шаблона решения задачи необходимо ввести следующие расчетные формулы.

Вводим в ячейку G11 формулу =СУММ(C11:F11), в ячейки G12:G15 копируем данную формулу методом протягивания (таким образом сформировано ограничение по мощностям инвесторов).

Вводим в ячейку С16 формулу =СУММ(С11:С15), в ячейки D16:F16 копируем данную формулу методом протягивания (таким образом сформировано ограничение по инвестиционному спросу на акции).

Вводим в ячейку С17 формулу для расчета значения целевой функции (=СУММПРОИЗВ(C4:F8;C11:F15)). Вызов функции осуществляется как и в предыдущем примере.

Затем вызываем надстройку «Поиск решения». И заполняем окно диалога как показано на рис. 3.11.

Рисунок 3.11 Окно диалога «Поиск решения» задачи распределения вложений инвестиционных средств в акции

Устанавливаем целевую ячейку С17 равной максимальному значению. Изменяя ячейки C11:F15.

Далее преступаем к вводу ограничений. Нажимаем кнопку Добавить и вводим ограничения как и в первом примере.

Оптимальное решение получено после нажатия кнопки Выполнить. На рис. 3.12 в диапазоне C11:F15 указано оптимальное распределение инвестиционных вложений в акции для каждой пары «инвестор – пакет акций».

Рисунок 3.12 - Оптимальное распределение инвестиционных вложений в акции

Рассмотрим решение задачи оптимизации материальных инвестиционных ресурсов.

Введем на рабочий лист исходные данные как это показано на рис.1. В ячейки А1 и А2 вводим наименования переменных, в ячейки А3 – А6 вводим ограничения задачи начиная со знака равно, в ячейку А7 вводим целевую функцию начиная со знака равно.

Перейдем теперь к формулировании задачи для надстройки «Поиск решения». При этом используется стандартная терминология, принятая для описания экстремальных задач. Для решаемой задачи целевой функцией будет функция в ячейке А7, изменяемыми данными – диапазон В1:В2, содержащий число видов продукции, диапазон А3:А6 – используется для определения ограничений задачи.

Заполним окно диалога по образцу (рис.3.13).

Рисунок 3.13 - Окно диалога «Поиск решения» задачи оптимизации материальных инвестиционных ресурсов

Установить целевую ячейку А7 равной максимальному значению. Изменяя ячейки В1:В2, воспользоваться для определения изменяемых ячеек специальной кнопкой .

Для ввода ограничений нужно нажать кнопку Добавить. Вводятся следующие ограничения:

Ссылка на ячейку: = $А$3, вид ограничения: = <=, ограничение 18.

Ссылка на ячейку: = $А$4, вид ограничения: = <=, ограничение 16.

Ссылка на ячейку: = $А$5, вид ограничения: = <=, ограничение 5.

Ссылка на ячейку: = $А$6, вид ограничения: = <=, ограничение 21.

Ссылка на ячейку: = $В$1:$В$2, вид ограничения: = целое.

Ссылка на ячейку: = $В$1:$В$2, вид ограничения: = >=, ограничение 0.

После нажатия кнопки Выполнить надстройка «Поиск решения» приступает к итерациям, после вычислений открывает диалоговое окно Результаты поиска решения (рис.3.14), в котором выводится сообщение о решении задачи.

Рисунок 3.14 - Результаты поиска решения задачи оптимизации материальных инвестиционных ресурсов

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

Если найденное решение устраивает, можно сохранить его на рабочем листе, выбрав Сохранить найденное решение. Можно также сохранить решение в качестве одного из сценариев с помощью кнопки Сохранить сценарий.

Рассмотрим решение задачи оптимизации финансовых инвестиционных ресурсов. Введем на рабочий лист исходные данные как это показано на рис.3.15

Рисунок 3.15 - Шаблон решения задачи оптимизации финансовых инвестиционных ресурсов

В ячейки А1:А7 вводим наименования показателей и переменных. В ячейки В1:В4 вводим значения показателей.

В ячейки В6:В7 вводим обозначения переменных.

В ячейку D2 вводим, начиная со знака равно, ограничение по уровню доходности инвестиционных портфелей (=В2*С6+В3*С7).

В ячейку D3 вводим, начиная со знака равно, ограничение по доле инвестиционных портфелей (=С6+С7).

Затем вызываем окно диалога «Поиск решения» и заполняем его по образцу (рис. 3.16).

Рисунок 3.16 - Окно диалога «Поиск решения» задачи оптимизации финансовых инвестиционных ресурсов

Установить целевую ячейку Е2 равной минимальному значению. Изменяя ячейки С6:С7.

Вводим ограничения:

Ссылка на ячейку: = $С$6:$С$7, вид ограничения: = >=, ограничение 0.

Ссылка на ячейку: = $D$2, вид ограничения: = <=, ограничение $B$1.

Ссылка на ячейку: = $D$3, вид ограничения: =, ограничение 1.

В результате решения задачи (рис. 3.17) получили следующие данные. Оптимальная доля инвестиционного портфеля I составляет 74%, доля инвестиционного портфеля II составляет 26%, значение риска составило 0,62%.