7.2. Использование средства «Поиск решения»

В качестве примера использования Поиска решения рассмотрим анализ хозяйственной деятельности предприятия «Лотос». В этот пример включены следующие модели:

• Модель сбыта.

• Структура производства (Product Mix).

• Транспортная задача (Shipping Routes).

• График занятости (Staff Scheduling).

• Управление капиталом (Maximizing Income).

• Портфель ценных бумаг (Portfolio of Securities).

• Проектирование цепи (Engineering Design).

Каждая из этих моделей содержит постановку реально встречающейся зада­чи и описание ее решения, поэтому подробное изучение приведенных мо­делей может дать ключ к решению конкретной проблемы.

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

Рис. 7.1

1. Открыть книгу Итоги.

2. Добавить новый лист, назвать его Поиск решения.

Таблица 7.1

Описание и содержание ячеек калькуляции

Адрес	Элемент	Содержание	Формат	Число десятичных знаков
С3	Квартал	1	Числовой	0
С4	Сезонный коэффициент	0,9	Числовой	1
С5	Число продаж	=35*C4*(C10+3000)^0,5	Числовой	0
С6	Выручка от реализации	=C5*$C$16	Денежный	2
С7	Затраты на сбыт	=C5*$C$17	Денежный	2
С8	Валовая прибыль	=C6-C7	Денежный	2
С9	Торговый персонал	8000	Денежный	2
С10	Реклама	10000	Денежный	2
С11	Косвенные затраты	=0,15*C6	Денежный	2
С12	Суммарные затраты	=СУММ(C9:C11)	Денежный	2
С13	Производственная прибыль	=C8-C12	Денежный	2
С14	Норма прибыли	=C13/C6	Процентный	0
С16	Цена изделия	40	Денежный	2
С17	Затраты на изделие	25	Денежный	2

3. Разместить на нем таблицу, отражающую результаты хозяйственной деятельности предприятия «Лотос» за год (рис. 7.1). Описание ячеек и их содержания для столбца С приведено в табл. 7.1. Формулы в столбцах D:F получить копированием. В столбец G поместить суммарные значения по соответствующим строкам. В ячейку G14 поместить формулу =G13/G6.

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

Чтобы найти наилучшее решение, близкое к оптимальному, необходимо:

1. 

   Рис. 7.2

   Выделить оптимизируемую ячейку. В рассматриваемом примере это ячейка G13 – Производственная прибыль за год.

2. Выбрать команду Сервис, Поиск решения. При этом поя­вится диалоговое окно Поиск решения (рис. 7.2). В поле Установить целе­вую ячей­ку уже находится ссылка на выделенную на первом шаге ячей­­ку. При необходимости эту ссыл­­ку можно изменить.

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

4. В поле Изменяя ячейки указать ячейки-параметры, которые могут изменяться в процессе поиска решения. В рассматриваемом приме­ре указать ячейки С10:F10 – расходы на рек­ламу в каждом квартале.

В некоторых случаях можно воспользоваться возможностью автоматиче­ского поиска ячеек-параметров. Для этого необходимо нажать кнопку Предположить. При этом в поле Изменяя ячейки попадут все ячейки, влияющие на формулу, ссылка на которую да­на в поле Установить целевую ячейку. Однако использование данной подсказки далеко не всегда дает удовлетворительные результаты поиска оптимального решения.

5. Н

   

   Рис. 7.3

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

6. Выбрать переключатель Сохра­нить найденное значение, чтобы сохранить найденные значения, или переключатель Восстановить исходные значения, чтобы оставить значения, ко­­торые были на рабочем листе. В рассматриваемом примере выбрать вариант Сохранить найденное значение.

7. Нажать кнопку ОК.

Результаты расчета, приведенные в табл. 7.2, показывают, что затраты на рекламу перераспределились, но при этом существенно выросли, что привело к росту суммарных затрат и к уменьшению нормы прибыли. Вместе с тем, увеличилось число продаж, и производственная прибыль за весь год выросла с 69662,1 руб. до 79705,62 руб.

Если годовой бюджет на рекламу ограничен сметой, то при поиске оптимального решения следует ввести соответствующее ограничение на итоговую сумму затрат на рекламу.

Таблица 7.2

Результаты расчета оптимальных затрат на рекламу без учета ограничений их бюджета

Рис. 7.4

Рис. 7.5

Для решения данной задачи с учетом ограничений сле­­дует предварительно вос­становить исходные значения затрат на рекламу в каждом квартале в размере 10000 руб. и выполнить приведенные выше действия 1–4. Затем в окне Поиск решения (см. рис. 7.2) нажать кнопку Добавить, чтобы ввести ограничения для задачи. При этом откроется диалоговое окно Добавление ограничения (рис. 7.4). В поле Ссылка на ячейку указать ячейку G10, а в поле Ограничение ввести установленную сметой величину 40000. Знак отношения <=, установ­ленный по умолчанию, в данном случае можно не изменять. После нажатия кнопки ОК диалоговое окно Поиск решения примет вид как на рис. 7.5. В этом окне нажать кнопку Выполнить. По окончании поиска решения появится диалоговое окно Результаты поиска решения (см. рис. 7.3). С помощью этого диалого­вого окна можно сформировать отчет. Такие отчеты полезны для сравнения влияния на решение различных ограничений или исходных данных. Отчеты бывают трех типов: Результаты, Устойчивость, Пределы. Тип выбирается в диалоговом окне Результаты поиска решения в списке Отчеты (см. рис. 7.3). Можно выбрать сразу два или три типа. Каждый отчет будет создан на отдельном рабочем листе. Содержание отчетов описано в табл. 7.3. В рассматриваемом примере рекомендуется выбрать все три типа отчетов, а затем сравнить их содержание между собой. Далее в окне Результаты поиска решения выбрать переключатель Сохра­нить найденное значение и нажать кнопку ОК. Полученное решение приведено в табл. 7.4.

Анализ результатов показывает, что произошло перераспределение расходов на рекламу по кварталам, в результате чего прибыль увеличилась с 69662,1 руб. до 71446,79 руб. без увеличения бюд­жета на рекламу.

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