Интервал – А13 : E512; Группирование – по столбцам; Метки в первой

строке – флажок 4; Параметры вывода – Новый рабочий лист - Корреляция.

5. Удалите в книге лист Результаты анализа и сохраните ее под новым именем Статистика.

Вид полученных на новом рабочем листе Корреляция результатов после элементарного форматирования показан в таблице 5.1.18.

Таблица 5.1.18

	A	B	C	D	E	F
		Переменные, EV	Объем, Q	Цена, P	Поступления, NCF	Чист. пр. ст., NPV
	Переменные, EV
	Объем, Q	-0,08
	Цена, P	0,04	-0,02
	Поступления, NCF	-0,42	0,63	0,62
	Чист. пр. ст., NPV	-0,42	0,63	0,62	1
	Корреляция	Имитация	Лист 3	3 4

Как следует из результатов корреляционного анализа, переменные EV, Q и P являются независимыми, т.к. их коэффициенты корреляции близки к 0. NPV напрямую зависят от NCF. Между NCF, NPV и Q, P существует корреляционная зависимость средней степени. Умеренная обратная (отрицательная) корреляционная зависимость существует между NCF, NPV и EV.

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

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

Описательная статистика данных.

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

Пример 5.1.8. Необходимо определить статистические характеристики полученных в таблице 5.1.15 имитационных данных (блок ячеек А13: E512): переменных затрат - EV, объема произведенной и реализованной продукции - Q, поступлений (чистые платежи) - NCF, чистой приведенной стоимости - NPV.

Решение.

1. Откройте сохраненную ранее под именем Статистика книгу Excel (таблица 5.1.15 ) на листе Имитация и выберите в главном меню тему Сервис, пункт Анализ данных. В появившемся диалоговом окне Анализ данных выберите из списка «Инструменты анализа» пункт Описательная статистика.

2. В появившемся диалоговом окне Описательная статистика укажите в соответствующих аргументах: Входной интервал – A12: E512; Группирование – по столбцам; Метки в первой строке – 4; Параметры вывода – новый рабочий лист; Итоговая статистика – 4; Уровень надежности – 95 %.

После нажатия кнопки [ОК] новый лист книги Статистика будетзаполнен вычисленными характеристиками имитационных данных. Эти характеристики (после несложного форматирования и присвоения листу имени Статистика) показаны в таблице 5.1.19.

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

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

