Trechtergrafieken (in het Engels Funnel charts) worden vooral binnen sales-afdelingen gebruikt.
Dit om het verloop van potentiële klanten naar daadwerkelijke verkopen inzichtelijk te maken.
Maar ze zijn nog op meer plaatsen toepasbaar; in principe overal waar een volgende laag een deelverzameling van de vorige is.
Bijvoorbeeld dus ook bij website-bezoeken: de bovenste laag wordt gevormd door alle bezoekers, de tweede door alle bezoekers die meerdere pagina’s bekijken, de volgende geeft het aantal bezoekers weer, die de winkelwagen vullen en de onderste laag zijn die mensen, die daadwerkelijk wat bestellen.
Maar ook: een uitsplitsing van alle medewerkers (bovenste laag), die goede computerkennis bezitten (tweede laag), waarvan kennis van Excel belangrijk is (derde laag) en daarbinnen ervaring met draaitabellen hebben (vierde laag).
Vanaf Excel 2016 zijn trechter-grafieken ingebouwd; binnen de oudere versies moet je zelf creatief aan de slag.
Maar ook binnen de nieuwe Excel-versies kan het handig zijn om alternatieve benaderingen te kennen en te gebruiken.
Basis-gegevens
We nemen als voorbeeld voor dit artikel een sales-afdeling, die over drie jaar gegevens heeft verzameld (zie het tabblad Data in het Voorbeeldbestand).
Naast de aantallen per sales-stadium zijn ook de daarmee gemoeide omzet-bedragen vastgelegd.
Voor ieder jaar is het aantal leads (potentiële klanten) in kaart gebracht (in 2016 waren dat er 1000); door middel van een kwalificatieslag blijft daar een gedeelte van over (800 in 2016). Bij de beoordeling of die klanten een oplossing geboden kan worden, vallen er weer klanten af (300 in het voorbeeld). Aan geïnteresseerden wordt dan een voorstel gestuurd (400 in 2016), waarna bij een gedeelte een afronding kan plaats vinden (er blijven er 250 over; in 2016 dus een eindconversie van 25%).
Vanuit deze gegevens kan gemakkelijk informatie gedestilleerd worden over de totale conversie, maar ook over de conversie per deelstap.
Op grond van deze analyse zijn er mogelijk verbeterplannen te maken, maar de informatie kan ook gebruikt worden om ramingen voor de jaaromzet te maken.
Draaitabel
Op het tabblad Ovz van het Voorbeeldbestand is een overzicht gemaakt van deze gegevens door middel van een draaitabel, waarbij de resultaten van de verschillende jaren bij elkaar worden genomen.
Door bovenaan een bepaald jaar te kiezen kan de informatie wel ingeperkt worden.
Analyse
Op basis van de draaitabel kunnen we een analyse van de sales-activiteiten uitvoeren (zie het tabblad Analyse van het Voorbeeldbestand):
De aantallen leads (cel C8) wordt uit de draaitabel opgehaald via de formule
==DRAAITABEL.OPHALEN(“Som van Aantal”;Ovz!$B$4;”Stadium”;$B8)
De overige aantallen gaan op een vergelijkbare manier; de formule kan naar beneden gekopieerd worden.
Voor de bedragen geldt een vergelijkbare formule:
=DRAAITABEL.OPHALEN(“Som van Bedrag”;Ovz!$B$4;”Stadium”;$B8)
De resultaten van deze formules gebruiken we nu om een Trechter-grafiek te maken; dit doen we door in de cel daarnaast een evenredig aantal blokjes te zetten.
Maar … 3700 blokjes is wel wat veel.
En als we in de draaitabel maar één jaar kiezen, dan ziet de grafiek er direct heel anders uit.
We zullen de resultaten dus moeten schalen: als we maar 100 blokjes in de eerste rij willen, dan moeten we de aantallen dus nog delen door 37. In het tabblad Analyse wordt daartoe een correctiefactor berekend: =C8/D2
Een vergelijkbare factor is nodig voor de bedragen.
NB wil je in de eerste rij meer of minder blokjes hebben, dan kan cel D2 gewijzigd worden.
Laten we de grafieken maken:
- in cel D8 komt de formule =HERHALING(“|”;C8/$D$3)
- om het mooier te maken (blokjes ipv streepjes) geven we die cel een aparte opmaak: kies het lettertype Script, met tekengrootte 8 en maak de ’tekst’ vet
- kopieer deze formule naar beneden
- voor de bedragen komt een vergelijkbare formule in kolom H
Wordt er nu in de draaitabel een bepaald jaar geselecteerd, dan worden alle getallen in de analyse-sheet automatisch aangepast en uiteraard ook de grafieken.
Om dit te vergemakkelijken is een zogenaamde slicer toegevoegd.
NB in (veel) Excel-versies voor de MAC zijn slicers niet geïmplementeerd.
De grafieken geven een globaal inzicht in het verloop tussen de verschillende stadia. Maar we willen ook met de conversie-percentages kunnen rekenen. In de analyse-tabel zijn daar hulpkolommen voor opgenomen. In cel E9 staat de formule =C9/C8; het conversiepercentage door de kwalificatieslag. Voor de overige aantallen en ook voor de bedragen gelden vergelijkbare formules.
Door deze stap-percentages te vermenigvuldigen weet je ook hoe groot de kans is, dat de acties voor een klant, die in een bepaald stadium zit, met een goed resultaat zullen worden afgerond (zie kolommen F en J in het tabblad Analyse van het Voorbeeldbestand).
Ramingen
Op dit moment kent het onderhavige bedrijf een verdeling van de klanten naar stadia zoals weergeven in de kolommen Realisatie (zie tabblad Raming in het Voorbeeldbestand).
Door nu de analyse-resultaten te gebruiken kun je een raming maken van de omzet in de toekomst: de aantallen en bedragen worden vermenigvuldigd met de totale conversie-percentages van het betreffend stadium.
Uiteraard zijn de resultaten voor die ramingen afhankelijk van welke historie wordt meegenomen in de analyse (één specifiek jaar of alle jaren).
NB voor het gemak is de slicer naar dit tabblad gekopieerd.
Pasen
In veel software zijn zogenaamde paas-eieren verstopt. In Excel 2010 zit bijvoorbeeld ergens ‘ver weg’ een race-spel; in andere versies is het mogelijk om foto’s van de ontwikkelaars tevoorschijn te toveren. De ontwikkelaars hebben ooit een flight-simulator ingebouwd! Maar in de meer moderne versies van Excel heeft Microsoft al deze grapjes verwijderd. Heb je nog een ‘oude’ Excel-versie: google maar eens op excel easter egg.
Over Google gesproken; daar kunnen ze er ook wat van. Tik de volgende opdrachten in in de Google-zoekbalk:
- askew
- do a barrel roll
- anagram
- recursion
- zerg rush
NB bij de laatste zoekopdracht: schrik niet; het is een spelletje. Probeer de rondjes weg te klikken.