Hoe u drop-down-scenario's in uw financieel model kunt bouwen - dummies

De meest gebruikte methode voor het bouwen van scenario's is het gebruik van een combinatie van formules en vervolgkeuzelijsten. In het financiële model maakt u een tabel met mogelijke scenario's en hun invoer en koppelt u de scenariodenamen aan een vervolgkeuzelijst met invoercellen. De ingangen van het model zijn gekoppeld aan de scantabel. Als het model op de juiste manier is gebouwd en alle ingangen naar de uitgangen lopen, veranderen de resultaten van het model wanneer de gebruiker verschillende opties in de vervolgkeuzelijst selecteert.

De vervolgkeuzevakken voor gegevensvalidatie worden voor een aantal verschillende doeleinden gebruikt bij financiële modellering, inclusief scenario-analyse.

Gegevensvalidaties gebruiken om winstgevendheidscenario's te modelleren

Bestand downloaden 0801. xlsx. Open het en selecteer het tabblad met het label 8-1-start.

Op de manier waarop dit is gemodelleerd, staan ​​de ingangen in kolom B opgesteld. U kunt een gevoeligheidsanalyse uitvoeren door een van de ingangen te wijzigen, bijvoorbeeld de klanten per oproepoperator in cel B3 wijzigen van 40 in 45 en je ziet alle afhankelijke getallen veranderen. Dit zou een gevoeligheidsanalyse zijn, omdat u slechts één variabele wijzigt. In plaats daarvan gaat u tegelijkertijd meerdere variabelen wijzigen in deze volledige scenarioanalyseoefening, dus u moet meer doen dan een paar getallen handmatig aanpassen.

Voer de volgende stappen uit om een ​​scenario-analyse uit te voeren met behulp van de vervolgkeuzevakjes voor gegevensvalidatie:

1. Neem het gedownloade model en knip de beschrijving uit kolom C in kolom F. U kunt dit doen door cellen C6: C8 te markeren, op Ctrl + X te drukken, cel F6 te selecteren en op Enter te drukken.

   De invoer in de cellen B3 tot en met B8 is het actieve bereik dat het model aandrijft en blijft dat ook. Ze moeten echter formules worden die veranderen afhankelijk van de vervolgkeuzelijst die u gaat maken.

2. Kopieer het bereik in kolom B naar de kolommen C, D en E.

   U kunt dit doen door B3: B8 te markeren, op Ctrl + C te drukken, cellen C3: E3 te selecteren en op Enter te drukken. Deze bedragen zijn hetzelfde voor elk scenario totdat u ze wijzigt.

3. Voer in rij 2 de titels Best Case , Base Case , en Worst Case in.

   

   Het model instellen voor scenario-analyse.

   Houd er rekening mee dat de formules nog steeds verwijzen naar de invoer in kolom B, zoals u kunt zien door cel C12 te selecteren en op de sneltoets F2 te drukken.

4. Bewerk de ingangen onder elk scenario.

   U kunt alles plaatsen waarvan u denkt dat het waarschijnlijk is, maar voer de waarden in om de nummers aan te passen aan die in dit voorbeeld. Kolom B voorlopig negeren.

   

   Ingangen voor scenario-analyse.

   Nu moet u de vervolgkeuzelijst bovenaan toevoegen, die uw scenario's gaat besturen. Het maakt niet echt uit waar je de vervolgkeuzelijst precies plaatst, maar het moet op een locatie zijn die gemakkelijk te vinden is, meestal bovenaan de pagina.

5. Voer in cel E1 de titel Scenario in.
6. Selecteer cel F1 en wijzig de opmaak in invoer zodat de gebruiker kan zien dat deze cel bewerkbaar is.

   De eenvoudigste manier om dit te doen, is door deze stappen te volgen:

   1. Klik op een van de cellen die al zijn opgemaakt als invoer, zoals cel E3.
   2. Druk op het pictogram Opmaak Painter in het gedeelte Klembord aan de linkerkant van het tabblad Home. Je cursor verandert in een penseel.
   3. Selecteer cel F1 om de opmaak te plakken.

      Opmaak Painter is normaal gesproken voor eenmalig gebruik. Nadat je de cel hebt geselecteerd, verdwijnt het penseel van de cursor. Als u wilt dat de opmaakfunctie "plakkerig" wordt en op meerdere cellen van toepassing is, dubbelklikt u op het pictogram wanneer u het op het tabblad Start selecteert.

7. Selecteer in cel F1 Gegevensvalidatie in het gedeelte Gegevenshulpmiddelen van het tabblad Gegevens.

   Het dialoogvenster Gegevensvalidatie wordt weergegeven.

8. Wijzig het dropdown-menu Toestaan ​​op het tabblad Instellingen, gebruik de muis om het bereik = $ C $ 2: $ E $ 2 te selecteren en klik op OK.

   

   De gegevensvalidatieschema's maken.
9. Klik op de vervolgkeuzelijst die nu naast cel F1 verschijnt en selecteer een van de scenario's (bijvoorbeeld Base Case).

Formules toepassen op scenario's

De cellen in kolom B besturen nog steeds het model en deze moeten worden vervangen door formules. Voordat u de formules toevoegt, moet u echter de opmaak van de cellen in het bereik wijzigen om aan te geven dat ze formules bevatten, in plaats van hardcodes. Volg deze stappen:

1. Selecteer cellen B3: B8 en selecteer de vulkleur in de groep Lettertype op het tabblad Start.
2. Wijzig de vulkleur in een witte achtergrond.

   Het is erg belangrijk om onderscheid te maken tussen formules en invoercellen in een model. U moet aan elke gebruiker die het model opent duidelijk maken dat de cellen in dit bereik formules bevatten en niet moeten worden opgeheven.

Nu moet u de hardgecodeerde waarden in kolom B vervangen door formules die veranderen wanneer de vervolgkeuzelijst verandert. U kunt dit doen met behulp van een aantal verschillende functies; een HORIZ.ZOEKEN, een geneste IF-instructie, een IFS en een SOM.IF doen allemaal de truc. Voeg de formules toe door deze stappen te volgen:

1. Selecteer cel B3 en voeg een formule toe die de waarde wijzigt afhankelijk van wat zich in cel F1 bevindt.

   Hier volgt de formule onder de verschillende opties:

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

     Houd er rekening mee dat u met deze oplossing moet wijzigen het rijindexnummer van 2 tot 3 enzovoort, terwijl u de formule naar beneden kopieert. In plaats daarvan zou u een RIJ-functie in het derde veld als volgt kunnen gebruiken: = HORIZ.ZOEKEN ($ F $ 1, $ C $ 2: $ E $ 8, RIJ (A3) -1, 0)

   • = ALS ($ 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)
   • = SOMM ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)

     Zoals altijd zijn er verschillende opties om uit te kiezen en de beste oplossing is die is de eenvoudigste en gemakkelijkst te begrijpen.Elk van deze functies zal exact hetzelfde resultaat opleveren, maar het rijindexnummer in het HLOOKUP moet niet robuust zijn en het toevoegen van ROW kan verwarrend zijn voor een gebruiker. De geneste IF-instructie is lastig te bouwen en te volgen, en hoewel de nieuwe IFS-functie is ontworpen om een ​​geneste ALS-functie eenvoudiger te maken, is deze nog steeds nogal onpraktisch. De SUMIF is vrij eenvoudig te bouwen en te volgen en het is eenvoudig uit te breiden als u in de toekomst extra scenario's moet toevoegen.

     Merk op dat IFS een nieuwe functie is die alleen beschikbaar is met Office 365 en Excel 2016 of later geïnstalleerd. Als u deze functie gebruikt en iemand dit model opent in een vorige versie van Excel, kan zij de formule bekijken, maar ze kan deze niet bewerken.

2. Kopieer de formule in cel B3 naar beneden in de kolom.

   

   De voltooide scenario-analyse.

   Als u een gewone kopie en een plak gebruikt, raakt u al uw opmaak kwijt. Het is belangrijk om de opmaak van het model te behouden, zodat u in één oogopslag kunt zien welke invoer in dollarwaarden, percentages of klantnummers voorkomt. Gebruik Formules plakken om de opmaak te behouden. U kunt het openen door de cel naar het klembord te kopiëren, het doelbereik te markeren, met de rechtermuisknop te klikken en het pictogram Formules plakken te selecteren om alleen de formules in te voegen en de opmaak intact te laten.

   Nu voor het leuke gedeelte! Het is tijd om de scenariofunctionaliteit in het model te testen.

3. Klik op cel F1, wijzig de vervolgkeuzelijst en zie de modeluitgangen veranderen terwijl u wisselt tussen de verschillende scenario's.