Si të ndërtoni skenarë zbritës në modelin tuaj financiar

Metoda më e përdorur për ndërtimin e skenarëve është përdorimi i një kombinimi të formulave dhe kutive me zbritje. Në modelin financiar, ju krijoni një tabelë të skenarëve të mundshëm dhe hyrjet e tyre dhe lidhni emrat e skenarëve me një kuti rënëse të qelizave hyrëse. Inputet e modelit janë të lidhura me tabelën e skenarit. Nëse modeli është ndërtuar siç duhet me të gjitha hyrjet që rrjedhin në dalje, atëherë rezultatet e modelit do të ndryshojnë pasi përdoruesi zgjedh opsione të ndryshme nga kutia rënëse.

Kutitë rënëse të vlefshmërisë së të dhënave përdoren për një sërë qëllimesh të ndryshme në modelimin financiar, duke përfshirë analizën e skenarëve.

Përdorimi i vlefshmërisë së të dhënave për të modeluar skenarët e përfitimit

Shkarkoni skedarin 0801.xlsx . Hapeni atë dhe zgjidhni skedën e emërtuar 8-1-start.

Siç është modeluar kjo, hyrjet renditen në kolonën B. Ju mund të kryeni analizën e ndjeshmërisë thjesht duke ndryshuar njërën nga hyrjet — për shembull, ndryshoni klientët për operator telefonate në qelizën B3 nga 40 në 45 dhe do të shikoni ndryshimin e të gjithë numrave të varur. Kjo do të ishte një analizë e ndjeshmërisë, sepse po ndryshoni vetëm një variabël. Në vend të kësaj, ju do të ndryshoni shumë variabla në të njëjtën kohë në këtë ushtrim të analizës së skenarit të plotë, kështu që do t'ju duhet të bëni më shumë se sa t'i rregulloni disa numra me dorë.

Për të kryer një analizë skenari duke përdorur kutitë rënëse të vlefshmërisë së të dhënave, ndiqni këto hapa:

Merrni modelin e shkarkuar dhe prisni dhe ngjitni përshkrimet nga kolona C në kolonën F. Këtë mund ta bëni duke theksuar qelizat C6:C8, duke shtypur Ctrl+X, duke zgjedhur qelizën F6 dhe duke shtypur Enter.

Hyrat në qelizat B3 deri në B8 janë diapazoni aktiv që drejton modelin dhe do të mbetet i tillë. Megjithatë, ato duhet të bëhen formula që ndryshojnë në varësi të kutisë së lëshimit që do të krijoni.

Kopjo diapazonin në kolonën B në kolonat C, D dhe E.

Këtë mund ta bëni duke theksuar B3:B8, duke shtypur Ctrl+C, duke zgjedhur qelizat C3:E3 dhe duke shtypur Enter. Këto shuma do të jenë të njëjta për çdo skenar derisa t'i ndryshoni ato.

Në rradhë 2 hyjë titujt mirë Rasti , Base Rasti , dhe rastin më të keq.

Vendosja e modelit për analizën e skenarit.

Vini re se formulat ende lidhen me hyrjet në kolonën B, siç mund ta shihni duke zgjedhur qelizën C12 dhe duke shtypur tastin e shkurtores F2.

Redaktoni hyrjet nën secilin skenar.

Mund të vendosni çfarëdo që mendoni se është e mundshme, por në mënyrë që të përputhen numrat me ato në këtë shembull, futni vlerat. Injoro kolonën B për momentin.

Inputet për analizën e skenarit.

Tani ju duhet të shtoni kutinë rënëse në krye, e cila do të drejtojë skenarët tuaj. Nuk ka shumë rëndësi se ku e vendosni saktësisht kutinë me zbritje, por duhet të jetë në një vendndodhje që është e lehtë për t'u gjetur, zakonisht në krye të faqes.

Në qelizën E1, vendosni titullin Skenari .

Zgjidhni qelizën F1 dhe ndryshoni formatimin në hyrje në mënyrë që përdoruesi të shohë se kjo qelizë është e modifikueshme.

Mënyra më e lehtë për ta bërë këtë është të ndiqni këto hapa:

Klikoni një nga qelizat që tashmë janë formatuar si hyrje, si p.sh. qeliza E3.

Shtypni ikonën Format Painter në seksionin Clipboard në anën e majtë të skedës Home. Kursori juaj do të ndryshojë në një penel.

Zgjidhni qelizën F1 për të ngjitur formatimin.

Format Painter është zakonisht për përdorim të vetëm. Pasi të keni zgjedhur qelizën, peneli do të zhduket nga kursori. Nëse dëshironi që Format Painter të bëhet "ngjitëse" dhe të aplikohet në shumë qeliza, klikoni dy herë mbi ikonën kur e zgjidhni atë nga skeda Home.

Tani, në qelizën F1, zgjidhni "Vleresimi i të dhënave" nga seksioni "Mjetet e të dhënave" të skedës "Të dhënat".

Shfaqet kutia e dialogut Vleresimi i te dhenave.

Në skedën "Cilësimet", ndryshoni menynë rënëse Lejo në Listë, përdorni miun për të zgjedhur diapazonin =$C$2:$E$2 dhe klikoni OK.

Krijimi i skenarëve rënës të vlefshmërisë së të dhënave.

Klikoni kutinë rënëse, e cila tani shfaqet pranë qelizës F1 dhe zgjidhni një nga skenarët (për shembull, Rasti bazë).

Zbatimi i formulave në skenarë

Qelizat në kolonën B janë ende duke drejtuar modelin dhe këto duhet të zëvendësohen me formula. Megjithatë, përpara se të shtoni formulat, duhet të ndryshoni formatimin e qelizave në interval për të treguar se ato përmbajnë formula, në vend të numrave të koduar. Ndiqni këto hapa:

Zgjidhni qelizat B3:B8 dhe zgjidhni ngjyrën e mbushjes nga grupi Font në skedën Home.

Ndrysho ngjyrën e mbushjes në një sfond të bardhë.

Është shumë e rëndësishme të bëhet dallimi midis formulave dhe qelizave hyrëse në një model. Duhet t'ia bëni të qartë çdo përdoruesi që hap modelin se qelizat në këtë gamë përmbajnë formula dhe nuk duhet të anashkalohen.

Tani ju duhet të zëvendësoni vlerat e koduara në kolonën B me formula që do të ndryshojnë me ndryshimin e kutisë së lëshimit. Ju mund ta bëni këtë duke përdorur një sërë funksionesh të ndryshme; një HLOOKUP, një deklaratë e mbivendosur IF, një IFS dhe një SUMIF do ta bëjnë këtë. Shtoni formulat duke ndjekur këto hapa:

Zgjidhni qelizën B3 dhe shtoni një formulë që do të ndryshojë vlerën në varësi të asaj që është në qelizën F1.

Ja se si do të jetë formula sipas opsioneve të ndryshme:

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

  Vini re se me këtë zgjidhje, ju duhet të ndryshoni numrin e indeksit të rreshtit nga 2 në 3 dhe kështu me radhë ndërsa kopjoni formulën poshtë. Në vend të kësaj, mund të përdorni një funksion ROW në fushën e tretë si kjo: =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)

  Si gjithmonë, ka disa opsione të ndryshme për të zgjedhur dhe zgjidhja më e mirë është ajo që është më e thjeshta dhe më e lehta për t'u kuptuar. Secili prej këtyre funksioneve do të prodhojë saktësisht të njëjtin rezultat, por ndryshimi i numrit të indeksit të rreshtit në HLOOKUP nuk është i fortë dhe shtimi i ROWIT mund të jetë konfuz për një përdorues. Deklarata e mbivendosur IF është e ndërlikuar për t'u ndërtuar dhe ndjekur, dhe megjithëse funksioni i ri IFS është krijuar për ta bërë më të thjeshtë një funksion IF të mbivendosur, ai është ende mjaft i pafuqishëm. SUMIF është mjaft i thjeshtë për t'u ndërtuar dhe ndjekur, dhe është e lehtë për t'u zgjeruar nëse keni nevojë të shtoni skenarë shtesë në të ardhmen.

  Vini re se IFS është një funksion i ri që disponohet vetëm me Office 365 dhe Excel 2016 ose më vonë të instaluar. Nëse e përdorni këtë funksion dhe dikush e hap këtë model në një version të mëparshëm të Excel, ajo mund ta shikojë formulën, por nuk do të jetë në gjendje ta modifikojë atë.

Kopjoni formulën në qelizën B3 poshtë kolonës.

Analiza e skenarit të përfunduar.

Duke përdorur një kopje dhe ngjitje të zakonshme, do të humbni të gjithë formatimin tuaj. Është e rëndësishme të ruani formatimin e modelit në mënyrë që të shihni me një shikim se cilat inpute janë në vlera dollarësh, përqindje ose numra klientësh. Përdorni formulat e ngjitjes për të ruajtur formatimin. Mund ta përdorni duke kopjuar qelizën në kujtesën e fragmenteve, duke theksuar diapazonin e destinacionit, duke klikuar me të djathtën dhe duke zgjedhur ikonën Paste Formulat për të ngjitur vetëm formulat dhe për ta lënë formatimin të paprekur.

Tani për pjesën argëtuese! Është koha për të testuar funksionalitetin e skenarit në model.

Klikoni në qelizën F1, ndryshoni kutinë e lëshimit dhe shikoni ndryshimet e rezultateve të modelit ndërsa kaloni midis skenarëve të ndryshëm.