Hvordan man bygger nedfallsscenarier i den finansielle modellen - dummies

Den mest brukte metoden for å bygge scenarier er å bruke en kombinasjon av formler og rullegardiner. I den økonomiske modellen lager du en tabell med mulige scenarier og deres innganger og kobler scenenavnene til en inndata-cellefelt. Inngangene til modellen er knyttet til scenarietabellen. Hvis modellen er bygget riktig med alle innganger som strømmer gjennom til utgangene, vil resultatene av modellen endres ettersom brukeren velger forskjellige alternativer fra rullegardinlisten.

Nedtrekksrutiner for datavalidering brukes for en rekke ulike formål i økonomisk modellering, inkludert scenarioanalyse.

Bruke datavalideringer til scenarioer med lønnsomhet

Last ned fil 0801. xlsx. Åpne den og velg fanen merket 8-1-start.

Måten dette har blitt modellert, er inngangene lined opp i kolonne B. Du kan utføre følsomhetsanalyse ved å endre en av inngangene - for eksempel bytte kundene per samtaleoperatør i celle B3 fra 40 til 45, og Du ser alle de avhengige tallene endres. Dette ville være en følsomhetsanalyse, fordi du bare endrer en variabel. I stedet skal du endre flere variabler samtidig i denne fullscenaranalyseøvelsen, så du må gjøre mer enn justere noen få numre manuelt.

Følg disse trinnene for å utføre en scenarioanalyse ved hjelp av datvalideringsruller:

1. Ta den nedlastede modellen og klipp og lim inn beskrivelsene fra kolonne C til kolonne F. Du kan gjøre dette ved å markere celler C6: C8, trykk Ctrl + X, velg celle F6, og trykk Enter.

   Inngangene i cellene B3 til B8 er det aktive området som driver modellen og forblir slik. Imidlertid må de bli formler som endres avhengig av rullegardinmenyen du vil opprette.

2. 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 celler C3: E3 og trykke Enter. Disse beløpene vil være de samme for hvert scenario til du endrer dem.

3. I rad 2 skriver du inn titlene Best Case , Base Case , og verste tilfellet.

   

   Sette opp modellen for scenarioanalyse.

   Merk at formlene fortsatt knytter til inngangene i kolonne B, som du kan se ved å velge celle C12 og trykke på F2-hurtigtasten.

4. Rediger inngangene under hvert scenario.

   Du kan sette hva du tror er sannsynlig, men for å matche tallene til de i dette eksemplet, skriv inn verdiene. Ignorer kolonne B for nå.

   

   Inngang for scenarioanalyse.

   Nå må du legge til rullegardinmenyen øverst, som skal drive scenariene dine. Det spiller ingen rolle hvor nøyaktig du legger rullegardinboksen, men den skal være på et sted som er lett å finne, vanligvis øverst på siden.

5. I celle E1, skriv inn tittel Scenario .
6. Velg celle F1, og endre formateringen for å legge inn slik at brukeren kan se at denne cellen er redigerbar.

   Den enkleste måten å gjøre dette på er å følge disse trinnene:

   1. Klikk på en av cellene som allerede er formatert som en inngang, for eksempel celle E3.
   2. Trykk på ikonet Formatmaleren i Utklippstavelseksjonen på venstre side av Hjem-fanen. Markøren din vil skifte til en pensel.
   3. Velg celle F1 for å lime inn formateringen.

      Formatmaleren er normalt for engangsbruk. Etter at du har valgt cellen, vil penselen forsvinne fra markøren. Hvis du vil at Format-maleren skal bli "klebrig" og gjelde for flere celler, dobbeltklikk på ikonet når du velger det fra Hjem-fanen.

7. Nå, i celle F1, velg Data Validation fra Data Tools-delen på fanen Data.

   Dialogboksen Data Validering vises.

8. På fanen Innstillinger, endre Tillat-rullegardinmenyen til Liste, bruk musen til å velge område = $ C $ 2: $ E $ 2, og klikk OK.

   

   Opprette nedtrekksscenarier for data validering.
9. Klikk på rullegardinlisten, som nå vises ved siden av celle F1, og velg ett av scenariene (for eksempel Base Case).

Bruk av formler til scenarier

Cellene i kolonne B kjører fortsatt modellen, og disse må erstattes av formler. Før du legger til formler, bør du imidlertid endre formatering av cellene i området for å vise at de inneholder formler, i stedet for hardkodede tall. Følg disse trinnene:

1. Velg celler B3: B8, og velg Fyll farge fra Font-gruppen på Hjem-fanen.
2. Endre fyll fargen til en hvit bakgrunn.

   Det er veldig viktig å skille mellom formler og inngangsceller i en modell. Du må gjøre det klart for alle brukere 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 vil endres når rullegardinlisten endres. Du kan gjøre dette ved hjelp av en rekke forskjellige funksjoner; en HLOOKUP, en nestet IF-setning, en IFS og en SUMIF vil alle gjøre trikset. Legg til formler ved å følge disse trinnene:

1. 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:

   • = HLOOKUP ($ 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 som dette: = 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)

     Som alltid er det flere alternativer å velge mellom, og den beste løsningen er den som er den enkleste og enkleste å forstå.En hvilken som helst av disse funksjonene vil gi nøyaktig samme resultat, 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 lett å utvide hvis du trenger å legge til ekstra scenarier i fremtiden.

     Merk at IFS er en ny funksjon som bare er tilgjengelig med Office 365 og Excel 2016 eller senere installert. Hvis du bruker denne funksjonen og noen åpner denne modellen i en tidligere versjon av Excel, kan hun se formelen, men hun kan ikke redigere den.

2. Kopier formelen i celle B3 nedover kolonnen.

   

   Den fullførte scenarioanalysen.

   Ved å bruke en vanlig kopi og lim, mister du all formatering. Det er viktig å beholde formateringen av modellen slik at du raskt kan se hvilke innganger 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 destinasjonsområdet, høyreklikke og velge ikonet Lim inn formler for å lime inn formler, og la formateringen være intakt.

   Nå for den morsomme delen! Det er på tide å teste scenaritetsfunksjonaliteten i modellen.

3. Klikk på celle F1, endre rullegardinmenyen, og se på modellutgangene endres mens du bytter mellom de forskjellige scenariene.