"In het artikel Krijg grip op periodieke contractkosten en cashflow met Copilot las je hoe je met Copilot een dynamisch Excel-model opzet dat periodieke contractkosten automatisch over toekomstige maanden verdeelt. In dit artikel stel ik een model voor dat de impact op de resultatenrekening berekent en hoe Copilot daarbij van dienst kan zijn.
De naam van de Excel-tabel is: PeriodiekePosten.
In een apart Parameters-blad plaats je de volgende parameters:
Geef bereiknamen aan de gele cellen door de cellen B4-C5 te markeren en uit het menu te kiezen: Formules --> Gedefinieerde namen --> Maken o.b.v. selectie --> Links --> OK.
Vraagstelling aan Copilot
Geef vervolgens de volgende opdracht aan Copilot:
Maak een allocatieberekening in een apart tabblad met dien verstande dat de bedragen met een kwartaalfrequentie door 3 gedeeld moeten worden en aan de maanden moeten worden toegerekend, rekening houdend met start- en einddatum zoals vermeld in tabel PeriodiekeKosten. De bedragen van een jaarfrequentie moeten door 12 worden gedeeld en verdeeld worden over de maanden, rekening houden met start- en einddatum zoals vermeld in tabel PeriodiekeKosten. Gaarne de formules in het tabblad volledig omzetten naar dynamische matrixformules en de kolommen Frequentie en Bedrag na Categorie plaatsen. Tevens de Totaalkolom aan de rechterkant weglaten.
Het resultaat ziet er als volgt uit:
De verdeling van de kosten over de toekomstige maanden is correct berekend en de toevoeging van het Totaal onderaan de tabel ziet er keurig uit.
Copilot helpt ons ook met de verdeling van posten die halfjaarlijks verschijnen. Ook al hebben we deze frequentie niet expliciet in de instructie vermeld. Copilot toont de volgende toelichting:
Geproduceerde formules
Copilot produceert de volgende dynamische matrixformules:
Ophalen van de basisgegevens
A4=VERT.STAPELEN(PeriodiekePosten[Categorie]; "Totaal")
B4=VERT.STAPELEN(PeriodiekePosten[Frequentie]; "")
C4=VERT.STAPELEN(PeriodiekePosten[Bedrag]; "")
Met de functie VERT.STAPELEN worden de posten uit de inputtabel PeriodiekePosten gehaald. Daaronder wordt de tekst Totaal geplaatst onder Categorie en niets onder Frequentie en Bedrag.
Weergeven dynamische tijdlijn
D4=DATUM(StartJaar; StartMaand + REEKS(1; 12; 0; 1); 1)
De functie REEKS genereert een reeks van getallen bestaande uit 1 rij, 12 kolommen, startend bij 0 en 1 stap vooruit, die bij de maand wordt opgeteld. REEKS is een dynamische matrixfunctie die ervoor zorgt dat de DATUM functie ook dynamisch wordt. Zo worden de toekomstige datums steeds 1 maand naar rechts uitgebreid en beslaan uiteindelijk 12 maanden.
Berekenen van de allocatie
De formule in cel D4 voor de verdeling van de uitgaven over de toekomstige maanden is vrij lang en moeilijk te lezen. Geef Copilot daarom de volgende instructie:
Maak de formule in cel D4 wat leesbaarder door elk logisch onderdeel naar een volgende regel te brengen.
De formule in cel D4 is nu leesbaar opgemaakt met regeleindes. Als je nu op cel D4 klikt en in de formulebalk kijkt, zie je de formule netjes gestructureerd.
Elk logisch onderdeel (variabele definitie) staat nu op een aparte regel. Ditmaakt het veel makkelijker maakt om de formule te begrijpen en te onderhouden.
De bedragen per maand vormen de basis voor de bepaling van de maandelijks accruals oftewel transitorische posten.
Voordelen van deze aanpak
Deze aanpak heeft 4 voordelen:
- Volledig dynamisch: Voeg een nieuwe post toe aan PeriodiekePosten en de allocatie breidt automatisch mee uit.
- Minder onderhoud: Je gebruikt slechts 5 formules in plaats van meer dan 70.
- Spill-referenties: Met spill-verwijzingen zoals A4# passen totalen zich automatisch aan als het aantal rijen verandert.
- Leesbare logica: Met LET() en benoemde variabelen blijft de formule begrijpelijk. Hiermee kun je de formule opknippen, door variabelen te definiëren, die in de uiteindelijke berekening aan de eind van de formule worden gebruikt. In dit model hebben we te maken met 13 variabelen en 10 verschillende functies. De LET-functie fungeert daarmee als overzichtelijke kapstok voor meerdere geneste functies.
Copilot helpt hiermee om snel tot een flexibel en onderhoudsvriendelijk model te komen. Juist wie de mogelijkheden van Excel goed kent, kan met gerichte instructies ontzettend veel uit Copilot halen.
