Budgetteren met Excel

Centraal in het budgetteren staat natuurlijk het bepalen van de variantie van het gerealiseerde ten opzichte van het gebudgetteerde en eventueel het geprognotiseerde. Er zijn in het budgetteringsproces dan ook drie fasen te onderscheiden:

• Het opstellen van het budget
• Het bewaken van het budget, waarbij de resultaten met het budget en eventueel het bijgestelde budget worden vergeleken
• Het analyseren van de resultaten

In het algemeen kan men stellen dat voor het budgetteren op zich niet de meest geavanceerde functies of formules gehanteerd behoeven te worden. Een goede presentatie is echter enorm belangrijk. Gelukkig biedt Excel u de mogelijkheid om een goede presentatie met intelligent rekenwerk te combineren. Ook bij het budgetrapportages kan men dan ook zeer fraai draaitabellen gebruiken.

Stel bijvoorbeeld dat we beschikken over de volgende budget data:  Het creëren van de draaitabel doen we natuurlijk op de welbekende wijze. We doorlopen de wizard door in het menu Data het item PivotTable and PivotChart Report te kiezen. We selecteren de gehele tabel en kiezen de standaard opties. De draaitabel verschijnt netjes op een aparte sheet. Als we vervolgens de maanden (months) als veld voor de rijen kiezen en bijvoorbeeld de ‘Actual Profit’ als data en daarbij de ‘Region’ als veld voor de kolommen, dan hebben we al snel een duidelijk overzicht van de omzet per regio per maand:  Vervolgens worden de data pas echt interessant wanneer we gebruik maken van de zogenaamde Custom Calculations van Excel.

Voor velden en voor items kunnen we extra berekeningen toevoegen. Wanneer slechts één data item in de draaitabel is ingevoegd, kunnen we het betreffende veld, in dit geval ‘Sum of Actual Profit’ te dubbelklikken, of door in het met PivotTable de optie Field Settings te selecteren. In het venster PivotTable Field kunnen we vervolgens de weergave van de datavelden aanpassen en eventueel impliciet berekeningen laten uitvoeren.

De opties worden echter nog interessanter wanneer we op de knop Options klikken en de extra opties zichtbaar worden. Onder Show Data As kunnen we formules op de datavelden toepassen die erg handig zijn. Zo kunnen we bijvoorbeeld kiezen voor de optie % of column. Wanneer we deze optie toepassen dan toont onze draaitabel niet meer de absolute waarden, maar het percentage in het betreffende veld ten opzicht van het totaal van die kolom. Voor ons in dit geval handig om te zien in welke maand relatief de meeste winst is gegenereerd.

De optie % of row is dan bijvoorbeeld interessant om te zien wat de relatieve bijdrage per regio was in de betreffende maand. De optie % of total geeft een aardige indruk van de relatieve bijdrage per maand per regio voor het gehele jaar. Voor rapportages is het bovendien erg handig dat Excel de gegevens voor u in een handomdraai ook in een grafiek plaats. Na het creëren van de draaitabel volgens uw wensen, maakt u eenvoudig een PivotChart aan door in het menu PivotTable de optie PivotChart te kiezen of door op het betreffende knopje te klikken.

Op een aparte worksheet worden dan de data overzichtelijk grafisch weergegeven. Als u de instellingen in de draaitabel wijzigt, wordt ook de PivotChart overeenkomstig aangepast. Binnen de PivotChart kunt u vervolgens ook weer op de bekende manier de instellingen zodanig veranderen. U kunt direct een staafdiagram of taartdiagram samenstellen en het uiterlijk van de grafiek volledig aan uw wensen aanpassen. Indien u van meerdere datavelden gebruik maakt, is het overigens handiger om specifieke velden aan te maken waarin berekeningen al opgeslagen liggen.

Dit kunt u doen voor velden of voor items (elementen binnen velden, North is het item bij het veld Region). Kies in het menu PivotTable voor de optie Formulas en vervolgens Calculated Field om zelf nieuwe velden aan te maken waarin de waarden van een berekening worden ingevoerd.  De velden die u op deze manier aanmaakt, kunt u vervolgens in uw draaitabellen en grafieken weer oproepen.

U kunt op deze manier bijvoorbeeld een veld aanmaken waarin de verhouding wordt aangegeven in kostenoverschrijding en tekortkomingen in de inkomsten. Al met al bieden draaitabellen voor het budgetteringsproces een hoop handige hulpmiddelen om snel inzicht te verkrijgen in de onderliggende data. In combinatie met de analysemogelijkheden van het Analysis Toolpack, beschikt u over voldoende middelen om budgetten samen te stellen, voortschrijdend te bewaken en achteraf de resultaten te analyseren. Verder willen we u graag de volgende raadgevingen meegegeven:

• Maak bij voorkeur gebruik van een vaste database of tabel waarin de budgetten, de forecast en de resultaten zijn opgeslagen. U hoeft uw analyse- en rapportagemiddelen dan niet telkens opnieuw samen te stellen. Als de data veranderd, zullen ook uw draaitabellen en grafieken veranderen.
• Maak gebruik van verschillende worksheets. Eén voor de voortschrijdende bewaking waarin de resultaten voor een maand worden afgezet tegen de forecast en het budget.
• Hanteer in de tabellen zo veel mogelijk platte data en relatief eenvoudige formules. Geavanceerd rekenwerk brengt u bijvoorkeur onder daarvoor aangemaakte velden binnen uw draaitabellen. Het voorkomt een hoop werk aan opmaak en controle van de formules in de tabellen.
• Maak gebruik van een conditionele opmaak (menu Format – Conditional Formatting) om bijvoorbeeld negatieve getallen meer attentiewaarde mee te geven.