Как найти максимальную прибыль в excel
Практически в любой компании в определенные периоды могут "высвобождаться" из оборота временно свободные денежные средства(ВСДС). Оставлять эти деньги просто так на счетах компании весьма нецелесообразно. Деньги должны делать деньги. Конечно, вложение денег в банковские депозиты может показаться не самым лучшим вариантом для инвестиций, но все же это хоть какой-то доход, который если и не будет колоссальным, то хотя бы частично покроет инфляционные потери.
И самое сложное это выбрать банк, программу депозита и срок для вложений таким образом, чтобы получить максимальную выгоду. Сделать это поможет один из самых мощных, но в тоже время малоиспользуемых инструментов Excel - надстройка Поиск решения (Solver) .
Чтобы пошагово выполнять дальнейшие действия, описанные в статье рекомендую сразу скачать файл с моделью для Поиска решения:
Модель_расчета_ВСДС.xls (44,5 KiB, 1 227 скачиваний)
Надстройка Поиск решения хоть и устанавливается автоматически вместе с Excel(начиная с версий 2007 и выше), но по умолчанию отключена. Чтобы включить надстройку необходимо перейти в Файл(File) -Параметры(Options). В появившемся диалоговом окне выбрать слева пункт Надстройки (Add-ins) . Далее справа внизу в выпадающем списке Управление выбрать -Надстройки Excel (Excel Add-ins) и нажать Перейти (Go) :
В окне Надстройки (Add-ins) устанавливаем галочку напротив пункта Поиск решения (Solver) , жмем ОК.
Поиск решения теперь будет доступен с вкладки Данные (Data) -группа Анализ (Analize) :
Исходные данные
Для начала нам потребуется определить сумму временно свободных денежных средств и сроки, на которые мы хотим эти средства разместить в банке. Далее конечно же потребуется выбрать несколько банков (или конкретные предложения отдельного банка) с различными процентами годовых за размещение ДС. Но так же придется учесть и реалии: не стоит все средства размещать исключительно в одном банке и поэтому надо для банков/предложений определить лимит ДС, который нельзя превышать.
Т.е. наши исходные данные выглядят примерно так:
- период размещения ДС на депозите(скажем 14 дней, 28 дней, 62 дня и 91 день)
- сумма ДС, доступная на каждый период размещения
- процент за размещение ДС на каждый период для каждого типа размещения(срочный депозит, до востребования, овернайт и т.д.) или банка
- лимит на размещение средств по каждому типу депозита или для каждого банка(лимит определяется самой компанией)
Как работает Поиск решения
Поиск решения хорош тем, что он может быть применен практически к любой задаче. Что он делает? Он на основании заданных условий и ограничений перебирает все возможные варианты, которые подходят под условия и не выходят за рамки заданных ограничений, если они есть. И из всех подобранных вариантов выбирает самый оптимальный. В нашем случае будем подбирать наиболее выгодный для нас вариант размещения ВСДС.
А что считать наиболее выгодным? Конечно то, что принесет наибольший доход. При этом наша цель не просто выбрать депозит с самым большим процентом (это было бы слишком просто и для этого не нужен Поиск решения), а может даже совместить несколько вариантов размещения ВСДС на разных депозитах с разными ставками и разными периодами. Ведь для различных сумм или сроков и ставки могут быть разными.
И теперь останется определить какие у нас могут быть ограничения. По сути их два основных:
- Непосредственно сумма ВСДС – мы не должны при расчете максимального дохода выходить за рамки общей суммы доступных ВСДС
- Лимит по депозиту для размещения – как упоминалось выше, могут быть установлены лимиты на размещение средств в том или ином банке на усмотрение компании. Конечно, любой банк заинтересован в большей сумме, но не стоит рисковать и вкладывать всю сумму в один банк
Исходные данные определены, ограничения тоже. Осталось записать все это в удобную таблицу(приложена к статье), в которой при необходимости сможем изменять эти исходные данные и ограничения:
Для большей наглядности блоки таблицы разделены цветами:
- Синий и голубой – заполняемые вручную данные: суммы ВСДС, сроки ВСДС, лимиты размещения ВСДС в банках, процент по каждому банку/размещению
- Зеленый – расчетные поля. Их не надо изменять вручную
- Красный – поля, заполняемые автоматически Поиском решения или формулами. Их не надо изменять вручную
- Так же серый шрифт – это "служебные" ячейки, которые не влияют на расчеты непосредственно в таблице, но которые потребуются нам для корректной работы Поиска решения для учета ограничений
После заполнения таблицы исходными данными можно приступать к определению максимального выгодного вложения ВСДС.
Переходим на вкладку Данные (Data) -группа Анализ (Analize) -Поиск решения (Solver) . В появившемся окне указываем следующие данные:
- Оптимизировать целевую функцию (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 KiB, 1 227 скачиваний)
Читайте также: