Gevoeligheidsanalyse uitvoeren in Excel: Stap voor stap

Om een gevoeligheidsanalyse in Excel uit te voeren, plaatst u elke invoer van uw model in een eigen cel. Wijzig vervolgens die invoerwaarden en leg vast hoe het resultaat verandert. Gebruik een gegevenstabel met één variabele voor één invoerwaarde, een gegevenstabel met twee variabelen voor twee, en Scenariobeheer voor volledige cases. Sluit af met een tornado-grafiek die de invoerwaarden rangschikt op basis van impact.

Deze gids legt uit wat een gevoeligheidsanalyse is, hoe u het model opzet en hoe u elke Excel-tool gebruikt. Het bevat een uitgewerkt voorbeeld met echte formules, een snellere methode met AI en de fouten die u moet vermijden.

Wat een gevoeligheidsanalyse is

Een gevoeligheidsanalyse laat zien hoeveel een resultaat verandert wanneer één aanname wijzigt. Het beantwoordt vragen als: "Wat gebeurt er met de winst als de prijs met 10% daalt?"

De meeste bedrijfsmodellen rusten op aannames. Verkoopvolume, prijs, kosten en groeipercentages zijn allemaal schattingen. Een gevoeligheidsanalyse test welke van die aannames het belangrijkst zijn.

Excel groepeert deze tools onder Wat-als-analyse. Microsoft definieert het als "het proces van het wijzigen van de waarden in cellen" om te zien hoe de resultaten van formules veranderen.

Microsoft noemt drie ingebouwde tools: "Scenario's, Doelzoeken en Gegevenstabellen." Elke tool heeft een andere taak:

• Gegevenstabellen tonen één resultaat over vele waarden van één of twee invoerwaarden.
• Scenariobeheer slaat volledige sets invoerwaarden op, zoals een worst-case en een best-case scenario.
• Doelzoeken werkt achterwaarts. Het vindt de invoer die nodig is om een doelresultaat te bereiken.

Een gevoeligheidsanalyse gebruikt meestal de eerste twee. Doelzoeken helpt bij een gerelateerde vraag, zoals de break-even prijs.

Stel het model op voordat u begint

De tools van Excel werken alleen als het model zuiver is opgebouwd. Controleer eerst deze punten.

Zet elke invoer in een eigen cel. Eenheden, prijs, kosten en percentages hebben elk een cel nodig. Typ geen getallen binnen formules.

Koppel het resultaat aan de invoerwaarden. De resultaatcel, zoals winst, moet een formule zijn die verwijst naar de invoercellen. Als dit niet het geval is, verandert er niets wanneer Excel nieuwe waarden invult.

Houd invoerwaarden bij elkaar. Groepeer ze in één blok bovenaan het blad. Geef ze elk een label en geef de invoercellen een kleur zodat ze opvallen.

Kies één resultaat om te testen. Een gevoeligheidsanalyse is gemakkelijker te lezen wanneer deze één output volgt, zoals winst, netto contante waarde of liquide middelen aan het einde van het jaar.

Noteer uw basisscenario. Schrijf de startwaarden op. Elk resultaat in de analyse wordt vergeleken met deze basis.

Hier is een eenvoudig winstmodel om mee te volgen. Het is een fictieve fietsenwinkel, Northgate Cycles:

Cel	Label	Waarde
B1	Verkochte eenheden	2,400
B2	Prijs per eenheid	$850
B3	Variabele kosten per eenheid	$510
B4	Vaste kosten	$520,000
B5	Winst	=B1*(B2-B3)-B4

Bij deze basiswaarden is de winst $296,000.

Hoe u een gevoeligheidsanalyse met één variabele uitvoert met een gegevenstabel

Een gegevenstabel met één variabele test een reeks waarden voor één invoerwaarde. Microsoft zegt dat het laat zien "hoe verschillende waarden van één variabele in één of meer formules de resultaten zullen veranderen."

Hier leest u hoe u de prijs test in het voorbeeldmodel:

1. Typ in D2 tot D6 de te testen prijzen: $800, $825, $850, $875 en $900.
2. Typ in E1, één rij boven en één kolom rechts van de eerste prijs, =B5.
3. Selecteer D1:E6.
4. Selecteer op het tabblad Gegevens de optie Wat-als-analyse > Gegevenstabel.
5. Voer in Kolominvoercel B2 in, de prijscel. Laat de Rij-invoercel leeg.
6. Klik op OK.

Excel vult E2:E6 met de winst voor elke prijs. De plaatsing van de formule is belangrijk. Microsoft zegt dat u deze "één rij boven en één cel rechts van de kolom met waarden" moet typen.

De resultaten voor Northgate Cycles:

Prijs	Winst
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Elke verandering van $25 in prijs verschuift de winst met $60,000. Dat is de gevoeligheid van winst voor prijs.

Hoe u een gevoeligheidsanalyse met twee variabelen uitvoert

Een gegevenstabel met twee variabelen test twee invoerwaarden tegelijk. Microsoft zegt dat het laat zien "hoe verschillende waarden van twee variabelen in één formule de resultaten van die formule zullen veranderen."

Hier leest u hoe u prijs en eenheden samen test:

1. Typ in een lege cel, zoals D10, =B5.
2. Typ daaronder, in D11 tot D13, de eenheidsvolumes: 2,000, 2,400 en 2,800.
3. Typ rechts daarvan, in E10 tot G10, de prijzen: $800, $850 en $900.
4. Selecteer D10:G13.
5. Selecteer Wat-als-analyse > Gegevenstabel.
6. Voer in Rij-invoercel B2 in. Voer in Kolominvoercel B1 in.
7. Klik op OK.

Het resultaat is een raster van winsten:

Eenheden \ Prijs	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

Het raster laat zien dat de winkel winstgevend blijft in elke geteste combinatie. De zwakste hoek, 2,000 eenheden bij $800, levert nog steeds $60,000 op.

Er is één harde grens. Microsoft zegt: "Een gegevenstabel kan niet meer dan twee variabelen bevatten." Gebruik voor meer invoerwaarden Scenariobeheer.

Gegevenstabellen worden ook herberekend telkens wanneer het blad wordt herberekend. In een groot werkmap kan dat Excel vertragen. Microsoft stelt voor om de berekening zo in te stellen dat gegevenstabellen alleen worden herberekend wanneer u op F9 drukt.

Hoe u volledige cases vergelijkt met Scenariobeheer

Scenariobeheer slaat volledige sets invoerwaarden op en wisselt deze uit. Microsoft beschrijft een scenario als "een set waarden die Excel opslaat en automatisch kan vervangen in uw werkblad."

Hier leest u hoe u drie cases opzet:

1. Selecteer op het tabblad Gegevens de optie Wat-als-analyse > Scenariobeheer.
2. Klik op Toevoegen, noem het scenario Basis, en voer B1:B4 in als de veranderende cellen. Behoud de huidige waarden.
3. Voeg een Worst-case toe: 2,000 eenheden, $825 prijs, $530 variabele kosten en $540,000 vaste kosten.
4. Voeg een Best-case toe: 2,800 eenheden, $875 prijs, $495 variabele kosten en $510,000 vaste kosten.
5. Selecteer een scenario en klik op Tonen om de waarden te laden.
6. Klik op Samenvatting en kies B5 als de resultaatcel om een rapport te maken.

Voor Northgate Cycles toont de samenvatting drie winsten. De worst-case levert $50,000 op, het basisscenario $296,000 en de best-case $554,000.

Microsoft merkt enkele beperkingen op. Elk scenario kan maximaal 32 veranderende waarden bevatten. Het samenvattingsrapport is niet automatisch: "Scenariorapporten worden niet automatisch herberekend." Als u een scenario wijzigt, maak dan een nieuwe samenvatting.

Hoe u een break-even punt vindt met Doelzoeken

Een gevoeligheidsanalyse laat zien hoe ver het resultaat verschuift. Doelzoeken beantwoordt de omgekeerde vraag: welke invoerwaarde geeft een doelresultaat?

Microsoft zegt dat Doelzoeken "een resultaat neemt en mogelijke invoerwaarden bepaalt die dat resultaat produceren." Het voorbeeld vindt de rentevoet die nodig is voor een leningbetaling.

Hier leest u hoe u de break-even prijs voor Northgate Cycles vindt:

1. Selecteer op het tabblad Gegevens de optie Wat-als-analyse > Doelzoeken.
2. Voer in Cel instellen B5 in, de winstcel.
3. Voer bij Waarde 0 in.
4. Voer bij Door cel te wijzigen B2 in, de prijscel.
5. Klik op OK.

Excel wijzigt de prijs totdat de winst nul bereikt. Bij 2,400 eenheden en de basiskosten is de break-even prijs ongeveer $726.67. Dat is ongeveer 15% onder de huidige prijs van $850.

