Побудова сценаріїв у вашій фінансовій моделі

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

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

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

Введення ваших припущень сценарію

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

У верхній частині аркуша «Припущення» введіть припущення для введення сценарію.

Вхідні припущення для сценарію.

Створення випадаючого списку

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

Виконайте такі дії:

Перейдіть до робочого аркуша IS Cash Flow і виберіть клітинку B1.

Виберіть Перевірка даних у розділі Інструменти даних на стрічці даних.

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

У спадному списку Дозволити виберіть Список.

Ви можете ввести слова «Найкращий», «Базовий» та «Гірший» безпосередньо в поле, але краще пов’язати його з джерелом, якщо ви введете значення з помилкою.

У полі Джерело введіть =, а потім клацніть робочий аркуш Припущення та виділіть назви сценаріїв Найгірший, Базовий, Найкращий.

Тепер ваша формула в полі Джерело має бути =Припущення!$B$2:$D$2.

Натисніть OK.

Поверніться до клітинки B1 на робочому аркуші IS Cash Flow і перевірте, що спадне вікно працює належним чином і надає параметри «Найкращий», «Базовий» і «Гірший».

Наразі встановіть спадне вікно на Base.

Побудова функціональності сценарію

Вам потрібно відредагувати свої вхідні припущення щодо кількості проданих чашок за день та щомісячної орендної плати, щоб у міру того, як спадне вікно на робочому аркуші IS Cash Flow змінювалося, вхідні припущення змінилися на відповідний сценарій. Наприклад, якщо на аркуші «Грошовий потік IS» вибрано «Найкраще», значення в клітинці B9 на аркуші «Припущення» має становити 140, а значення в клітинці B23 – 1080 доларів США. Це потрібно зробити за допомогою формули.

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

У цьому випадку можна використовувати кілька варіантів: HLOOKUP, SUMIF або IF. Оператор IF, будучи вкладеною функцією, є найскладнішим у побудові та менш масштабованим. Якщо кількість варіантів сценарію збільшується, варіант оператора IF буде складніше розширити. У цьому випадку я вирішив використовувати HLOOKUP з цими кроками.

Виконайте такі дії:

Виберіть клітинку B9 і натисніть кнопку Вставити функцію на вкладці Формули або поруч із рядком формул.

Знайдіть HLOOKUP, натисніть Go і натисніть OK.

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

Клацніть поле Lookup_value та виберіть спадне вікно на робочому аркуші IS Cash Flow.

Це критерій, який керує HLOOKUP.

Натисніть F4, щоб заблокувати посилання на клітинку.

У полі Table_array вам потрібно ввести масив, який ви використовуєте для HLOOKUP. Зауважте, що ваші критерії мають відображатися у верхній частині діапазону.

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

Посилання на клітинки зміняться на $B$2:$D$4.

У полі Row_index_num введіть номер рядка 2.

У полі Range_lookup введіть нуль або false, оскільки ви шукаєте точну відповідність.

Переконайтеся, що ваше діалогове вікно виглядає так само, як на зображенні нижче.

Натисніть OK.

Формула в комірці B9: =HLOOKUP('IS Cash Flow'!B1,$B$2:$D$4,2,0) з результатом обчислення 120.

Виконайте ту ж дію в клітинці B23 за формулою =HLOOKUP('IS Cash Flow'!$B$1,$B$2:$D$4,3,0) .

Замість того, щоб знову створювати всю формулу заново, просто скопіюйте формулу з клітинки B9 в клітинку B23 і змініть посилання на рядок з 2 на 3. Копіювання клітинки змінить форматування числа, тому вам потрібно буде змінити валюту символ знову до $.

Поверніться до робочого аркуша IS Cash Flow і змініть спадне меню на Найкраще.

Переконайтеся, що ваші припущення щодо середньої кількості проданих чашок за день та місячної орендної плати на робочому аркуші «Припущення» відповідно змінилися. Чашки будуть змінені на 140, а оренда - на 1080 доларів.

Тепер важливий тест – перевірити, чи баланс досі збалансований!

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

Перевірте спадне меню ще раз, змінивши його на Найгірший.
Чашки будуть змінені на 100, а оренда складе 1320 доларів. Перевірте перевірку помилок на робочому листі балансу ще раз.

Побудова сценарію за допомогою HLOOKUP.

Вітаю! Ваша повністю інтегрована фінансова модель разом із аналізом сценаріїв готова! Ви можете завантажити копію готової моделі у файлі 1002.xlsx .