Gevoeligheidsanalyse in Excel (voorbeeldgegevensblad)

Processen op het gebied van finance zijn altijd met elkaar verbonden, de ene factor is afhankelijk van de andere en verandert mee. Houd deze wijzigingen bij en begrijp wat u in de toekomst kunt verwachten, misschien met behulp van Excel-functies en spreadsheetmethoden.

Meerdere resultaten krijgen met een gegevenstabel

Gegevensbladmogelijkheden zijn elementen van wat-als-analyse, vaak gedaan via Microsoft Excel. Dit is de tweede naam voor gevoeligheidsanalyse.

Overzicht

Een gegevenstabel is een type celbereik dat kan worden gebruikt om problemen op te lossen door de waarden in sommige cellen te wijzigen. Het wordt gemaakt wanneer het nodig is om wijzigingen in de componenten van de formule bij te houden en updates van de resultaten te ontvangen, volgens deze wijzigingen. Laten we eens kijken hoe we datatabellen in onderzoek kunnen gebruiken en welke typen dat zijn.

Basisprincipes van gegevenstabellen

Er zijn twee soorten gegevenstabellen, ze verschillen in het aantal componenten. U moet een tabel samenstellen met een focus op het aantal waarden dat u ermee moet controleren.

Statistici gebruiken een enkele variabeletabel wanneer er slechts één variabele in een of meer uitdrukkingen is die hun resultaat kan veranderen. Het wordt bijvoorbeeld vaak gebruikt in combinatie met de PMT-functie. De formule is ontworpen om het bedrag van de periodieke betaling te berekenen en houdt rekening met de rentevoet die in de overeenkomst is vermeld. Bij dergelijke berekeningen worden de variabelen in de ene kolom geschreven en de resultaten van de berekeningen in een andere. Een voorbeeld van een typeplaatje met 1 variabele:

Beschouw vervolgens de platen met 2 variabelen. Ze worden gebruikt in gevallen waarin twee factoren de verandering in een indicator beïnvloeden. De twee variabelen kunnen in een andere bij de lening behorende tabel terechtkomen, die kan worden gebruikt om de optimale aflossingstermijn en de hoogte van de maandelijkse betaling te bepalen. In deze berekening moet u ook de PMT-functie gebruiken. Een voorbeeld van een tabel met 2 variabelen:

Een gegevenstabel maken met één variabele

Beschouw de analysemethode aan de hand van het voorbeeld van een kleine boekhandel met slechts 100 boeken op voorraad. Sommigen van hen kunnen duurder worden verkocht ($50), de rest kost kopers minder ($20). Het totale inkomen uit de verkoop van alle goederen wordt berekend - de eigenaar besloot dat hij 60% van de boeken tegen een hoge prijs zou verkopen. U moet uitzoeken hoe de omzet zal toenemen als u de prijs van een groter volume goederen verhoogt - 70%, enzovoort.

Let op! De totale omzet moet worden berekend met een formule, anders is het niet mogelijk om een ​​gegevenstabel samen te stellen.

1. Selecteer een vrije cel weg van de rand van het blad en schrijf de formule erin: = Cel van de totale omzet. Als inkomen bijvoorbeeld is geschreven in cel C14 (willekeurige aanduiding is aangegeven), moet u dit schrijven: =S14.
2. We schrijven het percentage van het goederenvolume in de kolom links van deze cel - niet eronder, dit is erg belangrijk.
3. We selecteren het cellenbereik waar de percentagekolom en de link naar het totale inkomen zich bevinden.

4. We vinden op het tabblad "Gegevens" het item "Wat als analyse" en klikken erop - in het menu dat wordt geopend, selecteert u de optie "Gegevenstabel".

5. Er wordt een klein venster geopend waarin u een cel moet opgeven met het percentage boeken dat aanvankelijk tegen een hoge prijs is verkocht in de kolom "Waarden vervangen door rijen in ...". Deze stap wordt gedaan om de totale omzet opnieuw te berekenen, rekening houdend met het stijgende percentage.

