Отчет об устойчивости 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.

A	B	C	D	E	F	G	H
Переменные
имя	x1	x2	x3	x4
значение
ЦФ	направление
коэффи-циенты ЦФ	=СУММПРОИЗВ (B3:E3;B6:E6)	макс
ресурсы	коэффициенты	левая часть	знак	правая часть
шерсть	0,5	0,2	0,3	0,2	=СУММПРОИЗВ (B3:E3;B10:E10)	<=
капрон	0,1	0,6	0,4	0,5	=СУММПРОИЗВ (B3:E3;B11:E11)	<=
акрил	0,4	0,2	0,3	0,3	=СУММПРОИЗВ (B3:E3;B12:E12)	<=
ресурсы оборудо-вания	0,06	0,04	0,03	0,09	=СУММПРОИЗВ (B3:E3;B13:E13)	<=

Рис. 14- Форма ввода условия задачи в Excel

После нажатия кнопки «Выполнить» выдается диалоговое окно «Результаты поиска решения». Для получения отчетов по устойчивости необходимо выбрать тип отчета (можно задать все виды): «Результаты», «Устойчивость», «Пределы» и нажать кнопку «ОК» (рис. 16).

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

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

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

A	B	C	D	E	F	G	H
Переменные
имя	x1	x2	x3	x4
значение	235,29	1352,9
ЦФ	направление
коэффи-циенты ЦФ	18823529,4	макс
ресурсы	коэффициенты	левая часть	знак	правая часть
шерсть	0,5	0,2	0,3	0,2	388,24	<=
капрон	0,1	0,6	0,4	0,5	700,00	<=
акрил	0,4	0,2	0,3	0,3	500,00	<=
ресурсы оборудо-вания	0,06	0,04	0,03	0,09	135,88	<=

Рис. 17. Результат решения задачи

Отчет по результатам. На рис. 18 представлен отчет по результатам для задачи примера 1.

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

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

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<=$H$12	связанное
$F$11	Капрон левая часть	700,00	$F$11<=$H$11	связанное
$F$10	Шерсть левая часть	388,24	$F$10<=$H$10	не связан.	211,8
$F$13	Ресурсы оборудования левая часть	135,88	$F$13<=$H$13	не связан.	4,12
$B$3	значение x1	235,29	$B$3>=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 тонн пряжи первого типа номенклатура выпуска продукции изменится. Также в отчете по пределам приводится информация о величине ЦФ при нижнем и верхнем предельных значениях переменных задачи.

3.1 Отчет об устойчивости

В процессе поиска оптимального решения MS Excel формирует так называемый отчет об устойчивости, в котором, в частности, выдает интервал изменений коэффициентов целевой функции, внутри которого их изменение не приводит к изменению оптимального решения. Для получения этого отчета, после того как «Поиск решения» нашел оптимальное решение, нужно в окне«Результаты поиска решения», перед тем как нажать на кнопкуOk, щелкнуть мышкой по строке«Устойчивость» в списке«Тип отчета» (см. рис. 9).

Примечание. Если«Поиск решения» уже был выполнен, надо сбросить параметры задачи (в окне«Поиск решения» надо нажать кнопку«Восстановить», рис. 10), после этого в окне«Поиск решения» заново ввести параметры задачи

Рисунок 9 – Вид окна «Результаты поиска решения»

Рисунок 10 – Вид окна «Поиск решения»

Тогда после нажатия на кнопку OkMS Excel создаст дополнительный лист«Отчет об устойчивости». Распечатка такого отчета для задачи об оптимальном плане выпуска продукции мебельного цеха дана на рис. 11.

Рисунок 11 – Отчет об устойчивости MSExcelдля примера «Оптимальный план выпуска продукции мебельного цеха»

Первая таблица отчета об устойчивости «Изменяемые ячейки» содержит столбцы«Целевой коэффициент»,«Допустимое увеличение» и«Допустимое уменьшение». В первом из них даны исходные значения целевых коэффициентов: прибыль от продажи одного шкафа (200 у.е.) и одной тумбы (100 у.е.). Второй и третий столбцы содержат информацию об интервале устойчивости найденного оптимального решения. При увеличении прибыли от продажи шкафа до 350 у.е. (на 150 у.е. больше исходного значения) и при ее уменьшении до 100 у.е. (на 100 у.е. меньше исходного значения) оптимальное решение не изменяется. Аналогично второй целевой коэффициент может изменяться в пределах от 57,14 у.е. (уменьшение на 42,86 у.е. относительно исходного значения) до 200 у.е. (увеличение на 100 у.е. относительно исходного значения).

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

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

Для любой задачи линейного программирования можно сформулировать двойственную задачу. Двойственная задача является своеобразным «зеркальным отражением»исходной задачи, поскольку ее формулировка использует те же параметры, что и исходная задача, а ее решение может быть получено одновременно с решением исходной задачи. Фактически при решении исходной задачи симплекс-методом одновременно решается и двойственная задача, и наоборот. Следует также отметить, что исходная и двойственная задачи совершенно симметричны. Если двойственную задачу рассматривать как исходную, то исходная будет для нее двойственной.

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

Метод линейной оптимизации

Формулировка математической модели для принятия решения

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

Это — параметры системы

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

Это — переменные X_i

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

Это — целевая функция

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

Это — ограничения

Непосредственные задачи оптимизации

Программа-минимум:

Мгновенно вычислять целевую функцию P и другие ключевые показатели при любых интересующих значениях переменных решения (параметров управления) x₁, x₂, x₃.
(Вычисления типа “что, если”)

Программа-минимум предусматривает, в частности, дополнительную проверку соответствия имеющейся модели операций и предоставленных данных. Если данные (или модель операций) не точны, то их не удастся корректно связать формулами. Другими словами, топ-менеджеры должны уметь увязать друг с другом живыми формулами Excel хотя бы важнейшие показатели компании. Так как это хороший способ проконтролировать совместные данные разных подразделений. Иначе понять, что в компании что-то не так, очень сложно.

Программа-максимум:

Быстро находить такие значения параметров управления (переменных) x₁, x₂, x₃. при которых целевая функция P оптимальна, т.е. достигает максимума или минимума (смотря, что интереснее) и все ограничения (требования) удовлетворены.
(Задача оптимизации)

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

Кейс: На кондитерской фабрике «Алиса»

Ситуация на фабрике

Кондитерская фабрика «Алиса» должна закрыть цех шоколадных конфет на 2-3 месяца для замены оборудования. Ежедневно цех выпускает 6 видов шоколадных конфет и имеет запасы сырья примерно на 25 суток работы.

Какую продукцию выпустить, чтобы осталось минимум сырья и получить максимальную прибыль?
При выполнении плана Михалыча останется 25 тонн сырья. Это лучше, чем при текущем плане.

—> Нажмите, чтобы прочитать или скрыть службную записку Михалыча

Построение математической модели

Какие данные являютя параметрами системы?

Какие из них можно принять за переменные?

Что принять за целевую функцию?

Выражение для вычисления целевой функции

Сформулировать ограничения

Реализация построенной модели в таблице MS Excel

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

Проверка плана Михалыча

Плоды просвящения

Применение надстройки «Поиск решения» MS Excel к решению задач оптимизации

Для решения задач оптимизации используется надстройка MS Excel Поиск решения.
Введите целевую функцию, переменные и ограничения надстройки в панель Поиска решения и получите результат.

Решение, полученное после запуска надстройки

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

Новое решение получено. В результате оптимизации:
• Прибыль увеличилась на 6,4 миллиона рублей;
• Заработан неплохой бонус: почти 1,3 миллиона рублей;
• Остатки склада сырья уменьшились до 1,2 тонн.

