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

Обновлено: 20.11.2024

Логистика — важная функция в обрабатывающей промышленности, поскольку она влияет на общую эффективность и конкурентоспособность вовлеченных фирм. Подготовка оптимальных планов транспортировки может рассматриваться как отправная точка в достижении эффективности управления логистикой. Различные инструменты и методы, связанные с промышленным проектированием и исследованием операций, используются для улучшения планирования логистики и транспортировки. В этой статье объясняется, как можно решить базовую транспортную задачу с помощью довольно распространенного инструмента анализа Microsoft Excel. Статья предназначена для специалистов-практиков в области логистики и представлена без углубления в теорию и математические формулировки, связанные с проблемой.

Предположим, что у компании есть три завода в точках X, Y и Z, которые производят определенный тип продукта. Этот продукт должен быть доставлен четырем покупателям в четырех разных точках A, B, C и D. Нам известны затраты на транспортировку единицы продукции от каждой фабрики к каждому покупателю. Стоимость перевозки этой единицы продукции (в индийских рупиях) представлена в таблице 1

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

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

Общая стоимость. Наша цель – спланировать транспортировку, минимизировав общую стоимость. Общая стоимость может быть получена путем умножения количества единиц продукта, транспортируемого с каждого завода в каждое местонахождение клиента, и соответствующих затрат на транспортировку единицы продукции. В Excel это значение можно определить с помощью функции «СУММПРОИЗВ». Для этого используются ячейки с удельной стоимостью перевозки (C4:F6) и ячейки распределения (C11:F13). В ячейку H3 вводится следующая формула: «=СУММПРОИЗВ(C11:F13,C4:F6)»
Предложение выполнено: сумма строк ячеек распределения рассчитывается с использованием функции Excel «СУММ». Формула, используемая в ячейке G11: «=СУММ(C11:F11)». Эту формулу суммы строк можно распространить на ячейки G12 и G13, используя функцию перетаскивания в Excel.
Спрос удовлетворен: сумма столбцов ячеек распределения также рассчитывается с использованием функции «СУММ». Формула, используемая в ячейке C14: «=СУММ(C11:C13)». Формулу можно распространить на другие ячейки, перетащив ее по горизонтали.

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

Чтобы решить проблему, перейдите к параметру "Поиск решения" в разделе "Данные" (надстройка "Поиск решения" должна быть загружена в Excel)

Затем откроется диалоговое окно «Параметры решения». Данные, которые необходимо ввести в диалоговое окно, приведены ниже. Это также показано на рис. 2.

«Установить цель». Выберите ячейку, в которую вводится формула общей стоимости (H3). Используйте знак «$» перед буквой/цифрой, если адрес ячейки вводится напрямую (см. рис. 2).
"Кому": выберите вариант "Минимум", поскольку наша цель – минимизировать общую стоимость.
«Изменив ячейки переменных». Выберите ячейки распределения (C11:F13).

«С учетом ограничений»: ограничения связаны со спросом и предложением. Сумма строк ячеек распределения должна быть равна поставке каждой фабрики, а сумма столбцов ячеек распределения должна быть равна спросу в каждом местоположении клиента. Чтобы указать это, нажмите на опцию «Добавить». Затем появляется диалоговое окно для ввода ограничения (рис. 3). В опции «Ссылка на ячейку» выберите ячейку со значением суммы столбца (требование выполнено). Выберите знак равенства «=» из следующего выпадающего списка. В опции «Ограничение» выберите ячейку со значением Спроса. Затем нажмите «ОК». Повторите процедуру, чтобы добавить все ограничения, связанные со спросом и предложением. См. рисунок 2, чтобы увидеть полный список ограничений.

Решатель решит проблему, и откроется диалоговое окно «Результаты решения», как показано на рис. 4. В нем выберите «Сохранить решение решения» и нажмите «ОК».

Теперь решение будет отображаться в файле Excel. См. рис. 5. Из решения можно узнать количество единиц продукта, которое необходимо транспортировать с каждого завода к каждому клиенту. Также отображается соответствующая общая стоимость (45 000 индийских рупий). Можно заметить, что значения «Сделано предложения» (сумма строки) и «Предложение» и «Удовлетворенный спрос» (сумма столбца) и «Спрос» равны во всех ячейках. Это гарантирует соблюдение ограничений, связанных со спросом и предложением, при создании решения. Таким образом, получается оптимальное решение.

