Како да направите падајуће сценарије у вашем финансијском моделу

Најчешће коришћени метод за прављење сценарија је коришћење комбинације формула и падајућих оквира. У финансијском моделу, креирате табелу могућих сценарија и њихових улаза и повезујете имена сценарија са падајућим оквиром ћелије за унос. Уноси модела су повезани са табелом сценарија. Ако је модел правилно направљен са свим улазима који теку кроз излазе, онда ће се резултати модела променити како корисник изабере различите опције из падајућег оквира.

Падајући оквири за валидацију података се користе за бројне различите сврхе у финансијском моделирању, укључујући анализу сценарија.

Коришћење валидације података за моделирање сценарија профитабилности

Преузмите датотеку 0801.клск . Отворите га и изаберите картицу са ознаком 8-1-старт.

На начин на који је ово моделирано, улази су поређани у колони Б. Можете извршити анализу осетљивости једноставним променом једног од улаза — на пример, промените корисника по оператеру позива у ћелији Б3 са 40 на 45, и ви ћете види да се сви зависни бројеви мењају. Ово би била анализа осетљивости, јер мењате само једну променљиву. Уместо тога, променићете више променљивих одједном у овој целој вежби анализе сценарија, тако да ћете морати да урадите више од ручног подешавања неколико бројева.

Да бисте извршили анализу сценарија помоћу падајућих оквира за валидацију података, следите ове кораке:

Узмите преузети модел и исеците и налепите описе из колоне Ц у колону Ф. То можете учинити тако што ћете означити ћелије Ц6:Ц8, притиснути Цтрл+Кс, изабрати ћелију Ф6 и притиснути Ентер.

Улази у ћелијама Б3 до Б8 су активни опсег који покреће модел и тако ће остати. Међутим, оне морају да постану формуле које се мењају у зависности од падајућег оквира који ћете креирати.

Копирајте опсег из колоне Б у колоне Ц, Д и Е.

То можете учинити тако што ћете означити Б3:Б8, притиснути Цтрл+Ц, изабрати ћелије Ц3:Е3 и притиснути Ентер. Ови износи ће бити исти за сваки сценарио док их не промените.

У реду 2 унесите њихове наслове најбољем случају , основни случај , и најгорем случају.

Постављање модела за анализу сценарија.

Имајте на уму да се формуле и даље повезују са улазима у колони Б, као што можете видети тако што ћете изабрати ћелију Ц12 и притиснути пречицу Ф2.

Уредите уносе испод сваког сценарија.

Можете ставити шта год мислите да је вероватно, али да бисте упарили бројеве са онима у овом примеру, унесите вредности. Занемарите колону Б за сада.

Улази за анализу сценарија.

Сада морате да додате падајући оквир на врху, који ће покретати ваше сценарије. Није битно где тачно ставите падајући оквир, али би требало да буде на локацији коју је лако пронаћи, обично на врху странице.

У ћелију Е1 унесите наслов Сценарио .

Изаберите ћелију Ф1 и промените форматирање на унос тако да корисник може да види да се ова ћелија може уређивати.

Најлакши начин да то урадите је да следите ове кораке:

Кликните на једну од ћелија које су већ форматиране као улаз, као што је ћелија Е3.

Притисните икону Формат Паинтер у одељку Цлипбоард на левој страни картице Почетна. Ваш курсор ће се променити у четкицу.

Изаберите ћелију Ф1 да бисте налепили форматирање.

Формат Паинтер је обично за једнократну употребу. Након што изаберете ћелију, четкица ће нестати са курсора. Ако желите да Формат Паинтер постане „лепљив“ и примени се на више ћелија, двапут кликните на икону када је изаберете са картице Почетна.

Сада, у ћелији Ф1, изаберите Валидација података из одељка Дата Тоолс на картици Подаци.

Појављује се дијалог Валидација података.

На картици Подешавања промените падајући мени Дозволи у Листа, мишем изаберите опсег =$Ц$2:$Е$2 и кликните на ОК.

Креирање падајућих сценарија за валидацију података.

Кликните на падајући оквир који се сада појављује поред ћелије Ф1 и изаберите један од сценарија (на пример, Основни случај).

Примена формула на сценарије

Ћелије у колони Б и даље управљају моделом и оне морају бити замењене формулама. Међутим, пре него што додате формуле, требало би да промените форматирање ћелија у опсегу како бисте показали да садрже формуле, уместо чврсто кодираних бројева. Пратите ове кораке:

Изаберите ћелије Б3:Б8 и изаберите боју попуне из групе Фонт на картици Почетна.

Промените боју испуне у белу позадину.

Веома је важно направити разлику између формула и улазних ћелија у моделу. Морате јасно ставити до знања сваком кориснику који отвара модел да ћелије у овом опсегу садрже формуле и да их не треба заменити.

Сада морате да замените чврсто кодиране вредности у колони Б формулама које ће се мењати како се падајући оквир мења. То можете учинити користећи низ различитих функција; ХЛООКУП, угнежђена ИФ изјава, ИФС и СУМИФ ће све учинити трик. Додајте формуле пратећи ове кораке:

Изаберите ћелију Б3 и додајте формулу која ће променити вредност у зависности од тога шта се налази у ћелији Ф1.

Ево шта ће формула бити под различитим опцијама:

• =ХЛООКУП($Ф$1,$Ц$2:$Е$8,2,0)

  Имајте на уму да са овим решењем морате да промените број индекса реда са 2 на 3 и тако даље док копирате формулу надоле. Уместо тога, можете користити функцију РОВ у трећем пољу овако: =ХЛООКУП($Ф$1,$Ц$2:$Е$8,РОВ(А3)-1,0)

• =ИФ($Ф$1=$Ц$2,Ц3,ИФ($Ф$1=$Д$2,Д3,Е3))
• =ИФС($Ф$1=$Ц$2,Ц3,$Ф$1=$Д$2,Д3,$Ф$1=$Е$2,Е3)
• =СУМИФ($Ц$2:$Е$2,$Ф$1,Ц3:Е3)

  Као и увек, постоји неколико различитих опција које можете изабрати, а најбоље решење је оно које је најједноставније и најлакше за разумевање. Било која од ових функција ће дати потпуно исти резултат, али потреба за променом броја индекса реда у ХЛООКУП-у није робусна, а додавање РОВ-а може бити збуњујуће за корисника. Угнежђену ИФ наредбу је тешко изградити и пратити, и иако је нова функција ИФС дизајнирана да учини угнежђену ИФ функцију једноставнијом, она је и даље прилично гломазна. СУМИФ је прилично једноставан за изградњу и праћење, а лако га је проширити ако у будућности треба да додате додатне сценарије.

  Имајте на уму да је ИФС нова функција која је доступна само са инсталираним Оффице 365 и Екцел 2016 или новијим верзијама. Ако користите ову функцију и неко отвори овај модел у претходној верзији Екцел-а, она може да види формулу, али неће моћи да је уређује.

Копирајте формулу у ћелију Б3 низ колону.

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

Коришћењем обичног копирања и лепљења изгубићете све своје форматирање. Важно је задржати форматирање модела тако да можете на први поглед да видите који су улази у доларским вредностима, процентима или бројевима купаца. Користите Пасте Формулес да задржите форматирање. Можете јој приступити тако што ћете копирати ћелију у међуспремник, означити опсег одредишта, кликнути десним тастером миша и изабрати икону Налепи формуле да бисте налепили само формуле и оставили форматирање нетакнуто.

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

Кликните на ћелију Ф1, промените падајући оквир и гледајте како се излази модела мењају док прелазите између различитих сценарија.