Основы принятия решения в Excel (стр. 2 из 3)

1. Выделите на листе целевую ячейку, в которую введена формула.

2. Выполните команду Сервис/Поиск решения. Открывается окно диалога Поиск решения (Рис. 1). Поскольку была выделена ячейка, в текстовом поле «Установить целевую ячейку» появится правильная ссылка на ячейку. В группе «Равной» переключатель по умолчанию устанавливается в положение «Максимальному значению».

Рис. 1

3. Перейдите к полю "Изменяя ячейки" и введите переменные ячейки листа.

4. Добавьте ограничения на переменные в изменяемых ячейках. Для ввода ограничений нажмите кнопку Добавить, чтобы задать первое ограничение в окне диалога, затем можно ввести второе, третье и т.д.

5. Когда оптимизационная задача будет готова к выполнению, можно нажать кнопку Выполнить для получения ответа. Появится окно диалога с описанием результатов процесса оптимизации.

6. Чтобы отобразить найденное решение в ячейках листа, установите переключатель "Сохранить найденное решение" и нажмите кнопку ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют установленным ограничениям.

Решение задачи MS Excel при помощи надстройки «Поиск решения».

Управление оборотным капиталом.

Условие:

Требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах (и гарантийный резерв).

Доход	Срок
1-мес. депозит:	1%	1
3-мес. депозит:	4%	3
6-мес. депозит:	9%	6

Депозиты по месяцам:

1, 2, 3, 4, 5 и 6

1 и 4

1

Доход по процентам составляет всего 7700 р.

Одной из задач сотрудника или управляющего финансового отдела является управление средствами и краткосрочные вложения с максимальной прибылью при сохранении достаточного резерва для покрытия расходов. Более доходными могут оказаться долгосрочные депозиты, однако краткосрочные депозиты предоставляет более гибкие возможности управления финансовыми средствами.

В данной модели конечная сумма рассчитывается исходя из начальной (из прошлого месяца), плюс погашаемые депозиты, минус новые депозиты и с учетом ежемесячных потребностей самого предприятия(=СУММ(H7:H9)-СУММ(H10:H13)).

Необходимо определить девять сумм: ежемесячные суммы для 1-месячных депозитов; суммы депозитов 1 и 4 месяца для квартальных депозитов; сумму шестимесячного депозита в 1 месяце.

Рис. 2

Параметры задачи:

Результат:	H4	Цель - получение наибольшего дохода по процентам
Изменяемые данные:	B10-G10B11, E11, B12	Сумма по каждому типу депозита.
Ограничения:	B10:G10>=0B11:E11>=0, B12>=0B14:H14>=100000 р.	Сумма каждого депозита не может быть меньше нуля.Конечная сумма не должна быть меньше 100000 р.

Рис. 3

Выделенные ячейки показывают изменяемый параметр,

а затемненные – ограничения.

Настройка параметров алгоритма и программы.

Настройка параметров алгоритма и программы производится в диалоговом окне Параметры (Рис. 4) поиска решения. В окне устанавливаются ограничения на время решения задач, выбираются алгоритмы, задается точность решения, предоставляется возможность для сохранения вариантов модели и их последующей загрузки. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.

Рис.4

Просмотр промежуточных результатов поиска решения.

В диалоговом окне Поиск решения нажмите кнопку Параметры. Чтобы получить возможность просмотра текущих значенийвлияющих ячеек каждой итерации, установите флажок Показывать результаты итераций, нажмите кнопку ОК, а затемкнопку Выполнить. На экране появится диалоговое окно Текущее состояние поиска решения, рис. 3, а влияющие ячейки листа изменятсвои значения.

Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп. Чтобы выполнить следующую итерацию и просмотреть ее результаты, нажмите кнопку Продолжить.

По окончании счета появляется диалоговое окно Результаты поиска решения

Рис. 3

Вычисления и результаты решения задачи.

Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения. Программа начинает работать, в строке сообщений (слева внизу листа) появляется сообщение Постановка задачи...Ваша таблица с моделью и параметрами алгоритма автоматически приводится к стандартам постановки задач математического программирования. Это преимущество Excel. В других пакетах Вам пришлось бы оторваться от экономической сути задачи и заниматься формальной математической постановкой задачи. После этапа постановки решается задача. Чтобы прервать поиск решения, нажмите клавишу ESC. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.

