Пример решения транспортной задачи в Excel

Предприятия А1, А2, А3 и А4 производят однородную продукцию а1, а2, а3 и а4, соответственно. В условных единицах – 246, 186, 196 и 197. Затем товар поступает в пять пунктов назначения: В1, В2, В3, В4 и В5. Это потребители продукции. Они готовы ежедневно принимать 136, 171, 71, 261 и 186 единиц товара.

Стоимость перевозки единицы продукции с учетом удаленности от пункта назначения:

Производители	Потребители	Объем производства
	В1	В2	В3	В4	В5
А1	4,2		3,35		4,65
А2		3,85	3,5	4,9	4,55
А3	4,75	3,5	3,4	4,5	4,4
А4			3,1	5,1	4,4
Объем потребления

Задача: минимизировать транспортные расходы по перевозке продукции.

1. Проверим, является ли модель транспортной задачи сбалансированной. Для этого все количество производимого товара сравним с суммарным объемом потребности в продукции: 246 + 186 + 196 + 197 = 136 + 171 + 71 + 261 + 186. Вывод – модель сбалансированная.

2. Сформулируем ограничения: объем перевозимой продукции не может быть отрицательным и весь товар должен быть доставлен к пунктам назначения (т.к. модель сбалансированная).

3. Введем стоимость перевозки единицы продукции в рабочие ячейки Excel.

4. Введем формулы для расчета суммарной потребности в товаре. Это будет первое ограничение.

5. Введем формулы для расчета суммарного объема производства. Это будет второе ограничение.

6. Вносим известные значения потребности в товаре и объема производства.

7. Вводим формулу целевой функции СУММПРОИЗВ(B3:F6; B9:F12), где первый массив (B3:F6) – стоимость единицы перевозки товаров. Второй (B9:F12) – искомые значения транспортных расходов.

8. Вызываем команду «Поиск решения» на закладке «Данные». Заполняем диалоговое окно. В графе «Установить целевую ячейку» - ссылка на целевую функцию. Ставим галочку «Равной минимальному значению». В поле «Изменяя ячейки» - массив искомых критериев. В поле «Ограничения»: искомый массив >=0, целые числа; «ограничение 1» = объему потребностей; «ограничение 2» = объему производства.

9. Нажимаем «Выполнить». Команда подберет оптимальные переменные при заданных ограничениях.

Варианты ИДЗ №1

Задание 1

Построить математическую модель с использованием заданной экономической постановки.

1.1. Мебельная фабрика выпускает столы, стулья, бюро и книжные шкафы. При изготовлении этих товаров используются два различных типа досок, причем фабрика имеет в наличии 1500 м досок I типа и 1000 м досок II типа. Кроме того, задан объём трудовых ресурсов в количестве 800 чел.-ч.

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

1.2. На производство одной партии тетрадей, дневников и наборов писчей бумаги требуется соответственно 2400, 2400 и 4800 кг сырья. При этом затраты рабочего времени на производство одной партии тетрадей и дневников составляют 0,18 и 0,36 машино-часов. На производстве одной партии писчей бумаги заняты специальные автоматы в течение 3,25 часа. Всего для производства бумажной продукции завод сможет использовать не более 312 т сырья. Основное оборудование может быть занято в течение 21,6 машино-часов, а автоматы по производству писчей бумаги - в течение 16,25 ч. Прибыль от реализации одной партии тетрадей, дневников и наборов писчей бумаги соответственно равна 300, 220 и 400 руб. Завод должен ежедневно производить не более 100 партий тетрадей. На производство другой продукции ограничений нет. Требуется определить, какую продукцию и в каком количестве следует ежедневно производить заводу, чтобы получаемая прибыль была максимальной.

1.3. На предприятии имеется склад вместимостью 80 м2, половину которого занимает сырьё для производства продукции. Предприятие имеет финансовые проблемы и поэтому должно получить максимальную прибыль в течение ближайших 3-х дней. Аналитический отдел прогнозирует ежедневный рост цен на производимую предприятием продукцию, получаемую из сырья, занимающего 1 м2 места на складе: 1 день – 1000 руб., 2 день – 1200 руб., 3 день – 1500 руб. После одного дня работы предприятие должно загрузить всю готовую продукцию на склад, т.к. заказчики забирают её только утром на следующий день. Готовая продукция, полученная из сырья, размещающегося на 1 м2, занимает 3 м2 места на складе. Оптимизировать ежедневный выпуск продукции, а соответственно и ежедневное потребление сырья, таким образом, чтобы за 3 дня получить максимальную прибыль.

1.4. Фирма производит некоторую продукцию и осуществляет ее рекламу двумя способами: посредством радиовещания и посредством телевидения. Стоимость проведения рекламы на телевидении составляет 100 условных денежных единиц (уде) за 1 минуту; стоимость проведения рекламы на радио – 5 уде за 1 минуту. Фирма готова выделить средства на рекламу в размере 1000 уде в месяц. Она также планирует рекламировать продукцию по радио, по крайней мере, в 2 раза чаще, чем по телевидению. Опыт показал, что 1 минута телерекламы приносит в 25 раз больший сбыт продукции, а значит и получение прибыли, чем 1 минута радиорекламы.

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

