Om de principes van de vorige post duidelijk te maken (en aan te tonen dat we met concrete oplossingen komen :-)) hieronder voorbeeld van hoe je een bestaand excel model kan omtoveren in een gebruiksvriendelijk model met een waanzinnig dashboard output. Het volgt dezelfde structuur en geeft een stap per stap handleiding van hoe we te werk zijn gegaan.
We gebruiken een bestaand model (download hier van www.exinfm.com). Het model evalueert wat het meest voordelig is: een auto aankopen dan wel te leasen. Het is een redelijk eenvoudig model en dient enkel om een aantal principes te illustreren. De methode die wordt voorgesteld kan echter zonder problemen toegepast worden om de meest complexe modellen.
Hier gaan we dan… (de resulterende excel staat in de ‘nuttige excels’ lijst voor download)
0. Voorbereiding
Het model ziet er zo uit:
1. Weet wat je wil
We willen een eenvoudige tool die ons snel en gemakkelijk de verschillen tussen de aankoop van een auto en het leasen van de wagen visueel duidelijk maakt.
Het is trouwens absoluut NIET de bedoeling om een debat op te starten of het model correct zou zijn opgesteld en hoe een leasing contract juist berekend wordt!
2. Denk output
We willen aan de hand van enkele parameters inzicht krijgen in:
- Vergelijking van de geactualiseerde huidige waarde (NPV) van de 2 alternatieven: Dit is de belangrijkste output voor een investeringsbeslissing. We opteren om de numerieke gegevens te gebruiken
- Belangrijk om de resulterende cash-flows te analyseren om de impact op de liquiditeit te bepalen. We kiezen de bar-chart per maand.
- Hoe verandert de huidige waarde als functie van de interestvoet. Sensitiveitsanalyse, we kiezen voor een lijngrafiek met op de x-as de interestvoet en op de y-as de huidige waarde van de huidige kost. De interestvoet varieert van 5% tot 15% (per procent).
3. Gebruik de black-box methode
Schematisch weergave van de black-box methode:
Definities:
- Model parameter:
- Verkoopsbelasting op auto’s
- Model input aankoop:
- Officiële prijs
- Onderhandelde prijs
- Verkeersbelasting
- Model input Lease:
- Lease periode (maanden)
- Verkoopswaarde aan het eind van de lease
- Interest voet (APR)
- Maandelijkse betaling voor lease
- Model output:
- NPV van totale kost van aankoop wagen
- NPV van totale kost van de lease van de auto
- Tabel met betaling per maand in aankoop scenario
- Tabel met betaling per maand in lease scenario
- Tabel met NPV van de totale kost van de lease van de auto voor de verschillende interestvoeten
Hoe?
1. Creeër een nieuwe sheet ‘IN-PARAM’
2. Link de inputs door naar het model
3. Creeër een nieuwe scheet ‘OUT’
4. Link de verschillende output door naar sheet (opmerking: om de sensitiviteitsanalyse gemakkelijk te kunnen opstellen, maken we gebruik van de functie ‘table’, excel laat echter niet toe dat input en output op verschillende sheet staan, hierdoor zijn we verplicht om de tussenberekening op de sheet ‘IN-PARAM’ uit te voeren)
5. Creeër een nieuwe sheet ‘Dashboard’
6. Ontwerp het dashboard en maak de verschillende grafische ouput voor het dashboard
4. Test test test test
Uiteraard belangrijk, vermits het hier over een eenvoudig model gaat, is het relatief gemakkelijk om te controleren of het resultaat overeenkomt met de resultaten van het oorspronkelijk model. Het valt redelijk veel voor dat er hierdoor bugs in het oorspronkelijke model bovenwater komen.
5. Verhoog gebruiksvriendelijkheid – user interface
Vermits de sheets ‘OUT’ en ‘Blackbox’ eigenlijk enkel tussen berekening bevatten, kunnen we die gemakkelijk verbergen voor de gebruiker.
Excel tips 
Bart
november 3, 2010
Goed artikel, alleen even taalkundig naar punt 3 kijken bij de sheet maken