Получение максимальной прибыли при ограниченном ресурсе

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

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

Рассмотрим эту задачу например планирования производства красок. Небольшая фабрика выпускает два типа красок для внутренних (I) и наружных (Е) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используется два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно. Расходы продуктов А и В на 1 тонну соответствующих красок (Рис. 11).

Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску Е более, чем на 1т.

Рис. 8

Кроме того, установлено, что спрос на краску I никогда не превышает 2 т в сутки. Оптовые цены одной тонны красок равны 3000 руб. для краски Е и 2000 руб. для краски I.

Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?

Для решения этой задачи необходимо построить математическую модель. Начнем с ответа на 3 вопроса.

Для определения каких величин строится модель (каковы переменные модели)?

В чем состоит цель оптимизации модели?

Каким ограничениям должны удовлетворять неизвестные?

В нашем случае необходимо спланировать объем производства красок, поэтому переменными являются X_I - суточный объем производства краски I и Х_Е - суточный объем производства краски Е.

Суммарная суточная прибыль от производства красок равна Z = 3000 Х_Е +2000 X_I. Целью оптимизации модели фабрики является определение таких величин суточного производства каждой краски, которые максимизируют суммарную прибыль, то есть целевую функцию Z.

Перейдем к ограничениям, которые налагаются на Х_Е и X_I.

Объем производства красок не может быть отрицательным, следовательно Х_Е, X_I ³ 0.

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

Кроме того, ограничения на величину спроса на краски таковы:

Таким образом, мы составили математическую модель, состоящую из целевой функции, которую надо максимизировать и ограничений.

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

Данная модель является линейной, так как все уравнения этой модели линейные.

Эта задача решается с помощью команды Сервис Þ Поиск решения. Если в меню Сервис команда Поиск решения отсутствует, для ее установки необходимо выполнить команду Сервис Þ Надстройки Þ Поиск решения.

1. Для упрощения Вашей работы при поиске решения сначала все исходные данные, целевую функцию и ограничения оформите в табличном виде (Рис. 12).

Рис. 9

2. В ячейки " Значение" (переменные Х_I и Х_Е) введите пока нули, Это результаты поиска решения, на начальном этапе они могут быть пустыми, но их адреса должны входить в формулу целевой функции и ограничений, если это необходимо.

3. Целевую функцию запишите в ячейке F9 в виде формулы (Рис. 12).

4. Ограничения также запишите в табличном виде, где есть левая и правая части ограничения и знак между ними. Потом эти части легко вставляются в диалоговое окно " Поиск решения".

5. Подготовив все данные, выполните команду Сервис Þ Поискрешения. Появляется диалоговое окно " Поиск решения" (Рис. 13).

· В строку " Установит целевую ячейку" установите курсор и щелкните мышью по ячейке F9. · Переключатель " Равной" устанавливаем в соответствующее задаче положение - " Максимальному значению".

Рис. 10

· В строку " Изменяя ячейки" укажите ячейки, которые должны изменяться в процессе поиска решения задачи, то есть ячейки, отведенные под переменные задачи (ХЕ и ХI) В данном случае это ячейки В4 и С4.

Ограничения вводятся в соответствующее поле в виде равенств, неравенств, также можно ввести требование целочисленности значения. Ограничения добавляются по одному. Для начала ввода ограничений щелкните по кнопке " Добавить ". Появляется диалоговое окно

· " Добавление ограничения", состоящее из трех частей (Рис. 14).

Рис. 11

· " Ссылка на ячейку" - введите мышью адрес ячейки A10, где записаны условия левой части ограничения. В поле " Ограничение" введите мышью адрес ячейки С10, правая часть ограничения. Для установки знака ограничения щелкните по кнопке списка и выберите знак > =.

· Далее можно нажать кнопку " Добавить" и анологичным способом вводить следующее ограничение (Рис. 12) После ввода всех ограничений нажать кнопку " ОК".

6. Теперь нажмите кнопку " Параметры" в диалоговом окне " Поиск решения" и ознакомьтесь с ним.

Рис. 12

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

Поле Максимальное время ограничивает время решения задачи.

Поле Предельное число итераций ограничивает число промежуточных вычислений.

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

Флажок Линейная модель служит для поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. При несоответствии положения флажка решаемой задаче можно получить неверный результат.

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

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

Группа Оценка - выбор метода экстраполяции.

Группа Производные - выбор метода численного дифференцирования.

Группа Метод - выбор алгоритма оптимизации.

7. Закройте окно «Параметры поиска решения», нажав кнопку «ОК».

8. Нажмите кнопку «Выполнить».

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

9. Ничего, не выбирая, нажмите кнопку «ОК».

Решение найдено. Все ограничения и условия выполнены. X_I = 3.333 и X_E = 1.333

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

Планирование штатного расписания

Рассмотрим задачу оптимального размещения сотрудников по должностям (рабочим местам)

Часто в практике руководителя возникает проблема: как разместить сотрудников по разным рабочим местам, чтобы и сотрудник мог проявить свои творческие возможности, и предприятие повысило свою производительность.

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

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

1. Составьте матрицу производительности труда всех претендентов при выполнении конкретных видов работы. При этом если работник Ai назначен на работу Bj, то переменная назначения Xij=1, или Xij=0, если он на эту работу не назначен (Рис. 16).

Рис. 13

2. В ячейках B8: E 8 и F4: F7 введите формулы суммы по столбцам и по строкам.

Если составить таблицу предварительного распределения сотрудников по видам работы (должностям), то из нее видно, что если сотрудник А1 назначен на выполнение работы В1 (B3 =1), то остальные ячейки строки и столбца имеют значение =0 (Рис 16).

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

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

Рис. 14

1. Заполните таблицу производительностей труда сотрудников на разных работах (рис 17).

2. В ячейку D9 рабочего листа введите формулу целевой функции, которая для нашего примера будет иметь вид:

=B4*B14+C4*C14+D4*D14+E4*E14+B5*B15+C5*C15+D5*D15+E5*E15+B6*B16+C6*C16+D6*D16+E6*E16+B7*B17+C7*C17+D7*D17+E7*E17

Это выражение проще ввести в ячейку целевой функции с использованием функции СУММПРОИЗВ, которая позволяет перемножать массивы данных.

=СУММПРОИЗВ(B4: E7; B14: E17)

3. Далее выполните команду СервисÞ Поиск решения и установите соответствующие параметры в диалоговом окне Поиск решения (Рис. 18).

· Укажите целевую ячейку D9. · Установите флажок " Максимальному значению". · Укажите диапазон изменяемых ячеек В4: Е7. · Введите ограничения

Рис. 15

В4: Е7 =двоичное

F4: F7 =1

B8: E8 =1

· В диалоговом окне " Параметры поиска решения" укажите, что решаемая модель линейна Þ ОК.

4. Нажмите кнопку «Выполнить».

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

Рис. 16