| Вид продукции | Время обработки, ч. | Прибыль, у.е. | |||
| I | II | III | IV | ||
| A | |||||
| B | |||||
| C |
Максимально допустимое время работы на устройствах I, II, III, IV составляет соответственно 84, 42, 21 и 42 часа.
Требуется рассчитать план производства, обеспечивающий максимальную прибыль.
Решение
Разместим таблицу с исходными данными в ячейrах A1:G9 Рабочего листа Excel и выполним необходимые предварительные расчеты (см. рис.4.3).
Рис. 4.3 – Исходные данные оптимизационной задачи
Отыскать решение задачи, приняв следующие условия:
| 1). | общая итоговая прибыль (F6) => max; |
| 2). | количество изделий (G3:G5)- целое и неотрицательное число; |
| 3). | баланс времени по каждому устройству (B7:E7) <= (B9:E9); |
| 4). | изменению подлежат: количество изделий (G3:G5). |
Окончательный вид формулировки задачи представлен на рис. 4.4:
Рис.4.4 – Формулировка задачи в терминах рабочего листа Excel
Итоговый результат представлен на рис.4.5:
Рис.4.5 – Результат оптимизации
Анализ решения показывает, что все без исключения требования задачи оптимизации выполнены. При этом видно, что для получения максимальной прибыли нецелесообразно выпускать изделие C.
Результаты расчетов представлены в отчете по результатам (рис.4.6):
Рис.4.6 – Отчет по результатам
Утилита «Поиск решения» может использоваться и для решения более сложных задач оптимизации.
Задание 2. Оптимизация плана перевозок (транспортная задача).
Фирма имеет 4 фабрики и 5 центров распределения ее товаров. Фабрики располагаются в г.г. Слуцке, Борисове, Молодечно и Бобруйске с производственными возможностями соответственно 200, 150, 225 и 175 единиц продукции ежедневно.
Распределительные центры располагаются в Витебске, Минске, Орше, Могилеве и Гомеле с потребностями в 100, 200, 50, 250 и 150 единиц продукции ежедневно соответственно.
Хранение на фабрике единицы продукции, не поставленной в центр распределения, обходится в 0,75 у.е. в день, а штраф за просрочку поставки заказанной потребителем в центре распределения единицы продукции, но там не находящейся, равен 2,5 у.е. в день.
Стоимость перевозки единицы продукции с фабрик в пункты распределения приведена в таблице 4.1
Таблица 4.1. План перевозок
| Витебск | Минск | Орша | Могилев | Гомель | Объемы производства | |
| Слуцк | 1,5 | 1,75 | 2,25 | 2,25 | ||
| Борисов | 2,5 | 1,75 | 1,5 | |||
| Молодечно | 1,5 | 1,5 | 1,75 | 1,75 | ||
| Бобруйск | 0,5 | 1,75 | 1,75 | 1,75 | ||
| Потребность |
Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы.
Важно отметить, что т.к. данная модель сбалансирована, т.е. суммарный объем произведенной продукции равен суммарному объему потребностей в ней, то в этой модели не надо учитывать издержки, связанные как со складированием, так и с недопоставками продукции. В противном случае в модель надо ввести:
- в случае перепроизводства – фиктивный пункт распределения; стоимость перевозок единицы продукции в этот фиктивный пункт полагается равной стоимости складирования, а объемы перевозок в этот пункт равны объемам складирования излишка продукции на фабриках;
- в случае дефицита – фиктивную фабрику; стоимость перевозок единицы продукции из фиктивной фабрики полагается равной стоимости штрафов за недопоставку продукции, а объемы перевозок из этой фабрики равны объемам недопоставок продукции в пункты распределения.
Для решения данной задачи построим математическую модель. Неизвестными здесь являются объемы перевозок. Пусть xij – объем перевозок с i-й фабрики в j-й центр распределения. Функцией цели являются суммарные транспортные расходы, т.е.
,
где cij – стоимость перевозки единицы продукции с i-й фабрики в j-й центр распределения. Кроме того, неизвестные должны удовлетворять следующим ограничениям:
- неотрицательность объема перевозок;
- т.к. модель сбалансирована, то вся продукция должна быть вывезена с фабрик и потребность всех центров распределения должна быть полностью удовлетворена.
Таким образом, мы имеем следующую модель:
- минимизировать:
,
- при ограничениях:
, jÎ[1, 5],
, iÎ[1, 4],
, iÎ[1, 4], jÎ[1, 5],
где ai – объем производства на i-й фабрике, bj – спрос в j-м центре распределения.
Постановка задачи в терминах рабочего листа Excel для использования утилиты «Поиск решения».
1. Разместить исходные данные, как показано на рис.4.7, 4.8.
2. Отвеcти ячейки В8:F11 под значения неизвестных (объемов перевозок).
3. Ввести в ячейки Н8:Н11 объемы производства на фабриках.
4. Ввести в ячейки B13:F13 потребность в продукции в пунктах распределения.
5. В ячейку В16 ввести функцию цели = СУММПРОИЗВ(В3:F6;B8:F11).
6. В ячейки G8:G11 ввести формулы, вычисляющие объемы производства на фабриках, в ячейки B12:F12 – объемы доставляемой продукции в пункты распределения.
Рис.4.7 – Исходные данные
Рис. 4.8– Исходные данные в режиме формул
В окне утилиты «Поиск решения» задать целевую ячейку, изменяемые ячейки и ограничения (см. рис.4.9).
Рис. 4.9– Параметры окна «Поиск решения»
Оптимальный план, обеспечивающий минимальные затраты на перевозку продукции от производителей к потребителям, найденный с помощью утилиты «Поиск решения», представлен на рис. 4.10.
Рис.4.10 – Результаты Поиска решения
Как описано выше, утилитой «Поиск решения» может быть сформирован отчет по результатам.
Задания для самостоятельной работы 
Задание 1. Решить задачу линейного программирования, используя надстройку «Поиск решения» ТП MS Excel.
Для производства двух видов изделий А и В используется три типа технологического оборудования. На производство единицы изделия А оборудование первого типа используется а1 часов, оборудование второго типа – а2 часов, оборудование третьего типа – а3 часов. На производство единицы изделия В оборудование первого типа используется в1 часов, оборудование второго типа – в2 часов, оборудование третьего типа – в3 часов.
На изготовление всех изделий администрация предприятия может предоставить оборудование первого типа не более чем на t1 часов, оборудование второго типа не более чем на t2 часов, оборудование третьего типа не более чем на t3 часов.
Прибыль от реализации единицы готового изделия А составляет α руб., а изделия В – β руб.
Составить план производства изделий А и В, обеспечивающий максимальную прибыль от их реализации.
Варианты заданий приведены в таблице 4.2.
Таблица 4.2. Варианты заданий
| Вариант | а1 | а2 | а3 | в1 | в2 | в3 | t1 | t2 | t3 | α | β |
Задание 2. Решить задачу оптимизации плана перевозок (транспортная задача).
Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i-го пункта производства в j-й центр распределения cij приведена в таблице, где под строкой понимается пункт производства, а под столбцом – пункт распределения. Кроме того, в этой таблице в i-той строке указан объем производства в i-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.
Вариант 1.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 2.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 3.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 4.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 5.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 6.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 7.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 8.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 9.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |
Вариант 10.
| Стоимость перевозки единицы продукции | Объемы производства | |||||
| Объемы потребления |