3.3. Сценарный подход к решению задач моделирования

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

Вернемся к рассмотренному нами выше примеру с определением будущей суммы вклада.

Изменяя исходные параметры (размер вклада, срок и процентную ставку), определяющие сумму выплат для каждого сценария мы можем получить свободный отчет близкий к таблицам анализа чувствительности. Каждый набор значений исходных параметров (табл. 3.1), рассматривается как возможный сценарий, проведенный в ЭТ Excel (табл. 3.6).

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

Таблица 3.6 - Структура сценария

			Альтернативное решение
Текущее значение   Текущие значения:			Сценарий 1	Сценарий 2
Изменяемые:
размер	$C$1	300 000	350 000	400 000
срок	$C$2	5	5	7
ставка	$C$3	0,05	0,11	0,12
Результат:
сумма	$C$5	382884	589770	884273

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

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

Задание1. Проведите анализ чувствительности с двумя управляемыми переменными для исчисления налоговой ставки.

Задание2. Создайте сценарий выбора банка для кредитования.

3.4. Метод оптимизации в решении социально-экономических задач

3.4.1. Лабораторная модель №3. Определение производственной программы условного предприятия

Цели работы:

1) иметь представление о решении оптимизационных задач в электронной таблице Excel;

2) различать линейные, линейные целочисленные и нелинейные задачи оптимизации;

3) уметь проводить анализ полученного решения.

Основные понятия

Оптимизационными называются задачи, в которых требуется найти

экстремальное значение некоторой функции F(X_j) при заданных

о граничениях:

Элементы математической модели:

– Исходные данные – это целевая функция и ограничения (левая и правая части).

Детерминированными данными называются исходные данные, если при составлении модели известны их точные значения.

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

– Оптимизируемые (искомые) переменные.

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

Дискретными называются переменные, принимающие только заданные значения.

Целочисленные переменные – это дискретные переменные, принимающие целые значения.

– Зависимости – отношения между данными и переменными.

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

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

Задачи оптимизации, решение которых возможно средствами Excel представлены таблицей 3.7.

Основные этапы построения математической модели:

1) выбор задачи включает требования, которым должна удовлетворять выбранная задача:

– существование как минимум двух вариантов решения задачи;

– четкое понимание смысла наилучшего решения;

2) содержательная постановка задачи – это формулировка в такой форме, на основании которой ясны элементы математической модели:

– исходные данные;

– искомые переменные;

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

– зависимости между переменными;

– критерии, по которым следует находить оптимальное решение;

3) составление математической модели – запись целевой функции и ограничений в виде набора формул, уравнений и их систем;

4) сбор исходных данных;

5) выбор метода и решение задачи;

6) анализ решения;

7) принятие решения;

8) графическое представление результата.

Таблица 3.7 - Классификация задач оптимизации

Класс задач	Зависимости	Искомые переменные	Исходные данные
Линейное программирование	Линейные	Непрерывные	Детерминированные
Целочисленное программирование	Линейные	Целочисленные	Детерминированные
Нелинейное программирование	Нелинейные	Целочисленные, непрерывные	Детерминированные
Стохастическое программирование	Линейные	Непрерывные	Случайные

Содержание работы. Рассмотрим решение оптимизационной задачи на примере определения производственной программы условного предприятия.

Предприятие может выпускать четыре вида продукции П1, П2, П3, П4. Для их изготовления используются три вида ресурсов Р1, Р2, Р3, объемы которых ограничены. Известна прибыль, получаемая от реализации единицы каждого вида продукции (единичная прибыль). Заданы также граничные значения выпуска каждого вида продукции. Также известна потребность в ресурсах для выпуска единицы каждого вида продукции.

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

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

Ц елевая функция:

О граничения на ресурсы:

О граничения на объемы производства по видам продукции:

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

Таблица 3.8

	А	В	C	D	E	F	G
1	Задача определения производственной программы условного предприятия
2
3	Виды продукции	П1	П2	П3	П4
4	Объем производства					Общая прибыль
5	Единичная прибыль	60	70	120	130		0
6
7	Ограничения по ресурсам					Требуется	Имеется
8	Ресурс 1	1	1	1	1		16
9	Ресурс 2	4	6	10	13		110
10	Ресурс 3	6	5	4	3		110
11	Ограничения по объемам
12	Нижняя граница	1	2	2	1
13	Верхняя граница	4			1

Технология решения данного типа задач в Excel:

1) ввод исходных данных модели (табл.3.8);

2) в ячейку G5 вводим формулу, соответствующую целевой функции модели (табл.3.9);

3) в ячейки F8:F10 вводим формулы, соответствующие левой части ограничений на ресурсы;

4) вызов команды Поиск решения из меню Сервис;

5) в открывшемся диалоговом окне указываем ячейку с целевой функцией (G5), направление оптимизации (максимизация), изменяемые ячейки (B4:E4), вводим ограничения, накладываемые на ресурсы и объем производства продукции.

Таблица 3.9

	А	В	C	D	E	F	G
1	Задача определения производственной программы условного предприятия
2
3	Виды продукции	П1	П2	П3	П4
4	Объем производства					Общая прибыль
5	Единичная прибыль	60	70	120	130		=B5*B4+C5*C4+D5*D4+E5*E4
6
7	Ограничения по ресурсам					Требуется	Имеется
8	Ресурс 1	1	1	1	1	=B8*B4+C8*C4+D8*D4+E8*E4	16
9	Ресурс 2	4	6	10	13	=B9*B4+C9*C4+D9*D4+E9*E4	110
10	Ресурс 3	6	5	4	3	=B10*B4+C10*C4+D10*D4+E10*E4	110
11	Ограничения по объемам производства
12	Нижняя граница	1	2	2	1
13	Верхняя граница	4			1

