Ik had al lang op mijn lijstje staan om iets te schrijven over Gegevens-tabellen in Excel. Een handig hulpmiddel bij scenario-analyses.
Een artikel op de site van Chandoo was aanleiding om daar daadwerkelijk invulling aan te geven!

Even verwarring wegnemen.
In Excel kennen we twee soorten tabellen: Tabellen en Gegevenstabellen. Helder? Vóór versie 2010 werden deze tabellen Lijsten, respectievelijk Tabellen genoemd.
Laten we de nieuwere terminologie maar hanteren: in Excel is een Tabel een verzameling bij elkaar horende cellen, die door de gebruiker worden gevuld (zie ook de  artikelen Kunst en Excel en Tabellen deel 2); een Gegevenstabel daarentegen wordt door Excel automatisch ingevuld.
Cryptisch? Lees hieronder verder.

Gegevenstabel met 1 variabele

Om de werking van een Gegevenstabel uit te kunnen leggen hebben we een voorbeeld nodig. In het Voorbeeldbestand heb ik een aangepaste versie van het Chandoo-voorbeeld gebruikt:

1. Sara wil een kopieer-bedrijfje beginnen en maakt een bedrijfsanalyse in Excel
2. Ze kan copiers leasen met een capaciteit van 100.000 kopieën per jaar voor een bedrag van € 5.000 per maand.
3. Een ruimte heeft ze ook al op het oog voor € 1.000 per maand.
4. De kostprijs van een kopie is 2 cent.
5. Marktonderzoek heeft uitgewezen, dat klanten 11 cent voor een kopie over hebben.
6. Hoeveel kopietjes moeten er dagelijks gemaakt worden om break-even te spelen; uitgaande van 50 werkweken van 5 dagen?

In het Voorbeeldbestand op het tabblad WA1 zijn deze aannames in het blok Input verwerkt.

In datzelfde tabblad staat ook een blok Berekeningen:

1. eerst het aantal kopieën: in cel G4 staat de formule =C8*C9
2. dan moeten we weten hoeveel copiers Sara daarvoor moet leasen: het aantal kopieën gedeeld door de capaciteit van een copier. Dit moet een heel getal zijn dus in cel G5 komt de formule =AFRONDEN.NAAR.BOVEN(G4/C5;0).
   De tweede parameter (0) geeft aan dat het resultaat geen decimalen mag hebben.
3. De formules voor de kosten, opbrengsten en de winst mogen verder geen verrassingen meer met zich mee brengen.
4. De 3 cellen in regel 16 hebben een voorwaardelijke opmaak gekregen, zodat direct duidelijk is of er sprake is van winst of verlies. 500 kopieën per dag zijn duidelijk niet voldoende om onder de gehanteerde aannames  winst te maken.

Door nu het aantal kopieën per dag in cel C9 te wijzigen kunnen we proberen het break-even-point voor dit bedrijfje te vinden.

En juist dit is het moment, dat een Gegevenstabel zijn kracht kan bewijzen:

1. plaats ergens in een lege kolom alle aantallen, die doorgerekend moeten worden (bv 100 t/m 1000)
2. één regel hoger in de kolom rechts daarnaast plaatsen we een verwijzing naar de cel met de winst/verliescijfers (of rechtstreeks de formule =G14-G11)
3. selecteer dan alle cellen met aantallen, inclusief de lege cel  daarboven en alle cellen daarnaast (in het voorbeeld hiernaast dus de cellen J7 tot en met K26.
   Tip selecteer cel J7 en druk op Ctrl-A
4. kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
5. we hebben een 1-dimensionale gegevenstabel: alleen in de kolom staan waardes die door Excel moeten worden doorgerekend. In het vervolgscherm vullen we dan ook alleen de kolominvoer in; we verwijzen naar de cel met het aantal kopieën (cel C9).
6. Klik OK en Excel vult alle cellen in kolom K met de winst, die bij het betreffende aantal hoort!

NB1 als je een waarde in kolom J wijzigt, zal ook het resultaat in kolom K worden aangepast

NB2 we hebben nu alleen een formule in kolom K gezet; in een Gegevenstabel met 1 variabele kunnen echter meerdere resultaten naast elkaar berekend worden.

NB3 Excel heeft een heel speciale matrix-formule in de sheet gezet (let op de {} rond de formules in kolom K). Een consequentie daarvan is, dat je niet zomaar meer cellen/regels kunt weghalen of toevoegen. Zorg dus bij het maken van een gegevenstabel dat je ruim voldoende invoercellen hebt. Moet je later iets toevoegen dan zul je de bestaande gegevenstabel moeten weghalen en een nieuwe tabel creëren.

Tot en met 1000 exemplaren per dag wordt dit bedrijf niet winstgevend. Tot en met 400 kopietjes neemt het verlies af maar daarna schiet die weer omhoog (er moet een 2e copier geleased worden). Tussen 400 en 800 loopt het verlies weer terug; na de hik bij 800 geldt hetzelfde verhaal.

Om het overzicht wat flexibeler te maken zijn in het Voorbeeldbestand 2 cellen opgenomen (K4 en K5) waarmee het startaantal en de stapgrootte aangepast kunnen worden.

Vullen we daar 500 resp. 100 in, dan krijgen we direct het overzicht zoals hiernaast.
Bij 1.200 kopietjes per dag speelt Sara quitte, maar daarboven gaat het weer mis (er moet nog een copier bij!). Pas rond 2.000 exemplaren begint er een stabiele winst te ontstaan.

Gegevenstabel met 2 variabelen

Maar het model sluit niet goed aan bij de realiteit: wanneer het aantal kopietjes per dag ineens groter wordt kan Sara natuurlijk niet (direct) over meer of nieuwe copiers beschikken; met de leverancier moeten van tevoren goede afspraken gemaakt worden. Voor het aantal gewenste copiers moet een inschatting gemaakt worden. Die raming nemen we in de input op.

In het tabblad WA2 van het Voorbeeldbestand is die wijziging doorgevoerd.
Ook is bij de berekening daar de formule voor het aantal kopieën aangepast; hier komt het minimum van (het aantal dagen * aantal kopieën) en (aantal copiers * capaciteit): =MIN(C9*C10;C6*C5)

Ons winst-model is nu afhankelijk van 2 variabelen: het aantal copiers en het aantal kopieën per dag.
Dus willen we een scenario-analyse doen dan zal onze gegevenstabel ook 2-dimensionaal moeten zijn:

1. plaats ergens in een lege kolom alle aantallen kopieën, die doorgerekend moeten worden (bv 100 t/m 1000)
2. één regel hoger in de kolommen rechts daarnaast plaatsen we de aantallen copiers, die we willen doorrekenen
3. in de cel links van de aantallen copiers (en dus net boven het aantal kopieën) komt weer een verwijzing naar de cel met de winst/verliescijfers
4. selecteer dan de cel met de formule en het hele blok cellen met links en boven de aantallen (in het voorbeeld hiernaast dus de cellen K8 tot en met R27.
5. kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
6. we hebben nu een 2-dimensionale gegevenstabel: in de rij staan de waardes voor het aantal copiers en in de kolom het aantal kopieën, waarvoor de resultaten door Excel moeten worden doorgerekend. In het vervolgscherm vullen we dan ook allebei de invoercellen in
7. Klik OK en Excel vult alle cellen in de gegevenstabel in.

Uit de tabel volgt dat het bedrijfje pas levensvatbaar gaat worden bij 4 kopiers en dat Sara moet proberen dan 1600 kopieën per dag te produceren.

Risicoanalyse mbv Gegevenstabel

Ambitieus als Sara is, gaat ze voor 5 copiers: “Het lukt me wel om gemiddeld 2000 kopietjes per dag aan de man te brengen! Mijn winst verdubbelt dan tov 4 copiers.”

Een vriend zegt haar, dat ze dan wel een risico-analyse moet uitvoeren. Exact 2000 kopieën per dag is niet reëel: “Ik denk dat het aantal normaal verdeeld is met een gemiddelde van 2000 en een standaard-deviatie van 100.”

In het tabblad WA3 van het Voorbeeldbestand is het model uitgebreid met een kans dat het betreffende aantal kopieën, op basis van het gemiddelde en SD , gehaald zal worden. Daarvoor gebruiken we in cel J11 de formule
=NORM.VERD.N(K11;$K$4;$K$5;ONWAAR)

Ofwel hoe groot is de kans dat er 1550 (cel K11) kopieën worden gemaakt onder de aanname dat de verdeling van het aantal Normaal Verdeeld is met een gemiddelde van 2000 (cel K4) en een standaard-deviatie van 100 (cel K5)? De echte kans, niet een cumulatieve (dus de laatste parameter is Onwaar).

Cel K10 bevat nu geen verwijzing naar de Winst maar naar de Winst * Kans.

Onder de gegevenstabel tellen we de, met de kansen,  gewogen winsten per kolom op. Die delen we door de som van de gebruikte kansen en krijgen dan de gemiddelde winst per kolom. De verwachte winst voor Sara wordt dan ongeveer 10% lager.

Multi-variabele Gegevenstabel

Sara heeft het idee, dat ook de huurprijs nog bespreekbaar is en wil in de analyse hier rekening mee houden. Het model krijgt dan een derde input-variabele. Maar de Excel-gegevenstabel kan maar 2 dimensies aan.

Geen nood:

1. zorg dat alle combinaties van de variabelen (dat kunnen er dus ook 10 zijn!), die doorgerekend moeten worden in een scenario-tabel zijn opgenomen
2. wijzig de input-tabel zodanig dat de input-variabelen afhankelijk zijn van het scenarionummer. Dat kan met Verticaal Zoeken in de scenariotabel (in het voorbeeld op het tabblad WA4 is dat B25:F37).
3. maak een 1-dimensionale Gegevenstabel, waarbij in de eerste kolom de scenarionummers staan. In de kolomkoppen daarnaast maken we dan verwijzingen naar de scenario-tabel (zie hieronder de formule in cel K5) en naar de bijbehorende winst.
4. het is niet fraai, dat het resultaat van die verwijzingen altijd zichtbaar is: wijzig de opmaak van die kopjes zodanig, dat de tekstkleur gelijk is aan de achtergrondkleur.

---