Hur man bygger nedfallsscenarier i din finansiella modell - dummies

Den vanligaste metoden för att bygga scenarier är att använda en kombination av formler och rullgardinslådor. I den finansiella modellen skapar du en tabell över möjliga scenarier och deras ingångar och länkar scenariansnamnen till en inmatningscells rullgardinsmeny. Ingångarna till modellen är kopplade till scenariotabellen. Om modellen har byggts ordentligt med alla ingångar som strömmar till utgångarna, kommer resultaten av modellen att ändras när användaren väljer olika alternativ i rullgardinsmenyn.

Valideringsrutiner för datvalidering används för ett antal olika ändamål i finansiell modellering, inklusive scenarioanalys.

Använda datavalideringar till scenario lönsamhetsscenarier

Hämta fil 0801. xlsx. Öppna den och välj fliken märkt 8-1-start.

Hur detta har modellerats är inmatningarna upptagna i kolumn B. Du kan utföra känslighetsanalys genom att ändra en av ingångarna, till exempel byta kunder per anropsoperatör i cell B3 från 40 till 45 och Du får se alla de beroende numren ändras. Detta skulle vara en känslighetsanalys eftersom du bara ändrar en variabel. I stället kommer du att ändra flera variabler samtidigt i denna fullständiga scenariotanalys, så du måste göra mer än justera några siffror manuellt.

Så här utför du en scenarioanalys med nedrullningsrutor för datvalidering:

1. Ta den nedladdade modellen och klipp och klistra in beskrivningarna från kolumn C till kolumn F. Du kan göra det här genom att markera celler C6: C8, tryck Ctrl + X, välj cell F6 och tryck Enter.

   Inmatningarna i cellerna B3 till B8 är det aktiva området som driver modellen och kommer att förbli så. Men de måste bli formuleringar som ändras beroende på rullgardinsmenyn som du skapar.

2. Kopiera intervallet i kolumn B över kolumnerna C, D och E.

   Du kan göra detta genom att markera B3: B8, trycka Ctrl + C, välja celler C3: E3 och tryck på Enter. Dessa belopp kommer att vara desamma för varje scenario tills du ändrar dem.

3. I rad 2 skriver du titlarna Best Case , Base Case , och värsta fallet.

   

   Konfigurera modellen för scenarioanalys.

   Observera att formlerna fortfarande länkar till ingångarna i kolumn B, vilket du kan se genom att välja cell C12 och trycka på genvägsknappen F2.

4. Redigera ingångarna under varje scenario.

   Du kan lägga vad du tror är troligt, men för att matcha siffrorna med dem i det här exemplet anger du värdena. Ignorera kolumn B för nu.

   

   Ingångar för scenarioanalys.

   Nu måste du lägga till rullgardinsmenyn högst upp, som ska driva dina scenarier. Det spelar ingen roll hur exakt du lägger ned rullgardinsmenyn, men den borde vara på en plats som är lätt att hitta, vanligtvis högst upp på sidan.

5. I cell E1 anger du scenariot Scenario .
6. Välj cell F1 och ändra formateringen för att mata in så att användaren kan se att den här cellen kan redigeras.

   Det enklaste sättet att göra detta är att följa dessa steg:

   1. Klicka på en av de celler som redan är formaterade som en inmatning, till exempel E3-cellen.
   2. Tryck på ikonen Formaterare på klippbordet på vänster sida av fliken Hem. Din markör ändras till en pensel.
   3. Välj cell F1 för att klistra in formateringen.

      Format Painter är normalt för engångsbruk. När du har valt cellen kommer pensel att försvinna från markören. Om du vill att formateraren ska bli "klibbig" och tillämpa på flera celler, dubbelklicka på ikonen när du väljer den från fliken Hem.

7. Välj nu i cell F1 Data Validation från Datatools-delen på fliken Data.

   Dialogrutan Data Validation visas.

8. På fliken Inställningar ändrar du Tillåt nedrullning till Lista, använd musen för att välja intervallet = $ C $ 2: $ E $ 2 och klicka på OK.

   

   Skapa nedrullningsscenarier för datavalidering.
9. Klicka på rullgardinsmenyn, som nu visas bredvid cellen F1, och välj ett av scenarierna (till exempel basfall).

Använda formler till scenarier

Cellerna i kolumn B kör fortfarande modellen, och dessa måste ersättas med formler. Innan du lägger till formlerna, bör du ändra formateringen av cellerna i intervallet för att visa att de innehåller formler istället för hårdkodade nummer. Följ dessa steg:

1. Markera celler B3: B8 och välj Fyll färg från Font-gruppen på fliken Hem.
2. Ändra fyllningsfärgen till en vit bakgrund.

   Det är mycket viktigt att skilja mellan formler och inmatningsceller i en modell. Du måste klargöra för alla användare som öppnar modellen att cellerna i det här intervallet innehåller formler och inte ska överstiga.

Nu måste du ersätta de hårdkodade värdena i kolumn B med formler som kommer att ändras när rullgardinsmenyn ändras. Du kan göra detta med ett antal olika funktioner; en HLOOKUP, ett nestat IF-uttalande, en IFS och en SUMIF kommer alla att göra tricket. Lägg till formlerna genom att följa dessa steg:

1. Välj cell B3 och lägg till en formel som ändrar värdet beroende på vad som finns i cell F1.

   Här är vad formeln kommer att ligga under de olika alternativen:

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

     Observera att med den här lösningen måste du byta raden index nummer från 2 till 3 och så vidare när du kopierar formeln ner. I stället kan du använda en ROW-funktion i det tredje fältet så här: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1, 0)

   • = IF ($ F $ 1 = $ $ 2 $, $ 3 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ E $ 2, E3)
   • = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)
   • Som alltid finns det flera olika alternativ att välja mellan, och den bästa lösningen är den som är det enklaste och enklaste att förstå.Någon av dessa funktioner kommer att producera exakt samma resultat, men det är inte robust att byta raden indexnummer i HLOOKUP, och att lägga till ROW kan vara förvirrande för en användare. Det nestade IF-uttalandet är knepigt att bygga och följa, och även om den nya IFS-funktionen är utformad för att göra en nestad IF-funktion enklare, är den fortfarande ganska otrevlig. SUMIF är ganska enkelt att bygga och följa, och det är lätt att expandera om du behöver lägga till extra scenarier i framtiden.

     Observera att IFS är en ny funktion som endast är tillgänglig med Office 365 och Excel 2016 eller senare installerad. Om du använder den här funktionen och någon öppnar den här modellen i en tidigare version av Excel kan hon visa formeln, men hon kommer inte att kunna redigera den.

     Kopiera formeln i cell B3 nerför kolumnen.

2. Den slutförda scenarioanalysen.

   

   Genom att använda en vanlig kopia och klistra in, förlorar du all din formatering. Det är viktigt att behålla formateringen av modellen så att du snabbt kan se vilka inmatningar som finns i dollarvärden, procentsatser eller kundnummer. Använd Klistra formulär för att behålla formateringen. Du kan komma åt det genom att kopiera cellen till klippbordet, markera destinationsområdet, högerklicka och välja ikonen Klistra formulär för att bara klistra in formler och lämna formateringen intakt.

   Nu för den roliga delen! Det är dags att testa scenariets funktionalitet i modellen.

   Klicka på cell F1, ändra rullgardinsmenyn och titta på modellutgångarna ändras när du växlar mellan de olika scenarierna.