УЧЕБНИКИ 3 Экономика / Управленческие решения / Степанов А.Г. Разработка управленческого решения средствами пакета Excel. 2001

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

НОРМОБР().

		Таблица 4.7
	Количество собранных компьютеров по месяцам
	Месяц	Количество собранных
		компьютеров
	январь	496
	февраль	500
	март	510
	апрель	450
	май	540
	июнь	530
	июль	500
	август	500
	Среднее значение	503,25
	Максимальное значение	540
	Минимальное значение	450
	Стандартное отклонение по выборке	26,8101793
	Стандартное отклонение по генеральной совокупности	25,07862636

Таблица 4.8

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

	Количество		Возможное	Планируемое	Стоимость	Объем
		Прибыль,	количество	количество	комплектующих	на скла-
	собранных
		р.	собранных	собранных	изделий,	де,
	компьютеров
			компьютеров	компьютеров	у.е.	м3
	Минимальное	1 360 354,40	450	450	265 180,00	229,32
	Среднее	2 231 804,20	503,3	503	290 000,00	254,06
	Максимальное	2 233 770,83	540	513	290 000,00	255,86

151

Таблица 4.9

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

									Решение
	Коли-
	чество	Прибыль,	C300/1-PC	C300/2-PC	C300/3-PC	C366A/1-PC	C366A/2-PC	C366A/3-PC	C400A/1-PC	C400A/2-PC	C400A/3-PC	II400/1-P-PC	II400/2-P-PC	II400/3-P-PC	II450/1-P-PC	II450/1-P-PC	II450/1-P-PC
	собран-
		р.
	ных ком-
	пьютеров
	Мини-	1 360 354,40	20	20	20	20	20	50	20	20	50	20	20	50	20	50	50
	мальное
	Среднее	2 231 804,20	20	20	20	20	50	20	20	43	20	21	50	50	50	50	50
	Макси-	2 233 770,83	20	20	20	20	50	20	20	50	20	50	50	24	50	50	50
	мальное

В табл. 4.10 и 4.11 приведены результаты расчетов, выполненных в процессе решения задачи, а на рис. 4.20 представлен вид рассчитанной алгоритмическим методом функции распределения. В колонке “Значение вероятности” записаны заданные в виде аргументов исходные значения вероятности. Колонка “Прибыль” содержит результирующее значение целевой ячейки, получающееся в результате отыскания экстремального значения при заданном значении вероятности. Колонка “Возможное количество собранных компьютеров” содержит оценку максимально достижимой производительности, определенную с помощью функции НОРМОБР(). В колонке “Количество собранных компьютеров” приведены значения суммы числа планируемых к выпуску компьютеров, полученное в результате работы режима Поиск решения (конкретные решения приведены в табл. 4.11). В колонках “Стоимость комплектующих” и “Объем на складе” также приведены данные, полученные в результате проведения расчетов по поиску экстремума при заданном значении вероятности.

Найдем теперь решение задачи в M-постановке. Как следует из подразд. 3.7, оптимальное решение в М-постановке обеспечивает максимум математического ожидания целевой функции. В случае нормального закона распределения математическое ожидание определяется при значении вероятности равном 0,5. Тогда оптимальное решение, определенное из соответствующей строки табл. 4.11, равно X = {20; 20; 20; 20; 43; 20; 20; 50; 20; 20; 50; 50; 50; 50; 50}.

152

					Таблица 4.10
		Результаты расчетов, выполненных
	в процессе решения задачи алгоритмическим методом
	Количество		Возможное	Планируемое	Стоимость	Объем
		Прибыль,	количество	количество	комплектую-
	собранных					на складе,
		р.	собранных	собранных	щих изделий,
	компьютеров		компьютеров	компьютеров	у.е.	м3
	0,1	1 799 884,40	471	471	277 906,00	240,6172
	0,2	2 026 266,40	482	482	284 484,00	246,5334
	0,3	2 178 314,40	490	490	288 980,00	250,8358
	0,4	2 223 840,10	497	496	290 000,00	250,7897
	0,5	2 231 581,08	503	503	290 000,00	254,0168
	0,6	2 233 123,04	510	510	290 000,00	253,9703
	0,7	2 233 770,83	516	513	290 000,00	255,8613
	0,8	2 233 770,83	524	513	290 000,00	255,8613
	0,9	2 233 770,83	535	513	290 000,00	255,8613
	0,99	2 233 770,83	562	513	290 000,00	255,8613

Таблица 4.11

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

Значения вероятности								Решение
	Прибыль,	C300/1-PC	C300/2-PC	C300/3-PC	C366A/1-PC	C366A/2-PC	C366A/3-PC	C400A/1-PC	C400A/3-PC	II400/1-P-PC	II400/2-P-PC	II400/3-P-PC	II450/1-P-PC	II450/1-P-PC	II450/1-P-PC	C400A/2-PC
	р.