Эта статья написана Anoop K P

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

Вам не нужно иметь никакого опыта в написании статей, нужны только знания в этой области.

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

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

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

Транспортная проблема

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

Вот список информации, необходимой для решения транспортной проблемы:

Спрос на продукцию в каждом пункте назначения.
Расположение объектов и расстояния между каждым исходным и целевым объектами.
Стоимость за километр пути.
Максимальная емкость хранилища каждого исходного объекта.

Ниже показана структура сети, которую мы собираемся использовать. Это простая сеть с двумя этапами: три распределительных центра и остальные восемь объектов — магазины. Объекты наносятся на карту с помощью приложения для моделирования и имитации цепочки поставок SCM Globe. На снимке экрана ниже показана эта сеть объектов.

Мы рады предоставить бесплатную ознакомительную учетную запись преподавателям, студентам и специалистам по цепочкам поставок, заинтересованным в изучении моделирования SCM Globe — нажмите здесь, чтобы запросить учетную запись — Получите бесплатную пробную демоверсию

Ниже показана абстрактная схема проектирования сети для модели цепочки поставок, показанной на снимке экрана выше:

Переход в Excel

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

Первым шагом в нашем процессе является упорядоченный ввод необходимых данных из модели цепочки поставок. Начните с ввода спроса каждого магазина; что дает нам общий спрос в 1190 единиц в этом случае. Затем введите емкость хранилища для каждого распределительного центра. Это дает нам общую мощность 2100 единиц. Мы легко видим, что у нас достаточно мощностей, чтобы покрыть рыночный спрос.

Вторым шагом является ввод расстояний между всеми источниками и пунктами назначения. Это расстояние туда и обратно, предоставляемое программным обеспечением SCM Globe. Стоимость за км составляет 0,60 доллара США, что является значением по умолчанию в программном обеспечении для больших грузовиков. Это значение по умолчанию может быть изменено, чтобы точно отражать текущие фактические затраты на километр на основе ваших исследований.

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

Переменные решения, которые будут меняться, выделены желтым цветом. Мы можем сделать значение в желтой матрице нулями, но чтобы убедиться, что наши суммы строк и столбцов верны, мы сделали их равными единице. Значения в правой части матрицы — это общее количество, доставленное в каждый магазин, а значения под матрицей — это общее количество, которое будет передано каждым РЦ. Мы также скопировали матрицу DC и емкости и вставили ее Transpose. Мы сделали это, чтобы можно было быстро и легко добавить ограничение мощности в Solver.

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

Общая стоимость перевозки может быть равна стоимости за километр, умноженной на расстояние каждого маршрута, а затем умноженной на количество необходимых доставок, поскольку транспортные средства имеют максимальный объем перевозки.
Если у нас есть стоимость за километр за единицу, то общая стоимость будет равна стоимости за километр за единицу, умноженной на расстояние каждого маршрута, а затем умноженной на количество единиц, отправленных по этим маршрутам.< /li>

В нашем случае у нас есть только стоимость за километр пути, поэтому мы будем использовать первый вариант. Чтобы узнать, сколько грузовиков нам нужно, чтобы доставить количество, полученное от одного РЦ, в магазин, мы должны разделить это количество на максимальный грузоподъемность наших грузовиков и округлить это число (Функция ОКРУГЛВВЕРХ в Excel). В нашем примере мы предполагаем, что один большой грузовик имеет максимальный объем перевозки 110 единиц. Тогда необходимое количество доставок будет равно « ROUNDUP(I15:K22/110;0)» . И формула общей стоимости транспортировки: « СУММПРОИЗВ(C15:E22;ОКРУГЛВВЕРХ(I15:K22/110;0))*C24 ».

Если у вас есть стоимость за километр на единицу, то формула будет в основном " СУММПРОИЗВ(C15:E22;I15:K22)*C24" . Здесь следует иметь в виду, что в этой простой транспортной задаче стоимость за километр или стоимость за километр за единицу не изменят решение, а повлияют только на общую стоимость.

Создание и запуск модели решателя

Давайте определим целевую функцию и ограничения нашей модели:

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

После открытия вкладки решателя мы должны установить цель. В нашем случае мы выделяем ячейку «I25» зеленым цветом; это наша общая стоимость. Переменные, которые мы изменяем, сгруппированы в желтой матрице, поэтому мы выбираем эту матрицу во втором блоке.

Что касается ограничений, мы должны нажать кнопку «Добавить». Три созданных ограничения показаны на рисунках ниже. После добавления всех этих ограничений мы должны сначала сделать неограниченные переменные неотрицательными, установив флажок. Соблюдая эти установленные ограничения, мы можем запустить нашу модель Solver, выбрав Simplex LP (метод решения, поскольку наша модель является линейной).

Наша модель готова, поэтому нажмите кнопку «Решить». Мы видим, что переменные решения и общая стоимость меняются. Результатом является предварительное оптимальное решение нашей транспортной задачи.

Интерпретация результатов

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

В этом примере мы получили общую стоимость 3 991,20 долл. США, что является приемлемым, но мы заметили, что единственным складом, который будет использовать свою максимальную емкость хранения, является РЦ № 1. РЦ № 3 использует 60 % своей емкости. максимальная емкость хранилища, а DC Nbr 2 использует только 10%. Отсюда вопрос: Нужно ли нам открывать все три распределительных центра на следующий период?

Можно сказать, что при использовании только двух объектов затраты на эксплуатацию объектов снизятся, но могут возрасти транспортные расходы. Чтобы найти оптимальное решение, мы должны сравнить общую стоимость двух разных сценариев. Какой сценарий предлагает наименьшие общие затраты при удовлетворении спроса в магазине? В первом сценарии компания открывает и использует все свои ЦОД, оплачивая все свои фиксированные расходы. Во втором сценарии компания управляет только двумя своими ДЦ, закрывая третий.

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

Заключение

Управление цепочками поставок — это одновременно и наука, и искусство. В дополнение к числовым расчетам роль профессионального суждения также важна, поскольку она может найти возможности для роста, в то время как числовые расчеты позволяют найти способы снижения затрат. Симуляции помогают людям исследовать различные идеи и находить новые возможности. Моделирование объединяет числовые расчеты и профессиональные суждения.

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

[Дополнительную информацию об использовании решателя для выбора и планирования маршрута цепочки поставок см. в записи нашего блога «Управление транспортировкой путем решения задачи выбора маршрута транспортного средства»]

Особая благодарность г-ну Кертису Фраю за качество его курса под названием «Анализ цепочки поставок в Excel: решение транспортных проблем» в LinkedIn Learning. Мы основывали нашу демонстрацию Excel на его способе организации шаблона электронной таблицы.

К концу этой статьи вы узнаете, как решать транспортные задачи с помощью инструмента "Решатель" в Excel.

Транспортная задача в Excel

Давайте рассмотрим задачу линейного программирования, чтобы минимизировать транспортные расходы. Ожидается, что три компании C1, C2, C3 доставят товары по трем направлениям D1, D2, D3.

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

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

В ячейку оптимального решения введите формулу =СУММПРОИЗВ(массив1,массив2).

После ввода всех данных и формул перейдите на вкладку «Данные», в группе «Анализ» щелкните инструмент «Решатель».

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

В поле to выберите Min, чтобы минимизировать транспортные расходы. В поле Путем изменения переменных ячеек выберите диапазон ячеек, который зависит от данных о товарах, которые должны быть доставлены, о наличии товаров, о достигнутых товарах и о необходимых товарах. В этой задаче C8:E10.

В соответствии с ограничениями, щелкните поле «Добавить», установите для доставленных товаров меньше или равное доступным товарам в поле ссылки на ячейку и установите достигнутые товары как равные нужным товарам.

В поле выбора метода решения выберите простой LP и нажмите решить.

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

Вот как вы решаете транспортную задачу в Excel с помощью решателя.