Получите решение в целых числах. Как сильно изменилось решение?

Для получения целочисленного решения в надстройке Поиск решения добавим новое ограниечение:

Как видно, решение изменилось незначительно.

Сценарии «что — если . «

Мы все так любим батончик .

Добавьте требование обязательного выпуска не менее 5 тонн конфеты «Золотой батончик».

Решение, учитывающее требование обязательного выпуска не менее 5 тонн конфеты «Золотой батончик»

В плане, который содержит требование обязательного выпуска продукции:
• Прибыль уменьшилась почти на 100 тыс. рублей.
• Заработанный бонус уменьшился на 19 тыс. рублей.
• Можно надеяться, что имидж фабрики не пострадает, и чьи-то амбиции будут удовлетворены.

Отчет об устойчивости (Sensitivity Report)

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

На занятиях по количественным методам в менеджменте, препод все время твердил об анализе полученного оптимального решения на чувствительность к изменениям:
малые изменения величины запасов могут привести к радикальному изменению плана почти без изменения целевого показателя!
Что покажет отчет об устойчивости?

Вернитесь к первоначальному решению (без «Золотого Батончика»), получите отчет об устойчивости и проанализируйте его.

Отчет об устойчивости. Такой вид отчета можно получить только в линейной модели (симплекс-метод). Если вид отчета получается более коротким, проверьте модель в окне Выберите метод решения и установите Поиск решения лин. задач симплекс-методом.

Отчет об устойчивости состоит из двух таблиц:
• Ячейки переменных
• Ограничения

Здесь указаны все продукты

Искомые переменные (план производства)

Данные в этих столбцах показывают, имеет ли смысл менять удельную прибыль и если да, то на сколько

Your browser does not support the HTML5 canvas tag.

Расход сырья при найденных переменных

Данные в этих столбцах показывают, имеет ли смысл менять запасы сырья и если да, то на сколько

Влияние изменений коэффициентов целевой функции. Основные выводы:

Если продукт включен в оптимальный план, т.е. переменная X_j > 0

Если продукт не вошел в оптимальный план, т.е. переменная X_j = 0

При этом оптимизировали целевую функцию До:

Всегда имеется верхний и нижний предел для изменения соответствующего коэффициента целевой функции, кроме случая, когда на переменную наложено прямое ограничение:
X_j b

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

Допустимое уменьшение равно 1Е+30 (бесконечно большое число). Можно как угодно уменьшать целевые коэффициенты — продукт все равно не войдет в оптимальный план.

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

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

Допустимое увеличение может быть как угодно велико — продукт все равно не войдет в оптимальный план.

Допустимое уменьшение и Допустимое увеличение теряют смысл.

Анализ отчета об устойчивости показал:
• Для того, чтобы «Золотой батончик» попал в оптимальный план необходимо поднять отпукную цену хотя бы на 19, а лучше на 20 рублей.Тоже можно достичь, если снизить издержки его производства.
• Для попадания в план «Райского вкуса» и «Первого фанта» отпускная цена должна быть увеличена на 144 руб. и 92 руб. соответственно.
• Если отпускную цену «Сибирской белочки» снизить на 24 руб. (или увеличить издержки на ее производство на эту величину), то эта конфета исчезнет из оптимального плана.

Рыночные ограничения

Когда казалось, что все позади, пришло СМС .

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

Решение, учитывающее ограничения: «Ореховый звон» не более 50 тонн, «Золотой батончик» не менее 5 тонн.

Решение, учитывающее ограничения по двум продуктам, оказалось значительно хуже предыдущего:
• Фабрика потеряет в прибыли 1,2 млн. рублей.
• Остатки сырья на складе возрастут почти до 6 тонн.
• Бонус уменьшится почти на 250 тыс. рублей.

Отчет об устойчивости. Таблица Ограничения.

Колонка Тень Цена. Что это?

