«Поиск решения» составит оптимальную депозитную программу

Цели: подобрать вариант размещения временно свободных денежных средств на банковских депозитах, обеспечивающий компании максимальный доход.

Как действовать: задать условия и ограничения на размещение депозитов в виде формул в Excel и воспользоваться надстройкой «Поиск решения».

Зачастую на расчетных счетах компаний, особенно тех, чей бизнес подвержен влиянию сезонных факторов, аккумулируются временно свободные денежные средства. Разумеется, не перевести такие излишки денег, например, на депозит — просто расточительство. Возможно, банковские депозиты не лучший инструмент для инвестирования, и все же они позволят получить некоторый дополнительный доход на вложенные деньги, хотя бы компенсировать инфляционные потери.

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

Сформулировать ограничения

Суть работы инструмента «Поиск решения» сводится к тому, что программа перебирает все возможные варианты решений с учетом заданных ограничений и выбирает из них наилучшее. Инструмент полностью оправдывает свое название. Сразу оговоримся, что «Поиск решения» можно использовать в отношении практически не ограниченного спектра оптимизационных задач — главное, правильно задать условия. В нашем случае речь идет о подборе оптимальной структуры размещения свободных денежных средств на депозитах в нескольких банках с разными ставками доходности.

Для начала разберемся с тем, что подразумевается под оптимальной структурой размещения. В чем цель оптимизации? Иначе говоря, по каким критериям Excel будет проводить сравнение и выбор? Чаще всего таким показателем служит ожидаемый доход. То есть смысл оптимизационной задачи сводится к поиску такого решения, которое обеспечит компании максимальный доход по депозитам.

Теперь нужно задать ограничения. Вся информация заносится в одну или несколько таблиц, например, таких, как показано на рисунке 1 на стр. 75 (для простоты восприятия ячейки, в которые вносятся ограничения, выделены голубым фоном). Итак, какие условия можно и нужно задать программе.

Во-первых, это имеющиеся на счетах в разных банках суммы временно свободных средств и время, в течение которого эти деньги не потребуются компании. На рисунке 1 это диапазоны ячеек H12:K12 и H11:K11 соответственно. Во-вторых, нужно указать ставки по депозитам, которые предлагают банки-партнеры для разных сроков размещения денег (в процентах годовых — диапазон ячеек D17:G25). Наконец, в-третьих, предстоит ввести сведения об объемах неиспользованных лимитов по каждому из банков (диапазон ячеек С17:С25). Другими словами, ограничения на размер депозита, определяемые в зависимости от надежности конкретной кредитной организации (на усмотрение менеджмента компании). Согласитесь, было бы неоправданно рискованно все деньги положить в один банк, особенно если речь идет о действительно больших суммах средств.

Подобрать лучшее решение

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

Рисунок 1. Модель для построения оптимальной программы размещения депозитов компании

В блоке «Суммы к размещению на депозитах по срокам в днях, руб.» (диапазон ячеек H17:K25) будут отражены результаты — та самая оптимальная структура депозита (или депозитная программа). Excel заполнит эту часть таблицы сам.

Разделы «Итого размещено, руб.» (диапазон ячеек С17:С25) и «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» (диапазон ячеек M17:P25) предназначены для вывода промежуточных расчетов. Здесь формулы придется вводить вручную. Сложного в них ничего нет. В первом разделе по каждому банку суммируются депозиты, во втором — вычисляется доход от размещенного вклада с учетом срока, суммы первоначального взноса и срока.

Целевой показатель «Доходность от размещения на депозитах, итого, руб.», который предстоит максимизировать, также расчетный. Это не что иное, как сумма доходов от всех вкладов (в примере использована следующая формула: "=СУММ(М17:Р25)").

Рисунок 2. Условия и ограничения для поиска решений

Теперь все готово. Запускаем процедуру «Поиск решения». Для этого нужно кликнуть по одноименной кнопке на вкладке панели инструментов «Данные»¹. В появившемся диалоговом окне (см. рис. 2) указываем:

• целевую ячейку — критерий оптимизации. В нашем случае С30 — «Доходность от размещения на депозитах, итого, руб.»;
• направление оптимизации — минимальное, максимальное или определенное значение целевой ячейки. Разумеется, нас интересует максимальный доход от вкладов;
• диапазон, в котором будут отражаться результаты решения — суммы вкладов в различных банках (H17:K25).

И в этом же диалоговом окне задаем ограничения для возможных решений:

• все свободные денежные средства должны быть размещены на депозитах без остатка (Н27:К27 = Н12:К12);
• сумма вкладов в одном банке не может быть выше лимита, установленного для этой же кредитной организации (L17:L25 <= C17:C25).

Остается настроить параметры поиска решения, а затем нажать кнопку «Выполнить». По опыту, расчеты выполняются системой корректнее, если, нажав кнопку «Параметры», задать такие условия, как показано на рисунке 3.

Рисунок 3. Рекомендуемые параметры поиска

Процедура «Поиск решения» запускается несколько раз, пока программа не сообщит о том, что оптимальное решение найдено. Каждый раз полученные результаты будут вноситься программой в область изменяемых ячеек (H17:K25). А при следующей итерации они послужат основой для улучшения этих же значений. В рассматриваемом примере оптимальное решение было найдено после третьего запуска «Поиска решения».

¹ В Excel настройка «Поиск решения» по умолчанию не активна. Если в панели инструментов, на вкладке «Данные», нужная кнопка отсутствует, ее потребуется активизировать. Открыть меню с помощью кнопки «Office», выбрать в нем «Параметры Excel», в левой части появившегося диалогового окна кликнуть по пункту «Надстройки», затем Управление-Параметры Excel-Перейти-Поиск решения.

Версия для печати