Tag: Excel

Met je draadloze headset ben jij als oplosser van problemen de hele dag in gesprek met mensen. Detachering of werving & selectie, jij zorgt voor de juiste mensen op de juiste plaats. ’s Avonds zit je op je gemak nog even in Excel de administratie te doen. Doordat je alles strak elke dag bijwerkt, heb je een goed overzicht in de marges per consultant, maar wat zijn eigenlijk je marges voor de komende maanden?

Met behulp van het onderstaande voorbeeld laat ik je zien hoe je in een drietal eenvoudige stappen een prognose kan maken van je marges voor de komende maanden.

De basis op orde

De linkerkant, tot en met kolom K, bestaat uit basisgegevens.

Vanaf kolom L zijn de meeste kolommen berekend om uiteindelijk de gemiddelde marge per maand te berekenen in kolom R.

Het uurloon wordt berekend door het salaris te delen door het (maximaal) gemiddeld aantal werkuren per maand van 173,33 uur. Het tarief wordt berekend door het uurloon te vermenigvuldigen met de opslagfactor in kolom I.

In kolom Q hanteer ik een vast gemiddeld aantal werkuren van 150 in de maand om de gemiddelde marge per maand te bereken in kolom R (kolom P x kolom Q). Dit getal kan je natuurlijk zelf naar eigen inzicht aanpassen.

De formule in kolom N telt het aantal dagen, op basis van kolom K, bij de Datum in dienst om tot de Datum uit dienst te komen:
=IF((J11+(K11/8)*(7/5)/(Q11/173))=0;””;(J11+(K11/8)*(7/5)/(Q11/173)))
Eerst gaan we naar dagen door K11 door 8 uur te delen en dat vermenigvuldigen we met de “werkdagen factor” (Q11/173).

De kostprijs wordt berekend door het uurloon te vermenigvuldigen met een overheadsfactor van 1,8. De marge in kolom P is dan het verschil tussen het tarief in kolom M en de kostprijs in kolom O.

Voor het berekenen van de marge voor de werving en selectie activiteiten, wordt de wervingsfee in kolom H vermenigvuldigd met de FTE factor in kolom C. Bij  projecten is dat kolom P x kolom C.

Nu we op een eenduidige manier de marges hebben bepaald per consultant, bepalen we in de volgende stap de marges per maand.

Liever uitbesteden aan Expert Excel?

Met het team Excel specialisten sta ik voor je klaar!

Neem contact op

Marges per maand per consultant

Voor elke regel gaan we per maand bepalen of daar marge geteld moet worden.

Dat doen we met onderstaande formule voor bijvoorbeeld de maand februari (U11):
=IFERROR(IF(AND($J11<V$10;$N11>U$10);MIN(($N11-U$10)/(U$10-T$10)*$R11;(V$10-$J11)/(U$10-T$10)*$R11;$R11);0);0)

Valt de marge binnen de betreffende maand (is datum in dienst kleiner dan de volgende maand en is de datum uit dienst groter dan de betreffende maand), dan wordt er daarna bepaald voor hoeveel dagen de marge geteld moet worden. Dit doet de formule door te kijken of de gehele maand marge moet worden meegenomen, een deel van de marge op basis van de datum in dienst, of een deel van de marge op basis van de datum uit dienst.

Om het deel van de marge te bepalen, kijkt de formule wat de kleinste waarde oplevert:
=IFERROR(IF(AND($J11<V$10;$N11>U$10);MIN(($N11-U$10)/(U$10-T$10)*$R11;(V$10-$J11)/(U$10-T$10)*$R11;$R11);0);0)

1. Is het verschil tussen de de eerste van de betreffende maand (1 feb) en de datum uit dienst, vermenigvuldigd met het gemiddelde marge van die maand het kleinst? ($N11-U$10)/(U$10-T$10)*$R11

2. Is het verschil tussen de eerste van de opvolgende maand (1 mrt) en de datum in dienst, gedeel door totaal aantal dagen in de in de maand, vermenigvuldigd met het gemiddelde marge van die maand het kleinst? (V$10-$J11)/(U$10-T$10)*$R11

3. Of is het gehele gemiddelde marge bedrag van die maand het kleinst? $R11

Door te zoeken naar de kleinste waarde kom je ‘als vanzelf’ uit of je het deel van de maand vanaf het begin van het contract moet berekenen of juist dat deel van de maand tot en met het einde contract.

Zodra het verschil aan dagen in de eerste twee gevallen boven het totaal aantal dagen in de maand uitkomt, zal dit zorgen voor een hoger berekend gemiddeld marge bedrag en vallen die daardoor af.

Voor U11 kom je dan uit op de volgende waarden:
1. ($N11-U$10)/(U$10-T$10) = 350,3/31)
($N11-U$10)/(U$10-T$10)*$R11 = (350,3/31 * 2582)

2. (V$10-$J11)/(U$10-T$10) = 41/31)
(V$10-$J11)/(U$10-T$10)*$R11 = (41/31 * 2582).

3. $R11 = 2582

Zoekend naar de kleinste waarde vallen de eerste twee opties af in  de vergelijking voor U11.

Marges per maand getotaliseerd

Als laatste stap willen we alle categorieën, Consultants in dienst, Werving en Selectie, ZZP’ers en Projecten, per maand getotaliseerd hebben.

Alles bij elkaar optellen is een mogelijkheid, maar getotaliseerd per categorie biedt natuurlijk het meeste inzicht. Door middel van een SUMIFS formule, kunnen we verschillende criteria opgeven voor het optellen.

Door aan elke categorie een ‘verborgen’ kenmerk mee te geven in kolom S, kan je datzelfde kenmerk weer gebruiken in de formule. Bij de consultants In Dienst zet je dan in witte letters in kolom S ‘detachering’. Op dezelfde manier ‘ws’ voor werving en selectie, ‘zzp’ voor de ZZP categorie en ‘project’ voor Projecten.

De formule voor marge detachering (T45) is: =SUMIFS(T$11:T$42;$S$11:$S$42;”detachering”)
Ik gebruik standaard een SUMIFS formule in plaats van een SUMIF, ook als ik zoals nu maar één criterium gebruik. Mocht het in de toekomst nodig zijn om een criterium toe te voegen, dan staat de formule al in het juiste volgorde format (die afwijkt van de SUMIF). Voor de andere categorieën maak je op dezelfde manier ook de SUMIFS.

Nu zie je precies wat je per categorie aan marges kan verwachten de komende maanden. En op basis van dit overzicht kan je weer nieuwe plannen maken voor de toekomst.

Succes!