Nadat u op de knop "OK" hebt geklikt in het venster waar de gegevens zijn ingevoerd voor het samenstellen van de tabel, verschijnen de resultaten van de berekeningen in de regels.

Een formule toevoegen aan een enkele variabele gegevenstabel

Van een tabel die hielp bij het berekenen van een actie met slechts één variabele, kunt u een geavanceerd analysehulpmiddel maken door een extra formule toe te voegen. Het moet worden ingevoerd naast een reeds bestaande formule - als de tabel bijvoorbeeld rijgeoriënteerd is, voeren we de uitdrukking in de cel rechts van de bestaande in. Wanneer de kolomoriëntatie is ingesteld, schrijven we de nieuwe formule onder de oude. Volg vervolgens het algoritme:

1. Selecteer het cellenbereik opnieuw, maar nu zou het de nieuwe formule moeten bevatten.
2. Open het analysemenu "wat als" en selecteer "Datablad".
3. We voegen een nieuwe formule toe aan het overeenkomstige veld in rijen of kolommen, afhankelijk van de oriëntatie van de plaat.

Maak een gegevenstabel met twee variabelen

Het begin van zo'n tabel is iets anders - je moet een link plaatsen naar de totale omzet boven de procentuele waarden. Vervolgens voeren we deze stappen uit:

1. Schrijf prijsopties in één regel met een link naar inkomen - één cel voor elke prijs.
2. Selecteer een celbereik.

3. Open het gegevenstabelvenster, zoals bij het samenstellen van een tabel met één variabele - via het tabblad "Gegevens" op de werkbalk.
4. Vervang in de kolom "Waarden vervangen door kolommen in ..." een cel met een aanvankelijk hoge prijs.
5. Voeg een cel met het initiële percentage van de verkoop van dure boeken toe aan de kolom "Waarden vervangen door rijen in ..." en klik op "OK".

Hierdoor is de hele tabel gevuld met bedragen van mogelijke inkomsten met verschillende voorwaarden voor de verkoop van goederen.

Berekeningen versnellen voor werkbladen met gegevenstabellen

Als u snelle berekeningen in een gegevenstabel nodig hebt die niet leiden tot een herberekening van de hele werkmap, zijn er een paar dingen die u kunt doen om het proces te versnellen.

1. Open het optievenster, selecteer het item "Formules" in het menu aan de rechterkant.
2. Selecteer het item "Automatisch, behalve gegevenstabellen" in het gedeelte "Berekeningen in de werkmap".

3. Laten we de resultaten in de tabel handmatig herberekenen. Selecteer hiervoor de formules en druk op de F-toets.

Andere hulpmiddelen voor het uitvoeren van gevoeligheidsanalyses

Er zijn andere hulpmiddelen in het programma om u te helpen bij het uitvoeren van gevoeligheidsanalyses. Ze automatiseren sommige acties die anders handmatig zouden moeten worden gedaan.

1. De functie "Parameterselectie" is geschikt als het gewenste resultaat bekend is en u de invoerwaarde van de variabele moet weten om een ​​dergelijk resultaat te verkrijgen.
2. "Zoeken naar een oplossing" is een add-on voor het oplossen van problemen. Het is noodzakelijk om grenzen te stellen en ernaar te wijzen, waarna het systeem het antwoord zal vinden. De oplossing wordt bepaald door de waarden te wijzigen.
3. Gevoeligheidsanalyse kan worden gedaan met behulp van de Scenario Manager. Deze tool is te vinden in het wat-als-analysemenu onder het tabblad Gegevens. Het vervangt waarden in verschillende cellen - het aantal kan 32 bereiken. De coördinator vergelijkt deze waarden zodat de gebruiker ze niet handmatig hoeft te wijzigen. Een voorbeeld van het gebruik van de scriptmanager:

Gevoeligheidsanalyse van een investeringsproject in Excel

Wat-als-analyse is vooral nuttig in situaties waarin prognoses vereist zijn, zoals investeringen. Analisten gebruiken deze methode om erachter te komen hoe de waarde van de aandelen van een bedrijf zal veranderen als gevolg van veranderingen in sommige factoren.

Analysemethode voor investeringsgevoeligheid

Gebruik bij het analyseren van "wat als" opsommingen - handmatig of automatisch. Het waardenbereik is bekend en ze worden één voor één in de formule vervangen. Het resultaat is een reeks waarden. Kies het juiste nummer uit hen. Laten we eens kijken naar vier indicatoren waarvoor gevoeligheidsanalyses worden uitgevoerd op het gebied van financiën:

1. Netto contante waarde - Berekend door het bedrag van de investering af te trekken van het bedrag aan inkomsten.
2. Intern rendement / winst - geeft aan hoeveel winst er in een jaar van een investering moet worden ontvangen.
3. De terugbetalingsratio is de verhouding van alle winst tot de initiële investering.
4. Verdisconteerde winstindex - geeft de effectiviteit van de investering aan.

Formule

De inbeddingsgevoeligheid kan worden berekend met behulp van deze formule: Wijziging in uitgangsparameter in % / Wijziging in ingangsparameter in %.

De uitvoer- en invoerparameters kunnen de eerder beschreven waarden zijn.

1. U moet het resultaat kennen onder standaardomstandigheden.
2. We vervangen één van de variabelen en monitoren de veranderingen in het resultaat.
3. We berekenen de procentuele verandering van beide parameters ten opzichte van de vastgestelde voorwaarden.
4. We voegen de verkregen percentages in de formule en bepalen de gevoeligheid.

Een voorbeeld van een gevoeligheidsanalyse van een investeringsproject in Excel

Voor een beter begrip van de analysemethodiek is een voorbeeld nodig. Laten we het project analyseren met de volgende bekende gegevens:

1. Vul de tabel in om het project erop te analyseren.

2. We berekenen de cashflow met behulp van de OFFSET-functie. In de beginfase is de stroom gelijk aan de investeringen. Vervolgens passen we de formule toe: =IF(OFFSET(Getal,1;)=2;SUM(Instroom 1:Uitstroom 1); SUM(Instroom 1:Uitstroom 1)+$B$ 5)
   Celaanduidingen in de formule kunnen verschillen, afhankelijk van de indeling van de tabel. Aan het einde wordt de waarde van de initiële gegevens toegevoegd - de restwaarde.

3. Wij bepalen de periode waarover het project rendeert. Voor de beginperiode gebruiken we deze formule: = SUMMESLY(G7: G.17;»<0″). Het celbereik is de cashflowkolom. Voor verdere periodes passen we deze formule toe: =Initiële periode+IF(Eerste e.stream>0; Eerste e.stream;0). Het project is op het break-even punt in 4 jaar.

4. We maken een kolom voor de aantallen van die perioden waarin het project loont.

5. Wij berekenen het rendement op uw investering. Het is noodzakelijk om een ​​uitdrukking te maken waarbij de winst in een bepaalde periode wordt gedeeld door de initiële investering.

6. We bepalen de kortingsfactor met behulp van deze formule: =1/(1+schijf.%) ^Nummer.

7. We berekenen de contante waarde door vermenigvuldiging - de cashflow wordt vermenigvuldigd met de disconteringsfactor.

8. Laten we PI (winstgevendheidsindex) berekenen. De contante waarde in de tijd wordt gedeeld door de investering bij aanvang van het project.

9. Laten we het interne rendement definiëren met behulp van de IRR-functie: =IRR(Bereik van cashflow).

Investeringsgevoeligheidsanalyse met behulp van gegevensblad

