Поиск оптимального решения

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

Чтобы найти наилучшее решение:

1. Выделите оптимизируемую ячейку. В данном примере это ячейка (общая прибыль за год -F15).

2. Выберите командуСервис, Поиск решения (Tools, Solver). При этом появится незаполненное диалоговое окноПоиск решения.

3. В полеУстановить целевую ячейку (Set Target Cell) уже находится ссылка на выделенную на первом шаге ячейку. При необходимости эту ссылку можно изменить.

4. Установите тип взаимосвязи между целевой ячейкой и решением путем выбора переключателя в группеРавной (Equal To).

5. В полеИзменяя ячейки (By changing cells) укажите ячейки-параметры, которые могут изменяться в процессе поиска решения. В данном примере — это ячейки в В11::E11 (расходы на рекламу в каждом квартале).

6. В некоторых случаях можно воспользоваться возможностью автоматического поиска ячеек-параметров. Для этого необходимо нажать кнопку Предположить (Guess). При этом в полеИзменяя ячейки попадут все ячейки, влияющие на формулу, ссылка на которую дана в полеУстановить целевую ячейку (Set Target Cell).

7. Нажмите кнопкуДобавить (Add), чтобы ввести ограничения для задачи. При этом откроется диалоговое окноДобавление ограничения.

8. Введите первое ограничение. В данном примере значение в ячейке F11 (общие расходы на рекламу) не должно превышать 40000.

9. В полеСсылка на ячейку (Cell Reference) укажите ячейку F11, а в поле Ограничение (Constraint) введите 40000. Знак отношения <=, установленный по умолчанию, в данном случае можно не изменять.

10. Нажмите кнопкуОК. В результате появится заполненное диалоговое оПоиск решения.

11. Нажмите кнопкуВыполнить (Solve). По окончании появится диалоговое окноРезультаты поиска решения.

12. Выберите переключательСохранить найденное значение, чтобы сохранить найденные значения, или переключатель Восстановить исходные значения (Restore Original Values), чтобы оставить значения, которые были на рабочем листе. С помощью этого диалогового окна можно сформировать отчет (как это сделать, описано в разделе "Создание отчетов по результатам поиска решения").



13. Нажмите кнопкуОК.

Решение, найденное с помощью средстваПоиск решения приведено в табл.2

Квартал Расходы на рекламу

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

Слишком затянувшийся процесс поиска решения можно прервать с клавиши .


8768377430689009.html
8768407655574219.html

8768377430689009.html
8768407655574219.html
    PR.RU™