Предметно-аналитическая справка по решению производственной оптимизационной задачи с использованием Excel в рамках дисциплина «Математич

Предметно-аналитическая  справка по решению  производственной оптимизационной  задачи с использованием Excel в рамках дисциплина «Математические методы в экономике». 

     Из  двух видов ресурсов (время, чел. - час; площадь, м²) продает 3 типа продукции (1, 2, 3). Затраты времени на продажу одной партии товаров 1-го вида = 0,5 чел. – час, на продажу одной партии товаров 2-го вида = 0,7 чел. – час, на продажу одной партии товаров 3-го вида = 0,6 чел. – час. Затраты площади на продажу одной партии товаров 1-го вида = 0,8 м², на продажу одной партии товаров 2-го вида = 0,9 м², на продажу одной партии товаров 3-го вида = 1,0 м². В наличие есть  времени – 370 чел. – час и площади – 90 м². Прибыль, получаемая от реализации одной партии товаров 1-го вида = 5 у.е., от реализации одной партии товаров 2-го вида = 8 у.е., от реализации одной партии товаров 3-го вида = 6 у.е.

     Математическая  модель задачи.

Управляющие переменные:

x_j (j=1,2,3) - количество партий проданной продукции j-го вида (шт.)

Целевая функция  Z(x_j) – суммарная прибыль от реализации всей произведенной продукции. Целевая функция стремится к максимуму, так как нам следует максимизировать прибыль.

Z (x_j) =5x₁+8x₂+6x₃       max

Ограничения на количество используемых ресурсов и  условие неотрицательности переменных:

    0,5х₁+0,7х₂+0,6х₃ <=370

    0,8x₁+0,9x₂+x₃ <= 90                         (1)

    x_j >= 0 (j=1, 2, 3)

     Решение задачи.

Для нахождения оптимального решения поставленной задачи, т.е. оптимальную структуру  товарооборота X* = (x*₁, x*₂, x*₃), обеспечивающего максимальное значение прибыли Z* при выполнении ограничений на ресурсы и условия неотрицательности, используем программу «Поиск решения» в EXCEL. В качестве исходных данных задаем нулевые значения управляющих переменных x_j = 0 (j =1, 2, 3). Для вычисления целевой функции и левых частей неравенств, указанных в системе ограничений, используем встроенную функцию EXCEL «СУММПРОИЗВ».

Вид ресурса	Норма расхода  на 1 партию товара			Объем ресурсов	Использовано  ресурсов
	1	2	3
Время, чел.-час	0,5	0,7	0,6	370	0
Площадь, м2	0,8	0,9	1	90	0
Прибыль	5	8	6
					Суммарная прибыль
Количество  партий продукции	0	0	0		0

 

Оптимальное решение  приведено в таблице:

Вид ресурса	Норма расхода  на 1 партию товара			Объем ресурсов	Использовано  ресурсов
	1	2	3
Время, чел.-час	0,5	0,7	0,6	370	70
Площадь, м2	0,8	0,9	1	90	90
Прибыль	5	8	6
					Суммарная прибыль
Количество  партий продукции	0	100	0		800

 

Видно, что для  выполнения вышеуказанной системы  ограничений необходимо положить x*₁=0 и x*₃=0.

Таким образом, для получения суммарной прибыли Z* = 800 при данном количестве ресурсов необходимо продавать 100 партий товара второго вида и не продавать товары первого и третьего вида, X* = (0, 100, 0). 
 

     Расшифровка отчетов.

Программой «Поиск решения» были сформированы отчеты по результатам, устойчивости и пределам.

4.1. Отчет по  результатам.

Целевая ячейка (Максимум)           Ячейка Имя Исходное  значение Результат       $F$7 Суммарная прибыль 0 800                                 Изменяемые  ячейки           Ячейка Имя Исходное  значение Результат       $B$7 Количество партий продукции 1-го вида 0 0       $C$7 Количество партий продукции 2-го вида 0 100       $D$7 Количество партий продукции 3-го вида 0 0                                 Ограничения           Ячейка Имя Значение Формула Статус Разница   $F$3 Время, чел.-час Использовано ресурсов 70 $F$3<=$E$3 не связан. 300   $F$4 Площадь, м2 Использовано ресурсов 90 $F$4<=$E$4 связанное 0

 

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

     В третьей таблице приведены номер  и название ячеек, в которых находятся  левые части вышеуказанной системы  ограничений, а также их результирующие значения. В столбце «Формула» ограничения (1) записаны через номера ячеек левых и правых частей ограничений (1). «Статус» «связанное» означает, что оптимальное решение получается при выполнении равенства в соответствующем ограничении. «Статус» «не связанное» означает, что оптимальное решение получается при выполнении строгого неравенства в соответствующем ограничении. В столбце «Разница» приведена разность между правой и левой частями ограничений (1) (без учета знака). Для задачи данного типа (производственной) «Разница» - это количество неиспользованного ресурса. В данной задаче не использовано 300 единиц ресурса «Время», а ресурс «Площадь» использован полностью.

     4.2. Отчет по устойчивости.

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

