Financiële analyse met Excel Power Pivot en Power BI

By Dries on 3/05/2016

image

In de meeste boekhoud -of andere pakketten kun je een aantal standaard rapporten opvragen of heb je ‘export naar excel’ mogelijkheden om rapporten verder te verfijnen in Excel. Voor basis rapporten met basis berekeningen heb je meestal voldoende met een aantal formules en lookups …  Maar wat als je die rapporten wil segmenteren over bijvoorbeeld klantengroepen, productgroepen of projecten ? Wat als je jaar over jaar of periode-vergelijkingen wilt maken en trends wil bepalen ? Wat als je actuele cijfers wilt afzetten tegen geplande cijfers of targets ? Wat als je … ? Dan zit je – als je er al in slaagt – met oeverloos complexe Excel werkboeken met tal van Excel formules waar niemand nog wijs uit raakt.  Excel PowerPivot en Power BI bieden hiervoor oplossingen.

Excel op steroïden !

Excel Power Pivot en Power BI (zelfde technologie in een ander jasje) bouwen verder op de kracht van Excel en Excel formules en bieden nog méér mogelijkheden om data te combineren, te analyseren en te visualiseren.  Excel Power Pivot en Power BI (die compatibel zijn met elkaar) werken met een data model met voorgedefiniëerde formules (berekende velden, meetpunten en KPI’s.)

Dynamische aggregatie

Bij de voorgedefiniëerde formules (geschreven in DAX: Data Analysis eXpressions) geldt het principe van dynamische aggregatie.  Het is namelijk zo dat – in tegenstelling tot klassieke excel-formules – de voorgedefiniëerde berekende velden, meetpunten en KPI’s altijd juist zullen berekend worden, wat ook de rapporterings-context is. Of je nu rapporteert over 2015 of 2016, over maanden of kwartalen, of je al dan niet je rapport wilt voor een bepaalde klantengroep/productgroep, …, de voorgedefiniëerde berekende velden, meetpunten en KPI’s zullen altijd de juiste cijfers weergeven volgens de filters die je gekozen hebt.

De formule voor bijvoorbeeld ‘% groei ten opzichte van vorige periode’ word 1 keer geschreven en zal dus steeds werken ongeacht of je het resultaat wil zien voor een jaar, een kwartaal of een maand.  Rapporteer je het jaar 2016, dan word de % groei ten opzichte van 2015 berekend.  Rapporteer je de maand 2016-mei, dan word de % groei ten opzichte van 2016-apr berekend, …

Hoe werkt het ?

Vooraleer we de data kunnen analyseren en visualiseren in rapporten en dashboards moet er een data model gemaakt worden met voorgedefiniëerde formules.  Deze stappen zijn te vergelijken met wat we vroeger in Excel deden: importeren (of knippen/plakken) van de nodige data in ons werkblad en het creëren van formules in de cellen.

Data model

Vooraleer vorm te geven aan een data model moeten we bepalen welke data we nodig hebben (de feiten of facts), waar we deze data vandaan kunnen/moeten halen en hoe we deze data willen zien (de analyse-assen of dimensions).

Voor financiële analyse rapportering zijn de behoeften bijvoorbeeld als volgt: de ruwe data waarop ik wil rapporteren is de historiek van de algemene rekeningen van het lopende boekjaar en de twee voorgaande afgesloten boekjaren.   Ik wil de cijfers kunnen zien per jaar, kwartaal en maand en ik wil de cijfers kunnen zien op rekening-niveau maar ook op een samengevat niveau zoals boekhoudklassen of een verkort schema’s voor resultatenrekening en balans.

Om mijn financiële analyse rapportering te kunnen doen heb ik 4 tabellen nodig in mijn data model en moet ik in mijn data model opgeven wat de onderlinge verhoudingen (relaties) zijn tussen die tabellen.  Om de tabellen in te lezen definiëer ik een connectie naar de data zodat ik de data (automatisch) kan vernieuwen op latere tijdstippen.  Op die manier zal ik steeds de nieuwe data kunnen bijladen in mijn data model.

de tabellen in het data model zijn:

Historiek algemene rekeningen

De historiek van de algemene rekeningen komt uit het boekhoud –of ERP pakket.  Bij de meeste van die paketten is het mogelijk om de data rechtstreeks te lezen uit de databank van het pakket zonder dat manuele tussenkomst nodig is om bijvoorbeeld eerst data te exporteren uit het pakket.

De gegevens die minstens nodig zijn uit de data zijn: de rekening, het bedrag en een datum (boekings –of document datum)

Datum

