What-if-analyse in Excel: Doelzoeken, Scenariobeheer en Gegevenstabellen

Je geeft drie dingen op:

• Stel cel in: de cel met je formule (de output)
• Op waarde: het doelresultaat dat je wilt
• Door wijzigen van cel: de invoercel die Excel mag aanpassen

Laten we het in de praktijk bekijken. 

Je runt een kleine onderneming die notitieboekjes verkoopt. Je huidige omzet is $25,000, maar je doel is $50,000. De prijs staat vast op $25, dus je moet uitvinden hoeveel stuks je moet verkopen. 

Zo pak je het aan:

1. Ga naar Gegevens > What-if analyse > Doelzoeken

2. Klik bij Stel cel in op cel B4 (je omzetformule)

3. Vul bij Op waarde 50000 in

4. Kies bij Door wijzigen van cel B2 (Verkochte stuks)

5. Klik op OK

Doelzoeken past B2 aan van 1.000 naar 2.000 stuks — precies genoeg om $50.000 omzet te halen bij $25 per stuk.

Doelzoeken gebruiken om een specifiek resultaat te bereiken. Afbeelding door de auteur.

Scenariobeheer gebruiken in Excel

Scenariobeheer is een What-if-analysetool waarmee je meerdere sets invoerwaarden in hetzelfde spreadsheet kunt opslaan en ertussen kunt wisselen. Elke opgeslagen versie heet een scenario. 

Je kunt er zoveel maken als je wilt en er direct tussen schakelen om resultaten te vergelijken. 

Handig wanneer je beste-, slechtste- en verwachte scenario’s wilt plannen zonder drie aparte bestanden te hoeven bijhouden. 

Je geeft drie dingen op:

• Scenarionaam: een label voor elke set aannames
• Wijzende cellen: de invoercellen die per scenario verschillen
• Waarden: de specifieke cijfers per scenario

Een scenario maken

Stel, je plant de kwartaalverkopen voor je notitieboekjesbedrijf.

Je huidige winst is $15,000. Je wilt drie scenario’s modelleren: best case, worst case en expected case. 

Zo maak je een scenario:

1. Ga naar Gegevens > What-if analyse > Scenariobeheer

2. Klik in het dialoogvenster Scenariobeheer op Toevoegen

3. Typ bij Scenarionaam Best Case.

4. Selecteer bij Wijzende cellen B2:B4.

5. Er verschijnt een dialoogvenster Scenariowaarden. Vul hierin de volgende waarden in:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Klik op OK. 

Herhaal deze stappen voor de andere twee scenario’s met de onderstaande waarden:

Creeer een scenario met drie gevallen. Afbeelding door de auteur.

Om tussen scenario’s te wisselen, selecteer je er één in de lijst en klik je op Weergeven. Excel vervangt de waarden direct en je winst wordt bijgewerkt.

Klik op Weergeven om de waarden van elk scenario te zien. Afbeelding door de auteur.

Scenario’s bewerken

Selecteer een scenario in de lijst en klik op Bewerken. Je kunt de naam wijzigen, aanpassen welke cellen het omvat, of de waarden bijwerken. Er verandert niets in je spreadsheet totdat je op Weergeven klikt.

Scenariorapporten genereren

Scenariobeheer kan ook een rapport maken dat alle opgeslagen scenario’s naast elkaar vergelijkt. 

Zo maak je een scenariorapport:

1. Klik binnen Scenariobeheer op Samenvatting

2. Kies in Scenariosamenvatting voor Scenariosamenvatting

3. Selecteer bij Resultaatcellen B7 (je winstcel)

4. Klik op OK

Excel maakt een samenvattingstabel op een nieuw werkblad waarop alle drie scenario’s naast elkaar staan

Genereer een scenariosamenvattingsrapport. Afbeelding door de auteur.

Gegevenstabellen gebruiken in Excel

Gegevenstabellen automatiseren herhaalde berekeningen over een reeks invoerwaarden. In plaats van een input handmatig te veranderen, het resultaat te noteren en te herhalen, definieer je de reeks één keer en vult Excel alle resultaten in één keer in.

Je geeft drie dingen op:

• Invoerreeks: de waarden die je wilt testen
• Rij-/kolominvoercel: de cel in je formule die door die waarden wordt vervangen
• Formulecel: de output waar je tabel omheen is gebouwd

Eén-variabele gegevenstabellen

Een één-variabele gegevenstabel test hoe het wijzigen van één input een formuleresultaat beïnvloedt.

Bijvoorbeeld: je hebt een formule voor de leningaflossing en wilt zien hoe de maandlast verandert bij rentepercentages van 5% tot 10%.

Zo maak je een één-variabele gegevenstabel:

1. Zet je dataset op. Let op: in mijn dataset zet ik in cel E1, direct boven je eerste percentage, niet naast de kolomkop, de formule =B5. Als E1 leeg blijft of de formuleverwijzing in een andere cel staat, draait de gegevenstabel wel, maar geeft die overal nullen in plaats van betalingswaarden.

Maak een één-variabele gegevenstabel. Afbeelding door de auteur.

2. Selecteer vervolgens het volledige bereik D1:E9 (inclusief E1)

3. Ga naar Gegevens > What-if analyse > Gegevenstabel

4. Laat de Rij-invoercel leeg

5. Selecteer in de Kolom-invoercel B4 (rentepercentage)

6. Klik op OK.

Excel vult de tabel met de berekende resultaten voor elke invoerwaarde. Let op dat de betaling niet gelijkmatig oploopt. De stap van 3% naar 4% is $49, maar van 9% naar 10% is het $61. Renteverhogingen hakken er harder in naarmate ze hoger worden.

Berekende resultaten voor elke invoerwaarde. Afbeelding door de auteur.

Twee-variabele gegevenstabellen

Een twee-variabele gegevenstabel test hoe het gelijktijdig wijzigen van twee inputs een formuleresultaat beïnvloedt.

Bijvoorbeeld: een prijsmodel hangt af van zowel productprijs als verkochte stuks. Een twee-variabele gegevenstabel kan de winst tonen bij verschillende prijsniveaus en verkoopvolumes in één overzicht.

Zet eerst de dataset op. 

Kijk naar de dataset die ik gebruik:

• Vul in cel D1 =B5 in. Dit is de formuleverwijzing en die moet linksboven in het raster staan, niet naast een label

• Zet over rij 1, vanaf E1, de waarden voor verkochte stuks die je wilt testen: 500, 1000, 1500, 2000

• Zet in kolom D, vanaf D2, de prijsniveaus die je wilt testen: $20, $25, $30, $35

Zet een dataset op voor een twee-variabele gegevenstabel. Afbeelding door de auteur.

Zo maak je een twee-variabele gegevenstabel:

1. Selecteer het volledige bereik D1:H5.

2. Ga naar Gegevens > What-if-analyse > Gegevenstabel

3. Selecteer bij de Rijinvoercel B3 (verkochte stuks)

4. Selecteer bij de Kolominvoercel B2 (prijs per stuk)

5. Klik op OK.

Excel vult de winst in voor elke combinatie van prijs en verkochte stuks

Berekende resultaten voor invoerwaarde. Afbeelding door de auteur.

Twee-variabele gegevenstabellen zijn vooral nuttig wanneer één aanname op zichzelf niet het hele verhaal vertelt. Een prijs kan er op zichzelf goed uitzien, maar de winst hangt ook af van hoeveel stuks klanten daadwerkelijk kopen. Die tweede variabele maakt het model nuttiger.

Doelzoeken vs. Scenariobeheer vs. Gegevenstabellen

Doelzoeken, Scenariobeheer en Gegevenstabellen horen allemaal bij de What-if-analysetoolkit van Excel, maar lossen verschillende problemen op. 

Hier is een korte vergelijking tussen deze drie 

What-if-analyse vs. Oplosser

What-if-analyse en Oplosser helpen allebei om verschillende uitkomsten in Excel te verkennen, maar ze lossen verschillende soorten problemen op.

• What-if-analyse test aannames. Je verandert één of meer inputs en ziet hoe die wijzigingen het resultaat beïnvloeden. Het doel is mogelijke uitkomsten te begrijpen, niet de beste te vinden.
• Oplosser werkt anders. Je geeft een doel op, zoals winst maximaliseren of kosten minimaliseren, en het zoekt de combinatie van inputs die dat doel het beste haalt. Het test geen enkele aanname, maar lost de optimale op. 

Een kredietaanvrager kan bijvoorbeeld Doelzoeken gebruiken om het leenbedrag te bepalen dat resulteert in $500 maandlasten. Maar een productiebedrijf kan Oplosser gebruiken om de productmix te bepalen die de hoogste winst oplevert binnen arbeids- en materiaallimieten. 

Let op: Oplosser maakt geen deel uit van de standaard What-if-analysetools. Het is een aparte invoegtoepassing. In de meeste Excel-versies moet je die handmatig inschakelen in Excel-opties voordat die zichtbaar is op het tabblad Gegevens.

Best practices voor What-if-analyse

Volg deze best practices wanneer je met What-if-analyse werkt: 

1. Houd aannames gescheiden van formules

Sla invoerwaarden op in aparte cellen en verwijs daarnaar in je formules. Vermijd het hardcoderen van getallen rechtstreeks in berekeningen. 

