Goal Seek gebruiken in Excel: een gids met echte voorbeelden

Hoe Goal Seek werkt

Goal Seek gebruik je wanneer je het gewenste resultaat van een formule kent, maar nog niet de invoerwaarde die daarvoor nodig is. Je werkt achteruit door de gewenste uitkomst in te stellen en Excel de invoervariabele te laten aanpassen om de oplossing te vinden.

Veelvoorkomende toepassingen van Goal Seek

Hoewel Goal Seek in verschillende vakgebieden wordt gebruikt, zijn de meest voorkomende toepassingen financiële modellen en onderwijssituaties. Enkele van deze toepassingen zijn:

• Budgetprognoses: Goal Seek helpt te bepalen hoeveel spaargeld of investering nodig is om een financieel doel te bereiken. Wil je bijvoorbeeld eind van het jaar een bepaald bedrag gespaard hebben, dan kan Goal Seek de benodigde maandelijkse inleg berekenen.
• Leningaflossingen: Het kan het leenbedrag berekenen dat je je kunt veroorloven op basis van een vaste maandelijkse betaling. Als je een specifiek maandelijks bedrag kunt betalen, kan Goal Seek de hoofdsom aanpassen om te zien hoeveel je kunt lenen.
• Break-evenanalyse: Bedrijven kunnen Goal Seek gebruiken om het verkoopvolume te vinden dat nodig is om de kosten te dekken en break-even te draaien. Door de winst op nul te zetten, kan Goal Seek de verkoophoeveelheid of prijs aanpassen om het break-evenpunt te bepalen.
• Cijferberekeningen: Leerlingen en docenten kunnen Goal Seek gebruiken om te bepalen welk cijfer nodig is op een eindexamen om een gewenst eindcijfer te halen. Wil een student bijvoorbeeld weten welke score nodig is om uit te komen op 85%, dan kan Goal Seek dat berekenen op basis van de huidige cijfers en het wegingpercentage van het examen.
• GPA-doelen: Goal Seek kan ook bepalen welke cijfers nodig zijn in aankomende vakken om een specifieke GPA te bereiken of te behouden. Als een student zijn GPA naar een bepaald niveau wil tillen, kan Goal Seek bepalen welke cijfers in toekomstige vakken nodig zijn om dat doel te halen.

Laten we nu echte voorbeelden in Microsoft Excel bekijken.

Goal Seek gebruiken in Excel

Goal Seek toepassen op je datamodellen bestaat in de kern uit drie herhaalbare stappen.

Stap 1: Je data opzetten

Begin met het opzetten van je data en formules op een manier die Excel kan interpreteren. Zorg dat de formule die je wilt oplossen correct is gekoppeld aan de invoercel die zal worden aangepast.

Voorbeelddata voor een Goal Seek-berekening. Afbeelding door de auteur.

Volgens de formule in de afbeelding hierboven, omdat Resultaat C afhangt van Invoer A en Invoer B, kunnen we ofwel Invoer A of B wijzigen.

Stap 2: Goal Seek uitvoeren

Selecteer eerst de cel met de formule waarvan je het resultaat wilt aanpassen (bijv. C2). Ga naar het tabblad Gegevens, klik op Wat-als-analyse en kies Doelzoeken.

Goal Seek selecteren in Excel. Afbeelding door de auteur.

Geef in het dialoogvenster Doelzoeken de cel met de formule op (Stel cel in), het gewenste resultaat (Naar waarde) en de te wijzigen invoercel (Door de cel te wijzigen).

Dialoogvenster Doelzoeken. Afbeelding door de auteur.

Klik op OK om Excel de juiste invoerwaarde te laten vinden.

Laatste stap van Goal Seek. Afbeelding door de auteur.

Je kunt ook op Annuleren klikken om de Goal Seek-bewerking ongedaan te maken als je de oorspronkelijke waarden wilt behouden. Dit is gebruikelijk wanneer we niet tevreden zijn met de wijziging en daarna een andere Goal Seek-bewerking willen uitvoeren.

Stap 3: Het resultaat analyseren 

Na het uitvoeren van Goal Seek past Excel automatisch de geselecteerde invoercel aan om de doeluitkomst te behalen. De bijgewerkte waarde in de invoercel kun je nu analyseren om het resultaat van je model te begrijpen.

Resultaat van Goal Seek. Afbeelding door de auteur.

In de afbeelding hierboven hebben we door Invoer B te wijzigen van 100 naar 200 ons doel Resultaat C van 1000 bereikt. Deze drie stappen vormen het standaardproces voor het gebruik van Goal Seek in verschillende datamodellen. 

Laten we verkennen hoe deze stappen van toepassing zijn op het oplossen van problemen in de echte wereld. Deze voorbeelden laten zien hoe Goal Seek effectief veelvoorkomende financiële vraagstukken kan oplossen, zoals het bepalen van leningaflossingen of het behalen van verkoopdoelen.

