Розподіл ймовірностей потоку платежів

Рік 1	Рік 2	Рік 3
	0,3		0,2		0,3
	0,4		0,6		0,4
	0,3		0,2		0,3

 

ЕТ із розрахунками для даного прикладу, яку необхідно розробити самостійно, керуючись табл. 5.7, наведена на рис. 5.14.

Таблиця 5.7

Формули таблиці (рис. 5.14)

Комірка	Формула
В11	=SUMPRODUCT(B7:B9;C7:C9)
В12	=SUMPRODUCT((B7:B9-B11)^2;C7:C9)
D11	=SUMPRODUCT(D7:D9;E7:E9)
D12	=SUMPRODUCT((D7:D9-D11)^2;E7:E9)
F11	=SUMPRODUCT(F7:F9;G7:G9)
F12	=SUMPRODUCT((F7:F9-F11)^2;G7:G9)
B14	=NPV(B3;B11;D11;F11)
B15	=B14-B2
B16	=SQRT((B12/(1+B3)^(2*B5))+(D12/(1+B3)^(2*D5))+(F12/(1+B3)^(2*F5)))
B18	=NORM.DIST(0;B15;B16;1)

 

Рис. 5.14. Розрахунок прикладу 5.5

Зверніть увагу на спосіб задання формул у комірках B12, D12, F12, що обчислюють дисперсію. Вони задані у вигляді формул масиву. Ознакою подібних формул служать фігурні дужки. На відміну від звичайних формули масиву можуть видавати одразу декілька значень. Розглянемо механізм роботи формули масиву для обчислення дисперсії у комірці В12.

Для визначення дисперсії спочатку необхідно обчислити різниці квадратів відхилень від середнього значення. Таким чином, при використанні традиційного підходу довелося б визначити чотири додаткові формули: =(B7-B11)^2, = (B8-B11)^2, =(B9-B11)^2 – для обчислення квадратів відхилень і функцію SUMPRODUCT () – для обчислення суми добутків отриманих відхилень на ймовірності. У даному випадку дії цих чотирьох формул виконуються однією, оскільки вираз (B7:B9-B11)^2 у формулі із комірки В12 повертає не одне значення, а масив із трьох значень (тобто масив різниць квадратів відхилень), які потім перемножуються на відповідні ймовірності (блок С7:С9) та підсумовуються.

Задання формул масиву у ППП Excel має свої особливості.

Формула набирається у рядку вводу звичайним способом, після чого натискається не клавіша ENTER, а комбінація клавіш CTRL+SHIFT+ENTER. При цьому фігурні дужки Excel додасть автоматично. Таким чином, для задання формули масиву у комірці В12 необхідно виконати такі дії:

1. Набрати у рядку вводу: SUMPRODUCT((B7:B9-B11)^2;C7:C9).

2. Натиснути комбінацію клавіш CTRL+SHIFT+ENTER.

Формули масиву – один із потужних та ефективних засобів автоматизації обчислень. У наступному прикладі, використовуючи формули масиву, ми ще більше скоротимо кількість проміжних обчислень (див. формули комірок B12, D12, F12 табл. 5.8). разом з тим їх застосування потребує глибокого розуміння сутності масивів ППП Excel та може викликати певні труднощі у початківців.

Визначивши очікуване значення NPV (2475,06) і величину стандартного відхилення (2257,27), можна провести аналіз ймовірнісного розподілу майбутнього доходу, виходячи із припущення про його нормальний розподіл. Для цього можна скористатися вже відомою для нас функцією NORM.DIST ().

Визначимо ймовірність того, що величина NPV для проекту буде менша або рівна 0.

=NORM.DIST(0; 2475,06; 2257,27 ;1) (Результат: 0,14).

Відповідно ймовірність отримання позитивного значення NPV буде дорівнювати: 1-0,14=0,86 або 86%.

Аналогічно можуть бути визначені ймовірності отримання інших значень NPV.