Задача со сценариями. Разработка модели распределения ресурсов при использовании централизованного склада

• Название: Разработка модели распределения ресурсов при использовании централизованного склада
• Дата: 09.11.2022
• Размер: 0.54 Mb.
• Имя файла: Задача со сценариями.docx
• Тип: Задача; #778397

Часть 3. Разработка модели распределения ресурсов при использовании централизованного склада

Цель занятия: изучить способы постановки задач линейного программирования при организации профессиональной деятельности. Задача распределения ресурсов.

Часть технической службы (транспортно-хозяйственная) выполняет ремонт пожарных автомобилей (основные пожарные автомобили, специальные пожарные автомобили, приспособленные автомобили).

Данные об автомобильных запасных частях, используемых для ремонта пожарных автомобилей, и месячный запас запасных частей на складе представлены в Таблице 4.

Таблица 4 - Исходные данные

Тип автомобиля	Основные пожарные автомобили	Специальные пожарные автомобили	Приспособленные автомобили
Запасные части	Расход			Запасы (мес)
Свечи накаливания	6	4	4	400
Свечи зажигания	8	4	8	300
АКБ	2	1	1	50
Шины	7	7	7	200
Колодки тормозные	12	8	12	250
Расходные материалы	5	5	5	500

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

• 
  основных пожарных автомобилей-5 штук;
  
• 
  специальных пожарных автомобилей-2 штук;
  
• 
  приспособленных автомобилей-3 штук.
  

Стоимость технического обслуживания:

• 
  основных пожарных автомобилей-4000 рублей;
  
• 
  специальных пожарных автомобилей-10000 рублей;
  
• 
  приспособленных автомобилей-2000 рублей.
  

Математическая модель строится с искомыми переменными величинами-ежемесячным ремонтом пожарных автомобилей (основных пожарных автомобилей Х1, специальных пожарных автомобилей Х2, приспособленных автомобилей Х3), которые определяют целевую функцию (ЦФ)-расход ресурсов при ремонте пожарных автомобилей:
F(x)=4000*X1+10000*X2+2000*X3 → min
Решению задачи отвечает минимум ЦФ при следующих условиях и ограничениях:



Ежемесячный расход материалов не долженпревышать их месячных запасов

Х1≥5, Х2≥2 и Х3≥3, с учетом выполнения государственного задания.

Х1, Х2 и Х3 - целые, - условие, не позволяющее проводить ремонт долям пожарных автомобилей (единица измерения –штука).
Построение модели

1. 
   На рабочий лист Excel вводим исходные данные (рис. 7)
   

Рис. 7. Исходные данные

2. 
   В ячейках B12:D12 размещаем переменные.
   
3. 
   Ячейки B13:D13 предназначены для значений переменных (это изменяется в процессе поиска решения ячейки), в которых появится искомые количества пожарных автомобилей (основных, специальных и приспособленных) по завершению поиска решения.
   
4. 
   Е16-целевая ячейка, в которой будет размещена формула целевой функции.
   
5. 
   Значения ячеек, в которых размещена стоимость технического обслуживания, являются коэффициентами ЦФ.
   
6. 
   Для нахождения ЦФ следует использовать функцию Excel СУММПРОИЗВ из категории математических: =СУММПРОИЗВ(В11:D11;B13:D13). Значение введенной ЦФ равно нулю, так как значения объема ремонта тоже пока нулевые (рис. 8 и рис. 9)
   

Рис. 8. Диалоговое окно функции СУММПРОИЗВ


Рис. 9. Фрагмент листа Excel в режиме формул. ЦФ и влияющие массивы ячеек

7. 
   Для ограничений удобно построить еще одну таблицу на этом же листе Excel (рис. 10). При вводе правых частей ограничений используем формулы ссылок на ячейки столбца ЗАПАСЫ (строки СПРОС) (рис. 9). Использование ссылок избавит нас от дублирования содержимого ячеек с данными в ячейки таблицы ограничений, кроме того изменения в таблице исходных данных будут синхронно отражаться в таблице ограничений.
   

Рис. 10. Таблица ограничений в символьном режиме

без формул ограничений.

8. 
   При вводе формул (левых частей) ограничений по материалу следует вновь использовать формулу СУММПРОИЗВ. При этом формулу достаточно ввести один раз в ячейку В22, сделав абсолютные ссылки на диапазон ячеек, где хранятся значения переменных Х1, Х2 и Х3 ($B$13:$D$13) (рис. 11). Затем необходимо скопировать введенную формулу для всех остальных ограничений по материалу в ячейки диапазона В23:В27.
   

Рис. 11. Диалоговое окно ввода функции СУММПРОИЗВ

для ввода ограничений по материалу