Voorbeelden van Goal Seek in Excel

Laten we een paar voorbeelden van Goal Seek in Excel bekijken die je in je werk kunt tegenkomen. 

Voorbeeld 1: Leningaflossing berekenen

Stel dat je een huis wilt kopen en wilt bepalen wat het maximale leenbedrag is dat je je kunt veroorloven met een maandelijkse betaling van $1.000. Op basis van je onderzoek naar verschillende banken heb je een rente en looptijd in gedachten, maar je moet de hoofdsom van de lening bepalen die je aanvraagt. Je kunt Goal Seek gebruiken om dit op te lossen.

Stap 1: Je data opzetten 

Open een nieuw Excel-werkblad en maak de opzet zoals in de onderstaande afbeelding. 

Dataset voor leningaflossing. Afbeelding door de auteur.

Vul je onderzochte rente (bijv. 5%) in cel A2 in, de looptijd (bijv. 30 jaar) in cel B2 en een eerste schatting van het leenbedrag (bijv. $200.000) in cel C2. Voer in cel D2 de formule in voor het berekenen van de maandelijkse betaling met de PMT-functie:

=PMT(B1/12, B2*12, -B3)

Deze formule deelt de jaarlijkse rente door 12 (voor maandrente), vermenigvuldigt de looptijd met 12 (voor het totale aantal betalingen) en gebruikt het negatieve leenbedrag om de maandelijkse betaling te berekenen.

Stap 2: Goal Seek uitvoeren 

Ons doel is om het leenbedrag te vinden dat resulteert in een maandelijkse betaling van $1.000. Met andere woorden: we wijzigen cel C2 zodat D2 wordt ingesteld op $1.000. Zoals eerder geleerd, openen we Doelzoeken binnen de sectie Wat-als-analyse op het tabblad Gegevens om het volgende dialoogvenster te krijgen.

Goal Seek-opties voor het leningvoorbeeld. Afbeelding door de auteur.

In het dialoogvenster Doelzoeken voeren we het volgende in:

• Stel cel in: D2, de cel met de maandelijkse betaling.

• Naar waarde: Voer 1000 in (je gewenste maandbedrag).

• Door de cel te wijzigen: Voer C2 in (het leenbedrag).

Zodra we op OK klikken, zal Excel het leenbedrag in cel C2 aanpassen om te bepalen hoeveel je kunt lenen terwijl je maandbedrag $1.000 blijft.

Stap 3: De resultaten interpreteren 

De Goal Seek-bewerking draait en stopt bij de onderstaande afbeelding:

Goal Seek-resultaat voor het leningvoorbeeld. Afbeelding door de auteur.

Zodra we op OK klikken, toont Excel het aangepaste leenbedrag in cel C2. Dit is de maximale hoofdsom die je je kunt veroorloven met de gegeven rente, looptijd en maandbedrag.

Het aangepaste leenbedrag is $186.282. Dit maximale bedrag kunnen we ons veroorloven met een maandbedrag van $1.000. Voel je vrij om met verschillende getallen te experimenteren en de Goal Seek-bewerking meerdere keren uit te voeren.

Voorbeeld 2: Een omzetdoel behalen

Stel dat je als salesmanager een omzetdoel van $500.000 wilt behalen voor een specifiek product. Je kent de prijs per stuk al, maar je weet nog niet hoeveel stuks je moet verkopen om dit doel te halen. Goal Seek kan je helpen om het exacte aantal benodigde stuks te bepalen.

Stap 1: Je data opzetten  

Open een nieuw werkblad en voer de data in zoals in de onderstaande afbeelding. 

Dataset voor verkoopdoel. Afbeelding door de auteur.

In cel A2 voeren we de daadwerkelijke prijs per stuk in en in cel B2 een schatting voor het aantal stuks. We voeren de volgende formule in om de totale omzet in cel C2 te berekenen:

=A2 * B2

Deze formule vermenigvuldigt de prijs per stuk met het aantal verkochte stuks om de totale omzet te berekenen.

Stap 2: Goal Seek uitvoeren  

Laten we Doelzoeken openen binnen de sectie Wat-als-analyse op het tabblad Gegevens om het volgende dialoogvenster te krijgen.

Goal Seek-opties voor het verkoopdoelvoorbeeld. Afbeelding door de auteur.

In het dialoogvenster Doelzoeken voeren we het volgende in:

• Stel cel in: Typ C2 (je totale omzetcel).

• Naar waarde: Voer 500000 in (je gewenste omzetdoel).

• Door de cel te wijzigen: Voer B2 in (het aantal verkochte stuks).

Excel past het aantal verkochte stuks in cel B2 aan om te bepalen hoeveel stuks je moet verkopen om $500.000 omzet te halen.

Stap 3: De resultaten interpreteren  

De Goal Seek-bewerking draait en stopt bij de onderstaande stap:

Goal Seek-resultaten voor het verkoopdoelvoorbeeld. Afbeelding door de auteur.

Zodra je op OK klikt, toont Excel het aangepaste aantal stuks in cel B2. Het resultaat is 2.000 stuks — je moet 2.000 stuks verkopen à $250 om je omzetdoel van $500.000 te halen. Als de prijs per stuk of het omzetdoel later verandert, kun je de Goal Seek-bewerking opnieuw uitvoeren met de bijgewerkte waarden.

Veelvoorkomende fouten bij Goal Seek en hoe je ze oplost

Als je de twee voorbeelden hierboven bekijkt, volgen we steeds dezelfde stappen om de resultaten te verkrijgen. Toch kunnen enkele veelvoorkomende fouten je berekeningen verstoren wanneer je Goal Seek in Excel gebruikt. Laten we daar eens naar kijken.

1. Excel Goal Seek heeft mogelijk geen oplossing gevonden

Een van de meest voorkomende problemen met Goal Seek is wanneer er geen oplossing wordt gevonden die aan je doel voldoet. Dit kan in twee scenario’s gebeuren:

• De oplossing bestaat niet.
• De oplossing kon niet convergeren binnen de gedefinieerde stappen.

Laten we dit begrijpen aan de hand van het verkoopdoelvoorbeeld dat we eerder zagen. Stel dat we per ongeluk de prijs per stuk als $0 hebben opgegeven in plaats van $250. De data ziet er dan als volgt uit:

Dataset voor verkoopdoel met fout. Afbeelding door de auteur.

We volgen onze gebruikelijke Goal Seek-stappen om de bewerking uit te voeren:

Goal Seek op de dataset voor verkoopdoel met fout. Afbeelding door de auteur.

Wat gebeurt er nadat we op OK klikken?

Fout: Goal Seek heeft mogelijk geen oplossing gevonden. Afbeelding door de auteur.

We zien het resultaat “Goal Seek heeft mogelijk geen oplossing gevonden.” Deze fout trad op omdat de vergelijking niet oplosbaar is. Voordat je een Goal Seek-bewerking uitvoert, is het essentieel om de formule en de invoerwaarden te controleren om te zorgen dat deze oplosbaar is.

Als je zeker weet dat er een oplossing bestaat, kan het zijn dat Goal Seek niet kon convergeren binnen de toegewezen 100 stappen. Om Goal Seek te helpen naar een oplossing te convergeren:

1. Maximale iteraties verhogen: Met een hogere waarde kan de Goal Seek-bewerking langer draaien. Je kunt dit wijzigen via Bestand -> Opties -> Formules -> Max. iteraties.
2. Begin met een betere schatting: Als je het bereik van de juiste waarde kunt inschatten, voer dan een startwaarde binnen dat bereik in.

Controleren of de vergelijking oplosbaar is en voldoende stappen toestaan om te convergeren, voorkomt de foutmelding dat er geen oplossing is gevonden.

2. Fout: De cel moet een waarde bevatten

Goal Seek verwacht dat de cel die we wijzigen een waarde bevat en geeft anders de fout “De cel moet een waarde bevatten.” Dit komt vaak voor wanneer de waarde in de cel is afgeleid van andere cellen via een formule.

Laten we de fout begrijpen met hetzelfde verkoopdoelvoorbeeld. Stel dat de prijs per stuk wordt berekend door de totale kosten te delen door het totaal aantal stuks. Onze data-opzet ziet er dan zo uit:

Bijgewerkte dataset voor verkoopdoel. Afbeelding door de auteur.

Als we Goal Seek proberen uit te voeren, treedt er een fout op wanneer we cel A2 selecteren voor de optie Door de cel te wijzigen.

Fout: De cel moet een waarde bevatten. Afbeelding door de auteur.

Dit komt doordat Goal Seek niet kan worden uitgevoerd wanneer de te wijzigen cel geen waarde bevat. Zorg ervoor dat de te wijzigen cel een waarde bevat om deze fout te voorkomen.

Conclusie

Dit artikel introduceerde je in een veelgebruikte What-if-analysetool — Goal Seek. Aan de hand van meerdere scenario’s leerden we hoe je deze in Excel in eenvoudige stappen gebruikt. We begrepen ook de veelvoorkomende fouten tijdens de implementatie en hoe je die voorkomt.

Ik moedig je aan om met Goal Seek te experimenteren in je datamodellen, zodat je vertrouwd raakt met de tool en de toepassingsmogelijkheden. Ben je geïnteresseerd om meer te leren over financiële modellering in Excel met tools die vergelijkbaar zijn met Goal Seek, bekijk dan onze cursus Financial Modelling in Excel voor beginners.