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

Добавил пользователь Дмитрий К.
Обновлено: 04.10.2024

На этом шаге мы рассмотрим создание отчета по сценарию.

В диалоговом окне Диспетчер сценариев щелкните на кнопке Отчет . Появится диалоговое окно Отчет по сценарию , показанное на рисунке 1.

Рис. 1. Диалоговое окно Отчет по сценарию

Можно выбрать следующие типы отчета:

• Структура. Итоговый отчет будет иметь форму структурированного списка.
• Сводная таблица. Итоговый отчет будет иметь форму сводной таблицы.

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

В диалоговом окне Отчет по сценарию нужно указать ячейки результата, в котрых содержатся интересующие Вас формулы (для примера нужно выбрать ячейку B4 ). Excel создаст новый лист и поместит туда итоговую таблицу. На рисунке 2 показан отчет, созданный в результате выбора опции Структура , а на рисунке 3 - созданный в результате выбора опции Сводная таблица . Если Вы присвоили имена изменяемым ячейкам и ячейкам результата, то в таблице будут использованы эти названия. В противном случае будут показаны только адреса ячеек.

Рис. 2. Отчет, созданный с помощью средства Диспетчер сценариев

Рис. 3. Сводная таблица, созданная с помощью средства Диспетчер сценариев

Файл с данным примером можно взять здесь.

На следующем шаге мы рассмотрим ограниченность средства Диспетчер сценариев .

Для анализа полученного оптимального решения в MS Excel предусмотрены три типа отчетов: отчет по результатам, устойчивости и пределам.

Проведем анализ чувствительности задачи (2.1). Для этого необходимо после запуска в Excel задачи на решениев окне Результаты поиска решения выделить с помощью мыши три типа отчетов: Результаты, Устойчивость и Пределы (рис. 3.1).

Рис. 3.1. Типы отчетов

Отчет по результатам.Отчет по результатам состоит из трех таблиц (рис. 3.2):

1) таблица 1 содержит информацию о целевой функции;

2) таблица 2 содержит информацию о значениях переменных, полученных в результате решения задачи;

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

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

Рис. 3.2. Лист отчета по результатам

Для граничных условий (строки 24-27 на рис. 3.2) в графе Разница показана разность между значением переменной в найденном оптимальном решении и заданным для нее граничным условием.

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

Так, если на ресурс наложено ограничение типа

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

Если на ресурс наложено ограничение типа

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

Отчет по устойчивости. Отчет по устойчивости состоит из двух таблиц (рис.3.3).

Таблица 1 содержит информацию, относящуюся к переменным:

результирующие значения переменных;

нормированная стоимость, т.е. дополнительные двойственные переменные, которые показывает, на сколько изменяется целевая функция при принудительном включении единицы этой переменной в оптимальное решение;

коэффициенты целевой функции;

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

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

Таблица 2 (рис. 3.3) содержит информацию, относящуюся к ограничениям:

величина использованных ресурсовв колонке Результ. значение;

теневые цены, т.е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу. Теневая цена рассчитывается только для дефицитных ресурсов;

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

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

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

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

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

4 Двойственная задача. Теневые цены

По найденным результатам можно создавать отчеты. Такие отчеты полезны для сравнения влияния на решение различных ограничений или исходных данных. Отчеты бывают трех типов: Результаты, Устойчивость, Пределы.Тип выбирается по окончании поиска решения в диалоговом окне Результаты поиска решения(рис.2.10).

Рис. 2.10. Указание необходимых отчетов в диалоговом окне

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

Рис. 2.11. Содержание Отчета по результатам

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

· Таблица 1 приводит сведения о целевой функции. В столбце Исходное значение приведены значения целевой функции до начала вычислений.

· Таблица 2 приводит значения искомых переменных, полу­ченные в результате решения задачи.

· Таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий.

Для Ограниченийв графе Формула приведены зависимости, ко­торые были введены в диалоговое окно Поиск решения; в графе Значение приведены величины использованного ресурса; в графе Разница показано количество неиспользованного ресур­са. Если ресурс используется полностью, то в графе Состояние указывается связанное; при неполном использовании ресурса в этой графе указывается не связан.

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

Рис.2.12. Содержание Отчета по устойчивости

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

Отчет по устойчивости состоит из двух таблиц.

В таблице 1 приводятся следующие значения для переменных:

· результат решения задачи;

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

· коэффициенты целевой функции;

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

В таблице 2 приводятся аналогичные значения для ограничений:

· величина использованных ресурсов;

· теневая цена, т. е. двойственные оценки, которые показывают, как изменится целевая функция при изменении ресурсов на единицу;

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

С помощью Отчета по устойчивости можно провести анализ полученного оптимального решения по следующим направлениям:

1. изучение устойчивости оптимального плана на изменение оценок целевой функции и первоначальных объемов производственных ресурсов;

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

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

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

1. В столбце Нормировання стоимостьзначения имеют две переменные : Прод2 (-10) и Прод4 (-20). Эти вели­чины показывают, насколько уменьшится целевая функция при принудительном выпуске единицы данной продукции. Следова­тельно, если мы захотим принудительно выпустить единицу продукции ПродЗ, то целевая функция F уменьшится на 10 единиц и будет равна 1320 -10 х 1 = 1310, а для Прод4 при тех же условиях .F= 1320-20 х 1 = 1300. Иногда приходится идти на уменьшение прибыли и принудительно включать неэффективную с точки зрения прибыли продукцию в план производства: при наличии долгосрочных договоров, при переходе на новую технологию (например, чтобы не останавливать производство), для решения технологических задач ( например, соблюдение севооборота в аграрном производстве). При этом, чтобы уменьшить потери для такой продукции обычно вводят ограничение на ее производство, так называемые, граничные условия.

2. Показатели Целевой коэффициент (c_j), Допустимое увеличение и Допустимое уменьшение (Δc_j)Эти показатели определяют пределы изменения c_j при кото­рых сохраняется структура оптимального плана, т. е. будет выгодно по-прежнему выпускать, например, продукцию х_1. Для нее нижний предел т.е. допустимое уменьшение равен 12, верхний предел, т.е. до­пустимое увеличение равен 40. Если от пределов приращений Δc₁ перейти к пределам значе­ния величины c₁, то можно записать

Таким образом, при изменении c₁ в пределах

будет по-прежнему выгодно выпускать продукцию x₁. При этом значение целевой функции будет

Если выполнить аналогичные преобразования с c₂, с₃, с₄, то получим

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

3. Показатели в таблице ограничения, а именно, Допустимое увеличение и Допустимое уменьшениепоказывают влияние изменения ресурсов на примере измене­ния имеющегося количества сырья. При изменении трудовых ресурсов на Δbi ограничение для них будет иметь вид:

Аналогично можно получить значения для Δb₂, Δb₃ и записать

Переход от Δb_i к пределам b_i производится по зависимостям

и в результате получим

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

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

При этом целевая функция будет

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

При этом получим

В данном случае целевая функция будет

Поясним эти зависимости на следующем примере. Пусть уве­личение финансов составляет

Видимо, было трудно представить, что при увеличении финансов для обеспечения максимизации прибыли выпуск продукции х₁ целесообразно уменьшить, а выпуск продукции х₃ — увеличить. Такое решение объясняется следующим. Как видно из условий задачи, прибыль с единицы продукции с₃= 120, т. е. единица продукции ПродЗ в 120/60 = 2 раза дает большую прибыль по сравнению с единицей продукции вида Прод1. В связи с этим оказалось целесообразным такое перераспределе­ние выпуска продукции.

5. Показатель в таблице ограничения – Теневая ценадает нам информацию о степени хозяйственной значимости, степень дефицитности данного ресурса в конкретных условиях и имеют большое значение в экономическом анализе. В рассматриваемом примере трудовые ресурсы и финансы ис­пользовались полностью, поэтому их дополнительные пере­менные равны нулю. Если ресурс исполь­зуется полностью, то его увеличение или уменьшение повлияет на объем выпускаемой продукции и, следовательно, на вели­чину целевой функции. Для трудовых ресурсов теневая цена равна 20, а для финансов –10.