Doelzoeken heeft één beperking. Microsoft zegt dat het "werkt met slechts één variabele invoerwaarde." Voor meer dan één invoerwaarde verwijst Microsoft naar de invoegtoepassing Oplosser.

Hoe u een tornado-grafiek bouwt

Een tornado-grafiek rangschikt invoerwaarden op basis van hoeveel ze het resultaat beïnvloeden. Het is de duidelijkste manier om een gevoeligheidsanalyse te presenteren.

Hier leest u hoe u er een bouwt:

1. Kies een testbereik, zoals elke invoer 10% lager en 10% hoger.
2. Wijzig één invoerwaarde tegelijk en leg het lage en hoge resultaat vast. Houd de andere invoerwaarden op de basiswaarde.
3. Zet de resultaten in een tabel met één rij per invoerwaarde.
4. Sorteer de rijen zodat de invoer met het breedste bereik bovenaan staat.
5. Voeg een staafdiagram in van de lage en hoge resultaten.

Voor Northgate Cycles, waarbij elke invoer 10% omhoog en omlaag is verschoven:

Invoer	Winst bij −10%	Winst bij +10%	Bereik
Prijs	$92,000	$500,000	$408,000
Variabele kosten	$418,400	$173,600	$244,800
Verkochte eenheden	$214,400	$377,600	$163,200
Vaste kosten	$348,000	$244,000	$104,000

Prijs is het belangrijkst, met een ruime marge. Een prijsdaling van 10% verlaagt de winst van $296,000 naar $92,000. Vaste kosten doen er het minst toe. Dat vertelt de eigenaar waar hij tijd moet besteden aan het controleren van aannames.

Een staafdiagram in Excel tekent staven van links naar rechts. Om de tornado-vorm te krijgen, zet u de verandering van elke invoer ten opzichte van de basiswinst uit in plaats van de ruwe waarden. Dan wijzen de staven voor het lage scenario naar links en de staven voor het hoge scenario naar rechts.

Hoe u een gevoeligheidsanalyse uitvoert met AI

Het bouwen van gegevenstabellen, scenario's en een tornado-grafiek voor een groot model kost tijd. Een AI-tool kan dezelfde tests uitvoeren vanuit uw werkmap.

Hier is de methode in drie stappen, met gebruik van Powerdrill Bloom.

Stap 1: Upload uw model

Begin met het spreadsheet dat uw model bevat. De AI Sensitivity Analysis Generator vraagt u om een "DCF, business case, operationeel plan of een ander spreadsheetmodel" te uploaden.

Zorg ervoor dat de invoerwaarden en de resultaatcel duidelijk gelabeld zijn. De tool moet weten welke getallen aannames zijn.

Stap 2: Kies de gevoeligheidsweergave in natuurlijke taal

Beschrijf de test die u wilt. Bijvoorbeeld: "Toon een tornado-grafiek voor winst waarbij elke invoer 10% omhoog en omlaag wordt verschoven. Voeg een tabel met twee variabelen toe voor prijs en verkochte eenheden."

De toolpagina vermeldt vier weergaven: "tornado, twee-weg, Monte Carlo of break-even." Het beschrijft de tornado-output als een grafiek die "de invoerwaarden rangschikt die het antwoord het meest beïnvloeden." Het biedt ook een "Gevoeligheidstabel over de twee aannames die er toe doen."

Stap 3: Controleer de resultaten en exporteer de analyse

Vergelijk een of twee resultaten met uw eigen gegevenstabel voordat u ze deelt. Het basisscenario moet exact overeenkomen met uw model.

Exporteer vervolgens. De toolpagina vermeldt een "Excel werkmap, branded PDF of canvas link." Voor een presentatie zet de Excel to PPT tool hetzelfde bestand om in dia's. De AI Financial Modeling pagina behandelt het bouwen van het model zelf.

Hoe u de resultaten leest en presenteert

Een gevoeligheidsanalyse is alleen nuttig als mensen er naar handelen. Deze gewoonten helpen daarbij.

Begin met de rangschikking. Open met de tornado-grafiek. Deze laat in één beeld zien welke aannames het resultaat sturen.

Vertaal bereiken naar beslissingen. "Winst daalt naar $92,000 als de prijs met 10% daalt" is een feit. "We moeten de prijs testen voordat we het huurcontract tekenen" is een beslissing.

