Расчетно-графическая работа по теме: Решение задач линейного программирования в среде MS Excel
Требования к выполнению работы
Расчетно-графическая работа выполняется на втором курсе в процессе изучения дисциплины Математика-2. Цель работы – овладение методами решения задач оптимизации на компьютере с использованием надстройки «Поиск решения» электронных таблиц MS Excel.
Каждый студент должен решить две содержательные задачи оптимизации, сформулированные в словесной форме. Для этого необходимо:
1. Построить математическую модель задачи;
2. Представить ее в табличной форме на листе Excel;
3. Найти оптимальный план средствами надстройки «Поиск решения»;
4. Вывести отчеты по результатам и устойчивости и объяснить их смысл;
5. Ответить на все вопросы из задания;
Отчет о проделанной работе может быть выполнен и распечатан либо в формате MS Word, либо в формате MS Excel. К отчету должна быть приложена дискета с решением задач в электронной таблице. Образец отчета, подготовленного в текстовом редакторе MS Word приведен в приложении 3. Примеры решения транспортной задачи и задачи на минимум целевой функции представлены в файлах:
Транспортная задача.xls
Задача на min (удобрения).xls.
Приложение 2
Индивидуальные задания к расчетно-графической работе
Вариант № 1
Задача 1.
Туристическое агентство собирается заказать издательству выпуск художественных альбомов трех типов A, B, C. Их изготовление лимитируется затратами ресурсов трех видов, удельные расходы которых приведены в таблице
| Вид ресурса | Удельные затраты ресурсов на выпуск альбомов | ||
| A | B | C | |
| Финансы ($) Бумага (листы) Трудозатраты (чел-час) |
Издательство для выполнения заказа получило финансовые средства в объеме $3600, имеет в наличии 52000 листов бумаги и может использовать трудовые ресурсы в объеме 2200 чел-час.
Агентство платит за выпуск одного альбома типа А - 22 долларов, за альбом В – 18 долларов, за альбом С – 30 долларов.
1) Сколько альбомов каждого типа должно выпустить издательство, чтобы получить максимальный суммарный доход?
2) Объясните смысл данных отчета по устойчивости.
3) Почему издательству не выгодно выпускать альбомы типа С? При какой оплате выпуск альбомов С станет выгодным?
4) Увеличится ли прибыль за счет привлечения дополнительных финансовых средств?
Задача 2.
Предприятие оптовой торговли может реализовать 
, группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
 |  |  |  | |||
Складские площади,  | 60 000 |  | ||||
| Трудовые ресурсы , чел.-час. | 400 000 | | ||||
| Издержки обращения, ден.ед. | 600 000 | | ||||
| Товарные запасы, ден.ед. | 90 000 | | ||||
| План товарооборота, ден.ед. | 300 000 |  | ||||
Минимально допустимый план товарооборота по  -й группе, ед. | 1 100 | | ||||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота 
-й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 2
Задача 1.
На трех мелькомбинатах ежедневно производится 110,190 и 120 т. муки. Эта мука потребляется четырьмя хлебозаводами, ежедневные потребности которых равны соответственно 80, 60, 170 и 80 т. Тарифы перевозок задаются таблицей
| Мелькомбинат | Хлебозаводы | |||
| I | II | III | IV | |
1) Составить план доставки муки, при котором общая стоимость перевозок будет минимальной.
2) Определить мелькомбинаты с избыточным производством муки.
3) Изменится ли план перевозок и транспортные расходы, если тариф на перевозку муки с 1-го комбината на 2-й хлебозавод возрастет до 3 единиц ?
4) Как изменятся транспортные расходы, если выпуск муки на 2-м комбинате снизится до 170 т. ?
Задача 2.
Предприятие оптовой торговли может реализовать 
группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 50 000 | | ||||
| Трудовые ресурсы , чел.-час. | 350 000 | | ||||
| Издержки обращения, ден.ед. | 720 000 | | ||||
| Товарные запасы, ден.ед. | 110 000 | | ||||
| План товарооборота, ден.ед. | 510 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | 1 000 | 1 200 | 1 300 | | ||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 3
Задача 1.
Кондитерская фабрика собирается освоить выпуск трех новых видов конфет, назовем их условно A, B, C. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены.
Нормы расхода сырья на производство 10 кг конфет каждого вида, запасы сырья и ожидаемая прибыль приведены ниже.
| Сырье | Нормы расхода сырья | Запас сырья | ||
| А | В | С | ||
| Какао | ||||
| Сахар | ||||
| Наполнитель | ||||
| Прибыль от 10 кг |
1) Определите, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.
2) Объясните смысл данных отчета по устойчивости.
3) Почему не выгодно выпускать конфеты А? Когда их выпуск будет рентабелен?
4) Приведет ли к возрастанию прибыли увеличение запасов сахара до 200 кг?
Задача 2.
Предприятие оптовой торговли может реализовать 
группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 65 000 | | ||||
| Трудовые ресурсы, чел.-час. | 480 000 | | ||||
| Издержки обращения, ден.ед. | 850 000 | | ||||
| Товарные запасы, ден.ед. | 150 000 | | ||||
| План товарооборота, ден.ед. | 500 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | | |||||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 4
Задача 1.
Три торговых склада могут поставлять холодильники в количестве 9, 4 и 8 единиц соответственно. Величины спроса трех магазинов, находящихся в пунктах А, В и С, на эти холодильники равны 3, 5 и 6 единицам соответственно. Тарифы на доставку одного изделия с каждого склада в каждый магазин приведены в таблице
| Склад | Стоимость перевозки 1 холодильника | ||
| Магазин А | Магазин В | Магазин С | |
1) Найдите план перевозок минимальной суммарной стоимости.
2) Определите склады с избытками хранения изделий.
3) Изменится ли план перевозок и транспортные расходы, если стоимость доставки 1 холодильника с 3-го склада в магазин А возрастет вдвое ?
4) Если спрос магазина В увеличится до 7 холодильников, с какого склада выгоднее организовать их завоз ?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 75 000 | | ||||
| Трудовые ресурсы , чел.-час. | 550 000 | | ||||
| Издержки обращения, ден.ед. | 800 000 | | ||||
| Товарные запасы, ден.ед. | 180 000 | | ||||
| План товарооборота, ден.ед. | 450 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | 1 050 | 1 000 | | |||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 5
Задача 1.
Компания производит различные виды мебели для кабинетов - столы трех типов (1, 2 и 3). Объемы работы, необходимой для каждой операции, приводится в таблице:
| Операции | Объем работы (чел.-час.) | ||
| Стол 1 | Стол 2 | Стол 3 | |
| Изготовление частей | |||
| Сборка | |||
| Полировка и проверка |
Максимум объема работ в неделю составляет 360 чел-час на изготовление частей стола, 240 чел-час на сборку и 180 чел-час на полировку. Возможности хранения ограничивают производство 170 столами в неделю. Прибыль от продажи столов типов 1, 2, 3 составляет соответственно 15, 22 и 19 долларов.
1) Составьте и решите задачу линейного программирования, считая целью максимизацию суммарной прибыли.
2) Объясните смысл данных отчета по устойчивости.
3) Определите возможные излишки рабочей силы на каждой операции.
4) Предположим, что складские помещения расширены, так что возможности хранения достигают 200 столов в неделю. Насколько увеличится при этом прибыль?
5) Изменится ли план выпуска столов и суммарная прибыль, если прибыль от продажи стола 2 будет равна 20 долларам?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 8 000 | | ||||
| Трудовые ресурсы, чел.-час. | 500 000 | | ||||
| Издержки обращения, ден.ед. | 850 000 | | ||||
| Товарные запасы, ден.ед. | 180 000 | | ||||
| План товарооборота, ден.ед. | 420 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | 1 000 | 1 100 | | |||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 6
Задача 1.
Рацион коpмления коpов на молочной феpме может состоять из 3-х пpодуктов: сена, силоса, концентpатов. Эти пpодукты содеpжат питательные вещества: белок, кальций, витамины. Численные данные пpедставлены в таблице.
| Продукты | Питательные вещества | ||
| Белок (г/кг) | Кальций (г/кг) | Витамины (мг/кг) | |
| Сено | |||
| Силос | |||
| Концентраты |
В pасчете на одну коpову суточные ноpмы потpебления белка и кальция составляют не менее 2000 г и 210 г соответственно.
Потpебление витаминов стpого дозиpовано и должно быть pавно 87 мг в сутки. Составьте самый дешевый pацион, если стоимость 1кг сена, силоса и концентpатов pавна соответственно 1,5; 2 и 6 центов.
1) Почему в оптимальный рацион не вошли концентраты? При каком условии они будут включены в рацион ?
2) Как изменится стоимость рациона при снижении суточной нормы по кальцию на 10 г?
3) Как изменится стоимость рациона, если потребуется увеличить потребление витаминов до 90 мг?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 100 000 | | ||||
| Трудовые ресурсы , чел.-час. | 500 000 | | ||||
| Издержки обращения, ден.ед. | 110 000 | | ||||
| Товарные запасы, ден.ед. | 220 000 | | ||||
| План товарооборота, ден.ед. | 710 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | 1 000 | 11 000 | 14 000 | 1 600 | | |
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшифровке».
Вариант № 7
Задача 1.
Издательство, выпускающее альбомы, в своей деятельности ограничено финансовыми ресурсами в размере 50000 долларов, лимитами на бумагу в объеме 70000 листов и пропускной способностью полиграфической базы, равной 60000 оттисков. Зная удельные расходы ресурсов и выручку от реализации каждого из 4-х видов альбомов, издательство должно составить план выпуска альбомов, максимизирующий прибыль от реализации продукции.
| Альбомы Показатели | I | II | III | IV |
| Финансовые расходы | 1/3 | 1/2 | 1/4 | |
| Расходы бумаги | ||||
| Затраты полиграфии | ||||
| Выручка от реализации |
1) На каких условиях издательство согласится выпускать альбомы I ?
2) Выгодно ли закупить (за счет будущей прибыли) 20000 листов бумаги по цене 4 доллара за 1000 листов?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 160 000 | | ||||
| Трудовые ресурсы, чел.-час. | 650 000 | | ||||
| Издержки обращения, ден.ед. | 330 000 | | ||||
| Товарные запасы, ден.ед. | 96 000 | | ||||
| План товарооборота, ден.ед. | 320 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | | |||||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 8
Задача 1.
Фермер хочет кормить своих животных таким образом, чтобы удовлетворить их пищевым потребностям при минимуме затрат. В рацион предполагается включить пшеницу, сою, овес и люцерну. В таблице приведены данные о содержании питательных веществ в злаках, их дневные нормы и цены на злаки.
| Питательные вещества | Пшеница | Соя | Овес | Люцерна | Дневная норма |
| протеин (мг) | минимум 40 мг | ||||
| кальций (мг) | минимум 120 мг | ||||
| жир (мг) | максимум 100 мг | ||||
| калории | 1 200 | 1 000 | 6 000 | минимум 4000 кал. | |
| Стоимость 1ф. |
1) Определите рацион наименьшей стоимости, удовлетворяющий всем нормам.
2) Изменится ли оптимальный рацион и его стоимость, если норма содержания жира будет снижена до 90 мг ?
3) Изменится ли оптимальный рацион и его стоимость, если норма содержания протеина будет установлена равной 45 мг ?
4) Изменится ли оптимальный рацион и его стоимость, если стоимость одного фунта люцерны снизится до 80 единиц ?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 95 000 | | ||||
| Трудовые ресурсы, чел.-час. | 320 000 | | ||||
| Издержки обращения, ден.ед. | 770 000 | | ||||
| Товарные запасы, ден.ед. | 160 000 | | ||||
| План товарооборота, ден.ед. | 350 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | 1 020 | | ||||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 9
Задача 1.
На звероферме могут выращиваться песцы, черно-белые лисы, нутрии и норки. Для их питания используются три вида кормов. В таблице приведены нормы расхода кормов, их ресурс в расчете на день, а также прибыль от реализации одной шкурки каждого зверька.
| Вид корма | Нормы расхода кормов (кг/день) | Ресурс кормов (кг) | |||
| Песец | Лиса | Нутрия | Норка | ||
| I | |||||
| II | |||||
| III | |||||
| Прибыль |
Определите, сколько и каких зверьков следует выращивать на ферме, чтобы прибыль от реализации шкурок была наибольшей.
1) Изменится ли оптимальный план выращивания зверьков и суммарная прибыль, если прибыль от реализации шкурки лисы будет равна 13 ед.?
2) Изменится ли оптимальный план выращивания зверьков и суммарная прибыль, если прибыль от реализации шкурки нутрии будет равна 10 ед.?
3) Что произойдет с решением задачи, если запас корма I увеличится до 320 кг?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 110 000 | | ||||
| Трудовые ресурсы , чел.-час. | 640 000 | | ||||
| Издержки обращения, ден.ед. | 900 000 | | ||||
| Товарные запасы, ден.ед. | 220 000 | | ||||
| План товарооборота, ден.ед. | 330 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | | |||||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Вариант № 10
Задача 1.
Сталеплавильная компания располагает тремя заводами М1, М2, М3, способными произвести в неделю 50, 30 и 20 тыс. т стали соответственно. Свою продукцию компания поставляет четырем потребителям Р1, Р2, Р3 и Р4, потребности которых составляют соответственно 12, 15, 25 и 36 тыс. т стали. Стоимости производства и транспортировки 1 тыс. т стали с различных заводов различным потребителям приведены в таблице
| Потребитель | Заводы | ||
| М1 | М2 | М3 | |
| Р1 | |||
| Р2 | |||
| Р3 | |||
| Р4 |
1) Требуется определить план перевозок стали от производителей потребителям, минимизирующий общую стоимость.
2) Можно ли сократить объемы производства на некоторых заводах без увеличения суммарной стоимости ?
3) Изменится ли план перевозок и общая стоимость, если стоимость производства и транспортировки 1 т стали с завода М3 потребителю Р3 уменьшится до 18 у.е.?
4) Изменится ли план перевозок и общая стоимость, если спрос потребителя Р2 возрастет на 2 тыс.т стали ?
Задача 2.
Предприятие оптовой торговли может реализовать группы товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены в таблице
| Лимитирующие ресурсы и показатели | Товарная группа | Объем ресурса | Вид ограничения | |||
| | | | | |||
| Складские площади, | 110 000 | | ||||
| Трудовые ресурсы, чел.-час. | 710 000 | | ||||
| Издержки обращения, ден.ед. | 100 000 | | ||||
| Товарные запасы, ден.ед. | 240 000 | | ||||
| План товарооборота, ден.ед. | 750 000 | | ||||
| Минимально допустимый план товарооборота по -й группе, ед. | 1 150 | 1 350 | 1 550 | | ||
| Прибыль, ден.ед. |
Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничения на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота -й группы задана. Сделать анализ полученного решения. Дать экономическую интерпретацию двойственных переменных. Выявить «узкие места» на торговом предприятии и дать рекомендации по их «расшивке».
Приложение 3