Debiteurenbeheer vanuit het financiële (ERP) systeem laat vaak te wensen over. Wat als er (nog) geen specifieke creditmanagementsoftware beschikbaar is? Kan Excel dan een oplossing bieden? Hoe kun je met dit bekende Office-programma leesbare openstaande postenlijsten, aanmaningen, ouderdomsanalyses en rapportages maken?
In dit artikel het vierde en laatste deel van een serie over hoe Excel kan worden ingezet voor de dagelijkse praktijk. Voor de dagelijkse activiteiten zijn we vaak aangewezen op de (on)mogelijkheden van ons financiële (ERP) systeem. En aangezien we het werken met een kladblok, stapel post-it briefjes en een agendering via bijvoorbeeld Outlook niet echt als een oplossing zien, zullen we andere mogelijkheden moeten zoeken. Bijvoorbeeld in Excel.
In de vorige drie artikelen hebben we gezien hoe we een download vanuit het bestand dat voor een printer bedoeld is, kunnen bewerken naar een voor Excel werkbaar bestand. Deze data hebben we verrijkt met de rapportagenaam, berekening van de ouderdom in dagen en een ouderdomscategorie.
Met gebruikmaking van al deze data hebben we een ouderdomsanalyse gemaakt. Verder hebben we gezien hoe je Excel kunt gebruiken om een actielijst, klachtenregistratie en aantekeningen bij te houden, hoe je een koppeling naar Word kunt leggen voor het maken van aanmaningen. En hoe je de data kunt bijwerken zonder dat al je aantekeningen verloren gaan. In dit artikel gaan we in op het maken van rapportages.
Rapportages
Rapportages zijn er in vele verschillende soorten. Voor rapportages op het gebied van Credit Management hanteren de meeste organisaties over het algemeen genomen minimaal de maand- en sales rapportages.
Sales rapportages
Sales rapportages richten zich over het algemeen op de behoefte van de account managers. Zij willen (als het goed is) inzicht hebben in het betaalgedrag van de klant(en). Betaalgedrag wat niet alleen bestaat uit de ouderdom van het nog openstaande saldo en het verloop ervan maar ook de termijn waarop de wel betaalde facturen zijn voldaan. Sales rapportages onderscheiden zich veelal in interne en externe rapportages. Op de externe rapportage zal vooral het gedrag van de specifieke klant naar voren komen.
Dit zijn overzichten die account managers kunnen gebruiken om met de klant te bespreken. Op de interne rapportage wordt dit gedrag ook zichtbaar gemaakt maar is het veelal aangevuld met zaken welke wel van belang zijn maar we niet aan de klant willen laten zien. Denk hierbij aan o.a. het aantal en saldo van de nog op te lossen klachten en kredietwaardigheid. Daarnaast bevatten de interne sales rapportages een overzicht van meerdere klanten. Afhankelijk van het aantal klanten per account manager zal het overzicht alle klanten bevatten of een selectie van de belangrijkste klanten.
Maand rapportages
Ook bij de maandrapportages dienen we een onderscheid tussen verschillende vormen te maken. Maandrapportages t.b.v. de eigen afdeling zullen meer detail informatie bevatten dan maandrapportages voor het management. Rapportages voor de eigen afdeling zullen zich, net als de sales rapportages, vooral richten op de huidige stand van zaken en het verloop ervan. Meestal aangevuld met stand van zaken t.o.v. de te behalen targets.
Maand rapportages voor het management zijn vaak in de vorm van Balance Scorecards. De voor het Credit Management gedefinieerde KPI’s worden opgenomen tussen de overige financiële KPI’s. Op dit soort rapportages zullen geen detail gegevens van de debiteuren zichtbaar zijn. De meest voorkomende KPI’s op een Balance Scorecard zullen dan ook bestaan uit het totale debiteurensaldo, eventueel uitsplitst naar het saldo buiten betaaltermijn (een hoog debiteuren saldo is immers niet erg als alles binnen betaaltermijn is), het saldo en percentage van de voorziening dubieuze debiteuren en de DSO.
Vanuit Excel gezien is het maken van een Balance Scorecard eenvoudig. Er zal eenmalig een frame ontwikkeld dienen te worden met daarop alle te rapporteren KPI’s. Het ophalen van de cijfers kan met bijvoorbeeld de formule VERT.ZOEKEN / VLOOKUP. Of er nu een openstaand saldo of het bedrag van de voorziening opgehaald moet worden, de werking, vanuit Excel gezien, blijft hetzelfde. De berekening van de waardes vindt namelijk niet plaats op de scorecard maar op onderliggende bestanden. De stand van de KPI’s kan ook gerapporteerd worden vanuit bijvoorbeeld een verkeerslicht. Zie figuur 1.
Verkeerslichten en de snelheidmeter kunnen beide met een grafiek gemaakt worden.
Figuur 1. Verkeerslichten en snelheidmeter als mogelijkheid voor KPI’s
De uitdaging zit voor ons dan ook niet in dit soort rapportages. De meeste tijdswinst valt te behalen bij het opstellen van rapportages die veel details bevatten en die ook nog eens regelmatig wijzigen of regelmatig opgesteld moeten worden. Zoals onder andere de maandrapportage. Of rapportages waarvan er vele gemaakt moeten worden. Bijvoorbeeld de sales rapportage per individuele klant. Als we die voor iedere klant handmatig moeten samenstellen zijn we behoorlijk wat tijd kwijt.
Cijfermatige rapportages
Hoewel iedere organisatie natuurlijk zelf bepaald wat de KPI’s zijn om op te rapporteren zijn er een aantal rapportageonderdelen die bij veel organisaties voorkomen. Één daarvan is de top 10 van openstaande bedragen. Zie figuur 2.
Nu kan de top 10 gemakkelijk uit de sortering van de ouderdomsanalyse gehaald worden. Deze werkwijze heeft echter als nadeel dat er veel knip en plak werk aan te pas komt. Een rapportage zou op ieder gewenst moment beschikbaar moeten zijn. Om een rapportage altijd up to date te hebben zijn er een aantal formules welke daarvoor gebruikt kunnen worden.
Met de functie GROOTSTE / LARGE kan bepaald worden welke klant het grootst openstaande saldo heeft. Of welk saldo bijvoorbeeld op de 3e plek staat in de top 10 van grootst openstaande bedragen. Op deze manier kunnen we eenvoudig een top 10 van de klanten met de grootste openstaande saldi maken.
En hebben we bij de volgende rapportages andere klanten in de top 10? Geen probleem. Doordat we met een formule werken zorgt Excel dat we altijd de juiste klanten op onze rapportage zien. Met behulp van de formule GROOTSTE / LARGE weten we nu wat de grootst openstaande bedragen zijn. Nu we dit weten kunnen we op zoek gaan naar de klant die daarbij hoort. Dit kunnen we doen door gebruik te maken van de formules INDEX / INDEX en VERGELIJKEN / MATCH. De ouderdom en het betaaltermijn kunnen we ophalen met de formule VERT.ZOEKEN / VLOOKUP.
Wanneer een klant meerdere vestigingen heeft welke een zelfstandig rechtspersoon zijn, zullen er ook meerdere kredietlimieten zijn. Omdat we rapporteren op de totale klant zullen we ook het totale limiet van deze klant willen weten. Dit betekend dat het kredietlimiet van alle onderliggende vestigingen getotaliseerd dient te worden. Dit kunnen we doen met de formule SOM.ALS / SOM.IF
Nu we alle onderdelen van de ‘Top 10 klanten’ hebben gevuld kunnen we op dezelfde manier een tweede frame maken met een overzicht van het verloop van het openstaande saldo over de maanden. We zouden dit met behulp van de VERT.ZOEKEN formule kunnen ophalen uit rapportages van voorgaande maanden. Wanneer voor deze methode gekozen wordt dient wel rekening gehouden te worden dat het maken van koppelingen naar vele bestanden veel geheugen kost. Het openen van de rapportage kan daardoor traag worden. De meeste organisaties rapporteren de voortgang 1x per maand. In het bovenste frame staat het saldo van de huidige maand. Wanneer we dit kopiëren en plakken als vaste waarden in het onderste frame hoeven we geen koppelingen te leggen.
In het frame waarin het saldo verloop zichtbaar is, kan ook een kolom worden toegevoegd met de omzet van de desbetreffende maand. Met behulp van die informatie kan de DSO berekend worden. Voor het berekenen van de DSO zijn er vele verschillende bereken wijzen. Dient er wel of geen rekening gehouden te worden met fluctuaties in de omzet vanwege seizoen invloeden? Dient het volledig openstaande saldo terug gerekend te worden naar de omzet uit de dezelfde periode? Of kunnen we misschien volstaan met alleen de omzet en het openstaande saldo van de desbetreffende maand? Ook hier zal iedere organisatie zelf haar keuze maken in de te hanteren berekeningsmethode. Belangrijk aandachtpunt hierbij is dat de berekeningswijze iedere maand gelijk dient te zijn zodat er vergelijkbare cijfers ontstaan.
In het voorbeeld van figuur 2 hebben we te maken met seizoeninvloeden en bereken we de DSO op basis van de omzet van de afgelopen 4 maanden. Met behulp van de formule SOM / SUM berekenen we de totale omzet van de afgelopen 4 maanden. Deze vermenig-vuldigen we met 3 om op een jaaromzet te komen. Deze jaaromzet delen we door 365 dagen. We hebben nu de gemiddelde dagomzet. Het openstaande saldo kunnen we nu delen door de gemiddelde dagomzet.
Grafische rapportages
Cijfertjes zijn natuurlijk leuk maar een grafische presentatie leest gemakkelijker en sneller. Van het verloop van het saldo kunnen we daarom een grafiek maken. Hiervoor gebruiken we de Wizard Grafieken / Chart Wizard.
Op deze zelfde wijze kunnen we uiteraard ook grafieken maken voor het verloop van het % current of voor het verloop van de DSO. Of andere KPI’s welke in de rapportage zijn opgenomen. Rapportages dienen de juiste hoeveelheid informatie te bevatten. Een rapportage vol zetten met cijfers of grafieken komt de leesbaarheid niet ten goede.
Voor veel organisaties is de DSO een belangrijke ‘thermometer’. Een KPI die zich daarom uitstekend leent te worden gerapporteerd in de vorm.. een thermometer. Deze kunnen we maken met de functie Voorwaardelijke opmaak / Conditional formatting. Door verschillende ranges op te nemen in de thermometer kunnen we in één oogopslag drie verschillende DSO onderdelen zien. De best mogelijke DSO voor als alle klanten conform de overeen gekomen conditie zouden betalen wordt groen weergegeven, de te behalen target maken we oranje en de actuele status rood.
Doordat onze rapportage is nu volledig is opgebouwd uit formules hoeven we nooit meer te ‘knippen en plakken’. Enige waar we voor moeten zorgen is dat onze openstaande postenlijst, waar onze rapportage de informatie ophaalt, actueel is. En hoe we dat kunnen doen hebben we kunnen lezen in het eerste en tweede deel van deze serie artikelen.
Figuur 2. Voorbeeld van een mogelijke rapportage
Tenslotte
In deze en eerdere artikelen hebben we gezien hoe we een bestand dat voor een printer bedoeld is, kunnen bewerken naar een voor Excel werkbaar bestand. En hoe we onze actielijsten en aantekeningen kunnen bijhouden. Verder hoe het koppelen van Excel aan Word voor het maken van aanmaningen werkt en hoe we de data actueel kunnen houden. Met dit artikel over hoe je kunt rapporteren met gebruikmaking van Excel is de cyclus rond.
Nu de meest voorkomende en belangrijkste processen zijn beschreven zijn we aan het einde gekomen van deze serie artikelen over het werken met Excel in de dagelijkse credit management praktijk. Ik hoop dat u er iets aan heeft gehad en wens u veel plezier in het optimaliseren van uw dagelijks noodzakelijke handelingen met gebruik van Excel.
Bron: De Credit Manager