De datum tabel laat toe om cijfers te aggregeren/op te rollen naar maanden, kwartalen en jaren, en om ‘time intelligence’ functionaliteit (zie verder) te gebruiken.  Deze tabel bevat 1 lijn per datum en bevat de maand, het kwartaal, het boekjaar, … waarin deze datum valt.  Waar Excel standaard datums zal oprollen naar kalendermaanden en jaren, biedt de datum tabel ons de mogelijkheid datums op te rollen naar fiscale maanden en jaren (die mogelijks niet het kalenderjaar volgen).

De datum tabel word gekoppeld aan de ‘historiek algemene rekeningen’

Boekhoudplan

Het boekhoudplan (chart of accounts) komt ook uit het boekhoud –of ERP pakket.  Deze tabel bevat 1 lijn per rekeningnummer.  Bij het inlezen word er voor gezorgd dat we voor iedere rekening weten tot welke rekening groepen/klassen van het rekeningstelsel de rekening behoort.

De boekhoudplan tabel word gekoppeld aan de ‘historiek algemene rekeningen’

Rekeninggroepering

Deze tabel bevat de informatie over de ‘andere’ rekeninggroepering die nodig zijn om cijfers in bijvoorbeeld een verkot schema te kunnen tonen.

De rekeninggroepering tabel word gekoppeld aan de boekhoudplan tabel.

image

Nadat dit data model gecreëerd is kunnen we het gaan gebruiken om de historiek van de algemene rekeningen te tonen in de structuur van het rekeningstelsel

image

of in de gedefiniëerde verkorte schema structuren.

Voorgedefiniëerde formules

Financiële analyse gaat verder dan de ‘naakte’ cijfers in de rekeningen.  We moeten een aantal kerncijfers en financiële ratios kunnen rapporteren en we moeten vergelijkingen kunnen maken en evoluties kunnen zien.

Voor financiële analyse rapportering zijn de behoeften bijvoorbeeld als volgt: per periode, financiële kerncijfers en ratios kunnen rapporteren en zien of deze onder, op of boven de doelstelling liggen. de ‘naakte’ cijfers, kerncijfers en financiële ratios van een gerapporteerde periode afzetten tegen de cijfers van de vorige periode of van dezelfde periode vorig jaar.  Voor rapportering per kwartaal of maand YTD (year to date) cijfers kunnen rapporteren voor de ‘naakte’ cijfers, kerncijfers en financiële ratios.

Om deze doelstellingen te kunnen verwezenlijken moet ik eerst een tabel ‘meetpunten’ toevoegen aan het data model.  Deze tabel zal voor de kerncijfers en financiële ratios een aantal gegevens bevatten zoals bijvoorbeeld het doel (en de tolerantie ten opzichte van het doel) en of een hoger cijfer ‘beter’ of ‘slechter’ betekent.

En dan is er de definitie zelf van de formules.  Hieronder een aantal voorbeelden van types formules in deze oefening

Vorige periode / zelfde periode vorig jaar

De DAX formule taal kent een aantal zogenaamde ‘time intelligence’ functies.  Dit zijn een reeks zeer krachtige functies die toelaten om met eenvoudige formules analyses te doen op tijdsreeksen.  Met behulp van de datum tabel in het data model weet DAX welke datums er behoren bij de YTD (year to date), zelfde periode vorig jaar, vorige periode, …

Als je rapporteert over 2016-apr, dan weet DAX dat de datums die voor een YTD meegerekend moeten worden 01/01/2016 tot en met 30/04/2016 zijn, en dat dezelfde periode vorig jaar 2015-apr is, en dat de opening balance de cumul is tot en met 31/03/2016.

Vergelijking met vorige periode / zelfde periode vorig jaar

Als we het cijfer kennen van de andere periode, dan kunnen we gemakkelijk vergelijken door bijvoorbeeld een procentuele groei te berekenen of een status (slechter, gelijk, beter) rekening houdend met het type cijfer (opbrengsten die stijgen zijn beter, maar kosten die dalen zijn ook beter)

Status ten opzichte van een doel

In het data model hebben we voor kerncijfers en ratios de mogelijkheid voorzien om een doel op te geven.  Dit laat toe om de status van het resultaat te bepalen ten opzicht van het doel.  Op basis van die status kunnen we conditionele formattering toepassen (bv. groen = goed, oranje = bijna goed, rood=slecht).

image

Nadat de formules gedefiniëerd zijn, kunnen ze gebruikt worden in een Pivot table zoals hierboven: een P&L met een evolutie (beter, slechter of gelijk) tov dezelfde periode vorige jaar en met de procentuele groei tov van dezelfde periode vorig jaar.

image

Of een overzicht van de kerncijfers en ratios met hun doel en hun status

image

Maar ook in een Excel Power View dashboard die op een zéér visuele manier een overzicht toont van de belangrijkste kerncijfers en financiële ratios.

Heb je een Office 365 abonnement met Excel, dan kun je een Power Pivot werkboek ook gebruiken in Excel Online.  Zo heb je altijd en overal toegang tot je cijfers.

Visuele schittering

Excel is uitermate geschikt voor diepgaande data analyse en het ‘graven’ in je data maar soms heb je voldoende aan een aantal zeer visuele rapporten die je in een oogopslag een situatie voorschotelen.  Speciaal voor visuele analyse doeleinden bestaat er de tool Power BI.  Power BI is complementair aan en compatibel met Excel PowerPivot.

Power BI bestaat (net zoals het geval is met Excel Desktop en Excel Online) in 2 varianten: Power BI Deskop en de Power BI Service in de cloud.  Vanuit Power BI Desktop kun je publiceren naar de Power BI Service, maar de twee varianten kunnen ook volledig los van elkaar gebruikt worden.

In Power BI kun je (net zoals in Excel Power Pivot) een data model en voorgedefiniëerde formules maken of je kunt je Excel Power Pivot werkboek importeren in Power BI Desktop.  In de Power BI Service in de cloud is het zelfs mogelijk om je Excel Power Pivot werkboek te koppelen aan Power BI.  Wijzigingen die je doet in je Excel Power Pivot werkboek worden dan automatisch overgenomen in Power BI.

Power BI Desktop

In Power BI Desktop maak je op basis van het data model en de voorgedefiniëerde formules rapporten.  Deze rapporten zijn gelijkaardig aan Excel Power View rapporten maar Power BI biedt veel meer mogelijkheden en visuele elementen.

image

Een Power BI Desktop oplossing kan opgeslagen worden in een Power BI Desktop file maar je kunt een Power BI Desktop oplossing ook publiceren naar de Power BI Service in de cloud waar je nog over heel wat bijkomende mogelijkheden en functionaliteit beschikt.

Power BI Service

Met de Power BI Service in de cloud heb je alle mogelijkheden van Power BI Desktop en nog veel meer.  Met de Power BI Service maak je rapporten onder andere altijd en overal, op eender welk toestel toegankelijk voor jezelf, je collega’s, je klanten of de hele wereld.

Hieronder enkele van de mogelijkheden die de Power BI Service biedt.

Dashboards

Met de Power BI Service kun je de verschillende rapporten samenvatten op een dashboard.  Visuele elementen en indicatoren vanop de verschillende rapporten (die je meestal zult opmaken per thema: voor financiële analyse hebben we bijvoorbeeld een rapport P&L, een rapport Balans en een rapport kerncijfers en ratios) vastpinnen op een dashboard.

 image

Vanop het dashboard kan je navigeren naar de detailrapporten die verdere inzichten geven in het betreffende thema.

image

In de detailrapporten kun je jouw visuele analyse verderzetten door filters aan te passen (om bijvoorbeeld een ander boekjaar te visualiseren) of door te navigeren van jaren naar kwartalen naar maanden.

Delen met data beveiliging

De Power BI dashboards en/of rapporten kunnen gedeeld worden met andere personen.  Bij het delen is het ook mogelijk om te bepalen wie wat mag zien.  Je kunt bijvoorbeeld bepalen dat een bepaalde collega of klant enkel gegevens kan zien van zijn/haar dossiers of dat een medewerker op de boekhoudafdeling enkel het lopende boekjaar mag zien.

Delen van dashboards en/of rapporten kan ook op andere manieren door ze bijvoorbeeld te publiceren op een website of door ze te integreren in een applicatie.

Automatisch data vernieuwen

In de Power BI Service is het mogelijk om automatisch vernieuwen in te stellen.  Je kunt bijvoorbeeld instellen dat elke dag om 6.00h ‘s morgens de nieuwe data moet opgehaald worden uit de systemen waarnaar een data connectie ligt.  Dit kan zowel voor data bij jou op het lokale netwerk als voor data in de cloud.

Analyseren in Excel

Het is ook mogelijk het Power BI data model en voorgedefiniëerde formules te gaan analyseren in Excel net zoals het met een Excel Power Pivot werkboek gaat.

Mobiele Apps

Rapporten en dashboards kunnen altijd en overal geraadpleegd worden met de mobiele Power BI Apps voor Windows, IOS en Android.

image

Iets voor jou ?

Elk bedrijf, van groot naar klein heeft op een bepaald moment en tot op bepaalde hoogte behoefte aan financiële analyse en analyse van de boekhouding.  Bitwize is Power BI partner en bouwt al sinds het ontstaan oplossingen met Excel Power Pivot en Power BI (en Microsoft SSAS tabular voor enterprise).  We kunnen de financiële analyse rapporterings oplossing aanpassen en indien nodig uitbreiden op jouw maat.  Uiteraard kunnen we ook voor uw andere rapporterings vraagstukken een oplossing bouwen.

BI projecten
Business Intelligence
Power BI
Analyse
DAX
Excel
Financiële
Power BI
Power Pivot
Ratios
Author