48797 (Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad), страница 5
Описание файла
Документ из архива "Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad", который расположен в категории "". Всё это находится в предмете "информатика" из 1 семестр, которые можно найти в файловом архиве . Не смотря на прямую связь этого архива с , его также можно найти и в других разделах. Архив можно найти в разделе "курсовые/домашние работы", в предмете "информатика, программирование" в общих файлах.
Онлайн просмотр документа "48797"
Текст 5 страницы из документа "48797"
Отчет:
Целевая ячейка (Максимум) | ||||||
Ячейка | Имя | Исходное значение | Результат | |||
$F$9 | Прибыль, руб. Расход показателей по плану | 0 | 140000000 | |||
Изменяемые ячейки | ||||||
Ячейка | Имя | Исходное значение | Результат | |||
$B$2 | Судно 1 типа | 0 | 4 | |||
$B$3 | Судно 2 типа | 0 | 2 | |||
Ограничения | ||||||
Ячейка | Имя | Значение | Формула | Статус | Разница | |
$B$2 | Судно 1 типа | 4 | $B$2=$B$3*2 | не связан. | 0 | |
$F$6 | Пассажировместимость Расход показателей по плану | 10000 | $F$6<=$G$6 | связанное | 0 | |
$F$7 | Горючее Расход показателей по плану | 4400 | $F$7<=$G$7 | не связан. | 4600 | |
$F$8 | Экипаж Расход показателей по плану | 600 | $F$8<=$G$8 | не связан. | 400 | |
$B$2 | Судно 1 типа | 4 | $B$2>=0 | не связан. | 4 | |
$B$3 | Судно 2 типа | 2 | $B$3>=0 | не связан. | 2 |
2. Нахождение оптимального решения в системе Mathcad.
Введем обозначения:
Суда первого типа – s.
Суда второго типа – n.
Прибыль от реализации всей продукции – P.
Mathcad получил результат:
Судов первого типа – 4 штуки, судов второго типа – 2 штуки.
Прибыль составляет 140000000 руб. Совпадает с результатом решения оптимизатора Excel.
Построение таблицы полученных решений.
№ | План найденный вручную | План, найденный программным путем | |||
1 | Excel | Mathcad | |||
Прибыль, руб. | 70000000 | 140000000 | 140000000 |
Вывод:
Наибольшая прибыль от реализации продукции получается при решении с помощью программы оптимизатора в Excel и в системе Mathcad.
Анализ оптимального плана и решение менеджера.
Оптимальными планами являются решения, полученные с помощью программ Excel и Mathcad, которые получили одинаковый результат:
Судна первого типа – 4 штуки, судов второго типа – 2 штуки. Прибыль будет составлять 140000000 руб.
При нахождении оптимального плана пассажировместимость остается прежней, равной 10000 человек. Для дальнейшего увеличения прибыли целесообразно увеличить пассажировместимость. Также остается неиспользованными 4600 тонн горючего и не задействованными 400 человек экипажа. В результате заморожены оборотные средства, оборотный капитал, увеличиваются расходы по хранению горючего на складе и не занятости экипажа, теряется прибыль. Завоз горючего можно сократить, а незанятое количество экипажа уволить.
Задача №3 (билет №59 ТВ).
Транспортная задача.
Определение проблемы: В современных условиях большие транспортные расходы связаны:
- с простоями в ожидании погрузочно-разгрузочных работ;
- с нерациональными перевозками;
- с затратами на бензин;
- с порожними пробегами и т. д.
В связи с этим необходимо решать задачи оптимального планирования перевозок грузов из пунктов отправления в пункты назначения.
Оптимизировать план перевозок груза можно по какому-либо экономическому показателю:
- финансовые затраты на перевозку грузов;
- время, затрачиваемое на перевозку.
Для решения таких задач в линейном программировании разработаны следующие методы:
- Симплекс-метод;
- Метод потенциалов;
- Венгерский метод.
Постановка транспортной задачи: В общем случае заключается в том, что нужно доставить необходимое количество ресурсов от разных поставщиков к разным потребителям. При этом:
- затраты на транспортировку ресурсов должны быть минимальными;
- возможности поставщиков ограничены;
- нужно полностью выполнить заявки каждого потребителя.
Построение экономико-математической модели задачи:
1. Имеются m пунктов отправления (поставщиков грузов),
А1, А2, А3 …Аi, …Аm
на которых сосредоточены запасы како-либо груза в объемах соответственно:
а1, а2, а3 …аi, …аm
где величина аi – максимально возможное количество груза в i пункте отправления. Тогда суммарный запас груза у всех поставщиков составляет:
2. Имеются n пунктов назначения,
B1, B2, B3 …Bj, …Bn
которые подали заявки на поставку грузов в объемах соответственно:
b1, b2, b3, …bj, …bn
тогда суммарная величина заявок составляет:
3. Стоимость перевозки 1 единицы груза от поставщика Аi к потребителю Bj обозначим ci,j (транспортный тариф).
Общая стоимость перевозок составляет матрицу транспортных издержек С.
Критерием оптимальности выберем суммарные затраты (издержки по перевозки груза).
Все исходные данные транспортной задачи записываются в виде таблицы (транспортной), в которой xi,j – значение объема перевозки грузов от поставщика Аi к потребителю Bj.
Пункты отправления | Пункты назначения | Запасы аi | |||
В1 | В2 | …Вj ... | Вn | ||
А1 | c11 x11 | c12 x12 | c1j x1j | c1n x1n | а1 |
А2 | c21 x21 | c22 x22 | c2j x2j | c2n x2n | а2 |
…Аi… | ci1 xi1 | ci2 xi2 | cij xij | cin xin | аi |
Аm | cm1 xm1 | cm2 xm2 | cmj xmj | cmn Xmn | аm |
Заявки bj | b1 | b2 | bj | bn |
Задача заключается в определении плана перевозок матрицы x, где
x (i = 1, m; j = 1, n ),
который удовлетворяет следующим условиям:
1. План перевозок от i поставщика всем потребителям (сумма по строке), ограничивается запасом на складе j поставщика.
2. План поставок j потребителю от всех поставщиков (сумма по столбцу) должен быть не меньше объема заявок j потребителя.
3. Из физического смысла задачи определяем, что объемы перевозок xij не могут быть отрицательными.
xij ≥ bj; j = 1, n
4. Затраты на перевозку равны:
m n
P(X) = ∑∑ cij* xij
i=1 j=1
X – план перевозок, то есть матрица X.
Таким образом, экономико-математическая модель задачи можно записать в виде:
- Найти минимальное значение целевой функции:
m n
P (X) = ∑∑ cij* xij min
i=1 j=1
Данная экономико-математическая модель является одной из разновидностей транспортной задачи.
Условие: требуется минимизировать затраты на перевозку грузов от заводов поставщиков (А1, А2, А3) на торговые склады В1, В2, В3. Товары могут доставляться с любого завода на любой склад, при условии:
-
Минимальное количество поставляемого груза с завода А1 и завода А2 к каждому потребителю составляет 160 тонн.
-
С завода А2 на склад В1 нужно привезти не более 200 тонн груза, а с завода А3 на склад В1 – не менее 100 тонн.
Необходимо учесть:
- возможности поставщиков;
- максимально удовлетворить заявки склада.
Дано:
1. Потребности склада:
В1 – 500;
В2 – 600;
В3 – 400.
2. Мощность заводов:
А1 – 500;
А2 – 600;
А3 – 500.
3. Стоимость перевозки единицы груза:
Потребители | В1 | В2 | В3 |
Поставщики | |||
А1 | 9 | 6 | 2 |
А2 | 4 | 15 | 15 |
А3 | 17 | 7 | 8 |
Ручной поиск оптимального плана.
Цель: составить оптимальный план вручную
Используя стандартный программный пакет Excel, необходимо интуитивно задать пять возможных вариантов плана перевозки, я нашла наилучший интуитивный план перевозок продукции, при котором затраты на перевозку были минимальные.
Стоимость перевозки единицы груза от завода к складу. | |||||
Потребитель | |||||
Поставщик | В1 | В2 | В3 | ||
А1 | 9 | 6 | 2 | ||
А2 | 4 | 15 | 15 | ||
А3 | 17 | 7 | 8 | ||
План перевозок от завода к складу | |||||
Потребитель | План поставок | Мощность завода | |||
Поставщик | В1 | В2 | В3 | ||
А1 | 180 | 160 | 160 | 500 | 500 |
А2 | 160 | 180 | 160 | 500 | 600 |
А3 | 160 | 260 | 80 | 500 | 500 |
Поставлено по складу | 500 | 600 | 400 | ||
Потребность складов | 500 | 600 | 400 | ||
Стоимость перевозок по каждому складу | Трансп. расходы | ||||
4980 | 5480 | 3360 | 13820 | ||
Стоимость перевозки единицы груза от завода к складу. | |||||
Потребитель | |||||
Поставщик | В1 | В2 | В3 | ||
А1 | 9 | 6 | 2 | ||
А2 | 4 | 15 | 15 | ||
А3 | 17 | 7 | 8 | ||
План перевозок от завода к складу | |||||
Потребитель | План поставок | Мощность завода | |||
Поставщик | В1 | В2 | В3 | ||
А1 | 180 | 160 | 160 | 500 | 500 |
А2 | 180 | 160 | 160 | 500 | 600 |
А3 | 140 | 280 | 80 | 500 | 500 |
Поставлено по складу | 500 | 600 | 400 | ||
Потребность складов | 500 | 600 | 400 | ||
Стоимость перевозок по каждому складу | Трансп. расходы | ||||
4720 | 5320 | 3360 | 13400 | ||
Стоимость перевозки единицы груза от завода к складу. | |||||
Потребитель | |||||
Поставщик | В1 | В2 | В3 | ||
А1 | 9 | 6 | 2 | ||
А2 | 4 | 15 | 15 | ||
А3 | 17 | 7 | 8 | ||
План перевозок от завода к складу | |||||
Потребитель | План поставок | Мощность завода | |||
Поставщик | В1 | В2 | В3 | ||
А1 | 180 | 160 | 160 | 500 | 500 |
А2 | 160 | 200 | 160 | 520 | 600 |
А3 | 160 | 140 | 80 | 380 | 500 |
Поставлено по складу | 500 | 500 | 400 | ||
Потребность складов | 500 | 600 | 400 | ||
Стоимость перевозок по каждому складу | Трансп. расходы | ||||
4980 | 4940 | 3360 | 13280 | ||
Стоимость перевозки единицы груза от завода к складу. | |||||
Потребитель | |||||
Поставщик | В1 | В2 | В3 | ||
А1 | 9 | 6 | 2 | ||
А2 | 4 | 15 | 15 | ||
А3 | 17 | 7 | 8 | ||
План перевозок от завода к складу | |||||
Потребитель | План поставок | Мощность завода | |||
Поставщик | В1 | В2 | В3 | ||
А1 | 160 | 160 | 160 | 480 | 500 |
А2 | 160 | 160 | 160 | 480 | 600 |
А3 | 180 | 270 | 80 | 530 | 500 |
Поставлено по складу | 500 | 590 | 400 | ||
Потребность складов | 500 | 600 | 400 | ||
Стоимость перевозок по каждому складу | Трансп. расходы | ||||
5140 | 5250 | 3360 | 13750 | ||
Стоимость перевозки единицы груза от завода к складу. | |||||
Потребитель | |||||
Поставщик | В1 | В2 | В3 | ||
А1 | 9 | 6 | 2 | ||
А2 | 4 | 15 | 15 | ||
А3 | 17 | 7 | 8 | ||
План перевозок от завода к складу | |||||
Потребитель | План поставок | Мощность завода | |||
Поставщик | В1 | В2 | В3 | ||
А1 | 170 | 170 | 160 | 500 | 500 |
А2 | 170 | 170 | 170 | 510 | 600 |
А3 | 160 | 260 | 70 | 490 | 500 |
Поставлено по складу | 500 | 600 | 400 | ||
Потребность складов | 500 | 600 | 400 | ||
Стоимость перевозок по каждому складу | Трансп. расходы | ||||
4930 | 5390 | 3430 | 13750 |
Вывод:
Наилучшим планом из составленных вручную с помощью программы Excel является план №3, т.к. при данном плане затраты на перевозку минимальные и составляют 13280 руб.
Программный поиск оптимального решения.
-
Нахождение оптимального решения с помощью программы оптимизации в Excel.
Стоимость перевозки единицы груза от завода к складу. | |||||||
Потребитель | |||||||
Поставщик | В1 | В2 | В3 | ||||
А1 | 9 | 6 | 2 | ||||
А2 | 4 | 15 | 15 | ||||
А3 | 17 | 7 | 8 | ||||
План перевозок от завода к складу | |||||||
Потребитель | План поставок | Мощность завода | |||||
Поставщик | В1 | В2 | В3 | ||||
А1 | 180 | 160 | 160 | 500 | 500 | ||
А2 | 200 | 160 | 160 | 520 | 600 | ||
А3 | 120 | 280 | 80 | 480 | 500 | ||
Поставлено по складу | 500 | 600 | 400 | ||||
Потребность складов | 500 | 600 | 400 | ||||
Стоимость перевозок по каждому складу | Трансп. расходы | ||||||
4460 | 5320 | 3360 | 13140 |
Excel получил результат:
С завода А1 в склад В1 необходимо поставить 180 тонн груза, на В2 – 160 тонн, на В3 – 160 тонн. С завода А2 в склад В1 – 200 тонн, в В2 – 160 тонн, в В3 – 160 тонн. С завода А3 в склад В1 – 120 тонн, в В2 – 280 тонн, в В3 – 80 тонн.
Затраты на перевозку составляют 13140 руб.
Отчет.
Целевая ячейка (Минимум) | |||||||
Ячейка | Имя | Исходное значение | Результат | ||||
$E$16 | Трансп. расходы | 0 | 13140 | ||||
Изменяемые ячейки | |||||||
Ячейка | Имя | Исходное значение | Результат | ||||
$B$10 | А1 В1 | 0 | 180 | ||||
$C$10 | А1 В2 | 0 | 160 | ||||
$D$10 | А1 В3 | 0 | 160 | ||||
$B$11 | А2 В1 | 0 | 200 | ||||
$C$11 | А2 В2 | 0 | 160 | ||||
$D$11 | А2 В3 | 0 | 160 | ||||
$B$12 | А3 В1 | 0 | 120 | ||||
$C$12 | А3 В2 | 0 | 280 | ||||
$D$12 | А3 В3 | 0 | 80 | ||||
Ограничения | |||||||
Ячейка | Имя | Значение | Формула | Статус | Разница | ||
$E$10 | А1 План поставок | 500 | $E$10<=$F$10 | связанное | 0 | ||
$E$11 | А2 План поставок | 520 | $E$11<=$F$11 | не связан. | 80 | ||
$E$12 | А3 План поставок | 480 | $E$12<=$F$12 | не связан. | 20 | ||
$B$13 | Поставлено по складу В1 | 500 | $B$13=$B$14 | не связан. | 0 | ||
$C$13 | Поставлено по складу В2 | 600 | $C$13=$C$14 | не связан. | 0 | ||
$D$13 | Поставлено по складу В3 | 400 | $D$13=$D$14 | не связан. | 0 | ||
$B$10 | А1 В1 | 180 | $B$10>=0 | не связан. | 180 | ||
$C$10 | А1 В2 | 160 | $C$10>=0 | не связан. | 160 | ||
$D$10 | А1 В3 | 160 | $D$10>=0 | не связан. | 160 | ||
$B$11 | А2 В1 | 200 | $B$11>=0 | не связан. | 200 | ||
$C$11 | А2 В2 | 160 | $C$11>=0 | не связан. | 160 | ||
$D$11 | А2 В3 | 160 | $D$11>=0 | не связан. | 160 | ||
$B$12 | А3 В1 | 120 | $B$12>=0 | не связан. | 120 | ||
$C$12 | А3 В2 | 280 | $C$12>=0 | не связан. | 280 | ||
$D$12 | А3 В3 | 80 | $D$12>=0 | не связан. | 80 | ||
$B$11 | А2 В1 | 200 | $B$11<=200 | связанное | 0 | ||
$B$12 | А3 В1 | 120 | $B$12>=100 | не связан. | 20 | ||
$B$10 | А1 В1 | 180 | $B$10>=160 | не связан. | 20 | ||
$C$10 | А1 В2 | 160 | $C$10>=160 | связанное | 0 | ||
$D$10 | А1 В3 | 160 | $D$10>=160 | связанное | 0 | ||
$B$11 | А2 В1 | 200 | $B$11>=160 | не связан. | 40 | ||
$C$11 | А2 В2 | 160 | $C$11>=160 | связанное | 0 | ||
$D$11 | А2 В3 | 160 | $D$11>=160 | связанное | 0 |
2. Нахождение оптимального решения в системе Mathcad.
Mathcad получил результат:
С завода А1 в склад В1 необходимо поставить 180 тонн груза, на В2 – 160 тонн, на В3 – 160 тонн. С завода А2 в склад В1 – 200 тонн, в В2 – 160 тонн, в В3 – 160 тонн. С завода А3 в склад В1 – 120 тонн, в В2 – 280 тонн, в В3 – 80 тонн.
Затраты на перевозку составляют 13140 руб.
Построение таблицы полученных решений.
План, найденный вручную | План, найденный программным путем | |||||||
№ | 1 | 2 | 3 | 4 | 5 | Excel | Mathcad | |
Трансп. расходы | 13820 | 13400 | 13280 | 13750 | 13750 | 13140 | 13140 |
Вывод:
Наименьшие затраты на перевозку грузов получается при решении с помощью программы оптимизатора в Excel и в системе Mathcad.
Анализ результатов.
Из графика видно, что наилучшим планом, составленным вручную является план №4 с транспортными расходами 13280 руб. Оптимальным планом является план полученный с помощью оптимизаторов в Excel и Mathcad. При этом транспортные расходы составляют 13140 руб.
По оптимальному плану мощность завода А1 задействована не полностью, а мощности заводов А2 и А3 полностью.
При долгосрочных планах можно предложить на будущее увеличить мощности заводов А1, а уменьшить мощность в заводах А2 и А3.