Практически в любой компании в определенные периоды могут "высвобождаться" из оборота временно свободные денежные средства(ВСДС). Оставлять эти деньги просто так на счетах компании весьма нецелесообразно. Деньги должны делать деньги. Конечно, вложение денег в банковские депозиты может показаться не самым лучшим вариантом для инвестиций, но все же это хоть какой-то доход, который если и не будет колоссальным, то хотя бы частично покроет инфляционные потери.
И самое сложное это выбрать банк, программу депозита и срок для вложений таким образом, чтобы получить максимальную выгоду. Сделать это поможет один из самых мощных, но в тоже время малоиспользуемых инструментов Excel - надстройка Поиск решения
Чтобы пошагово выполнять дальнейшие действия, описанные в статье рекомендую сразу скачать файл с моделью для Поиска решения:
Модель_расчета_ВСДС.xls (44,5 КиБ, 1 708 скачиваний)
Надстройка Поиск решения хоть и устанавливается автоматически вместе с Excel(начиная с версий 2007 и выше), но по умолчанию отключена. Чтобы включить надстройку необходимо перейти в Файл
В окне Надстройки
Поиск решения теперь будет доступен с вкладки Данные
Для начала нам потребуется определить сумму временно свободных денежных средств и сроки, на которые мы хотим эти средства разместить в банке. Далее конечно же потребуется выбрать несколько банков (или конкретные предложения отдельного банка) с различными процентами годовых за размещение ДС. Но так же придется учесть и реалии: не стоит все средства размещать исключительно в одном банке и поэтому надо для банков/предложений определить лимит ДС, который нельзя превышать.
Т.е. наши исходные данные выглядят примерно так:
- период размещения ДС на депозите(скажем 14 дней, 28 дней, 62 дня и 91 день)
- сумма ДС, доступная на каждый период размещения
- процент за размещение ДС на каждый период для каждого типа размещения(срочный депозит, до востребования, овернайт и т.д.) или банка
- лимит на размещение средств по каждому типу депозита или для каждого банка(лимит определяется самой компанией)
Поиск решения хорош тем, что он может быть применен практически к любой задаче. Что он делает? Он на основании заданных условий и ограничений перебирает все возможные варианты, которые подходят под условия и не выходят за рамки заданных ограничений, если они есть. И из всех подобранных вариантов выбирает самый оптимальный. В нашем случае будем подбирать наиболее выгодный для нас вариант размещения ВСДС.
А что считать наиболее выгодным? Конечно то, что принесет наибольший доход. При этом наша цель не просто выбрать депозит с самым большим процентом (это было бы слишком просто и для этого не нужен Поиск решения), а может даже совместить несколько вариантов размещения ВСДС на разных депозитах с разными ставками и разными периодами. Ведь для различных сумм или сроков и ставки могут быть разными.
И теперь останется определить какие у нас могут быть ограничения. По сути их два основных:
- Непосредственно сумма ВСДС – мы не должны при расчете максимального дохода выходить за рамки общей суммы доступных ВСДС
- Лимит по депозиту для размещения – как упоминалось выше, могут быть установлены лимиты на размещение средств в том или ином банке на усмотрение компании. Конечно, любой банк заинтересован в большей сумме, но не стоит рисковать и вкладывать всю сумму в один банк
Исходные данные определены, ограничения тоже. Осталось записать все это в удобную таблицу(приложена к статье), в которой при необходимости сможем изменять эти исходные данные и ограничения:
Для большей наглядности блоки таблицы разделены цветами:
- Синий и голубой – заполняемые вручную данные: суммы ВСДС, сроки ВСДС, лимиты размещения ВСДС в банках, процент по каждому банку/размещению
- Зеленый – расчетные поля. Их не надо изменять вручную
- Красный – поля, заполняемые автоматически Поиском решения или формулами. Их не надо изменять вручную
- Так же серый шрифт – это "служебные" ячейки, которые не влияют на расчеты непосредственно в таблице, но которые потребуются нам для корректной работы Поиска решения для учета ограничений
После заполнения таблицы исходными данными можно приступать к определению максимального выгодного вложения ВСДС.
Переходим на вкладку Данные
- Оптимизировать целевую функцию
(Set Objective) – указываем ячейкуH18 , в которой у нас подводится сумма общего дохода от вложений - До
(To) – выбираем Максимум(Max) , т.к. нам нужен максимально возможный доход - Изменяя ячейки переменных
(By Changing Variable Cells) – указываемH9:K14 . В эти ячейки Поиск решения будет подставлять суммы к размещению и вычисляя от этого возможный доход. Собственно, заполненные здесь данные нам и нужны в итоге - В соответствии с ограничениями
(Subject to the Constraints) – здесь мы сами добавляем ограничения, которые необходимо учитывать при расчете дохода. Нам потребуется добавить два ограничения(на скрине выше они уже добавлены, но в любом случае необходимо знать как их создавать). Нажимаем справа кнопку Добавить(Add) , появится окно добавления ограничения:
В данном случае я хочу добавить ограничение, что суммы в ячейках с лимитом размещения в банке должны быть больше или равны общей сумме размещенных ВСДС. Эта сумма у нас подводится в ячейкахL9:L14 . Таким образом нам в левой части надо выбрать ячейки с суммами заданных лимитов(C9:C14 ), а в правой суммы всех вложений –L9:L14 . В выпадающем списке между двумя этими полями можно выбрать тип сравнения. В нашем случае ячейки слева (лимиты ДС) должны быть больше или равны(>= ) общей сумме вложений по данному типу – ячейки справа.
Аналогично добавляем второе ограничение – суммы доступных ВСДС не должны превышать суммы, которые Поиск решения предложит разместить. Доступные суммы у нас указаны в ячейкахD7:G7 , а общие суммы предложенных к размещению Поиском решения – в ячейкахH16:K 16(в этих ячейках записаны формулы, суммирующие данные сумм по каждому периоду в ячейкахH9:K14 ) - Так же лучше установить галочку Сделать переменные без ограничений неотрицательными
(Make Unconstrained Variables Non-Negative) , чтобы Поиск решения не стал подбирать отрицательные суммы для выполнения условий
В рассматриваемой задаче это маловероятно, но при использовании Поиска решения в других задачах этому пункту советую уделять особое внимание, т.к. иногда оптимальным решением для достижения заданного результата с точки зрения Поиска решения будет добавление отрицательного значения среди заполняемых ячеек
Нажимаем Найти решение
После этого мы сможем более детально изучить предложенное решение:
При необходимости изменить какие-то исходные данные и запустить поиск решения заново. Все ранее указанные ограничения и условия сохраняются и создавать их заново не придется.
Осталось понять
В блоке Доход в зависимости от срока размещения, руб(
При этом если мы захотим исключить какой-либо банк/тип депозита из просчета, достаточно будет установить в ячейках
Надстройка Поиск решения реализована при помощи весьма сложных алгоритмов и, пожалуй, является самой непредсказуемой надстройкой в Excel. Поэтому рекомендуется тщательно перепроверять результаты вручную, прежде чем полностью на них положиться. Сверяйте полученные результаты, чтобы убедиться, что ограничения не нарушены и главное, что результат отвечает ожиданиям (хотя бы примерно).
Так же следует учитывать, что надстройка может при одинаковых условиях и исходных данных выдавать различные результаты при многократном запуске. Это так же обусловлено сложностью заложенных алгоритмов. Т.е. теоретически, запустив Поиск решения пять раз есть вероятность, что все пять раз решение будет разным. Хотя в большинстве случаев я наблюдал ситуации, когда при одинаковых исходных данных решение было одинаковым.
Модель_расчета_ВСДС.xls (44,5 КиБ, 1 708 скачиваний)
И еще один пример применения Поиска решения. Предположим, что у нас есть таблица с начислениями НДС за первый квартал. И есть некая итоговая сумма, которая должна быть отчислена за первый квартал. Проблема в том, что не все строки из таблицы были использованы для отчисления общего налога за первый квартал. Задача отобрать из всех сумм только те, которые при сложении дадут именно нужную итоговую сумму отчислений. Данный прием я решил не описывать текстом, а записать видео для большей наглядности:
Скачать файл из видео:
Подбор под сумму.xls (60,5 КиБ, 1 083 скачиваний)
Так же см.:
План-фактный анализ в Excel при помощи Power Query
Автообновляемая сводная таблица