Kuidas luua oma finantsmudelis rippstsenaariume

Stsenaariumide koostamise kõige sagedamini kasutatav meetod on valemite ja rippmenüükastide kombinatsioon. Finantsmudelis loote tabeli võimalike stsenaariumide ja nende sisendite kohta ning linkite stsenaariumide nimed sisendlahtri rippmenüüga. Mudeli sisendid on lingitud stsenaariumitabeliga. Kui mudel on üles ehitatud korralikult ja kõik sisendid voolavad läbi väljunditesse, siis mudeli tulemused muutuvad, kuna kasutaja valib rippmenüüst erinevaid valikuid.

Andmete valideerimise rippmenüüd kasutatakse finantsmodelleerimisel mitmel erineval eesmärgil, sealhulgas stsenaariumianalüüsiks.

Andmete valideerimise kasutamine kasumlikkuse stsenaariumide modelleerimiseks

Laadige alla fail 0801.xlsx . Avage see ja valige vahekaart 8-1-start.

Selle modelleerimisel on sisendid rivistatud veergu B. Tundlikkuse analüüsi saate teha lihtsalt üht sisendit muutes – näiteks muutke lahtris B3 iga kõneoperaatori kliendid 40-lt 45-le ja saate vaadake, kuidas kõik sõltuvad numbrid muutuvad. See oleks tundlikkuse analüüs, kuna muudate ainult ühte muutujat. Selle asemel muudate selles täielikus stsenaariumianalüüsis korraga mitut muutujat, nii et peate tegema rohkem kui mõne numbri käsitsi näpistamiseks.

Stsenaariumianalüüsi tegemiseks andmete valideerimise rippmenüüde abil toimige järgmiselt.

Võtke allalaaditud mudel ning lõigake ja kleepige kirjeldused veerust C veergu F. Seda saate teha, tõstes esile lahtrid C6:C8, vajutades Ctrl+X, valides lahtri F6 ja vajutades Enter.

Lahtrite B3 kuni B8 sisendid on aktiivne vahemik, mis mudelit juhib ja jääb selleks. Kuid need peavad muutuma valemiteks, mis muutuvad olenevalt rippmenüüst, mille loote.

Kopeerige veeru B vahemik veergudesse C, D ja E.

Seda saate teha, tõstes esile B3:B8, vajutades Ctrl+C, valides lahtrid C3:E3 ja vajutades sisestusklahvi. Need summad on iga stsenaariumi puhul samad, kuni te neid muudate.

In real 2 sisesta nende pealkirjad parimal juhul , Base Case , ja halvimal juhul.

Stsenaariumianalüüsi mudeli seadistamine.

Pange tähele, et valemid lingivad endiselt veerus B olevate sisenditega, nagu näete, valides lahtri C12 ja vajutades kiirklahvi F2.

Muutke iga stsenaariumi all olevaid sisendeid.

Võite panna kõik, mida peate tõenäoliseks, kuid selleks, et viia numbrid vastavusse selles näites olevatega, sisestage väärtused. Ignoreeri veergu B praegu.

Sisendid stsenaariumianalüüsiks.

Nüüd peate lisama ülaosas oleva rippmenüü, mis juhib teie stsenaariume. Pole vahet, kuhu rippmenüü kasti täpselt asetate, kuid see peaks asuma kergesti leitavas kohas, tavaliselt lehe ülaosas.

Sisestage lahtrisse E1 pealkiri Stsenaarium .

Valige lahter F1 ja muutke sisendi vormingut, et kasutaja näeks, et see lahter on redigeeritav.

Lihtsaim viis seda teha on järgida järgmisi samme.

Klõpsake üht lahtrit, mis on juba sisendiks vormindatud, näiteks lahtril E3.

Vajutage vahekaardi Avaleht vasakus servas jaotises Lõikelaud ikooni Format Painter. Kursor muutub pintsliks.

Valige vormingu kleepimiseks lahter F1.

Format Painter on tavaliselt ühekordseks kasutamiseks. Pärast lahtri valimist kaob pintsel kursorilt. Kui soovite, et Format Painter muutuks kleepuvaks ja rakenduks mitmele lahtrile, topeltklõpsake ikooni, kui valite selle vahekaardilt Avaleht.

