Kako zgraditi spustne scenarije v vašem finančnem modelu

Najpogosteje uporabljena metoda izdelave scenarijev je uporaba kombinacije formul in spustnih polj. V finančnem modelu ustvarite tabelo možnih scenarijev in njihovih vhodov ter povežete imena scenarijev s spustnim poljem vnosne celice. Vhodi modela so povezani s tabelo scenarijev. Če je bil model pravilno zgrajen z vsemi vhodi, ki tečejo do izhodov, se bodo rezultati modela spremenili, ko uporabnik iz spustnega polja izbere različne možnosti.

Spustna polja za preverjanje veljavnosti podatkov se uporabljajo za številne različne namene pri finančnem modeliranju, vključno z analizo scenarijev.

Uporaba potrditev podatkov za modeliranje scenarijev dobičkonosnosti

Prenesite datoteko 0801.xlsx . Odprite ga in izberite zavihek z oznako 8-1-start.

Na način, kako je bilo to modelirano, so vhodi razvrščeni v stolpec B. Lahko izvedete analizo občutljivosti preprosto tako, da spremenite enega od vhodov – na primer spremenite stranke na operaterja klica v celici B3 iz 40 na 45 in boste glej, kako se spreminjajo vse odvisne številke. To bi bila analiza občutljivosti, ker spreminjate samo eno spremenljivko. Namesto tega boste v tej celotni vaji analize scenarija spremenili več spremenljivk hkrati, tako da boste morali narediti več kot ročno prilagoditi nekaj številk.

Če želite izvesti analizo scenarija z uporabo spustnih polj za preverjanje veljavnosti podatkov, sledite tem korakom:

Vzemite preneseni model ter izrežite in prilepite opise iz stolpca C v stolpec F. To lahko storite tako, da označite celice C6:C8, pritisnete Ctrl+X, izberete celico F6 in pritisnete Enter.

Vhodi v celicah B3 do B8 so aktivni obseg, ki poganja model in bo tako tudi ostal. Vendar pa morajo postati formule, ki se spreminjajo glede na spustni meni, ki ga boste ustvarili.

Kopirajte obseg v stolpcu B v stolpce C, D in E.

To lahko storite tako, da označite B3:B8, pritisnete Ctrl+C, izberete celice C3:E3 in pritisnete Enter. Ti zneski bodo enaki za vsak scenarij, dokler jih ne spremenite.

V vrsti 2 vnesti naslove najboljšem primeru , osnovnem scenariju , in v najslabšem primeru.

Postavitev modela za analizo scenarijev.

Upoštevajte, da so formule še vedno povezane z vnosi v stolpcu B, kot lahko vidite, če izberete celico C12 in pritisnete bližnjico F2.

Uredite vnose pod vsakim scenarijem.

Vnesete lahko vse, kar mislite, da je verjetno, vendar če želite ujemati številke s tistimi v tem primeru, vnesite vrednosti. Zaenkrat prezrite stolpec B.

Vhodi za analizo scenarijev.

Zdaj morate na vrhu dodati spustno polje, ki bo vodilo vaše scenarije. Pravzaprav ni pomembno, kam točno postavite spustni meni, vendar mora biti na mestu, ki ga je enostavno najti, običajno na vrhu strani.

V celico E1 vnesite naslov Scenarij .

Izberite celico F1 in spremenite oblikovanje na vnos, tako da lahko uporabnik vidi, da je to celico mogoče urejati.

Najlažji način za to je, da sledite tem korakom:

Kliknite eno od celic, ki so že oblikovane kot vhod, na primer celica E3.

Pritisnite ikono Format Painter v razdelku Odložišče na levi strani zavihka Domov. Vaš kazalec se bo spremenil v čopič.

Izberite celico F1, da prilepite oblikovanje.

Format Painter je običajno za enkratno uporabo. Ko izberete celico, bo čopič izginil iz kazalca. Če želite, da Preslikava oblik postane "lepljiva" in se uporablja za več celic, dvokliknite ikono, ko jo izberete na zavihku Domov.