1.5. На склад ковровых дорожек поступил заказ на куски дорожек размерами 1,2; 1,8 и 2,9 м, в количествах соответственно не менее: 16 шт., 20 шт. и 12 шт. Склад имеет в достаточном количестве дорожек в рулонах по 8 м. Необходимо провести раскрой рулонов таким образом, чтобы используемое количество рулонов было минимальным.

1.6. На приобретение нового оборудования для открытия филиала фирма имеет в наличии 18 тыс.руб., причем наличная производственная площадь составляет 28 м2. Фирма может себе позволить содержать штат из 16 работников для обслуживания данного оборудования. На рынке представлено 2 вида подобного оборудования: более мощное, стоимостью 4 тыс. руб., требующее 3 человека для обслуживания и производственную площадь 5 м2. Данный вид оборудования позволяет производить 4 тыс. ед. продукции за смену. Второй вид оборудования, стоимостью 2,5 тыс. руб., требует 2 человека для обслуживания и производственную площадь 7 м2. Производственная мощность этого вида оборудования составляет 3 тыс. ед. продукции за смену.

Найти такой вариант приобретения оборудования, при котором филиал будет выпускать наибольшее количество продукции.

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

Резервы			Потребности строительных объектов, т
карьеров, т
	Расстояние от карьеров до строительных объектов, км

Составить план перевозок песка, обеспечивающий минимум перевозок в тоннокилометрах.

 

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

	Тип оборудования	Затраты времени на производство одного изделия, ч./изд.	Фонд
	времени,
		1 вид	2 вид	3 вид	4 вид	5 вид	ч.
	1 тип
	2 тип
	3 тип
	Минимальный план
	выпуска изделий, шт.
	Прибыль от единицы
	продукции, грн./изд.

Определить план выпуска продукции, обеспечивающий максимум прибыли.

 

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

	Резервы постав-		Потребность торговых предприятий, т
	щиков, т
		Стоимость перевозки 1 т груза, руб./т

Составить такой план перевозок муки от поставщиков потребителям, чтобы суммарные затраты на перевозку были минимальными.

 

1.10. Необходимо оптимальным образом распределить пять экскаваторов для выполнения работ на каждом из пяти строительных объектов. Себестоимость выполнения земляных работ указана в таблице.

	Экскаватор	Строительный участок

Задание 2

Используя MS Excel, решить задачу своего варианта. Отчет оформить на рабочем листе Excel.

Вариант 1

На предприятии для производства запасных частей для автомобилей используется три вида ресурсов. Выпускаются три вида запасных частей. Организация производства на предприятии характеризуется таблицей:

Ресурсы	Расход материалов на производство одной запасной части, кг	Запас ресурсов, кг
I
II		-
III	-
Прибыль от реализации 1 запасной части (д.е.)

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

Вариант 2

Кондитерская фабрика для производства трех видов карамели А,В и С использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья каждого вида на производство 1 т карамели данного вида приведены в таблице. В ней же указано общее количество сырья каждого вида, которое может быть использовано фабрикой, а также приведена прибыль от реализации 1 т карамели данного вида.

Вид сырья	Нормы расхода сырья (т) на 1 т карамели	Запас сырья (т)
А	В	С
Сахарный песок	0,5	0,3	0,6
Патока	0,2	0,6	0,2
Фруктовое пюре	0,3	0,1	0,2
Прибыль от реализации 1т продукции (руб.)

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

Вариант 3

Для поддержания нормальной жизнедеятельности человеку ежедневно необходимо потреблять не менее 118 г белков, 56 г жиров, 500 г углеводов, 8 г минеральных солей. Количество питательных веществ, содержащихся в 1 кг каждого вида потребляемых продуктов, а также цена 1 кг каждого из этих продуктов приведены в следующей таблице:

Питательные вещества	Содержание (г) питательных веществ в 1 кг продуктов
мясо	Рыба	молоко	масло	сыр	крупа	картофель
Белки
Жиры
Углеводы
Минеральные соли
Цена 1 кг продуктов (руб)			2,8

 

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

Вариант 4

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

Ресурсы	Нормы затрат ресурсов на одно изделие	Запас ресурсов
стол	шкаф
Древесина первого вида (м3)	0,2	0,1
Древесина второго вида(м3)	0,1	0,3
Трудоемкость (человеко-часов)	1,2	1,5	371,4
Прибыль от реализации 1 изделия (руб)

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

Вариант 5

