Формулы таблицы
После составления плановой таблицы необходимо связать причинно-следственные показатели формулами для вычислений. Представление формул и чисел исходных данных дано в табл. 9.2.
В таблице формулы по месяцам идентичны, поэтому для компактности таблицы мы скрываем 3, 4 и 5-й месяцы.
Управление экспериментами
Осуществляется вручную или с помощью программы оптимизации Поиск решения. Управляющими данными являются суммы инвестиций на покупку депозитных сертификатов в строках 14:16. Управление осуществляется с целью максимизации процентной прибыли в ячейке Н8.
Для расширения экспериментов, где есть над чем подумать менеджеру, поменяйте доходность депозитов, динамику прогноза расходов, введите ограничения для показателей и др. Результаты проведенных экспериментов проанализируйте и отразите в отчете.
3. Практическая работа
Чтобы Вы ощутили трудность и вариантность планирования и мощь экономико-математических методов и компьютерных программ, Вам вначале предлагается выполнить оптимальное планирование вручную. Затем выполняется компьютерная оптимизация и смена условий плановых экспериментов.
4. Задание 1. Ручной поиск оптимального плана
Вам предлагается составить оптимальный план инвестиций в 3 типа депозитных сертификатов вручную. Меняя суммы инвестиций в строках 14:16 необходимо добиться максимального дохода по процентам при соблюдении финансовых ограничений.
Обычно банк при заключении договора на расчетно-кассовое обслуживание требует установить суммы неснижаемых остатков на счетах клиентов. Это повышает надежность и банка и клиента по обслуживанию обязательств. Определим границу сальдо на конец каждого месяца в 100 000 руб. Теперь при планировании инвестиций в сертификаты менеджер должен контролировать, чтобы остатки счета не были меньше 100 000 руб.
5. Задание 2. Компьютерный поиск оптимального плана
После того как Вы попытались в задании 1 составить оптимальный план вручную и убедились, что это почти невозможно, предлагается составить оптимальный план с помощью программы оптимизации.
Вызвать команду меню Сервис Поиск решения. Появляется диалоговое окно оптимизатора (рис. 9.1).
Настройка модели (математическая постановка задачи для оптимизатора).
В диалоговое окно Поиск решения, в поле целевой ячейки, вводим адрес Н8 Доход по процентам. В поле Изменяя ячейки вводим адреса ячеек искомого плана инвестиций в депозиты. В поле Ограничения вводим 4 строки условий, ограничивающих область допустимых решений нашей задачи.
Первое, второе и четвертое ограничение требуют, чтобы инвестиции в депозиты были больше нуля, т. е. неотрицательными. В движении средств по счету они будут вычитаться, пойдут по кредиту.
Третье неравенство требует неснижаемого остатка на расчетном счете (сальдо на конец месяца) в сумме 100 000 руб. Это обеспечивает надежность (ликвидности) банка и фирмы.
Свод параметров модели дан в табл. 9.3.
Таблица 9.3
Параметры задачи
Результат Н8 Цель - получение наибольшего дохода по процентам
Изменяемые данные B14:G14 Сумма по каждому типу депозита
В15.Е15.В16
Ограничения B14:G14>=0 Сумма каждого депозита не может быть меньше нуля
В15:В16>=0
Е15>=0
В18:Н18>=100000 Конечная сумма не должна быть меньше 100000 р.
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное решение готово. Студенты получили урок полезности математических методов оптимизации.
В табл. 9.4 дано оптимальное решение, найденное программой. Получен план максимальной доходности инвестиций свободных денежных средств в депозитные сертификаты. Удовлетворены все ограничения.
Студентам необходимо провести серию экспериментов: снижать или увеличивать суммы в строке расходов, менять доходность сертификатов в колонке В, варьировать конечную сумму, выполнять оптимизацию и наблюдать изменения в процентных доходах и матрице сумм инвестиций в депозиты. Результаты экспериментов отобразить в отчете.