Kako izgraditi padajuće scenarije u svom financijskom modelu

Najčešće korištena metoda izrade scenarija je korištenje kombinacije formula i padajućih okvira. U financijskom modelu stvarate tablicu mogućih scenarija i njihovih ulaza te povezujete nazive scenarija s padajućim okvirom ćelije za unos. Unosi modela povezani su s tablicom scenarija. Ako je model pravilno izgrađen sa svim ulazima koji prolaze kroz izlaze, tada će se rezultati modela promijeniti kako korisnik odabere različite opcije iz padajućeg okvira.

Padajući okviri za provjeru valjanosti podataka koriste se za brojne različite svrhe u financijskom modeliranju, uključujući analizu scenarija.

Korištenje provjere valjanosti podataka za modeliranje scenarija profitabilnosti

Preuzmite datoteku 0801.xlsx . Otvorite ga i odaberite karticu s oznakom 8-1-start.

Kako je ovo modelirano, ulazi su poredani u stupcu B. Analizu osjetljivosti možete izvesti jednostavno promjenom jednog od ulaza — na primjer, promijenite korisnika po operateru poziva u ćeliji B3 s 40 na 45 i vidjeti kako se mijenjaju svi zavisni brojevi. Ovo bi bila analiza osjetljivosti, jer mijenjate samo jednu varijablu. Umjesto toga, promijenit ćete više varijabli odjednom u ovoj vježbi analize scenarija, tako da ćete morati učiniti više od ručnog podešavanja nekoliko brojeva.

Da biste izvršili analizu scenarija pomoću padajućih okvira za provjeru valjanosti podataka, slijedite ove korake:

Uzmite preuzeti model i izrežite i zalijepite opise iz stupca C u stupac F. To možete učiniti tako da označite ćelije C6:C8, pritisnete Ctrl+X, odaberete ćeliju F6 i pritisnete Enter.

Ulazi u ćelijama B3 do B8 aktivni su raspon koji pokreće model i tako će ostati. Međutim, one moraju postati formule koje se mijenjaju ovisno o padajućem okviru koji ćete izraditi.

Kopirajte raspon iz stupca B u stupce C, D i E.

To možete učiniti tako da označite B3:B8, pritisnete Ctrl+C, odaberete ćelije C3:E3 i pritisnete Enter. Ti će iznosi biti isti za svaki scenarij dok ih ne promijenite.

U redu dva unijeti naslove najboljem slučaju , osnovni scenarij , a najgori slučaj.

Postavljanje modela za analizu scenarija.

Imajte na umu da se formule još uvijek povezuju s ulazima u stupcu B, kao što možete vidjeti odabirom ćelije C12 i pritiskom tipke prečaca F2.

Uredite unose ispod svakog scenarija.

Možete staviti što god smatrate vjerojatnim, ali da biste uskladili brojeve s onima u ovom primjeru, unesite vrijednosti. Zanemarite stupac B za sada.

Ulazi za analizu scenarija.

Sada morate dodati padajući okvir na vrhu, koji će pokretati vaše scenarije. Zapravo nije važno gdje točno stavite padajući okvir, ali on bi trebao biti na mjestu koje je lako pronaći, obično na vrhu stranice.

U ćeliju E1 unesite naslov Scenarij .

Odaberite ćeliju F1 i promijenite formatiranje na unos tako da korisnik može vidjeti da se ova ćelija može uređivati.

Najlakši način da to učinite je da slijedite ove korake:

Kliknite jednu od ćelija koje su već formatirane kao ulaz, kao što je ćelija E3.

Pritisnite ikonu Format Painter u odjeljku Međuspremnik na lijevoj strani kartice Početna. Vaš kursor će se promijeniti u kist.

Odaberite ćeliju F1 da biste zalijepili oblikovanje.

Format Painter je obično za jednokratnu upotrebu. Nakon što odaberete ćeliju, kist će nestati s kursora. Ako želite da Format Painter postane "ljepljiv" i primjenjuje se na više ćelija, dvaput kliknite ikonu kada je odaberete na kartici Početna.

Sada, u ćeliji F1, odaberite Provjera valjanosti podataka u odjeljku Alati za podatke na kartici Podaci.

Pojavljuje se dijaloški okvir Provjera valjanosti podataka.

Na kartici Postavke promijenite padajući izbornik Dopusti u Popis, mišem odaberite raspon =$C$2:$E$2 i kliknite U redu.

Izrada padajućih scenarija za provjeru valjanosti podataka.

Kliknite padajući okvir koji se sada pojavljuje pored ćelije F1 i odaberite jedan od scenarija (na primjer, Osnovni slučaj).

Primjena formula na scenarije

Ćelije u stupcu B još uvijek pokreću model i treba ih zamijeniti formulama. Međutim, prije nego što dodate formule, trebali biste promijeniti oblikovanje ćelija u rasponu kako biste pokazali da sadrže formule umjesto tvrdo kodiranih brojeva. Prati ove korake:

Odaberite ćelije B3:B8 i odaberite boju ispune iz grupe Font na kartici Početna.

Promijenite boju ispune u bijelu pozadinu.

Vrlo je važno razlikovati formule i ulazne ćelije u modelu. Morate jasno dati do znanja svakom korisniku koji otvara model da ćelije u ovom rasponu sadrže formule i da se ne smiju nadjačati.

Sada morate zamijeniti tvrdo kodirane vrijednosti u stupcu B formulama koje će se mijenjati kako se padajući okvir mijenja. To možete učiniti pomoću niza različitih funkcija; HLOOKUP, ugniježđeni IF naredba, IFS i SUMIF će sve učiniti trik. Dodajte formule slijedeći ove korake:

Odaberite ćeliju B3 i dodajte formulu koja će promijeniti vrijednost ovisno o tome što se nalazi u ćeliji F1.

Evo kakva će formula biti pod različitim opcijama:

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

  Imajte na umu da s ovim rješenjem morate promijeniti broj indeksa retka s 2 na 3 i tako dalje dok kopirate formulu prema dolje. Umjesto toga, možete koristiti funkciju ROW u trećem polju ovako: =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)

  Kao i uvijek, postoji nekoliko različitih opcija koje možete izabrati, a najbolje rješenje je ono koje je najjednostavnije i najlakše razumjeti. Bilo koja od ovih funkcija proizvest će potpuno isti rezultat, ali potreba za promjenom broja indeksa retka u HLOOKUP-u nije robusna, a dodavanje ROW-a može biti zbunjujuće za korisnika. Ugniježđenu IF naredbu je teško izgraditi i slijediti, i iako je nova funkcija IFS dizajnirana da učini ugniježđenu IF funkciju jednostavnijom, još uvijek je prilično nezgrapna. SUMIF je prilično jednostavan za izgradnju i praćenje, a lako ga je proširiti ako trebate dodati dodatne scenarije u budućnosti.

  Imajte na umu da je IFS nova funkcija koja je dostupna samo s instaliranim Office 365 i Excel 2016 ili novijim. Ako koristite ovu funkciju i netko otvori ovaj model u prethodnoj verziji Excela, može vidjeti formulu, ali je neće moći uređivati.

Kopirajte formulu u ćeliju B3 niz stupac.

Završena analiza scenarija.

Korištenjem običnog kopiranja i lijepljenja izgubit ćete sve svoje oblikovanje. Važno je zadržati formatiranje modela tako da možete na prvi pogled vidjeti koji su ulazi u dolarskim vrijednostima, postocima ili brojevima kupaca. Koristite Zalijepi formule da zadržite oblikovanje. Možete joj pristupiti kopiranjem ćelije u međuspremnik, isticanjem odredišnog raspona, desnim klikom i odabirom ikone Zalijepi formule da biste zalijepili samo formule, a oblikovanje ostavili netaknutim.

Sada za zabavni dio! Vrijeme je da testiramo funkcionalnost scenarija u modelu.

Kliknite ćeliju F1, promijenite padajući okvir i gledajte kako se izlazi modela mijenjaju dok prelazite između različitih scenarija.