Как да изградите падащи сценарии във вашия финансов модел

Най-често използваният метод за изграждане на сценарии е да се използва комбинация от формули и падащи полета. Във финансовия модел създавате таблица с възможни сценарии и техните входни данни и свързвате имената на сценариите с падащо поле за входна клетка. Входните данни на модела са свързани с таблицата на сценариите. Ако моделът е изграден правилно с всички входове, преминаващи към изходите, тогава резултатите от модела ще се променят, когато потребителят избере различни опции от падащото поле.

Падащите полета за валидиране на данни се използват за редица различни цели във финансовото моделиране, включително анализ на сценарии.

Използване на валидиране на данни за моделиране на сценарии за рентабилност

Изтеглете файл 0801.xlsx . Отворете го и изберете раздела с надпис 8-1-start.

По начина, по който това е моделирано, входните данни са подредени в колона 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. Тези суми ще бъдат еднакви за всеки сценарий, докато не ги промените.

В ред 2 влиза заглавията най-добрия случай , при основния случай , и най-лошия случай.

Създаване на модела за анализ на сценарии.

Обърнете внимание, че формулите все още се свързват с входните данни в колона B, както можете да видите, като изберете клетка C12 и натиснете клавиша за бърз достъп F2.

Редактирайте входните данни под всеки сценарий.

Можете да поставите каквото смятате, че е вероятно, но за да съпоставите числата с тези в този пример, въведете стойностите. Игнорирайте колона B засега.

Входове за анализ на сценарии.

Сега трябва да добавите падащото поле в горната част, което ще управлява вашите сценарии. Всъщност няма значение къде точно ще поставите падащото поле, но то трябва да бъде на място, което е лесно за намиране, обикновено в горната част на страницата.

В клетка E1 въведете заглавието Сценарий .

Изберете клетка F1 и променете форматирането на въвеждане, така че потребителят да види, че тази клетка може да се редактира.

Най-лесният начин да направите това е да следвате следните стъпки:

Щракнете върху една от клетките, които вече са форматирани като вход, като клетка E3.

Натиснете иконата Format Painter в секцията Clipboard от лявата страна на раздела Начало. Курсорът ви ще се промени на четка.

Изберете клетка F1, за да поставите форматирането.

Format Painter обикновено е за еднократна употреба. След като изберете клетката, четката ще изчезне от курсора. Ако искате 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 надолу по колоната.

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

Като използвате обикновено копиране и поставяне, ще загубите цялото си форматиране. Важно е да запазите форматирането на модела, за да можете да видите с един поглед кои входове са в стойности в долари, проценти или номера на клиенти. Използвайте Paste Formulas, за да запазите форматирането. Можете да получите достъп до него, като копирате клетката в клипборда, маркирате диапазона на местоназначението, щракнете с десния бутон и изберете иконата Поставяне на формули, за да поставите само формули и да оставите форматирането непокътнато.

Сега за забавната част! Време е да тестваме функционалността на сценария в модела.

Щракнете върху клетка F1, променете падащото меню и наблюдавайте как изходите на модела се променят, докато превключвате между различните сценарии.