Forecasts maken op basis van verwachte resultaten binnen diverse scenario’s is een belangrijke taak van controllers. Excel maakt het makkelijk om uitgebreide voorspellingen te genereren op basis van enkele invoercellen. Cm: behandelt de Wat-als-analysetool Gegevenstabel (Data Table).

Met verschillende van deze wat-als-analysetools is het mogelijk om snel en overzichtelijk inzicht te krijgen in omzetdoelen en prijsproposities. Onder Wat-als-analyse is een drietal tools beschikbaar: Doelzoeken (Goal Seek), Scenariobeheer (Scenario Manager) en Gegevenstabel (Data Table). De eerste feature is handig om snel heel simpele resultaten te zoeken: wat verandert er aan een bepaalde cel om een specifieke uitkomst te vinden? Oplosser (Solver) is te gebruiken als iets uitgebreidere versie die formules op meerdere cellen aanpast op inputs. Met Scenariobeheer is het mogelijk om meerdere situaties te genereren en snel te vergelijken.

De Gegevenstabel wordt in voorbeelden vaak gebruikt voor renteberekeningen, om snel inzichtelijk te maken hoe een veranderende ratio invloed heeft op betaaltermijnen of investeringsbedrag. Maar in principe is deze feature geschikt voor iedere vraag waarbij een of twee veranderende inputs andere resultaten geeft. Hier volgt een manier om een voorspelling te genereren, maar dit uitgebreide voorbeeld is vooral bedoeld om de feature toe te lichten. In een volgend artikel wordt ingegaan op een voorbeeld met ratio’s en formules, maar hier volgt een basis om op te bouwen. In dit voorbeeld gaat u op zoek naar een nieuwe prijspropositie om een specifiek omzetdoel te behalen.

Om deze feature toe te lichten, keren we terug naar het eerdere voorbeeld van de tekenmaterialen uit het artikel over Doelzoeken. Wat als u preciezer wilt bepalen hoe u op de gewenste omzet was uitkomen dan mogelijk was met de andere feature? Met Doelzoeken bepaalden we dat er 3698 potloden verkocht moesten worden om een omzettarget van 30.000 voor de winkel te realiseren. Met een normale target van 2000 potloden lijkt dat een forse opgave. Een andere oplossing is het verhogen van de prijs, wat resulteert tot mogelijk verlies van verkoop. Wat is de sweet spot tussen prijsverhoging en verkoopstijging om het omzettarget te halen?

Tekenwinkel G. Apeldee had de volgende tabel in het eerdere artikel over Doelzoeken:

De eenvoudige vraag daarbij was: hoe vergroot G. Apeldee zijn omzet naar 30.000 euro door een aanpassing te maken de omzet van potloden? Excel kwam op de proppen met het antwoord: 4622 euro voor de potloden die 1,25 euro per stuk kosten. Dat komt neer op een verhoging van de verkoop naar 3698 potloden, of door de huidige prijs bij het target van 2000 stuks te verhogen naar ongeveer 2,31 euro. Dat is echter een forse prijsstijging die ongetwijfeld tot een lagere verkoop zal leiden. Wat is een mooi midden tussen die twee uitersten?

Wat-als-analyse: Gegevenstabel

Voor dat antwoord kunt u zich keren tot Gegevenstabel onder Wat-als-analyse. Als eerste stap zijn de basisgegevens nodig die reeds zijn berekend: het aantal potloden, de prijs en een cel waar de twee worden vermenigvuldigd. Deze noteert u als eerste. Deze formule vormt tevens de invoercel voor de gegevenstabel straks (in dit voorbeeld =B1*B2 in B3).

Voor de tabel gebruikt u in dit voorbeeld twee variabelen: het aantal potloden en het prijspunt. Voor het eerste nemen we vijftien intervallen per 250 stuks vanaf 1500. Dat plaatst u in de kolom. Voor het tweede vijftien intervallen van tien cent, van een lager prijspunt op één euro tot en met bijna een prijsverdubbeling naar 2,40 euro. Deze prijzen gaan in de rij.

Tip: Door bijvoorbeeld 1500 en 1750 in te vullen en deze naar beneden te trekken, wordt het interval van 250 stuks (jnclusief de juiste getalopmaak) direct ingevoerd. Dat kan ook door de eerste twee prijzen te noteren en deze invoer naar rechts te trekken.

Ten slotte gebruikt u de originele invoer, de formule uit B3. Deze zet u in de linkerbovenhoek van de tabel. De tabel in aanbouw ziet er dan als volgt uit:

Selecteer deze hele tabel, inclusief de invoer (de gewenste omzet). In dit voorbeeld is dat een selectie van A5 t/m P20. Ga vervolgens naar Gegevens (Data) > Wat-als-analyse (What-If Analysis), in het lint boven Voorspelling (Forecast) en kies uit de drie opties Gegevenstabel (Data Table). Er verschijnt vervolgens een venster waar wordt gevraagd om de invoercellen:

In dit voorbeeld gaat het ten eerste om de prijsvoering in de eerste rij. Die staat genoteerd in invoercel B2 (€1,25), klik daarop onder Rij-invoercel (Row input cell). Ten tweede gaat het om de aantallen potloden die in de kolom staan genoteerd. De originele invoercel daarvan is B1 (3698). Klik op deze cel onder Kolom-invoercel (Column input cell). Klik op OK om de matrix te vullen.

Voorwaardelijke opmaak toepassen

Dat levert een simpel overzicht op van de omzet op elk prijspunt bij een x-aantal verkochte potloden. We zijn op zoek naar totalen van rond de 4600 euro om het doel van 30k te verwezenlijken. Met voorwaardelijke opmaak is het beoogde bereik te markeren. Selecteer de tabel, ga naar Start > Voorwaardelijke opmaak (Home > Conditional Formatting) en kies Nieuwe regel (New Rule). Neem bijvoorbeeld een bereik van 4200 naar 5000 euro. Kies voor 3-kleurenschaal bij Opmaakstijl, zet de types allemaal op getal en tik 4200 bij de laagste waarde en 5000 bij de hoogste. Zet de kleuren van zowel het minimum als het maximum op dezelfde kleur en het middelpunt (4600 euro) op iets anders om de middelpunten te onderscheiden. Klik op OK om de opmaak aan te passen:

De waardes die u wilt bereiken liggen nogal in het midden en zijn niet zo goed af te lezen. Het is daarom voor dit specifieke doel wellicht beter om een fijnmazigere tabel te maken, met kortere intervallen in zowel de aantallen als de prijspunten. Dat wordt een gigantische tabel die op deze pagina niet goed meer leesbaar is, maar hij is in een handomdraai gemaakt zoals hierboven toegelicht. Begin de tabel met een kolom met 1500 en 1550, en trek dit naar beneden voor een lange kolom met korte intervallen. Doe hetzelfde met de rij: ga van 1,00 euro, naar 1,05 en trek dit naar rechts om een lange rij aan te maken.

Herhaal hier het toepassen van de gegevenstabel met invoercel B2 als rij-invoer (de prijs) en invoercel B1 als kolom-invoer (het aantal). Dat levert een nieuwe tabel op, van zo’n 70 regels en 30 kolommen. Nogal een databerg. Met voorwaardelijke opmaak wordt dat al een stuk inzichtelijker. Aangezien 4600 euro de doelomzet was, nemen we nu een krapper bereik van 4550 euro naar 4650 euro bij en drie kleurenschaal (geel – groen – geel). Dat levert de volgende tabel op waarin de ideale prijs/aantal-verhoudingen in groen zijn weergegeven:

Volg CMWeb op LinkedIn!