Теневые цены Y_i показывают, как меняется целевая функция P при не большом изменении количества ресурсов Δb_i: ΔP = Y_i * Δb_i.
Эти оценки верны только в пределах постоянства теневой цены (при этом численные значения переменных решения X_j, конечно изменяются). Пределы изменения Δb_i, в которых теневая цена остается одинаковой, также даны в таблице Ограничения ( столбцы Допустимое увеличение и Допустимое уменьшение количества ресурса).
Причем, если ресурс используется полностью (дефицитный ресурс), существует как верхний, так и нижний предел. Если же ресурс используется не полностью, верхний предел устойчивости равен бесконечности (Excel пишет 1Е+30, что означает 10+30, для программы – это практическая бесконечность).

Получите отчет об устойчивости при нахождении плана, учитывающего рыночные ограничения и требование о производстве «Золотого батончика».
Добавьте к таблице Ограничения столбец Теневая цена / Себестоимость ресурса. Проанализируйте полученный результат.

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

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

Почему бы не использовать новую возможность?

В соответствии с формулой
ΔP = Yi * Δbi
при Δbi = 1000 кг
(1000 кг — 50000 руб/50 руб/кг)
можно ожидать
ΔP = 518,5 * 1000 = 518,5 тыс. руб.
Или иначе
ΔP = 10,37 * 50000 = 518,5 тыс. руб.
Решение: Дозакупить 1 тонну сахара, потратив 50 тыс. руб. из бонуса!

Оптимаьный план при выполнении решения дозакупить 1 тонну сахара

Анализ таблицы Ограничения отчета об устойчивости показал:
• Существует ресурс, увеличение финансирования которого значительно увеличивает прибыль.
• Потратив на дозакупку 1 тонны сахара всего 50 тыс. рублей, можно получить увеличение прибыли на 518 тыс.рублей.
• Бонус даже за вычетом расходов на дозакупку сахара составил чуть менее 1,09 миллиона рублей.

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

Добавляем переменные и инвестируем в ресурсы

Для постановки задачи:
• введем новые переменные, которые показывают, сколько каждого ресурса нужно закупить. Таких переменных 5 по числу имеющихся ресурсов;
• добавим закупленные ресурсы к запасам;
• установим максимальную сумму закупки.

Изменение рабочей таблицы в Excel. Новые столбцы.

Изменения в настройках Поиска решения

Найденное оптимальное решение

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

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

Итоговая таблица результатов увеличения финансирования ресурсов

Какие можно сделать выводы из данных таблицы?

График зависимости реального размера бонуса от вложения в ресурсы

При ведении бизнеса часто хочется вложить в ресурсы все больше и больше. При этом:
• Бизнес растет;
• Растет производство, увеличивается прибыль.

План, кратный упаковке отгружаемой продукции

—>

Продукт	Ореховый звон	Райский вкус	Золотой батончик	Сибирская белочка	Поздний вечер	Первый фант
Нетто вес одной коробки,кг	10	8	6	10	8	6

Решение, учитывающее условие: результат должен быть кратен заданным значениям

Добавим нужные данные в таблицу Excel. Теперь переменные: число упаковок каждого продукта, и эти числа должны быть целыми.

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

Продаем неиспользуемое сырье

А что если продать остатки сырья?

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

Как учесть стоимость остатков сырья?

Результат оптимизации получается точно такой же, как при максимизации прибыли. Ожидаемо, если задуматься, правда?

Историческая справка

