Студентам ИТ / 3 ЛП_ИТ / ИТ_прогнозирования / ИТ_фин_произв_бизнесом_Excel
.pdfФормулы, используемые в таблице 5.1, приведены в таблице 5.2.
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 5.1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A |
|
|
|
|
|
|
B |
|
C |
D |
E |
1 |
|
Динамика состояний бизнес процессов |
|
|||||||||
2 |
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
|
|
|
|
Периоды состояний, t: |
|
4 |
Структурные параметры |
|
|
|
|
|
|
Янв |
Фев |
|||
|
бизнеса: |
|
|
|
|
|
|
|
|
|
|
|
5 |
Переменные затраты, |
|
EVt |
|
|
|
10 000,00р. |
9 900,00р. |
||||
6 |
Цена единицы продукции, |
Pt |
|
|
|
|
|
11 000,00р. |
11 500,00р. |
|||
7 |
Коэффициент реинвестиции, at |
|
|
|
|
|
0,52 |
0,51 |
||||
8 |
Налог на прибыль, |
|
|
|
taxt |
|
|
0,22 |
|
0,00 |
0,00 |
|
9 |
НДС, |
|
|
taxНДС |
|
|
0,20 |
|
0,20 |
0,20 |
||
10 |
|
|
|
|
|
|
|
|
|
|
|
|
11 |
Помехи: |
|
|
|
|
|
|
|
|
|
|
|
12 |
Граница помехи, |
|
|
Hmax |
|
35 000,00р. |
|
|
|
|||
13 |
Линейная помеха, равномерно |
|
|
|
|
Ht |
-26 876,00р. |
-32 115,00р. |
||||
|
распределенная в интервале (-H1max; H1max) |
|
|
|
||||||||
14 |
|
|
|
|
|
|
|
|
|
|
|
|
15 |
Управление бизнесом: |
|
|
|
|
|
|
|
|
|
||
16 |
Собственные средства, |
|
Ot |
|
|
|
|
50 000,00р. |
50 000,00р. |
|||
17 |
Заемные средства, |
|
|
|
It |
|
|
|
|
70 000,00р. |
70 100,00р. |
|
18 |
Постоянные затраты, |
|
ECt |
|
|
|
EC |
50 000,00р. |
55 000,00р. |
|||
19 |
Постоянные затраты + помеха, |
EСt + Ht |
|
EC+H |
23 124,00р. |
22 885,00р. |
||||||
20 |
Управление c учетом помех, |
Ct |
= Ot + It |
- EС+ Ht |
43 124,00р. |
32 985,00р. |
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
21 |
|
|
|
|
|
|
|
|
|
|
|
|
22 |
Динамика состояний бизнеса в условиях помех: |
|
|
|||||||||
23 |
Объем производства, |
Qt |
|
|
|
|
|
Qt |
8 |
13 |
||
24 |
Прибыль с текущих продаж, |
|
|
|
|
|
|
|
|
|||
|
|
|
|
Pt*Qt - EVt*Qt - ECt |
|
-42 049,41р. |
-33 689,12р. |
|||||
25 |
Накопленная прибыль, PROFt |
|
|
|
|
|
-84 028,53р. |
-63 265,99р. |
||||
26 |
Валовая прибыль, |
GPROFt |
|
|
|
|
|
|
-93 124,00р. |
-87 985,00р. |
||
27 |
Чистая прибыль, |
NPROFt |
|
|
|
|
|
|
-93 124,00р. |
-87 985,00р. |
||
28 |
|
|
|
|
|
|
|
|
|
|
|
|
29 |
Динамика эффективности бизнеса: |
|
|
|
|
|||||||
30 |
Норма дисконтирования, |
d |
|
|
|
10,00% |
|
|
|
|||
31 |
Поток платежей, CFt = NPROFt - It |
|
|
|
-163 124,00р. |
-158 085,00р. |
||||||
32 |
Чистая современная стоимость, NPVt |
|
NPV |
-163 124,00р. |
-306 837,64р. |
|||||||
|
|
|
|
|
|
|
|
|
|
|||
33 |
Промасштабированная |
NPVt |
|
|
10 |
NPV/10 |
-16 312,40р. |
-30 683,76р. |
||||
|
Состояния |
|
|
Лист 2 |
|
Лист 3 |
|
|
|
|||
21
Таблица 5.2
Ячейка |
Формула |
D13:O13 |
=СЛУЧМЕЖДУ(-$B$12;$B$12) |
D19:O19 |
=D18+D13 : =O18+O13 |
D20:O20 |
=D16+D17 - D18+D13 : =O16+O17 - O18+O13 |
D23 |
=(D20+(1-D8-D9)*(C7*C6*C23))/((1-D8)*D5-(1-D8-D9)*D7*D6) |
E 23 |
==(E20+(1-E8-E9)*(C7*C6*C23+ D7*D6*D23))/((1-E8)*E5-(1-E8-E9)*E7*E6) |
….. |
|
O23 |
=(O20+(1-O8-O9)*(N7*N6*N23+M7*M6*M23 + L7*L6*L23+K7*K6*K23 + |
|
J7*J6*J23+I7*I6*I23+H7*H6*H23+G7*G6*G23+F7*F6*F23+E7*E6*E23 + |
|
D7*D6*D23))/((1-O8)*O5-(1-O8-O9)*O7*O6) |
D24:O24 |
=D6*D23-D5*D23-D18 : =O6*O23-O5*O23-O18 |
D25: |
=D7*D6*D23+C7*C6*C23-D5*D23-D18 |
…… |
|
O25 |
=O7*O6*O23+N7*N6*N23+M7*M6*M23+L7*L6*L23+K7*K6*K23+J7*J6*J23 |
|
+ I7*I6*I23+H7*H6*H23+G7*G6*G23+F7*F6*F23+E7*E6*E23+D7*D6*D23- |
|
O5*O23-O18 |
D26 |
=(1-D9)*(D7*D6*D23+C7*C6*C23)-D5*D23-D18 |
|
|
O26 |
=(1-O9)*(O7*O6*O23+N7*N6*N23+M7*M6*M23+L7*L6*L23+ |
|
K7*K6*K23+J7*J6*J23+I7*I6*I23+H7*H6*H23+G7*G6*G23+F7*F6*F23+ |
|
E7*E6*E23+D7*D6*D23)-O5*O23-O18 |
D27:O27 |
=(1-D8)*D26 : =(1-O8)*O26 |
D31:O31 |
=D27-D17 : O27-O17 |
D32 |
=D31 |
E32 |
=ЧПС(B30;E31)+D32 |
F32 |
=ЧПС(B30;E31:F31)+D32 |
G32 |
=ЧПС(B30;E31:G31)+D32 |
… |
… |
O32 |
=ЧПС(B30;E31:O31)+D32 |
D33:O33 |
=D32/$B$33 : =O32/$B$33 |
Основная трудность построения шаблона связана с набором рекуррентных выражений в блоках ячеек D23:O23, D25:O25 и D26:O26.
В блоке ячеек D23:O23 вычисляется Qt по следующей формуле:
|
m |
|
|
|
|
|
|
Qt = [Ct + Ht + (1– taxНДС) |
a |
P |
Q |
k |
]/ /{(1– taxt) EVt – [(1– taxНДС) at – taxt ]Pt}. |
||
|
k 1 |
t |
k t |
k t |
|
|
|
В блоке ячеек D25:O25 вычисляется PROFt |
по следующей формуле: |
||||||
|
m |
|
|
|
|
|
|
PROFt = |
(a0 P0 Q0 + |
at |
k Pt |
k Qt |
|
k ) – [ EVt Qt + ECt ]. |
|
|
k |
0 |
|
|
|
|
|
В блоке ячеек D26:O26 вычисляется GPROFt по следующей формуле: |
|||||||
|
|
|
|
m |
|
|
|
GPROFt |
= (1– taxНДС)( a0 P0 Q0 |
+ |
at k Pt |
k Qt k )– [ EVt Qt + ECt ]. |
|||
|
|
|
|
k 0 |
|
|
|
Для упрощения набора соответствующих формул следует воспользоваться инструментом их копирования, однако следует заметить, что в каждую последующую ячейку необходимо вносить дополнения, соответствующие процессу накопления.
Масштабирование в блоке ячеек D33:O33 (в 10 раз) необходимо для одновременного отображения на диаграммах нескольких построенных зависимостей. В данном случае -
22
постоянных затрат (EC), постоянных затрат + помеха (EC + H), критерия чистой приведенной стоимости (NPV). В отсутствия масштабирования графики EC и EC+H не будут заметны на фоне больших значений NPV.
Постройте графики EC , EC+H, NPV / 10. На рис. 5.1 приведены примеры имитационного моделирования управления проектом (выпуска продукции, продажи товаров и услуг) при наличии помех (колебаний постоянных затрат).
|
|
|
Динамика состояния производства - А |
|
|
|||
|
(EC - постоянные затраты, H - помеха, NPV - чист. прив. стоим.) |
|||||||
900 000,00р. |
|
|
|
|
|
|
|
|
800 000,00р. |
|
|
|
|
|
|
|
|
700 000,00р. |
|
|
|
|
|
|
|
EC |
600 000,00р. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EC+H |
|
500 000,00р. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
400 000,00р. |
|
|
|
|
|
|
|
NPV/10 |
300 000,00р. |
|
|
|
|
|
|
|
|
200 000,00р. |
|
|
|
|
|
|
|
|
100 000,00р. |
|
|
|
|
|
|
|
|
0,00р. |
|
|
|
|
|
|
|
|
-100 000,00р. |
Янв |
Фев |
Мар Апр Май Июн Июл |
Авг |
Сен |
Окт |
Ноя |
Дек |
Рис. 5.1.
Случай А (рис. 5.1) иллюстрирует, что даже при значительном, 35%-ом уровне помех, возможно достичь эффективности проекта (по критерию чистой приведенной стоимости) не позже октября, если осуществлять оптимальное управление его состояниями - вложить в проект собственные и заемные средства в течение лишь первых двух месяцев.
Случай Б (рис. 5.2) иллюстрирует, что при изменении лишь одного структурного параметра проекта – коэффициента реинвестиции прибыли в производство всего лишь на 5% (с 52% до 47%) при том же уровне помех проект может оказаться бесперспективным.
|
|
|
Динамика состояния производства - Б |
|
|||
(EC - постоянные затраты, H - помеха, NPV - чист. прив. стоим.) |
|||||||
140 000,00р. |
|
|
|
|
|
|
|
120 000,00р. |
|
|
|
|
|
|
|
100 000,00р. |
|
|
|
|
|
|
EC |
80 000,00р. |
|
|
|
|
|
|
EC+H |
60 000,00р. |
|
|
|
|
|
|
NPV/10 |
40 000,00р. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
20 000,00р. |
|
|
|
|
|
|
|
0,00р. |
|
|
|
|
|
|
|
-20 000,00р. |
Янв |
Фев |
Мар Апр Май Июн Июл Авг |
Сен |
Окт |
Ноя |
Дек |
-40 000,00р. |
|
|
|
|
|
|
|
Рис. 5.2.
23
Лабораторная работа № 6
Анализ чувствительности критериев эффективности.
Электронная таблица Excel позволяет легко решать проблемы перебора вариантов типа «что будет, если». Для этого используются специальные средства – Таблица подстановки, Сценарии.
Пример 6.1. На основании исходных данных инвестиционного проекта проведите анализ чувствительности критерия NPV от влияния следующих параметров: «переменные затраты»; «объем выпуска»; «переменные затраты» и «объем выпуска» с использованием средства
Таблица подстановки.
1. Подготовьте шаблон, согласно таблице 6.1. Присвойте имена ячейкам: B3 – Q, B4 – P, B5 – EV и т.д.
|
|
|
|
|
Таблица 6.1 |
|
|
|
|
|
|
|
A |
B |
C |
|
D |
1 |
Анализ чувствительности критериев эффективности |
|
|||
2 |
|
|
|
|
|
3 |
Объем выпуска, Q |
200,00 |
Начальные инвестиции, I0 |
|
2000,00 |
4 |
Цена за штуку, P |
50,00 |
Постоянные затраты, EC |
|
500,00 |
5 |
Переменные затраты, EV |
30,00 |
Амортизация, A |
|
100,00 |
6 |
Норма дисконта, r |
0,10 |
Остаточная стоимость, S |
|
200,00 |
7 |
Срок проекта, T |
5,00 |
Налог на прибыль, TAX |
|
0,60 |
8 |
|
|
|
|
|
9 |
Чистые платежи, NCF = |
1460,00 |
|
|
NPV = |
10 |
|
|
Значения варьируемого |
|
3658,73 |
|
|
|
параметра (например, EV) |
|
|
Вкачестве постоянного чистого платежа, задайте в ячейке B9 таблицы формулу NCF = (Q
*(P-EV) - EC-A) * (1-TAX) + A. Вычислите в ячейке D10 критерий чистой приведенной стоимости с использованием функции ПС():
NPV = ПC(ставка;кпер;- платежи) + S/(1+r)T - I0 .
После ввода формулы ячейке D10 получится результат NPV = 3658,73.
2. Выберите в качестве параметра, влияние которого необходимо исследовать, показатель «переменные затраты». Заполните столбец (блок ячеек C11:C26) изменяемыми переменными затратами EV = 25, 26, … 40, исчерпав, тем самым, весь диапазон изменений показателя. Выполните следующую последовательность действий.
1.Выделите блок ячеек C10:D26.
2.Выберите из темы Данные главного меню пункт Таблица подстановки. На экране появится окно диалога (рис. 6.1).
|
Таблица подстановки |
|
|
? |
|
||
|
|
|
|
|
|
|
|
|
Подставлять значения по столбцам в: |
|
|
|
|
||
|
|
|
|
|
|||
|
|
|
|
|
|||
|
|
|
|
|
|||
|
Подставлять значения по строкам в: |
|
B5 |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ОК |
|
|
Отмена |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Рис. 6.1.
3. Установите курсор в поле Подставлять значения по строкам в: и ввести имя ячейки,
24
содержащий значения параметра «переменные затраты», на которую ссылается формула NPV в ячейке D10 (например, В5).
4. Закройте окно диалога, нажав кнопку ОК.
В результате напротив каждого значения переменных затрат в ячейках столбца D появятся вычисленные значения NPV, соответствующие данным затратам.
Таблица подстановки является средством автоматизации построения функции одной переменной. Для проведения анализа чувствительности критерия эффективности по другому показателю нужно заменить колонку С его значениями и повторить действия
2. Исследуйте влияние на NPV изменений показателя «объем выпуска» самостоятельно.
3. Проведите одновременный анализ зависимости NPV по двум показателям: от «переменных затрат» и «объема выпуска», формируя функцию двух переменных.
Введите значения «переменных затрат» в ячейках столбца D под ячейкой D10, содержащей формулу NPV, а значения “объем выпуска” в строку 10 справа от ячейки D10.
При вызове диалогового окна, в полях «Подставлять значения по строкам в:» укажите
B5 (имя начальной |
ячейки, на которую ссылается формула ячейки D10) и |
«Подставлять |
значения по столбцам в:», укажите имя начальной ячейки – B3, на которую ссылается |
||
формула ячейки D10. Результатом работы средства Таблица подстановок будет матрица |
||
значений NPV. |
|
|
4. Результаты |
применения средства Таблица подстановок отобразите |
графически с |
помощью Мастера диаграмм Excel (пиктограмма главного меню). На основании построенных диаграмм проанализируйете условия оптимальности исследуемого критерия эффективности инвестирования.
Пример 6.2. На основании исходных данных инвестиционного проекта (используйте шаблон таблицы 6.1) проведите анализ чувствительности критерия NPV с использованием сценариев от влияния параметров, возможные значения которые представлены в таблице 6.2.
Таблица 6.2
Сценарии |
Вероятный - 0,5 |
Наихудший - 0,25 |
Наилучший - 0,25 |
|
|
|
|
Объем выпуска, Q |
200,00 |
150,00 |
300,00 |
Цена за штуку, P |
50,00 |
40,00 |
55,00 |
Переменные затраты, EV |
30,00 |
35,00 |
25,00 |
Норма дисконта, r |
0,10 |
0,15 |
0,08 |
Срок проекта, T |
5,00 |
7,00 |
5,00 |
Первый сценарий.
1.Выделите блок ячеек, которые будут использоваться в качестве изменяемых (в данном примере блок B3:B7).
2.Выберите в главном меню тему Сервис пункт Сценарии. В появившемся диалоговом окне Диспетчер сценариев задайте операцию Добавить. Результатом будет появление диалогового окна Добавление сценария.
3.Введите имя сценария, например Вероятный – 0,5 (см. рис. 6.2). В поле Изменяемые ячейки автоматически будет поставлен выделенный на первом шаге блок. В противном случае
вэто поле необходимо ввести координаты входного блока – B3:B7. Поле Примечание заполняется по усмотрению пользователя.
4.Нажмите кнопку «ОК». На экране появится диалоговое окно Значения ячеек сценария (см. рис. 6.3), содержащее данные выделенного ранее блока B3:B7. Нажмите кнопку «ОК».
Снова появится окно Диспетчер сценариев.
25
|
|
Добавление сценария |
? |
Название сценария: |
|
ОК |
|
|
|
|
|
Вероятный |
|
Отмена |
|
Изменяемые ячейки: |
|
|
|
$B$3:$B$7 |
|
|
|
Чтобы добавить несмежную изменяемую |
|
||
ячейку, укажите ее при нажатой клавише Ctrl. |
|
||
Примечания: |
|
|
|
Автор: A. Krasnov, 19.02.00 |
|
||
Защита |
|
|
|
V |
Закрепить изменения |
Скрыть |
|
|
|
|
|
|
|
Рис. 6.2. |
|
|
|
Значения ячеек сценария |
? |
|
|
|
|
Введите значения каждой изменяемой ячейки. |
ОК |
||
|
|||
|
|
200 |
|
|
Отмена |
|
|
|
|
|
|
||
1: |
Объем |
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
50 |
|
|
|
|
2: |
Цена |
|
|
Добавить |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
3: Перем_затр 30
4: Норма 0,1
5: Срок 5
Рис. 6.3.
Полученные результаты могут быть использованы для проведения дальнейшего анализа – оценки вероятностного распределения значений критерия NPV. В окне Диспетчер сценариев выберите кнопку Отчет, Структура.
В таблице 6.3 показана преобразованная структура: удалены лишние строки и столбцы, изменено имя Структура сценария на Анализ рисков, в блок ячеек B4:D4 введены соответствующие значения априорных вероятностей.
26
|
|
|
|
|
Таблица 6.3 |
|
|
|
|
|
|
|
A |
B |
C |
|
D |
1 |
|
Анализ рисков |
|
||
|
|
|
|||
2 |
|
|
|
|
|
3 |
Сценарии |
Вероятный |
Наихудший |
|
Наилучший |
4 |
Вероятности |
0,5 |
0,25 |
|
0,25 |
5 |
Объем выпуска, Q |
200,00 |
150,00 |
|
300,00 |
6 |
Цена за штуку, P |
50,00 |
40,00 |
|
55,00 |
7 |
Переменные затраты, EV |
30,00 |
35,00 |
|
25,00 |
8 |
Норма дисконта, r |
0,10 |
0,15 |
|
0,08 |
9 |
Срок проекта, T |
5,00 |
7,00 |
|
5,00 |
10 |
|
|
|
|
|
11 |
NPV |
3 658,73 |
-1 259,15 |
|
11 950,89 |
12 |
|
|
|
|
|
13 |
Средняя NPV, M(NPV) |
4502,30 |
|
|
|
|
Квадраты разностей |
711611,20 |
33194293,33 |
|
20270736,42 |
14 |
|
||||
15 |
Стандартное отклонение, сигма |
4673,62 |
|
|
|
16 |
Коэффициент вариации, CV |
1,04 |
|
|
|
17 |
Pr(NPV 0) |
0,17 |
|
|
|
|
|
0,32 |
|
|
|
18 |
Pr(NPV 0,5 Среднее) |
|
|
|
|
19 |
Pr(NPV > максимума) |
0,06 |
|
|
|
|
В таблице 6.3 ячейке B13 присвоено имя «Среднее» и проведены вычисления: |
||||
|
- среднее значение NPV, M(NPV) = СУММПРОИЗВ(B4:D4; B11:D11) |
4502,30; |
|||
-квадраты разностей, = ( NPV - Среднее)^2;
-стандартное отклонение, = КОРЕНЬ(СУММПРОИЗВ(B4:D4;B14:D14);
- коэффициент вариации, CV = /Среднее.
На основании данных вычислений определены вероятности того, что (см. табл.): - NPV будет иметь нулевое или отрицательное значение, т.е.
Pr(NPV |
0) = НОРМРАСПР(0;Среднее;Отклонение;1) 0,17; |
|
- |
NPV будет меньше ожидаемой (средней) на 50%, т.е. |
|
Pr(NPV |
0,5 Среднее) =НОРМРАСПР(0,5*Среднее;Среднее;Отклонение;1) 0,32; |
|
- |
NPV будет больше значения наиболее благоприятного исхода, т.е. |
|
Pr(NPV > максимума) = 1 – НОРМРАСП(D11; Среднее; Отклонение;1) 0,06. |
||
Здесь использовалась статистическая функция, возвращающая значение нормальной функции распределения и имеющая формат
НОРМРАСП (x;среднее;стандартное_отклонение;интегральный), где x – исследуемое значение случайной величины;
среднее – среднее значение случайной величины; стандартное_отклонение - ; интегральный – 0 или 1.
В зависимости от заданного параметра «интегральный» – 0 (ложь) или 1 (истина) – она возвращает плотность распределения вероятности w(x) нормальной случайной величины X или
значение кумулятивной функции распределения вероятности Pr(X x) = x w t dt .
Полученные результаты свидетельствуют о наличии риска для данного проекта.
В случае, если значения 
и CV (стандартного отклонения и коэффициента вариации) по данному проекту меньше, чем у остальных альтернатив, при прочих равных условиях ему следует отдать предпочтение.
27
Лабораторная работа № 7
Анализ рисков инвестиционного проекта методом имитационного моделирования.
Имитационное моделирование инвестиционных рисков с помощью инструмента Генератор случайных чисел.
Задача 7.1. В таблице 7.1 заданы три нормально распределенных ключевых параметра инвестиционного проекта и определены возможные границы их изменений. Прочие параметры проекта считаются постоянными величинами (таблица 7.2).
|
|
|
|
Таблица 7.1 |
Показатели |
|
|
Сценарии |
|
|
Наихудший, |
Вероятный, |
Наилучший, |
|
|
Pr = 0,25 |
Pr = 0,5 |
Pr = 0,25 |
|
Объем выпуска, Q |
150 |
200 |
300 |
|
Цена за штуку, P |
40 |
50 |
55 |
|
Переменные затраты, EV |
35 |
30 |
25 |
|
|
|
|
|
Таблица 7.2 |
Показатели |
|
|
Наиболее вероятные значения |
|
Постоянные затраты, EC |
|
|
500 |
|
Амортизация, A |
|
|
100 |
|
Налог на прибыль, TAX |
|
|
60 % |
|
Норма дисконта, r |
|
|
10 % |
|
Срок проекта, T |
|
|
5 |
|
Начальные инвестиции, I0 |
|
|
2000 |
|
В целях упрощения предполагается, что генерируемый проектом чистый поток платежей NCFt имеет вид аннуитета. Тогда элементы чистого потока платежей для любого периода t одинаковы и определяются из соотношения
NCF = [Q(P - EV) - EC - A](1-TAX) + A.
Необходимо промоделировать различные случаи значений ключевых параметров рассматриваемой модели.
Решение.
Сформируйте шаблон для имитационного моделирования 500 случаев примера. Для удобства выделите в рабочей книге Excel два листа: Имитация и Результаты анализа (щелчок правой кнопкой мышки после наведения курсора на корешок соответствующего листа, выбор подраздела «Переименовать» в появившемся «висящем» меню).
Формирование шаблона начните с листа Результаты анализа (см. таблицу 7.3).
28
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 7.3 |
|
|
|
|
A |
|
|
|
B |
|
C |
|
|
|
D |
|
|
E |
|
|
F |
|
1 |
|
|
Имитационное моделирование (метод Монте-Карло) |
|
||||||||||||||||
|
|
|
|
|||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||
2 |
Нач. инвестиции, Io |
|
2000,00 |
|
Норма, r |
|
0,10 |
|
|
|
|
|
|
|
||||||
3 |
Пост. затраты, |
EC |
|
500,00 |
|
Налог, TAX |
|
0,60 |
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
4 |
Амортизация, |
A |
|
100,00 |
|
Срок, T |
|
|
5,00 |
|
|
|
|
|
|
|
||||
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
|
Показатели: |
|
|
|
Пер-ые, |
|
Объем, |
|
|
|
Цена, |
|
|
Пос-ния, |
|
|
Чист.пр.ст., |
|
|
|
|
|
|
|
|
|
EV |
|
Q |
|
|
|
P |
|
|
NCF |
|
|
NPV |
|
7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8 |
Среднее значение |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
9 |
Станд. откл-ние |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
10 |
Коэфф. вариации |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
11 |
Минимум |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
12 |
Максимум |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
13 |
Число cлуч. NPV <0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
14 |
Сумма убытков |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
15 |
Сумма доходов |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
16 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
17 |
Pr(E<=0) |
|
|
|
ЧИСЛО! |
|
ЧИСЛО! |
|
|
|
ЧИСЛО! |
ЧИСЛО! |
ЧИСЛО! |
|
||||||
|
|
|
|
|
|
|
|
|
||||||||||||
18 |
Pr(E<=МИН(E)) |
|
|
|
ЧИСЛО! |
|
ЧИСЛО! |
|
|
|
ЧИСЛО! |
ЧИСЛО! |
ЧИСЛО! |
|
||||||
|
|
|
|
|
|
|
|
|
||||||||||||
19 |
Pr(M(E)+сигма <= |
|
|
ЧИСЛО! |
|
ЧИСЛО! |
|
|
|
ЧИСЛО! |
|
|
ЧИСЛО! |
|
|
ЧИСЛО! |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||
|
E <= max) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
20 |
Pr(M(E)-сигма <= |
|
|
ЧИСЛО! |
|
ЧИСЛО! |
|
|
|
ЧИСЛО! |
|
|
ЧИСЛО! |
|
|
ЧИСЛО! |
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||
|
E <= M(E)) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
Результаты анализа |
|
|
Лист 2 |
|
Лист 3 |
|
|
|
|
|
|
|
|
|
|
|
||
|
|
Формулы, используемые в таблице 7.3, приведены в таблице 7.4. |
|
|
|
|
||||||||||||||
|
|
Присвоим следующему листу имя Имитация и оформим шаблон, как показано в таблице |
||||||||||||||||||
7.5. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 7.4 |
|
Ячейка |
|
|
|
|
|
|
Формула |
|
|
|
|
|
|
|
||||||
|
B17 |
=НОРМРАСП(0;B8;B9;1) |
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
B18 |
=НОРМРАСП(B11;B8;B9;1) |
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
B19 |
=НОРМРАСП(B12;B8;B9;1) - НОРМРАСП(B8+B9;B8;B9;1) |
|
|
|
|
||||||||||||||
|
B20 |
=НОРМРАСП(B8;B8;B9;1) - НОРМРАСП(B8-B9;B8;B9;1) |
|
|
|
|
||||||||||||||
|
C17 |
=НОРМРАСП(0;C8;C9;1) |
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
C18 |
=НОРМРАСП(C11;C8;C9;1) |
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
C19 |
=НОРМРАСП(C12;C8;C9;1) - НОРМРАСП(C8+C9;C8;C9;1) |
|
|
|
|
||||||||||||||
|
C20 |
=НОРМРАСП(C8;C8;C9;1) - НОРМРАСП(C8-C9;C8;C9;1) |
|
|
|
|
||||||||||||||
|
D17 |
=НОРМРАСП(0;D8;D9;1) |
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
D18 |
=НОРМРАСП(D11;D8;D9;1) |
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
D19 |
=НОРМРАСП(D12;D8;D9;1) - НОРМРАСП(D8+D9;D8;D9;1) |
|
|
|
|
||||||||||||||
|
D20 |
=НОРМРАСП(D8;D8;D9;1) - НОРМРАСП(D8-D9;D8;D9;1) |
|
|
|
|
||||||||||||||
|
E17 |
=НОРМРАСП(0;E8;E9;1) |
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
E18 |
=НОРМРАСП(E11;E8;E9;1) |
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
E19 |
=НОРМРАСП(E12;E8;E9;1) - НОРМРАСП(E8+E9;E8;E9;1) |
|
|
|
|
||||||||||||||
|
E20 |
=НОРМРАСП(E8;E8;E9;1) - НОРМРАСП(E8-E9;E8;E9;1) |
|
|
|
|
||||||||||||||
|
F17 |
=НОРМРАСП(0;F8;F9;1) |
|
|
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
29 |
|
|
|
|
|
|
|
|
|
|
|
F18 |
=НОРМРАСП(F11;F8;F9;1) |
F19 |
=НОРМРАСП(F12;F8;F9;1) - НОРМРАСП(F8+F9;F8;F9;1) |
F20 |
=НОРМРАСП(F8;F8;F9;1) - НОРМРАСП(F8-F9;F8;F9;1) |
Первая часть шаблона (блок ячеек A1 : E10) предназначена для ввода исходных данных таблицы 7.1. Формулы расчета параметров нормальных распределений - математических ожиданий (средних) и стандартных отклонений, используемые в блоках ячеек B7 : D8 таблицы 4 показаны в таблице 7.5.
Для расчета стандартных отклонений используются формулы-массивы (комбинация клавиш [Ctrl+Shift+Enter]), для ячеек D7 : B8 и блока ячеек C7 : D8.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 7.5 |
|
|
|
|
|
A |
|
|
B |
|
|
C |
|
D |
|
|
E |
|
1 |
|
|
|
|
|
|
Исходные условия эксперимента |
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
Переменные, |
|
|
Объем, |
|
Цена, |
|
|
Вероятности, |
|
|
|
|
|
|
|
|
EV |
|
|
Q |
|
P |
|
|
Pr |
|
3 |
|
Наихудший |
|
35,00 |
|
150,00 |
|
40,00 |
|
0,25 |
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
4 |
|
Вероятный |
|
30,00 |
|
200,00 |
|
50,00 |
|
0,5 |
|
|||||
5 |
|
Наилучший |
|
25,00 |
|
300,00 |
|
55,00 |
|
0,25 |
|
|||||
6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
|
Среднее |
|
30,00 |
|
212,50 |
|
48,75 |
|
|
|
|
||||
|
|
значение |
|
|
|
|
|
|
|
|
|
|
|
|||
8 |
|
Станд.отклон. |
|
3,54 |
|
54,49 |
|
5,45 |
|
|
|
|
||||
9 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
10 |
|
Эксп-нтов = |
|
500 |
|
|
|
|
Номер строки = |
|
512 |
|
||||
11 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
12 |
|
|
Перем-ные, |
|
Объем, Q |
|
|
Цена, P |
|
Поступления, |
|
|
Чист. пр. ст., |
|
||
|
|
|
|
EV |
|
|
|
|
|
|
NCF |
|
|
NPV |
|
|
13 |
|
|
|
|
|
|
|
|
|
|
|
-140,00 |
|
-2530,71 |
|
|
|
|
|
Результаты |
|
|
Имитация |
|
|
Лист 3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||
|
|
|
анализа |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 7.6 |
|
Ячейка |
|
|
|
|
Формула |
|
|
|
|
|||||||
|
B7 |
|
=СУММПРОИЗВ(B3:B5;$E$3:$E$5) |
|
|
|
|
|
||||||||
|
B8 |
|
{=КОРЕНЬ(СУММПРОИЗВ((B3:B5-B7)^2;$E$3:$E$5))} |
|
|
|
|
|||||||||
|
С7 |
|
=СУММПРОИЗВ(C3:C5;$E$3:$E$5) |
|
|
|
|
|
||||||||
|
С8 |
|
{=КОРЕНЬ(СУММПРОИЗВ((C3:C5-C7)^2;$E$3:$E$5))} |
|
|
|
|
|||||||||
|
D7 |
|
=СУММПРОИЗВ(D3:D5;$E$3:$E$5) |
|
|
|
|
|
||||||||
|
D8 |
|
{=КОРЕНЬ(СУММПРОИЗВ((D3:D5-D7)^2;$E$3:$E$5))} |
|
|
|
|
|||||||||
E10 |
|
=B10+13-1 |
|
|
|
|
|
|
|
|
|
|
||||
D13 |
=(B13*(C13 - A13) – EC – А) * (1 - TAX ) + A |
|
|
|
|
|
||||||||||
E13 |
|
=ПC(r;T;-NCF) – I0 |
|
|
|
|
|
|
|
|
|
|||||
Формула в ячейке E10 по заданному в ячейке B10 числу имитаций вычисляет номер последней строки для блоков, в которых будут храниться сгенерированные значения ключевых параметров.
Перейдите к проведению имитационного эксперимента, предварительно установив курсор
вячейку A13.
1.Выберите раздел Анализ данных темы Сервис главного меню. Появляется диалоговое окно Анализ данных со списком «Инструменты анализа».
30