Работа № 2. Выбор поставщиков, план перевозок, транспортная задача
Проблема: в общем случае постановка транспортной задачи заключается в том, что необходимо доставить необходимое количество ресурсов от распределенных поставщиков к распределенным потребителям. При этом надо обеспечить минимум затрат на транспортировку. Потребности получателей и возможности поставщиков ограничены.
Цель работы
Освоить методику и технологию оптимизации планов в табличном процессоре Excel с помощью программы Поиск решения (Slover).
Научиться составлять наилучший (оптимальный) план перевозок от поставщиков к потребителям с учетом ограниченных ресурсов поставщиков и известной потребности потребителей.
Постановка задачи
Требуется минимизировать затраты на перевозку товаров от предприятий-производителей на торговые склады. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей. В этой модели представлена задача доставки товаров с трех заводов на пять региональных складов. Товары могут доставляться с любого завода на любой склад, однако, очевидно, что стоимость доставки на большее расстояние будет большей.
Необходимо определить объемы перевозок между каждым заводом и складом, в соответствии с потребностями складов и производственными мощностями заводов, при которых транспортные расходы минимальны.
Порядок выполнения работы
Исходные данные приведены в Таблице 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;
Xij — искомое плановое количество перевозки от i-го поставщика к j-му потребителю;
Si — план поставок от i-го поставщика всем потребителям, сумма по строке;
Сj — план поставок j-му потребителю от всех поставщиков, сумма по столбцу;
Pij — цена франко-склад единицы груза от i-го поставщика к j-му потребителю;
Bi — ограниченная мощность i-го поставщика;
Dj — ограниченный спрос 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. Разработка аис для расчета амортизационных отчислений
- Справочник сроков и способов расчета амортизации Вх.Ф.№ 1
- Расчет годовой суммы амортизации Пром.Ф.№ 1
- Работа № 6. Разработка автоматизированной системы по начислению заработной платы
- Учетные сведения о сотрудниках отделения
- Количество дней невыхода на работу без причины
- Количество дней нахождения в административном отпуске
- Количество дней по больничным листам
- Фактическое количество отработанных дней в текущем месяце
- Справочные данные
- Разряды единой тарифной сетки
- Работа № 7. Создание локальных реляционных баз данных
- Работа № 8. Обработка данных в локальных реляционных базах данных
- Работа № 9. Нормализация реляционной бд
- Работа № 10. Создание er-модели и ее нормализация