Elke week denken we eenmaal in een hokjesgeest en verdiept cm: zich in Excel. Deze week verdiept Excel-goeroe Tony de Jonker zich aan de wederzijdse afhankelijkheid van kostenplaatsen.

Volg ook het webinar

Power BI voor Financials in de praktijk (25 juni/2 juli)

Als twee of meer kostenplaatsen onderling prestaties uitwisselen, ontstaat het probleem van de wederzijdse afhankelijkheid van de kostentarieven van die prestaties. De totale kosten van kostenplaats A worden mede afhankelijk van de totale kostenplaats B en omgekeerd. De kosten van A kunnen pas bepaald worden als de kosten van B bekend zijn, maar ook het omgekeerde is waar. Deze ogenschijnlijke vicieuze cirkel kan alleen doorbroken worden door middel van matrixalgebra, waarin de afhankelijkheidsrelaties tussen de kostenplaatsen onderling in een stelsel van lineaire vergelijkingen simultaan in één calculatieproces worden opgelost.
De matrixalgebra is een zeer geschikt hulpmiddel ter oplossing van kostenallocatie bij wederzijdse leveringen. Excel is daarbij een belangrijk hulpmiddel bij het snel uitvoeren van relatief bewerkelijke operaties die normaliter met de matrixalgebra zijn verbonden. In deze bijdrage gaan deze methode illustreren aan de hand van een voorbeeld.

Opzetten van een tabel met onderlinge leveringen

Het is van belang om de uitgangstabel met onderlinge leveringen aan kostenplaatsen goed op te zetten. Ik adviseer de volgende lay-out:

In het voorbeeld hebben we drie kostenplaatsen, te weten A-100-Energie, A-200-Transport en A-300-ICT. Zowel in kolom A als in rij 1 staan de betreffende kostenplaatsen vermeld.

U dient de tabel als volgt in te vullen en te interpreteren:

• De positieve waarden in kolommen B tot en met C vertegenwoordigen de geleverde prestaties van de betrokken kostenplaats. A-100-Energie levert 800 eenheden prestaties (bijvoorbeeld KWh), A-200 levert 2000 eenheden (bijvoorbeeld aantal transporten) en A-300-ICT levert 300 eenheden (bijvoorbeeld manuren).
• De negatieve waarden in kolommen B tot en met C tonen de afgenomen prestaties. Zo neemt A-200-Transport 300 eenheden af van A-300-ICT.
• In kolom E worden de totale kosten per kostenplaats weergegeven. Dit is de verdeling voor de allocatie.
• In kolom F kunt u de procentuele aandelen berekenen. (F2=E2/E$5)

Opzetten hulptabel

U dient de volgende hulptabel op te zetten, waarin u met matrixalgebra de onbekende variabelen gaat oplossen.

De lay-out is identiek aan de eerste tabel. U gaat nu als volgt te werk:

• Markeer cellen B8 tot en met D10
• Geef de volgende functie in: =INVERSEMAT(B2:D4) en voer deze in door gelijktijdig te drukken op de toetsen Control-SHIFT-ENTER. U ziet accolades verschijnen rond de functie ten teken dat u een matrix-formule heeft ingevoerd. In de Engelse versie heet deze functie MINV.
• Markeer cellen E8 tot en met E10
• Geef de volgende functie in: =PRODUCTMAT(B8:D10;E2:E4) en voer deze in door gelijktijdig te drukken op de toetsen Control-SHIFT-ENTER. U ziet accolades verschijnen rond de functie ten teken dat u een matrix-formule heeft ingevoerd. In de Engelse versie heet deze functie MMULT.

De resultaten in de hulptabel zien er als volgt uit:

Berekenen kostenverdeling

U maakt een derde tabel met dezelfde lay-out waarin u de waarden kunt berekenen.

U gaat nu als volgt te werk:

• Markeer cellen B13 tot en met D15
• Voer de volgende formule in: =B2*$E8 en sluit deze af door gelijktijdig op de toetsen Control-Enter te drukken.
• Markeer cellen E13 tot en met E15
• Voer de volgende formule in: =SOM(B13:D13) en sluit deze af door gelijktijdig op de toetsen Control-Enter te drukken.
• Bereken de procentuele aandelen. Markeer cellen F13 – F15 en voer door middel van Control-Enter de volgende formule in: =E13/E$5

Vergelijken kostenverdelingen voor en na allocatie

Om de kostenverdelingen gemakkelijker te kunnen vergelijken zou u de volgende visualisatie kunnen maken:

U ziet dat na de herverdeling kostenplaats A-200-transport de grootse kosten vertegenwoordigt in plaats van A-300-ICT.

Qua visualisaties kunt u gebruikmaken van:

• Voorwaardelijke opmaak
• Cirkeldiagrammen

Voorwaardelijke opmaak

U gaat als volgt te werk:

• U kunt de cellen G1-G4 en G13-G15 markeren en een cel-referentie plaatsen naar de cellen in kolom F: =F2 gevolgd door Control-Enter.
• U heeft nog steeds de genoemde cellen gemarkeerd. Via het Lint selecteert u: Start à Stijlen à Voorwaardelijk Opmaak à Gegevensbalken à Effen opvulling à Oranje gegevensbalk.
• Met dezelfde cellen gemarkeerd kiest u uit het Lint: Start à Stijlen à Voorwaardelijk Opmaak à Regels beheren à Regel bewerken à aanvinken: Alleen balk weergeven.

Cirkeldiagrammen

U gaat als volgt te werk:

• Markeer cellen A2-A4 gevolgd door het ingedrukt houden van de Control toets en daarna markeert u cellen F2-F4.
• Uit het Lint kiest u : Invoegen à Grafieken à Cirkeldiagrammen à 2D Cirkeldiagram
• U kunt de grafiektitel verwijderen
• U kunt de legenda verwijderen, aangezien u deze wel laat zien in de tweede cirkeldiagram
• Klik op de elementen in het cirkeldiagram en klik op de rechtermuisknop. Uit het verkorte menu kiest u: Gegevensreeks opmaken à Opties voor reeks à Hoek van eerste segment à 270 graden.
  

• U kunt de achtergrond van het cirkeldiagram wit maken en de randlijn onzichtbaar laten.
• Tenslotte kunt u een verticale pijl plaatsen tussen de cijfers voor en de cijfers na kostenallocatie. Dit kunt u doen door via het Lint te kiezen: Invoegen à Illustraties à Vormen à Pijl omlaag.

Auteur: Tony de Jonker

Lees meer Excel tips en trucs van Tony de Jonker