АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция

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

Читайте также:
  1. II.1.1 Разновидности метонимии и ее функция в процессе создания газетной экспрессии
  2. Анализ временного ряда на стационарность (автокорреляционная функция)
  3. АРГУМЕНТ, ФУНКЦИЯ
  4. Артериолы, капилляры, венулы: функция и строение. Органоспецифичность капилляров. Понятие о гистогематическом барьере.
  5. Банк правительства как функция ЦБ
  6. В). каталитическая функция
  7. Волновая функция. Уравнение Шредингера
  8. ВЫДЕЛИТЕЛЬНАЯ (ЭКСКРЕТОРНАЯ) ФУНКЦИЯ СЛЮННЫХ ЖЕЛЕЗ. УЧАСТИЕ СЛЮННЫХ ЖЕЛЕЗ В ПОДДЕРЖАНИИ ГОМЕОСТАЗА ОРГАНИЗМА.
  9. Выделительная функция печени и желудочно-кишечного тракта
  10. ГЛАВА 14 ФУНКЦИЯ СЛЕЗООТВЕДЕНИЯ, МЕТОДЫ ИССЛЕДОВАНИЯ ПРОХОДИМОСТИ СЛЕЗНЫХ ПУТЕЙ. ПАТОЛОГИЯ СЛЕЗНЫХ ОРГАНОВ
  11. ГЛАВА1.7. УРАВНЕНИЯ ЧЕТЫРЕХПОЛЮСНИКА В ГИПЕРБОЛИЧЕСКИХ ФУНКЦИЯХ
  12. ГОЛОС, КАК ФУНКЦИЯ

Как следует из названия этой функции, она позволяет получить случайное число из заданного интервала. При этом тип возвращаемого числа (т.е. вещественное или целое) зависит от типа заданных аргументов.

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

Введите в любую ячейку ЭТ формулу:

=СЛУЧМЕЖДУ(150; 300) (Результат: 210)
(Читатель может получить другой результат - любое число из заданного диапазона)

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

Приступаем к разработке шаблона. С целью упрощения и повышения наглядности анализа выделим для его проведения в рабочей книге ППП 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 подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП().


1 | 2 | 3 | 4 | 5 |

Поиск по сайту:



Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Студалл.Орг (0.004 сек.)