Для изготовления трех видов изделий А, В и С используется токарное, фрезерное, сварочное и шлифовальное оборудование. Затраты времени на обработку одного изделия для каждого из типов оборудования указаны в таблице. В ней же указан фонд рабочего времени каждого из типов используемого оборудования, а также прибыль от реализации одного изделия данного вида.

Тип оборудования	Затраты времени на обработку одного изделия вида	Общий фонд рабочего времени
А	В	С
Фрезерное
Токарное
Сварочное
Шлифовальное
Прибыль

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

Вариант 6

При откорме животных каждое животное ежедневно должно получить не менее 60 ед. питательного вещества А, не менее 50 ед. вещества В и не мене 12 ед. вещества С. Указанные питательные вещества содержат три вида корма. Содержание единиц питательных веществ в 1 кг каждого из видов корма приведено в таблице.

Питательные вещества	Количество единиц питательных веществ в 1кг корма вида
I корма	II	III
А
В
С

Составить дневной рацион, обеспечивающий получение необходимого количества питательных веществ при минимальных денежных затратах, если цена 1 кг корма I вида составляет 9 ден.ед., II вида – 12 ден.ед., III вида – 10 ден.ед.

Вариант 7

На мебельной фабрике из стандартных листов фанеры необходимо вырезать заготовки трех видов в количествах, соответственно равных 24, 31 и 18 шт. Каждый лист фанеры может быть разрезан на заготовки двумя способами. Количество получаемых заготовок при данном способе раскроя приведено в таблице. В ней же указана величина отходов, которые получаются при данном способе раскроя одного листа фанеры.

 

Вид заготовки	Количество заготовок (шт.) при раскрое по способу
А	В
I
II
III
Величина отходов (см2)

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

Вариант 8

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

Вид корма	Кол-во единиц корма, которое ежедневно должны получать	Общее кол-во корма (кг)
лисица	песец
I
II
III
Прибыль от реализации 1 шкурки (у.е.)

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

Вариант 9

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

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

Артикул ткани	Нормы расхода ткани (м) на одно изделие вида	Общее кол-во ткани (м)
А	В	С	D
I		-
II	-
III			-
Цена одного изделия (у.е.)

Вариант 10

При откорме каждое животное должно получить не менее 9 ед.белков, 8 ед. углеводов и 11 ед. протеина. Для составления рациона используют два вида корма, представленных в следующей таблице:

Питательные вещества	Количество единиц питательных веществ на 1 кг
Корма 1	Корма 2
Белки
Углеводы
протеин

Стоимость 1 кг корма первого вида – 4 ден.ед., второго – 6 ден.ед.

Составить дневной рацион питания, имеющий минимальную стоимость.

Вариант 11

Хозяйство располагает следующими ресурсами: площадь – 100 ед., труд – 120 ед., тяга – 80 ед. Хозяйство производит четыре вида продукции П₁, П₂, П_3, П₄. Организация производства характеризуется следующей таблицей:

ресурсы	Затраты на 1 ед. продукции
П1	П»	П3	П4
Площадь
Труд
Тяга
Доход от ед.продукции

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

Вариант 12

Цех выпускает трансформаторы двух видов. Для изготовления трансформаторов обоих видов используются железо и проволока. Общий запас железа – 3 т, проволоки – 18 т. На один трансформатор первого вида расходуются 5 кг железа и 3 кг проволоки, а на один трансформатор второго вида расходуются 3 кг железа и 2 кг проволоки. За каждый реализованный трансформатор первого вида завод получает прибыль 3 д.е., второго – 4 д.е. Составьте план выпуска трансформаторов, обеспечивающий заводу максимальную прибыль.

Вариант 13

Из трех продуктов – I, II, III составляется смесь. В состав смеси должно входить не менее 6 ед. химического вещества А, 8 ед. – вещества В и не менее 12 ед. вещества С. Структура химических веществ приведена в таблице:

 

 

Химические вещества	Содержание химического вещества в 1 ед. продукции
I	II	III
А
В			1,5
С
Стоимость 1 ед. продукции			2,5

Составить наиболее дешевую смесь.

Вариант 14

Цех выпускает три вида деталей – А, В, С. Каждая деталь обрабатывается тремя станками. Организация производства в цехе характеризуется следующей таблицей:

Станок	Длительность обработки детали, мин.	Фонд времени, час
А	В	С
I
II
III
Отпускная цена за одну деталь

Составить план загрузки станков, обеспечивающий цеху получение максимальной прибыли.

Вариант 15

На заводе выпускают изделия четырех типов. От реализации 1 ед. каждого изделия завод получает прибыль соответственно 2, 1, 3, 5 д.е. на изготовление изделий расходуются ресурсы трех типов: энергия, материалы, труд. Данные о технологическом процессе приведены в таблице:

Ресурсы	Затраты ресурсов на единицу изделия	Запасы ресурсов,ед.
I	II	III	IY
Энергия
Материалы
Труд

Спланировать производство изделий так, чтобы прибыль от их реализации была наибольшей.

Задание 3