Решение задачи.

1. Прежде всего, определим цель задачи и вид целевой функции. В данном случае мы хотим максимизировать прибыль, следовательно, целевая функция должна вычислять полную прибыль. В задаче не приводится сведений об издержках и выручке, а задана прибыль, которую приносит каждая произведенная дверь. Поэтому полная прибыль P будет определяться этой прибылью и тем, сколько дверей произведено. Эти соображения приводят нас к выводу, что в качестве переменных задачи следует выбрать количества дверей каждого типа, которые следует произвести. Значит, в задаче будет 3 переменных: x1 - количество стандартных дверей, x2 – количество полированных и x3 – количество резных дверей. При этом целевая функция запишется, очевидно, следующим образом:

Рационально организовать данные на листе MS Excel следующим образом (Рис.1):

Рис.1. Организация данных и введённые формулы для решения пункта 1 задачи примера 1.1.

Фрагмент рабочего листа на Рис.1.1 изображён в режиме показа формул. Формулы в ячейки рационально вводить следующим образом. В начале в ячейку E4 вводят формулу =СУММПРОИЗВ(B4:D4;$B$3:$D$3). Эта формула позволяет рассчитать реальные временные затраты на производство дверей

Блок ячеек $B$3:$D$3, выделенный под значения переменных x1, x2 и x3, адресован абсолютно. Это позволяет ввести формулы для расчёта времени на обработку

и целевой функции

в ячейки E5 и E7, соответственно, путём копирования формулы в ячейке E4. При этом массивы коэффициентов будут автоматически меняться, а массив переменных оставаться неизменным.

В ячейки F4 и F5 введены ограничения по времени производства и обработки в минутах (множитель 60 в формулах).

Теперь имеется вся информация, необходимая надстройке «Поиск решения» для определения оптимального по прибыли плана производства. В строке меню находим пункт Сервис (Tools), а внутри выпадающего меню пункт Поиск решения (в английской версии программы Solver).

Вызов надстройки «Поиск решения» приводит к появлению следующего

диалогового окна (Рис.2):

Рис.2. Диалоговое окно надстройки «Поиск решения»

В нем и следует задать параметры поиска. В окне «Установить целевую ячейку» необходимо указывать ячейку, содержащую целевую функцию (нашем примере, как видно по Рис. 1, это ячейка E7). Переключатель оставляем в позиции Равной максимальному значению. В окошке «Изменяя ячейки» нужно указать ячейки, содержащие переменные решения – в нашем случае это B3:D3. Чтобы указать несколько ячеек, просто выделяем диапазон, как обычно это делается в Excel (в случае разрозненных ячеек удерживая клавишу Ctrl на клавиатуре).

Для того, чтобы добавить что-либо в окно Ограничения, следует нажать кнопку «Добавить» и в выпадающем окне (Рис. 3) ввести ограничения

Рис.3. Диалоговое окно ввода ограничений на переменные.

В данном случае записано, что числа в ячейках E4:E5 меньше или равны числам в ячейках F4:F5, соответственно. Эта установка, таким образом, описывает систему временных ограничений

К этим ограничениям необходимо добавить ограничения на неотрицательность и целочисленность переменных.

Результат всех этих действий показан на рисунке (Рис.4).

Рис.4. Окончательный вид диалогового окна надстройки «Поиск решения».

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

Рис.5. Установка параметров надстройки «Поиск решения».

Больше никаких изменений здесь делать не нужно. Нажав ОК, возвращаемся в панель «Поиск решения».

Теперь можно нажимать кнопку «Выполнить», после чего и будет найдено решение, о чем и сообщит панель Результаты поиска решения (Рис.6).

Рис.6. Окно результатов поиска решений.

Нажав ОК, сохраняем найденное решение на листе MS Excel, содержащем условия задачи.

Рис.7. Окончательные результаты решения задачи по пункту 1.

В данном случае оказывается, что максимально возможная прибыль равна 990000 руб. и получена она будет, если производить за неделю 100 полированных дверей и 200 резных. Это и есть оптимальный план производства для базовой задачи (пункт 1).

2. В первой части задачи мы полагали, что суммарное рабочее время по каким-то причинам (не упоминаемым в условии задачи) жестко разбито на 250 часов производства и 150 часов обработки. Возможно, что это связано со специализацией рабочих. Тем не менее, можно попробовать выяснить, каково оптимальное распределение рабочего времени между стадиями? Ведь если выигрыш от некоторого, возможного на практике, изменения условий значителен, будет иметь смысл приложить определенные усилия и реорганизовать работу.

Оставим действующим решение задачи, и для модифицированной задачи создадим новый лист. (Имеет смысл создать копию листа, щелкнув правой кнопкой по ярлычку листа и отметив пункт «Переместить/Скопировать», а затем поставив флажок «Создавать копию». Во время этой процедуры копируется и скрытый лист с установками для надстройки «Поиск решения»).

Для изменения условий в исходную таблицу добавим две строки: одна с заголовками, другая с формулами суммирования реальных временных затрат E7 и временных ограничений F7 (Рис.8).

Рис.8. Организация данных и введённые формулы для решения пункта 2 задачи примера 1.1.

После этого нужно немного модифицировать задание надстройке «Поиск решения». Вызвав надстройку, удалим из ограничений условие $E$4:$E$5 <= $F$4:$F$5, и добавим вместо него условие E7 <= F7.

Получим следующее решение (Рис. 9)

Рис.9. Окончательные результаты решения задачи по пункту 2.

Распределение времени на производство и на обработку изменилось. Кроме того отметим, во-первых, что максимальная общая прибыль выросла на 180000 руб. в неделю. Во-вторых, оптимальный план рекомендует выпускать только полированные двери в количестве 400 штук.

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

Поэтому имеет смысл посмотреть, что меняется, если потребовать выпускать все двери. Конечно, здесь нужно задать некоторое конкретное число, которое мы вынуждены «взять с потолка». Положим, что следует выпускать не менее 50 штук дверей каждого типа. Для этого необходимо изменить опции настройки «Поиск решения», как показано на Рис. 10.

Рис.10. Настройка опций поиска решений при ограничениях на выпуск не менее 50 штук каждого изделия.

Получим новое решение задачи (Рис.11).

Рис.11. Окончательные результаты решения задачи при ограничениях на выпуск не менее 50 штук каждого изделия.

Введенное ограничение, как любое новое ограничение задачи, уменьшает итоговую прибыль с 990000 руб. до 970000 руб. Конечно, только что проведенное исследование задачи не требуется по условию, но зачастую такой анализ («что будет, если…») очень интересен и полезен для принятия разумного управленческого решения при использовании той или иной математической модели.

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

Организация данных на листе MS Excel в этом случае представлена на Рис.12.

Рис.12. Организация данных и введённые формулы для решения пункта 3 задачи примера 1.1.

На Рис. 13 показана настройка опций поиска решения.

Рис.13. Вид диалогового окна для настройки опций поиска решения пункта 3 задачи примера 1.1.

Результаты примера (Рис.14) свидетельствуют о выполнении всех ограничений.

Рис.14. Окончательные результаты решения задачи по пункту 3.

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

Рис.15. Окончательные результаты решения задачи по пункту 4.