Marge Berekenen Excel - Gratis Voorbeeld

1. Open het tabblad Producten en controleer productcode, productnaam, categorie, inkoopprijs, standaard verkoopprijs en BTW-tarief.
2. Ga naar Margeberekening en vul per verkoopregel het regelnummer, de verkoopdatum, verkoper, stad en productcode in.
3. Vul Aantal en Verkoopprijs excl. BTW in. De velden Productnaam, Categorie, Inkoopprijs excl. BTW en BTW-tarief worden automatisch opgehaald.
4. Controleer Omzet excl. BTW, Inkoopwaarde, Brutowinst, Brutowinstpercentage, Marge per stuk en Margeklasse.
5. Bekijk in Overzicht de totale omzet, inkoopwaarde, brutowinst, gemiddelde marge en het aantal regels met lage marge.
6. Gebruik de categorieanalyse en grafieken om prijsverschillen of zwakke productgroepen te onderzoeken.
7. Lees het tabblad Instructies bij uitbreiding en voeg nieuwe verkoopregels of producten toe binnen de voorbereide tabellen.

Een boekhouder, financieel medewerker of ondernemer gebruikt dit bestand om verkoopprijzen te vergelijken met directe productkosten. In een handelsbedrijf met laptops, monitoren en kantoorinrichting wil je niet alleen omzet zien, maar vooral weten welke verkopen de brutowinst dragen.

Figuur 1 toont het tabblad Margeberekening. De tabel loopt van kolom A tot en met Q: van Regelnummer en Verkoopdatum tot Productcode, Aantal, prijzen, Omzet excl. BTW, Inkoopwaarde, Brutowinst, Brutowinstpercentage, Marge per stuk en Margeklasse. De gele invoervelden zijn bedoeld voor handmatige gegevens; de berekende kolommen vullen zich automatisch.

Van verkoopregel naar brutowinst

Bij drie laptops van € 899,00 excl. BTW en een inkoopprijs van € 620,00 ontstaat € 2.697,00 omzet en € 1.860,00 inkoopwaarde. De brutowinst is dan € 837,00 en het brutowinstpercentage 31,0%. De marge per stuk bedraagt € 279,00.

De berekeningen blijven exclusief BTW. Dat is de juiste basis voor margeanalyse: BTW is geen opbrengst voor de onderneming. Het tarief van 21% blijft wel zichtbaar in de kolom BTW-tarief, zodat je kunt controleren welk tarief bij het product hoort.

Producten als centrale prijslijst

Figuur 2 laat Producten zien. Daar staan onder meer LAP-001 voor Zakelijke laptop, MON-002 voor 27 inch monitor en STO-003 voor Ergonomische bureaustoel. De productcode koppelt de verkoopregel aan categorie, inkoopprijs en BTW-tarief.

Voor een order van vijf monitoren tegen € 240,00 met € 180,00 inkoopwaarde per stuk is de omzet € 1.200,00 en de brutowinst € 300,00. Dat is 25,0%, dus de klasse Normaal.

Gebruik in de dagelijkse workflow

Een verkoper registreert de order, een planner controleert aantallen en een financieel medewerker beoordeelt uitzonderingen. Met maximaal 100 voorbereide regels kun je bijvoorbeeld een maandbestand met 80 verkoopregels analyseren zonder eerst een draaitabel te bouwen.

De marge wordt in dit bestand bewust exclusief BTW berekend. Bij het Nederlandse standaardtarief van 21% is een verkoopprijs van € 899,00 excl. BTW € 1.087,79 incl. BTW. Voor de brutomarge blijft echter € 899,00 de omzetbasis; de € 188,79 BTW hoort bij de omzetbelasting.

De wettelijke basis voor het algemene tarief is artikel 9 van de Wet OB 1968. Het bestand voert geen BTW-aangifte uit en berekent geen verschuldigde BTW, maar toont het tarief per product zodat je afwijkende productinstellingen kunt signaleren.

Welke formules het bestand gebruikt

De productgegevens worden opgehaald met VERT.ZOEKEN uit Producten, bereik A2:F101. De omzet is functioneel Aantal maal Verkoopprijs excl. BTW; de inkoopwaarde is Aantal maal Inkoopprijs excl. BTW. Daarna volgt Brutowinst als omzet minus inkoopwaarde.

Het brutowinstpercentage is Brutowinst gedeeld door omzet. De klassegrenzen zijn technisch hard vastgelegd: vanaf 0,40 is de uitkomst Hoog, vanaf 0,20 Normaal en onder 0,20 Laag. Voor een artikel met € 50,00 omzet en € 8,00 brutowinst is het percentage 16,0% en dus Laag.

Bereiken en gegevenskwaliteit

De tabel Margeberekening loopt van A1:Q101 en Producten van A1:F101. De kolommen met formules zijn gevuld voor rijen 2 tot en met 101. Voeg je producten toe, gebruik dan een productcode die exact overeenkomt; een extra spatie of afwijkend streepje verhindert een correcte opzoeking.

De invoer accepteert aantallen van 1 tot 100.000, verkoopprijzen vanaf € 0,00 en verkoopdatums van 01-01-2020 tot en met 31-12-2100. Die validaties beperken typefouten, maar controleren niet of een verkoopprijs commercieel haalbaar is.

In klantbestanden gaat het meestal mis bij de prijsbasis. Een gebruiker zet € 1.087,79 inclusief 21% BTW in de kolom Verkoopprijs excl. BTW. Bij drie laptops lijkt de omzet dan € 3.263,37, terwijl de werkelijke omzet exclusief BTW € 2.697,00 is. De marge wordt daardoor kunstmatig te hoog.

Verkeerde productcode of prijs

Een code als LAP001 in plaats van LAP-001 levert geen betrouwbare productkoppeling op. Controleer daarom de exacte schrijfwijze in Producten. Ook een verouderde inkoopprijs geeft een verkeerd resultaat: bij 20 monitoren zorgt een fout van € 15,00 per stuk al voor € 300,00 afwijking in de berekende brutowinst.

Aantallen en eenheden door elkaar

Een licentie kan per jaar worden verkocht, terwijl iemand in Aantal 12 invult omdat de klant maandelijks betaalt. Bij een verkoopprijs van € 180,00 per jaar wordt dan € 2.160,00 omzet berekend in plaats van € 180,00. Leg daarom vooraf vast of Aantal verkochte stuks, contracten of perioden betekent.

Een tweede fout is een nul- of leegwaarde in de verkoopprijs. Lege regels blijven grotendeels leeg, maar een ingevulde verkoopregel met € 0,00 geeft geen zinvolle margeanalyse. Filter in de tabel op verkoopdatum of productcode om onvolledige regels op te sporen.

Een percentage verkeerd interpreteren

Brutowinstpercentage en opslagpercentage zijn niet hetzelfde. Bij € 100,00 omzet en € 60,00 inkoopwaarde is de brutowinst € 40,00 en de brutomarge 40,0%; de opslag op inkoopwaarde is 66,7%. Gebruik voor beslissingen steeds de definitie uit het bestand: brutowinst gedeeld door omzet.

Let ten slotte op het gemiddelde in Overzicht. Het bestand gebruikt het gemiddelde van de regelpercentages; dat is niet hetzelfde als totale brutowinst gedeeld door totale omzet. Bij een kleine order van € 100,00 met 80% marge en een grote order van € 10.000,00 met 20% marge geven die methoden duidelijk verschillende uitkomsten.

Begin met de productreferentietabel. Vervang de voorbeeldproducten door je eigen assortiment en houd per code één actuele inkoopprijs, standaard verkoopprijs en BTW-tarief aan. Met 40 producten blijven de bestaande opzoekformules binnen het bereik A2:F101 werken.

Praktische uitbreidingen

• Gebruik een vaste productcode zoals SKU-001 in plaats van vrije productnamen, zodat spellingverschillen geen dubbele producten veroorzaken.
• Maak voor acties een aparte verkoopprijs per regel; de standaard verkoopprijs in Producten blijft dan een referentie.
• Voeg in de brongegevens alleen informatie toe die je voor margeanalyse nodig hebt. Gebruik geen BSN of andere overbodige persoonsgegevens.
• Controleer maandelijks of inkoopprijzen zijn gewijzigd. Een prijsstijging van € 10,00 bij 50 verkochte stuks verlaagt de brutowinst al met € 500,00.

Overzicht gebruiken voor besluitvorming

Figuur 3 toont Overzicht met KPI's voor totale omzet, totale inkoopwaarde, totale brutowinst, gemiddeld brutowinstpercentage, aantal verkoopregels en lage-margeregels. De categorieanalyse bevat omzet, brutowinst, gemiddeld margepercentage en verkochte eenheden voor vier categorieën.

De drie grafieken zijn al gekoppeld aan de voorbereide bereiken. Figuur 3 laat de categorieanalyse en de grafische samenvatting zien; de verkoopgrafiek gebruikt brutowinst per verkoopdatum. Bij 60 verkoopregels zie je daardoor snel welke dagen of productgroepen extra aandacht vragen.

Instructies en beheer

Figuur 4 toont het tabblad Instructies met uitleg over invoer, productreferenties, berekeningen, margeklassen, Overzicht, uitbreiden en privacy. Gebruik dit blad als werkinstructie voor collega’s die alleen verkoopregels hoeven in te voeren.

Voor een klein team is één eigenaar van de Producten-prijstabel de beste keuze. Zo voorkom je dat twee medewerkers dezelfde code met verschillende inkoopprijzen invullen en het Overzicht onverklaarbare verschillen laat zien.