Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad (стр. 6 из 6)
Программный поиск оптимального решения.
1. Нахождение оптимального решения с помощью программы оптимизации в 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.