Практическая работа 13

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

Постановка задачи:

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

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

Порядок выполнения работы

· Выполнить таблицу по образцу (табл. 1);

Таблица 1
		План по объемам перевозок от завода к складу
Заводы:	План поставок	Москва	Волгоград	Новосибирск	Челябинск	Иркутск
Сибирь
Урал
Поволжье
		Поставки по каждому складу
Итого:
		Исходные данные для расчета плана
	Потребности складов
Заводы:	Мощность заводов	Стоимость перевозки единицы груза от завода к складу
Сибирь
Урал
Поволжье
Результат: затраты на перевозку	Стоимость перевозок по каждому складу

 

Общие плановые затраты на перевозку (ячейка В15) необходимо минимизировать. Исходная плановая матрица объемов перевозок от каждого поставщика к каждому потребителю расположена в диапазоне С3: G5.

В диапазоне В3: В5 вычисляются планы поставок от каждого завода всем потребителям как сумма по строкам. Плановик во время расчетов наблюдает, чтобы эти суммы не превысили мощностей заводов – поставщиков. В строке 6 (итого:) вычисляются планы поставок каждому потребителю от всех заводов как сумма по столбцам. Плановик наблюдает, чтобы эта сумма была ровна или не меньше заказов потребителей.

· Сумма перевозок по каждому складу = произведение плана перевозок по отдельным городам на стоимость перевозок единицы груза для каждого завода (например: С3*С11+С4*С12…и т.д.). Можно воспользоваться функцией СУММОПРОИЗВЕДЕНИЕ из мастера функций.

В строке 15 вычисляются стоимости перевозок для каждого склада и общие затраты по транспортировке. (=СУММА(С15: G20))

 

Заполняете ячейки формулами для расчетов:

Ø Планы поставок (диапазон В3: В5) = СУММА (С3: G3) и т.д.по строкам;

Ø Итого поставки по каждому складу (диапазон С7: G7) = СУММА(С3: С5) и т.д. по столбцам;

Ø Стоимость перевозок по каждому складу (диапазон С15: G15) = С3*С11+С4*С12…

и т.д.). Можно воспользоваться функцией СУММОПРОИЗВЕДЕНИЕ из мастера функций.

Ø Затраты на перевозку (ячейка B15) = СУММА (С15: G15)

 

Далее осуществляем компьютерный поиск оптимального плана перевозок. Для этого воспользуемся Сервис – Поиск решения ( в случае отсутствия подменю Поиск решения, подключаем его через Сервис – Надстройки).

 

 

Свод параметров дан в таблице 2.

Таблица 2

Параметры задачи	Ячейки	Семантика
Результат	В15	Цель – уменьшение всех транспортных расходов
Изменяемые данные	С3: G5	Объемы перевозок от каждого завода к каждому складу
Ограничения	B3: B5< =B11: B13	Количество перевезенных грузов не может превышать производственных возможностей заводов
	C3: G5> =0	Количество доставляемых грузов не должно быть меньше потребностей складов
	C7: G7> =C9: G9	Число перевозок не может быть отрицательным.

 

Сравните полученные результаты (таблица 3)

 

 

Таблица 3

		План по объемам перевозок от завода к складу
Заводы:	План поставок	Москва	Волгоград	Новосибирск	Челябинск	Иркутск
Сибирь
Урал
Поволжье
		Поставки по каждому складу
Итого:
		Исходные данные для расчета плана
	Потребности складов
Заводы:	Мощность заводов	Стоимость перевозки единицы груза от завода к складу
Сибирь
Урал
Поволжье
Результат: затраты на перевозку	Стоимость перевозок по каждому складу