Работа № 2. Выбор поставщиков, план перевозок, транспортная задача

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

Цель работы

1. Освоить методику и технологию оптимизации планов в табличном процессоре Excel с помощью программы Поиск решения (Slover).

2. Научиться составлять наилучший (оптимальный) план перевозок от поставщиков к потребителям с учетом ограниченных ресурсов поставщиков и известной потребности потребителей.

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

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

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

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

Исходные данные приведены в Таблице 2.1.

Таблица 2.1

	A	B	C	D	E	F	G
2			План по объемам перевозок от завода х к складу у:
3	Заводы	План поставок	Казань	Рига	Воронеж	Курск	Москва
4	Белоруссия	=СУММ(C4:G4)
5	Урал	=СУММ(C5:G5)
6	Украина	=СУММ(C6:G6)
7			Поставлено каждому складу
8		Итого:	=СУММ(C4:C6)	=СУММ(D4:D6)	=СУММ(E4:E6)	=СУММ(F4:F6)	=СУММ(G4:G6)
9			Исходные данные для расчета плана
10		Потребности складов	180	80	200	160	220
11	Заводы	Мощность заводов	Стоимость перевозки единицы груза
12	Белоруссия	310	10	8	6	5	4
13	Урал	260	6	5	4	3	6
14	Украина	280	3	4	5	5	9
15	Результат: Затраты на перевозку		Стоимость перевозок по каждому складу
16		=СУММ(C16:G16)	=C4*C12+C5*C13+C6*C14	=D4*D12+D5*D13+D6*D14	=E4*E12+E5*E13+E6*E14	=F4*F12+F5*F13+F6*F14	=G4*G12+G5*G13+G6*G14

В колонке А — имена заводов-поставщиков. В строке 3 — имена потребителей. Общие плановые затраты на перевозку в ячейке В16 надо минимизировать. Искомая плановая матрица объемов перевозки грузов от каждого поставщика к каждому потребителю расположена в диапазоне С4:G6. В диапазоне В4:В6 вычисляются планы поставок от каждого завода всем потребителям как суммы по строкам, они не должны превышать мощности заводов-поставщиков. В строке 8 вычисляются планы поставок каждому потребителю от всех заводов как суммы по столбцам, они должны быть равны или не меньше заказов потребителей. В строках 9:14 представлены исходные данные для расчетов. В диапазон В12:В14 вводятся мощности заводов-поставщиков. В матрицу С12:G14 надо ввести стоимость перевозки единицы груза от каждого поставщика к каждому потребителю. В строку 10 надо ввести плановые потребности складов. В строке 16 вычисляются стоимость перевозок для каждого склада и общие затраты по транспортировке. Общие затраты суммируются в целевой ячейке В16.

Математическая модель алгоритма оптимизации будет выглядеть следующим образом:

n — количество поставщиков;

m — количество потребителей;

i — номер строки, поставщика, 1..n;

j — номер столбца, потребителя, 1..m;

X_ij — искомое плановое количество перевозки от i-го поставщика к j-му потребителю;

S_i — план поставок от i-го поставщика всем потребителям, сумма по строке;

С_j — план поставок j-му потребителю от всех поставщиков, сумма по столбцу;

P_ij — цена франко-склад единицы груза от i-го поставщика к j-му потребителю;

B_i — ограниченная мощность i-го поставщика;

D_j — ограниченный спрос j-го потребителя.

Необходимо минимизировать затраты на перевозку грузов

при ограничениях и неотрицательных объемах перевозок.

Ввести исходные данные, т. е. Таблицу 2.1 набрать в Excel. Для ввода формул удобнее воспользоваться режимом представления формул, для этого нужно установить галочку в меню Сервис>Параметры>Параметры окна>формулы.

Задание № 1. Ручной поиск оптимального плана

Изменяя данные в плане перевозок, диапазон C4:G6, добиться минимальной стоимости перевозок в ячейке В16. При этом необходимо контролировать, чтобы план поставок в ячейках В4:В6 не превышал мощности заводов в ячейках В12:В14. Поставки каждому складу не должны быть менее их потребностей.

Задание № 2. Настройка экономико-математической модели

Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Сервис>Поиск решения и настроить экономико-математическую модель.

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

Таблица 2.2

Параметры задачи	Ячейки	Семантика
Целевая ячейка	$B$16	Цель — уменьшение всех транспортных расходов.
Изменяемые данные	$C$4:$G$6	Объемы перевозок от каждого из заводов к каждому складу.
Ограничения	$B$4:$B$6<=$B$12:$B$14	Количества перевезенных грузов не могут превышать производственных возможностей заводов.
	$C$8:$G$8>=$C$10:$G$10	Количество доставляемых грузов не должно быть меньше потребностей складов.
	$C$4:$G$6>=0	Число перевозок не может быть отрицательным.

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

Задание № 3. Компьютерный поиск оптимального плана

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

Оформление отчета

Отчет должен содержать:

1. Определение проблемы.

2. Плановую таблицу с результатами оптимального плана.

3. Краткую характеристику программы оптимизации Поиск решения.

4. Анализ оптимального плана и решения менеджера.

5. Предложения по модификации и расширению модели.

6. Выводы обо всей проделанной работе.