Excel kennis verspreiden - deel 09: Gegevenstabellen in Excel
- 25/05/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Ik ben er een tijdje tussenuit geweest en heb wat minder artikels gepost op de website, maar hier is nog eens een artikel dat al lang in de steigers stond. Het gaat verder op het onderwerp van de dienstencheques (zie het vorige artikel in de lessenreeks). In les 8 hebben we Stijlen besproken en les 7 was interessant op dit vlak aangezien het benoemde bereiken uit de doeken deed. In dit artikel richten we ons op het maken van simulaties in Excel: dit blijkt zeer eenvoudig te zijn met Gegevenstabellen! De simulaties gaan over de aanschaf van dienstencheques. Elke cheque (goed voor 1 uur werk) kost 9€ bruto, tenzij je meer dan 400 cheques per kalenderjaar koopt. De overheid geeft een belastingvoordeel van 30% zodat de netto kostprijs lager ligt dan de bruto kostprijs. De centrale vraag in dit artikel luidt als volgt: afhankelijk van het aantal cheques dat je koopt, wat kost je dit?
Grijp desnoods even terug naar het vorige artikel als je de berekeningen niet helemaal helder meer voor de geest hebt. Je kan ook het bijgaande Excel bestand bovenaan deze pagina downloaden. Op basis van een aantal inputcellen (handig aangeduid door middel van Stijlen), berekenen we de kostprijs, bruto en netto, per week, maand, jaar. Cel C9 ("INPUT_VraagUrenPerWeek") is belangrijk: hoeveel uur werk per week wens je te gebruiken in ruil voor cheques? Verander je die cel, dan zullen alle resultaten wijzigen, aangezien alles vraag-gestuurd is. Dan rijst de vraag: hoe veranderen kosten met de input van cel C9? Als je 5 uur werk neemt in plaats van 4 uur werk, wat is de extra kost (per week, maand, jaar)? Zowel bruto als netto. Hoeveel bespaar je netto op jaarbasis door iemand 3 uur in plaats van 4 uur per week te laten komen?
Gegevenstabel
Een gegevenstabel laat ons toe om te simuleren. Kijk bijvoorbeeld eens naar cellen I3:K11 in het bestand. Daar zien we bruto en netto kostprijs per week, als je 1, 2, 3, …, 8 uur werk per week neemt. Welke ingewikkelde formules heb ik daarvoor nodig gehad? Want uiteindelijk is elke cel in het bereik J4:K11 het resultaat van de berekeningen zoals je ze in kolom C ziet staan. Met andere woorden, voor elke cel in het bereik I4:I11 moeten we het héle model nalopen voor zowel bruto- als nettokosten, en dit op weekbasis. WACHT ! Excel kan dit zelf én automatisch met een Gegevenstabel! Excel simuleert de hele spreadsheet met wat er zou gebeuren als cel C9 ("INPUT_VraagUrenPerWeek") niet 4 zou zijn maar 1. Dan weer helemaal hetzelfde met C9 = 2. Vervolgens C9 = 3. Enzoverder, tot en met C9 = 8. De resultaten komen in de tabel J4:K11 terecht. Magie?! :-)
-
Hoe zetten we zoiets op?
- In cel I3 zet je de formule die je wil simuleren. Ofwel zet je een expliciete formule zoals je altijd zou doen in Excel, ofwel zet je (zoals ik) een formule: =OUTPUT_KostVoorKeuzes. Dan zeg je tegen Excel dat het benoemd bereik OUTPUT_KostVoorKeuzes (cel C29) de formule bevat die je wil simuleren in elke cel.
- Dan zet je in de cellen NET onder die cel I3 de waarden van één van de variabelen waarover je wil simuleren. Ik neem als voorbeeld het aantal uren met dienstencheques per week (komt overeen met de input in cel C9)
- Zet ook de waarden voor de tweede variabele waarover je wenst te sumuleren, onmiddellijk rechts van cel I3. Concreet: cellen J3, K3, …. Dit kunnen harde waarden zijn maar evengoed celverwijzingen of formules.
- We zijn er bijna. Selecteer de cellen I3:K11 en zoek in het lint naar de optie van de Gegevenstabellen. Het staat bij Gegevens (Data) en dan bij de "What if" analyses. Zie schermafdruk hieronder voor mijn Engelstalige versie van Excel 2013.
- Je krijgt een scherm te zien waarin je 2 cellen kan invullen. Voor de Row input cell selecteer je de cel of cellen rechts van I3 (hier dus J3:K3). Voor de Column input cell selecteer je de cel of cellen onder I3 (hier dus I4:I11)
- Als je het scherm verlaat en je hebt alles goed gedaan, zal Excel een matrixformule in het bereik zetten. Matrixformules worden gekenmerkt door de { } karakters rondom de formule. Kijk maar eens in de formulebalk. Individuele cellen kan je in het bereik met de matrixformule niet wijzigen: éénzelfde formule geldt voor alle cellen in het bereik.
- Je kan dan de resultaten opmaken maar het belangrijkste op dit moment is dat de formule juist is en alle gewenste simulaties doorrekent. Aangezien de simulatie via een formule afgehandeld wordt, kan je de inputs aanpassen en zie je direct het effect. Verander in cel I11 maar eens het getal 8 naar 12 bijvoorbeeld. Of verwijder de inhoud in cel K3: je zal een foutmelding krijgen, terecht uiteraard.
2 afbeeldingen volgen:
Bekijk op het gemak even de gegevenstabellen die ik voor jullie gemaakt heb in het bestandje. Telkens zijn het simulaties over 2 van de 3 variabelen:
- aantal uren per week gevraagd
- tijdsaspect (week, maand, jaar)
- kostconcept (bruto of netto)
Strikt genomen hoef je niet over 2 variabelen te simuleren, over 1 variabele is reeds voldoende. Zie de tabel in cellen I14:J22. De werkwijze is dan net iets anders: je zet de te simuleren formule in de kolom (of rij) als "ontbrekende" variabele. Voor de rest is werkwijze identiek aan hetgeen eerder beschreven werd. Aangezien je over 2 van de 3 variabelen uit het model niet wil simuleren, moet je die vastzetten. Dat doe ik (impliciet) in cel J14, die verwijst naar cel C17: ik zeg hiermee dat ik de nettokost per week wil simuleren, over het aantal uren werk per week.
Ziezo, super handige tool en maar weinig bekend onder Excel gebruikers. Hopelijk kon ik jou vandaag alweer iets bijleren. Pas de kennis gerust toe op andere vraagstukken van simulatie en Monte Carlo toepassingen.