Nüüd valige lahtris F1 vahekaardi Andmed jaotises Andmetööriistad suvand Andmete valideerimine.

Ilmub dialoogiboks Andmete valideerimine.

Vahekaardil Seaded muutke rippmenüü Luba väärtuseks Loend, valige hiirega vahemik =$C$2:$E$2 ja klõpsake nuppu OK.

Andmete valideerimise rippmenüü stsenaariumide loomine.

Klõpsake rippmenüüd, mis kuvatakse nüüd lahtri F1 kõrval, ja valige üks stsenaariumidest (näiteks põhijuhtum).

Valemite rakendamine stsenaariumidele

Veeru B lahtrid juhivad endiselt mudelit ja need tuleb asendada valemitega. Enne valemite lisamist peaksite siiski muutma vahemiku lahtrite vormingut, et näidata, et need sisaldavad valemeid, mitte kõvakodeeritud numbreid. Järgige neid samme.

Valige lahtrid B3:B8 ja valige vahekaardi Avaleht rühmast Font Täitevärv.

Muutke täitevärv valgeks taustaks.

Väga oluline on mudelis eristada valemeid ja sisendlahtreid. Peate igale mudelit avavale kasutajale selgeks tegema, et selle vahemiku lahtrid sisaldavad valemeid ja neid ei tohiks tühistada.

Nüüd peate asendama veerus B kodeeritud väärtused valemitega, mis muutuvad rippmenüü muutumisel. Seda saate teha mitmete erinevate funktsioonide abil; HLOOKUP, pesastatud IF-lause, IFS ja SUMIF aitavad kõik ära. Lisage valemid, järgides neid samme:

Valige lahter B3 ja lisage valem, mis muudab väärtust sõltuvalt sellest, mis on lahtris F1.

Siin on valem erinevate valikute all:

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

  Pange tähele, et selle lahenduse korral peate valemit alla kopeerides muutma rea ​​indeksi numbrit 2-lt 3-le ja nii edasi. Selle asemel võite kasutada funktsiooni ROW kolmandal väljal järgmiselt: =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)
• =SUMF($C$2:$E$2,$F$1,C3:E3)

  Nagu ikka, on valida mitme erineva variandi vahel ja parim lahendus on see, millest on kõige lihtsam ja arusaadavam. Kõik need funktsioonid annavad täpselt sama tulemuse, kuid HLOOKUPis rea indeksi numbri muutmine ei ole usaldusväärne ja ROW lisamine võib kasutajat segadusse ajada. Pesastatud IF-lauset on keeruline koostada ja järgida ning kuigi uus IFS-funktsioon on loodud pesastatud IF-funktsiooni lihtsamaks muutmiseks, on see siiski üsna kohmakas. SUMIF-i on üsna lihtne ehitada ja järgida ning seda on lihtne laiendada, kui teil on vaja tulevikus lisada täiendavaid stsenaariume.

  Pange tähele, et IFS on uus funktsioon, mis on saadaval ainult Office 365 ja Excel 2016 või uuemate installitud versioonidega. Kui kasutate seda funktsiooni ja keegi avab selle mudeli Exceli eelmises versioonis, saab ta valemit vaadata, kuid ei saa seda muuta.

Kopeerige lahtris B3 olev valem veerus alla.

Valmis stsenaariumianalüüs.

Kui kasutate tavalist kopeerimist ja kleepimist, kaotate kogu vorminduse. Oluline on säilitada mudeli vorming, et saaksite ühe pilguga näha, millised sisendid on dollarites, protsentides või kliendinumbrites. Kasutage vormingu säilitamiseks käsku Kleebi valemid. Sellele pääsete juurde, kopeerides lahtri lõikelauale, tõstes esile sihtkoha vahemiku, paremklõpsates ja valides ikooni Kleebi valemid, et kleepida ainult valemid ja jätta vorming puutumata.

Nüüd lõbusast osast! On aeg testida mudeli stsenaariumi funktsionaalsust.

Klõpsake lahtril F1, muutke rippmenüüd ja vaadake, kuidas mudeli väljundid erinevate stsenaariumide vahel vahetades muutuvad.