Gegevensanalyse met Excel

Gevoeligheidsanalyse in Excel

Laten we een voorbeeld nemen van een lening. De gegevens voor de lening zien er als volgt uit:

In cel B7 is de functie BET van Excel gebruikt om de maandelijkse betaling te berekenen. Deze functie ziet er als volgt uit:

=BET(B4/12;B5*12;B3)

Neem deze gegevens in een lege excel-sheet over.

Zo’n berekening is natuurlijk op zich al nuttig. Beter zou het echter zijn als u in één oogopslag zou kunnen zien wat de maandelijkse betaling wordt als de hoofdsom verandert of als de rente verandert. Dit kunt u eenvoudig voor elkaar krijgen door toepassing van de functie Data – Tabel in Excel. Hiervoor moet u het volgende doen:

Zet nu in de Excel-sheet de volgende tabel op:

Zoals u kunt zien, hebben we op rij de hoofdsom veranderd en op kolom de rente steeds veranderd. In de cel boven de rente en links van de hoofdsom hebben we een verwijzing gemaakt naar de cel met de berekening voor het maandelijks te betalen bedrag.

Selecteer nu de hele tabel (10 rijen en 5 kolommen) en ga naar de menuoptie Data – Tabel.

Vul bij Rij-invoercel de cel B3 in en bij Kolominvoercel cel B4 in. Klik op OK.

Zoals u kunt zien, is de tabel gevuld met de juiste waarden. U kunt eventueel de opmaak van de gegevens aanpassen.

Uit deze tabel kunt u uitlezen wat de maandelijkse betaling zal zijn bij een bepaalde hoofdsom en een bepaalde rentepercentage. Maar misschien vraagt u uzelf af hoeveel de hoofdsom zal zijn als er maximaal 125 EURO per maand betaald mag worden. Om dit bedrag te berekenen gebruiken we de tool Doelzoeken van Excel.

Ga hiervoor naar de menuoptie Extra – Doelzoeken. Vul de volgende verwijzingen in:

Klik op OK en Excel zal voor u het juiste bedrag berekenen.

De Oplosser / Solver

De tool Extra – Doelzoeken is goed te gebruiken bij simpele vraagstukken. Bij wat meer ingewikkelde vraagstukken is de Doelzoeker beperkt. Het kan bijvoorbeeld zijn dat het model rekening moet houden met restricties. Misschien zijn er meerdere cellen die veranderd moeten worden. Voor al deze vraagstukken gebruiken we de Oplosser/ Solver.

De oplosser is een invoegtoepassing van Excel, dat wil zeggen een extra optie die geïnstalleerd moet zijn voordat u het kunt gebruiken. Om te zien of op uw computer de Oplosser is geïnstalleerd doet u het volgende:

Kijk eerst onder de menuoptie Extra. Als u de Oplosser in de lijst met menu’s ziet, dan kunt u deze functie direct toepassen. Is de optie niet beschikbaar in het menu Extra, dan gebruikt u de menuoptie Extra – Invoegtoepassingen om uit de verschenen lijst de optie Oplosser / Solver aan te vinken. Vindt u de Oplosser niet in deze lijst met Invoegtoepassingen, dan is deze component van Excel niet op uw computer geïnstalleerd. We raden u aan in dat geval de optie alsnog te installeren.

Om u te laten zien hoe de Oplosser toegepast kan worden, gebruiken we de spreadsheet Oplosser.xls. De Oplosser kun u hier downloaden.

Zoals u kunt zien, wordt dit bestand gebruikt om een simpele productieproces in kaart te brengen. Er worden 5 producten gemaakt. De grondstoffen die hiervoor nodig zijn (met het aantal eenheden) vindt u op de rijen 5 t/m 10. De kosten per grondstof vindt u in kolom G.

De verkoopprijs voor de producten vindt u in rij 12. In rij 13 vindt u het aantal te produceren eenheden van ieder product. Verder zijn de omzet per eenheid, de kosten en uiteindelijk de winst berekend.

De vraag is nu: Hoeveel stuks van ieder product moeten we produceren om een maximale winst te behalen?

Hiervoor gebruiken we de Oplosser. Ga naar de menuoptie Extra – Oplosser. We zullen in dit scherm een aantal wijzigingen aanbrengen.

We willen namelijk de winst maximaliseren door het aantal te produceren eenheden te veranderen. Voordat we Excel de beste productmix voor ons gaat berekenen, moeten we ook aangeven met welke restricties rekening gehouden moet worden. In dit geval hebben we te maken met de volgende restricties:

1. Voorraad moet groter of gelijk zijn aan gebruikte grondstof.
2. De te produceren aantallen moeten positief zijn (>=0)
3. De te produceren aantallen moeten gehele getallen zijn (integer).

Door op Toevoegen / Add kunt u deze voorwaarden inbouwen. Als u de alle criteria gegeven heeft, dan ziet het scherm als volgt uit:

Klik nu op Oplossen / Solve.

Excel is druk aan het rekenen voor u. Als een oplossing gevonden wordt dan krijgt u het volgende scherm te zien:

Kies in dit scherm de optie Oorspronkelijke waarden herstellen en selecteer bij Rapporten voor een Antwoordrapport / Answer. Klik daarna op OK. Zoals u ziet, verschijnt een extra werkblad met daarop de uitkomsten van de Oplosser. Toepassing van restricties is aldus zeer belangrijk bij het gebruiken van de Oplosser.

In dit artikel hebben we geprobeerd u te laten zien hoe u een aantal tools van Excel kunt gebruiken om complexere vraagstukken op te lossen. We wensen u veel succes toe bij toepassing van deze functies tijdens uw werkzaamheden!