Zdaj v celici F1 izberite Preverjanje podatkov v razdelku Podatkovna orodja na kartici Podatki.

Prikaže se pogovorno okno Preverjanje podatkov.

Na zavihku Nastavitve spremenite spustni meni Dovoli na Seznam, z miško izberite obseg =$C$2:$E$2 in kliknite V redu.

Ustvarjanje spustnih scenarijev za preverjanje veljavnosti podatkov.

Kliknite spustno polje, ki se zdaj prikaže poleg celice F1, in izberite enega od scenarijev (na primer Osnovni primer).

Uporaba formul za scenarije

Celice v stolpcu B še vedno poganjajo model in jih je treba nadomestiti s formulami. Preden dodate formule, morate spremeniti oblikovanje celic v obsegu, da pokažete, da vsebujejo formule namesto trdo kodiranih številk. Sledite tem korakom:

Izberite celice B3:B8 in izberite Barvo polnila v skupini Pisava na zavihku Domov.

Spremenite barvo polnila v belo ozadje.

Zelo pomembno je razlikovati med formulami in vhodnimi celicami v modelu. Vsakemu uporabniku, ki odpira model, morate jasno povedati, da celice v tem obsegu vsebujejo formule in jih ne smete preglasiti.

Zdaj morate zamenjati trdo kodirane vrednosti v stolpcu B s formulami, ki se bodo spremenile, ko se spustno polje spremeni. To lahko storite s številnimi različnimi funkcijami; HLOOKUP, ugnezdeni stavek IF, IFS in SUMIF bodo naredili trik. Dodajte formule tako, da sledite tem korakom:

Izberite celico B3 in dodajte formulo, ki bo spremenila vrednost glede na to, kaj je v celici F1.

Tukaj je, kakšna bo formula pod različnimi možnostmi:

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

  Upoštevajte, da morate pri tej rešitvi spremeniti indeksno številko vrstice iz 2 v 3 in tako naprej, ko kopirate formulo navzdol. Namesto tega lahko uporabite funkcijo ROW v tretjem polju, kot je ta: =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)

  Kot vedno je na izbiro več različnih možnosti in najboljša rešitev je tista, ki je najenostavnejša in najlažje razumljiva. Vsaka od teh funkcij bo dala popolnoma enak rezultat, vendar sprememba indeksne številke vrstice v HLOOKUP ni robustna, dodajanje VRSTIC pa je lahko za uporabnika zmedeno. Ugnezdeni stavek IF je težko zgraditi in slediti, in čeprav je nova funkcija IFS zasnovana tako, da poenostavi ugnezdeno funkcijo IF, je še vedno precej okorna. SUMIF je precej preprost za sestavljanje in spremljanje ter ga je enostavno razširiti, če boste morali v prihodnosti dodati dodatne scenarije.

  Upoštevajte, da je IFS nova funkcija, ki je na voljo samo z nameščenimi Office 365 in Excel 2016 ali novejšimi. Če uporabite to funkcijo in nekdo odpre ta model v prejšnji različici Excela, si lahko ogleda formulo, vendar je ne bo mogel urediti.

Kopirajte formulo v celici B3 navzdol po stolpcu.

Končana analiza scenarija.

Z uporabo navadnega kopiranja in lepljenja boste izgubili vse svoje oblikovanje. Pomembno je, da ohranite oblikovanje modela, tako da lahko na prvi pogled vidite, kateri vnosi so v vrednostih v dolarjih, odstotkih ali številu strank. Za ohranitev oblikovanja uporabite Prilepi formule. Do njega lahko dostopate tako, da kopirate celico v odložišče, označite ciljni obseg, kliknete z desno tipko miške in izberete ikono Prilepi formule, da prilepite samo formule in pustite oblikovanje nedotaknjeno.

Zdaj pa za zabavni del! Čas je, da preizkusite funkcionalnost scenarija v modelu.

Kliknite celico F1, spremenite spustno polje in opazujte, kako se izhodi modela spreminjajo, ko preklapljate med različnimi scenariji.