Використання інструменту «Генератор случайных чисел»

Цей інструмент призначений для автоматичної генерації множини даних (генеральної сукупності) заданого обсягу, елементи якої характеризуються певним розподілом ймовірностей. Можна використовувати сім типів розподілу: рівномірне, нормальне, Бернуллі, Пуассона, біноміальне, модельне та дискретне. Застосування інструменту «Генератор случайных чисел» вимагає встановлення спеціального доповнення «Пакет анализа».

Змінимо умови прикладу, визначивши ймовірності для кожного сценарію розвитку подій, як вказано у табл. 8. Будемо вважати, що ключові змінні мають нормальний розподіл. Кількість імітацій залишимо без змін: 500.

Таблиця 8. Ймовірнісні сценарії реалізації проекту

Показники	Сценарій
	Найгірший P = 0.25	Найкращий P = 0.25	Ймовірний P = 0.5
Обсяг випуску - Q	150	300	200
Ціна за штуку - P	40	55	50
Змінні витрати - V	35	25	30

Приступимо до формування шаблона. Виділимо в робочій книзі два аркуші: «Имитация» и «Результаты анализа». Формування шаблона доцільно почати з аркуша «Результаты анализа» (рис. 8.).

Як випливає з рис. 8, цей аркуш практично відповідає раніше розробленому для рішення попереднього завдання (рис. 2). Відмінність становлять лише формули для розрахунку ймовірностей, які наведені в табл. 9. Власні імена осередків також узяті з аналогічного аркуша попереднього шаблона (табл. 7). Щоб швидко сформувати новий аркуш "Результаты анализа" виконайте такі дії:

1. завантажте шаблон SIMUL_1.XLT і збережіть його під іншим іменем, наприклад - SIMUL_2.XLT;

2. видаліть аркуш "Имитация", для цього встановите курсор миші на ярличок цього аркуша, натисніть праву кнопку; з’явиться контекстне меню, виберіть операцію "Удалить";

3. перейдіть на аркуш "Результаты анализа", видаліть рядки 17-18, відкоригуйте заголовок таблиці;

4. додайте формули з табл. 9, записавши їх до комірок блоку В17:В20, скопіюйте формули до блоку С17:F20, ведіть відповідні коментарі;

5. порівняйте отриману таблицю з рис. 8.

Перейдіть до наступного аркуша, надайте йому ім'я - "Имитация". Приступаємо до його формування (рис. 9).

Рис. 8 — Аркуш «Результаты анализа»

Таблиця 9. Формули аркуша "Результаты анализа"

Осередок	Формула
В17	=НОРМРАСП(0;B8;B9;1)
В18	=НОРМРАСП(B11;B8;B9;1)
В19	=НОРМРАСП(B12;B8;B9;1) -НОРМРАСП(B8+B9;B8;B9;1)
В20	=НОРМРАСП(B8;B8;B9;1) -НОРМРАСП(B8-B9;B8;B9;1)
З17	=НОРМРАСП(0;C8;C9;1)
З18	=НОРМРАСП(C11;C8;C9;1)
З19	=НОРМРАСП(C12;C8;C9;1) -НОРМРАСП(C8+C9;C8;C9;1)
З20	=НОРМРАСП(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)
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)

Рис. 9 — Аркуш "Имитация"

Перша частина цього аркуша (блок А1:Е10) призначена для введення вихідних даних і розрахунку параметрів їх розподілу. Нагадаємо, що нормальний розподіл випадкової величини характеризується двома параметрами — математичним очікуванням (середнім) і стандартним відхиленням. Формули розрахунку вказаних параметрів для ключових змінних моделі задані в блоках В7:D7 та B8:D8 (див. табл. 11). Для зручності визначення формул задайте блоку Е3:Е5 ім'я "Ймовірності" (табл. 10).

Таблиця 10. Імена осередків аркуша "Имитация" (шаблон II)

Адреса осередку	Ім'я	Коментарі
Блок Е3:Е5	Вероятности	Ймовірність значення параметра
Блок A13:A512	Перем_расх	Змінні витрати
Блок B13:B512	Количество	Обсяг випуску
Блок C13:C512	Цена	Ціна виробу
Блок D13:D512	Поступления	Надходження від проекту NCF
Блок E13:E512	ЧСС	Чиста сучасна вартість NPV

Таблиця 11. Формули аркуша "Имитация" (шаблон II)

Осередок	Формула
В7	=СУММПРОИЗВ(B3:B5; Вероятности)
В8	{=КОРЕНЬ(СУММПРОИЗВ((B3:B5 - B7)^2; Вероятности))}
З7	=СУММПРОИЗВ(C3:C5; Вероятности)
З8	{=КОРЕНЬ(СУММПРОИЗВ((C3:C5 - C7)^2; Вероятности))}
D7	=СУММПРОИЗВ(D3:D5; Вероятности)
D8	{=КОРЕНЬ(СУММПРОИЗВ((D3:D5 - D7)^2; Вероятности))}
E10	=B10+13 –1
D13	=СУММПРОИЗВ(B3:B5; Вероятности)
E13	{=КОРЕНЬ(СУММПРОИЗВ((B3:B5 - B7)^2; Вероятности))}

Зверніть увагу, що для розрахунку стандартних відхилень використовуються формули–масиви. Для формування блоку формул треба визначити їх для блоку В7:В8 і скопіювати в блок С7:D8.

Формула в Е10 за заданим числом імітацій (комірка В10) знаходить номер останнього рядка для блоків, де будуть зберігатися згенеровані значення ключових змінних. Комірки D13:E13 містять уже знайомі нам формули для розрахунку величини потоку платежів NCF і його чистої сучасної вартості NPV.

Сформуйте елементи оформлення аркуша "Имитация", визначте необхідні імена для блоків (див. табл. 10) і задайте необхідні формули (табл. 11). Звірте отриману таблицю з рис. 9. Збережіть отриманий шаблон під ім'ям SIMUL_2.XLT.

Введіть вихідні значення постійних змінних (табл. 2) в комірки В2:В4 та D2:D4 аркушу "Результаты анализа". Перейдіть до аркушу "Имитация". Введіть значення ключових змінних та відповідні ймовірності (табл. 8). В результаті ви маєте отримати таблицю, що показана на рис. 10.

Рис. 10 — Аркуш "Имитация" після введення даних

Встановіть курсор на комірку А13. Приступимо до проведення імітаційного експерименту.

1. Виберіть з меню "Сервис\Анализ данных". З’явиться діалогове вікно "Анализ данных", що містить список інструментів аналізу.

2. Виберіть зі списку пункт "Генерация случайных чисел" і натисніть "ОК" (рис. 11).

З'явиться діалогове вікно "Генерация случайных чисел". Вкажіть у списку "Распределения" тип "Нормальное". Заповніть інші поля у вікні згідно рис. 12 і натисніть кнопку "ОК". Комірки блоку А13:А512 будуть заповнені випадковими значеннями.

Рис. 11 — Вибір інструмента "Генерация случайных чисел"

Рис. 12 — Заповнення полів вікна "Генерация случайных чисел"

Наведемо необхідні пояснення. Першим аргументом діалогового вікна "Генерация случайных чисел" є поле "Число переменных". Воно задає кількість стовпчиків таблиці, в яких будуть розміщуватися згенеровані ймовірнісні випадкові величини. У нашому прикладі аргумент "Число переменных" дорівнює 1, адже ми відвели під значення змінної V (змінні витрати) один стовпчик – "А". Якщо вказати параметр більше за 1, випадкові величини будуть розміщені у відповідній кількості сусідніх стовпчиків, починаючи з активного осередку. Якщо параметр не вказувати , будуть заповнені всі стовпчики у вихідному діапазоні.

Наступним обов'язковим аргументом є "Число случайных чисел" (кількість імітацій). За умовами прикладу воно має дорівнювати 500 (рис. 12). Excel автоматично підраховує необхідну кількість осередків для зберігання генеральної сукупності.