Изменяемые  ячейки                 Результ. Нормир. Целевой Допустимое Допустимое   Ячейка Имя значение стоимость Коэффициент Увеличение Уменьшение   $B$7 Количество партий продукции 1-го вида 0 -2,111111111 5 2,111111111 1E+30   $C$7 Количество партий продукции 2-го вида 100 0 8 1E+30 2,375   $D$7 Количество партий продукции 3-го вида 0 -2,888888889 6 2,888888889 1E+30

     Представляют  интерес значения, приведенные в столбце «Нормированная стоимость», соответствующие управляющим переменны равным нулю. Значения «Нормированной стоимости» показывают, на сколько изменится целевая функция (в данной задаче прибыль) при принудительном выпуске 1 партии данного типа продукции. Отметим, что все ограничения должны по-прежнему выполнятся. Для этого в систему ограничений (1) необходимо добавить ограничение x_j = 1 или x_j >= 1 и снова запустить программу поиск решения.

     Для данной задачи при принудительном выпуске  1 партии товара 1-го вида и 1 партии товара 3-го вида прибыль должна уменьшиться на 5 ( значение соответствующей нормированной стоимости товара 1-го вида = -2,111111111 и значение соответствующей нормированной стоимости товара 3-го вида = - 2,888888889) и составит Zновое = 800 – 5 = 795 (см. таблицу). 
 

Вид ресурса	Норма расхода  на 1 партию товара			Объем ресурсов	Использовано  ресурсов
	1	2	3
Время, чел.-час	0,5	0,7	0,6	370	69,7
Площадь, м2	0,8	0,9	1	90	90
Прибыль	5	8	6
					Суммарная прибыль
Количество партий продукции	1	98	1		795

 

     Отметим, что значения «Нормированной стоимости» для x₁ = 0 и x₂ = 0 не равны нулю, следовательно, данная задача имеет единственное решение.

     В столбце «Целевой коэффициент» приведены  значения коэффициентов при управляющих переменных в целевой функции. Столбцы «Допустимое увеличение» и «Допустимое уменьшение» показывают, в каких пределах может изменяться целевой коэффициент, чтобы сохранялся оптимальный план X* = (x*₁, x*₂, x*₃). При этом, если был изменен целевой коэффициент при x*_j = 0 (например, x*₁ или x*₃), то значение целевой функции остается неизменным; если был изменен целевой коэффициент при x*_j ≠ 0 (например, x*₂), то значение целевой функции изменится. Например, для товара 2-го вида целевой коэффициент (прибыль от реализации одной партии товара 2-го вида) с₂ = 8. Диапазон допустимых изменений данного коэффициента: 8 - 2,375 ≤ с₂ ≤8 + 1E+30, т.е. 5,625  ≤ c₂ ≤ 1E+30.

     Увеличим c₂ в допустимых пределах изменения. Пусть c₂ = 8,5. Как видно из приведенной ниже таблицы оптимальный план сохранился, целевая функция (суммарная прибыль) увеличилась. 
 
 
 
 

Вид ресурса	Норма расхода  на 1 партию товара			Объем ресурсов	Использовано  ресурсов
	1	2	3
Время, чел.-час	0,5	0,7	0,6	370	70
Площадь, м2	0,8	0,9	1	90	90
Прибыль	5	8,5	6
					Суммарная прибыль
Количество  партий продукции	0	100	0		850

 

     Во  второй таблице отчета

Ограничения                 Результ. Теневая Ограничение Допустимое Допустимое   Ячейка Имя значение Цена Правая  часть Увеличение Уменьшение   $F$3 Использовано ресурсов Время, чел.-час 70 0 370 1E+30 300   $F$4 Использовано ресурсов Площадь, м2 90 8,888888889 90 385,7142857 90