Den mest brukte metoden for å bygge scenarier er å bruke en kombinasjon av formler og rullegardinbokser. I den økonomiske modellen lager du en tabell over mulige scenarier og deres input og kobler scenarionavnene til en rullegardinboks for inputceller. Inngangene til modellen er knyttet til scenariotabellen. Hvis modellen har blitt bygget riktig med alle inngangene som strømmer gjennom til utgangene, vil resultatene av modellen endres når brukeren velger forskjellige alternativer fra rullegardinboksen.
Nedtrekksbokser for datavalidering brukes til en rekke forskjellige formål i finansiell modellering, inkludert scenarioanalyse.
Bruke datavalideringer for å modellere lønnsomhetsscenarier
Last ned fil 0801.xlsx . Åpne den og velg fanen merket 8-1-start.
Slik dette har blitt modellert, er inngangene stilt opp i kolonne B. Du kan utføre sensitivitetsanalyse ganske enkelt ved å endre en av inngangene — for eksempel endre kundene per samtaleoperatør i celle B3 fra 40 til 45, og du vil se alle de avhengige tallene endres. Dette ville være en sensitivitetsanalyse, fordi du endrer bare én variabel. I stedet kommer du til å endre flere variabler samtidig i denne fullstendige scenarioanalyseøvelsen, så du må gjøre mer enn å justere noen tall manuelt.
Følg disse trinnene for å utføre en scenarioanalyse ved hjelp av rullegardinboksene for datavalidering:
Ta den nedlastede modellen og klipp og lim inn beskrivelsene fra kolonne C til kolonne F. Du kan gjøre dette ved å markere cellene C6:C8, trykke Ctrl+X, velge celle F6 og trykke Enter.
Inngangene i cellene B3 til B8 er det aktive området som driver modellen og vil forbli det. Imidlertid må de bli formler som endres avhengig av rullegardinboksen du oppretter.
Kopier området i kolonne B over til kolonnene C, D og E.
Du kan gjøre dette ved å markere B3:B8, trykke Ctrl+C, velge cellene C3:E3 og trykke Enter. Disse beløpene vil være de samme for hvert scenario til du endrer dem.
I rad 2 skriver du inn titlene Best Case , Base Case , og Worst Case .
Sette opp modellen for scenarioanalyse.
Merk at formlene fortsatt lenker til inngangene i kolonne B, som du kan se ved å velge celle C12 og trykke på F2-snarveistasten.
Rediger inndataene under hvert scenario.
Du kan sette det du tror er sannsynlig, men for å matche tallene med tallene i dette eksemplet, skriv inn verdiene. Ignorer kolonne B foreløpig.
Innspill til scenarioanalyse.
Nå må du legge til rullegardinboksen øverst, som skal drive scenariene dine. Det spiller egentlig ingen rolle hvor nøyaktig du plasserer nedtrekksboksen, men den bør være på et sted som er lett å finne, vanligvis øverst på siden.
I celle E1 skriver du inn tittelen Scenario .
Velg celle F1, og endre formateringen til input slik at brukeren kan se at denne cellen er redigerbar.
Den enkleste måten å gjøre dette på er å følge disse trinnene:
Klikk på en av cellene som allerede er formatert som en inngang, for eksempel celle E3.
Trykk på Format Painter-ikonet i utklippstavlen på venstre side av Hjem-fanen. Markøren endres til en pensel.
Velg celle F1 for å lime inn formateringen.
Format Painter er normalt for engangsbruk. Etter at du har valgt cellen, forsvinner penselen fra markøren. Hvis du vil at Format Painter skal bli "klebrig" og brukes på flere celler, dobbeltklikker du på ikonet når du velger det fra Hjem-fanen.
Nå, i celle F1, velg Datavalidering fra Dataverktøy-delen av Data-fanen.
Dialogboksen Datavalidering vises.
På fanen Innstillinger endrer du Tillat-rullegardinmenyen til Liste, bruker musen til å velge området =$C$2:$E$2, og klikker OK.
Opprette rullegardinmenyen for datavalidering.
Klikk på rullegardinboksen, som nå vises ved siden av celle F1, og velg ett av scenariene (for eksempel Base Case).
Bruke formler på scenarier
Cellene i kolonne B driver fortsatt modellen, og disse må erstattes av formler. Før du legger til formlene, bør du imidlertid endre formateringen av cellene i området for å vise at de inneholder formler, i stedet for hardkodede tall. Følg disse trinnene:
Velg cellene B3:B8, og velg Fyllfarge fra Font-gruppen på Hjem-fanen.
Endre fyllfargen til en hvit bakgrunn.
Det er veldig viktig å skille mellom formler og inndataceller i en modell. Du må gjøre det klart for enhver bruker som åpner modellen at cellene i dette området inneholder formler og ikke bør overstyres.
Nå må du erstatte de hardkodede verdiene i kolonne B med formler som endres etter hvert som rullegardinboksen endres. Du kan gjøre dette ved å bruke en rekke forskjellige funksjoner; en HLOOKUP, en nestet IF-setning, en IFS og en SUMIF vil alle gjøre susen. Legg til formlene ved å følge disse trinnene:
Velg celle B3, og legg til en formel som vil endre verdien avhengig av hva som er i celle F1.
Her er hva formelen vil være under de forskjellige alternativene:
- =OPSLAKK($F$1,$C$2:$E$8,2,0)
Merk at med denne løsningen må du endre radindeksnummeret fra 2 til 3 og så videre mens du kopierer formelen ned. I stedet kan du bruke en ROW-funksjon i det tredje feltet slik: =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)
- =SUM.HVIS($C$2:$E$2;$F$1;C3:E3)
Som alltid er det flere forskjellige alternativer å velge mellom og den beste løsningen er den som er enklest og lettest å forstå. Enhver av disse funksjonene vil gi nøyaktig det samme resultatet, men å måtte endre radindeksnummeret i HLOOKUP er ikke robust, og å legge til ROW kan være forvirrende for en bruker. Den nestede IF-setningen er vanskelig å bygge og følge, og selv om den nye IFS-funksjonen er designet for å gjøre en nestet IF-funksjon enklere, er den fortsatt ganske uhåndterlig. SUMIF er ganske enkelt å bygge og følge, og det er enkelt å utvide hvis du trenger å legge til ekstra scenarier i fremtiden.
Merk at IFS er en ny funksjon som kun er tilgjengelig med Office 365 og Excel 2016 eller nyere installert. Hvis du bruker denne funksjonen og noen åpner denne modellen i en tidligere versjon av Excel, kan hun se formelen, men hun vil ikke kunne redigere den.
Kopier formelen i celle B3 nedover i kolonnen.
Den ferdige scenarioanalysen.
Ved å bruke en vanlig kopi og lim, mister du all formatering. Det er viktig å beholde formateringen av modellen slik at du med et øyeblikk kan se hvilke inndata som er i dollarverdier, prosenter eller kundenummer. Bruk Lim inn formler for å beholde formateringen. Du kan få tilgang til den ved å kopiere cellen til utklippstavlen, markere målområdet, høyreklikke og velge Lim inn formler-ikonet for kun å lime inn formler, og la formateringen være intakt.
Nå til den morsomme delen! Det er på tide å teste scenariofunksjonaliteten i modellen.
Klikk celle F1, endre rullegardinboksen, og se modellutdataene endres mens du veksler mellom de forskjellige scenariene.