Мода – наиболее вероятное значение случайной величины. Если мода отсутствует, то Excel возвращает сообщение об ошибке (#Н/Д).

Эксцесс (e) – характеристика остроконечности (при положительном значении) или пологости (при отрицательном значении) распределения по сравнению с нормальной кривой.

Асимметричность (коэффициент асимметрии или скоса – s) – характеристика смещения распределения относительно математического ожидания (вправо – при положительном значении).

Для оценок значимостей коэффициента асимметрии и величины эксцесса рассчитывают их стандартные ошибки

s_s = [ 6 (n - 1)/(n + 1)(n + 3)]^1/2,

s_e = [ 24n (n - 2)(n - 3)/(n - 1)²(n + 3)(n + 5)]^1/2,

где n – число значений случайной величины.

Таблица 5.1.19

	A	B	C	D	E	F
		Переменные, EV	Объем, Q	Цена, P	Поступления, NCF	Чист. пр. ст., NPV
	Среднее	30,148	210,036	49,012	1447,703	3487,934
	Стандартная ошибка	0,149	2,596	0,247	31,949	121,112
	Медиана	30,189	206,768	49,232	1380,257	3232,260
	Мода	34,343	214,232	45,736	#Н/Д	#Н/Д
	Стандартное отклонение	3,336	58,050	5,530	714,401	2708,142
	Дисперсия выборки	11,128	3369,782	30,579	510368,855	7334033,276
	Эксцесс	0,311	-0,475	-0,201	0,735	0,735
	Асимметрич-ность	0,090	0,095	0,033	0,718	0,718
	Интервал	22,703	307,002	30,728	4317,748	16367,662
	Минимум	19,767	50,734	33,977	62,605	-1762,679
	Максимум	42,470	357,736	64,704	4380,353	14604,983
	Сумма	15074,232	105017,822	24506,050	723851,582	1743967,001
	Счет	500,000	500,000	500,000	500,000	500,000
	Уровень надежности (95,0 %)	0,293	5,101	0,486	62,771	237,952
	Статистика	Корр-ция	Им-ция	3 4

Если отношение соответствующих величин к их стандартным ошибкам меньше трех (s / s_s < 3 и e / s_e < 3), то они считаются несущественными, а их наличие объясняется воздействием случайных факторов. В противном случае как асимметрия, так и эксцесс статистически значимы.

Например, в рассматриваемом случае (n = 500) для NPV получим

s / s_s = 0,718/0,108 @ 6,648 > 3,

что свидетельствует о статистической значимости асимметрии или правосторонней скошенности распределения NPV.

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

Счет – число значений в заданном интервале.

Сумма – сумма случайных величин в заданном интервале.

Уровень надежности – величина доверительного интервала для математического ожидания, соответствующая заданному уровню надежности (95 %). Так, например, с вероятностью 0,95 величина математического ожидания NPV попадает в интервал 3487,934 ± 237,952.

Следует заметить, что коэффициент асимметрии, величину эксцесса, доверительный интервал возможно вычислить с помощью функций СКОС(), ЭКСЦЕСС() и ДОВЕРИТ() мастера функций.

Для наглядности построим гистограмму распределения случайных величин NPV. Предварительно заметим, что интервал 500 значений NPV в диапазоне (минимум = - 1762,679 ¸ максимум = 14604,983) составляет величину 16367,662. Следовательно шаг составляет 16367,662/500 @ 33. Для построения боле гладкой гистограммы необходимо выбрать ее шаг @ 330 и выполнить следующие действия.

1. На листе Имитация книги Статистика введите в ячейку G13 округленное минимальное значение NPV @ - 1763.

2. С помощью инструмента Прогрессия (пункта Заполнить темы Правка главного меню) постройте арифметическую прогрессию (расположение - по столбцам) с шагом 330 и предельным значением 14605. В результате будет заполнен блок ячеек G13: G62.

3. Выберите в главном меню тему Сервис, пункт Анализ данных. В появившемся диалоговом окне Анализ данных выберите из списка «Инструменты анализа» пункт Гистограмма. В качестве входного интервала укажите блок ячеек E13: E512, а в качестве выходного интервала – блок ячеек G13: G62. В параметрах вывода укажите новый рабочий лист и вывод графика.

Полученная гистограмма, изображенная на рис. 5.1.9 наглядно свидетельствует о скошенности распределения NPV вправо относительно своего среднего значения, равного 3487,934.

Рис. 5.1.9

5.1.11 Управление состоянием и структурой бизнеса.

Имитационное моделирование состояний производственных и

коммерческих процессов.

Пример 5.1.9. Моделируется реальная ситуация производственных или коммерческих процессов в течение года в условиях инфляции. Пусть начальная себестоимость единицы продукции (переменные затраты) 10 000 р. снижалась на 1 %, а ее начальная рыночная цена 11 000 р. повышалась на 4,5 % в месяц. В качестве других структурных параметров бизнеса выбраны: коэффициенты налога на прибыль – 0,22 % и НДС – 20 %, коэффициент реинвестиции прибыли

в производство 52 % с ежемесячным снижением на 1 %. Запланированные начальные постоянные затраты в 50 000 р. повышались на 10 % в месяц. При этом необходимо учесть равномерно распределенные помехи – колебания постоянных затрат при различных уровнях (амплитудах) помех. Управление в виде собственных и заемных средств составляло соответственно 2 * 50 000, 00 р. + 2 * 70 000, 00 р. в течение первых двух месяцев. Необходимо определить вероятный период окупаемости бизнеса по критерию чистой приведенной стоимости при различных уровнях помех. Чистая приведенная стоимость рассчитывается при норме дисконтирования в 10 %. Необходимо также определить минимально допустимую величину коэффициента реинвестиции. Тем самым, в задаче необходимо не только провести имитационное моделирование состояний бизнеса, но и промоделировать его структурную (в данном случае параметрическую) перестройку.

Решение.

Оформите шаблон, фрагмент которого (для двух периодов состояний - январь, февраль) показан в таблице 5.1.20, присвоив книге имя Бизнес, а листу Excel имя Состояния. Шаблон должен быть оформлен по декабрь месяц включительно.

Формулы, используемые в таблице 5.1.20, приведены в таблице 5.1.21. Все они построены на основе зависимостей 4.1.6, рассмотренных в главе 4.

Основная трудность построения шаблона связана с набором рекуррентных выражений в блоках ячеек D23: O23, D25: O25 и D26: O26. Для упрощения набора соответствующих формул следует воспользоваться инструментом их копирования, однако следует заметить, что в каждую последующую ячейку необходимо вносить дополнения, соответствующие процессу накопления.

Масштабирование в блоке ячеек D33: O33 (в 10 раз) необходимо для одновременного отображения на диаграммах нескольких построенных зависимостей. В данном случае - постоянных затрат (EC), постоянных затрат + помеха (EC + H), критерия чистой приведенной стоимости (NPV). В отсутствия масштабирования графики EC и EC+H не будут заметны на фоне больших значений NPV.

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

Случай А (рис. 5.1.10а) показывает, что даже при значительном, 35%-ом уровне помех, возможно достичь эффективности проекта (по критерию чистой приведенной стоимости) не позже октября, если осуществлять оптимальное управление его состояниями - вложить в проект собственные и заемные средства в течение лишь первых двух месяцев.

Таблица 5.1.20

	A	B	C	D	E
	Динамика состояний бизнес процессов
				Периоды состояний, t:
	Структурные параметры бизнеса:			Янв	Фев
	Переменные затраты, EVt		10 000,00р.	9 900,00р.
	Цена единицы продукции, Pt			11 000,00р.	11 500,00р.
	Коэффициент реинвестиции, at			0,52	0,51
	Налог на прибыль, taxt	0,22		0,00	0,00
	НДС, taxНДС	0,20		0,20	0,20
	Помехи:
	Граница помехи, Hmax	35 000,00 р.
	Линейная помеха, равномерно распределенная в интервале (- H1max; H1max)	Ht	-26 876,00р.	-32 115,00р.
	Управление бизнесом:
	Собственные средства, Ot			50 000,00р.	50 000,00р.
	Заемные средства, It			70 000,00р.	70 100,00р.
	Постоянные затраты, ECt		EC	50 000,00р.	55 000,00р.
	Постоянные затраты + помеха, EСt + Ht	EC+H	23 124,00р.	22 885,00р.
	Управление c учетом помех, Ct = Ot + It + Ht	43 124,00р.	32 985,00р.
	Динамика состояний бизнеса в условиях помех:
	Объем производства, Qt		Qt	8	13
	Прибыль с текущих продаж, Pt*Qt - EVt*Qt - ECt		-42 049,41р.	-33 689,12р.
	Накопленная прибыль, PROFt			-84 028,53р.	-63 265,99р.
	Валовая прибыль, GPROFt			-93 124,00р.	-87 985,00р.
	Чистая прибыль, NPROFt			-93 124,00р.	-87 985,00р.
	Динамика эффективности бизнеса:
	Норма дисконтирования, d	10,00 %
	Поток платежей, CFt = NPROFt - It		-163 124,00р.	-158 085,00р.
	Чистая современная стоимость, NPVt	NPV	-163 124,00р.	-306 837,64р.
	Промасштабированная NPVt	10	NPV/10	-16 312,40р.	-30 683,76р.
	Состояния	Лист 2	Лист 3	3 4

Таблица 5.1.21

Ячейка	Формула
D13:O13	=СЛУЧМЕЖДУ(-$B$12;$B$12)
D19:O19	=D18+D13: =O18+O13
D20:O20	=D16+D17+D13: =O16+O17+O13
D23:O23	=(D20+(1-D8-D9)*(C7*C6*C23))/((1-D8)*D5-(1-D8-D9)*D7*D6): =(O20+(1-O8-O9)*(N7*N6*N23+M7*M6*M23 + L7*L6*L23+K7*K6*K23 + J7*J6*J23+I7*I6*I23+H7*H6*H23+G7*G6*G23+F7*F6*F23+E7*E6*E23 + D7*D6*D23))/((1-O8)*O5-(1-O8-O9)*O7*O6)
D24:O24	=D6*D23-D5*D23-D18: =O6*O23-O5*O23-O18
D25:O25	=D7*D6*D23+C7*C6*C23-D5*D23-D18: =O7*O6*O23+N7*N6*N23+M7*M6*M23+L7*L6*L23+K7*K6*K23+J7*J6*J23+ I7*I6*I23+H7*H6*H23+G7*G6*G23+F7*F6*F23+E7*E6*E23+D7*D6*D23- O5*O23-O18
D26:O26	=(1-D9)*(D7*D6*D23+C7*C6*C23)-D5*D23-D18: =(1-O9)*(O7*O6*O23+N7*N6*N23+M7*M6*M23+L7*L6*L23+ K7*K6*K23+J7*J6*J23+I7*I6*I23+H7*H6*H23+G7*G6*G23+F7*F6*F23+ E7*E6*E23+D7*D6*D23)-O5*O23-O18
D27:O27	=(1-D8)*D26: =(1-O8)*O26
D31:O31	=D27-D17: O27-O17
D32	=D31
E32	=НПЗ(B30;E31)+D32
F32	=НПЗ(B30;E31:F31)+D32
G32	=НПЗ(B30;E31:G31)+D32
…	…
O32	=НПЗ(B30;E31:O31)+D32
D33:O33	=D32/$B$33: =O32/$B$33

Рис. 5.1.10 а

Рис. 5.1.10 б

Случай Б (рис. 5.1.10б) показывает, что при изменении лишь одного структурного параметра проекта – коэффициента реинвестиции прибыли в производство всего лишь на 5 % (с 52 % до 47 %) при том же уровне помех проект может оказаться бесперспективным.

5.1.12 Решение оптимизационных задач управления.

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

Ресурсное планирование объема выпускаемой продукции.

Пример 5.1.10. В таблице 5.1.22 дано решение примера 4.3.1 главы 4. Ограничения вносятся в верхнюю часть таблицы. Коэффициенты отношений – в область C2:D4, правая часть уравнений – в F2:F4. Коэффициенты целевой функции – в C6:D6. В процессе расчетов в области Е2:Е4 отображаются вычисляемые (фактические) значения правой части неравенств. Сюда вводятся формулы:

E2=СУММПРОИЗВ(C$7:D$7;C2:D2),

E3=СУММПРОИЗВ(C$7:D$7;C3:D3),

E4=СУММПРОИЗВ(C$7:D$7;C4:D4).

Таблица 5.1.22

	A	B	C	D	E	F
	№	Вид ресурса	Продукт 1	Продукт 2	Вычисленные значения	Заданные ограничения
		Сырье	3	6	18,0	18
		Труд	6	4	24,0	24
		Транспорт	2	1	7,5	2
					Прибыль:
		Целевая функция	5,00	5,50	23,25
		Результаты	3,00	1,50

Аналогично значение целевой функции (прибыль) равно

E6=СУММПРОИЗВ(C$7:D$7;C6:D6).

Если размерность системы уравнений (как в нашем случае) невелика, можно воспользоваться более простыми функциями (таблица 5.1.23):

Таблица 5.1.23

E2=C2*С$7+D2*D$7, E4=C4*С$7+D4*D$7,

E3=C3*С$7+D3*D$7, E6=C6*С$7+D2*D$7.

Результат (оптимальное количество Продукта 1 и Продукта 2) формируется в области С7:D7. Клетки, в которых вычисляются какие-то значения, выделены жирным шрифтом. Остальное – исходные данные.

Для оптимизации в Excel используется инструмент Поиск решения, вызываемый через меню Сервис, который предъявляет окно (рис. 5.1.11). Сначала задается ячейка, содержащая оптимизируемое значение (здесь Е2), затем указывается его желаемое значение (у нас максимальное). Можно задать не только максимальное/минимальное значения, но и любую произвольную величину, введя ее в специальное поле (Равной значению:).

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

Рис. 5.1.11

Рис. 5.1.12

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

Если вычисления оказались успешными, Excel предъявит следующее (рис. 5.1.13) окно итогов. Их можно сохранить или отказаться (Восстановить исходные значения). Сохраним их. Кроме того, можно получить один из трех видов отчетов (Результаты, Устойчивость, Пределы), позволяющие лучше осознать полученные результаты, в том числе, оценить их достоверность.

Как видим, результаты, вычисленные в таблице, совпали с результатами, найденными вручную с помощью графика. Здесь же попутно мы можем сравнить предельные и фактически затребованные значения ресурсов (Сырье: 18 из 18; Труд: 24 из 24; Транспорт: 2 из 7,5).

Рис. 5.1.13

Планирование транспортных перевозок.

Пример 5.11. Пусть с трех складов требуется развести грузы в объемах 50, 30 и 40 тонн потребителям в 2 пункта доставки в объеме 40 и 80 тонн (таблица 5.1.24). Известна цена перевозки единицы груза с каждого склада в каждый пункт доставки (столбцы С и Е). Наша задача заключается в следующем: определить такие объемы перевозок со складов в пункты доставки, чтобы стоимость транспортировки была минимальной. В таблице 5.1.24 искомые значения уже вычислены и обведены жирной рамкой. Видим, что стоимость всего “мероприятия” составляет 1300 руб.

Таблица 5.1.24

	A	B	C	D	E	F
			Пункт доставки 1	Пункт доставки 2
	Наличие груза на складах	Цена перевозки	Объем груза	Цена перевозки	Объем груза
	Склад 1	50 т	10 р/т	6,40 т	5 р/т	43,60 т
	Склад 2	30 т	20 р/т	30,00 т	25 р/т	0,00 т
	Склад 3	40 т	15 р/т	3,60 т	10 р/т	36,40 т
	ВСЕГО	120 т		40,00 т		80,00 т
	Целевая функция:	1300,00 р

Рассмотрим, как были получены эти значения. Прежде всего, в ячейку С8 заносим целевую функцию. Здесь это стоимость всех “элементарных” перевозок, вычисляемая как сумма произведений цены на объем груза

С8=C3*D3+E3*F3+C4*D4+E4*F4+C5*D5+E5*F5.

Для решения снова используем инструмент Поиск решения, где введем следующие параметры