Как сделать отчет об устойчивости в excel

Анализ устойчивости в Excel

Проведем анализ устойчивости в Excel на примере задачи о прядильной фабрике.

Пример 2. Прядильная фабрика для производства четырех видов пряжи использует три типа сырья – чистую шерсть, капрон, акрил. В табл.1 указаны нормы расхода сырья, его общее количество, которое может быть использовано фабрикой в течение года; загрузка оборудования при производстве тонны пряжи и прибыль от реализации тонны пряжи каждого вида. Годовой ресурс оборудования составляет 140 тыс. маш.ч.

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

Таблица 1. Исходные данные

Тип сырья Нормы расхода сырья на 1 т. пряжи Количество сырья (т.)
Вид 1 Вид 2 Вид 3 Вид 4
Шерсть Капрон Акрил 0,5 0,1 0,4 0,2 0,6 0,2 0,3 0,4 0,3 0,2 0,5 0,3
Загрузка оборудования в тыс. маш.ч 0,06 0,04 0,03 0,09
Прибыль от реализации 1 т пряжи (в $)

Обозначим: – объем производства пряжи i-го типа в тоннах.

На рис. 14 представлена заполненная форма для ввода условия задачи в Excel.

Диалоговое окно «Поиск решения» для рассматриваемой задачи с введенными данными приведено на рис. 15.

Рис. 15. Диалоговое окно «Поиск решения»

Рис. 16. Диалоговое окно «Результаты поиска решения»

Результаты решения задачи выводятся в форму ввода условий задачи: =235,29; =0; =0; =1352,9; ЦФz=18823529,41 (рис. 17), отчеты по результатам, по устойчивости и по пределам выводятся на отдельных листах в текущей рабочей книге Excel.

Microsoft Excel 11.0 Отчет по результатам
Целевая ячейка (Максимум)
Ячейка Имя Исходное значение Результат
$F$6 коэффициенты ЦФ 0,00 18823529,41
Изменяемые ячейки
Ячейка Имя Исходное значение Результат
$B$3 значение x1 235,29
$C$3 значение x2
$D$3 значение x3
$E$3 значение x4 1352,94
Ограничения
Ячейка Имя Значение Формула Статус Разница
$F$12 Акрил левая часть 500,00 $F$12 =0 не связан. 235,3
$C$3 значение x2 $C$3>=0 связанное
$D$3 значение x3 $D$3>=0 связанное
$E$3 значение x4 1352,94 $E$3>=0 не связан.

Рис. 18- Отчет по результатам

По ресурсам приводится следующая информация: формула, соответствующая левой части ограничения; имя ограничения; значение (величина) использованного ресурса при оптимальном решении задачи; формула, задающая ограничение; статус ограничения и разница. Если ресурс используется полностью (то есть ресурс дефицитный), то в графе «Статус» («Состояние») соответствующее ограничение указывается как «связанное»; при неполном использовании ресурса (то есть ресурс недефицитный) в этой графе указывается «не связан». В графе «Разница» показана разность между значением использованного ресурса и исходно заданной величиной ресурса. Аналогичная информация приводится по переменным задачи: оптимальное значение, статус (связанная, если оптимальное значение переменной не нулевое; несвязанная в противном случае), разность между оптимальным значением переменной и заданным для нее граничным условием.

В задаче о прядильной фабрике полученное оптимальное решение означает выпуск первого и четвертого типов пряжи (базисные переменные: =235,29; =1352,9), выпускать пряжу второго и третьего типов не выгодно ( =0; =0). При таком плане выпуска полностью будут использованы ресурсы (запасы) акрила и капрона, а запасы шерсти и ресурс оборудования избыточны.

Отчет по результатам дает информацию для анализа возможного изменения запасов недефицитных ресурсов при сохранении полученного оптимального значения ЦФ. Если на ресурс наложено ограничение типа ≤, то в графе «Разница» дается количество ресурса, которое не используется при реализации оптимального решения. Например, используется 388.24 тонны шерсти. Неизрасходованным остается 211.76 тонн из общих запасов шерсти, на это количество можно уменьшить ресурс «шерсть» без изменения оптимального решения. Аналогично можно уменьшить ресурсы оборудования на 4.12 тыс. часов и это не повлияет на оптимальное решение.

Если на ресурс наложено ограничение типа ≥, то в графе «Разница» дается количество ресурса, на которое была превышена минимально необходимая норма. Если на эту величину увеличить ресурс, оптимальное решение задачи не изменится.

Отчет по устойчивости. На рис. 19 представлен отчет по устойчивости для задачи примера 1, который состоит из двух частей: информация по переменным и информация по ограничениям.

Microsoft Excel 11.00 Отчет по устойчивости
Изменяемые ячейки
Рез. Нормир. Целевой Допусти -мое Допусти -мое
Ячейка Имя знач. стоимость Коэфф. увели -чение умень- шение
$B$3 x1 235,3
$C$3 x2 -1352,9 1352,94 1E+30
$D$3 x3 -1617,7 1617,65 1E+30
$E$3 x4 1045,45
Ограничения
Рез. Теневая Ограни- чение Допусти -мое Допусти -мое
Ячейка Имя знач. Цена Правая часть увели -чение умень- шение
$F$12 акрил 25294,1 33,33
$F$11 капрон 8823,5 38,89 514,29
$F$10 шерсть 0,00 1E+30 211,76
$F$13 обору дование 0,00 1E+30 4,12

Рис. 19- Отчет по устойчивости

Нормированная стоимость показывает, на сколько изменится значение ЦФ в случае принудительного включения единицы этой продукции в оптимальное решение. Например, в отчете по устойчивости для рассматриваемой задачи нормированная стоимость для второго вида пряжи равна: -1352.94. Это означает, что если включить в план выпуска 1 тонну пряжи второго вида, то новый план выпуска принесет прибыль на 1352.94$ меньше, чем прежний оптимальный план. Нормированная стоимость для базисных переменных всегда равна нулю.

Предельные значения приращения целевых коэффициентов. Для каждой переменной указаны заданные коэффициенты ЦФ, допустимые увеличение и уменьшение коэффициентов при которых сохраняется оптимальное решение задачи. Например, допустимое увеличение цены на пряжу первого вида равно 2875$ за тонну, а допустимое уменьшение – 8600$. Это означает, что если цена на тонну пряжи первого вида возрастет не более чем на 2875$, например станет равной 13875$, то оптимальное решение сохранится, изменится только значение ЦФ в оптимальной точке.

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

Далее в отчете по устойчивости приводится информация, относящаяся к ограничениям. В колонке «Результирующее значение» приводится величина использованных ресурсов.

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

Теневая цена (ценность дополнительной единицы i-го ресурса). Теневая цена показывает насколько возрастет значение ЦФ в случае выделения дополнительной единицы i-го ресурса. Очевидно, что теневая цена не нулевая только для дефицитных ресурсов. Например, если запасы акрила возрастут на 1 тонну, прибыль увеличится на 25294,12$, если запасы капрона возрастут на 1 тонну, то прибыль будет на 8823.53$ больше, чем исходная. Поэтому в первую очередь для фабрики выгодно увеличивать запасы акрила.

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

Отчет по пределам. Для рассматриваемой задачи отчет по пределам приведен на рис. 20.

Microsoft Excel 11.0 Отчет по пределам
Целевое
Ячейка Имя Знач.
$F$6 ЦФ
Изменяемое Ниж. Целевой Верх. Целевой
Ячейка Имя Знач. пред. результат пред. рез.
$B$3 x1 235,29 16235294,1 235,3
$C$3 x2 18823529,4
$D$3 x3 18823529,4
$E$3 x4 1352,94 2588235,3 1352,9

Рис. 20. Отчет по пределам

В отчете по пределам показано в каком диапазоне могут изменяться значения переменных, без изменения базиса (номенклатуры выпуска продукции). Например, если будет выпускаться 235 тонн пряжи первого вида, то в оптимальном решении ненулевые переменные будут соответствовать объемам выпуска первого и четвертого видов пряжи. В случае выпуска более чем 235.29 тонн пряжи первого типа номенклатура выпуска продукции изменится. Также в отчете по пределам приводится информация о величине ЦФ при нижнем и верхнем предельных значениях переменных задачи.

Содержание

1. Настройка MS Excel 4

2. Подготовка листа с исходными данными. 5

Порядок заполнения листа. 6

3. Установка данных для пакета «Поиск решения». 6

4. Получение результатов решения. 8

5. Типы отчетов: 9

6. Решение в Excel задач ЦЛП.. 10

7. Пример решения транспортной задачи. 11

8 Исследование устойчивости решения задачи ЛП.. 12

Анализ сокращения или увеличения ресурсов: 13

Microsoft Excel 8.0 Отчет по устойчивости

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

Отчет по устойчивости имеет две различные формы: отчет по устойчивости решений, полученных с помощью линейных моделей оптимизации и нелинейных.

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

