De evolutie van Excel en Power BI

Voor een financial professional is Excel een van de populairste hulpmiddelen voor analyse en rapportage. Met komst van big data en de veranderende rol van de financial professional rijst de vraag wat de rol van Excel daarbij is. We vragen Tony De Jonker wat zijn inzichten hieromtrent zijn. Als ervaren Financial Consultant, Excel-Power BI ontwikkelaar/trainer en tevens Microsoft Most Valuable Professional weet hij als geen ander wat de mogelijkheden zijn. We gaan hem een aantal vragen voorleggen die we vaak tegenkomen in de community.

Met de komst van de Business Intelligence tools zoals Qlik View en Power BI lijkt het erop dat de rol van Excel langzamerhand is uitgespeeld. Wat is uw visie hierop?

“Excel is de nummer 1 software voor financials wereldwijd. Hiermee kun je diverse soorten analyses maken die in de BI tools niet gemakkelijk te doen zijn zoals bijvoorbeeld:
• Beoordeling investeringsplannen
• Opzetten dynamische liquiditeitsprognose
• Knelpuntcalculaties
• Break-even analyse
• Balans- en resultatenrekening

Daarnaast heeft het moderne Excel de mogelijkheden om grote bestanden te importeren en te transformeren met Power Query en te modelleren met Power Pivot. Deze beide BI-tools zijn ook ingebouwd in Power BI. Excel is wat dat betreft de voorloper van Power BI. Eigenlijk zijn Excel en Power BI complementaire producten. Je kunt in Power BI geen gegevens invoeren of een standaardformulier maken om gegevens gestructureerd te verwerken. De verzamelde gegevens kunnen wel gebruikt worden al basis voor het maken van interactieve visuals in Power BI en deze in de Cloud te delen. Verder heeft Excel automatiseringsmogelijkheden via VBA om zaken te versnellen en te communiceren met andere Office-applicaties die met Power BI niet mogelijk zijn.”

Verschijnen er nog nieuwe features in het moderne Excel die van belang kunnen zijn voor de financial professional?

“Ondanks dat Excel al 35 jaar bestaat, zijn er onlangs nieuwe functies erbij gekomen in Excel Office 365 die voor de modellering in Excel een flinke sprong voorwaarts zijn. Het betreft de introductie van dynamische arrays oftewel dynamische matrixfuncties. Met deze functie kunt u eenvoudigere modellen bouwen, aangezien deze functies dynamisch van aard zijn en ze automatisch worden uitgespreid naar naastgelegen cellen op basis van ingegeven variabelen.

Afhankelijk van het aantal gekozen perioden wordt de output-tabel automatisch ingekrompen of uitgebreid. In vorige versies was hiervoor nog een manuele interventie nodig om het bereik van de formules aan te passen. U herkent de dynamische matrix-formules aan het blauwe kader.

Daarnaast zijn er nieuwe functies om eenvoudiger te kunnen zoeken, geografische gegevens door middel van datatypen uit te beelden en eigen functies aan te maken, waardoor lange formules kunnen worden vereenvoudigd.”

Kunt u een voorbeeld uit de praktijk geven waarin Excel een belangrijke rol speelt in combinatie met ander Office-producten en Power BI?

“Een bedrijf heeft te maken met het monitoren van projecten. Daartoe dienen de projectleiders maandelijks de geprognotiseerde opbrengsten en kosten per project in beeld te brengen. Ieder projectleider is verantwoordelijk voor de aan hem/haar toegewezen projecten. We hebben te maken met 150 projectleiders.

Om dit te vereenvoudigen dienen elke maand de werkelijke kosten van de afgelopen 12 maanden gedeeld te worden met de projectleiders. Daartoe wordt in Excel een uniform gebruikersvriendelijk invulformulier ontworpen welke als sjabloon wordt gebruikt voor ieder projectleider. Door middel van een batch VBA-macro worden 150 bestanden per projectleider aangemaakt met de daarbij behorende projecten. Met behulp van een ander batch VBA-programma worden maandelijks de projectenbestanden voorzien van de laatste actuals afkomstig uit het ERP programma.

Zodra de bestanden voorzien zijn van de laatste actuals wordt automatisch een e-mail verstuurd per Outlook aan de betreffende projectleiders ten teken dat ze verder kunnen met het invullen van de gegevens van de volgende prognoses.

Aan het einde van de maand dienen de projectleiders een verantwoordingsrapport in Word te schrijven waarin automatisch tevens de voortgangsgrafiek wordt afgebeeld. Door middel van een macro wordt de voortgangsgrafiek automatisch in het Word rapport ingeplakt.

Daarnaast dient elke maand een geconsolideerd rapport en dashboard te worden aangeleverd alsmede een pdf-rapport aan de bank. De geconsolideerde dataset wordt door middel een Power Query aangemaakt en via een VBA-macro omgezet naar een pdf-bestand, welke automatisch naar de Bank wordt gemaild.

De interactieve dashboards worden gemodelleerd in Power BI desktop en gepubliceerd in de Power BI service zodat ze met de belanghebbenden kunnen worden gedeeld. Elke projectleider kan alleen zijn eigen projecten raadplegen.

Schematisch ziet het proces er als volgt uit:

Zoals u kunt zien zal elke component haar bijdrage vervullen om een complete set te kunnen genereren. Hierbij speelt Excel een centrale rol in het verzamelen van de prognoses alsmede het automatisch aanmaken van Word-rapporten/pdf document en het versturen van e-mails via Outlook.

Na verloop van tijd is het denkbaar dat de Word/pdf rapporten verdwijnen en vervangen worden door relevante Power BI dataviews. Hiertoe is wel draagvlak nodig van de betrokken belanghebbenden wat de nodige omschakeling met zich meebrengt en tijd zal vergen maar technisch is alles mogelijk. De ervaring leert dat de meeste bedrijven via de lijnen der geleidelijkheid langzaam omschakelen door gebruik te maken van de ingebouwde Power BI tools in Excel (Power Query en Power Pivot) alvorens men volledig overstapt naar Power BI.

Dit gezegd hebbende zal het moderne Excel steeds een belangrijke rol spelen als intermediair voor het verzamelen van prognoses en speciale analyses alsmede het aanmaken van custom rapportages.”