Вид розподілу треба вибрати зі списку "Распределения". Тут можна отримати 7 найпоширеніших типів розподілу. Кожний з них характеризується власними параметрами. Обраний тип розподілу визначає зовнішній вигляд діалогового вікна. У нашому прикладі вибираємо тип розподілу "Нормальное". Вводимо його параметри "Среднее" та "Стандартное отклонение". Ці параметри для досліджуваної змінної V ми визначили раніше в осередках В7 та В8 аркушу "Имитация". На жаль, ці аргументи можуть бути задані тільки у вигляді констант. Вказувати адреси осередків і власних імен не допускається!

Зазначення аргументу "Случайное рассеивание" дозволяє при декількох запусках генератора одержувати однакові послідовності випадкових величин. Таким чином можна одержати однакову генеральну сукупність випадкових чисел декілька разів. Це значно підвищує ефективність аналізу (зрівняйте з попереднім шаблоном!). Якщо цей аргумент не заданий або дорівнює 0, кожний наступний запуск генератора дасть нову генеральну сукупність. У нашому прикладі вказуємо 1. Це дозволить оперувати з однією і тією ж генеральною сукупністю та уникнути постійних перерахунків таблиці.

Останній аргумент діалогового вікна "Генерация случайных чисел" - "Параметры вывода" визначає місце розташування результатів (задається шляхом відповідним прапорцем). Excel пропонує три варіанти:

• вихідний блок осередків на поточному аркуші — потрібно вказати адресу лівого верхнього осередку вихідного блоку; розмір блоку буде визначено автоматично;

• новий робочий аркуш — у робочій книзі буде відкрито новий аркуш, що містить результати генерації випадкових величин, починаючи з осередку A1;

• нова робоча книга — буде відкрита нова книга з результатами імітації на першому аркуші.

У нашому прикладі для проведення подальшого аналізу необхідно, щоб випадкові величини розміщувалися в спеціально відведених для них блоках (табл. 10). Так, для зберігання 500 значень першої змінної раніше було відведено блок А13:А512. Цей блок має власне ім'я — "Перем_расх", тому воно вказано як вихідний діапазон. Зазначимо, що при збільшенні або зменшенні кількості імітацій необхідно перевизначити вихідні блоки , в яких зберігаються результати.

Генерація значень змінних Q і Р здійснюється аналогічно. Приклад заповнення вікна "Генерация случайных чисел" для змінної Q (кількість) наведений на рис. 13.

Для одержання генеральної сукупності значень потоку платежів та їх чистої теперішньої вартості треба скопіювати формули базового рядка (комірки D13:E13) 499 разів. Щоб полегшити процес копіювання великого діапазону значень, виконайте такі дії.

• Скопіюйте до буферу комірку D13.

• Натисніть клавішу [F5]. На екрані з'явиться діалогове вікно "Переход".

• Вкажіть у полі "Ссылка" ім'я блоку "Поступления" і натисніть кнопку "ОК". Блок буде виділено.

• Натисніть клавішу ENTER.

• Якщо було встановлено режим ручних обчислень, натисніть клавішу F9 для перерахування таблиці.

Рис — 13. Заповнення полів вікна для змінної Q

Аналогічно скопіюйте формули з Е13. При цьому в полі "Ссылка" діалогового вікна "Переход" вкажіть ім'я блоку — "ЧСС". Ви також можете вибрати необхідне ім'я зі списку "Перейти к".

Результати рішення для нашого прикладу наведені на рис. 14 , 15.

За результатами проведеного імітаційного експерименту видно, що величина очікуваної NPV дорівнює 3412,14 при стандартному відхиленні 2556,83. Коефіцієнт варіації 0,75 менше за 1, тобто ризик проекту в цілому незначний і знаходиться у допустимих межах середнього ризику інвестиційного портфелю фірми. Результати ймовірнісного аналізу показують, що шанс одержати негативне значення NPV не перевищує 9%. Загальне число негативних значень NPV у вибірці становить 32 з 500. Тобто з ймовірністю близько 91% можна стверджувати, що чиста реальна вартість проекту буде позитивною 0. При цьому ймовірність того, що величина NPV виявиться більше за М(NPV) + , дорівнює 16% (осередок F19). Ймовірність влучення значення NPV в інтервал [М(NPV) - ; М(NPV)] дорівнює 34%.

Рис. 14 — Результати імітаційного експерименту (шаблон II)