Voor de analyse van projecten op het gebied van investeringen zijn andere methoden beter geschikt dan een datatabel. Veel gebruikers ervaren verwarring bij het samenstellen van een formule. Om de afhankelijkheid van één factor van veranderingen in andere te achterhalen, moet u de juiste cellen selecteren voor het invoeren van berekeningen en voor het lezen van gegevens.

Factor- en spreidingsanalyse in Excel met berekeningsautomatisering

Een andere typologie van gevoeligheidsanalyse is factoranalyse en variantieanalyse. Het eerste type definieert de relatie tussen getallen, het tweede onthult de afhankelijkheid van de ene variabele van de andere.

ANOVA in Excel

Het doel van een dergelijke analyse is om de variabiliteit van de waarde in drie componenten te verdelen:

1. Variabiliteit als gevolg van de invloed van andere waarden.
2. Veranderingen als gevolg van de relatie van de waarden die erop van invloed zijn.
3. Willekeurige veranderingen.

Laten we variantieanalyse uitvoeren via de Excel-invoegtoepassing "Gegevensanalyse". Als het niet is ingeschakeld, kan het worden ingeschakeld in de instellingen.

De begintabel moet aan twee regels voldoen: er is één kolom voor elke waarde en de gegevens erin zijn in oplopende of aflopende volgorde gerangschikt. Het is noodzakelijk om de invloed van het opleidingsniveau op het gedrag in conflictsituaties na te gaan.

1. Zoek de tool Gegevensanalyse op het tabblad Gegevens en open het bijbehorende venster. In de lijst moet u eenrichtingsanalyse van variantie selecteren.

2. Vul de lijnen van het dialoogvenster in. Het invoerinterval is alle cellen, exclusief kopteksten en getallen. Groeperen op kolommen. We tonen de resultaten op een nieuw blad.

Aangezien de waarde in de gele cel groter is dan één, kan de aanname als onjuist worden beschouwd: er is geen verband tussen opvoeding en gedrag in conflictsituaties.

Factoranalyse in Excel: een voorbeeld

Laten we de relatie van gegevens op het gebied van verkoop analyseren - het is noodzakelijk om populaire en impopulaire producten te identificeren. Eerste informatie:

1. We moeten uitzoeken naar welke vraag naar goederen in de tweede maand het meest is gestegen. We stellen een nieuwe tabel samen om de groei en daling van de vraag te bepalen. De groei wordt berekend met behulp van deze formule: =ALS((vraag 2-vraag 1)>0; vraag 2-vraag 1;0). Verlaag formule: =ALS(Groei=0; Vraag 1- Vraag 2;0).

2. Bereken de groei van de vraag naar goederen in procenten: =ALS(Groei/Resultaat 2 =0; Daling/Resultaat 2; Groei/Resultaat 2).

3. Laten we voor de duidelijkheid een grafiek maken - selecteer een celbereik en maak een histogram via het tabblad "Invoegen". In de instellingen moet je de vulling verwijderen, dit kan via de tool Format Data Series.

Tweerichtingsanalyse van variantie in Excel

Variantieanalyse wordt uitgevoerd met verschillende variabelen. Beschouw dit met een voorbeeld: je moet uitzoeken hoe snel de reactie op een geluid met een ander volume zich manifesteert bij mannen en vrouwen.

1. We openen "Data-analyse", in de lijst die u nodig hebt om een ​​tweerichtingsanalyse van variantie zonder herhalingen te vinden.
2. Invoerinterval – cellen die gegevens bevatten (zonder koptekst). We tonen de resultaten op een nieuw blad en klikken op "OK".

De F-waarde is groter dan de F-kritiek, wat betekent dat de vloer de reactiesnelheid op geluid beïnvloedt.

Conclusie

In dit artikel werd de gevoeligheidsanalyse in een Excel-spreadsheet in detail besproken, zodat elke gebruiker de methoden van zijn toepassing kan begrijpen.