14  Doelzoeken

Doelstellingen

  • Methode Doelzoeken.
  • Toepassingsgebied van Doelzoeken.
  • Tips gebruik Doelzoeken.

Een formule is uit een of meerdere variabelen opgebouwd. Meestal wil je voor bepaalde waarden van de variabelen weten wat de uitkomst van de formule is. Soms komt ook het omgekeerde voor. Voor een bepaalde uitkomst wil je dan weten welke waarden de variabelen moeten hebben.

Wanneer de uitkomst van een formule maar van één variabele afhangt kun je de methode Doelzoeken gebruiken. Met deze methode wordt de waarde van de variabele net zo lang gewijzigd totdat de formule de gewenste uitkomst heeft.

Wanneer de uitkomst van een formule van meerdere variabelen afhangt moet je Excels Oplosser gebruiken.

Dialoogvenster Doelzoeken

Doelzoeken kun je oproepen via tab Gegevens > Wat-als-analyse (groep Voorspelling) > Doelzoeken.

Figuur 14.1: Dialoogscherm Doelzoeken.

In het dialoogvenster moet je drie waarden specificeren:

Cel instellen
Het adres van de cel welke de formule bevat waarvoor je een bepaalde uitkomst wilt.
Op waarde
De gewenste uitkomst van de formule, het doel dus.
Door wijzigen van cel
Het adres van de cel waarvan de waarde gewijzigd moet worden om het doel te behalen. Dit is de cel voor de varabele.

14.1 Break-even koffiebar

Stel je wilt een koffiestand in een voetbalstadion runnen. De maandelijkse kosten voor huur, personeel en diversen zijn bekend. Je weet ook wat de inkoop- en verkoopprijs voor een kop koffie is. Voor de berekening van het maandelijkse resultaat is een berekeningsmodel gemaakt, zie Figuur 14.2, afhangend van het aantal verkochte koppen koffie.

Figuur 14.2: Berekeningsmodel voor koffiebar stadion.
Informatiebehoefte
Hoeveel koppen koffie moet je maandelijks verkopen om quitte te spelen?

Bij dit eenvoudige voorbeeld kun je het antwoord gemakkelijk met de hand uitrekenen. Echter voor dergelijke vraagstukken is Doelzoeken zeer geschikt.

Taak 14.1 Bestand: Stadionkoffie.xlsx

  1. Open het bestand.

  2. Kies tab Gegevens > Wat-als-analyse (groep Voorspelling) > Doelzoeken.

  3. Specificeer de waarden voor Doelzoeken.

Figuur 14.3: Doelzoekwaarden voor de koffiebar.
  • Cel instellen: De cel met het maandresultaat, B18.
  • Op waarde: Voor een break-even is dat 0.
  • Door wijzigen van cel: Dit is de cel met het aantal koppen koffie dat verkocht moet worden, B16.
  1. Klik op OK.

Excel gaat nu aan het rekenen en vind als waarde 562,5. Omdat je geen halve koppen koffie verkoopt moet je zelf het antwoord afronden op 563.

14.2 Tips doelzoeken

Het is mogelijk dat de resultaatfunctie zodanig in elkaar zit dat het gewenste antwoord niet bestaat. Ook is het mogelijk dat het antwoord wel bestaat, maar dat Excel het niet kan vinden. In dat geval verschijnt er een dialoogvenster waarin dat wordt aangegeven.

Bestaat de oplossing wel, maar kan Excel deze niet vinden loop dan de volgende oplossingsmogelijkheden langs:

  1. Controleer in het model of de cel waarin het resultaat berekend wordt wel afhangt van de te wijzigen cel. De resultaatcel moet altijd een formule of een functie bevatten.
  2. Controleer dat de te wijzigen cel alleen een waarde bevat en geen formule of functie.
  3. Kies andere beginwaarden voor de te wijzigen cel.
  4. Verhoog het maximum aantal iteraties (standaard is dit 100) via Bestand > Opties > Formules, zie Figuur 14.4
Figuur 14.4: Berekeningsopties voor doelzoeken.

14.3 Opgaven

Oefening 14.1 Freelancer (goal001)

Stel je wilt als freelancer gaan werken en je hebt je voorgenomen dit alleen te doen wanneer je tenminste € 5000,- per maand kunt verdienen. Voor elke uitgevoerde opdracht krijg je een provisie van 7,8%.

  1. Maak in een werkblad een model waarin het percentage provisie en de omzet kunnen worden ingevoerd en waarin het provisiebedrag berekend wordt.
  2. Bepaal via Doelzoeken de omzet die gemaakt moet worden zodat een provisie van € 5000 behaald wordt.

Omzet € 64102,56

Oefening 14.2 Betaling lening (goal002)

Met behulp van de functie BET kan de periodieke betaling van een lening worden berekend op basis van constante betalingen en een constant rentepercentage. In de volgende afbeelding is een voorbeeld van een dergelijke berekening te zien. Hierbij wordt de betaling aan het begin van elke termijn gedaan en na de laatste termijn is de lening volledig afgelost.

  1. Maak in een werkblad dit model.
  2. Bepaal met Doelzoeken hoeveel je kunt lenen wanneer je maximaal € 750,- per maand kunt aflossen.

Omdat een betaling als een uitgaande kasstroom wordt gezien wordt de uitkomst als een negatief getal weergegeven.

Geleend bedrag € 7280.

Oefening 14.3 Break-even bepalen (goal003)

Een ondernemer heeft te maken met kosten en opbrengsten die afhankelijk zijn van de geproduceerde hoeveelheid Q. Het verband tussen de totale kosten (TK) resp. de totale opbrengst (TO) en de geproduceerde hoeveelheid Q is als volgt:

\(\mathrm{TK = Q^2 + 8Q + 15}\) en \(\mathrm{TO = -5Q^2 + 80Q}\)

In de grafiek hierna zijn TK en TO uitgezet als functie van Q.

  1. Teken deze grafiek.
  2. Bepaal het break-even punt. Dit is de hoeveelheid Q waarvoor de opbrengsten en de kosten aan elkaar gelijk zijn. De grafiek laat zien dat er twee van deze punten zijn. Bepaal ze beide m.b.v. Doelzoeken en geef de antwoorden op 4 decimalen nauwkeurig.

Oplossing 1: Q = 0,2120 en TK = TO = 16,74

Oplossing 2: Q=11,7879 en TK = TO = 248,26

Oefening 14.4 Spaarbedrag (goal004)

Op 1 januari 2010 wordt een bedrag van € 1000 op een spaarrekening gestort. De renteopbrengst is 2,7% op jaarbasis en deze wordt steeds aan de spaarrekening toegevoegd. In de volgende afbeelding is de ontwikkeling van het bedrag op de spaarrekening voor eerste tien jaar te zien.

  1. Maak in een werkblad dit model.
  2. Bepaal met Doelzoeken hoeveel je aan het begin had moeten storten om na 10 jaar € 2500 euro op de spaarrekening te hebben.

Storting € 1915,29.