Categorieën
Uncategorized

Meer inzicht in je maandelijkse marges met 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.

Michel van Expert Excel

Liever uitbesteden aan Expert Excel?

Met het team Excel specialisten sta ik voor je klaar!

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!

Categorieën
Uncategorized

Snel en foutloos factureren in Excel – Hoe doe je dat?

Facturatie is voor de meeste ZZP’ers een haat-liefde verhouding. Het is heerlijk om met je product een lach op het gezicht van je klant te toveren. Daar doe je het tenslotte voor. Aan het eind van de maand is het tijd om de facturen de deur uit te doen. De schoorsteen moet roken! Dat doe je wel even in een ochtendje. Maar vlak voor lunchtijd zit je pas op de helft en zie je langzaam ook de middag opgaan aan dit ‘administratief klusje’. Dat moet toch anders kunnen. Inderdaad, met Excel kan dat makkelijker én sneller.

Facturatievoorbeeld

Met behulp van het bovenstaande facturatievoorbeeld laat ik je zien hoe je met dropdowns en formules veel sneller klaar bent en bovendien de kans op fouten op je factuur aanzienlijk vermindert.

Michel van Expert Excel

Liever uitbesteden aan Expert Excel?

Met het team Excel specialisten sta ik voor je klaar!

Koppelen klantenbestand

Allereerst koppel ik het klantenbestand aan een keuzeveld. Met een dropdown menu kies je uit een voorgedefineerde selectie.

In dit geval de klantnamen uit het klantenbestand. Door de klantnamen te koppelen aan het dropdown menu sluipt er nooit meer een spelfout in. Voorwaarde is dan wel een eenmalige correcte invoer in het klantenbestand.

Het koppelen van het dropdown menu met het klantenbestand is heel eenvoudig. Staand op cel H7, klik onder Data op Gegevensvalidatie.

Dan selecteer je het bereik door rechts bij Bron op het pictogrammetje te klikken (zie hieronder). Ik klik dan op de betreffende KLANTDATA tab en selecteer B7 tot en met B100 te selecteren (opnieuw aan te passen als je klantenbestand groter wordt dan regel 100).

De beste zoekformule

Met de koppeling van het klantenbestand op orde zijn we klaar voor de volgende stap.

Op basis van de geselecteerde klantnaam haal ik met formules de rest van de klantgegevens erbij. Zoals de adresgegevens en het BTW percentage. Ik gebruik hiervoor de INDEX en MATCH formule.

Deze heeft twee grote voordelen in vergelijking met de populaire VLOOKUP formule:
1. INDEX MATCH is sneller, zeker als je zoekt in veel data.
2. INDEX MATCH is flexibeler. Bij het invoegen van een kolom in het zoekbereik, ben je verplicht om je VLOOKUP aan te passen. Je geeft namelijk een harde verwijzing uit welke kolom je een waarde terug wilt hebben. Hier heb je met INDEX MATCH geen last van.

Om de cel H8 (T.a.v.) op te zoeken gebruik ik: =(INDEX(KLANTDATA!D:D;MATCH($H$7;KLANTDATA!B:B))

De MATCH formule

Laten we het laatste stukje van die formule bekijken, de MATCH formule: =MATCH($H$7;KLANTDATA!B:B)
Als ik met de waarde van H7 (‘De Contentspecialist’) zoek in kolom B van het klantenbestand, dan vind ik een match in rij 9. De uitkomst van deze formule is 9.

De INDEX formule

Als ik vervolgens met die uitkomst, waarde 9, zoek in kolom D met de INDEX formule: =(INDEX(KLANTDATA!D:D;9)) dan vind ik de waarde Dhr. Krijgsman.

Om het variabel te houden, zet ik in de INDEX formule, op de plek van de waarde 9, de MATCH formule. Voor de volledigheid voeg ik nog een foutafhandeling toe. Mocht er een error ontstaan, dan geeft de formule een spatie terug: =IFERROR(INDEX(KLANTDATA!D:D;MATCH($H$7;KLANTDATA!B:B));””)

Om de flexibiliteit van de formule te testen, voeg ik in het klantenbestand een kolom toe vóór kolom D.

De formule verandert netjes automatisch mee!

Op dezelfde manier vul ik de rest van de velden. Om adres en huisnummer samen te voegen, gebruik ik twee INDEX MATCH formules achter elkaar en plaats ik &” “& ertussen.

Voor partno (artikelnummer) in cel X7, gebruik ik weer een dropdown menu dat gekoppeld is aan de tab met artikelen. Voor de resterende velden gebruik ik weer INDEX MATCH.

Factuurgegevens

Om de factuurgegevens te genereren, gebruik ik ook formules.

Factuurnummer: =YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Opgeknipt in stukjes.
Huidige jaar:
=YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Huidige maand, bij de eerste 9 maanden wil ik een nul voorafgaand:
=YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Uit het factuuroverzicht pak ik het hoogste nummer, alleen kijkend naar de laatste vier cijfers (kolom Q) en tel ik daar 1 bij op voor een uniek nummer:
=YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Voor de factuurdatum gebruik ik de datum van vandaag: =TODAY()
Voor de vervaldatum tel ik 30 dagen op bij de factuurdatum: =AN8+30

Als laatste stap ga ik de opgehaalde informatie op de factuur zetten. Dat doe ik door middel van celverwijzing. Bijvoorbeeld in cel C18 zet ik =H7.

De voetnoot bouw ik op met behulp van bovenstaande technieken.

Met een druk op de Visual Basic knop voeg regel toe zet ik een artikel op de factuur. Om een factuurregel in het factuuroverzicht te zetten, gebruik ik de Visual Basic knop maak factuur, pakbon. Een volgende keer vertel ik meer over Visual Basic.

Zonder Visual Basic selecteer je het factuurgebied en print je de factuur naar PDF. Klaar om digitaal te versturen.

Met het gebruik van dropdown menu’s en een paar simpele formules hoef je niets meer handmatig in te tikken door alleen broninformatie te gebruiken. Is dat administratief klusje af voordat je het weet en met betrouwbare broninformatie heb je gegarandeerd correcte facturen.

Succes!