Bijvoorbeeld, deze formule is lastig bij te werken:

=(50*1000)-10000

Maar deze versie is veel onderhoudsvriendelijker:

=(B2*B3)-B4

Als aannames veranderen, hoef je alleen de invoercellen bij te werken in plaats van formules overal in de werkmap te bewerken.

2. Label scenario’s duidelijk

Scenarionamen moeten meteen duidelijk maken wat ze voorstellen. "Scenario 1", "Scenario 2" en "Scenario 3" zeggen je over zes maanden niets meer. Noem ze naar wat ze echt voorstellen, zoals "Best Case: 15% groei." 

3. Valideer inputs

What-if-analyse is zo betrouwbaar als de inputs die je test. Controleer vóór je analyse of:

• Percentages correct zijn ingevoerd
• Datums een consistente notatie hebben
• Maat­eenheden consistent zijn
• Invoerwaarden binnen realistische bandbreedtes vallen

Een omzetprognose op basis van 500% verkoopgroei kan technisch kloppen, maar is nog steeds onbruikbaar voor besluitvorming.

4. Documenteer aannames

Een model dat vandaag voor jou logisch is, is over drie maanden niet per se logisch voor jou of iemand anders. 

Een korte notitie bij elke aanname, desnoods "gebaseerd op Q3-actuals", voorkomt later veel giswerk. Documenteren helpt anderen begrijpen waar de cijfers vandaan komen en maakt toekomstige updates veel eenvoudiger.

Veelvoorkomende problemen met What-if-analyse

De meeste problemen met What-if-analyse komen door de opzet van het werkblad en niet door de tools zelf. Als Doelzoeken, Scenariobeheer of Gegevenstabellen onverwachte resultaten geven, controleer dan een paar veelvoorkomende probleemgebieden; meestal lost dat het op.

1. Formules werken niet bij 

Je wijzigt een input en er gebeurt niets. Meestal betekent dit dat de rekenmodus van Excel ergens onderweg op handmatig is gezet. 

Oplossing: ga naar Formules > Berekeningsopties en zorg dat die op Automatisch staat. Is dat al zo, druk dan op F9 om een herberekening af te dwingen.

2. Onjuiste celverwijzingen

What-if-analysetools zijn afhankelijk van correcte celverwijzingen. Als de verkeerde cellen zijn geselecteerd, zijn de resultaten onjuist, ook al rondt Excel de berekening succesvol af. 

Zo kan Doelzoeken de verkeerde invoercel wijzigen. Een gegevenstabel kan naar de verkeerde rij- of kolominvoercel verwijzen. Scenariobeheer kan waarden voor de verkeerde cellen opslaan.

Oplossing: Controleer je invoercellen vóór je de analyse start, dubbelcheck wat er is geselecteerd in elk dialoogvenster van de tool en gebruik beschrijvende labels zodat duidelijk is wat elke cel voorstelt. 

3. Beperkingen van Doelzoeken verkeerd inschatten

Veel gebruikers verwachten dat Doelzoeken problemen met meerdere veranderende variabelen kan oplossen. Dat kan niet. Doelzoeken wijzigt slechts één invoercel tegelijk om een doelwaarde te halen. 

Zo kan Doelzoeken bepalen welk verkoopvolume nodig is om $15.000 winst te maken. Het kan niet tegelijkertijd verkoopvolume, prijs en kosten aanpassen om dat doel te halen.

Oplossing: gebruik Doelzoeken wanneer slechts één variabele hoeft te veranderen. Gebruik Scenariobeheer of Oplosser wanneer meerdere variabelen meespelen. 

4. Te complexe scenariomodellen

Scenariobeheer staat technisch tot 32 wijzende cellen per scenario toe, maar dat wil niet zeggen dat je dat ook moet doen. 

Boven vier of vijf variabelen worden scenario’s lastig te vergelijken en nog lastiger uit te leggen aan iemand anders. Als je model zoveel bewegende delen nodig heeft, is het waarschijnlijk tijd om het te herstructureren in plaats van alles in één scenario te proppen.

Tot slot 

Elke spreadsheet bevat ergens aannames. Dat kan een groeipercentage zijn, een prijs of een rente.

What-if-analyse haalt die aannames niet weg. Het laat je zien wat er gebeurt als ze onjuist blijken, en daar draait goed beslissen grotendeels om.

Als dit nieuw voor je is, begin dan niet met een model met vijf variabelen en drie geneste scenario’s. Bouw eerst iets kleins: één input, één output, één keer Doelzoeken, en leer hoe input en resultaat samen bewegen. Het complexe werk kan later, zodra de basis logisch is.