Лабораторная работа №5

По дисциплине: Методы оптимизации и исследование операций

Тема занятия: Решение задач оптимизации производственной программы с использованием ТП Ms Excel

Цель занятия: научиться находить оптимальное решение с использованием пакета «Поиск решений» в табличном процессоре MS EXCEL

Количество часов: 2

Содержание работы

1. Часть 1. Пример

2. Ход работы

3. Часть 2. Самостоятельная работа

4. Задания для самостоятельной работы

Пособия и инструменты:

Тетрадь для лабораторных работ. ПК. Табличный процессор MS EXCEL

Методические указания по выполнению

Часть 1. Решить приведенную ниже задачу в табличном процессоре ms excel.

Предприятие планирует выпускать четыре вида продукции. Объемы ресурсов трех видов (в условных единицах - УЕ), затраты каждого ресурса на изготовление единицы продукции и цена единицы продукции (в условных денежных единицах - УДЕ) приведены в таблице 5.1.

Таблица 5.1

Вид продукции Вид ресурса	П1	П2	П3	П4	Объем (УЕ)
Р1	3	2	4	5	4800
Р2	3	0	4	4	4200
Р3	10	8	6	8	10000
Цена на единицу продукции (УДЕ)	48	50	45	50

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

Ответьте на поставленные вопросы:

1. Сколько продукции каждого вида следует производить?

2. Какова ожидаемая максимальная выручка от продажи продукции?

3. Выяснить, какой из ресурсов наиболее дефицитен, какой из видов продукции наиболее убыточен?

4. На сколько изменится максимальная выручка, если запасы ресурсов каждого вида уменьшатся на 100 единиц?

Решение

Для того, чтобы ответить на поставленные вопросы, необходимо:

а) построить модель исходной и двойственной задач (в тетради и на чистом листе книги ЛР 5);

б) решить исходную задачу с использованием ТП MS EXCEL;

в) найти оптимальное решение исходной задачи;

г) при сохранения решения сохранить отчет по устойчивости и пределам, откуда найти оптимальное решение двойственной задачи;

д) данные отчетов занести в тетрадь.

Составим экономико-математическую модель данной (исходной) задачи (задачи об оптимальной производственной программе или задачи об оптимальном распределении ресурсов):

Z = 48x₁ + 50x₂ + 45x₃ + 50x₄  max, (1)

3x₁ + 2x₂ + 4x₃ + 5x₄  4800, (2)

3x₁ + 4x₃ + 4x₄  4200, (3)

10x₁ + 8x₂ + 6x₃ + 8x₄  10000, (4)

x_j  0,

а) Исходная задача записана в стандартной симметричной форме, поэтому построим двойственную ей ЗЛП, пользуясь правилами построения двойственных задач:

f = 4800у₁ + 4200у₂ + 10000у₃  min (5)

3у₁ + 3у₂ +10у₃  48, (6)

2у₁ + 8у₃  50, (7)

4у₁ + 4у₂ + 6у₃  45, (8)

5у₁ + 4у₂ + 8у₃  50, (9)

у₁  0, у₂  0, у₃ 0. (10)

Ход работы.

1. Загрузить ТП MS EXCEL.

2. Сформировать рабочий лист

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

4. Во вкладке Данные запустить надстройку Поиск решений.

5. Установить параметры в открывшемся окне «Поиск решений».

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

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

Y* = (3; 0; 5,5)

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

X* = (0; 560; 920; 0; 0; 520; 0)

Y* = (3; 0; 5,5; 16; 0; 0; 9)

9. На листе 3 решить задачу, уменьшив запасы ресурсов каждого вида на 100 единиц

10. Ответить на поставленные вопросы

Ответы на вопросы:

1. Каждого вида продукции следует производить: П₁= 0 (УЕ), П₂= 560 (УЕ), П₃ = 920 (УЕ), П₄ = 0 (УЕ).

2. Ожидаемая максимальная выручка от продажи продукции составляет Z_max = 69400 (УДЕ).

3. Р3 наиболее дефицитен (5,5 > 3), П1 наиболее убыточен (16 > 9).

4. Ожидаемая максимальная выручка от продажи продукции составляет Z_max = 68550 (УДЕ), т.е. уменьшится на 850 УДЕ.