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

 

Формулы в ячейках D10:E11 вычисляют величину потока платежей и его чистую современную стоимость соответственно.

Обратите внимание!

Значения постоянных переменных берутся из листа шаблона - "Результаты анализа";.

 

Лист "Результаты анализа" кроме значений постоянных переменных содержит также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных, значения которых выбираются из листа «Имитация».

Например, по формуле «=СРЗНАЧ(Перемененные расходы)» определяется среднее значение переменных расходов на проект, полученных в результате имитационного эксперимента. Исходные данные эксперимента берутся с листа «Имитация»: блок ячеек от ячейки А10 до ячейки, координаты которой определяются формулой ячейки Е7 листа «Имитация»: =B7+10-2.

Определенные для данного листа формулы и собственные имена ячеек приведены в табл. 6 и табл.7. Общий вид листа показан на рис.2.

 

Данные для нахождения средних значений, минимума, максимума и пр. (табл. 3) берутся из листа «Имитация»

 

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

Функции МИН() и МАКС() вычисляют минимальное и максимальное значение для массива данных из блока ячеек, указанного в качестве их аргумента. Имена и диапазоны этих блоков приведены в табл.7.

Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию. Функция имеет следующий формат:

 

=СЧЕТЕСЛИ(блок; "условие").

В данном случае, заданная в ячейке F13, функция СЧЕТЕСЛИ() осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС (см. табл.7).

Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ(). Отличие заключается лишь в том, что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат:

=СУММЕСЛИ(блок; "условие").

В данном случае, заданные в ячейках F14 и F15 функции осуществляют подсчет суммы отрицательных (ячейка F14) и положительных (ячейка F15) значений NPV, содержащихся в блоке ЧСС. Смысл этих расчетов будет объяснен позже.

Две последние формулы (ячейки Е18 и F18) предназначены для проведения вероятностного анализа распределения NPV и требуют небольшого теоретического отступления.

В рассматриваемом примере было сделано допущение, что ключевые переменные Q, V, P независимы и равномерно распределены. Однако какое распределение при этом будет иметь результатная величина - показатель NPV, заранее определить нельзя.

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

В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения - т.н. стандартное нормальное распределение. Математическое ожидание стандартно распределенной случайной величины Z равно 0: M(Z)=0. График этого распределения симметричен относительно оси ординат и оно характеризуется всего одним параметром - стандартным отклонением , равным 1.

Приведение случайной переменной Х к стандартно распределенной величине Z осуществляется с помощью т.н. нормализации - вычитания средней и последующего деления на стандартное отклонение:

(1)

Для вычисления вероятностей по значению нормализованной величины Z используются специальные статистические таблицы.

В ППП EXCEL подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП().