Як створити розкривні сценарії у вашій фінансовій моделі

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

Розкривні списки перевірки даних використовуються для різних цілей у фінансовому моделюванні, включаючи аналіз сценаріїв.

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

Завантажте файл 0801.xlsx . Відкрийте його та виберіть вкладку з позначкою 8-1-початок.

Як це було змодельовано, вхідні дані вибудовуються в стовпці B. Ви можете виконати аналіз чутливості, просто змінивши один із вхідних даних — наприклад, змініть кількість клієнтів на оператора виклику в клітинці B3 з 40 на 45, і ви побачити, як змінюються всі залежні числа. Це буде аналіз чутливості, оскільки ви змінюєте лише одну змінну. Натомість у цій повній вправі з аналізу сценарію ви збираєтеся змінити кілька змінних одночасно, тому вам потрібно буде зробити більше, ніж налаштувати кілька чисел вручну.

Щоб виконати аналіз сценарію за допомогою розкривних списків перевірки даних, виконайте такі дії:

Візьміть завантажену модель, виріжте та вставте описи з стовпця C в стовпець F. Ви можете зробити це, виділивши клітинки C6:C8, натиснувши Ctrl+X, вибравши клітинку F6 і натиснувши Enter.

Вхідні дані в клітинках B3–B8 є активним діапазоном, який керує моделлю і залишиться таким. Однак вони мають стати формулами, які змінюються залежно від створеного вами спадного списку.

Скопіюйте діапазон у стовпці B у стовпці C, D та E.

Ви можете зробити це, виділивши B3:B8, натиснувши Ctrl+C, виділивши клітинки C3:E3 і натиснувши Enter. Ці суми будуть однаковими для кожного сценарію, доки ви їх не зміните.

У рядку-введіть назви найкращого випадок , базовий випадок , і найгірший випадок.

Налаштування моделі для аналізу сценаріїв.

Зверніть увагу, що формули все ще пов’язуються з вводами в стовпці B, як ви можете побачити, вибравши клітинку C12 і натиснувши клавішу F2.

Відредагуйте вхідні дані під кожним сценарієм.

Ви можете вказати все, що вважаєте ймовірним, але щоб зіставити числа з цими в цьому прикладі, введіть значення. Наразі проігноруйте стовпець B.

Вхідні дані для аналізу сценаріїв.

Тепер вам потрібно додати спадне вікно вгорі, яке буде керувати вашими сценаріями. Насправді не має значення, куди саме ви помістили спадне вікно, але воно має бути в місці, яке легко знайти, зазвичай у верхній частині сторінки.

У клітинку E1 введіть назву Сценарій .

Виберіть клітинку F1 та змініть форматування на введення, щоб користувач міг бачити, що цю клітинку можна редагувати.

Найпростіший спосіб зробити це – виконати такі дії:

Клацніть одну з клітинок, які вже відформатовані як вхідні дані, наприклад клітинку E3.

Натисніть піктограму Format Painter в розділі буфера обміну зліва на вкладці «Головна». Ваш курсор зміниться на пензлик.

Виберіть клітинку F1, щоб вставити форматування.

Format Painter зазвичай призначений для одноразового використання. Після того, як ви виділите клітинку, пензлик зникне з курсору. Якщо ви хочете, щоб «Маляр форматів» став «липким» і застосовувався до кількох осередків, двічі клацніть піктограму, вибравши її на вкладці «Головна».

Тепер у клітинці F1 виберіть Перевірка даних у розділі Інструменти даних на вкладці Дані.

З’явиться діалогове вікно Перевірка даних.

На вкладці Налаштування змініть спадне меню Дозволити на Список, за допомогою миші виберіть діапазон =$C$2:$E$2 і натисніть OK.

Створення розкривних сценаріїв перевірки даних.

Клацніть спадне меню, яке тепер з’являється поруч із коміркою F1, і виберіть один із сценаріїв (наприклад, базовий варіант).

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

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

Виділіть клітинки B3:B8 і виберіть Колір заливки в групі «Шрифт» на вкладці «Головна».

Змініть колір заливки на білий фон.

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

Тепер вам потрібно замінити жорстко запрограмовані значення в стовпці B на формули, які будуть змінюватися у міру зміни спадного списку. Ви можете зробити це за допомогою кількох різних функцій; HLOOKUP, вкладений оператор IF, IFS і SUMIF – все це зробить свою справу. Додайте формули, виконавши такі дії:

Виберіть клітинку B3 і додайте формулу, яка змінить значення залежно від того, що знаходиться в клітинці F1.

Ось якою буде формула для різних варіантів:

• =HLOOKUP($F$1,$C$2:$E$8,2,0)

  Зауважте, що за допомогою цього рішення вам потрібно змінити номер індексу рядка з 2 на 3 і так далі, коли ви копіюєте формулу вниз. Замість цього ви можете використовувати функцію ROW у третьому полі, наприклад: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

• =IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SUMIF($C$2:$E$2,$F$1,C3:E3)

  Як завжди, на вибір є кілька різних варіантів, і найкращим рішенням є те, яке є найпростішим і найлегшим для розуміння. Будь-яка з цих функцій дасть точно такий же результат, але необхідність змінити номер індексу рядка в HLOOKUP не є надійною, і додавання ROW може заплутати користувача. Вкладений оператор IF складний для створення та виконання, і хоча нова функція IFS розроблена для спрощення вкладеної функції IF, вона все ще досить громіздка. SUMIF досить просто побудувати та слідувати, і його легко розширити, якщо вам знадобиться додати додаткові сценарії в майбутньому.

  Зверніть увагу, що IFS – це нова функція, яка доступна лише з установленими Office 365 та Excel 2016 або новішої версії. Якщо ви використовуєте цю функцію, і хтось відкриє цю модель у попередній версії Excel, вона зможе переглянути формулу, але не зможе її редагувати.

Скопіюйте формулу в клітинку B3 вниз по стовпцю.

Завершений аналіз сценарію.

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

Тепер найцікавіше! Настав час перевірити функціональність сценарію в моделі.

Клацніть клітинку F1, змініть спадне вікно та подивіться, як змінюються вихідні дані моделі під час перемикання між різними сценаріями.