Kā savā finanšu modelī izveidot nolaižamos scenārijus

Visbiežāk izmantotā scenāriju veidošanas metode ir izmantot formulu un nolaižamo lodziņu kombināciju. Finanšu modelī jūs izveidojat tabulu ar iespējamiem scenārijiem un to ievades datiem un saistāt scenāriju nosaukumus ar ievades šūnu nolaižamo lodziņu. Modeļa ievades ir saistītas ar scenāriju tabulu. Ja modelis ir izveidots pareizi, kad visas ievades plūst cauri izejām, modeļa rezultāti mainīsies, jo lietotājs nolaižamajā lodziņā atlasīs dažādas opcijas.

Datu validācijas nolaižamās izvēlnes tiek izmantotas vairākiem dažādiem finanšu modelēšanas mērķiem, tostarp scenāriju analīzei.

Datu validācijas izmantošana rentabilitātes scenāriju modelēšanai

Lejupielādēt failu 0801.xlsx . Atveriet to un atlasiet cilni ar nosaukumu 8-1-start.

Modelēšanas veidā ievades ir sakārtotas kolonnā B. Jūs varat veikt jutīguma analīzi, vienkārši mainot vienu no ievadiem, piemēram, nomainiet klientu skaitu katram zvana operatoram šūnā B3 no 40 uz 45, un jūs redzēt, kā mainās visi atkarīgie skaitļi. Tā būtu jutīguma analīze, jo jūs maināt tikai vienu mainīgo. Tā vietā šajā pilnajā scenāriju analīzes uzdevumā vienlaikus mainīsit vairākus mainīgos, tāpēc jums būs jādara vairāk nekā manuāli jāpielāgo daži skaitļi.

Lai veiktu scenārija analīzi, izmantojot datu validācijas nolaižamos lodziņus, rīkojieties šādi:

Paņemiet lejupielādēto modeli un izgrieziet un ielīmējiet aprakstus no C kolonnas uz F kolonnu. To var izdarīt, iezīmējot šūnas C6:C8, nospiežot Ctrl+X, atlasot šūnu F6 un nospiežot taustiņu Enter.

Ievades šūnās B3 līdz B8 ir aktīvais diapazons, kas virza modeli un tāds arī paliks. Tomēr tām jākļūst par formulām, kas mainās atkarībā no izveidotā nolaižamā lodziņa.

Kopējiet diapazonu B kolonnā uz C, D un E kolonnu.

To var izdarīt, iezīmējot B3:B8, nospiežot Ctrl+C, atlasot šūnas C3:E3 un nospiežot taustiņu Enter. Šīs summas būs vienādas katram scenārijam, līdz tās mainīsit.

Šajā rindā 2 ievadīt nosaukumus, labākajā gadījumā , Base Case , un sliktākajā gadījumā.

Modeļa iestatīšana scenāriju analīzei.

Ņemiet vērā, ka formulas joprojām ir saistītas ar ievadiem kolonnā B, kā to varat redzēt, atlasot šūnu C12 un nospiežot īsinājumtaustiņu F2.

Rediģējiet ievades datus zem katra scenārija.

Varat ievietot visu, ko uzskatāt par iespējamu, taču, lai skaitļi atbilstu šajā piemērā norādītajiem, ievadiet vērtības. Pagaidām ignorēt kolonnu B.

Ievades scenāriju analīzei.

Tagad jums jāpievieno nolaižamais lodziņš augšpusē, kas virzīs jūsu scenārijus. Nav īsti svarīgi, kur tieši ievietojat nolaižamo lodziņu, taču tam ir jāatrodas viegli atrodamā vietā, parasti lapas augšdaļā.

Šūnā E1 ievadiet nosaukumu Scenārijs .

Atlasiet šūnu F1 un mainiet ievades formatējumu, lai lietotājs varētu redzēt, ka šī šūna ir rediģējama.

Vienkāršākais veids, kā to izdarīt, ir veikt šādas darbības:

Noklikšķiniet uz vienas no šūnām, kas jau ir formatētas kā ievade, piemēram, šūnas E3.

Cilnes Sākums kreisajā pusē sadaļā Starpliktuve nospiediet ikonu Format Painter. Kursors mainīsies uz otu.

Atlasiet šūnu F1, lai ielīmētu formatējumu.

Format Painter parasti ir paredzēts vienreizējai lietošanai. Kad esat atlasījis šūnu, ota pazudīs no kursora. Ja vēlaties, lai Format Painter kļūtu “lipīgs” un lietotu vairākas šūnas, veiciet dubultklikšķi uz ikonas, kad to atlasāt cilnē Sākums.