Канторович Леонид Витальевич (6 (19) января 1912, Санкт-Петербург — 7 апреля 1986, Москва)
В 1926 году в возрасте четырнадцати лет поступил в Ленинградский университет. Окончил математический факультет (1930), учился в аспирантуре университета, c 1932 года преподаватель, в 1934 стал профессором, в 1935 году ему присвоена ученая степень доктора физико-математических наук без защиты диссертации.
В 1938 году консультировал фанерный трест по проблеме эффективного использования лущильных станков. Канторович понял, что дело сводится к задаче максимизации линейной формы многих переменных при наличии большого числа ограничений в форме линейных равенств и неравенств. Он модифицировал метод разрешающих множителей Лагранжа для ее решения и понял, что к такого рода задачам сводится колоссальное количество проблем экономики.
В 1939 году опубликовал работу «Математические методы организации и планирования производства», в которой описал задачи экономики, поддающиеся открытому им математическому методу и, тем самым, заложил основы линейного программирования.
В 1949 году стал лауреатом Сталинской премии «за работы по функциональному анализу». 28 марта 1958 года избран членом-корреспондентом АН СССР (экономика и статистика). С 1958 года возглавляет кафедру вычислительной математики. Одновременно возглавлял отдел приближенных вычислений Математического института им. Стеклова Ленинградского отделения АН СССР. В середине 1948 года по распоряжению И. В. Сталина, расчетная группа Канторовича была подключена к разработке ядерного оружия.
С 1960 года жил в Новосибирске, где создал и возглавил Математико-экономическое отделение Института математики СО АН СССР и кафедру вычислительной математики Новосибирского университета. 26 июня 1964 года избран академиком АН СССР (математика). За разработку метода линейного программирования и экономических моделей удостоен в 1965 году вместе с академиком В. С. Немчиновым и профессором В. В. Новожиловым Ленинской премии.
С 1971 года работал в Москве, в Институте управления народным хозяйством Государственного комитета Совета Министров СССР по науке и технике.
1975 год — Нобелевская премия по экономике (совместно с Т. Купмансом «за вклад в теорию оптимального распределения ресурсов»). С 1976 работал во ВНИИ системных исследований Госплана СССР и АН СССР.

Джордж Бернард Данциг (англ. George Bernard Dantzig; 8 ноября 1914 — 13 мая 2005) — математик, разработавший симплексный алгоритм (симплекс-метод). Считается «отцом линейного программирования» (наряду с советским математиком Л. В. Канторовичем).
Ему были присуждены: Национальная Медаль Науки (National Medal of Science) в 1975, Приз Джона фон Неймана (John von Neumann Theory Prize) в 1974. Он был членом Национальной Академии Наук (National Academy of Sciences), Национальной технической Академии (National Academy of Engineering), и американской Академии Искусств и Наук (American Academy of Arts and Sciences).
Он получил степень бакалавра по математике и физике в Университете Мэриленд (University of Maryland) в 1936, степень магистра математики в Университете Мичиган (University of Michigan) и доктора философии в Беркли (UC Berkeley) в 1946.
Отец Данцига, Тобиас Данциг, был российским (латвийским) математиком, который учился у Анри Пуанкаре (Henri Poincaré) в Париже, затем эмигрировал в Соединенные Штаты.

Тьяллинг Купманс, Джордж Данциг, Леонид Канторович, 1975 год

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

В процессе поиска оптимального решения MS Excel формирует отчет об устойчивости, в котором указывается интервал изменений коэффициентов целевой функции. Для вывода такого отчета в окне Результаты поиска решения (Рис.7) следует указать типы отчетов «Устойчивость» и «Пределы». В результате MS Excel создаст дополнительный лист «Отчет об устойчивости» и «Отчет по пределам», где указаны границы интервалов устойчивости для параметров модели (Рис.10)

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

1. Сформулируйте задачу.
2. В меню Сервис выберите команду Поиск решения.
3. Нажмите кнопку Дополнительно.
4. Нажмите кнопку Сохранить модель.
5. Введите ссылку на верхнюю ячейку столбца, в котором следует разместить модель оптимизации.

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

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

1. В меню Сервис выберите команду Поиск решения.
2. Нажмите кнопку Дополнительно.
3. Нажмите кнопку Загрузить модель.
4. Введите ссылку на весь диапазон ячеек с областью модели.

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями: