Kaip sukurti išskleidžiamuosius scenarijus savo finansiniame modelyje

Dažniausiai naudojamas scenarijų kūrimo būdas yra naudoti formulių ir išskleidžiamųjų langelių derinį. Finansiniame modelyje sukuriate galimų scenarijų ir jų įvesties lentelę ir susiejate scenarijų pavadinimus su įvesties langelio išskleidžiamuoju laukeliu. Modelio įvestys susietos su scenarijų lentele. Jei modelis buvo sukurtas tinkamai, o visi įėjimai teka į išvestis, modelio rezultatai pasikeis, nes vartotojas išskleidžiamajame laukelyje pasirenka skirtingas parinktis.

Duomenų patvirtinimo išskleidžiamieji langeliai naudojami įvairiems finansinio modeliavimo tikslams, įskaitant scenarijų analizę.

Duomenų patvirtinimo naudojimas pelningumo scenarijams modeliuoti

Atsisiųskite failą 0801.xlsx . Atidarykite jį ir pasirinkite skirtuką 8-1 pradžia.

Tai sumodeliavus, įvestys yra išdėstytos B stulpelyje. Jautrumo analizę galite atlikti tiesiog pakeisdami vieną iš įvesties, pavyzdžiui, pakeiskite klientus vienam skambučio operatoriui langelyje B3 nuo 40 iki 45, ir jūs pamatyti, kaip pasikeitė visi priklausomi skaičiai. Tai būtų jautrumo analizė, nes keičiate tik vieną kintamąjį. Vietoj to, atlikdami visą scenarijaus analizės pratimą, vienu metu pakeisite kelis kintamuosius, todėl turėsite daugiau nei rankiniu būdu pakoreguoti kelis skaičius.

Norėdami atlikti scenarijaus analizę naudodami duomenų patvirtinimo išskleidžiamuosius laukelius, atlikite šiuos veiksmus:

Paimkite atsisiųstą modelį ir iškirpkite ir įklijuokite aprašymus iš C stulpelio į F stulpelį. Tai galite padaryti pažymėdami langelius C6:C8, paspausdami Ctrl+X, pasirinkę langelį F6 ir paspausdami Enter.

Įvestys langeliuose B3–B8 yra aktyvusis diapazonas, kuris valdo modelį ir toks išliks. Tačiau jos turi tapti formulėmis, kurios keičiasi atsižvelgiant į išskleidžiamąjį laukelį, kurį sukursite.

Nukopijuokite B stulpelio diapazoną į C, D ir E stulpelius.

Tai galite padaryti paryškindami B3:B8, paspausdami Ctrl+C, pažymėdami langelius C3:E3 ir paspausdami Enter. Šios sumos bus vienodos kiekvienam scenarijui, kol jas nepakeisite.

Iš eilės 2 Įveskite pavadinimus geriausiu atveju , bazine , o blogiausiu atveju.

Scenarijų analizės modelio nustatymas.

Atkreipkite dėmesį, kad formulės vis tiek susieja su įvestimis B stulpelyje, kaip matote pasirinkę langelį C12 ir paspausdami spartųjį klavišą F2.

Redaguokite įvestis po kiekvienu scenarijumi.

Galite įdėti viską, kas, jūsų manymu, yra tikėtina, bet norėdami, kad skaičiai atitiktų šiame pavyzdyje esančius skaičius, įveskite reikšmes. Kol kas nepaisykite B stulpelio.

Įvesties scenarijų analizei.

Dabar viršuje turite pridėti išskleidžiamąjį laukelį, kuris paskatins jūsų scenarijus. Nesvarbu, kur tiksliai įdėsite išskleidžiamąjį laukelį, bet jis turėtų būti lengvai randamoje vietoje, dažniausiai puslapio viršuje.

E1 langelyje įveskite pavadinimą Scenarijus .

Pasirinkite langelį F1 ir pakeiskite įvesties formatavimą, kad vartotojas matytų, jog šį langelį galima redaguoti.

Lengviausias būdas tai padaryti yra atlikti šiuos veiksmus:

Spustelėkite vieną iš langelių, kurie jau suformatuoti kaip įvestis, pvz., langelį E3.

Paspauskite Format Painter piktogramą, esančią skiltyje Iškarpinė, kairėje skirtuko Pagrindinis pusėje. Jūsų žymeklis pasikeis į teptuką.

Pasirinkite langelį F1, kad įklijuotumėte formatavimą.

„Format Painter“ paprastai yra skirtas vienkartiniam naudojimui. Pasirinkus langelį, teptukas dings iš žymeklio. Jei norite, kad formato dažytojas taptų „lipnus“ ir būtų taikomas kelioms ląstelėms, dukart spustelėkite piktogramą, kai pasirenkate ją skirtuke Pagrindinis.

Dabar langelyje F1 pasirinkite Duomenų patvirtinimas iš skirtuko Duomenys skiltyje Duomenų įrankiai.

Pasirodo duomenų patvirtinimo dialogo langas.

Skirtuke Nustatymai pakeiskite išskleidžiamąjį meniu Leisti į Sąrašas, pele pasirinkite diapazoną =$C$2:$E$2 ir spustelėkite Gerai.

Duomenų patvirtinimo išskleidžiamųjų scenarijų kūrimas.

Spustelėkite išskleidžiamąjį laukelį, kuris dabar rodomas šalia langelio F1, ir pasirinkite vieną iš scenarijų (pavyzdžiui, pagrindinis atvejis).

Formulių taikymas scenarijams

B stulpelio langeliai vis dar valdo modelį, todėl juos reikia pakeisti formulėmis. Tačiau prieš įtraukdami formules turėtumėte pakeisti diapazono langelių formatavimą, kad parodytumėte, jog juose yra formulės, o ne užkoduoti skaičiai. Atlikite šiuos veiksmus:

Pasirinkite langelius B3:B8 ir skirtuke Pagrindinis esančioje grupėje Šriftas pasirinkite užpildymo spalvą.

Pakeiskite užpildymo spalvą į baltą foną.

Modelyje labai svarbu atskirti formules ir įvesties langelius. Bet kuriam vartotojui, atidarančiam modelį, turite aiškiai pasakyti, kad šio diapazono langeliuose yra formulių ir jų negalima nepaisyti.

Dabar B stulpelyje užkoduotas reikšmes turite pakeisti formulėmis, kurios pasikeis, kai pasikeis išskleidžiamasis laukelis. Tai galite padaryti naudodami daugybę skirtingų funkcijų; HLOOKUP, įdėtas IF sakinys, IFS ir SUMIF atliks viską. Pridėkite formules atlikdami šiuos veiksmus:

Pasirinkite langelį B3 ir pridėkite formulę, kuri pakeis reikšmę priklausomai nuo to, kas yra langelyje F1.

Štai kokia bus formulė pagal skirtingas parinktis:

• =ŽIŪRĖTI ($F$1,$C$2:$E$8,2,0)

  Atminkite, kad naudodami šį sprendimą, kopijuodami formulę turite pakeisti eilutės indekso numerį nuo 2 iki 3 ir pan. Vietoj to galite naudoti funkciją ROW trečiajame lauke, kaip šis: =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)

  Kaip visada, galima rinktis iš kelių skirtingų variantų ir geriausias sprendimas yra tas, kuris yra paprasčiausias ir lengviausiai suprantamas. Bet kuri iš šių funkcijų duos lygiai tokį patį rezultatą, tačiau HLOOKUP eilutės indekso numerio keitimas nėra patikimas, o pridėjus EILUTĮ vartotojas gali būti painus. Įdėtą IF teiginį sudėtinga sukurti ir sekti, ir nors naujoji IFS funkcija sukurta taip, kad įdėta IF funkcija būtų paprastesnė, ji vis tiek yra gana sudėtinga. SUMIF yra gana paprasta sukurti ir sekti, o jį lengva išplėsti, jei ateityje reikės pridėti papildomų scenarijų.

  Atminkite, kad IFS yra nauja funkcija, pasiekiama tik įdiegus „Office 365“ ir „Excel 2016“ ar naujesnę versiją. Jei naudojate šią funkciją ir kas nors atidaro šį modelį ankstesnėje „Excel“ versijoje, ji gali peržiūrėti formulę, bet negalės jos redaguoti.

Nukopijuokite formulę langelyje B3 stulpeliu žemyn.

Baigta scenarijaus analizė.

Naudodami įprastą kopijavimo ir įklijavimo būdą, prarasite visą formatavimą. Svarbu išlaikyti modelio formatavimą, kad galėtumėte iš karto matyti, kurios įvesties vertės yra doleriais, procentais arba klientų skaičiais. Norėdami išlaikyti formatavimą, naudokite įklijavimo formules. Jį galite pasiekti nukopijavę langelį į mainų sritį, paryškinę paskirties diapazoną, spustelėję dešiniuoju pelės mygtuku ir pasirinkę piktogramą Įklijuoti formules, kad įklijuotumėte tik formules ir paliktumėte nepažeistą formatavimą.

Dabar linksmoji dalis! Atėjo laikas išbandyti modelio scenarijaus funkcionalumą.

Spustelėkite langelį F1, pakeiskite išskleidžiamąjį laukelį ir stebėkite, kaip keičiasi modelio išėjimai, kai perjungiate skirtingus scenarijus.