Транспортные задачи в Excel (стр. 1 из 2)

В настоящее время в условиях жесткой конкуренции автомобильным компаниям приходится бороться за каждый рубль прибыли, поэтому вопрос об уменьшении издержек производства становится первостепенным. И, конечно же, рациональное размещение производственных объектов играет здесь ключевую роль [2].

Так, например, перед ООО «MG Auto» стоит реальная задача: стоит ли изменить действующую на сегодняшний день схему транспортировки автомобилей с заводов до распределительных центрах в Денвере и Майами или же действующая схема наиболее эффективна? Таким образом, в этой работе делается попытка показать пути снижения общих затрат предприятия путем снижения транспортных издержек.

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

Глава 1

Классическая транспортная задача

Транспортная задача является частным видом линейной оптимизационной задачи. Наиболее часто транспортная задача представляется следующим образом: имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i-гo пункта производства в j-й центр распределения cji приведена в таблице, где под строкой понимается пункт производства, а под столбцом - пункт распределения. Кроме того, в этой таблице в i-той строке указан объем производства в i-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения[6]. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.

Для математического описания транспортной задачи введем переменные x_ij, обозначающие объемы поставок товара от i-го источника к j-му стоку. В этом случае x_i1+x_i2+...+x_in — общий объем поставок товара от i-го источника, т.е. мощность этого источника; x₁_j+x₂_j+...+x_mj — общий объем поставок товара к j-му стоку, т.е. мощность этого стока; c₁₁x₁₁+c₁₂x₁₂+...+c_mnx_mn — суммарная стоимость перевозок товара от источников к стокам[7]. С учетом этого рассматриваемая задача может быть представлена в следующем виде:

Задача состоит в определении неизвестных величин x_ij, минимизирующих суммарные транспортные расходы и удовлетворяющих ограничениям, накладываемым на объемы грузов в пунктах отправления (предложение) и пунктах назначения (спрос).

1.1. Решение классической сбалансированной транспортной задачи в Excel

Рассмотрим решение классической транспортной задачи.

Автомобильная компания MG Auto имеет три завода в Лос-Анджелесе, Детройте и Новом Орлеане и два распределительных центра в Денвере и Майами. Объемы производства заводов компании в следующем квартале составят соответственно 1000, 1500 и 1200 автомобилей. Ежеквартальная потребность распределительных центров составляет 2300 и 1400 автомобилей. Расстояние (в милях) между заводами и распределительными центрами приведены в Таблице 1.

Таблица 1

Поставщики	Потребители
Поставщики	Денвер	Майами
Лос-Анджелес	1000	2690
Детройт	1250	1350
Новый Орлеан	1275	850

Транспортная компания оценивает свои услуги в 8 центов за перевозку одного автомобиля на одну милю. В результате получаем, представленную в Таблице 2, стоимость перевозок (с округлением до доллара) по каждому маршруту.

Таблица 2

Поставщики	Потребители
Поставщики	Денвер	Майами
Лос-Анджелес	$80	$215
Детройт	$100	$108
Новый Орлеан	$102	$68

Решение:

В данной задаче общий объем произведенных автомобилей (S=1000+1500+1200=3700) равен суммарному спросу распределительных центров (D=2300+1400=3700).

Задачу можно решить с помощью транспортной таблицы. Исходные данные для решения классической транспортной задачи целесообразно представить в виде двух таблиц, в первой из которых представлены значения стоимости перевозок единицы товара c_ij от i-го поставщика к j-му потребителю (см. Рисунок 1).

Рисунок 1

Во второй таблице представлены: значения S_i предложения каждого i-го поставщика; значения D_j спроса каждого j-го потребителя; переменные x_ij, первоначально принимающие нулевые значения; вспомогательная строка и вспомогательный столбец "Сумма" (см. Рисунок 2).

Рисунок 2

Целевая ячейка C17 должна содержать формулу, выражающую целевую функцию: = СУММПРОИЗВ(C3:D5; C12:D14). (Рисунок 3)

Рисунок 3

Используя меню СервисÞПоиск решения открываем диалоговое окно Поиск решения (см. Рисунок 4), в котором устанавливаем целевую ячейку равной минимальному значению, определяем диапазон изменяемых ячеек и ограничения и запускаем процедуру вычисления, щелкнув по кнопке Выполнить [3].

Рисунок 4

Рисунок 5

Оптимальное решение задачи (Рисунок 5). Оно предполагает перевозку 1000 автомобилей из Лос-Анджелеса в Детройт, 1300 автомобилей – из Детройта в Денвер, 200 автомобилей – из Детройта в Майами и 1200 – из Нового Орлеана в Майами. Минимальная стоимость перевозок составляет 313200 долларов.

1.2. Решение классической несбалансированной транспортной задачи в Excel

Несбалансированная классическая транспортная задача – это когда суммарный объем предложений (грузов, имеющихся в пунктах отправления) не равен общему объему спроса на товары (грузы), запрашиваемые пунктами назначения. В этом случае, при решении классической транспортной задачи вводят фиктивные пункты назначения или отправления [4]. В Excel несбалансированная транспортная задача решается путем изменения ограничений по спросу (если спрос превышает предложение) или по предложению (если предложение превышает спрос), т.е. система ограничений будет иметь вид:

или

Рассмотрим решение несбалансированной транспортной задачи в Excel

В рамках модели компании MG Auto предположим, что завод в Детройте уменьшил выпуск продукции до 1300 автомобилей (вместо 1500, как было ранее). В этом случае общее количество произведенных автомобилей (=3500) меньше общего количества заказанных (=3700) автомобилей. Таким образом, очевидно, что часть заказов распределительных центров Денвера и Майами не будет выполнена.