Представленная на рисунке 3.1 форма отражает эти действия.

В результате получим решение задачи, представленное таблицей 3.10.

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

Рисунок 3.1 - Диалоговое окно поиска решения

Таблица 3.10

		А	В	C	D	E	F	G
1		Задача определения производственной программы условного предприятия
2
3		Виды продукции	П1	П2	П3	П4
4		Объем производства	4	2	5,9	1	Общая прибыль
5		Единичная прибыль	60	70	120	130		1338
6
7		Ограничения по ресурсам					Требуется	Имеется
8		Ресурс 1	1	1	1	1	13,9	16
9		Ресурс 2	4	6	10	13	110	110
10		Ресурс 3	6	5	4	3	64,6	110
11		Ограничения по объемам производства
12		Нижняя граница	1	2	2	1
13		Верхняя граница	4			1

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

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

В первой таблице приведено исходное и оптимальное значение целевой ячейки.

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

Таблица 3.11

Целевая ячейка (Максимум)
	Ячейка	Имя	Исходно	Результат
	$G$5	Единичная прибыль	1338	1338
Изменяемые ячейки
	Ячейка	Имя	Исходно	Результат
	$B$4	Объем производства П1	4	4
	$C$4	Объем производства П2	2	2
	$D$4	Объем производства П3	6,9	6,9
	$E$4	Объем производства П4	1	1
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$F$8	Ресурс 1 Требуется	13,9	$F$8<=$G$8	не связан.	2,1
	$F$9	Ресурс 2 Требуется	110	$F$9<=$G$9	связанное	0
	$F$10	Ресурс 3 Требуется	64,6	$F$10<=$G$10	не связан.	45,4
	$D$4	Объем производства П3	6,9	$D$4>=$D$12	не связан.	4,9
	$B$4	Объем производства П1	4	$B$4>=$B$12	не связан.	3
	$C$4	Объем производства П2	2	$C$4>=$C$12	связанное	0
	$E$4	Объем производства П4	1	$E$4=$E$12	связанное	0
	$B$4	Объем производства П1	4	$B$4<=$B$13	связанное	0

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

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

Анализ устойчивости и пределов представлен таблицей 3.12 и таблицей 3.13 соответственно.

Таблица 3.12

	Изменяемые ячейки	Результ.	Нормир.	Целевой	Допустимое	Допустимое
Ячейка	Имя	значение	стоимость	Коэффициент	Увеличение	Уменьшение
$B$4	Объем производства П1	4	12	60	1E+30	12
$C$4	Объем производства П2	2	-1,999999999	70	1,999999999	1E+30
$D$4	Объем производства П3	6,9	0	120	30	2,333333332
$E$4	Объем производства П4	1	-26	130	26	1E+30
	Ограничения	Результ.	Теневая	Ограничение	Допустимое	Допустимое
Ячейка	Имя	значение	Цена	Правая часть	Увеличение	Уменьшение
$F$8	Ресурс 1 Требуется	13,9	0	16	1E+30	2,1
$F$9	Ресурс 2 Требуется	110	12	110	31	39
$F$10	Ресурс 3 Требуется	64,6	0	110	1E+30	44,4

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

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

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

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

Значения объемов производства в столбце «Нижний предел» (табл.3.13) показывают, какие минимальные значения эти переменные могут принимать, в то время как остальные независимые переменные остаются без изменения и выполняются все ограничения модели.

Таблица 3.13

Microsoft Excel 11.0 Отчет по пределам
Рабочий лист: [лаб2.xls]Отчет по пределам
Отчет создан: 05.02.2007 1:29:38
		Целевое
	Ячейка	Имя	Значение
	$G$5	Единичная прибыль	1338
		Изменяемое			Нижний	Целевой
	Ячейка	Имя	Значение		предел	результат
	$B$4	Объем производства П1	4		1	1338
	$C$4	Объем производства П2	2		2	1338
	$D$4	Объем производства П3	6,9		2	1338
	$E$4	Объем производства П4	1		1	1338

Аналогично в столбце «Верхний предел» содержатся максимально возможные значения переменных при сохранении неизменности всех остальных переменных и соблюдения ограничений модели.

Анализ модели показывает, что полученное решение не является оптимальным, т.к. ресурсные коэффициенты в отчете по результатам в двух случаях (Р1 и Р3) имеют статус «несвязан». Одним из возможных вариантов решения проблемы является запуск в производство пятого вида продукции.

Задание. Постройте предлагаемую модель определения производственной программы предприятия для пятого вида продукции П5 с единичной прибылью 127 условных единиц и требуемыми ресурсными коэффициентами: Р1 – 1, Р2 – 8, Р3 – 13 (Р1 – 1, Р2 – 7, Р3 – 15) для имеющихся Р1 –16, Р2 – 130, Р3 – 110 условных единиц.

САМОСТОЯТЕЛЬНАЯ РАБОТА. Постройте и решите средствами ЭT Excel модель определения производственной программы конкретного предприятия. Проверьте адекватность модели реальному объекту.