Решение экономических задач средствами MS Excel
Задание
1. Изучить информацию о задачах линейного программирования и о средствах
их решения в среде Excel (подбор параметра, поиск решения).
2. В соответствии с вариантом задания подготовить
математические модели задач и выполнить их решение с помощью инструмента
"Поиск решения".
3. Продемонстрировать решение задачи в среде
Excel и интерпретировать полученное решение с точки зрения смысла задачи.
Обязательные требования к оформлению решения
- Создайте папку под именем вида Фамилия_ЭЗ
- Каждую из предложенных задач решить в отдельной рабочей книге,
которой присвоить имя Фамилия_№задачи
и сохранить в созданной папке Фамилия_ЭЗ
- Каждая рабочая книга должна содержать четыре листа:
- На первом листе разместить условие задачи. Имя
листа – Фамилия_Условие
- На втором листе описать математическую модель,
соответствующую условию задачи. Имя листа – Фамилия_Математическая модель
- На третьем листе поместить решение и ответ к
задаче. Оформление должно быть
доступным не только Вашему пониманию. Имя листа – Фамилия_Решение, а также
поместить экранную копию диалогового окна «Поиск решения».
- На четвертом листе поместить решение с третьего
и отобразить формулы, в вычисляемых ячейках, а также поместить экранную
копию диалогового окна «Поиск решения». Имя листа – Фамилия_Формулы .
- Оформление третьей задачи можно упростить до
одного листа с решением (Фамилия_Решение)
Распределение
задач по варантам
|
№ варианта |
|||||||||||||
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
|
1.1 |
1.2 |
1.3 |
1.4 |
1.5 |
1.6 |
1.7 |
1.8 |
1.9 |
1.10 |
1.11 |
1.12 |
1.13 |
1.14 |
|
2.14 |
2.13 |
2.12 |
2.11 |
2.10 |
2.9 |
2.8 |
2.7 |
2.6 |
2.5 |
2.4 |
2.3 |
2.2 |
2.1 |
|
3.1 |
3.2 |
3.3 |
3.4 |
3.5 |
3.6 |
3.7 |
3.8 |
3.9 |
3.10 |
3.11 |
3.12 |
3.13 |
3.14 |
Задачи линейного программирования
1. Задачи о рациональном
использовании сырья
Постановка задачи
Для производства трех видов
продукции используется три вида сырья. Нормы затрат каждого из видов сырья на
единицу продукции данного вида, запасы сырья, а также прибыль с единицы
продукции приведены в таблицах вариантов. Определить план выпуска продукции для
получения максимальной прибыли при заданном дополнительном ограничении.
1.1.
1.2.
1.3.
1.4.
1.5.
1.6.
1.7.
1.8.
1.9.
1.10.
1.11.
1.12.
1.13.
1.14.
2. Транспортные задачи
2.1. Имеются два мелькомбината I , II и три пекарни А, В,
С. Производительность каждого из комбинатов равна 30 тонн. Потребность в муке
каждой из пекарней А, В, С составляет 20 тонн. Стоимость перевозки 1 тонны муки
с мелькомбината I в пекарни А, В, С равна соответственно 2, 5, 4 денежных
единиц, с мелькомбината II – 1 , 3, 5 денежных единиц. Необходимо составить
план перевозки муки, чтобы затраты на перевозку были минимальными.
2.2. В трех хранилищах А1, А2, А3 имеется соответственно
70, 90, 50 т топлива. Требуется спланировать перевозку топлива потребителям В1,
В2, В3, В4, В5 спрос которых равен соответственно 50, 70, 40, 40, 10 т, так,
чтобы затраты на транспортировку были минимальными. Стоимость перевозки указаны
в таблице:
|
Хранилища |
В1 |
В2 |
В3 |
В4 |
В5 |
Запас топлива |
|
А1 |
5 |
2 |
3 |
6 |
0 |
70 |
|
А2 |
4 |
3 |
5 |
7 |
0 |
90 |
|
А3 |
2 |
4 |
1 |
5 |
0 |
50 |
|
Потребность |
50 |
70 |
40 |
40 |
10 |
210 |
2.3. С трех складов А1, А2, А3 необходимо доставить овощи
в пять торговых точек В1, В2, В3, В4, В5. Требуется закрепить склавды за
торговыми точками так, чтобы общая сумма затрат на перевозку была минимальной.
Стоимость перевозок приведена в таблице:
|
Склады |
В1 |
В2 |
В3 |
В4 |
В5 |
Объем вывоза |
|
А1 |
7 |
3 |
5 |
4 |
2 |
40 |
|
А2 |
6 |
2 |
3 |
1 |
7 |
150 |
|
А3 |
3 |
5 |
2 |
6 |
4 |
100 |
|
Объем вывоза |
20 |
80 |
90 |
60 |
40 |
290 |
2.4. Составить план первозок зерна из районов А1, А2, А3,
А4 республики, в которых запасы соответственно 800, 700, 1000 и 500 тыс. ц, на
три элеватора В1, В2 И В3мощностью 1000, 1100 и 900 тыс. ц. Затраты на
перевозку 1ц зерна из районов на элеваторы приведены в таблице:
|
Районы |
В1 |
В2 |
В3 |
Запас |
|
А1 |
3 |
5 |
6 |
800 |
|
А2 |
7 |
2 |
4 |
700 |
|
А3 |
4 |
3 |
5 |
1000 |
|
А4 |
6 |
4 |
7 |
500 |
|
Мощность элеватора |
1000 |
1100 |
900 |
3000 |
2.5. Найти решение транспортной задачи, исходные данные
которой приведены в таблице:
|
Пункты отправления |
Пункты назначения |
Запасы |
||||
|
В1 |
В2 |
В3 |
В4 |
В5 |
|
|
|
А1 |
5 |
3 |
2 |
4 |
8 |
160 |
|
А2 |
7 |
6 |
5 |
3 |
1 |
90 |
|
А3 |
8 |
9 |
4 |
5 |
2 |
140 |
|
Потребности |
90 |
60 |
80 |
70 |
90 |
390 |
2.6. Найти решение транспортной задачи, исходные данные
которой приведены в таблице
|
Заводы |
Бензохранилища |
Производство |
|||
|
В1 |
В2 |
В3 |
В4 |
|
|
|
А1 |
8 |
7 |
3 |
0 |
40 |
|
А2 |
2 |
4 |
9 |
1 |
80 |
|
А3 |
9 |
5 |
5 |
8 |
70 |
|
А4 |
3 |
4 |
9 |
11 |
40 |
|
Вместимость |
40 |
60 |
50 |
80 |
230 |
2.7. С двух заводов А и В развозят бетон на три
стройплощадки. Запасы завода А – 320 тонн, завода В – 380 тонн. Потребность
стройплощадки I в бетоне 200 тонн, стройплощадки II – 280 тонн, стройплощадки
III – 220 тонн. Стоимость перевозки одной тонны бетона с завода А на
стройплощадку I – 2000 денежных единиц, на стройплощадку II – 4000 денежных
единиц, на стройплощадку III – 6000 денежных единиц. Стоимость перевозки 1
тонны бетона с завода В на стройплощадку I – 5000 денежных единиц, на
стройплощадку II – 5000 денежных единиц, на стройплощадку III – 3000 денежных
единиц. Как спланировать снабжение стройплощадок так, чтобы затраты на
перевозку были наименьшими?
2.8. Перевозится однородный груз из двух пунктов А и В к
трем местам назначения – I , II , III . Ежедневно отправляется 65 тонн. В том
числе из пункта А – 40 тонн, из В – 25 тонн. В пункты назначения должны
поступить следующие количества груза: в пункт I – 10 тонн, во II – 35 тонн, в
III – 20 тонн. Расстояние от пункта А до пунктов I , II , III равны соответственно
7, 2 и
2.9. В пунктах А и В расположены кирпичные заводы, а в
пунктах С и Д – карьеры, снабжающие их песком. Потребность заводов в песке не
больше производительности карьеров и равна соответственно 40 и 50 тонн.
Количество добываемого песка в каждом из карьеров и стоимость перевозки (в
денежных единицах) одной тонны песка из каждого карьера к заводам представлены
в виде таблицы
|
Карьеры |
Заводы |
Количество добываемого песка |
|
|
А |
В |
|
|
|
С |
2 |
6 |
70 тонн |
|
Д |
5 |
3 |
30 тонн |
Как спланировать снабжение заводов песком,
чтобы затраты были наименьшими?
2.10. Есть два пункта производства однородного продукта и
пункты потребления М, Р и К, которым необходимо 15, 20, 10 единиц этого
продукта. Первый пункт производит 25 единиц продукции, второй пункт – 20
единиц. Стоимость доставки единицы продукции в пункты потребления определена
таблицей:
|
|
М |
Р |
К |
|
1 пункт |
1 |
3 |
5 |
|
2 пункт |
2 |
4 |
3 |
Составить план распределения продукта при
минимальных затратах.
2.11. Имеется два склада готовой продукции и три
потребителя этой продукции. Склад I располагает 10 000 единиц продукции, а
склад II – 5000 единиц продукции. Потребителям нужно соответственно: I –4000
единиц, II – 8000 единиц, III - 3000 единиц. Стоимость доставки единицы
продукции с каждого склада каждому потребителю определена следующей таблицей:
|
|
1 потребитель |
2 потребитель |
3 потребитель |
|
I склад |
3 |
3 |
2 |
|
II склад |
6 |
5 |
1 |
Составить такой план перевозок, который
соответствует минимальной суммарной стоимости.
2.12. На трех складах ( I , II , III ) имеются
соответственно 90, 70, 50 тонн муки, которую надо перевезти в магазины – 1, 2,
3, 4 соответственно в количестве 80, 60, 40, 30 тонн. Необходимо составить
оптимальный план перевозки муки, если стоимость перевозки одной тонны в
магазины 1. 2, 3, 4 со склада I равна соответственно 2, 1, 3, 2 денежных
единицы, со склада II равна соответственно 2, 3, 3, 1 денежных единицы, со склада
III равна соответственно 3, , 3, 2, 1 денежных единицы.
2.13. В некоторой местности в двух пунктах А и В имеется
потребность в дополнительном транспорте. В пункте А требуется 5 дополнительных
автобусов, а в пункте В- 7. Известно, что 3, 4, 5 автобусов могут быть получены
соответственно из гаражей G1, G2, G3.
Как следует распределить эти автобусы между пунктами А и В, чтобы
минимизировать их суммарный пробег? Расстояния от гаражей до пунктов А и В
приведены в таблице:
|
Гараж
|
Расстояния до пунктов |
|
|
А |
В |
|
|
G1 |
3 |
4 |
|
G2 |
1 |
3 |
|
G3 |
4 |
2 |
2.14. Три поставщика одного и того же продукта располагают
в планируемый период следующими запасами этого продукта: первый- 120 условных
единиц, второй- 100 и третий 80 единиц. Этот продукт должен быть перевезен к
трем потребителям, спросы которых соответственно равны 90, 90 и 120 условных
единиц. Приведенная ниже таблица содержит показатели затрат, связанных с
перевозкой продукта из i-го пункта отправления в j-й пункт потребления.
|
Поставщики |
Потребители и их спрос |
Запасы |
||
|
|
А |
Б |
В |
|
|
I |
7 |
6 |
4 |
120 |
|
II |
3 |
8 |
5 |
100 |
|
III |
2 |
3 |
7 |
80 |
|
Спрос |
90 |
90 |
120 |
|
Требуется перевезти продукт с минимальными
затратами.
3. Разные задачи
3.1. В малом предприятии по пошиву одежды освоили пошив
двух новых видов плащей и костюмов. На их пошив требуется два вида материала I
и II . В запасе имеется материала I –
|
Изделие |
I |
II |
|
Плащ |
1,5 |
1 |
|
Костюм |
2,7 |
0,8 |
От продажи одного плаща предприятие получает
доход в 9 денежных единиц, а от продажи костюма – 8 денежных единиц. Сколько
плащей и костюмов следует сшить предприятию, чтобы доход был наибольшим?
3.2. Имеются два вида сырья А и В. Для производства двух
видов продуктов I и II . Продукт I состоит из двух единиц сырья А и из 5 единиц
сырья В. Продукт I I состоит из 3 единиц сырья А и из 3 единиц сырья В. Доход
от производства одной единицы продукта I составляет 4 у.е., а от одной единицы
продукта II - 5 у.е.. Сколько единиц каждого продукта нужно производить, чтобы
максимизировать прибыль, если в распоряжении находится не более 500 единиц
сырья А и не более 750 единиц сырья В?
3.3. Для производства продукции вида I и II используется
5 видов оборудования A , B , C , D , E . На производство одной штуки продукции
I требуется занять в течение единицы времени 0, 2, 1, 5, 3 единиц
соответственно A , B , C , D , E . На производство одной штуки продукции II
требуется занять в течение единицы времени 1, 1, 0, 2, 1 единиц оборудования A
, B , C , D , E . Имеется оборудование по группам: A - 19, B -
3.4. Для откорма животных на ферме в их еженедельный
рацион необходимо включать не менее 32 единиц питательного вещества А и 23
единицы питательного вещества В. Для откорма используют два вида кормов. Данные
о содержании питательных веществ и стоимость весовой единицы каждого из кормов
помещены в таблице:
|
В одной весовой единице |
А |
В |
Стоимость одной весовой единицы |
|
Корма 1 |
4 у.е. |
3 у.е. |
20 ден. ед. |
|
Корма 2 |
3 у.е. |
2 у.е. |
25 ден. ед. |
Составить наиболее дешевый рацион, при
котором каждое животное получило бы необходимое количество питательных веществ.
3.5. На фабрике освоили производство столов и тумбочек для
торговой сети. Для их изготовления имеются два вида древесины: I –
|
Изделие |
I |
II |
|
Стол |
0,18 |
0,08 |
|
Тумбочка |
0,09 |
0,29 |
От производства одного стола фабрика
получает чистый доход в 1,1 у.е., а от производства одной тумбочки – 0,7 у.е.
Определить, сколько столов и тумбочек должна производить фабрика из имеющегося
материала, чтобы обеспечить наибольший доход.
3.6. Для сохранения здоровья и работоспособности человек
должен потреблять в сутки питательных веществ В1 не менее 4 единиц, В2 – не
более 6 единиц, В3 – 9 единиц, В4 – 6 единиц. Имеются два вида пищи: I и II . В
3.7. Содержание витаминов А и С в одном килограмме
фруктов задано следующей таблицей:
|
Фрукты |
Витамины |
|
|
|
А (мг) |
С (мг) |
|
Вишня |
3 |
150 |
|
абрикосы |
24 |
75 |
Сколько граммов вишни и сколько граммов
абрикосов следует включить в дневной рацион, чтобы в нем оказалось не менее 6
мг витамина А и не менее 75 мг витамина С при минимальных затратах, если
3.8. Изготовление продукции двух видов А и В требует
использования 4 видов сырья I , II , III и IV . Запас сырья ограничен и
составляет соответственно 19, 13, 15 и 19 единиц. На производство 1 единицы
продукции вида А необходимо 2 единицы сырья I , 2 - II , 0 - III и 3 - IV . На
производство 1 единицы продукции вида В необходимо 3 единицы сырья I , 1 - II ,
3 - III и 0 - IV . Доход от продажи 1 единицы продукции А составляет 7 денежных
единиц, от продажи 1 единицы продукции В составляет 5 денежных единиц.
Составить такой план выпуска продукции, при котором доход предприятия от
реализации продукции оказался бы максимальным.
3.9. Некоторому заводу требуется составить оптимальный по
реализации план выпуска двух видов изделий при определенных возможностях 4
видов машин. План выпуска должен быть таким, чтобы от реализации выпущенной
продукции завод получил бы наибольшую прибыль. Оба вида изделий последовательно
обрабатываются этими машинами. План должен учитывать, что 1 вид машин ежедневно
может обрабатывать эту продукцию в течение 18 часов, 2-й - 12 часов, 3-й – 12
часов, 4-й – 9 часов. В следующей таблице указано время, необходимое для
обработки каждого изделия этих двух видов указанными типами машин. Нуль
означает, что изделие машинами данного вида не обрабатывается. Завод от
реализации одного изделия вида I получает 4 у.е., а от реализации одного
изделия вида II – 6 у.е. прибыли.
|
Виды изделий |
Виды машин |
||||
|
|
1-й |
2-й |
3-й |
4- й |
|
|
I |
1 |
0,5 |
1 |
0 |
|
|
II |
1 |
1 |
0 |
1 |
|
|
Возможное время работы машин |
18 |
12 |
12 |
9 |
|
3.10. На участке работает 20 человек. Каждый из них в
среднем за год работает 1800 часов. Выделенные ресурсы: 32 т металла, 54 тыс.
квтч электроэнергии. План по реализации продукции: не менее 2 тыс. изделий А и
не менее 3 тыс. изделий В. На выпуск 1 тыс. изделий А затрачивается 3 т
металла, 3 тыс. Квтч электроэнергии и 3 тыс. часов рабочего времени. На выпуск
1 тыс. изделий В затрачивается 1 т металла, 6 тыс. Квтч электроэнергии и 3 тыс.
часов рабочего времени. От реализации 1 тыс. изделий А завод получает прибыль
500 тыс. у.е., от реализации 1 тыс. изделий В – 700 тыс. у.е. Выпуск какого
количества изделий А и В (в тыс. штук) надо запланировать, чтобы прибыль от их
реализации была наибольшей?
3.11. Требуется составить смесь, содержащую три химических
вещества А, В, С. Известно, что составленная смесь должна содержать вещества А
не менее 6 единиц, вещества В не менее 8 единиц, вещества С не менее 12 единиц.
Вещества А, В, С содержатся в двух видах продуктов I , II в концентрации,
указанной в таблице:
|
продукты |
Химические вещества |
||
|
|
А |
В |
С |
|
I |
2 |
1 |
3 |
|
II |
1 |
2 |
4 |
Стоимость единицы продукта I стоит 200 у.е.,
единицы II - 300 у.е. Смесь надо составить так, чтобы стоимость используемых
продуктов была наименьшей.
3.12. Изделия А и В в процессе своего изготовления
последовательно проходят три станка I , II, III. На обработку изделия А станок
I затрачивает 18 часов, второй – 12 часов, третий – 6 часов. На обработку
изделия В станок I затрачивает 6 часов, второй – 12 часов, третий – 12 часов.
Станок I может работать не более 84 часов, второй – не более 72 часов, а третий
– не более 54 часов. От реализации единицы изделия А завод получает прибыль 180
денежных единиц, от реализации единицы изделия В завод получает прибыль 120 денежных
единиц. Требуется спланировать выпуск изделий А и В так, чтобы прибыль от их
реализации была наибольшей.
3.13. Фирма производит два продукта А и В, рынок сбыта
которых неограничен. Каждый продукт должен быть обработан каждой машиной I, II,
III. Время обработки в часах для каждого из изделий А и В приведено ниже
|
|
I |
II |
III |
|
А |
0.5 |
0.4 |
0.2 |
|
В |
0.25 |
0.3 |
0.4 |
Время работы машин I, II, III соответственно
40, 36 и 36 часов в неделю. Прибыль от изделий А и В составляет соответственно
5 и 3 доллара. Фирме надо определить недельные нормы выпуска изделий А и В,
максимизирующие прибыль.
3.14. Фирма занимается составлением диеты, содержащей по
крайней мере 20 единиц белков, 30 единиц углеводов, 10 единиц жиров и 40 единиц
витаминов. Как дешевле всего достичь этого при указанных в таблице ценах (в
рублях) на