Найденное оптимальное решение предполагает получение дохода по процентам в размере 18 585р. при вложении максимально возможных сумм в шести- и трехмесячные депозиты, с последующим возвратом к одномесячным. Данное решение удовлетворяет всем поставленным ограничениям.

Если не была включена функция Показывать число итераций, то в результате нахождения оптимального решения можно автоматически создать отчет по результатом, пределам или устойчивости. К сожалению, эти отчеты очень неудобны. Они перегружены плохо читаемыми абсолютными ссылками со знаками доллара.

Ручной поиск оптимального плана.

Напомним условие задачи:

Требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах (и гарантийный резерв).

Следовательно, нужно размещать такую сумму денег на депозит, чтобы конечная сумма была максимальная, при этом учитывая ежемесячные расходы, погашенные депозиты ( для ежемесячных – каждый месяц, для 3-м –раз в тои месяца).

Например, рассмотрим для первого месяца:

Месяц:	1-й месяц	2-й месяц	3-й месяц	4-й месяц	5-й месяц	6-й месяц	Конец
Нач. сумма:	400 000р.	205 000р.	216 000р.	237 000р.	158 400р.	109 400р.	125 400р.
Погаш. деп.:	100 000	100 000	110 000	100 000	100 000	120 000
Проценты:	1 000	1 000	1 400	1 000	1 000	2 300
1-м.депозит:	100 000	100 000	100 000	100 000	100 000	100 000
3-м.депозит:	10 000	0	0	10 000
6-м.депозит:	10 000
Расходы:	75 000	-10 000	-20 000	80 000	50 000	-15 000	60 000
Кон. сумма:	205 000р.	216 000р.	237 000р.	158 400р.	109 400р.	125 400р.	187 700р.

Конечная сумма представляет разность начальной суммы и средств, затраченных на депозиты и расходы в каждом месяце:

400 000 – (100 000 + 10 000 + 10 000 + 75 000) = 205 000р.

Следовательно, в следующем месяце мы будем располагать суммой денег равной 205 000 р. и процентами с одномесячного депозита, однако к этой сумме прибавится денежная сумма в размере 10 000 от затраченных расходов на производство в прошлом месяце. И так будет повторяться в следующие шесть 6 месяцев, в исключении только каждый третий и шестой месяц.

В 3-м месяце к процентом от депозитов прибавиться доход с трехмесячного депозита, а в 6-м месяце процент с трехмесячного и шестимесячного депозитов.

Мы имеем определенные ограничения: конечная сумма в каждом месяце не должна быть меньше 100 000 р., т.к. наши максимальные расходы составляют 80 000 р. + непредвиденные расходы 30 000 р., так же мы не имеем права использовать больше той суммы денег, которая лежит на депозите.

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

Решение.

Так как шестимесячные депозиты имеют самый большой процент дохода, то попробуем положить определенную сумму на такой депозит и рассчитаем доход. Допустим, что сумма вклада составляет 100 000 р., тогда конечная сумма будет составлять 9 000 р..

Попробуем разместить 20 000 р. на трехмесячный депозит, в итоге наш доход по истечению шести месяцев увеличивается на 1600р. Если мы увеличиваем сумму этого вклада до 50 000р., мы получаем напряженные ситуации в пятом и шестом месяцах, так как денежная сумма, преходящая из предыдущего месяца меньше 100 000р. и не покрывает необходимые расходы.

Оставляем 20 000р. на трехмесячном депозите и размещаем 10 000р. на одномесячном, получаем доход 11 200р. но не удовлетворение ограничению в пятом месяце.

Месяц:	1-й месяц	2-й месяц	3-й месяц	4-й месяц	5-й месяц	6-й месяц	Конец
Нач. сумма:	400 000р.	195 000р.	205 100р.	225 200р.	146 100р.	96 200р.	111 300р.
Погаш. деп.:	10 000	10 000	30 000	10 000	10 000	130 000
Проценты:	100	100	900	100	100	9 900
1-м.депозит:	10 000	10 000	10 000	10 000	10 000	10 000
3-м.депозит:	20 000	0	0	20 000
6-м.депозит:	100 000
Расходы:	75 000	-10 000	-20 000	80 000	50 000	-15 000	60 000
Кон. сумма:	195 000р.	205 100р.	225 200р.	146 100р.	96 200р.	111 300р.	191 200р.

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