Решение задач линейного программирования на примере оптимизации и транспортной задачи, анализ чувствительности с помощью ms excel.
 Скачать 1.06 Mb.
|
|
= 150 — 93,5 тонн. Прежде чем подвести итоги анализа чувствительности, надо как-то записать и структурировать ту информацию, которую мы получили в результате этого анализа. Для этого можно нарисовать таблицу, где для тех значений параметров модели, которые изменялись при проведении анализа чувствительности, были бы приведены значения переменных решения и соответствующие значения целевой функции. Весть средство для создания подобных таблиц. Это средство называется сценарии. Сценарий — это сохраненные как единое целое значения ячеек рабочего листа, содержащие значения и формулы. Excel имеет возможность быстрого переключения между различными сценариями. Поэтому, если сохранить в качестве сценария значения параметров модели и значения переменных решения, можно быстро восстановить модель представления задачи в Excel и ее решение при различных наборах параметров. Кроме того, на основе сохраненных сценариев Excel может создать отчет или в виде структурированной таблицы, или в виде сводной таблицы. Сценарии могут быть очень полезными при проведении анализа чувствительности (для сравнения различных решений) и для документирования результатов анализа. Покажем на нашем примере, как создавать и сохранять сценарии и как затем на их основе построить отчет. Конечно, сценарии надо сохранять по мере их созревания, те. после каждого изменения, внесенного в модель представления задачи в Excel. Но, допустим, что мы забыли об этом правиле или вообще ничего не знали о сценариях. И сейчас хотим наверстать упущенное, создав кучу сценариев на все случаи жизни. Перед началом создания сценариев сделаем маленькое, но существенное замечание о том, что следует сохранять в сценариях. В сценариях сохраняются константы, те. такие значения, которые в ячейки рабочего листа введены напрямую, а не вычислены по формулам. Значения переменных решения, хотя они вычисляются с помощью средства Поиск решения, 14 также считаются константами, поскольку для их определения не используются формулы рабочего листа. Возникает естественный вопрос как же сохранить результаты вычислений Ответ простой они не сохраняются, а вычисляются заново при восстановлении на рабочем листе ранее сохраненных констант сценария или при создании отчета по сценариям. В своих сценариях мы будем сохранять значения переменных решения, значения целевых коэффициентов и значения правых частей ограничений. Итак, вспомним нашу первую модель и ее решение, где целевые коэффициенты си сравнялись соответственно и 2500 , а правая часть пятого ограничения равнялась 30 . Восстановите на рабочем листе эти значения и запустите средство Поиск решения для получения решения. Надеюсь, вы получили прежнее решение x1= 257,14, x2 = 150 и z = см. рис. 7 ). Чтобы создать новый сценарий для текущего рабочего листа, выполните следующие действия. 1. Выберите команду Сервис->Сценарии (В Excel 2007 — Данные->Анализ что если. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить рис. 18 ). Рис. 18. Диалоговое окно Диспетчер сценария — основное окно для работы со сценариями 3. В диалоговом окне Изменение сценария введите название сценария в поле ввода Название сценария рис. 19 ). Желательно давать содержательные названия, показывающие отличия данного сценария от других. В нашем примере первый сценарий назовем Исходный. Рис. 19. Диалоговое окно Изменение сценария — создание нового сценария 4. В поле ввода Изменяемые ячейки введите адреса ячеек, содержащих константы, задающие параметры модели. Эти ячейки в сценариях называются изменяемые ячейки В нашем примере надо ввести B4:C4;B8:C8;F11:F17. Проще всего вводить адреса ячеек путем выделения ячеек непосредственно на рабочем листе. 5. В поле ввода Примечание желательно ввести комментарии к создаваемому сценарию. Если вы не введете комментарии, то Excel автоматически создаст примечание, содержащее имя создателя сценария (по зарегистрированному имени пользователя) и дату его создания. 6. Щелкните в диалоговом окне Изменение сценария на кнопке ОК. 7. В открывшемся диалоговом окне Значения ячеек сценария проверьте и при необходимости измените значения для изменяемых ячеек (рис. 20). 8. Щелкните в диалоговом окне Значения ячеек сценария на кнопке ОК, что создает сценарий и возвращает в диалоговое окно Диспетчер сценариев. Рис. 20. Задание значений для нового сценария Сценарий создан. Чтобы посмотреть, как сценарий вычисляет результаты (и для проверки сохраненных в сценарии значений, измените какие-либо значения на рабочем листе (например, измените значения переменных решения) и затем выполните следующие простые действия. Выберите команду Сервис->Сценарии, в открывшемся диалоговом окне 15 Диспетчер сценариев в списке Сценарии выберите сценарий, который вы хотите отобразить, и щелкните на кнопке Вывести должен воспроизвести на рабочем листе решение нашей первой задачи, которое показанона рис. 7. Если есть какие-нибудь числовые расхождения между тем, что показано на рис. 7, и результатами восстановленного сценария, то проверьте в сценарии значения изменяемых ячеек. Далее создаем сценарий для решения, где правая часть пятого ограничения заменена значением 36,5. Для этого введите в ячейку F14 данное значение и найдите решение с помощью средства Поиск решения см. рис. 11). Затем повторите описанные выше действия по созданию сценария. Этот новый сценарий назовем, к примеру, Полная загрузка. Подобным образом создаем сценарий, где удельные прибыли красок обоих типов равны, и поэтому Excel предлагает отказаться от производства краски Б см. рис. 15). Этот сценарий назовем Без краски Б. Наконец, создадим еще один сценарий, где удельные прибыли красок также равны, но требуется произвести 150 тонн краски Б см. рис. 16). Этому сценарию дадим название Даешь краску Б. Создать отчет по имеющимся сценариям можно следующим образом. Выберите команду Сервис->Сценарии. В открывшемся диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет. В диалоговом окне Отчет по сценарию укажите, какой тип отчета вы хотите создать — выберите переключатель структура для создания итогового отчета в виде структурированного рабочего листа либо переключатель сводная таблица — для создания итогового отчета в виде сводной таблицы (рис. 21). Для сценариев решения задач оптимизации наиболее подходит отчет в виде структурированного рабочего листа. Рис. 21. Диалоговое окно Отчет по сценарию 4. В поле ввода Ячейки результата введите адреса ячеек (вручную или путем выделения их непосредственно на рабочем листе, содержащих итоговые результаты. Для задач оптимизации обязательно надо указать ячейку с целевой функцией, а также, коль в сценариях сохраняются значения правых частей ограничений, ячейки с формулами, вычисляющими значения левых частей ограничений. Щелкните на кнопке ОК. Отчет по сценариям будет легко читаться и будет понятен с первого взгляда, если изменяемым ячейкам сценариев и ячейкам результатов (задаваемых при создании отчета) присвоить уникальные имена, соответствующие их сущности. Присвоить имена ячейкам можно, в частности, с помощью команды Вставка->Имя->Присвоить. В противном случае ячейки в столбце В отчета останутся пустыми и заполнять их придется вручную. Готовый отчет по нашим сценариям показан на рис. 22. Этот отчет может послужить необходимому делу документирования и обоснования принятия решения. Он послужит основой для заключительных выводов выполненного анализа чувствительности. Подведем итоги выполнения анализа чувствительности в нашем примере. 1. Первоначальное решение (сценарий Исходный в отчете на рис. 22) — производить 257,14 т краски Аи т краски Б, при этом будет получена прибыль в размере 889 285,17 руб. — не загружает полностью производственные мощности. 16 Рис. 22. Отчет по сценариям Чтобы полностью загрузить производственные мощности, надо увеличить месячный запас сырья 2 с 30 до 36,5 тонн сценарий Полная загрузка в отчете на рис. 22), при этом следует производить 350 т краски Аи т краски Б, тогда будет получена прибыль в размере 1 075 000 руб. Первые два решения имеют силу, если удельная прибыль краски Б превышает удельную прибыль краски А. Если удельная прибыль краски Б меньше удельной прибыли краски А, то производить краску Б нерентабельно. Если удельная прибыль краски Б примерно равна удельной прибыли краски А, то прибыль не зависит от количества произведенной краски Б (сценарии Без краски Б и Даешь краску Б в отчете на рис. 22). При этом рационально отказаться от производства краски Били уменьшить ее производство до минимума, поскольку это сокращает необходимый для производства суммарный запас всех видов сырья (сценарий Без краски Б. Вот так можно кратко подвести итоги анализа нашей математической модели реальной ситуации. Как эти итоги и полученные оптимальные решения будут интерпретированы в реальную ситуацию, какое влияние они окажут (и окажут ли) на процесс принятия реального решения, как это решение будет воплощаться в жизнь — это вопросы реальной жизни, ответить на которые может только сама жизнь. 17 Варианты заданий Задача 1 На швейной фабрике для изготовления четырёх видов изделий может быть использована ткань трёх артикулов. Нормы расхода тканей всех артикулов на пошив одного изделия приведены в таблице. В ней также указаны имеющиеся в распоряжении фабрики общее количество тканей каждого артикула и цена изделия данного вида. Определить, сколько изделий каждого вида должна произвести фабрика, чтобы стоимость изготовленной продукции была максимальной. Сколько ткани каждого из артикулов может сэкономить фабрика, не теряя прибыли Насколько минимально нужно поднять цену на четвертое изделие, чтобы это увеличило прибыль Что произойдет с прибылью, если фабрике будет необходимо выпускать изделие 3 в количестве не меньше 5 штук Артикул ткани Норма расхода ткани (м) на одно изделие вида Общее количество ткани 1 2 3 4 I II III 1 - 4 - 1 2 2 3 - 1 2 4 180 210 800 Цена одного изделия (руб) 9±2 6 4±3 7 Задача 2 Предприятие выпускает четыре вида продукции и использует три типа основного оборудования токарное, фрезерное и шлифовальное. Затраты времени на изготовление единицы продукции для каждого из типов оборудования приведены в таблице. В ней же указаны общий фонд рабочего времени каждого из типов оборудования, а также прибыль от реализации одного изделия данного вида. Определить такой объем выпуска каждого из изделий, при котором общая прибыль от их реализации является максимальной. Что произойдет с общей прибылью, если прибыль от продажи продукции вида 4 вырастет втрое Как изменится общая прибыль, если предприятию будет необходимо выпускать не меньше 1 единиц продукции 3? Если увеличить время использования фрезерных станков на 80 станко- часов, то можно ли будет уменьшить время использования других станков Тип оборудования Затраты времени (станко-ч) на единицу продукции вида Общий фонд рабочего времени (станко-ч) 1 2 3 4 Токарное Фрезерное Шлифовальное 2 1 1 1 - 2 1 2 1 3 1 - 300 70 340 Прибыль от реализации единицы продукции (руб) 8 3 2±1 1 Задача 3 Для перевозок груза натр х линиях могут быть использованы суда трёх типов. Производительность судов при использовании их на различных линиях характеризуются данными, приведёнными в таблице. В ней же указаны общее время, в течение которого суда каждого типа находятся в эксплуатации, и минимально необходимые объёмы перевозок на каждой линии. Определить, какие суда, на какой линии ив течение какого времени следует использовать, чтобы обеспечить максимальную загрузку судов с учётом возможного времени их эксплуатации. Как изменится общий объем перевозок, если производительность 18 судов видана третьей линии возрастет вдвое, а на второй — уменьшится до 8 млн. тонномиль в сутки Возможно ли в этом случае выполнить заданный объем перевозок На какой линии выгоднее всего использовать суда вида I? Тип судна Производительность судов млн. тонномиль в сутки) на линии Общее время эксплуатации судов 1 2 3 I II III 8 6 12 14 15 12 11 13 4 300 300 300 Заданный объём перевозок млн. Тонно-миль) 3000 5400 3300 Задача 4 Компания "Лакокраска" специализируется на производстве технических лаков. Представленная ниже таблица содержит информацию о ценах продажи и соответствующих издержках производства единицы полировочного и матового лаков. Лак Цена продажи Издержки производства 1 галлона, ф. ст 1 галлона, ф. ст. Матовый 13,0 9,0 Полировочный 16,0 10,0 Для производства 1 галлона матового лака необходимо затратить 6 мин. трудозатрат, а для производства одного галлона полировочного лака — 12 мин. Резерв фонда рабочего времени составляет 400 чел.-ч. вдень. Размер ежедневного запаса необходимой химической смеси равен 100 унциям, тогда как ее расход на один галлон матового и полировочного лаков составляет 0,05 и 0,02 унции соответственно. Технологические возможности завода позволяют выпускать не более 3000 галлонов лака вдень. В соответствии с соглашением с основным оптовым покупателем компания должна поставлять ему 5000 галлонов матового лака и 2500 галлонов полировочного лака за каждую рабочую неделю (состоящую из 5 дней. Кроме того, существует профсоюзное соглашение, в котором оговаривается минимальный объем производства вдень, равный 2000 галлонов. Администрации данной компании необходимо определить ежедневные объемы производства каждого вида лаков, которые позволяют получать максимальный общий доход. Требуется а) Определить ежедневный оптимальный план производства и соответствующую ему величину дохода. б) Для исходной задачи (не учитывающей сверхурочные работы) определить промежуток изменений показателя единичного дохода за 1 галлон полировочного лака, в котором исходное оптимальное решение остается прежним. Задача 5 Найти решение, состоящее в определении плана изготовления изделий A, B и C, обеспечивающего максимальный их выпуск, в стоимости, выраженной с учётом ограничений на возможное использование сырья трёх видов. Нормы расхода сырья каждого видана одно изделие, цена одного изделия соответствующего вида, а также имеющегося сырья, приведены в таблице. Можно ли сэкономить сырье, не уменьшая общей прибыли Что произойдет с прибылью, если перед предприятием поставлена задача выпустить не менее пяти изделий вида A? 19 Вид сырья Нормы затрат (кг) на одно изделие Общее количество сырья (кг) A B C I II III 18 6 5 15 4 3 12 8 3 360 192 180 Цена одного изделия руб) 9±1 10±2 16 - Задача 6 Полиграфическая компания выпускает рекламные издания LinksLetter и Ragged Edge, которые покупатели могут брать в местных магазинах и ресторанах Компания получает доход, продавая место для размещения рекламы в своих изданиях. Стоимость LinksLetter составляет $50 за тысячу экземпляров, а стоимость Ragged Edge — $100 за тысячу экземпляров. Чтобы напечатать тысячу экземпляров LinksLetter требуется один часа печать тысячи экземпляров Ragged Edge занимает всего полчаса. Наследующей неделе ресурс времени печати составит 120 ч. Обе рекламные газеты складываются фальцевальной машиной, ресурс рабочего времени которой составляет 200 ч в неделю, причем она складывает обе газеты с одинаковой скоростью 1000 экземпляров в час. Компания хочет полностью использовать время печатного станка, минимизировав при этом затраты на производство печатной продукции. Определите оптимальный производственный план и его минимальную стоимость. Предположим, что цели менеджера полиграфической компании изменились. Теперь он решил максимизировать получаемую от публикаций прибыль. Он определил, что прибыль от тысячи экземпляров LinksLetter составляет $25, а от тысячи экземпляров Ragged Edge — $45. Необходимо напечатать не менее 60000 экземпляров LinksLetter и не менее 30000 экземпляров Ragged Edge. Ограничения на ресурс рабочего времени печатного станка и фальцевальной машины остаются прежними. Каким будет оптимальный производственный план Какие ограничения являются связывающими Задача 7 Завод может производить пять различных продуктов в произвольном соотношении. В выпуске каждого продукта принимают участие три станка, как показано в таблице. Все цифры даны в минутах на фунт продукта. Время работы станка, мин/фунт Продукт 1 2 3 А 12 8 5 ВСЕ Ресурс рабочего времени каждого станка составляет 128 ч в неделю. Все продукты конкурентоспособны и все их произведенное количество может быть продано по цене $5, $4, $5, $4 и $4 за фунт продукта А, В, Си Е соответственно. Переменные затраты на зарплату составляют $4 в час для станков 1 и 2 ив час для станка 3. Стоимость материалов, затраченных навыпуск каждого фунта продуктов Аи С, составляет $2, а продуктов В, D и Е — $1. Руководство хочет максимизировать прибыль компании. Сколько часов отработает каждый станок, ив каких единицах измеряются теневые цены для ограничений, задающих ресурс рабочего времени для станков Какую цену фирма может позволить себе заплатить за получение дополнительного часа рабочего времени станка 2? Насколько может увеличиться цена продажи продукта А, прежде чем изменится оптимальный производственный план 20 Задача 8 На ткацкой фабрике для изготовления трёх артикулов ткани используются станки двух типов, пряжа и красители. В таблице указаны производительность станка каждого типа, нормы расхода пряжи и красителей, цена 1 метра ткани данного артикула, а также общий фонд рабочего времени станков каждого типа, имеющихся в распоряжении фабрики, фонды пряжи и красителей и ограничения на возможный выпуск тканей данного артикула. Ресурсы Нормы затратна м ткани артикула Общее количество ресурсов 1 2 3 Производительность станков (станко-ч): I типа II типа Пряжа (кг) Красители (кг) Ценам ткани (руб) Выпуск ткани (м Минимальный Максимальный 0,02 0,04 1,0 0,03 5 1000 2000 - 0,03 1,5 0,02 8 2000 9000 0,04 0,01 2,0 0,025 8 2500 4000 200 500 15000 450 - - - Составить такой план изготовления тканей, согласно которому будет произведено возможное количество тканей данного артикула, а общая стоимость всех тканей максимальна. Можно ли будет при этом сэкономить ресурсы пряжи и красителей Будут ли полностью загружены станки Задача 9 Машиностроительное предприятие для изготовления четырёх видов продукции использует токарное, фрезерное, сверлильное, расточное и шлифовальное оборудование, а также комплектующие изделия. Кроме того, сборка изделий требует выполнения определённых сборочно-наладочных работ. Нормы затрат всех видов на изготовление каждого из изделий приведены в таблице. В этой же таблице указаны наличный фонд каждого из ресурсов, прибыль от реализации единицы продукции данного вида, а также ограничения на возможный выпуск продукции 2- го иго вида. Найти план выпуска продукции, при котором прибыль от её реализации является максимальной. Ресурсы Нормы затратна изготовление одного изделия Общий объём ресурсов 1 2 3 4 Производительность оборудования (человек-ч): Токарного Фрезерного Сверлильного Расточного Шлифовального Комплектующие изделия шт) Сборочно-наладочные работы (человек-ч) 550 40 86 160 - 3 4,5 - 30 110 92 158 4 4,5 620 20 150 158 30 3 4,5 - 20 52 128 50 3 4,5 64270 4800 22360 26240 7900 520 720 21 Прибыль от реализации одного изделия (руб) Выпуск (шт Минимальный Максимальный 315 - - 278 40 - 573 - 120 370 - - - - - Найти план выпуска продукции, при котором прибыль от ее реализации является максимальной. Можно ли будет при этом сэкономить комплектующие изделия Будет ли полностью загружено оборудование Задача 10 Для поддержания нормальной жизнедеятельности человеку необходимо потреблять не менее 118 г белков, 56 г жиров, 500 г углеводов, 8 г минеральных солей. Количество питательных веществ, содержащихся в 1 кг каждого вида потребляемых продуктов, а также цена 1 кг каждого из этих продуктов приведены в следующей таблице Питательные вещества Содержание (г) питательных веществ в 1 кг продуктов Мясо рыба молоко Масло сыр крупа картофель Белки Жиры Углеводы Минеральные соли 180 20 - 9 190 3 - 10 30 40 50 7 10 865 6 12 260 310 20 60 130 30 650 20 21 2 200 10 Цена 1 кг продуктов руб) 1,8 1,0 0,28 3,4 2,9 0,5 0,1 Составить дневной рацион, содержащий не менее минимальной суточной нормы потребности человека в необходимых питательных веществах при минимальной общей стоимости потребляемых продуктов. Задача 11 Для перевозок трёх видов продукции предприятие использует два типа технологического оборудования и два вида сырья. Нормы затрат сырья и времени на изготовление одного изделия каждого вида приведены в таблице. В ней же указаны общий фонд рабочего времени каждой из групп технологического оборудования, объёмы имеющегося сырья каждого вида, а также цена одного изделия данного вида и ограничения на возможный выпуск каждого из изделий. Ресурсы Нормы затратна одно изделие вида Общее количество ресурсов 1 2 3 Производительность оборудования (норм-ч): I типа II типа Сырь (кг го вида го вида Цена одного изделия (руб) Выпуск (шт Минимальный Максимальный 2 4 10 30 10 10 20 - 3 15 20 15 20 40 4 1 20 25 20 25 100 200 500 1495 4500 - - - 22 Составить такой план производства продукции, согласно которому будет изготовлено необходимое количество изделий каждого вида, а общая стоимость всей изготовляемой продукции максимальна. Можно ли будет при этом получить экономию сырья Будет ли полностью загружено оборудование Что произойдет с величиной прибыли, если цену на изделие 1 увеличить на 20%? Задача 12 При производстве четырёх видов кабеля выполняется пять групп технологических операций. Нормы затратна км кабеля данного видана каждой из групп операции, прибыль от реализации 1 км каждого вида кабеля, а также общий фонд рабочего времени, в течение которого могут выполняться эти операции, указаны в таблице. Технологическая операция Нормы затрат времени (ч) на обработку 1 км кабеля вида Общий фонд рабочего времени (ч) 1 2 3 4 Волочение 1,2 1,8 1,6 2,4 7200 Наложение изоляции 1,0 0,4 0,8 0,7 5600 Скручивание элементов в кабель 6,4 5,6 6,0 8,0 11176 Освинцевание 3,0 - 1,8 2,4 3600 Испытание и контроль 2,1 1,5 0,8 3,0 4200 Прибыль от реализации 1 км кабеля 1,2 0,8 1,0 1,3 - Определить такой план выпуска кабеля, при котором общая прибыль от реализации изготовляемой продукции является максимальной. Кабель какого вида производить выгоднее всего Задача 13 На мебельной фабрике изготовляется пять видов продукции столы, шкафы, диваны- кровати, кресла-кровати и тахты. Нормы затрат труда, а также древесины и ткани на производство единицы продукции данного вида приведены в таблице. Ресурсы Норма расхода ресурса на единицу продукции Общее количество ресурсов стол шкаф диван- кровать кресло- кровать тахта Трудозатраты (человека-ч) 4 8 12 9 10 3456 Древесина (м) 0,4 0,6 0,3 0,2 0,3 432 Ткань (м) - - 6 4 5 2400 Прибыль от реализации одного изделия (руб) 8 10 16 14 12 - Выпуск (шт Минимальный Максимальный 120 480 90 560 20 180 40 160 30 120 - - В этой же таблице указана прибыль от реализации одного изделия каждого вида, приведено общее количество ресурсов данного вида, имеющееся в распоряжении фабрики, а 23 также указано (на основе изучения спроса, в пределах каких объёмов может изготовляться каждый вид продукции. Определить план производства продукции мебельной фабрикой, согласно которому прибыль от её реализации является максимальной. Можно ли при этом будет сэкономить древесину и ткань Задача 14 Из четырех видов сырья необходимо составить смесь, в состав которой должно входить не менее 26 ед. химического вещества A, 30 ед. – вещества B и 24 ед. – вещества C. Количество единиц химического вещества, содержащегося в 1 кг сырья каждого вида, указано в таблице. В ней же приведена цена 1 кг сырья каждого вида. Составить смесь, содержащую не менее необходимого количества данного вида и имеющую минимальную стоимость. Вещество Количество единиц вещества, содержащегося в 1 кг сырья вида 1 2 3 4 A B C 1 2 1 1 - 2 - 3 4 4 5 6 Цена 1 кг сырья (руб) 5 6 7 8 24 Варианты индивидуальных заданий Номер задания соответствует номеру студента в журнале. Варианты 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Задачи 1 2 12 3 4 9 2 6 10 12 8 5 7 6 4 4 6 8 7 11 5 13 14 6 1 5 10 13 1 9 Литература 1. Минько А.А. Принятие решений с помощью Excel. — М Эксмо, 2007. — 240 с. 2. Эддоус М, Стэнсфилд Р. Методы принятия решений. — М ЮНИТИ, 1997. — 587 с. 3. Таха Х. Введение в исследование операций. — М Вильямс, 2005. — 912 с. |