Пример решения транспортной задачи в 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	4	3,35	5	4,65	246
А2	4	3,85	3,5	4,9	4,55	186
А3	4,75	3,5	3,4	4,5	4,4	196
А4	5	3	3,1	5,1	4,4	197
Объем потребления	136	171	71	261	186

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

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. Нажимаем «Выполнить». Команда подберет оптимальные переменные при заданных ограничениях.

5 Варианты идз №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. Пять строительных объектов используют песок, добываемый в трех карьерах. Суточная производительность каждого карьера, потребность в песке на объектах и расстояние от карьеров до потребителей (в километрах) представлены в таблице.

Резервы			Потребности строительных объектов, т
	300		200	400	600		300
карьеров, т
		Расстояние от карьеров до строительных объектов, км
500	2		4	3	8		1
400	5		6	4	7		2
900	1		3	5	4		7

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

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

	Тип оборудования	Затраты времени на производство одного изделия, ч./изд.					Фонд
							времени,
		1 вид	2 вид	3 вид	4 вид	5 вид	ч.
	1 тип	4	3	5	1	4	2000
	2 тип	2	1	3	6	2	2500
	3 тип	4	3	6	2	1	1800
	Минимальный план	800	1200	400	950	1000
	выпуска изделий, шт.
	Прибыль от единицы	7	6	4	3	5
	продукции, грн./изд.

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

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

	Резервы постав-		Потребность торговых предприятий, т
		50	30	40	50
	щиков, т
			Стоимость перевозки 1 т груза, руб./т
	70	3	4	2	1
	60	1	5	4	3
	40	4	1	1	2

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

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

	Экскаватор	Строительный участок
		1	2	3	4	5
	1	3	4	2	7	5
	2	4	5	6	2	3
	3	8	6	5	4	4
	4	2	3	8	5	6
	5	9	7	2	6	2