Як використовувати диспетчер сценаріїв для моделювання розрахунків позики

Менеджер сценаріїв згрупований разом із пошуком цілі та таблицями даних у розділі «Аналіз що-якщо» на вкладці «Дані». Згрупування з іншими інструментами, які настільки корисні, приведе вас до думки, що Менеджер сценаріїв також є важливим інструментом, який потрібно знати. Однак, незважаючи на свою корисну назву та хорошу компанію, яку він тримає, Scenario Manager досить обмежений у своїй функціональності і настільки корисний, як випливає з назви! Тому експертами, які займаються моделюванням фінансів, його не часто використовують.

Налаштування моделі

Щоб продемонструвати, як користуватися Scenario Manager, давайте застосуємо його до простої моделі розрахунку позики. Теорія розрахунку позики досить складна, але, на щастя, Excel легко справляється з кредитами.

У наведеному нижче прикладі ви побачите калькулятор процентної ставки, за допомогою якого можна перевірити чутливість щомісячних виплат до змін процентних ставок та умов кредиту. Виконайте такі дії:

Завантажте файл 0801.xlsx , відкрийте його та виберіть вкладку з позначкою 8-14 або просто налаштуйте модель із жорстко запрограмованими припущеннями.

Налаштування функції PMT для розрахунку щомісячних виплат по кредиту.

У комірці B11 введіть =PMT( і натисніть Ctrl+A.

З'явиться діалогове вікно Аргументи функції.

Функція PMT вимагає введення наступних даних:

• Ставка: відсоткова ставка.
• Nper: кількість періодів протягом терміну дії позики.
• Pv: Поточна вартість позики (сума позики).
• Fv: сума, що залишилася наприкінці періоду позики. (У більшості випадків ви хочете повернути всю суму протягом періоду позики, тому ви можете залишити це поле порожнім.)
• Тип: ви хочете, щоб виплати відбувалися на початку або в кінці періоду. (Ви можете залишити це поле порожнім для цілей цієї вправи.)

Зв’яжіть поля діалогового вікна «Аргументи функції» з вхідними параметрами у вашій моделі.

Функція PMT повертає річну суму погашення. Оскільки ви хочете розрахувати суму щомісячного погашення, ви можете просто розділити всю формулу на 12, але оскільки відсотки наростають, точніше розділити кожне поле на 12 у формулі. Отже, ставка в першому полі перетворюється на місячну ставку, а кількість періодів у другому полі також перетворюється на місячну ставку.

Натисніть OK.

Формула =PMT(B7/12,B9*12,B5).

Ця функція повертає від’ємне значення, оскільки це витрати. Для ваших цілей змініть його на позитивне, поставивши перед функцією знак мінус.

Застосування менеджера сценаріїв

Тепер ви можете використовувати Диспетчер сценаріїв, щоб додати деякі сценарії. Ви хочете знати, як зміни внесених даних впливають на ваші щомісячні виплати. Виконайте такі дії:

На вкладці «Дані» в розділі «Прогноз» на стрічці клацніть піктограму «Аналіз «Що якщо» та виберіть Диспетчер сценаріїв зі спадного списку.

З’явиться діалогове вікно Диспетчер сценаріїв.

Натисніть кнопку Додати, щоб створити новий сценарій.

З’явиться діалогове вікно Додати сценарій.

Побудова сценарію за допомогою диспетчера сценаріїв.

Введіть назву для першого сценарію в поле Ім’я сценарію (наприклад, Сценарій 1).

Введіть посилання на клітинки для змінних клітинок у полі «Зміна клітинок».

Розділяйте кожне посилання комою (якщо їх більше одного), але не використовуйте пробіли. Ви також можете, утримуючи клавішу Ctrl, клацнути кожну клітинку в електронній таблиці, щоб вставити посилання в поле.

Натисніть OK.

З’явиться діалогове вікно «Значення сценарію» з наявними значеннями (0,045 для процентної ставки та 25 для років).

Натисніть OK, щоб прийняти ці значення як перший сценарій.

Натисніть Додати, щоб додати інший сценарій.

Знову з’явиться діалогове вікно Додати сценарій.

Введіть назву для другого сценарію в поле Ім’я сценарію (наприклад, Сценарій другий).

Натисніть OK.

Знову з’явиться діалогове вікно «Значення сценарію».

Введіть значення змінних для цього сценарію (наприклад, 0,05 для процентної ставки та 30 для років).

Введення значень сценарію за допомогою диспетчера сценаріїв.

Натисніть OK.

Ви повернетеся до діалогового вікна Менеджера сценаріїв.

Виконайте кроки з 7 по 9 ще раз, щоб створити додаткові сценарії.

Після створення всіх сценаріїв ви можете використовувати Менеджер сценаріїв для перегляду кожного сценарію, натиснувши кнопку Показати внизу.

Вхідні дані автоматично змінюються, щоб відобразити сценарії.

Сценарії є специфічними для аркуша, тобто вони існують лише на аркуші, де ви їх створили. Отже, коли ви шукаєте сценарії, які ви створили, ви повинні вибрати правильний аркуш у моделі.