Achteruit rekenen in Excel

Stel je voor: je hebt een budget dat je niet mag overschrijden. In Excel heb je een overzicht van je kosten, maar het totaal is helaas te hoog. Je kosten zullen moeten wijzigen om het gewenste budget te behalen…

Er zijn veel voorbeelden te bedenken, waarbij je in een Excel-model wilt uitkomen op een gewenst totaal door een of meerdere variabelen te wijzigen. In Excel zijn er twee manieren om achteruit te rekenen.

Wanneer je slechts één variabele wilt wijzigen, gebruik je de functie Doelzoeken. Wil je meerdere variabelen wijzigen? Gebruik dan de functie Oplosser. Aan de hand van verschillende voorbeelden leggen wij in deze blog beide opties stap voor stap uit.

Allereerst gaan wij in deze blog aan de slag met onderstaand voorbeeld. In de oorspronkelijke situatie is het totaal aan kosten gelijk aan € 128,-. Stel je nu voor dat wij willen uitkomen op een totaal van € 120,-. We willen dit bedrag bereiken door de kosten voor de inkoop te wijzigen.

Een optie is natuurlijk om handmatig de inkoopprijs (cel D4) te wijzigen. Je doet dit net zolang tot je uitkomt op het gewenste totaal. In het rechtervoorbeeld zie je echter dat de overige kosten afhankelijk zijn van de kosten voor inkoop. Wanneer de inkoop verandert, veranderen dus ook de andere kosten. In dit geval is het daarom heel lastig om het juiste getal te vinden.

Gelukkig kan Excel dit een stuk sneller dan wij het handmatig kunnen. Excel wijzigt het getal in cel D4, totdat het juiste resultaat zichtbaar is! In onderstaande tekst leggen we uit hoe we deze opdracht aan Excel kunnen geven.

De functie Doelzoeken

Doelzoeken is een methode om vanuit een formulecel terug te rekenen naar het gewenste resultaat. Om de Doelzoeker te gebruiken is het belangrijk dat het totaal door middel van een formule verbonden is met de te wijzigen cel. Wanneer dit niet het geval is, geeft de functie een foutmelding. In bovenstaand voorbeeld zie je dat de cellen door middel van formules met elkaar verbonden zijn.

Onze tip: de formules zijn zichtbaar gemaakt door op het tabblad Formules te klikken op de knop Formules weergeven. Werk je in de Nederlandstalige versie van Excel? Dan kun je ook de sneltoets Ctrl + T gebruiken.

Om de functie Doelzoeken te gebruiken ga je staan op de cel met de formule. In ons geval is dit de cel waar de functie Som staat (cel D10). Klik vervolgens op het tabblad Gegevens op de knop Wat-als-analyse en kies voor Doelzoeken, zodat het volgende venster verschijnt.

Wij hebben het venster alvast ingevuld. We willen cel D10 instellen op de waarde 120 door cel D4 te wijzigen. Dit is de cel waarin de inkoopprijs staat. Wanneer je op OK klikt, gaat Excel voor je aan de slag en komt met de oplossing! Zoals je ziet, is niet alleen de inkoop veranderd, maar zijn ook de overige kosten volgens de formules aangepast.

De functie Oplosser

Wanneer je meerdere variabelen wilt veranderen om tot het gewenste resultaat te komen, kun je in Excel gebruik maken van de Oplosser.

Let op: wanneer je gebruik wilt maken van de Oplosser, moet je deze eerst installeren. De Oplosser wordt standaard meegeleverd, maar niet geïnstalleerd. Om de functie te installeren ga je naar de tab Bestand en kies je voor Opties – Invoegtoepassingen.

Kies vervolgens in het vak Beheren voor Excel-invoegtoepassingen. Waarschijnlijk is deze optie al geselecteerd. Klik dan op Start, zodat het venster Invoegtoepassingen verschijnt. Activeer hier de Oplosser-invoegtoepassing en klik op OK. Op het tabblad Gegevens verschijnt nu in het groepsvak Analyse de opdracht Oplosser en je kunt de functie gebruiken.

In het voorbeeld waarmee wij gaan werken hebben wij een budget van € 825.000,-. In onderstaand voorbeeld zie je aan de linkerkant de huidige situatie. Zoals je ziet, overschrijden we nu ons budget en moeten we gaan bezuinigen.

Met de Oplosser kan Excel voor ons uitrekenen hoe we weer binnen ons budget kunnen blijven. Wanneer we de Oplosser gebruiken zonder voorwaarden op te geven, werkt de functie als een kaasschaaf. Op alle kostenposten wordt een beetje bezuinigd.

In het rechtervoorbeeld hebben wij de Oplosser uitgevoerd zonder voorwaarden op te geven. Je ziet dat iedere afdeling met € 5.400,- is gekort.

Maar wat nu als dit helemaal niet de bedoeling is? Wij gaan nu een aantal voorwaarden stellen:

• er mag niet worden bezuinigd op de Research and Development;
• het verkoopbudget mag niet onder € 122.500,- dalen;
• het budget van PR & Marketing mag niet meer onder € 443.000,- dalen.

Wanneer je op de knop Oplosser klikt, verschijnt het volgende venster:

Wij hebben de eerste velden vast ingevuld. In cel B10 staat het totale budget. Dit is de cel waarvoor wij de doelfunctie willen bepalen. Wij hebben ervoor gekozen om het gehele budget te gebruiken. Zoals eerder genoemd, is het budget € 825.000,-. Dit hebben wij dan ook ingevoerd in het veld Waarde van. In het veld Door veranderen van variabelecellen hebben wij het bereik geselecteerd van de te wijzigen cellen (cellen B4 tot en met B8).

Om voorwaarden op te geven aan de Oplosser, klik je op de knop Toevoegen. Het onderstaande venster verschijnt. Wij hebben in het totaal drie keer op de knop Toevoegen geklikt om de drie voorwaarden in te voeren.

Wanneer alle voorwaarden zijn ingevoerd, klik je op de knop Oplossen. Het volgende venster verschijnt.

Wanneer je in dit venster op OK klikt, zie je dat het totale budget is veranderd in € 825.000,-. De bezuinigingen zijn in overeenstemming met onze voorwaarden doorgevoerd!

Wil je graag wat meer ‘spelen’ met de variabelen en voorwaarden? Dit kan natuurlijk! Met de Oplosser kun je rapporten maken en werken met meerdere scenario’s. Dit valt echter buiten de scope van deze blog.

Zoals je ziet, kun je met behulp van Excel heel handig achteruit rekenen om tot een gewenste oplossing te komen. Wil je meer van dit soort handige functies leren? Volg dan een van onze cursussen Excel!