При увеличении (уменьшении) трудовых ресурсов на единицу, целевая функция увеличится (уменьшится) на 20 единиц и будет равна:

· при увеличении F = 1320 + 20 х 1 = 1340,

· при уменьшении F = 1320 - 20 х 1 = 1300.

Аналогично обстоит дело и с финансами. При увеличена (уменьшении) финансов на единицу целевая функция будеп равна

• при увеличении F = 1320 + 10 х 1 = 1330,
• при уменьшении F = 1320 - 10 х 1 = 1310.

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

Рис. 2.13. Содержание Отчета по пределам

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

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

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

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

F = c₁x₁ + c₃x₃ = 60 x 0 + 120 x 6 = 720. Соответственно верхним пределом называется пределы изменения x_j и значение целевой функции, вошедшей в оптимальное решение. Поэтому везде F = 60 x 10 + 120 x 6 = 1320

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

САМОСТОЯТЕЛЬНАЯ РАБОТА

Цель: закрепить полученные навыки создания экономической модели и ее решения средствами Excel:

· создать экономико-математическую модель

· выполнить подготовку ее решения в Excel

· получить решение, выполнить анализ решения на основании отчетов и дать ему экономическую оценку

Для самостоятельной работы студенту предлагается две задачи. Одна выполняется в лаборатории под руководством преподавателя, вторая предлагается как домашнее задание. Следует учитвать, что задачи носят сквозной характер и их решение может быть использовано на последующих этапах изучения данного курса, поэтому полученное решение рекомендуется сохранять. Для сохранения результатов самостоятельной работы следует в своей папке создать отдельный файл в Excel. под именем ЭММ2.

Условия задачи 1.

Пусть требуется определить оптимальное сочетание трех культур – пшеницы, ячменя и картофеля. Бригада располагает следующими производственными ресурсами: пашня –500 га, труда – 4200 человеко-дней и финансами на сумму 100000 грн. Причем, площадь под зерновыми культурами не может быть менее 250 га. Критерий оптимальности – максимум дохода. Технико-экономические коэффициенты имеют следующие значения (табл)

Таблица 2.2. Исходные данные для построения модели

Культуры	Затраты на 1 га посева	Стоимость валовой продукции с 1 га, грн.
труд, человеко-дни	финансы грн
Пшеница (X1)
Ячмень ( X2)
Картофель ( X 3)

Условия задачи 2.

Предприятие имеет запасы 4-х видов ресурсов (мука, жиры, сахар, финансы), которые используются для производства 2 видов продуктов (хлеб и батон). Известны нормы расхода ресурсов на единицу продукции, запасы ресурсов, цена на единицу продукции и спрос на нее. Критерий оптимальности: максимум дохода от продажи продукции

Таблица 2.3.Исходные данные для построения модели

Ресурсы	Нормы расхода	Запасы
Хлеб	Батон
Мука	0,6	0,5
Жиры	0,05	0,08
Сахар	0,2	0,6
Финансы	0,2	0,24
Цена	0,99	1,21
Спрос (верхний.)
Спрос (нижний.)

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Сформулируйте задачу линейного программирования

2.Дайте определение для следующих понятий: план, допустимый план, оптимальный план

3. Чем отличается общая задача линейного программирования от канонической?

4. Сформулируйте основные этапы стандартной итерации симплекс-метода

5. Какой инструмент Excel обеспечивает решение задач оптимизации и основные этапы этого решения? Как он устанавливается?

7. Какие отчеты получает пользователь после решения задачи и какая инфрмация в них используется для анализа?

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

9. Какую информацию для анализа оптимального решения дает нам Отчет по устойчивости?

10. Какую информацию для анализа оптимального решения дает нам Отчет по пределам?

Лабораторная работа № 6. Вариантный анализ полученного решения линейной модели

Читайте также:

  
• Шакотис как сделать
  
• Серьги из джинсовой ткани своими руками
  
• Тренажер для ног после инсульта своими руками
  
• Как сделать чтобы при заходе на сервер дискорд выдавалась роль
  
• Паскаль абс как сделать чтобы возраст посчитал