Как следует из названия этой функции, она позволяет получить случайное число из заданного интервала. При этом тип возвращаемого числа (т.е. вещественное или целое) зависит от типа заданных аргументов.
В качестве примера, сгенерируем случайное значение для переменной Q (объем выпуска продукта). Согласно табл. 3.1, эта переменная принимает значения из диапазона 150 - 300.
Введите в любую ячейку ЭТ формулу:
=СЛУЧМЕЖДУ(150; 300) (Результат: 210)
(Читатель может получить другой результат - любое число из заданного диапазона)
Если задать аналогичные формулы для переменных P и V, а также формулу для вычисления NPV и скопировать их требуемое число раз, можно получить генеральную совокупность, содержащую различные значения исходных показателей и полученных результатов. После чего, используя рассмотренные в предыдущих главах статистические функции, нетрудно рассчитатьсоответствующие параметры распределения и провести вероятностный анализ.
Продемонстрируем изложенный подход на решении примера 3.1. Перед тем, как приступить к разработке шаблона, целесообразно установить в ЭТ режим ручных вычислений. Для этого необходимо выполнить следующие действия.
- Выбрать в главном меню тему "Сервис".
- Выбрать пункт "Параметры" подпункт "Вычисления".
- Установить флажок "Вручную" и нажать кнопку "ОК".
Приступаем к разработке шаблона. С целью упрощения и повышения наглядности анализа выделим для его проведения в рабочей книге ППП EXCEL два листа.
Первый лист - "Имитация", предназначен для построения генеральной совокупности (рис. 3.1). Определенные в данном листе формулы и собственные имена ячеек приведены в табл. 3.4. и 3.5.
Рис. 3.1. Лист "Имитация"
Таблица 3.4.
Формулы листа "Имитация"
Ячейка | Формула |
Е7 | =B7+10-2 |
A10 | =СЛУЧМЕЖДУ($B$3;$C$3) |
A11 | =СЛУЧМЕЖДУ($B$3;$C$3) |
B10 | =СЛУЧМЕЖДУ($B$4;$C$4) |
B11 | =СЛУЧМЕЖДУ($B$4;$C$4) |
C10 | =СЛУЧМЕЖДУ($B$5;$C$5) |
C11 | =СЛУЧМЕЖДУ($B$5;$C$5) |
D10 | =(B10*(C10-A10)-Пост_расх-Аморт)*(1-Налог)+Аморт |
D11 | =(B11*(C11-A11)-Пост_расх-Аморт)*(1-Налог)+Аморт |
E10 | =ПЗ(Норма;Срок;-D10)-Нач_инвест |
E11 | =ПЗ(Норма;Срок;-D11)-Нач_инвест |
Таблица 3.5.
Имена ячеек листа "Имитация"
Адрес ячейки | Имя | Комментарии |
Блок A10:A11 | Перем_расх | Переменные расходы |
Блок B10:B11 | Количество | Объем выпуска |
Блок C10:C11 | Цена | Цена изделия |
Блок D10:D11 | Поступления | Поступления от проекта NCFt |
Блок E10:E11 | ЧСС | Чистая современная стоимость NPV |
Первая часть листа (блок ячеек А1.Е7) предназначена для ввода диапазонов изменений ключевых переменных, значения которых будут генерироваться в процессе проведения эксперимента. В ячейке В7 задается общее число имитаций (экспериментов). Формула, заданная в ячейке Е7, вычисляет номер последней строки выходного блока, в который будут помещены полученные значения. Смысл этой формулы будет раскрыт позже.
Вторая часть листа (блок ячеек А9.Е11) предназначена для проведения имитации. Формулы в ячейках А10.С11 генерируют значения для соответствующих переменных с учетом заданных в ячейках В3.С5 диапазонов их изменений. Обратите внимание на то, что при указании нижней и верхней границы изменений используется абсолютная адресация ячеек.
Формулы в ячейках D10.E11вычисляют величину потока платежей и его чистую современную стоимость соответственно. При этом значения постоянных переменных берутся из следующего листа шаблона - "Результаты анализа".
Лист "Результаты анализа" кроме значений постоянных переменных содержит также функции, вычисляющие параметры распределения изменяемых (Q, V, P) и результатных (NCF, NPV) переменных и вероятности различных событий. Определенные для данного листа формулы и собственные имена ячеек приведены в табл. 3.6 и 3.7. Общий вид листа показан на рис. 3.2.
Таблица 3.3.
Формулы листа "Результаты анализа"
Ячейка | Формула |
B8 | =СРЗНАЧ(Перем_расх) |
B9 | =СТАНДОТКЛОНП(Перем_расх) |
B10 | =B9/B8 |
B11 | =МИН(Перем_расх) |
B12 | =МАКС(Перем_расх) |
C8 | =СРЗНАЧ(Количество) |
C9 | =СТАНДОТКЛОНП(Количество) |
C10 | =C9/C8 |
C11 | =МИН(Количество) |
C12 | =МАКС(Количество) |
D8 | =СРЗНАЧ(Цена) |
D9 | =СТАНДОТКЛОНП(Цена) |
D10 | =D9/D8 |
D11 | =МИН(Цена) |
D12 | =МАКС(Цена) |
E8 | =СРЗНАЧ(Поступления) |
E9 | =СТАНДОТКЛОНП(Поступления) |
E10 | =E9/E8 |
E11 | =МИН(Поступления) |
E12 | =МАКС(Поступления) |
F8 | =СРЗНАЧ(ЧСС) |
F9 | =СТАНДОТКЛОНП(ЧСС) |
F10 | =F9/F8 |
F11 | =МИН(ЧСС) |
F12 | =МАКС(ЧСС) |
F13 | =СЧЁТЕСЛИ(ЧСС;"<0") |
F14 | =СУММЕСЛИ(ЧСС;"<0") |
F15 | =СУММЕСЛИ(ЧСС;">0") |
Е18 | =НОРМАЛИЗАЦИЯ(D18;$F$8;$F$9) |
F18 | =НОРМСТРАСП(E18) |
Таблица 3.7.
Имена ячеек листа "Результаты анализа"
Адрес ячейки | Имя | Комментарии |
B2 | Нач_инвест | Начальные инвестиции |
B3 | Пост_расх | Постоянные расходы |
B4 | Аморт | Амортизация |
D2 | Норма | Норма дисконта |
D3 | Налог | Ставка налога на прибыль |
D4 | Срок | Срок реализации прока |
Рис. 3.2. Лист "Результаты анализа"
Поскольку формулы листа содержат ряд новых функций, приведем необходимые пояснения.
Функции МИН() и МАКС() вычисляют минимальное и максимальное значение для массива данных из блока ячеек, указанного в качестве их аргумента.Имена и диапазоны этих блоков приведены в табл. 3.7.
Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке, значения которых удовлетворяют заданному условию. Функция имеет следующий формат:
=СЧЕТЕСЛИ(блок; "условие").
В данном случае, заданная в ячейке F13, эта функция осуществляет подсчет количества отрицательных значений NPV, содержащихся в блоке ячеек ЧСС (см. табл. 3.7).
Механизм действия функции СУММЕСЛИ() аналогичен функции СЧЕТЕСЛИ(). Отличие заключается лишь в том,что эта функция суммирует значения ячеек в указанном блоке, если они удовлетворяют заданному условию. Функция имеет следующий формат:
=СУММЕСЛИ(блок; "условие").
В данном случае, заданные в ячейках F14.F15, функции осуществляет подсчет суммы отрицательных (ячейка F14) и положительных (ячейка F14) значений NPV,содержащихся в блоке ЧСС. Смысл этих расчетов будет объяснен позже.
Две последние формулы (ячейки Е18и F18) предназначены для проведения вероятностного анализа распределения NPV и требуют небольшого теоретического отступления.
В рассматриваемом примере мы исходим из предположения о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет иметь результатная величина - показатель NPV, заранее определить нельзя.
Одно из возможных решений этой проблемы - попытаться аппроксимировать неизвестное распределение каким-либо известным. При этом в качестве приближения удобнее всего использовать нормальное распределение. Это связано с тем, что в соответствии с центральной предельной теоремой теории вероятностей при выполнении определенных условий сумма большого числа случайных величин имеет распределение, приблизительно соответствующее нормальному.
В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения - т.н. стандартное нормальное распределение. Математическое ожидание стандартно распределенной случайной величины Е равно 0: M(E) = 0. График этого распределения симметричен относительно оси ординат и оно характеризуется всего одним параметром - стандартным отклонением , равным 1.
Приведение случайной переменной E к стандартно распределенной величине Z осуществляется с помощью т.н. нормализации - вычитания средней и последующего деления на стандартное отклонение:
(3.3).
Как следует из (3.3), величина Z выражается в количестве стандартных отклонений. Для вычисления вероятностей по значению нормализованной величины Z используются специальные статистические таблицы.
В ППП EXCEL подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП().