Hvordan man bygger nedfaldsscenarier i din finansielle model - dummies

Den mest almindeligt anvendte metode til opbygning af scenarier er at bruge en kombination af formler og rullelister. I den finansielle model opretter du en tabel over mulige scenarier og deres input og knytter scenenavnene til en drop-down-boks med inputcelle. Indgangene til modellen er knyttet til scenarietabellen. Hvis modellen er bygget korrekt med alle indgange, der strømmer gennem til udgange, ændres resultaterne af modellen, da brugeren vælger forskellige muligheder i rullemenuen.

Valideringsvalider for data validering anvendes til en række forskellige formål i finansiel modellering, herunder scenarioanalyse.

Brug af data valideringer til scenarier for rentabilitet af modeller

Download fil 0801. xlsx. Åbn den og vælg fanen mærket 8-1-start.

Den måde, hvorpå dette er blevet modelleret, er indgange anbragt i kolonne B. Du kan udføre følsomhedsanalyse ved blot at ændre en af ​​indgangene - for eksempel ændre kunderne pr. Opkaldsoperatør i celle B3 fra 40 til 45, og du vil se alle de afhængige tal ændre sig. Dette ville være en følsomhedsanalyse, fordi du kun ændrer en variabel. I stedet skal du ændre flere variabler på én gang i denne scenarieanalyse, så du skal gøre mere end justere et par tal manuelt.

Følg disse trin for at udføre en scenarieanalyse ved hjælp af datvalideringsruller:

1. Tag den downloadede model og klipp og indsæt beskrivelserne fra kolonne C til kolonne F. Du kan gøre dette ved at fremhæve cellerne C6: C8, trykke Ctrl + X, vælge celle F6 og trykke Enter.

   Indgangene i cellerne B3 til B8 er det aktive område, der driver modellen og forbliver så. Men de skal være formler, der ændres afhængigt af rullemenuen, som du opretter.

2. Kopier rækkevidden i kolonne B over til kolonne C, D og E.

   Du kan gøre dette ved at fremhæve B3: B8, trykke på Ctrl + C, vælge celler C3: E3 og trykke på Enter. Disse beløb vil være de samme for hvert scenario, indtil du ændrer dem.

3. I række 2 indtaster titlerne Best Case , Base Case , og værste tilfælde.

   

   Opsætning af modellen til scenarioanalyse.

   Bemærk, at formlerne stadig linker til inputene i kolonne B, som du kan se ved at vælge celle C12 og trykke på F2 genvejstasten.

4. Rediger inputene under hvert scenario.

   Du kan lægge det, du tror, ​​er sandsynligt, men for at matche tallene til dem i dette eksempel skal du indtaste værdierne. Ignorer kolonne B for nu.

   

   Indgange til scenarieanalyse.

   Nu skal du tilføje rullemenuen øverst, som skal drive dine scenarier. Det er ligegyldigt, hvor præcis du lægger rullemenuen, men den skal være på et sted, der er let at finde, normalt øverst på siden.

5. I celle E1 skal du indtaste titel Scenario .
6. Vælg celle F1, og skift formateringen for at indtaste, så brugeren kan se, at denne celle er redigerbar.

   Den nemmeste måde at gøre dette på er at følge disse trin:

   1. Klik på en af ​​de celler, der allerede er formateret som en indtastning, f.eks. Celle E3.
   2. Tryk på ikonet Formatmaleren i afsnittet Udklipsholder på venstre side af fanen Startside. Din markør vil skifte til en pensel.
   3. Vælg celle F1 for at indsætte formateringen.

      Format Maleren er normalt til engangsbrug. Når du har valgt cellen, vil pensel forsvinde fra markøren. Hvis du vil have Format Painter til at blive "klæbrig" og gælder for flere celler, skal du dobbeltklikke på ikonet, når du vælger det fra fanen Startside.

7. Vælg nu Data Validation fra Data Tools i fanen Data i celle F1.

   Dialogboksen Data Validation vises.

8. På fanen Indstillinger skal du ændre rullemenuen Tillad til Liste, Brug musen til at vælge område = $ C $ 2: $ E $ 2, og klik på OK.

   

   Oprettelse af data validering drop-down scenarier.
9. Klik på rullemenuen, som nu vises ved siden af ​​celle F1, og vælg et af scenarierne (f.eks. Base Case).

Anvendelse af formler til scenarier

Cellerne i kolonne B kører stadig modellen, og disse skal erstattes af formler. Før du tilføjer formlerne, skal du dog ændre formateringen af ​​cellerne i området for at vise, at de indeholder formler i stedet for hardkodede tal. Følg disse trin:

1. Vælg celler B3: B8, og vælg Fyld farve fra Font-gruppen på fanen Startside.
2. Skift Fill Color til en hvid baggrund.

   Det er meget vigtigt at skelne mellem formler og inputceller i en model. Du skal gøre det klart for enhver bruger, der åbner modellen, at cellerne i dette interval indeholder formler og ikke bør tilsidesættes.

Nu skal du erstatte de hardkodede værdier i kolonne B med formler, der vil ændre sig, når rullemenuen ændres. Du kan gøre dette ved hjælp af en række forskellige funktioner; en HLOOKUP, en indlejret IF-erklæring, en IFS og en SUMIF vil alle gøre tricket. Tilføj formlerne ved at følge disse trin:

1. Vælg celle B3, og tilføj en formel, der vil ændre værdien afhængigt af hvad der er i celle F1.

   Her er hvad formlen vil være under de forskellige muligheder:

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

     Bemærk, at du med denne løsning skal ændre rækkeindeksnummeret fra 2 til 3 og så videre, mens du kopierer formlen nedad. I stedet kan du bruge en ROW-funktion i det tredje felt 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 altid er der flere forskellige muligheder at vælge imellem, og den bedste løsning er den er den enkleste og nemmeste at forstå.Enhver af disse funktioner vil producere nøjagtigt det samme resultat, men at skulle ændre rækkeindeksnummeret i HLOOKUP'en er ikke robust, og at tilføje ROW'en kan være forvirrende for en bruger. Den indlejrede IF-erklæring er vanskelig at bygge og følge, og selvom den nye IFS-funktion er designet til at gøre en nestet IF-funktion enklere, er den stadig ret uhåndterlig. SUMIF er ret nemt at opbygge og følge, og det er nemt at udvide, hvis du skal tilføje ekstra scenarier i fremtiden.

     Bemærk, at IFS er en ny funktion, der kun er tilgængelig med Office 365 og Excel 2016 eller senere installeret. Hvis du bruger denne funktion, og nogen åbner denne model i en tidligere version af Excel, kan hun se formlen, men hun kan ikke redigere den.

2. Kopier formlen i celle B3 ned ad søjlen.

   

   Den afsluttede scenarioanalyse.

   Ved at bruge en almindelig kopi og indsæt, vil du miste al din formatering. Det er vigtigt at beholde formateringen af ​​modellen, så du hurtigt kan se hvilke indgange der er i dollarværdier, procentsatser eller kundenumre. Brug Indsæt formler for at bevare formateringen. Du kan få adgang til det ved at kopiere cellen til udklipsholderen, fremhæve destinationsområdet, højreklikke og vælge ikonet Indsæt formularer for kun at indsætte formler og lade formateringen være intakt.

   Nu for den sjove del! Det er på tide at teste scenaritetsfunktionaliteten i modellen.

3. Klik på celle F1, skift rullelisten, og se modeludgangene ændres, mens du skifter mellem de forskellige scenarier.