Транспортные задачи в Excel

Рисунок
5

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

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

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

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

или

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

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

Решение:

Представлено на Рисунке 6 – Рисунке 8 (аналогично предыдущему).

Рисунок
6

Изменения имеют место в ограничениях (выделенная строка):

Рисунок 7

Рисунок
8

В таблице-плане оптимального закрепления на Рисунке 8 представлено оптимальное решение. Решение показывает, что спрос распределительного центра Денвера будет удовлетворен полностью, а в распределительный центр Майами из заказа в 1400 автомобилей не будет поставлено 200 автомобилей.
Глава 2.

Транспортная задача с промежуточными пунктами

Одно практически важное обобщение классической транспортной задачи связано с учетом возможности доставки товара от i
-го источника к j
-му стоку по маршруту, проходящему через некоторый промежуточный пункт

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

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

[7].

Пусть J

— множество номеров складов, на которые товар может быть доставлен с k
-го склада, а I

— множество номеров складов, с которых товар может быть доставлен на k
-й склад. Tk

— величина чистого запаса

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

2.1. Решение транспортной задачи с промежуточными пунктами в Excel

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

Найти решение транспортной задачи с промежуточными пунктами, в рассмотренном выше примере, если стоимость перевозки единицы товара составляет: c
12
=3 у.е., c
23
=7 у.е., c
25
=3 у.е., c
43
=6 у.е., c
45
=4 у.е., c
47
=5 у.е., c
54
=5 у.е., c
56
=3 у.е., c
67
=5 у.е., c
78
=2 у.е.

Решение:

На Рисунке 9 представлены таблицы Стоимость перевозки единицы товара
и План перевозок товара между складами
, сформированные на рабочем листе Excel. Здесь в таблице Стоимость перевозки единицы товара
мы видим, что если между отдельными складами отсутствует возможность перевозки товара, то в соответствующие ячейки таблицы (выделенные темным фоном) заносится любое большое число (в данном случае 100). Для того, чтобы найти в таблице Плана перевозок товара между складами
объем предложения и объем спроса, определим объем буфера B
по следующему правилу:

B
= общий объем предложения = S
1
+
S
4
=
10+2 = 12 ед.

или

B
= общий объем спроса = D
3
+
D
6
+
D
8
=
3+1+8 = 12 ед.

Для остальных промежуточных пунктов объемы предложения Si
или объемы спроса Dj
равны нулю.

В целевую ячейку, в данном случае C
23
, необходимо занести формулу: =СУММПРОИЗВ(
C
4:
I
9;
C
15:I20)
.

Рисунок
9

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

Рисунок
10

Результат решения данной задачи представлен на Рисунке 11.

Рисунок
11

Здесь мы видим, что оптимальный план перевозок товара между складами следующий:

- со склада 1 товар в количестве трех единиц транзитом через склад 2 отправлен на склад 3, который является истинным пунктом назначения;

- со склада 1 товар в количестве семи единиц транзитом через склады 2 и 5 отправлен на склад 6, где одна единица товара используется для пополнения запаса на этом складе;

- со склада 6 товар в количестве шести единиц транзитом через склад 7 отправлен на склад 8, который также является истинным пунктом назначения;

- со склада 4 избыток товара в количестве четырех единиц отправлен на склад 8 транзитом через склад 7.

Стоимость перевозок при этом минимальна и составляет 149 условных денежных единиц.
Заключение

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

Была найдена минимальная стоимость перевозок автомобилей в указанные города, которая составила 313200 долларов.

Отсюда следует, что воспользовавшись новой схемой транспортировки автомобилей можно сэкономить до 40% транспортных расходов. Таким образом, сэкономленными средствами можно поступить по-разному:

1) за счет этих средств увеличить прибыль предприятия, и воспользоваться ею для каких-либо нужд предприятия;

2) либо снизить стоимость автомобилей на заводах, что может принести еще больше прибыли.

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

Список литературы

1. Дубина А.Г. Excel для экономистов и менеджеров/ А.Г. Дубина, С.С. Орлова, И.Ю. Шубина, А.В. Хромов. – СПб.: Питер, 2004. – 295 с.

2. Кузьмин В. Microsoft Office Excel 2003. Учебный курс/ В. Кузьмин. – СПб.: Питер, 2004. – 493 с.

3. Мур Д. Экономическое моделирование в Microsoft Excel/ Д. Мур, Ларри Р. Уэдерфорд и др. – М.: Издательский дом «Вильямс», 2004. – 1024 с.

4. http://www.mathelp.spb.ru/lp_on_line.htm

5. http://www.math.mrsu.ru/text/method/usl_razresh.htm

6. http://www.iqlib.ru/book/preview/BDAC41E5C58241D6949C90F37E3F4AC

7. http://referat-kursovaya.repetitor.info/Решение_транспортной_задачи