Wanneer je een model in een spreadsheet ontwerpt, wil je vaak weten hoe een resultaat verandert als je de waarde van een van de betrokken variabelen aanpast. Dit kun je in Excel doen door een gevoeligheidsanalyse uit te voeren
Weten hoe je een gevoeligheidsanalyse uitvoert is cruciaal als je met data werkt in financiële modellering of besluitvormingsprocessen.
In dit artikel duiken we diep in gevoeligheidsanalyse. We behandelen de basis van deze nuttige techniek binnen de familie van “what-if”-analyses en hoe je de resultaten van eenvoudige en geavanceerde gevoeligheidsanalyses in Excel uitvoert en interpreteert. Tot slot bespreken we de rol van AI en machine learning bij het versterken van krachtigere gevoeligheidsanalyses, plus best practices en veelvoorkomende valkuilen.
Kun je niet wachten om zelf aan de slag te gaan? Bekijk dan onze cursus Financial Analysis in Excel, waarin what-if-analyse uitgebreid aan bod komt.
Wat is een gevoeligheidsanalyse in Excel?
De meest gebruikelijke aanpak bij een gevoeligheidsanalyse is onderzoeken hoe wijzigingen in één variabele tegelijk het eindresultaat beïnvloeden, terwijl alle andere factoren constant blijven. Dit wordt herhaald voor alle variabelen die je wilt bekijken. Het helpt de meest invloedrijke drivers in een model te identificeren, zodat bedrijven zich kunnen richten op de factoren die hun prestaties het meest beïnvloeden.
Gevoeligheidsanalyse helpt bedrijven prioriteit te geven aan belangrijke risico’s en kansen door te bepalen welke factoren de grootste impact hebben op de prestaties. Vanwege dit karakter is gevoeligheidsanalyse gebruikelijk in finance, business en projectmanagement, bijvoorbeeld om te berekenen hoe rentevoeten in de tijd kunnen veranderen of om verschillende groeitrends te begrijpen.
Daarnaast wordt gevoeligheid vaak gecombineerd met scenarioanalyse, waarbij meestal combinaties van variabelen tegelijk worden geanalyseerd. Deze twee what-if-analyses bestuderen hoe een afhankelijke variabele reageert op bepaalde inputs. Scenarioanalyse is echter specifiek voor een bepaald "scenario", terwijl gevoeligheidsanalyse meer open is omdat het een bereik aan inputs en waarden aanreikt.
Het doel van gevoeligheidsanalyse is begrijpen hoe de afhankelijke variabele reageert op een reeks inputwaarden, de zogeheten onafhankelijke variabelen.
Door deze techniek in besluitvorming te gebruiken, kunnen organisaties strategieën proactief aanpassen en hun veerkracht tegen onzekerheid vergroten.
Voor een meer gedetailleerde uitleg van de theorie achter gevoeligheidsanalyse raden we onze cursus Advanced Probability: Uncertainty in Data ten zeerste aan.
Vaak wordt een gevoeligheidsanalyse gepresenteerd in een gevoeligheidstabel met voorwaardelijke opmaak die de waarden van hoog naar laag accentueert.
Hieronder staat een voorbeeld van de prijssensitiviteit van vraag en aanbod. Vraag en aanbod zijn de onafhankelijke variabelen op de assen, en de prijs is de afhankelijke variabele, die de waarden in de tabel vormt.
In de volgende secties werken we met soortgelijke gevoeligheidstabellen in Excel.
Gevoeligheidstabel. Bron: DataCamp
Aan de slag met gevoeligheidsanalyse in Excel
Laten we stap voor stap bekijken hoe je een gevoeligheidsanalyse in Excel uitvoert.
Excel is een handig hulpmiddel voor het maken van wiskundige modellen, waaronder what-if-analyses. What-if-analyse is in essentie het maken van formules waarmee je vragen kunt verkennen, zoals bij een gevoeligheidsanalyse.
De meeste recente versies van Excel hebben een Data-tab, die talloze tools en mogelijkheden biedt om met data te werken. Daarin zit een What-if-analyse-knop op het lint. Deze bevat drie tools. De knop Gegevenstabel is specifiek ontworpen om gevoeligheidstabellen te maken.
Voor scenarioanalyse klik je op de knop Scenariobeheer.
We gebruiken ook Solver, een invoegtoepassing die je kunt activeren via de Ontwikkelaars-tab.
Solver wordt in what-if-analyses gebruikt om een optimale (maximale of minimale) waarde te vinden voor een formule in één cel — de doelcel – met inachtneming van beperkingen of limieten voor de waarden van andere formulecellen op een werkblad.
Voordat je Solver kunt inschakelen, moet je toegang krijgen tot de tab Ontwikkelaars, die standaard niet wordt weergegeven. Zo maak je deze tab zichtbaar:
- Ga naar Bestand > Opties
- Selecteer Lint aanpassen in het linkerpaneel
- Vink in de rechterkolom onder "Hoofdtabbladen" het vakje Ontwikkelaars aan
Een enkelvariabele gevoeligheidsanalyse uitvoeren
Nu de tools klaarstaan, beginnen we met een eenvoudige gevoeligheidsanalyse met één variabele in Excel.
Je model opzetten
De eerste stap voor een gevoeligheidsanalyse in Excel is het identificeren van de input- en outputvariabelen waarop je model is gebaseerd.
Voor deze tutorial gebruiken we een gevoeligheidsanalyse om te onderzoeken hoe de nettowinst van een gitaarverkopend bedrijf verandert als we de waarde van bepaalde inputvariabelen aanpassen, zoals het aantal verkochte gitaren, de prijs van de gitaar of de productiekosten.
Hieronder vind je de tabellen met de input- en outputvariabelen.
Bij gebruik van gegevenstabellen voor gevoeligheidsanalyse is het belangrijk om outputcellen te koppelen aan inputvariabelen, zodat Excel de relatie tussen de modelvariabelen begrijpt. Dit doe je met formules. In ons voorbeeld:
- Omzet wordt berekend door de prijs per eenheid te vermenigvuldigen met het aantal verkochte eenheden (=B2*B4)
- Kostprijs van de omzet wordt berekend door de productiekost per eenheid te vermenigvuldigen met het aantal verkochte eenheden (=B3*B4)
- Winst wordt berekend door de kosten van de omzet af te trekken van de opbrengsten (=B7-B8).
Een eenweg-gegevenstabel maken
Je wilt misschien weten hoe de winst verandert als we de prijs per eenheid verhogen of verlagen, terwijl de productiekosten en het aantal verkochte eenheden gelijk blijven. Als we analyseren hoe een outputvariabele verandert wanneer we de waarde van één inputvariabele aanpassen, hebben we een zogeheten eenweg-gegevenstabel nodig.
De volgende GIF laat zien hoe je in Excel een eenweg-gegevenstabel maakt. Zoals je ziet, herberekent de tabel automatisch de winst voor elk aantal verkochte eenheden. Logischerwijs is de winst wanneer we 250 eenheden verkochten gelijk aan de oorspronkelijke parameters (dus 50.000 €).
Je kunt ook bekijken hoe verschillende outputvariabelen veranderen wanneer we het aantal verkochte eenheden aanpassen. Dat is ook vrij eenvoudig, zoals hieronder te zien is. In de volgende GIF berekenen we hoe de verkochte eenheden zowel de winst als de kostprijs van de omzet beïnvloeden.
Een gevoeligheidsanalyse met twee variabelen uitvoeren
In plaats van te analyseren hoe een output wordt beïnvloed door één input, kun je ook een tweedimensionale tabel maken om de impact van twee inputvariabelen te analyseren. Stel dat je wilt weten hoe de winst verandert als we zowel de gitaarverkoop als de prijs per gitaar aanpassen.
Maak eerst een tweedimensionale tabel met de bereiken van de prijs per eenheid in de rijen en de verkochte eenheden in de kolommen. Kies vervolgens in de linkerbovenhoek van de tabel de variabele die je wilt bestuderen. Kies ten slotte de celverwijzing van de onafhankelijke variabelen in de inputtabel (dus prijs per eenheid en verkochte eenheden).
Zoals je ziet kun je, zodra de tabel is gemaakt, eenvoudig de outputvariabele in de linkerbovenhoek wijzigen naar een andere variabele, waarna Excel alle waarden opnieuw berekent
Resultaten van gevoeligheidsanalyse in Excel interpreteren
Nu je gegevenstabellen klaar zijn, is de laatste stap het interpreteren van de resultaten om inzicht te krijgen in hoe wijzigingen in inputs de outputs beïnvloeden.
Uitvoer van gegevenstabellen analyseren
Met deze gevoeligheidsanalyse kunnen we een onderbouwde beslissing nemen over de winst. Enkele inzichten:
- Als we de prijzen van gitaren verhogen naar 350 €, kunnen we de huidige winst van 50.000 € evenaren door slechts 200 gitaren te verkopen.
- Als we de prijs van gitaren verlagen naar 200 €, moeten we 500 eenheden verkopen om de huidige winst te behalen.
- Als we het aantal verkochte gitaren verlagen naar 150, halen we de huidige winst niet tenzij we de prijs van de gitaren verhogen tot boven 400 €.
De interpretatie van de analyse hangt af van de specifieke behoeften van je bedrijf. Vragen stellen als hoeveel gitaren je kunt produceren, wat het minimale winstniveau is om je bedrijf gezond te houden, en wat je kunt doen om de productiekosten van de gitaar te verlagen, is cruciaal om betekenisvolle inzichten uit de gevoeligheidsanalyse te halen.
Solver gebruiken voor gevoeligheidsanalyse
Wanneer je een gevoeligheidsanalyse uitvoert, krijg je een reeks mogelijke outputs op basis van wijzigingen in de inputvariabelen van een model. Toch wil je misschien meer details weten over het binnenwerk van je model.
Gelukkig wordt Excel geleverd met een krachtige tool genaamd Solver, die je hierbij kan helpen. Zoals vermeld wordt Solver in what-if-analyses gebruikt om een optimale (maximale of minimale) waarde voor een formule te vinden met inachtneming van beperkingen of limieten voor de waarden van andere formulecellen op een werkblad.
Dit lijkt misschien op het doel van Doelzoeken, een andere vorm van what-if-analyse die zich richt op het aanpassen van één variabele om een gewenst resultaat te behalen. Maar Solver is veel meer dan Doelzoeken.
Ten eerste kun je met Solver een optimaal resultaat berekenen op basis van meer dan één variabele en beperkingen in je modellen opnemen.
Bovendien heeft Solver een optionele gevoeligheidsfunctie die inzicht geeft in hoe de optimale oplossing verandert wanneer je de coëfficiënten van het model aanpast.
Om de kracht van Solver te illustreren, gaan we terug naar ons gitaarbedrijf. Stel dat het bedrijf twee gitaarmodellen (model A en B) produceert, die elk een bepaalde combinatie van mahonie en ceder vereisen. Elk model heeft een andere prijs.
Het bedrijf wil weten hoeveel eenheden van elk model het moet produceren om de winst te maximaliseren, gegeven de huidige beschikbaarheid van mahonie en ceder.
Er zijn drie dingen die we door Solver willen laten berekenen: winst, aantal eenheden van model A en aantal eenheden van model B. De bovenstaande afbeelding toont de inputs, formules en beperkingen waarop het model is gebaseerd.
In de volgende GIF laten we zien hoe je Solver gebruikt om de optimale aantallen te vinden om de winst te maximaliseren, met gebruik van houtbeschikbaarheidsbeperkingen en met de eis dat aantallen hele getallen zijn. Solver kan verschillende strategieën gebruiken om het model te optimaliseren, zowel voor lineaire als niet-lineaire problemen. In ons geval gebruiken we Simple LP.
Zodra we alle parameterwaarden invullen, schat Solver dat 48 eenheden van model A en 9 van model B de beste combinatie is om de winst te maximaliseren (17.591 €). We kunnen op Sensitiviteit klikken voordat we het Solver-venster sluiten, waarna Excel een nieuw werkblad met een gevoeligheidsrapport maakt.
De sensitiviteit geeft ons waardevolle inzichten om te schatten hoe de winst zou variëren als we de inputvariabelen aanpassen. De schaduwprijs geeft aan met hoeveel de winst zou toenemen als je één extra eenheid van een beperkende hulpbron had. In dit voorbeeld verhoogt elke extra vierkante meter mahonie de winst met €98, terwijl ceder de winst met €103 verhoogt, wat het grotere potentiële rendement op investering van ceder benadrukt
Een ander belangrijk onderdeel in het gevoeligheidsrapport zijn de bereiken waarbinnen je de prijzen van de modellen kunt houden om de uitkomsten van het model ongewijzigd te laten.
De winst wordt berekend door de aantallen van elk model te vermenigvuldigen met de prijs, die is gegeven in de kolommen Eindwaarde en Doelcoëfficiënt. De bereiken staan in de kolommen Toegestane verhoging en Toegestane verlaging.
Zo zou Solver hetzelfde aantal te produceren eenheden vinden als je de waarde van model A met 1.140 € verhoogt of met 180 € verlaagt.
Data visualiseren met voorwaardelijke opmaak
Als je een- en tweeweg-gevoeligheidstabellen maakt, kan het opmaken van de resultaten een wereld van verschil maken om snel relevante informatie te zien en besluitvorming te verbeteren. Voorwaardelijke opmaak is essentieel om belangrijke drempels en overtuigende heatmaps te creëren.
Laten we teruggaan naar onze tweewegtabel. In plaats van de tabel in witte cellen te tonen, kunnen we voorwaardelijke opmaak gebruiken om een meerkleurige heatmap of een tweekleurenschaal te maken, met een aangepaste drempel, zoals in de GIF te zien is.
AI en machine learning integreren
Laten we kort bekijken hoe AI en machine learning je gevoeligheidsanalyse kunnen verbeteren.
AI-ondersteunde gevoeligheidsanalyse
Tot nu toe hebben we gezien hoe je handmatig een gevoeligheidsanalyse uitvoert. Maar naarmate je modellen complexer worden, kan het uitvoeren en interpreteren van de resultaten van de gevoeligheidsanalyse tijdrovend worden.
Hier komt AI in beeld. Met AI en machine learning kun je machines de berekeningen laten doen zodra aan bepaalde voorwaarden is voldaan en de resultaten laten opnemen in overtuigende dashboards.
Zodra je weet welke berekeningen je wilt uitvoeren, is je volgende stap een duidelijke AI-pijplijn definiëren om het proces te stroomlijnen en te automatiseren.
Benieuwd hoe je dat doet? Bekijk onze Machine Learning, Pipelines, Deployment and MLOps Tutorial.
Montecarlo-simulaties
Montecarlo-simulatie is een wiskundige techniek die wordt gebruikt om de kans op verschillende uitkomsten te modelleren in een proces dat niet eenvoudig te voorspellen is door de invloed van willekeurige variabelen.
Het probleem wordt eerst gemodelleerd met een kansverdeling voor elke variabele met inherente onzekerheid. Vervolgens worden grote aantallen willekeurige steekproeven getrokken uit deze kansverdelingen, en deze steekproeven worden gebruikt om de uitkomsten te berekenen.
Dit proces wordt vele malen herhaald om een verdeling van mogelijke uitkomsten te creëren, die statistisch kan worden geanalyseerd om voorspellingen te doen over hoe een systeem zich zal gedragen. Montecarlo analyseert onzekerheid grondiger dan traditionele gevoeligheidstabellen.
Je kunt alles over deze krachtige techniek leren in onze Monte Carlo Simulation in Excel Guide.
Gezien het karakter van de Montecarlo-simulatie kun je deze gebruiken om je gevoeligheidsanalyse te versterken en te bepalen welke variabelen de grootste impact op de output hebben.
In ons gitaarexample kunnen we de kostprijs en verkochte eenheden constant houden en de prijs per eenheid variëren om de veranderingen in de schattingen te begrijpen. Herhaal dit vervolgens voor de overige twee variabelen één voor één. Uiteindelijk helpt deze techniek te begrijpen op welke variabele je de focus moet leggen om de nauwkeurigheid te verbeteren.
Best practices en veelvoorkomende valkuilen
Hoewel Excel gevoeligheidsanalyse extreem eenvoudig en toegankelijk maakt, zijn er nog steeds enkele veelvoorkomende valkuilen om in gedachten te houden. Hier zijn een paar aandachtspunten.
Robuuste modellen ontwerpen
Zoals je in deze tutorial hebt gezien, moet Excel voor een goed werkende gevoeligheidsanalyse de relaties tussen input- en outputvariabelen kennen. Daarvoor moet je formules verstandig gebruiken, met duidelijk gedefinieerde celverwijzingen en bereiken.
Wil je nieuwe waarden aan de gegevenstabellen toevoegen, dan is het een goede gewoonte om Excel-tabellen te gebruiken om problemen tijdens het herberekenen te voorkomen, omdat ze van nature dynamisch zijn en automatisch uitbreiden wanneer nieuwe rijen of kolommen worden toegevoegd.
Dit helpt je de juiste formuleherberekeningen uit te voeren en de informatie in het juiste formaat te houden.
Fouten beperken en nauwkeurigheid waarborgen
Zelfs als je gevoeligheidsanalyses duidelijk zijn gedefinieerd, moet je je resultaten zorgvuldig verifiëren. Handmatige tests en grafieken zijn goede opties om je resultaten te visualiseren en te controleren op uitschieters of anomalieën in de outputs.
Houd er echter rekening mee dat gevoeligheidsanalyse mogelijk gewoon niet werkt voor jouw usecase. Deze analyse is gebaseerd op een aantal eenvoudige aannames, zoals de onafhankelijkheid van inputvariabelen, het bestaan van lineaire relaties en het statische karakter van inputvariabelen.
Je model kan echter complexer zijn en beïnvloed worden door externe factoren die een gevoeligheidsanalyse per definitie over het hoofd ziet. Ondanks de voordelen is het daarom belangrijk om je model breder te begrijpen dan alleen gevoeligheid, om tot een correcte analyse te komen.
Conclusie
Gefeliciteerd, je bent aan het einde van deze tutorial gekomen. Gevoeligheidsanalyse is een krachtige what-if-analyse die een gamechanger kan zijn in je besluitvormingsproces. Er zijn ook andere what-if-analyses die je financiële modellen aanvullen, zoals Doelzoeken of Scenariobeheer. Gelukkig helpt DataCamp je hierbij. Bekijk onze zorgvuldig samengestelde Excel-cursussen en -materialen:
%22%3E%3Cpath%20d%3D%22m-369%200h408v79h-408z%22%20fill%3D%22%237933ff%22%2F%3E%3Cpath%20d%3D%22m-148%200h306v45h-306z%22%20fill%3D%22%23974dff%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%2F%3E%3C%2FclipPath%3E%3Cg%20clip-path%3D%22url(%23a)%22%3E%3Cpath%20d%3D%22m76%200h484v36h-484z%22%20fill%3D%22%23974dff%22%2F%3E%3Cpath%20d%3D%22m0%200h384v24h-384z%22%20fill%3D%22%2306bdfc%22%20transform%3D%22matrix(1%200%200%20-1%200%2060)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
Wanneer je een model in een spreadsheet ontwerpt, wil je vaak weten hoe een resultaat verandert als je de waarde van een van de betrokken variabelen aanpast. Dit kun je in Excel doen door een gevoeligheidsanalyse uit te voeren
Weten hoe je een gevoeligheidsanalyse uitvoert is cruciaal als je met data werkt in financiële modellering of besluitvormingsprocessen.
In dit artikel duiken we diep in gevoeligheidsanalyse. We behandelen de basis van deze nuttige techniek binnen de familie van “what-if”-analyses en hoe je de resultaten van eenvoudige en geavanceerde gevoeligheidsanalyses in Excel uitvoert en interpreteert. Tot slot bespreken we de rol van AI en machine learning bij het versterken van krachtigere gevoeligheidsanalyses, plus best practices en veelvoorkomende valkuilen.
Kun je niet wachten om zelf aan de slag te gaan? Bekijk dan onze cursus Financial Analysis in Excel, waarin what-if-analyse uitgebreid aan bod komt.
Wat is een gevoeligheidsanalyse in Excel?
De meest gebruikelijke aanpak bij een gevoeligheidsanalyse is onderzoeken hoe wijzigingen in één variabele tegelijk het eindresultaat beïnvloeden, terwijl alle andere factoren constant blijven. Dit wordt herhaald voor alle variabelen die je wilt bekijken. Het helpt de meest invloedrijke drivers in een model te identificeren, zodat bedrijven zich kunnen richten op de factoren die hun prestaties het meest beïnvloeden.
Gevoeligheidsanalyse helpt bedrijven prioriteit te geven aan belangrijke risico’s en kansen door te bepalen welke factoren de grootste impact hebben op de prestaties. Vanwege dit karakter is gevoeligheidsanalyse gebruikelijk in finance, business en projectmanagement, bijvoorbeeld om te berekenen hoe rentevoeten in de tijd kunnen veranderen of om verschillende groeitrends te begrijpen.
Daarnaast wordt gevoeligheid vaak gecombineerd met scenarioanalyse, waarbij meestal combinaties van variabelen tegelijk worden geanalyseerd. Deze twee what-if-analyses bestuderen hoe een afhankelijke variabele reageert op bepaalde inputs. Scenarioanalyse is echter specifiek voor een bepaald "scenario", terwijl gevoeligheidsanalyse meer open is omdat het een bereik aan inputs en waarden aanreikt.
Het doel van gevoeligheidsanalyse is begrijpen hoe de afhankelijke variabele reageert op een reeks inputwaarden, de zogeheten onafhankelijke variabelen.
Door deze techniek in besluitvorming te gebruiken, kunnen organisaties strategieën proactief aanpassen en hun veerkracht tegen onzekerheid vergroten.
Voor een meer gedetailleerde uitleg van de theorie achter gevoeligheidsanalyse raden we onze cursus Advanced Probability: Uncertainty in Data ten zeerste aan.
Vaak wordt een gevoeligheidsanalyse gepresenteerd in een gevoeligheidstabel met voorwaardelijke opmaak die de waarden van hoog naar laag accentueert.
Hieronder staat een voorbeeld van de prijssensitiviteit van vraag en aanbod. Vraag en aanbod zijn de onafhankelijke variabelen op de assen, en de prijs is de afhankelijke variabele, die de waarden in de tabel vormt.
In de volgende secties werken we met soortgelijke gevoeligheidstabellen in Excel.
Gevoeligheidstabel. Bron: DataCamp
Aan de slag met gevoeligheidsanalyse in Excel
Laten we stap voor stap bekijken hoe je een gevoeligheidsanalyse in Excel uitvoert.
Tools en functies in Excel
Excel is een handig hulpmiddel voor het maken van wiskundige modellen, waaronder what-if-analyses. What-if-analyse is in essentie het maken van formules waarmee je vragen kunt verkennen, zoals bij een gevoeligheidsanalyse.
De meeste recente versies van Excel hebben een Data-tab, die talloze tools en mogelijkheden biedt om met data te werken. Daarin zit een What-if-analyse-knop op het lint. Deze bevat drie tools. De knop Gegevenstabel is specifiek ontworpen om gevoeligheidstabellen te maken.
Voor scenarioanalyse klik je op de knop Scenariobeheer.
We gebruiken ook Solver, een invoegtoepassing die je kunt activeren via de Ontwikkelaars-tab.
Solver wordt in what-if-analyses gebruikt om een optimale (maximale of minimale) waarde te vinden voor een formule in één cel — de doelcel – met inachtneming van beperkingen of limieten voor de waarden van andere formulecellen op een werkblad.
Voordat je Solver kunt inschakelen, moet je toegang krijgen tot de tab Ontwikkelaars, die standaard niet wordt weergegeven. Zo maak je deze tab zichtbaar:
Een enkelvariabele gevoeligheidsanalyse uitvoeren
Nu de tools klaarstaan, beginnen we met een eenvoudige gevoeligheidsanalyse met één variabele in Excel.
Je model opzetten
De eerste stap voor een gevoeligheidsanalyse in Excel is het identificeren van de input- en outputvariabelen waarop je model is gebaseerd.
Voor deze tutorial gebruiken we een gevoeligheidsanalyse om te onderzoeken hoe de nettowinst van een gitaarverkopend bedrijf verandert als we de waarde van bepaalde inputvariabelen aanpassen, zoals het aantal verkochte gitaren, de prijs van de gitaar of de productiekosten.
Hieronder vind je de tabellen met de input- en outputvariabelen.
Bij gebruik van gegevenstabellen voor gevoeligheidsanalyse is het belangrijk om outputcellen te koppelen aan inputvariabelen, zodat Excel de relatie tussen de modelvariabelen begrijpt. Dit doe je met formules. In ons voorbeeld:
Een eenweg-gegevenstabel maken
Je wilt misschien weten hoe de winst verandert als we de prijs per eenheid verhogen of verlagen, terwijl de productiekosten en het aantal verkochte eenheden gelijk blijven. Als we analyseren hoe een outputvariabele verandert wanneer we de waarde van één inputvariabele aanpassen, hebben we een zogeheten eenweg-gegevenstabel nodig.
De volgende GIF laat zien hoe je in Excel een eenweg-gegevenstabel maakt. Zoals je ziet, herberekent de tabel automatisch de winst voor elk aantal verkochte eenheden. Logischerwijs is de winst wanneer we 250 eenheden verkochten gelijk aan de oorspronkelijke parameters (dus 50.000 €).
Je kunt ook bekijken hoe verschillende outputvariabelen veranderen wanneer we het aantal verkochte eenheden aanpassen. Dat is ook vrij eenvoudig, zoals hieronder te zien is. In de volgende GIF berekenen we hoe de verkochte eenheden zowel de winst als de kostprijs van de omzet beïnvloeden.
Een gevoeligheidsanalyse met twee variabelen uitvoeren
In plaats van te analyseren hoe een output wordt beïnvloed door één input, kun je ook een tweedimensionale tabel maken om de impact van twee inputvariabelen te analyseren. Stel dat je wilt weten hoe de winst verandert als we zowel de gitaarverkoop als de prijs per gitaar aanpassen.
Maak eerst een tweedimensionale tabel met de bereiken van de prijs per eenheid in de rijen en de verkochte eenheden in de kolommen. Kies vervolgens in de linkerbovenhoek van de tabel de variabele die je wilt bestuderen. Kies ten slotte de celverwijzing van de onafhankelijke variabelen in de inputtabel (dus prijs per eenheid en verkochte eenheden).
Zoals je ziet kun je, zodra de tabel is gemaakt, eenvoudig de outputvariabele in de linkerbovenhoek wijzigen naar een andere variabele, waarna Excel alle waarden opnieuw berekent
Resultaten van gevoeligheidsanalyse in Excel interpreteren
Nu je gegevenstabellen klaar zijn, is de laatste stap het interpreteren van de resultaten om inzicht te krijgen in hoe wijzigingen in inputs de outputs beïnvloeden.
Uitvoer van gegevenstabellen analyseren
Met deze gevoeligheidsanalyse kunnen we een onderbouwde beslissing nemen over de winst. Enkele inzichten:
De interpretatie van de analyse hangt af van de specifieke behoeften van je bedrijf. Vragen stellen als hoeveel gitaren je kunt produceren, wat het minimale winstniveau is om je bedrijf gezond te houden, en wat je kunt doen om de productiekosten van de gitaar te verlagen, is cruciaal om betekenisvolle inzichten uit de gevoeligheidsanalyse te halen.
Solver gebruiken voor gevoeligheidsanalyse
Wanneer je een gevoeligheidsanalyse uitvoert, krijg je een reeks mogelijke outputs op basis van wijzigingen in de inputvariabelen van een model. Toch wil je misschien meer details weten over het binnenwerk van je model.
Gelukkig wordt Excel geleverd met een krachtige tool genaamd Solver, die je hierbij kan helpen. Zoals vermeld wordt Solver in what-if-analyses gebruikt om een optimale (maximale of minimale) waarde voor een formule te vinden met inachtneming van beperkingen of limieten voor de waarden van andere formulecellen op een werkblad.
Dit lijkt misschien op het doel van Doelzoeken, een andere vorm van what-if-analyse die zich richt op het aanpassen van één variabele om een gewenst resultaat te behalen. Maar Solver is veel meer dan Doelzoeken.
Ten eerste kun je met Solver een optimaal resultaat berekenen op basis van meer dan één variabele en beperkingen in je modellen opnemen.
Bovendien heeft Solver een optionele gevoeligheidsfunctie die inzicht geeft in hoe de optimale oplossing verandert wanneer je de coëfficiënten van het model aanpast.
Om de kracht van Solver te illustreren, gaan we terug naar ons gitaarbedrijf. Stel dat het bedrijf twee gitaarmodellen (model A en B) produceert, die elk een bepaalde combinatie van mahonie en ceder vereisen. Elk model heeft een andere prijs.
Het bedrijf wil weten hoeveel eenheden van elk model het moet produceren om de winst te maximaliseren, gegeven de huidige beschikbaarheid van mahonie en ceder.
Er zijn drie dingen die we door Solver willen laten berekenen: winst, aantal eenheden van model A en aantal eenheden van model B. De bovenstaande afbeelding toont de inputs, formules en beperkingen waarop het model is gebaseerd.
In de volgende GIF laten we zien hoe je Solver gebruikt om de optimale aantallen te vinden om de winst te maximaliseren, met gebruik van houtbeschikbaarheidsbeperkingen en met de eis dat aantallen hele getallen zijn. Solver kan verschillende strategieën gebruiken om het model te optimaliseren, zowel voor lineaire als niet-lineaire problemen. In ons geval gebruiken we Simple LP.
Zodra we alle parameterwaarden invullen, schat Solver dat 48 eenheden van model A en 9 van model B de beste combinatie is om de winst te maximaliseren (17.591 €). We kunnen op Sensitiviteit klikken voordat we het Solver-venster sluiten, waarna Excel een nieuw werkblad met een gevoeligheidsrapport maakt.
De sensitiviteit geeft ons waardevolle inzichten om te schatten hoe de winst zou variëren als we de inputvariabelen aanpassen. De schaduwprijs geeft aan met hoeveel de winst zou toenemen als je één extra eenheid van een beperkende hulpbron had. In dit voorbeeld verhoogt elke extra vierkante meter mahonie de winst met €98, terwijl ceder de winst met €103 verhoogt, wat het grotere potentiële rendement op investering van ceder benadrukt
Een ander belangrijk onderdeel in het gevoeligheidsrapport zijn de bereiken waarbinnen je de prijzen van de modellen kunt houden om de uitkomsten van het model ongewijzigd te laten.
De winst wordt berekend door de aantallen van elk model te vermenigvuldigen met de prijs, die is gegeven in de kolommen Eindwaarde en Doelcoëfficiënt. De bereiken staan in de kolommen Toegestane verhoging en Toegestane verlaging.
Zo zou Solver hetzelfde aantal te produceren eenheden vinden als je de waarde van model A met 1.140 € verhoogt of met 180 € verlaagt.
Data visualiseren met voorwaardelijke opmaak
Als je een- en tweeweg-gevoeligheidstabellen maakt, kan het opmaken van de resultaten een wereld van verschil maken om snel relevante informatie te zien en besluitvorming te verbeteren. Voorwaardelijke opmaak is essentieel om belangrijke drempels en overtuigende heatmaps te creëren.
Laten we teruggaan naar onze tweewegtabel. In plaats van de tabel in witte cellen te tonen, kunnen we voorwaardelijke opmaak gebruiken om een meerkleurige heatmap of een tweekleurenschaal te maken, met een aangepaste drempel, zoals in de GIF te zien is.
AI en machine learning integreren
Laten we kort bekijken hoe AI en machine learning je gevoeligheidsanalyse kunnen verbeteren.
AI-ondersteunde gevoeligheidsanalyse
Tot nu toe hebben we gezien hoe je handmatig een gevoeligheidsanalyse uitvoert. Maar naarmate je modellen complexer worden, kan het uitvoeren en interpreteren van de resultaten van de gevoeligheidsanalyse tijdrovend worden.
Hier komt AI in beeld. Met AI en machine learning kun je machines de berekeningen laten doen zodra aan bepaalde voorwaarden is voldaan en de resultaten laten opnemen in overtuigende dashboards.
Zodra je weet welke berekeningen je wilt uitvoeren, is je volgende stap een duidelijke AI-pijplijn definiëren om het proces te stroomlijnen en te automatiseren.
Benieuwd hoe je dat doet? Bekijk onze Machine Learning, Pipelines, Deployment and MLOps Tutorial.
Montecarlo-simulaties
Montecarlo-simulatie is een wiskundige techniek die wordt gebruikt om de kans op verschillende uitkomsten te modelleren in een proces dat niet eenvoudig te voorspellen is door de invloed van willekeurige variabelen.
Het probleem wordt eerst gemodelleerd met een kansverdeling voor elke variabele met inherente onzekerheid. Vervolgens worden grote aantallen willekeurige steekproeven getrokken uit deze kansverdelingen, en deze steekproeven worden gebruikt om de uitkomsten te berekenen.
Dit proces wordt vele malen herhaald om een verdeling van mogelijke uitkomsten te creëren, die statistisch kan worden geanalyseerd om voorspellingen te doen over hoe een systeem zich zal gedragen. Montecarlo analyseert onzekerheid grondiger dan traditionele gevoeligheidstabellen.
Je kunt alles over deze krachtige techniek leren in onze Monte Carlo Simulation in Excel Guide.
Gezien het karakter van de Montecarlo-simulatie kun je deze gebruiken om je gevoeligheidsanalyse te versterken en te bepalen welke variabelen de grootste impact op de output hebben.
In ons gitaarexample kunnen we de kostprijs en verkochte eenheden constant houden en de prijs per eenheid variëren om de veranderingen in de schattingen te begrijpen. Herhaal dit vervolgens voor de overige twee variabelen één voor één. Uiteindelijk helpt deze techniek te begrijpen op welke variabele je de focus moet leggen om de nauwkeurigheid te verbeteren.
Best practices en veelvoorkomende valkuilen
Hoewel Excel gevoeligheidsanalyse extreem eenvoudig en toegankelijk maakt, zijn er nog steeds enkele veelvoorkomende valkuilen om in gedachten te houden. Hier zijn een paar aandachtspunten.
Robuuste modellen ontwerpen
Zoals je in deze tutorial hebt gezien, moet Excel voor een goed werkende gevoeligheidsanalyse de relaties tussen input- en outputvariabelen kennen. Daarvoor moet je formules verstandig gebruiken, met duidelijk gedefinieerde celverwijzingen en bereiken.
Wil je nieuwe waarden aan de gegevenstabellen toevoegen, dan is het een goede gewoonte om Excel-tabellen te gebruiken om problemen tijdens het herberekenen te voorkomen, omdat ze van nature dynamisch zijn en automatisch uitbreiden wanneer nieuwe rijen of kolommen worden toegevoegd.
Dit helpt je de juiste formuleherberekeningen uit te voeren en de informatie in het juiste formaat te houden.
Fouten beperken en nauwkeurigheid waarborgen
Zelfs als je gevoeligheidsanalyses duidelijk zijn gedefinieerd, moet je je resultaten zorgvuldig verifiëren. Handmatige tests en grafieken zijn goede opties om je resultaten te visualiseren en te controleren op uitschieters of anomalieën in de outputs.
Houd er echter rekening mee dat gevoeligheidsanalyse mogelijk gewoon niet werkt voor jouw usecase. Deze analyse is gebaseerd op een aantal eenvoudige aannames, zoals de onafhankelijkheid van inputvariabelen, het bestaan van lineaire relaties en het statische karakter van inputvariabelen.
Je model kan echter complexer zijn en beïnvloed worden door externe factoren die een gevoeligheidsanalyse per definitie over het hoofd ziet. Ondanks de voordelen is het daarom belangrijk om je model breder te begrijpen dan alleen gevoeligheid, om tot een correcte analyse te komen.
Conclusie
Gefeliciteerd, je bent aan het einde van deze tutorial gekomen. Gevoeligheidsanalyse is een krachtige what-if-analyse die een gamechanger kan zijn in je besluitvormingsproces. Er zijn ook andere what-if-analyses die je financiële modellen aanvullen, zoals Doelzoeken of Scenariobeheer. Gelukkig helpt DataCamp je hierbij. Bekijk onze zorgvuldig samengestelde Excel-cursussen en -materialen: