Статистические методы измерения экономических процессов (90

числа, большие или равные 0 и меньшие 0,10, соответствуют спросу в 10 000 шт.; большие или равные 0,10 и меньшие 0,45 соответствуют спросу в 20 000 шт.; случайные числа, большие или равные 0,45 и меньшие 0,75, соответствуют спросу в 40 000 шт.; большие 0,75 соответствуют спросу в 60 000 шт. Данные представлены в таблице 5. Для моделирования спроса используется функция

ВПР (искомое значение; таблица; номер столбца; [интервальный просмотр])

Формула гарантирует, что любое случайное число меньше 0,10 сгенерирует спрос, равный 10 000 и т.д., согласно значениям таблицы 3. Моделирование спроса с учетом фактора неопределенности приводится в таблице 4.

Таблица 5

Вид диапазона поиск (lookup)

Cтроки/	F	G
Столбцы	Границы	Спрос
2	0	10 000
3	0,1	20 000
4	0,45	40 000
5	0,75	60 000

Затем c использованием функции =СЧЁТЕСЛИ(data;E8)/400 определяем долю каждого значения спроса во всех 400 итерациях.

Таблица 6

Определение спроса с учетом фактора неопределенности

Строки/	E	F	F
Столбцы		Доля из общего
	Спрос	числа раз	Формула определения доли
8	10 000	0,12	=СЧЁТЕСЛИ(data;E8)/400
9	20 000	0,345	=СЧЁТЕСЛИ(data;E9)/400
10	40 000	0,275	=СЧЁТЕСЛИ(data;E10)/400
11	60 000	0,26	=СЧЁТЕСЛИ(data;E11)/400

11

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

Пример 2. Принятие бизнес-решений на основе моделирования методом Монте-Карло (файл пример2.xls).

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

Таблица 7

Ряд распределения случайной величины

Спрос	Вероятность
10 000	0,10
20 000	0,35
40 000	0,30
60 000	0,25

Требуется определить, сколько открыток необходимо напечатать, если поздравительная открытка продается по цене $4,00, переменные издержки на производство одной открытки составляют $1,50 и нереализованные открытки должны быть распроданы по цене $0,20 за штуку. Экономическая постановка задачи конкретизируется следующим образом. Моделируется каждый возможный объем производства множество раз (например, проводится 1 000 итераций). Затем определяется, какой объем обеспечивает максимальный средний доход для этих 1 000 итераций.

Для практической реализации в Excel ячейкам С1:С11 назначены имена диапазонов из ячеек В1:В11. В ячейках С4:С6 указаны параметры цены реализации и затрат.

В ячейку С1 введен пробный объем производства, в данном случае 40 000. Генерируется случайное число в ячейке С2 с помощью формулы =СЛЧИС(). Спрос на открытку в ячейке С3 определяется по формуле:

= ВПР(случайное_число, lookup;2),

где случайное_число – это имя ячейки С2, а не функция СЛЧИС().

12

Число проданных открыток меньше нашего объема производства и спроса. В ячейке С8 подсчитывается доход по формуле:

=МИН(объем_производства;спрос)*цена_открытки

			Таблица 8
	Исходные данные
Строки/	В	С	C
Столбцы	Показатели	Значения	Формулы расчетов
1	объем производст-
	ва	40 000
2	случайное число	0,4254213	=СЛЧИС()
3			=ВПР(случайное_число;loo
	спрос	20 000	kup;2)
4	себестоимость
	производства	$1,50
5	цена открытки	$4,00
6	цена при распро-
	даже	$0,20
7
8			=МИН(объем_производств
			а;спрос)*
	доход	$80 000,00	цена_открытки
9	общие переменные		=объем_производства*себе
	издержки	$60 000,00	стоимость_производства
10			=цена_при_распродаже*ЕС
			ЛИ(объем_производства>с
	общие издержки		прос;объем_производства-
	на распродажу	$4 000,00	спрос;0)
11			=доход-общие_перемен-
			ные_ издержки-
			общие_издерж-
	прибыль	$16 000,00	ки_на_распродажу

В ячейке С9 вычисляются общие затраты на производство по формуле:

=объем_производства*себестоимость_производства

Если мы производим открыток больше, чем требуется, то число нереализованных открыток равно объему производства с корректировкой на величину спроса. В противном случае нереализованных открыток не будет. Вычисляем затраты на переработку в ячейке С10 по формуле:

13

=цена_при_распродаже*ЕСЛИ(объем_производства>спрос; объем_производства-спрос;0)

В ячейке С11 вычисляется прибыль по формуле:

=доход-общие_переменные_издержки- общие_издержки_на_распродажу

Диапазону G3:H6 (таблица 9) присвоено имя поиск (lookup).

Таблица 9

Вид диапазона поиск (lookup)

Cтроки /	G	H
Столбцы	Границы	Спрос
3	0	10 000
4	0,1	20 000
5	0,45	40 000
6	0,75	60 000

Требуется выбрать эффективный способ имитации многократного, скажем, 1 000 раз нажатия клавиши F9 (см. Пример 1) и подсчета дохода для каждого объема производства. В этом случае на практике часто используется таблица подстановки с двумя переменными, фрагмент которой представлен в таблицах 10 – 11. Excel моделирует 1 000 итераций спроса для каждого объема производства. Чтобы создать таблицу подстановки с двумя параметрами, указываем в качестве ячейки для подстановки по строкам любую пустую ячейку, в данном случае I14, а в качестве ячейки для подстановки по столбцам – объем производства (ячейка С1 в таблице 8). Техника работы таблицы подстановки заключается в следующем. Для каждой из ячеек диапазона (С16:С1015) программа подставляет значение 20 000, равное спросу (ячейка С3 в таблице 8), в ячейку С1. В С16 в пустую ячейку помещается значение, подставляемое по строкам (1), и случайное число в ячейке С2 генерируется заново. После этого в ячейку С16 записывается соответствующее значение прибыли. Затем в пустую ячейку снова помещается значение, подставляемое по строкам (2), и случайное число в ячейке С2 генерируется заново. Соответствующее значение прибыли записывается в ячейку С17.

14

Таблица 10

Моделирование объема производства открыток (фрагмент)

Строки/	F	B	C	D	E
Столбцы
16	1	25 000	50 000	-26 000	150 000
17	2	25 000	8 000	100 000	150 000
18	3	25 000	50 000	100 000	66 000
19	4	25 000	50 000	100 000	-60 000
20	5	25 000	50 000	16 000	66 000
21	6	25 000	8 000	100 000	150 000
22	7	25 000	50 000	16 000	150 000

Таблица 11

Формулы для расчета объема производства открыток

Строки/ Столбцы	F	B	C	D	E
16	1	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
17	2	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
18	3	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
19	4	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
20	5	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
21	6	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
22	7	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
23	8	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
24	9	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)
25	10	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА	=ТАБЛИЦА
		(C1;I14)	(C1;I14)	(C1;I14)	(C1;I14)

