Применение MS Excel в решение логистических задач

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

Оптимальным считается маршрут, по которому возможно доставить логистический объект, в кратчайшие сроки (или предусмотренные сроки) с минимальными затратами, а также с минимальным вредом для объекта доставки.

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

Транспортная задача является частным типом задачи линейного программирования и формулируется следующим образом: имеется m пунктов отправления (или пунктов производства) , в которых сосредоточены запасы однородных продуктов в количестве единиц. Имеется n пунктов назначения (или пунктов потребления) , потребность которых в указанных продуктах составляет единиц. Известны также транспортные расходы С_ij, связанные с перевозкой единицы продукта из пункта A_i в пункт В_j, ; .

Общая постановка транспортной задачи состоит в определении оптимального плана перевозок некоторого однородного груза из т пунктов отправления в n пунктов назначения .

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

Обозначим C_ij тарифы перевозки единицы груза из i-го пункта отправления в j-й пункт назначения, через a_i — запасы груза в i -ом пункте отправления, через b_j — потребности в грузе в j-ом пункте назначения, а через x_i — количество единиц груза, перевозимого из i-го пункта отправления в j-й пункт назначения. Тогда математическая модель транспортной задачи состоит в определении минимального значения функции:

, (1)

при условиях

, j = 1,2,…,n, (2)

, i = 1,2,…,m (3)

, , . (4)

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

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

, (5)

то такая транспортная задача называется закрытой или сбалансированной. В противном случае — открытой или несбалансированной.

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

,

вводится фиктивный n+1-й потребитель (или пункт назначения) с потребностью, равной:

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

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

вводится фиктивный m+l-й пункт отправления с запасом груза в нем, равным:

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

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

Рассмотрим алгоритм решения таких задач на конкретном примере:

Продукты, находящиеся на четырех складах — С1, С2, С3, С4 необходимо развезти по пяти магазинам — Ml, М2, МЗ, М4 и М5. Потребности этих магазинов в продуктах равны соответственно 15, 14, 25, 5 и 9 ед. Запасы продуктов на складах С1, С2, СЗ, С4 составляют соответственно 23, 15, 45, 15 ед. Тарифы по доставке продуктов (руб./ед. груза) приведены в таблице:

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

Так как данная задача относится к задачам линейного программирования, то решение начинается с построения математической модели:

При данном плане перевозок суммарная их стоимость составит (целевая функция):

Дальнейшее решение задачи ведётся в MS Excel.

Составляется таблица с исходными данными (рис. 1)

Затем заполняем столбец с ограничениями, при помощи функции СУММ (для ячейки Н4 =СУММ (В4:F4)), а формулу СУММПРОИЗВ мы вводим в строке с целевой функцией (=СУММПРОИЗВ (B4:F8;B15:F19)).

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

В поле изменяя ячейки выбираем ячейки с неизвестными переменными (B4:F8), а поле ограничения вводим наложенные нами ограничения.

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

После чего получаем ответ к задаче (рис. 2)

Решив математическую модель, пришли к выводу, что

из пункта 1-го необходимо поставлять 8 ед. продукции на 2-й склад, из 2-го необходимо 6 ед. на 2-й и 9 на 5-й, из 3-го 15 ед. на 1-й, 25 на 3-й, 5 на 4-й, из 4-го продукции не требуется, при этом затраты на перевозку составят 202 ден. ед.

Как видно из примера, решение задачи с помощью MS Excel быстрое и удобное, так как:

• создав один раз таблицу, её можно применять для задач такого же типа изменяя только исходные данные;
• практически все необходимые для решения задачи формулы уже представлены в MS Excel;
• решение задачи занимает в несколько раз меньше времени, нежели её же решение вручную;
• точность решения гораздо выше, чем вручную, а погрешности сведены к минимуму.