Формулы табличной модели
После составления плановой таблицы необходимо связать показатели формулами для вычислений, как показано в таблице 2 и 3.
Таблица 2
Таблица 3
В данной таблице формулы по месяцам идентичны, поэтому скрываем для компактности таблицы столбцы D:F.
Поиск оптимального плана
Составить оптимальный план вручную, меняя данные в суммах инвестиций на покупку депозитных сертификатов в строках 14:16, добиться при этом максимизации процентной прибыли в ячейке Н8.
Обычно банки при заключении договора на расчетно- кассовое обслуживание требуют установить суммы неснижаемых остатков на счетах клиентов. Это повышает надежность и банка и клиента по обслуживанию обязательств. Определим границу сальдо на конец каждого месяца в 100000 уд.ед.
После того, как вы убедились, что вручную составить оптимальный план не выходя за границы обязательных остатков на счетах почти не возможно, составьте его с помощью программы оптимизации.
Вызвать команду Поиск решения в группе Анализ вкладки Данные (для MS OFFICE 2007) или команду Сервис/Поиск решения (для MS OFFICE 97/2003). Появляется окно Поиск решения (рисунок 1), в котором в поле Целевая ячейка вводим адрес Н8. В поле Изменяя ячейки вводим адреса ячеек искомого плана инвестиций в депозиты B14:G14; B15; E15; B16. В поле Ограничения вводим 4 строки условий, ограничивающих область допустимых решений задачи:
инвестиции в 1- месячные депозитные сертификаты были не меньше 0;
инвестиции в 3- месячные депозитные сертификаты были не меньше 0;
инвестиции в 6- месячные депозитные сертификаты были не меньше 0;
обязательные остатки на расчетных счетах (сальдо на конец месяца) не должны быть меньше 100000 уд.ед.
Рисунок 1 Диалоговое окно «Поиск решения»
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить. В окне Результаты поиска решения указать Тип отчета: Результаты, Устойчивость, Пределы.
Депозитные сертификаты являются эффективным средством вложения временно свободных средств предприятия при управлении оборотными фондами (таблица 5).
Таблица 5
2.4 Лабораторная работа № 3 Оптимизация портфеля ценных бумаг
Цель работы: освоить компьютерную методику формирования портфелей ценных бумаг. Освоить методику и технологию оптимизации проектов в табличном процессоре Excel с помощью программы Поиск решения (Solver).
Постановка задачи: Банк, паевой фонд, инвестиционная компания, страховая компания, компания по управлению активами пенсионного фонда формируют портфель ценных бумаг клиента (инвестора) и управляют им. Клиент банка имеет инвестиционный капитал и желает получить от него максимальную прибыль при минимальном риске потери средств. Теория и практика финансовых рынков утверждает, что эти критерии противоречивы и желаемое увеличение прибыли почти всегда сопровождается увеличением рисков. Портфельный менеджер обязуется сформировать оптимальный портфель акций клиента, т.е. наилучший в смысле получения хорошей прибыли при небольшом риске.