Tagad šūnā F1 atlasiet Datu validācija cilnes Dati sadaļā Datu rīki.

Tiek parādīts dialoglodziņš Datu validācija.

Cilnē Iestatījumi mainiet nolaižamo izvēlni Atļaut uz Saraksts, izmantojiet peli, lai atlasītu diapazonu =$C$2:$E$2, un noklikšķiniet uz Labi.

Datu validācijas nolaižamo scenāriju izveide.

Noklikšķiniet uz nolaižamā lodziņa, kas tagad tiek parādīts blakus šūnai F1, un atlasiet vienu no scenārijiem (piemēram, pamata gadījums).

Formulu pielietošana scenārijiem

Šūnas B kolonnā joprojām vada modeli, un tās ir jāaizstāj ar formulām. Tomēr pirms formulu pievienošanas ir jāmaina diapazona šūnu formatējums, lai parādītu, ka tajās ir formulas, nevis stingri kodēti skaitļi. Veiciet tālāk norādītās darbības.

Atlasiet šūnas B3:B8 un cilnes Sākums grupā Fonts atlasiet Aizpildījuma krāsa.

Mainiet aizpildījuma krāsu uz baltu fonu.

Ir ļoti svarīgi modelī atšķirt formulas un ievades šūnas. Ikvienam modelim atverošajam lietotājam ir skaidri jānorāda, ka šajā diapazonā esošās šūnas satur formulas un tās nedrīkst ignorēt.

Tagad jums ir jāaizstāj stingri kodētās vērtības kolonnā B ar formulām, kas mainīsies, mainoties nolaižamajam lodziņam. To var izdarīt, izmantojot vairākas dažādas funkcijas; HLOOKUP, ligzdots IF priekšraksts, IFS un SUMIF veiks visu nepieciešamo. Pievienojiet formulas, veicot šādas darbības:

Atlasiet šūnu B3 un pievienojiet formulu, kas mainīs vērtību atkarībā no tā, kas atrodas šūnā F1.

Lūk, kāda būs formula dažādās opcijās:

• =UZKLĀŠANA($F$1,$C$2:$E$8,2,0)

  Ņemiet vērā, ka, izmantojot šo risinājumu, jums ir jāmaina rindas indeksa numurs no 2 uz 3 un tā tālāk, kopējot formulu uz leju. Tā vietā varat izmantot funkciju ROW trešajā laukā šādi: =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)

  Kā vienmēr, ir vairākas dažādas iespējas, no kurām izvēlēties, un labākais risinājums ir visvienkāršākais un vieglāk saprotamais. Jebkura no šīm funkcijām sniegs tieši tādu pašu rezultātu, taču rindas indeksa numura maiņa programmā HLOOKUP nav stabila, un RINDAS pievienošana var radīt neērtības lietotājam. Ligzdoto IF priekšrakstu ir sarežģīti izveidot un izpildīt, un, lai gan jaunā IFS funkcija ir izstrādāta, lai vienkāršotu ligzdoto IF funkciju, tā joprojām ir diezgan smagnēja. SUMIF ir diezgan vienkārši izveidot un sekot, un to ir viegli paplašināt, ja nākotnē būs jāpievieno papildu scenāriji.

  Ņemiet vērā, ka IFS ir jauna funkcija, kas ir pieejama tikai ar instalētu Office 365 un Excel 2016 vai jaunāku versiju. Ja izmantojat šo funkciju un kāds atver šo modeli iepriekšējā Excel versijā, viņa var skatīt formulu, bet nevarēs to rediģēt.

Kopējiet formulu šūnā B3 uz leju kolonnā.

Pabeigta scenārija analīze.

Izmantojot parastu kopēšanu un ielīmēšanu, jūs zaudēsiet visu formatējumu. Ir svarīgi saglabāt modeļa formatējumu, lai vienā mirklī varētu redzēt, kuras ievades ir dolāros, procentos vai klientu skaitļos. Lai saglabātu formatējumu, izmantojiet ielīmēšanas formulas. Tam var piekļūt, kopējot šūnu starpliktuvē, iezīmējot galamērķa diapazonu, noklikšķinot ar peles labo pogu un atlasot ikonu Paste Formulas, lai ielīmētu tikai formulas un formatējumu atstātu neskartu.

Tagad par jautro daļu! Ir pienācis laiks pārbaudīt scenārija funkcionalitāti modelī.

Noklikšķiniet uz šūnas F1, mainiet nolaižamo lodziņu un skatieties, kā mainās modeļa izvadi, pārslēdzoties starp dažādiem scenārijiem.