Функция СЛУЧМЕЖДУ (нижн_граница; верхн_граница)

В качестве примера сгенерируем случайное значение для переменной Q (объем выпуска продукта).

Введите в любую ячейку ЭТ формулу: =СЛУЧМЕЖДУ (150/ 300) (Результат: 210)31.

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

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

Выбрать в главном меню тему Сервис. 2.

Выбрать пункт Параметры, подпункт Вычисления. 3.

Установить флажок Вручную и нажать кнопку [ОК].

Приступаем к разработке шаблона. С целью упрощения и

повышения наглядности анализа выделим для его проведения в рабочей книге ППП EXCEL два листа.

Первый лист — Имитация, предназначен для построения генеральной совокупности (рис. 6.1). Определенные в данном листе формулы и собственные имена ячеек приведены в табл. 6.4 и 6.5.

Первая часть листа (блок ячеек А1.Е7) предназначена для ввода диапазонов изменений ключевых переменных, значения которых будут генерироваться в процессе проведения эксперимента. В ячейке В7 задается общее число имитаций (экспериментов). ш Д Ъп В 1 с .

L 5 Перем, расходы Количество Цена 7 Г Экспериментов = Номер стр. = 8 Переменные Количество Цена Поступления ЧСС 9 расходы (V) (Q) (Р) (NCFt) (NPVt) 10 11 0 0 0 0 0 0 0,00 0.00 0,00 — ОЛЮ 12 J3 чр і 7 fV|Q- hi : w ЇШ

Рис. 6.1. Лист Имитация

Таблица 6.4. Формулы листа Имитация Ячейка Формула Е7 =B7+10-2 А10 =СЛУЧМЕЖДУ($B$3;$C$3) All =СЛУЧМЕЖДУ($B$3;$C$3) B10 =СЛУЧМЕЖДУ($B$4;$C$4) Bll =СЛУЧМЕЖДУ($B$4;$C$4) C10 =СЛУЧМЕЖДУ($B$5;$C$5) Cll =СЛУЧМЕЖДУ($B$5;$C$5) D10 =(B10*(C10-A10)-Пост расх-Аморт)*(1-Налог) +Аморт Dll =(В11*(С11-А11)-Пост расх-Аморт)*(1-Налог) +Аморт ЕЮ =ПЗ(Норма;Срок:-D10)-Нач инвест Ell =ПЗ (Норма / Срок; -Dll) -Нач_инвест

Таблица 6.5. Имена ячеек листа Имитация

Адрес ячейки Имя Комментарии Блок А10: All nepeM_j?acx Переменные расходы Блок BIO: В11 Количество Объем выпуска Блок СЮ:СИ Цена Цена изделия Блок D10:D11 Поступления Поступления от проекта NCFt Блок Е10:Е11 ЧСС Чистая современная стоимость NPV

Формула, заданная в ячейке Е7, вычисляет номер последней строки выходного блока, в который будут помещены полученные значения. Смысл этой формулы далее раскрыт.

Вторая часть листа (блок ячеек А9.Е11) предназначена для проведения имитации. Формулы в ячейках А10.С11 генерируют значения для соответствующих переменных с учетом заданных в ячейках ВЗ. С5 диапазонов их изменений. Обратите внимание на то, что при указании нижней и верхней границы изменений используется абсолютная адресация ячеек.

Формулы в ячейках D10.E11 вычисляют величину потока платежей и его чистую современную стоимость соответственно. При этом значения постоянных переменных берутся из следующего листа шаблона — Результаты анализа.

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

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

Таблица 6.6. Формулы листа Результаты анализа Ячейка Формула В8 =СРЗНАЧ(Перем^расх) В9 ^СТАНДОТКЛОНП(Перем расх) В10 =В9/В8 В11 =МИН(Перем_расх) В12 =МАКС(Перем_расх) С8 =СРЗНАЧ(Количество) С9 =СТАНДОТКЛОНП(Количество) СЮ =С9/С8 СИ =МИН(Количество) С12 =МАКС(Количество) D8 =СРЗНАЧ(Цена) D9 =СТАНДОТКЛОНП(Цена) D10 =D9/D8 D11 =МИН(Цена) D12 =МАКС(Цена) Е8 =СРЗНАЧ(Поступления) Е9 =СТАНДОТКЛОНП(Поступления) ЕЮ =Е9/Е8 Е11 =МИН(Поступления) Е12 =МАКС(Поступления) F8 =СРЗНАЧ(ЧСС) F9 ^СТАНДОТКЛОНП(ЧСС) F10 =F9/F8 F11 =МИН(ЧСС) F12 =МАКС(ЧСС) F13 =СЧЁТЕСЛИ(ЧСС;"<0") F14 =СУММЕСЛИ(ЧСС;"<0") F15 =СУММЕСЛИ(ЧСС;">0") Е18 НОРМАЛИЗАЦИЯ (D18 ; $F$8 ;$F$9) F18 =НОРМСТРАСП(Е18)

Таблица 6.7. Имена ячеек листа Результаты анализа

Адрес ячейки Имя Комментарии В 2 Hav инвест Начальные инвестиции ВЗ Пост расх Постоянные расходы В4 Аморт Амортизация D2 Норма Норма дисконта D3 Налог Ставка налога на прибыль D4 Срок Срок реализации проекта

-1 А і 8 J о. Ї f 7 Имитационныи анализ (Метой Монте-Карііо) г Р аспреРеление с равными вероятностями Z Начальные инвест. (I) Норма г 3 Пост, расколы (F) Іалог (Г) 4 і Амсртнзацня (А) Грок (її) Показатели Переменные Количество Цена Поступления MPV 6 m Л (NCF« 7 15 Среднее значение nnn 0,00 "00 3,f 1 одо 9 Стандарт, отклонение 0,00 (U0 (i,00 ОДО од- 10 Ко^ф. вариации # ДЕЛ/О! ЫЕП/0! #ДЕЛ/0! ЩЕПД)! /ДЕЛА)! П Ммнимуі' 0,00 0,00 000 І.ДЮ 0ДН> 12 Максимум 0,04 0ДЮ 0,00 U,00 год О Чисю случаев NPV < 0 0.0D 14- Сумм j убытков 1^00 16. Сумма дохода в 0J01 % 17 Вероятность p(NPV<- X) lej і ma (X) Нормал. (X) p(NPV<-X) 1» № 0,00 ячиспо- #ЧИ(ЛОІ

W Н ' 1 Им-г-. . Результаты анализ,, J* ^ ^ і * и ч*ь

Рис. 6.2. Лист Результаты анализа

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

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

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

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