Hoofdstuk 26: Modelleren in Excel

Hoofdstuk 26: Modelleren in Excel 26.0 Inleiding In dit hoofdstuk leer je een aantal technieken die je kunnen helpen bij het voorbereiden van bedrijfsmodellen in Excel (zie hoofdstuk 25 voor wat bedoeld wordt met ‘modellen’). De focus ligt op bedrijfs- en financiële modellen, maar de technieken kunnen net zo goed worden gebruikt voor andere modellen. De technieken die je hier leert, zijn simpel. Je zult wel de basiskennis nodig hebben die in de andere hoofdstukken is besproken.

26.1 Voorspellingen Extrapoleren Net als dat investeringsadviseurs verplicht zijn ons te vertellen dat ‘resultaten behaald in het verleden geen enkele garantie bieden voor de toekomst’, zijn resultaten uit het verleden vaak wel de beste indicatie die er is. Als de oorzaak van een trend niet bekend is, is de meest logische aanname dat het op dezelfde manier zal doorgaan. Het is vaak handig om aan de hand van historische groei te berekenen en te voorspellen hoe de toekomst eruitziet als die groei doorzet. Historische groei berekenen

Er zijn verschillende manieren om de gemiddelde groei te berekenen. Een specifieke definitie van gemiddelde groei wordt aangeraden, een die heel gemakkelijk is om te berekenen en die in bijna alle gevallen het meest toepasselijk is voor het maken van modellen. De afbeelding hieronder laat zien hoe de huizenprijzen variëren over een bepaalde periode en de grafiek brengt deze variatie in beeld:

© 2010 Excel with Business

1

Hoofdstuk 26: Modelleren in Excel

De formule in cel C7 berekent de gemiddelde jaarlijkse groei van de verkoopprijs, als percentage. Om deze formule te gebruiken, kun je over het algemeen het volgende recept gebruiken: =100*(G4/C4)^(1/4)-100 =100*(<eindwaarde>/)^(1/aantal jaren groei>)-100 Je ziet dat de gegevens een periode van 5 jaar beslaan, maar er zijn maar 4 jaren van groei geweest tussen het eerste en het laatste jaar (dus 4 verschijnt in onze formule in blauw). Wat doet deze formule? Op zich hoeft je dit niet te weten om het te gebruiken, maar als je geïnteresseerd bent: het neemt de ratio van eind- tot beginwaarde en neemt daar de viermachtswortel van. Die wortel verdeelt de ratio over de vier jaar (het geeft dus de groei weer, welke die 4 jaar op rij nodig zou zijn om tot die ratio te komen). De vermenigvuldiging met en het aftrekken van 100 verandert de ratio t in een groei percentage. Voorspellingen met historische groei

Zodra de historische groei is berekend, is het gemakkelijk te gebruiken om de toekomstige groei te voorspellen vanaf het laatste jaar van de historische gegevens. In het voorbeeld van hierboven:


2


De formule in H4 vermenigvuldigt de prijs in 2009 met 1.01 (dus de prijs stijgt met de gemiddelde historische groei van 1% per jaar). De verwijzing naar de groeiwaarde (cel C7) is absoluut (en word dus als $C$7 weergegeven), zodat de formule gekopieerd kan worden naar de volgende jaren. Natuurlijk kan model nog beter gemaakt worden door andere relevante gegevens erbij te betrekken om toekomstige groei te voorspellen, zoals economische voorspellingen, de laatste economische cyclus etc. In essentie is de procedure hetzelfde, basisgegevens worden gebruikt om de meest nauwkeurige extrapolatie te krijgen die mogelijk is.

26.2 Scenariomodellen In hoofdstuk 25 over modelleringsprincipes staat beschreven hoe een model in stukken te verdelen, zoals in het schema hieronder. Met gebruik van de technieken voor het maken scenariomodellen, kun je dus verschillende invoerbronnen en aannames gebruiken in een model en dus verschillende scenario’s tonen.

Invoerbronnen Tussenliggende berekeningen

Resultaten

variabelen


3


Er zijn veel manieren om dit te doen – sommige zijn erg gemakkelijk en daar is geen nieuwe kennis van Excel voor nodig. Bijvoorbeeld: je kunt simpelweg twee versies van een model in twee aparte Excel Werkmappen opslaan, elk met zijn eigen aannames en basisgegevens. Soms is het handig om meerdere scenario’s in dezelfde werkmap te hebben staan. Hierdoor kun je sneller wisselen tussen scenario’s en het model kan ontwikkeld worden voor alle scenario’s tegelijk. Excel kan dit doen met Scenariobeheer. Met Scenariobeheer kun je een aantal cellen met aannames specificeren. In de ‘Scenario’s’ kunnen dan bepaalde waarden aan die cellen worden toegewezen, zoals in de volgende tabel bijvoorbeeld: Cel

Beschrijving

Celwaarde (Sterke groei scenario)

Celwaarde (Neutraal scenario)

Celwaarde (Minimale groei scenario)

B2

Jaarlijkse groei 20092014

5%

2%

1%

B3

Jaarlijkse groei 20142019

4%

1%

0%

B4

Inflatie aanname

2%

2%

2%

Met Scenariobeheer kun je één van de scenario’s in de tabel selecteren. De cellen B2, B3 en B4 krijgen dan de groeiwaardes die bij dat scenario horen. De rest van het model (dat deze groei gebruikt om het resultaat te berekenen) werkt dan met de aannames van dat scenario als basisgegevens. Je kunt Scenariobeheer vinden op het Lint > Tabblad Gegevens > Wat-als-analyse:

Als je de optie selecteert, dan krijg je het volgende venster te zien:


4


De Toevoegen… knop (met rood omcirkelt) geeft het volgende dialoogvenster waarin je het scenario kunt specificeren:

In het eerste veld (Scenarionaam) in de afbeelding hierboven kun je het scenario een naam geven. In het tweede veld kun je alle cellen die het scenario bepalen, aangeven (dit kun je doen door te klikken op de cellen terwijl je Ctrl ingedrukt hebt). Zodra de ‘Veranderende cellen’ gespecificeerd zijn, krijg je een nieuw scherm te zien waarin je de waarden kunt specificeren die ze in dat scenario moeten hebben. Dit voorbeeld laat de ‘Sterke groei’ waarden zien voor B2, B3 en B4:


5


Je ziet dat, omdat de cellen zijn opgemaakt als percentages, de waarden worden ingevoerd als fracties van 100 (zie hoofdstuk 28 over Getalopmaak). Zodra de scenario’s gespecificeerd zijn, kunnen ze geselecteerd worden door Scenariobeheer te openen, het gewenste scenario te selecteren en op ‘Weergeven’ te klikken:

26.3 Optimalisatie We zoeken vaak de beste manier om iets te doen; de goedkoopste, de snelste, de meest winstgevende. Excel heeft een hulpmiddel wat je hierbij kan helpen. 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,


6


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? Het hulpmiddel hiervoor in Excel dat kan helpen om deze vraag te beantwoorden is ‘Doelzoeken’. Net als Scenariobeheer kun je deze functie vinden op het Lint > Tabblad Gegevens > Wat-als-analyse.


7


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 krijgt de cel de opdracht om de kosten (C7) in te stellen op €150 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:

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


8


die je had ingesteld, is mislukt en op een willekeurig moment wordt afgebroken, zonder de waarde te produceren die je wilde zien).

26.4 Rangschikken Neem een model in gedachten waarbij het resultaat de winst is van de productlijnen van het bedrijf. Het is misschien handig om deze te rangschikken in omgekeerde volgorde zodat producten met een (relatief) lage winst onderzocht kunnen worden. Hoofdstuk 13: Sorteren & Filteren beschrijft hoe je gegevens kunt sorteren als de gegevens statisch zijn, maar te doen als de winst per maand verschilt? Deze paragraaf omschrijft een eenvoudige techniek om automatisch te rangschikken, zodat de resultaten automatisch aan de resultaten van het model aangepast worden. Hoog tot laag rangschikken

De functie =RANG.GELIJK geeft een specifieke waarde aan een rangschikking binnen een reeks van waarden. Dus:

De formule in cel D5 (en de rest van kolom D) geeft de rangschikking weer van cel C5 binnen de reeks C5:C9. Dus het product ‘Capesca’, met het hoogste winstpercentage in de tabel, krijgt de eerste plaats toebedeeld. Je kunt nu heel handig een lijst van resultaten opzetten die automatisch in de juiste volgorde komt te staan. Als je een lijst met een rangschikking in oplopende volgorde wilt opzetten, dan kun je de functiecombinatie INDEX/VERGELIJKEN gebruiken om producten te zoeken bij de juiste rangschikking (zie hoofdstuk 16 over Zoek- en verwijzingsfuncties):


9


Laag naar hoog rangschikken

Om in omgekeerde volgorde te rangschikken, moet je de rang aftrekken van het totaal aantal waarden, plus 1. De formule wordt dan: =AANTAL($C$5:$C$9)-RANG.GELIJK(C5;$C$5:$C$9)+1

Geavanceerd: twee objecten met dezelfde rangschikking

Om de mogelijkheid van ‘gelijkspel’ (twee objecten met dezelfde waarden en daardoor een gelijke rangschikking) te voorkomen, maak je gebruik van: =AANTAL($C$5:$C$9)-(RANG(C5;$C$5:$C$9)+AANTAL.ALS($C$5:C5;C5)-1)+1


10


26.5 Ontwikkelingsproces voor modellen Modellen ontwikkelen zich vaak stukje bij beetje en zo kan het lastig worden de structuur helder en logisch te houden. Om dit toch voor elkaar te krijgen, is het een goed idee een procedure te volgen welke de structuur in de gaten houdt tijdens de opzet. Deze paragraaf beschrijft de aanbevolen procedure. Het procedure wordt weergegeven in dit schema:

Bereik







Invoeren

Presenteren

Bereik Zet de modelstructuur op, bepaal waar de invoer, berekeningen en resultaten zullen komen staan en hoe de gegevens door het model heen gaan (zoals besproken in Hoofdstuk 25 over Modelleringsprincipes). Invoeren Voeg gegevens en berekeningen toe aan het model. Koppel waar mogelijk de gegevens in de ‘ruwe’ vorm (bijvoorbeeld in een apart werkblad) zodat je gemakkelijk kunt aanpassen en anderen kunnen zien waar het op gebaseerd is. Gebruik kleurcodes om de bron van de gegevens inzichtelijk te maken. Presenteren Maak je model helder op door de principes uit hoofdstuk 25 en de hoofdstukken 2730 toe te passen.


11


Hoofdstuk 26: Modelleren in Excel

Recommend Documents