Хорошо известно, что компании не только производят продукцию в разных местах, но и отгружают ее покупателю в разные места. Менеджеры по сбыту должны распределять и отгружать товары наиболее эффективным и рентабельным способом. Такая задача называется транспортной. Я расскажу, как использовать Excel Solver для решения транспортной задачи. Если вы не знакомы с решателем Excel, вы можете прочитать одну из моих предыдущих статей — Как решить LP с помощью Microsoft Solver.

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

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

td>

Северный	Центральный	Южный
Северный	5000$	7000$	8000 долл. США
Центральный	7000 долл. США	5000$	6000$
Южный	8000$	6000$	5000$

Какой самый дешевый способ доставить необходимое количество препарата в каждый регион?

Наша цель в данном случае – рассчитать общую стоимость доставки и минимизировать ее. Общая стоимость может быть выражена следующим образом:

(Количество миллиардов галлонов воды, отправленных с севера на север)*(Стоимость одного миллиарда галлонов воды, отправленной с севера на север) + (Количество миллиардов галлонов воды, отправленных с севера на север)*(Стоимость за миллиард галлонов подачи воды из северного региона в центральный) + (Количество миллиардов галлонов воды, отправленного из северного региона в южный)*(Стоимость одного миллиарда галлонов воды, отправленного из северного региона в южный) + …… + (Количество миллиардов галлонов воды, отправленного из южного в центральный)*(Стоимость за миллиард галлонов воды, перекачиваемой с юга в центр) + (Количество миллиардов галлонов воды, перекачиваемой с юга в южную)*(Стоимость за миллиард галлонов, перекачиваемой с юга в южную)

Если мы переведем приведенную выше модель в формулу Excel, это не только будет стоить нам много времени, но и подвергнет нас высокому риску совершить ошибку. Чтобы упростить вычисления, я положил стоимость доставки 1 миллиарда галлонов воды между этими тремя регионами в диапазоне C3:E5. А отгрузки по каждой точке снабжения в каждый регион помещались в диапазон ячеек C8:E10. Эти два диапазона имеют одинаковые размеры, поэтому для вычисления общей стоимости здесь можно использовать функцию СУММПРОИЗВ. В этом случае для расчета общей стоимости используется формула «=СУММПРОИЗВ (C3: E5, C8: E10)», введенная в ячейку G3.

Пока что мы определили нашу цель и изменили ячейки. Теперь нам нужно определить явные или неявные ограничения. Одним из ограничений является то, что количество воды, получаемой каждым регионом, должно быть равно или больше 100 миллиардов галлонов воды. Это ограничение было помещено в диапазон C12:E14. Формула в ячейке C12: «=СУММ (8 канадских долларов: 10 канадских долларов)». Формула в ячейках D12 и E12 была скопирована с формулы в C12. Еще одно из них заключается в том, что вода, отправляемая из каждого региона, должна быть равна или меньше миллиарда галлонов доступной воды. Мы используем формулу «=СУММ (C8: E8)», чтобы вычислить общее количество миллиардов галлонов воды, отправленных из Северной Калифорнии. Скопировав и вставив эту формулу в ячейки G9 и G10, мы также можем получить количество воды, отправленной из Центральной или Южной.

Теперь давайте перейдем на вкладку "Данные", а затем нажмите "Решатель" в группе "Анализ", чтобы открыть диалоговое окно "Параметры решателя".

Рисунок 1.2 [нажмите на изображение, чтобы открыть его]

Заполните диалоговое окно, как показано на рис. 1.3. Общая стоимость доставки рассчитывается путем сложения условий, меняющих ячейку * константу. Два явных ограничения создаются путем сравнения сумм изменения ячеек с константой. Вы можете видеть, что и наша цель, и ограничения могут быть представлены линейной зависимостью. Поэтому наша модель является линейной моделью, и был выбран двигатель Simplex LP. Очевидно, что отгрузки должны быть положительными, и именно поэтому мы установили флажок Сделать неограниченные переменные неотрицательными.

Рисунок 1.3 [щелкните изображение, чтобы открыть его полностью]

100 млрд галлонов воды, отправленной с севера на север, и 20 миллиардов галлонов воды, отправлено с севера на юг
100 млрд галлонов воды, отправленных из Центральной в Центральную, и 20 млрд галлонов воды, отправленных из Центральной в Южную
Южный регион оставляет всю доступную воду для собственного использования

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

Компания производит и продает лекарства в нескольких местах. Решение о том, где производить товары для каждого места продаж, может оказать огромное влияние на прибыльность. Предположим, что нам нужно производить лекарства в шести местах и продавать их покупателям в шести районах. Ставка налога и переменная стоимость производства зависят от того, где производится лекарство. Цена продажи каждого препарата зависит от того, где он продается.

td> < td width="13%">7$

Продано1	Продано2	Продано3	Продано4	Продано5	Продано6
Цена продажи	45 долларов	40 долларов	38$	36$	39$	34$
Ставка налога	31%	40%	20%	40%	35 %	18%
Переменная себестоимость	$8	$7	$6	$9	7$

Каждый из шести заводов может производить до 6 миллионов единиц продукции в год. Годовой спрос на ваш продукт в каждом регионе выглядит следующим образом:

< td width="81">5000000

Продано1	Продано2	Продано3	Продано4	Продано5	Продано6
1000000	2000000	3000000	4000000	6000000

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

< td width="81">$6

Продано1	Продано2	Продано3	Продано4	Продано5	Продано6
Завод1	$3	$4	$5	$7	$8
Завод2	$5	$2	$6	$9	$10	$11
Завод3	4$	3$	1$	$6	$8	$6
Завод4	$5	$5	$7	2$	5$	5$
Завод5	$6	$9	$6	$5	3$	7$
Завод6	$7	$7	$8	$9	10$	4$

Как можно максимизировать прибыль после уплаты налогов при ограниченных производственных мощностях?

Поскольку налоговая ставка, переменные производственные затраты и продажная цена связаны с местом, где производятся или продаются лекарства, мы можем упростить задачу, вычислив прибыль для каждого завода, а затем сложив их вместе. На рис. 2.1 ниже показано, как упорядочить данные. Стоимость доставки была заполнена в диапазоне C3:H8. Количество наркотиков, отправленных с заводов на места, было введено в диапазон C13:H18. Формула «=СУММ (C13:C18)» была введена в ячейку C20 для расчета количества наркотиков, полученных в пункте 1. Скопировав формулу в ячейки от D20 до H20, мы можем получить все количества наркотиков, полученные в других местах. Формула «=СУММ (C13: H13)» использовалась для вычисления количества лекарств, производимых на заводе 1. Чтобы вычислить, сколько лекарств производится на других растениях, формула из ячейки J13 была скопирована в диапазон J14: J18.

Цена продажи указана в диапазоне C9:H9, поскольку она зависит от того, где продается препарат. Они вводятся в одну и ту же строку, поэтому я могу использовать функцию СУММПРОИЗВ для вычисления валового дохода. Точно так же налоговая ставка и переменные производственные затраты были указаны в одном столбце, поскольку они зависят от того, где производятся лекарства, а названия растений указаны в одном столбце.

Формула на рис. 2.1 была введена в ячейку L3 для расчета прибыли после налогообложения для Завода 1. Благодаря хорошему расположению данных я могу скопировать эту формулу в ячейки с L4 по L8, чтобы рассчитать прибыль после уплаты налогов для других заводов. И, наконец, сложив прибыль по формуле «=СУММ (L3: L8)», мы можем получить общую прибыль, которая является нашей целью.

Рисунок 2.1 [щелкните изображение, чтобы открыть его полностью]

Откройте диалоговое окно Solver Parameters и заполните его, как показано на рис. 2.2. Оба явных ограничения могут быть представлены в виде линейной зависимости, поэтому я выбрал движок Simplex LP. Первое ограничение заключается в том, что количество лекарств, получаемых в каждом пункте, должно быть равно спросу или превышать его. Это можно описать с помощью «$C$20:$H$20 >= $C$22:$H$22».Еще одно ограничение заключается в том, что количество производимых наркотиков не может превышать мощности, которая может быть представлена как «$J$13:$J$18 Подробнее…

Читайте также: