Financiële rapportages zoals balans en resultatenrekening dienen inzicht te verschaffen in de huidige periode en ook vergeleken te kunnen worden met voorgaande perioden. Naast de cijfers van de vorige periode willen we ook de cumulatieve cijfers zien alsmede de cijfers van vorig jaar totaal en van de gekozen maand. Als we dit dynamisch in Excel willen modelleren met de normale hulpmiddelen dan heeft dat heel wat voeten in de aarde.
Met behulp van het Datamodel, Power Pivot, DAX-calculaties en draaitabellen kunt u dit aanzienlijk vereenvoudigen. In dit artikel laat ik u de mogelijkheden zien.
Brondata
In Excel beschikken we over een werkmap met twee bladen waarin de journaalposten (Ledger) wordt vastgelegd en het rekeningschema oftewel de Chart of Accounts (CoA). Beide bladen zijn als Excel-tabel opgemaakt via Invoegen => Tabel. Het Ledger-blad ziet er als volgt uit en bevat ettelijke duizenden mutaties over 2018 en 2019.
Het rekeningschema ziet er als volgt uit:
Werken met Power Pivot
U gaat naar het Ledger-blad en voegt deze tabel toe aan het datamodel door uit het Lint te kiezen => Power Pivot => Tabellen => Toevoegen aan gegevensmodel. Dit doet u ook voor het blad met het rekeningschema. U gaat nu naar Power Pivot via Power Pivot => Gegevensmodel => Beheren.
U ziet het volgende venster verschijnen:
U bent nu terecht gekomen in de werkomgeving van Power Pivot, van waaruit u de bestaande tabellen kunt verrijken of nieuwe tabellen toevoegen, relaties tussen de tabellen aanbrengen, DAX-berekeningen uitvoeren en tenslotte draaitabellen aanmaken. De tabbladnamen zijn gelijk aan de tabelnamen die u vanuit de voorkant van Excel aan de tabellen heeft toegekend.
Toevoegen datumtabel
Om met dynamische tijdvakken kunnen werken dient u een aparte datumtabel aan te maken waarin alle dagen van de bewuste transacties worden opgenomen. Dit kunt u automatisch laten aanmaken. Ga daartoe eerst naar het tabblad DataActuals. Uit het Power Pivot Lint kiest u: Ontwerpen => Agenda’s => Gegevenstabel => Nieuw. Het systeem maakt de volgende tabel aan:
Vervolgens hernoem ik:
• Het blad Agenda in Calendar
• Koptekst Jaar in YearNbr
• Koptekst Maandnummer in MonthNbr
• Koptekst Maand in Month
• Koptekst MMM-JJJJ in MonthYear
Aanbrengen Relaties
U kunt koppelingen aanbrengen tussen de drie tabellen. Kies uit het Lint: Start => Weergave => Diagramweergave.
Markeer het label GL Account in CoA en sleep hem naar Account in Ledger. Markeer Date in Calendar en sleep hem naar Date in Ledger. Het schema ziet er dan als volgt uit:
Schakel daarna terug naar de normale weergave via: Start => Weergave => Gegevensweergave.
Inbouwen metingen
Met behulp van DAX-functies (Data Analysis Expressions) kunt u berekeningen (metingen) maken om de voorgaande perioden in het model in te voeren. Ga daartoe naar het blad Ledger.
Kies een lege cel onderaan de tabel uit waarin u de eerste meting wilt invoeren. Hier voert u de volgende formule in voor cijfers van de gekozen maand:
Sum Actuals This Month:=CALCULATE (Ledger
[Sum Amount];
FILTER ( ALL ( Calendar ); Calendar[YearNbr] = MAX ( Calendar[YearNbr] ) );
FILTER ( ALL ( Calendar ); Calendar[MonthNbr] = MAX ( Calendar[MonthNbr] ) )
)
Op analoge wijze maakt u de volgende formules aan:
Actuals Last Month:=CALCULATE (
[Sum Amount];
FILTER ( ALL ( Calendar ); Calendar[YearNbr] = MAX ( Calendar[YearNbr] ) );
FILTER ( ALL ( Calendar ); Calendar[MonthNbr] = MAX ( Calendar[MonthNbr] ) – 1 )
)
Actuals Same Month Last Year:=CALCULATE (
[Sum Amount];
FILTER ( ALL ( Calendar ); Calendar[YearNbr] = MAX ( Calendar[YearNbr] ) – 1 );
FILTER ( ALL ( Calendar ); Calendar[MonthNbr] = MAX ( Calendar[MonthNbr] ) )
)
Actuals This Year:=CALCULATE (
[Sum Amount];
FILTER ( ALL ( Calendar ); Calendar[YearNbr] = MAX ( Calendar[YearNbr] ) )
)
Actuals Last Year:=CALCULATE (
[Sum Amount];
FILTER ( ALL ( Calendar ); Calendar[YearNbr] = MAX ( Calendar[YearNbr] ) – 1 )
)
De functie CALCULATE werkt op dezelfde wijze als de functie SOMMEN.ALS in Excel. Met de functie FILTER worden alleen die records in de calculatie betrokken die voldoen aan de gestelde voorwaarde(n). Met de functie ALL wordt geregeld dat eerst alle records worden getoond alvorens een nadere filtering plaatsvindt via de criteria. De functie MAX bepaalt de maximumwaarde van de bewuste kolom. De kolomnamen staan tussen vierkante haken en ze worden voorafgegaan door de naam van de tabel.
Aanmaken draaitabel
Uit het lint kiest u: Start => Draaitabel => Nieuw werkblad => OK. U kiest de volgende indeling:
Na enige opmaak-aanpassingen ziet de draaitabel er als volgt uit:
Let u erop dat de totalen van de saldibalans, zijnde BS (Balance Sheet) en PL (Profit & Loss) op nul uitkomen.
Zodra de bronnen worden aangepast, kunt u de draaitabel(len) eenvoudig verversen door het Lint te kiezen: Gegevens => Query’s en verbindingen => Alles vernieuwen.
Conclusie
Door een optimaal gebruik van het Datamodel en Power Pivot kunt u met een minimum aan formules dynamische draaitabellen maken met vergelijkingen door de tijd.
Auteur: Tony De Jonker
Dit artikel is verschenen in cm: 2019, afl. 3.