15

Для принятия обоснованных управленческих решений требуется определить такие статистические характеристики, как среднее значение и стандартное отклонение. На основе формулы =СРЗНАЧ() вычислим среднюю прибыль для каждого объема производства. С помощью функции =СТАНДОТКЛОН() рассчитаем стандартное отклонение прибыли для каждого объема производства. Результаты вычислений представлены в таблице 12.

Таблица 12

Моделирование прибыли от производства открыток

A	B	С	D	E
среднее	25 000	45 884	57 538	46 806
стандартное
отклонение	0	12 493,46352	48 513,42	75 988,95
	=СРЗНАЧ	=СРЗНАЧ	=СРЗНАЧ	=СРЗНАЧ
формулы	(B16:B1015)	(C16:C1015)	(D16:D1015)	(E16:E1015)
	=СТАНД	=СТАНД	=СТАНД	=СТАНД
для вычис-	ОТКЛОН	ОТКЛОН	ОТКЛОН	ОТКЛОН
лений	(B16:B1015)	(C16:C1015)	(D16:D1015)	(E16:E1015)

Тема 3. Влияние риска на принятие

бизнес-решений

Пример 3. Определение влияния риска на принятие бизнесрешений на основе моделирования методом Монте-Карло (файл пример3.xls).

На основе исходных данных Примера 2 определить, каково влияние риска на наше решение об объемах производства открыток.

При рассмотрении влияния риска на бизнес-решения требуется учесть, что если будет напечатано 20 000 открыток вместо 40 000, то ожидаемая прибыль упадет примерно на 22%, однако при этом риск, измеряемый стандартным отклонением прибыли, упадет практически на 73%. Следовательно, если риск для нас крайне неприемлем, печать 20 000 открыток может оказаться более правильным решением. При печати 10 000 открыток стандартное отклонение всегда равно 0, поскольку мы в любом случае продадим их, и ничего не останется.

16

Таблица 13

Исследование прибыли от производства открыток с помощью статистических показателей

Строки	A
/столбцы		B	C	D	E
13	среднее	25 000	45 884	57 538	46 806
14	стандартное
	отклонение	0	12 493,46352	48 513,42	75 988,95
15	16 000	10 000	20 000	40 000	60 000

Для решения задач подобного рода используется построение точечной и интервальной оценок. Оценки истинных, но неизвестных, значений параметров – это числа, зависящие от количества и состава наблюдений, т.е. от выборки. При различных выборках мы получили бы различные оценки. Если продолжать брать все больше выборок и получать дополнительные оценки, то оценки каждого параметра будут соответствовать некоторому распределению вероятностей, которое может быть суммировано как среднее, если сравниваемые параметры распределены нормально. Нормальное распределение имеет следующее свойство: область, находящаяся в пределах 1,96 стандартного отклонения от его среднего значения составляет 95% всей области. Учитывая это, можно указать такой интервал вокруг оценки параметра, что с вероятностью 95% истинное значение параметра лежит внутри этого интервала. Данный интервал, называемый 95-процентным доверительным интерва-

лом, определяется так:

b ± 1,96 стандартное отклонения от b,

где b – искомая величина.

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

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

17

Средняя прибыль ± (1,96*стандартное отклонение прибыли)/ числоитераций.

В ячейке J11 (таблица 14) определена нижняя граница 95-процентного доверительного интервала для средней прибыли при производстве 40 000 открыток по формуле:

=D13-1,96*D14/КОРЕНЬ(1 000).

В ячейке J12 определена верхняя граница 95-процентного доверительного интервала для средней прибыли при производстве 40000 открыток по формуле:

=D13+2*D14/КОРЕНЬ(1 000).

Таблица 14

Верхняя и нижняя границы допустимой прибыли

Строки/
Столбцы	I	J	J
11	Нижняя
	граница	54 531,11	=D13-1,96*D14/КОРЕНЬ(1000)
12	Верхняя
	граница	60 606,26	=D13+2*D14/КОРЕНЬ(1000)

Для принятия управленческих решений мы на 95% уверены, что средняя прибыль при производстве 40 000 открыток составит от $54 531,11 до $60 606,26.

Задачи для самостоятельного решения Пример 4 (файл пример4.xls).

Дилер General Motors Сompany считает, что спрос на модель «Envoy» выпуска 2007 г. будет распределен по нормальному закону со средним, равным 200, и стандартным отклонением, равным 30. Его затраты на выпуск одной машины «Envoy» составят $25 000, и продает он ее по $40 000. Половину всех нереализованных машин модели «Envoy» можно продать по $30 000. В качестве возможного размера заказа дилер рассматривает 200, 220, 240, 260, 280 и 300 машин. Сколько машин следует заказать компании?

Пример 5 (файл пример5.xls).

Небольшой супермаркет пытается определить, сколько копий журналов «People» им следует заказывать каждую неделю. Менед-

18

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

Таблица 15

Ряд распределения случайной величины

Спрос	Вероятность
15	0,10
20	0,20
25	0,30
30	0,25
35	0,15

Супермаркет покупает каждый экземпляр журнала «People» за $1,00 и продает ее по цене $1,95. Каждый нереализованный журнал «People» они могут вернуть за $0,50. Сколько экземпляров журнала следует заказать супермаркету?

Тема 4. Основные приемы

обработки экономической информации

При работе с функциями требуется выполнить 2 действия:

1)вызвать нужную функцию;

2)задать ее аргументы.

Функции представлены в библиотеке и для их выбора существует 2 пути вызова Мастера функций.

Первый способ вызова функции:

1)установить курсор на ячейку, в которой должен оказаться результат выполнения функции;

2)одновременно нажать клавиши Shift+F3 – в ячейке появится знак «=», а в верхней части экрана откроется меню Мастера функций.

Второй способ:

19

1)установить курсор на нужную ячейку, в меню задать Вставка – Функция;

2)откроется экран Мастера функций, в котором осуществляется выбор нужной функции.

Различают 2 вида структуры функции: с аргументом и без аргумента. Структура функции с аргументом: ИМЯ_ФУНКЦИИ (аргумент1, аргумент2, …)

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

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

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

1. ЕСЛИ(условие; значение если условие истинно, значение если условие ложно) – используется для проверки выполнения условия;

2. СУММЕСЛИ(диапазон; критерий; диапазон суммирования)

–используется для суммирования ячеек по какому-либо условию; 3. СЧЕТЕСЛИ(диапазон; критерий) – подсчитывает количест-

во непустых ячеек в указанном диапазоне;

4.СРЗНАЧ(диапазон) – вычисляет среднее значение в указанных ячейках;

5.МАКС(диапазон), МИН(диапазон) – находит максимальное или минимальное значение в указанном диапазоне ячеек.

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

20