Toon het break-even punt. Koppel de analyse aan de waarde waar het resultaat nul bereikt. Voor Northgate Cycles is het break-even punt ongeveer 1,530 eenheden bij de basisprijs en -kosten. Onze gids voor break-even analyse in Excel behandelt die berekening.

Gebruik realistische bereiken. Een verschuiving van 10% past bij stabiele invoerwaarden. Het volume van een nieuw product kan een veel breder bereik nodig hebben.

Vermeld wat constant werd gehouden. Elke test waarbij één variabele tegelijk wordt gewijzigd, houdt de andere invoerwaarden op de basiswaarde. Zeg dit op de dia of in een voetnoot. Lezers weten dan dat een prijsverandering van 10% uitgaat van hetzelfde volume, dezelfde kosten en dezelfde vaste uitgaven. Deze ene regel voorkomt de meeste vervolgvragen over de methode.

Kom er op terug nadat de resultaten binnen zijn. Zodra de werkelijke cijfers binnenkomen, vergelijkt u ze met uw bereiken. Een budget vs. werkelijkheid rapport is een natuurlijk vervolg.

Veelvoorkomende fouten bij gevoeligheidsanalyses

Deze fouten komen vaak voor en zijn gemakkelijk te vermijden.

Hard-coded getallen in formules. Als een formule 850 bevat in plaats van B2, kan de gegevenstabel dit niet wijzigen. Houd elke invoer in een cel.

Te veel invoerwaarden tegelijk testen. Een gegevenstabel kan er één of twee aan. Stap over op Scenariobeheer of een tornado-grafiek voor meer.

Hetzelfde bereik gebruiken voor elke invoer. Prijs en volume hebben zelden dezelfde onzekerheid. Kies bereiken die weerspiegelen wat u werkelijk weet.

Links tussen invoerwaarden negeren. Een lagere prijs kan het volume verhogen. Een test waarbij één variabele tegelijk wordt gewijzigd, mist dat, dus voeg een scenario toe dat beide wijzigt.

Verouderde scenariosamenvattingen. Scenariorapporten worden niet vanzelf bijgewerkt. Bouw ze opnieuw op na elke wijziging.

Het basisscenario vergeten. Elke tabel moet het basisresultaat tonen, zodat lezers de omvang van elke verandering kunnen zien.

Wanneer u klaar bent om uw volgende model aan een stresstest te onderwerpen, probeer dan Powerdrill Bloom met de werkmap die u gebruikt voor planning.

Veelgestelde vragen

Wat is een gevoeligheidsanalyse in Excel?

Het is een manier om te testen hoe een resultaat verandert wanneer de invoerwaarden wijzigen. In Excel gebruikt u Wat-als-analyse tools, voornamelijk gegevenstabellen en Scenariobeheer, om nieuwe invoerwaarden in te vullen en de resultaten vast te leggen.

Hoe voer ik een gevoeligheidsanalyse met één variabele uit in Excel?

Zet de invoerwaarden in een kolom. Plaats een formule die verwijst naar het resultaat één rij boven en één kolom naar rechts. Selecteer het bereik, kies Wat-als-analyse > Gegevenstabel, en voer de invoercel in als de kolominvoercel.

Wat is het verschil tussen een gegevenstabel en Scenariobeheer?

Een gegevenstabel test één of twee variabelen over vele waarden en toont alle resultaten in één raster. Scenariobeheer slaat volledige sets invoerwaarden op, elk tot 32 veranderende waarden, en wisselt deze één voor één uit.

Hoe maak ik een tornado-grafiek in Excel?

Verschuif elke invoerwaarde met hetzelfde bedrag omhoog en omlaag, één voor één, en leg het resultaat vast. Sorteer de invoerwaarden op de grootte van het bereik en breng vervolgens de verandering van elke invoer ten opzichte van het basisresultaat in kaart als horizontale staven.

Kan AI een gevoeligheidsanalyse uitvoeren?

Ja. Een AI-tool kan uw spreadsheetmodel lezen, tornado- en twee-wegtests uitvoeren en de resultaten exporteren als een werkmap of rapport. U moet nog steeds het basisscenario controleren en realistische bereiken kiezen.

Bronnen: Microsoft Support, Inleiding tot Wat-als-analyse · Microsoft Support, Meerdere resultaten berekenen met een gegevenstabel · Microsoft Support, Schakelen tussen verschillende sets waarden met behulp van scenario's.