0,1	1 799 884,40	20	20	20	20	20	50	20	20	50	20	41	50	20	50	50
0,2	2 026 266,40	20	20	22	20	20	50	20	20	50	20	50	50	20	50	50
0,3	2 178 314,40	20	20	30	20	20	50	20	20	50	20	50	50	20	50	50
0,4	2 223 840,10	20	20	20	20	20	22	20	35	50	20	50	50	50	50	50
0,5	2 231 581,08	20	20	20	20	43	20	20	50	20	20	50	50	50	50	50
0,6	2 233 123,04	20	20	20	20	50	20	20	35	20	50	50	35	50	50	50
0,7	2 233 770,83	20	20	20	20	50	20	20	50	20	50	50	24	50	50	50
0,8	2 233 770,83	20	20	20	20	50	20	20	50	20	50	50	24	50	50	50
0,9	2 233 770,83	20	20	20	20	50	20	20	50	20	50	50	24	50	50	50
0,99	2 233 770,83	20	20	20	20	50	20	20	50	20	50	50	24	50	50	50

153

Решение задачи в P-постановке при значениях вероятности P ≥ 0,7 оказывается одинаковым и равным X = {20; 20; 20; 20; 50; 20; 20; 50; 20; 50; 50; 24; 50; 50; 50}. Это объясняется особенностью рассматриваемой задачи, нашедшей свое отражение в табл. 4.10 и 4.11. Так при P ≥ 0,7 результат и расчетные параметры задачи не изменяются. Это обстоятельство объясняется тем, что на решение начинает оказывать существенное влияние ограничение по стоимости комплектующих изделий (величина банковского кредита). На основании анализа содержимого таблиц можно сделать вывод, что выбранная модель функционирования предприятия и кредитная политика, в частности, оправданы.

Вероятность появления

1,2

1

0,8

0,6

0,4

0,2

0

1 750	1 850	1 950	2 050	2 150	2 250	2 350
000,00	000,00	000,00	000,00	000,00	000,00	000,00

Значения целевой функции

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

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

154

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

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

4.4.Пример решения однокритериальной статической задачи

вусловиях неопределенности

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

Таблица 4.12

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

	Стратегии природы	N1	N2	N3	N4	N5	N6	N7	N8	N9	N10
	Курс доллара	26	27	28	29	30	31	32	33	34	35
	к рублю
	Вероятность	0,2	0,225	0,23	0,17	0,1	0,05	0,015	0,005	0,003	0,002
	появления

Зададим в табл. 4.12 набор смешанных стратегий природы N1,N2,…,N10 в виде возможных значений курса условной единицы к рублю и поставим им в соответствие определенные экспертным путем вероятности их появления g1,g2,…,g10. Предположим, что в распоряжении менеджера также имеется десять возможных стратегий M1,M2,…,M10,

155

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

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

Решения задачи, соответствующие стратегиям менеджера, приведены в табл. 4.14. При расчетах предполагалось, что кредит был получен по курсу 26 рублей за условную единицу, а соответствующие стратегии N1,N2,…,N10 и M1,M2,…,M10 предполагали одинаковые значения курса из табл. 4.12. Очевидно, что рост курса ведет к уменьшению прибыли фирмы, выраженной в условных единицах. При расчете элементов выше главной диагонали матрицы было сделано предположение о том, что программа выпуска может быть пересчитана в начале каждого месяца исходя из реального курса на момент приобретения комплектующих. Тогда элементы матрицы выше диагонали повторяют значения прибыли, полученные в результате использования режима Поиск решения в случае совпадения стратегий природы и менеджера.

Таблица 4.13

Платежная матрица задачи в условиях неопределенности

Sn	N	N	N!	N"	N#	N$	N%	N&	N'	N
M	"%%	& %""	%'%'#	%% "%	%""&!	% %'	$'& #	$%% #	$#%	$!& !
M	% %&$	& %""	%'%'#	%% "%	%""&!	% %'	$'& #	$%% #	$#%	$!& !
M!	#&$$!	$'$	%'%'#	%% "%	%""&!	% %'	$'& #	$%% #	$#%	$!& !
M"	"$""	#%!''	$%#%"	%% "%	%""&!	% %'	$'& #	$%% #	$#%	$!& !
M#	!# !$	"#''!	#$ $&	$#$"	%""&!	% %'	$'& #	$%% #	$#%	$!& !
M$	"!$	!#! '	"#"'!	#"'$%	$!& &	% %'	$'& #	$%% #	$#%	$!& !
M%	"!#	#!	!#"	""'#&	#!%''	$ %	$'& #	$%% #	$#%	$!& !
M&	"'"'	#' $	$ &	!###"	""!'$	# $$%	$ "	$%% #	$#%	$!& !
M'	`!'	% ##	% !	$% !	!##"#	"!& $	# #%	#&"	$#%	$!& !
M	` $!	` ! $	&&$'	&!"	% &"	!#"##	"! '	# "'!	#%!"'	$!& !

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

156

тегию (табл. 4.14) при различных вариантах стратегий природы, определяемых табл. 4.12.

Для расчета платежной матрицы в основной рабочей таблице на рис. 4.17 были сделаны следующие изменения. В ячейку A14 вписан текст “Курс 1 у.е. в руб. для получения кредита”, а в ячейке B14 задано собственно значение курса, по которому осуществлялось кредитование. Вставлена строка 20. В этой строке рассчитываются затраты (запрограммирована формула) расчета стоимости комплектующих в условных единицах, причем левая часть неравенства (ячейка B20) использует для вычислений курс из ячейки B8, а правая (ячейка D20) курс из ячейки B14. Старое выражение ограничения в рублях заменено на новое в условных единицах. Итоговый вид рабочей таблицы задачи представлен на рис. 4.21.

Таблица 4.14

Решения, соответствующие стратегиям менеджера

PC-C300/1

PC-C300/2

PC-C300/3

PC-C366A/1

PC-C366A/2

PC-C366A/3

PC-C400A/1

PC-C400A/3

PC-P-II400/1

PC-P-II400/2

PC-P-II400/3

PC-P-II450/1

PC-P-II450/1

PC-P-II450/1

PC-C400A/2

M

&

&

#

#

#

#

#

#

M

#

!"

#

#

#

#

#

M!

#

#

#

#

#

"%

M"

#

#

#

#

#

!"

M#

#

#

#

#

#

M$

!&

#

#

#

#

M%

!

#

#

#

#

M&

!'

#

#

#

M'

#

#

#

#

M

#

"!

#

Определим величину среднего выигрыша с учетом заданных вероятностей появления стратегий природы в соответствии с (3.19). Расчеты в Excel дают максимальное значение среднего выигрыша 79926,56 при использовании менеджером стратегии M1 с оптимальным решением из табл. 4.14. Отметим, что взвешенный средний риск при выборе стратегии M1 также имеет минимальное значение равное нулю.

157

158

Рис. 4.21. Основная рабочая таблица задачи в условиях неопределенности

Предположим теперь, что у менеджера нет оснований предполагать конкретные значения вероятностей появления определенных величин курса условной единицы к рублю. В этом случае для решения задачи целесообразно воспользоваться критериями Вальда, Сэвиджа или Гурвица. Использование критерия максимума минимального выигрыша Вальда применительно к платежной матрице в этом случае дает две равноценные стратегии M1 и M2 с одинаковым значением показателя 63813. Критерий минимаксного риска Сэвиджа определяет оптимальное решение в виде стратегии M1 при r = 0 . Наконец, расчеты по критерию Гурвица также указывают на оптимальную стратегию M1 при любом значении параметра α . Таким образом, результатом решения рассматриваемой задачи в условиях неопределенности является оптимальная стратегия менеджера M1.

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

4.5. Пример решения многокритериальной статической задачи

Рассматриваемая в этом разделе задача оптимального оперативного планирования деятельности компьютерной фирмы может быть сформулирована и как многокритериальная. Результаты, полученные в подразд. 4.2–4.4, показывают существенное влияние заемных средств на экономические показатели деятельности фирмы. Логично предположить, что на основании проведенных исследований руководством фирмы менеджеру будет предложено постараться минимизировать величину банковского кредита при условии сохранения требования возможной максимизации извлекаемой прибыли. Указанные требования являются противоречивыми, поскольку для увеличения прибыли требуется увеличение объема производства и, как следствие, объема кредитования. Обеспечение выполнения этих двух противоречивых требований переводит рассматриваемую задачу в категорию многокритериальных. Отметим, что многокритериальная задача также может решаться в условиях риска

159

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

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

Дополнительно в ячейке B14 рассчитаем величину прибыли в условных единицах по действующему курсу. Результатом решения будет минимально возможная программа выпуска (все типы компьютеров по 20 штук) при стоимости комплектующих 165580 у.е. Отметим, что при реализации такой программы выпуска фирма несет убытки в размере 78 449 у.е.

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

			k
			∑
E = max				ei
			i=1

как сумму значений локальных критериев. Тогда, учитывая то обстоятельство, что по критерию “прибыль” e1 должен быть найден максимум, а по критерию “расходы на приобретение комплектующих изделий” e2 должен быть найден минимум, общее выражение для критериальной функции в ячейке B16 будет иметь вид

E = max{e1 − e2}.

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

160