9. Создайте на пустом листе незаполненную таблицу, как на рис. 5. Рис. 5. Образец новой таблицы

10. Перейдите на предыдущий лист и выберите пункт меню Сервис  Анализ данных...  Описательная Статистика (рис. 6).

Рис. 6. Диалоговое окно «Анализа данных» 11.В открывшемся диалоговом окне ввести значения как на рис. 7.

• Входной интервал: значение поля «Перем.расх.» ($А$12:$А$511);

• Группирование: по столбцам;

• Параметры выхода (выходной интервал): Лист2!$А$2 (т.е. адрес на новом листе).

• Установите метку на параметре: Итоговая статистика.

• Нажмите ОК.

19

Рис. 7. Диалоговое окно «Описательная статистика»

12. Проделайте ту же работу для полей «Количество», «Цена», «Поступления» и «ЧСС».

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

Рис. 8. Итоговый результат задачи 1 Задача 2

В данной задаче найдем с помощью стандартных функций Excel, следующие значения:

• коэффициент вариации ЧСС;

• вероятность того, что ЧСС будет меньше нуля;

20

• вероятность того, что ЧСС будет больше максимума;

• вероятность того, что ЧСС будет находится в интервале [М(Е) + а;

max];

вероятность того, что ЧСС будет находиться в интервале [М(Е) - а;

1. Дополните таблицу следующими строками: «Коэффициент вариации», «Р(Е<=0)>>, «Р(Е<=МИН (Е))», «Р(М(Е) + а <=Е <= max)», «P(M(E) - а <=Е <=М(Е))>>.

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

Ячейка	Формула
Коэффициент вариации	В6/В2
Р(Е<=0)	НОРМРАСП(0;В2;В6; 1)
Р(Е<=МИН (Е))	НОРМР АСП(В 11 ;В2;В6; 1)
Р(М(Е) + а <=Е <= max)	НОРМР АСП(В 12;В2;В6; 1 )-НОРМР АСП(В2+В6;В2;В6;1)
Р(М(Е) - а <=Е <=М(Е))	НОРМР АСП(В2;В2;В6;1)-НОРМРАСП(В2-В6;В2;В6;1)

Функция НОРМРАСП

НОРМРАСП - возвращает нормальную функцию распределения для указанного среднего и стандартного отклонения. Эта функция имеет очень широкий круг приложений в статистике, включая проверку гипотез.

НОРМРАСП(х;среднее;стандартное_откл;интегральная)

х - значение, для которого строится распределение.

Среднее - среднее арифметическое распределения.

Стандартное_откл - стандартное отклонение распределения.

Интегральная - логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА, то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ, то возвращается функция плотности распределения.

21

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

Рис. 9. Итоговый результат задачи 2

Рассмотрим результаты проведенного имитационного эксперимента: Величина ожидаемой ЧСС (NPV) равна 3386,86 при стандартном отклонении 2567,02. Коэффициент вариации (0,76) несколько высок, но меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину ЧСС (NPV) не превышает 9%. Общее число отрицательных значений ЧСС (NPV) в выборке составляет 32 из 500. Таким образом, с вероятностью около 91% можно утверждать, что чистая современная стоимость проекта будет больше 0.

Для расчёта цены риска в данном случае используем показатель среднеквадратического отклонения - а, и матожидания - М (NPV). При этом вероятность того, что величина NPV окажется больше чем M(NPV) + а , равна 16% (ячейка F18). Вероятность попадания значения NPV в интервал [M(NPV) -а ; M(NPV)] равна 34%.

22