37

Часть 1. Поиск решений на электронных таблицах

Основной вопрос, освещаемый в этом отчете: насколько устойчиво найденное оптимальное решение по отношению к возможным изменениям параметров задачи. Любая строка любой таблицы этого отчета говорит о том, какие изменения можно произвести по отношению к ячейке (столбец «Ячейка») при условии, что содержимое остальных ячеек определяется оптимальным решением.

Отчет по устойчивости длялинейной модели

Отчет состоит из двух разделов: изменяемые ячейки и ограниче­ния.

Рабочий лист: [Задача о красках.хфРешение задачи Отчет создан: 19.02.01 13:31:42

Ячейка Имя Результ. значение Нормир. стоимость Целевой Допустимое Коэффици- Увеличениеент Допустимое Уменьшение
$В$23 Краска_Н 3,33 0,00 3 1
$В$24 Краска_В 1,33 0,00 2 4 0,5
Ограничения
Ячейка Имя Результ.значение Теневая Цена Ограничение Допустимое Правая часть Увеличение Допустимое Уменьшение
$Е$16 П1 Суточ-ный_расход исх. продукте 6,00 0,33 6 1 1Е+30
$Е$17 П2 Суточ- 8,00 1,33 8 1Е+30 2′

ный_расход исх.продукто в(т)

Раздел «Ограничения» связан с анализом связанных ограничений на возможность изменения их правых частей (запасов дефицитных ресурсов) в пределах постоянства теневой цены.

Для дефицитных ресурсов (в нашей задаче это запасы исходных продуктов, сохраняемые в ячейках Е16, Е17) важен вопрос: какое до­полнительное увеличение целевой функции может обеспечить увели­чение запасов ресурса. Ответ на этот вопрос связан с использованием понятия теневой цены (скрытой цены, двойственной цены, ценности ресурса):

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

Анализ отчетов

Так, теневая цена продукта П1 (ячейка Е16) определена как 0,333333 (тыс. долл./т). Это означает, что увеличение запаса П1 на тонну увеличит целевую функцию примерно на 333 долл. Аналогич­ное влияние на изменение значения целевой функции оказывает те­невая цена продукта Ш (ячейка Е17), которая определяется значени­ем 1,333333 (тыс. долл./т).

Теневая цена определяет скрытые возможности реорганизации системы путем изменения запасов дефицитных ресурсов, изменения организации использования этих ресурсов (расширение складов и т. п.). Кроме того, теневая цена позволяет ранжировать такие ресурсы с точки зрения их полезности для расширения производства.

Например, в нашем примере сравнение теневых цен Ш и П2 по­казывает, что при стремлении увеличить общую прибыль от продажи краски предпочтение нужно отдать увеличению запасов продукта П2, поскольку он имеет большую теневую цену.

Однако анализ найденного оптимального решения с помощью те­невых цен имеет смысл только в определенных пределах. Эти пределы указывают, в каких границах изменение запасов того или иного дефи­цитного ресурса влияет на найденное оптимальное решение задачи и значение целевой функции. В этом разделе отчета утверждается, что запасы ресурса П1 могут изменяться в пределах (6 — 1, 96; 6+1) тонн, дальнейшее увеличение или уменьшение запаса не будет оказы­вать влияния на оптимальное решение задачи (т. е. соответствующее ограничение по запасу ресурсов станет избыточным, а сам ресурс .пе­рейдет в категорию недефицитных). Аналогично значения (8 — 1, 95) и (8 + 4) определяют пределы возможных изменений запасов другого дефицитного ресурса П2. Увеличение П2 в этом интервале от 8 т до 12 т приведет к изменению оптимального решения задачи и увеличе­нию значения целевой функции, уменьшение запаса П2 с 8 т до при­мерно 6 т — к уменьшению найденного значения целевой функции.

Другими словами, четыре правых столбца таблицы ограничений определяют теневую цену ресурса и диапазон возможных изменений запасов этого ресурса, в котором теневая цена остается для данной за­дачи постоянной. Любое изменение дефицитного ресурса в пределах постоянства теневой цены приводит к изменению оптимального ре­шения задачи и значения целевой функции.

Для любого недефицитного ресурса теневая цена равна нулю, поэто­му для этого типа ресурсов интересен один вопрос: насколько можно снизить запасы ресурса при сохранении найденного оптимального ре­шения. Ответ на этот вопрос можно получить при анализе отчета по результатам.

Часть 1. Поиск решений на электронных таблицах

Анализ Отчетов

Третья строка таблицы «Ограничения» отчета по устойчивости ха­рактеризует несвязанное ограничение по сбыту. Фактически эта строка не добавляет никакой информации к содержанию аналогичной строки в отчете по результатам: недефицитный ресурс сбыта может быть со­кращен на 3 т и неограниченно увеличен (величина 1E + 30 в этом смысле просто большое число, представленное в научном формате).

В разделе «Изменяемые ячейки» определяется нормированная стоимость (редуцированная стоимость) единицы изменяемой ячейки (в нашем примере тонны краски) и анализируются возможные изме­нения коэффициентов целевой функции (в нашем примере это стои­мости тонны краски Н и В).

Редуцируемая стоимость (Рс) единицы продукции определяет раз­ницу между ее стоимостью (Ст) и производственными затратами на ее изготовление (Пз): Рс= СтПз.

Первая строка таблицы «Изменяемые ячейки» посвящена анализу ячейки В23, в которой содержится оптимальное значение производи­мого объема краски Н — 3,33 т.

Целевой коэффициент, определяющий стоимость первой тонны краски Н, имеет значение 3 (тыс.$/т). Производственные затраты на изготовление тонны краски Н связаны с расходами исходных продук­тов Ш и П2, которые относятся к дефицитным ресурсам.

В соответствии с условиями задачи на изготовление тонны краски Н требуется 1 т продукта Ш и 2 т продукта П2, следовательно, затра­ты на производство тонны краски Н определяются выражением:

Пз =1 (т П1/т Краски_Н) * Теневая_Цена_Ш (тыс.$/т П1) + + 2 (т П2/т Краски_Н) * Теневая_Цена_П2 (тыс. $/т П2).

Подставляя сюда значения теневых цен П1 и П2 из таблицы «Ограничения», получим:

Пз =1 * 0,333333 + 2 * 1,333333 = 3 (тыс.$/т краски_Н).

Поскольку стоимость тонны краски Н составляет 3 (тысдолл./т), редуцируемая стоимость тонны Краски_Н будет определяться значе­нием:

Рс = Ст — Пз = 3 — 3 = 0 (тыс.$/т краски_Н).

Аналогично определяется редуцируемая стоимость единицы варь-ируемой переменной ячейки В24 — тонны краски В.

Нулевые значения редуцируемой стоимости свидетельствуют полной реализации всех потенциальных возможностей, связанных с по-!

лучением прибыли от данного вида производственной деятельности

(производства краски). В этом и заключается оптимальное решение задачи.

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

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

Два последних столбца таблицы «Изменяемые ячейки» определя­ют возможные вариации целевых коэффициентов, при которых сохра­няется оптимальное решение задачи, но изменяется оптимальное значе­ние целевой функции.

При изменении целевого коэффициента 3 (это стоимость 1 т кра­ски Н, в тыс. долл.) в пределах (3 — 2 = 1;3 + 1 = 4)и изменении це­левого коэффициента 2 (стоимость 1 т краски В) в пределах (1,5; 6) оптимальное решение в ячейках В23,В24 сохранится, но значение це­левой функции изменится. Например, для прежних объемов произ­водства краски (3,33 т краски Ни 1,33 т краски В) и допустимых но­вых целевых коэффициентах 4 и 6 значение общей прибыли будет:

4 (тыс.$/т) * 3,33т + 6 (тыс. $/т) * 1,33т = 21,33 (тыс.$).

С другой стороны использование минимально возможных значений целевых коэффициентов приведет к получению прибыли в размере:

1 (тысдолл./т) * 3,33т +1,5 (тысдолл./т) * 1,33т = 5,33 (тысдолл.).

Еще раз подчеркнем, что эти изменения общей прибыли могут быть получены только за счет изменения цен на краску без изменения оптимального соотношения объемов производства, т. е. фактически без какой-либо реорганизации исследуемой системы.

Отчет по устойчивости для нелинейной модели

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

Нормированный градиент является «нелинейным аналогом» реду­цируемой стоимости для линейной модели, а множитель Лагранжа — аналогом теневой цены в малой окрестности точки оптимума. Это утверждение позволяет построить лишь некоторые весьма приблизи­тельные аналогии с линейной моделью. Оба этих понятия являются

Часть 1. Поиск решений на электронных таблицах

Анализ отчетов

математическими, а не экономическими и должны интерпретировать­ся математиком, а не экономистом или менеджером.

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Ссылка на основную публикацию