Hoofdstuk 24: De Wat-als-analyse

Hoofdstuk 24: De Wat-als-analyse 24.0 Inleiding Dit hoofdstuk gaat over een aantal hulpmiddelen om modellen voor te bereiden die in Excel onder de Wat-als-analyse vallen. Deze technieken worden ook besproken in deze context in hoofdstuk 26: Modelleren in Excel. Deze technieken zijn eenvoudig, maar je zult wel veel van de basiskennis wat al besproken is in deze cursus nodig hebben.

24.1 Modelleren met scenario’s In hoofdstuk 25: Modelleringsprincipes wordt beschreven hoe een model kan worden opgebroken in verschillende stappen, zoals je in het schema hieronder kunt zien. Modelleren met scenario’s bestaat uit technieken die ervoor zorgen dat een model verschillende gegevensinvoer of aannames kan hebben, oftewel, verschillende scenario’s.

Gegevensinvoer

variabelen

Tussenliggende berekeningen

Resultaten

Er zijn vele manieren om dit te doen, sommige zijn zeer eenvoudig en vereisen geen nieuwe kennis van Excel. Bijvoorbeeld: je kunt simpelweg twee versies van een model in twee afzonderlijke Excel werkmappen opslaan; ieder met zijn eigen aannames en gegevensinvoer. Soms is het beter om meerdere scenario's in een werkboek te zetten, dit maakt het gemakkelijker om te wisselen tussen scenario's en het model kan voor alle scenario’s tegelijk worden opgezet. De functie Scenariobeheer is hiervoor bedoeld. Met Scenariobeheer kun je een aantal cellen specificeren die aannames bevatten. Je kunt dan 'Scenario's' maken die bepaalde waarden aan die cellen toekennen. Zoals in het volgende voorbeeld:

© 2010 Excel with Business

1

Hoofdstuk 24: De Wat-als-analyse

Celwaarde (Neutraal scenario)

Celwaarde (Minimale groei scenario)

5%

2%

1%

Jaarlijkse groei 2014-2019

4%

1%

0%

Inflatie prognose

2%

2%

2%

Cel

Beschrijving

Celwaarde (Sterke groei scenario)

B2

Jaarlijkse groei 2009-2014

B3

B4

In dit voorbeeld kun je met Scenariobeheer een van de drie scenario's binnen de tabel selecteren. De cellen B2, B3 en B4 zullen dan de groei-waarden toegekend krijgen die bij dat scenario horen. De rest van het model (dat deze groeiwaarden gebruikt om voorspellingen te berekenen) zal dan werken met de aannames van dat scenario als uitgangspunt. Je kunt Scenariobeheer vinden op het Lint, tabblad Gegevens, onder de knop Wat-als analyse:

Als je op deze knop klikt, zie je het volgende venster:


2


Als je klikt op Toevoegen (rood omcirkeld), dan krijg je het volgende venster te zien waarmee je het scenario kunt specificeren:

Het eerste veld (Scenarionaam) in het venster hierboven, kun je gebruiken om het scenario een naam te geven. Het tweede veld kun je gebruiken om alle cellen aan te geven die deel uitmaken van het scenario (dit kun je doen door een cel te selecteren en terwijl je de CTRLtoets ingedrukt houdt, met de muis te klikken op de andere cellen die je wilt opnemen). Nadat de 'Veranderende cellen' zijn gespecificeerd, zal er een venster openen waarin je aan


3


kunt geven welke waarden deze cellen moeten krijgen in het scenario. Dit voorbeeld laat het 'Sterke Groei' scenario zien dat de waarden specificeert voor de cellen B2, B3 en B4:

Het is belangrijk er op te letten dat, omdat de cellen zijn opgemaakt als percentages, de waarden gedeeld moeten worden door 100 (. Binnen scenario’s wordt geen opmaak aangebracht. Als de scenario's eenmaal opgesteld zijn, kunnen deze geselecteerd worden door het openen van Scenariobeheer, het gewenste scenario te selecteren en dan te klikken op 'Weergeven'.

Bedenk wel dat er meer flexibele manieren bestaan om scenario’s te maken (bijvoorbeeld door de zoekfuncties te gebruiken beschreven in hoofdstuk 16). Scenariobeheer is goed bruikbaar, afhankelijk van de situatie.


4


Tip.   

Maak eerst een scenario van de basis situatie. De scenario manager overschrijft de waarde in cel. Zonder basis scenario krijg je de originele situatie niet terug. Scenario’s rekenen jouw model geheel door. Van belang is dat je we weg van invoer naar uitvoer berekend hebt opgelost. Gebruik je veel scenario’s weeg dan om in de knop Scenario: (te vinden in het onderdeel Kies opdrachten uit: Niet in het lint opgenomen). Sta je dan in een scenariomodel, dan kun je daar het gewenste scenario direct selecteren.

24.2 Optimalisatie We zijn vaak op zoek naar de beste manier om iets te doen: de goedkoopste, de snelste, de meest winstgevende, etc. Excel heeft een hulpmiddel om deze te vinden. Je kunt een model opzetten wat aangeeft wat de kosten zullen zijn gebaseerd op bepaalde gegevens. Bijvoorbeeld, als je een ronde vijver zouden maken, met een bodem van beton, kun je een simpel model opzetten dat je de kosten laat zien van het beton in verhouding tot de doorsnee van de vijver:

Dit model neemt de kosten per vierkante meter beton en de doorsnee van de vijver op als invoer en berekent hiermee eerst het benodigde oppervlakte voor het beton, daarna de totale kosten van het beton door de oppervlakte te vermenigvuldigen met de kosten per vierkante meter. Je zou in plaats hiervan ook de volgende vraag kunnen stellen: ik heb een budget van €150 voor beton; wat is de maximale doorsnee van de vijver die ik kan aanleggen voor dat geld?


5


Het hulpmiddel in Excel dat het antwoord op deze vraag kan geven is 'Doelzoeken'. En net zoals bij Scenariobeheer, kun je het openen via Wat-als-analyse op het tabblad Gegevens op het Lint.

De afbeelding hierboven laat het venster Doelzoeken zien. Doelzoeken zal een bepaalde cel (welke een formule moet bevatten) instellen op een bepaalde waarde door de waarde van de cel te veranderen welke deel uitmaakt van die formule. In dit geval wordt de waarde in cel (C7) berekend door de waarde van C4 -de doorsnee van de vijver- te veranderen. Door op OK te klikken in het venster Doelzoeken verandert de waarde van C4 als volgt:


6


Let er wel op dat C7 nog steeds een formule bevat, de waarde is alleen veranderd in €150 omdat de invoer van de andere cellen in de berekening veranderd zijn. Ook zou het niet mogelijk zijn om sommige waarden in stellen voor C7. Het kan bijvoorbeeld geen negatief getal zijn, hoe groot of klein de doorsnee ook zou zijn. Als je zoiets probeert, dan komt Doelzoeken met de volgende melding:

In zulke situaties moet je voorzichtig zijn dat de invoer in een cel niet ingesteld is op de een of andere bizarre waarde (wat er dan gebeurd, is dat de opdracht om de waarde te vinden die je had ingesteld, is mislukt en op een willekeurig moment wordt afgebroken, zonder de waarde te produceren die je wilde zien). Tip 

Net als bij scenario’s wordt de celinhoud van de invoer overschreven. Zorg dus dat je de uitgangssituatie eerst vasthoud. Bij doelzoeken is dat lastiger, omdat je geen verschillende uitgangsposities kan bewaren.


7


Hoofdstuk 24: De Wat-als-analyse

Recommend Documents