9. 
   При вводе ограничений по спросу в левой части делаем ссылки на ячейки с искомыми значениями переменных (В13:D13). В правой части ограничения вводятся данные по условию (с помощью ссылок на ячейки диапазона B14:D14.
   
10. 
    Результат ввода ограничений в режиме формул (рис. 12).
    

Рис. 12. Результаты ввода формул ограничений
Исследование модели
Оптимизация рассматриваемой модели, то есть поиск неизвестных, при которых достигается минимум целевой функции и удовлетворяются все введенные условия, выполняется встроенной процедурой автоматического поиска решения. Из меню СЕРВИС командой ПОИСК РЕШЕНИЯ необходимо вызывать одноименное диалоговое окно, в котором произвести следующие установки:

• 
  В поле УСТАНОВИТЬ ЦЕЛЕВУЮ ЯЧЕЙКУ вводим адрес ЦФ Е16 (щелчком по указанной ячейке).
  
• 
  Ниже, для строки РАВНОЙ, выбираем параметр МИНИМАЛЬНОМУ ЗНАЧЕНИЮ (рис. 13).
  
• 
  В поле ИЗМЕНЯЯ ЯЧЕЙКИ вводим диапазон ячеек с искомыми переменными B13:D13 (рис. 14).
  

Рис. 13. Фрагмент диалогового окна ПОИСК РЕШЕНИЯ

• 
  Щелчком по кнопке ДОБАВИТЬ вызываем окно ДОБАВЛЕНИЕ ОГРАНИЧЕНИЯ. В этом окне выполняем ссылки на ячейки подготовленной таблицы ограничений, а также выбираем оператор ограничений, причем в нашем случае однотипные ограничения (по материалу и по спросу) можно ввести не отдельно по каждой строке, а массивами, например по материалу (рис. 14)
  

Рис. 14. Окно добавления ограничения по МАТЕРИАЛУ.

• 
  Аналогично вводим ограничения по спросу: $B$29:$B$31>=$E$29:$E$31 (рис. 15)
  
• 
  Вводим ограничение по целостности переменных (оператор «целое» не вводим вручную, а выбираем его среди прочих операторов ограничений)
  
• 
  Задав ограничения, из окна ПОИСК РЕШЕНИЯ кнопкой ПАРАМЕТРЫ вызываем окно ПАРАМЕТРЫ ПОИСКА РЕШЕНИЯ (рис. 15)
  

Рис. 15. Настройка параметров поиска решения

• 
  Происходит возврат в окно поиска решения. Настроенное окно поиска решения показано на рис. 16.
  

Рис. 16. Настройка диалогового окна ПОИСК РЕШЕНИЯ

• 
  Кнопкой ВЫПОЛНИТЬ запускаем процедуру выполнения поиска решения.
  
• 
  Выполнение процедуры завершается выводом сообщения о завершении поиска и найденном решении (Рис 17).
  

Рис. 17. Фрагмент рабочего листа с окном результата поиска решения
Анализ результатов решения

• 
  При ошибках ввода и невозможности найти решение, в окне результатов поиска решения появляется сообщение (рис. 15). Следует использовать переключатель ВОССТАНОВИТЬ ИСХОДНЫЕ ЗНАЧЕНИЯ. При этом происходит возврат на рабочий лист Excel и восстановление исходных его значений. Необходимо убедиться в правильности введенных данных, и после устранения ошибок опять запустить процедуру поиска решения.
  

Рис. 18. Окно результатов поиска решения

• 
  Решение найдено (рис. 18). Из окна результатов поиска решения кнопкой СОХРАНИТЬ СЦЕНАРИЙ запускаем операцию сохранения результатов поиска решения в виде сценария.
  

Рис. 19. Окно результатов поиска решения

• 
  В диалоговом окне сохранения сценария вводим имя «Ремонт-1» (рис. 20). Нажимаем ОК, при этом происходит возврат в окно результатов поиска решения.
  

Рис. 20. Окно сохранения сценария

• 
  Для анализа результатов решения задачи сформируйте отчет. Из окна результатов поиска решения выбираем тип отчета- «Результаты». Он автоматически сформируется на отдельном листе Excel. Отчеты «Устойчивость» и «Пределы» не применимы для задач с целочисленными ограничениями.
  

Отчеты по результатам содержат три таблицы:

• 
  в таблице «Целевая ячейка (Минимум)» находятся сведения об исходном и оптимизированном значениях ЦФ.
  
• 
  в таблице «Изменяемые ячейки» указаны исходные и конечные значения изменяемых ячеек.
  
• 
  в таблице «Ограничения» приведен список всех ограничений. Если ограничение не влияет на изменение ЦФ, то в графе «Статус» указывается значение не связан, это значение устанавливается для всех изменяемых ячеек, не равных нулю. В противном случае указывается значение связанное. В графе «Разница» указаны разности между нулевым и оптимальным значениями соответствующих ячеек.
  

После сохранения сценария и выбора отчета нажимаем кнопку ОК для завершения поиска решения и закрытия окна результатов. Отчет по результатам представлен на рис. 21.



Рис. 21. Фрагмент Листа Excel с отчетом по результатам поиска решения

• 
  Результаты решения задачи представлены на рис. 22.
  
• 
  Анализ результатов показывает, что минимальная стоимость проведения технического обслуживания составит 46000 рублей, государственное задание выполнено в полном объеме.
  

Рис. 22. Результаты поиска решения

• 
  Командой АНАЛИЗ «ЧТО ЕСЛИ» открываем диспетчер сценариев, выбираем свой сценарий, кнопкой ИЗМЕНИТЬ заходим в диалоговое окно «Изменение сценария», просматриваем ссылки, нажимаем кнопку ОК и проверяем значения изменяемых ячеек на соответствие с данными рабочего листа, если необходимо, внести коррективы значений в окне сценария (Рис. 23).
  

Рис. 23. Диалоговое окно просмотра (корректировки) значений изменяемых ячеек сценария
Сценарий №1.

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

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

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

Открываем рабочий лист Excel, где было получено решение по сценарию «Ремонт-1».

Изменяем исходные данные согласно новым условиям.

Выполняем поиск решения в новых условиях.

Полученный результат сохраняем в виде сценария с именем «Ремонт-2», просматриваем результаты сценария.

Результат выполнения представлен на рис. 24.


Рис. 24. Результат поиска решения при Сценарии №1
Анализ полученных результатов показывает, что минимальная стоимость проведения технического обслуживания не изменилась и составляет 46000 рублей, государственное задание выполнено в полном объеме.

Командой АНАЛИЗ «ЧТО ЕСЛИ» открываем диспетчер сценариев (Рис. 25).


Рис. 25. Диалоговое окно диспетчера сценариев
Из диспетчера сценариев можно выбрать нужный сценарий и кнопкой ВЫВЕСТИ получить соответствующие ему результаты.

Сохраняем задачу в виде книги Excel с именем «Ремонт-1-2.xls».
Сценарий №2.

В связи с наступлением чрезвычайной ситуации необходимо провести техническое обслуживание и ремонт всего парка пожарных автомобилей, с учетом складских запасов запасных частей.

Вносим изменение в компьютерную модель с учетом нового технического задания. Для этого выполняем аналогичные действия.

Полученный результат сохраняем в виде сценария с именем «Ремонт-3», просматриваем результаты сценария.

Результат выполнения представлен на Рис. 26.


Рис. 26. Результат поиска решения при Сценарии №2
Анализ полученных результатов показывает, что максимальная стоимость проведения технического обслуживания и ремонта увеличилась и составляет 70000 рублей, государственное задание выполнено в полном объеме, ремонт основных и специальных пожарных автомобилей выполнен сверх государственного задания.
Сценарий №3.

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

Вносим изменение в компьютерную модель с учетом нового технического задания. Для этого выполняем аналогичные действия.

Полученный результат сохраняем в виде сценария с именем «Ремонт-3», просматриваем результаты сценария.

Результат выполнения представлен на рис. 27.


Рис. 27. Результат поиска решения при Сценарии №3
Анализ полученных результатов показывает, что минимальная стоимость проведения технического обслуживания и ремонта уменьшилась и составляет 36000 рублей, государственное задание не выполнено в полном объеме (ремонт основных и специальных пожарных автомобилей не выполнен согласно государственного задания).
Сценарий №4.

С наступлением пожароопасного периода возникла необходимость первоочередного ремонта и технического обслуживания основной пожарной техники.

Вносим изменение в компьютерную модель с учетом нового технического задания. Для этого выполняем аналогичные действия.

Полученный результат сохраняем в виде сценария с именем «Ремонт-4», просматриваем результаты сценария.

Результат выполнения представлен на рис. 28.


Рис. 28. Результат поиска решения при Сценарии №4
Анализ полученных результатов показывает, что минимальная стоимость проведения технического обслуживания и ремонта уменьшилась и составляет 34000 рублей, государственное задание не выполнено в полном объеме (ремонт специальных и приспособленных пожарных автомобилей не выполнен согласно государственного задания).
Сценарий №5.

В связи с изменением государственного задания возникла необходимость первоочередного ремонта и технического обслуживания специальной пожарной техники.

Вносим изменение в компьютерную модель с учетом нового технического задания. Для этого выполняем аналогичные действия.

Полученный результат сохраняем в виде сценария с именем «Ремонт-5», просматриваем результаты сценария.

Результат выполнения представлен на рис. 29.


Рис. 29. Результат поиска решения при Сценарии № 5
Анализ полученных результатов показывает, что минимальная стоимость проведения технического обслуживания и ремонта увеличилась и составляет 58000 рублей, государственное задание не выполнено.

1. 
   Выполнить три произвольных сценария поступления новой техники с изменением переменных задачи.
   

При поступлении новой техники у вас должна изменяться потребность в материалах, в одном сценарии уменьшиться (получили технику с меньшими заправочными объемами), во втором сценарии увеличиться (получили технику с большими заправочными объемами).

Алгоритм модернизации модели:

Открыть рабочий лист MS Excel, где получено решение по начальному сценарию.

Изменить исходные данные с учетом изменившихся условий и удалить полученные ранее результаты.

Выполнить процедуру поиска решения.

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

Получить результаты поиска решения и оформить их в требуемой форме (в виде измененного плана-графика или производственной программы).

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

Открыть диспетчер сценариев.

Выбрать нужный сценарий, нажать Вывести требуемые результаты.

Сохранить задачу в книге MS Excel с оригинальным именем.