Wie man Verkaufsprovisionen in einer Tabellenkalkulation berechnet (gestaffelte Sätze und Splits)
Die Vertriebsprovision in einer Tabellenkalkulation richtig zu berechnen, hängt von vier Entscheidungen ab. Sind Ihre Staffeln progressiv oder flach, und wie wird der Provisionssatz ermittelt? Wie wird ein geteilter Deal aufgeteilt, und wo landen Clawbacks? Wenn Sie die erste Entscheidung falsch treffen, ist jede darauffolgende Zahl falsch.
Die Arithmetik ist nicht schwer. Schwierig wird es dadurch, dass die Regeln in einem Provisionsplan stehen, der von jemand anderem verfasst wurde. Die Tabellenkalkulation muss diese Regeln dann so abbilden, dass ein Kollege sie prüfen kann.
Dieser Leitfaden erklärt, warum Tabellenkalkulationen daran scheitern, welche drei Ansätze üblich sind und an welchen Stellen das Modell bei Planänderungen versagt. Es handelt sich um einen Daten-Workflow, nicht um eine Lohnabrechnungs- oder Rechtsberatung. Stimmen Sie das Ergebnis daher mit den Verantwortlichen des Provisionsplans ab.
Warum Vertriebsprovisionen Tabellenkalkulationen an ihre Grenzen bringen
Das erste Problem ist, dass „gestaffelt“ zwei verschiedene Dinge bedeuten kann, und in den Plandokumenten wird selten spezifiziert, welches gemeint ist.
Bei einem Plan mit flachen Staffeln wird bei Erreichen einer Stufe der Provisionssatz dieser Stufe auf den gesamten Betrag angewendet. Bei einem Plan mit progressiven Staffeln wird jeder Teilbetrag mit dem Satz der Stufe verzinst, in die er fällt – ähnlich wie bei den Einkommensteuerklassen. Bei einem Buchungsvolumen von 120.000 $ über Staffeln von 5 %, 7 % und 9 % unterscheiden sich diese beiden Auslegungen um Tausende von Dollar.
Das zweite Problem ist, dass ein Deal nicht lange eine einzige Zeile bleibt. Ein geteilter Deal wird zu zwei Zeilen, ein Accelerator ändert den Provisionssatz mitten im Abrechnungszeitraum, eine Rückerstattung macht einen Teil einer Zahlung rückgängig und ein Cap begrenzt den Gesamtbetrag.
Das dritte Problem ist die Prüfbarkeit. Provisionen müssen für die Person, die sie erhält, nachvollziehbar sein. Eine einzelne Zelle mit sechs verschachtelten WENN-Formeln ist nicht nachvollziehbar – und genau in diesem Format liegen die meisten dieser Modelle vor.
Rundungsfehler summieren sich unbemerkt. Wenn bei jedem Zwischenschritt gerundet wird und nicht erst einmalig bei der Auszahlung, führt dies zu Abweichungen, die mit der Anzahl der Zeilen wachsen und sich nie mit der Lohnbuchhaltung decken.
Was Sie das kostet
Streitigkeiten, die Sie nicht schnell beilegen können. Wenn ein Vertriebsmitarbeiter eine Zahl anzweifelt, müssen Sie den Weg vom Deal bis zur Auszahlung aufzeigen können. Eine verschachtelte Formel lässt sich nicht einfach vorlesen, also läuft das Gespräch darauf hinaus, das Modell neu aufzubauen.
Eine Vertriebsprovision, die sich nicht erklären lässt, ist eine Zahl, die im nächsten Quartal garantiert wieder angezweifelt wird.
Ein Neuaufbau in jedem Planjahr. Sätze, Staffeln und Acceleratoren ändern sich jährlich und manchmal sogar pro Mitarbeiter. Ein Modell, bei dem die Sätze fest in den Formeln verankert sind, muss jedes Mal neu geschrieben statt nur neu konfiguriert werden.
Mühsame Abstimmung. Die Lohnbuchhaltung arbeitet centgenau. Ein Modell mit Rundungen bei den Zwischenschritten führt bei Hunderten von Zeilen zu minimalen Abweichungen. Die Ursache dafür zu finden, dauert oft länger als die eigentliche Erstellung des Modells.
Die Workarounds, die versucht werden
Option 1: Provisionssätze aus den Formeln auslagern
Tragen Sie Staffeln und Sätze in eine kleine Tabelle ein und schlagen Sie den Satz nach, anstatt ihn fest zu codieren. VLOOKUP mit dem Parameter Bereich_verweis auf WAHR (TRUE) findet die passende Staffel für einen Wert, vorausgesetzt, die Tabelle ist aufsteigend sortiert.
XLOOKUP macht dasselbe mit einem expliziten Vergleichsmodus für „genaue Übereinstimmung oder nächstkleineres Element“, was auch sechs Monate später noch leicht zu verstehen ist. Wenn die Logik tatsächlich nur aus einer kurzen Kette von Bedingungen besteht, ist IFS in puncto Lesbarkeit verschachtelten WENN-Formeln deutlich überlegen.
Dies ist die wertvollste Änderung überhaupt, da der Plan für das nächste Jahr so zu einer einfachen Tabellenbearbeitung statt zu einer Formelumschreibung wird. Damit lassen sich flache Staffeln vollständig lösen, progressive Staffeln jedoch überhaupt nicht.
Option 2: Progressive Staffeln richtig berechnen
Bei einem progressiven Plan entspricht die Provision der Summe über alle Staffeln hinweg, berechnet aus dem Betrag, der in die jeweilige Staffel fällt, multipliziert mit dem Satz dieser Staffel. Eine Hilfstabelle mit einer Zeile pro Staffel, die den entsprechenden Anteil des Deals anzeigt, macht dies transparent und überprüfbar.
Wenn Sie das Ergebnis in einer einzigen Zelle haben möchten, liefert SUMPRODUCT über die Staffelgrenzen und die Differenzen zwischen aufeinanderfolgenden Sätzen dasselbe Ergebnis. Für welche Variante Sie sich auch entscheiden: Behalten Sie die Hilfstabelle im Hintergrund, denn genau diese werden Sie einem Mitarbeiter zeigen, der mit der Berechnung nicht einverstanden ist.
Wenden Sie ROUND nur einmal an – und zwar auf den Auszahlungsbetrag, niemals zwischendurch. Die Grenze dieses Ansatzes liegt im Wartungsaufwand: Jede Änderung einer Staffel betrifft sowohl die Hilfsstruktur als auch die Tabelle mit den Provisionssätzen.
Option 3: Splits, Caps und Clawbacks als separate Buchungszeilen behandeln
Widerstehen Sie dem Drang, die ursprüngliche Deal-Zeile anzupassen. Erfassen Sie stattdessen jedes Ereignis als eigene Zeile mit einem Typ: ursprüngliche Gutschrift, Split-Zuweisung, Accelerator-Anpassung, Cap-Reduzierung, Clawback.
Splits werden dann zu zwei Zuweisungszeilen, deren Prozentsätze sich auf 100 % summieren müssen. Eine Überprüfung dieser Summe deckt den häufigsten Fehler auf. Eine Rückerstattung wird zu einer negativen Zeile mit dem Datum des Zeitraums, in dem sie aufgetreten ist, wodurch die Abrechnungen der Vorperioden unverändert bleiben.
Dies führt zu einem Modell, das Zeile für Zeile überprüft werden kann – und genau darum geht es. Es führt allerdings auch zu viermal so vielen Zeilen und erfordert eine Disziplin, an die sich jeder halten muss, der mit der Datei arbeitet. Unser Leitfaden zur Umwandlung eines CRM-Exports in einen Pipeline-Bericht beschreibt die Aufbereitung der Deal-Daten, auf denen dies aufbaut.
Die gemeinsame Obergrenze. Alle drei Ansätze setzen voraus, dass der Plan für den Zeitraum stabil bleibt. In der Praxis treffen unterjährige Änderungen, einmalige Garantien und Ausnahmen pro Mitarbeiter per E-Mail ein – und jede davon ist eine manuelle Änderung, die niemand dokumentiert.
So berechnen Sie Vertriebsprovisionen mit Powerdrill Bloom
Step 1: Upload your deal data and rate table
Laden Sie den Export der abgeschlossenen Deals und die Provisionssatztabelle des Plans zusammen hoch. Powerdrill Bloom analysiert beide, sodass fehlende Verantwortliche, leere Beträge und Split-Prozentsätze, die sich nicht auf 100 % summieren, auffallen, noch bevor eine Zahlung berechnet wird.
Step 2: Describe the plan rules in natural language
Formulieren Sie den Plan, anstatt ihn mühsam aufzubauen. Geben Sie an, dass die Staffeln progressiv sind, nennen Sie die Stufen und Sätze und definieren Sie den Accelerator-Schwellenwert sowie eventuelle Caps.
Fordern Sie im selben Schritt die Überprüfungen an. Fragen Sie, bei welchen Deals die Splits sich nicht auf 100 % summieren und welche Mitarbeiter den Accelerator-Schwellenwert mitten im Abrechnungszeitraum überschritten haben. Fragen Sie dann, welche Rückerstattungen in einen anderen Zeitraum fallen als der ursprüngliche Deal.
Step 3: Export the chart, report, or deck
Exportieren Sie eine Abrechnung pro Mitarbeiter, die den Weg vom Deal bis zur Auszahlung zeigt, ein Diagramm zur Zielerreichung im Vergleich zur Quote oder eine Zusammenfassung für die Finanzabteilung.
Warum dies besser ist, als das Modell jedes Quartal neu aufzubauen
| Manuelle Methode | Powerdrill Bloom | |
|---|---|---|
| Neue Sätze im Planjahr | Tabellen bearbeiten, dann Formeln erneut überprüfen | Neue Staffeln und Sätze einfach angeben |
| Progressive versus flache Staffeln | Hilfsstruktur neu aufbauen | Angeben, welche Variante der Plan nutzt |
| Split-Prozentsätze, die sich nicht summieren | Manuelle Prüfspalte | Fragen, welche Deals die Prüfung nicht bestehen |
| Einem Mitarbeiter eine Zahl erklären | Den Formelpfad rekonstruieren | Nach der Aufschlüsselung vom Deal bis zur Auszahlung fragen |
Die letzte Zeile ist diejenige, die echte Zeit spart. Der größte Aufwand bei der Provisionsabrechnung liegt nicht in der Berechnung, sondern in der Erklärung – und genau diese Erklärung wird durch eine verschachtelte Formel unmöglich gemacht.
Häufige Fehler
Anwendung eines einzigen Satzes auf den Gesamtbetrag bei einem progressiven Plan. Dies ist der teuerste Fehler in dieser Kategorie, und er führt fast immer dazu, dass die Top-Performer entweder deutlich zu viel oder zu wenig ausgezahlt bekommen.
Festcodierung von Sätzen in Formeln. Das funktioniert für ein Jahr, macht aber die Planänderung im nächsten Jahr zu einem kompletten Neuaufbau. Halten Sie die Sätze in einer Tabelle fest, die Sie auch der Finanzabteilung übergeben können.
Runden bei jedem Schritt. Runden Sie nur einmal, und zwar bei der Auszahlung. Runden bei Zwischenschritten führt zu Abweichungen, die sich nicht mit der Lohnbuchhaltung in Einklang bringen lassen.
Bearbeiten der ursprünglichen Zeile bei einer Rückerstattung. Dies macht frühere Abrechnungen ungültig, die bereits freigegeben wurden. Fügen Sie stattdessen eine negative Zeile mit dem Datum des Zeitraums hinzu, in dem die Rückerstattung erfolgt ist.
Vergessen, dass sich Split-Prozentsätze auf 100 % summieren müssen. Zwei Zuweisungen von jeweils 60 % zahlen 120 % der Provision aus und sehen in der Tabelle völlig normal aus.
Planregeln nur in E-Mails aufzubewahren. Ein Provisionsmodell, dessen Regeln in einem E-Mail-Verlauf verstreut sind, lässt sich weder prüfen noch an andere übergeben. Schreiben Sie sie direkt in die Arbeitsmappe.
Mischen von Zeitraumsdefinitionen. Das Abschlussdatum des Deals, das Rechnungsdatum und das Datum des Zahlungseingangs führen zu drei unterschiedlichen Ergebnissen. Wählen Sie eine Definition, halten Sie sie schriftlich fest und wenden Sie sie auf jede Zeile an – dieselbe Disziplin wie bei einem Soll-Ist-Vergleichsbericht.
Fazit
Entscheiden Sie, ob der Plan progressiv oder flach ist, lagern Sie die Sätze in eine Tabelle aus, berechnen Sie die Staffeln explizit und erfassen Sie Splits, Caps und Clawbacks als separate Zeilen. Diese Struktur übersteht jede Prüfung und Planänderung. Ein Provisionsmodell wird danach beurteilt, ob ein Außenstehender es nachvollziehen kann.
Was die Sache teuer macht, ist der Neuaufbau bei jeder Planänderung und die anschließende Erklärungsarbeit. Wenn Sie damit Ihr halbes Quartal verbringen, testen Sie Powerdrill Bloom mit Ihrem Deal-Export und Ihrer Provisionssatztabelle. Lesen Sie auch unseren Leitfaden zur Berechnung der Kundenakquisitionskosten aus einer Tabellenkalkulation sowie unsere Seiten zum Excel AI assistant und zum AI financial analysis-Tool.
Häufig gestellte Fragen
Was ist der Unterschied zwischen flachen und progressiven Provisionsstaffeln?
Eine flache Staffel wendet einen einzigen Satz auf den gesamten Betrag an, sobald eine Stufe erreicht ist. Eine progressive Staffel wendet den Satz jeder Stufe nur auf den Teilbetrag an, der in diese Stufe fällt – ähnlich wie bei den Einkommensteuerklassen.
Wie kann ich einen Provisionssatz ohne verschachtelte WENN-Formeln ermitteln?
Tragen Sie Staffeln und Sätze in eine sortierte Tabelle ein und nutzen Sie dann VLOOKUP mit ungefährer Übereinstimmung oder XLOOKUP mit der Einstellung für die genaue oder nächstkleinere Übereinstimmung. Beide Varianten ermöglichen es Ihnen, Sätze zu ändern, ohne eine Formel anfassen zu müssen.
Wie sollten geteilte Deals gehandhabt werden?
Erfassen Sie eine Zuweisungszeile pro Mitarbeiter mit einem expliziten Prozentsatz und fügen Sie eine Prüfung hinzu, ob sich die Prozentsätze auf 100 % summieren. Wenn Sie stattdessen die ursprüngliche Deal-Zeile anpassen, lässt sich der Split nicht mehr nachvollziehen.
Wo werden Clawbacks und Rückerstattungen verbucht?
In dem Zeitraum, in dem die Rückerstattung erfolgt ist, als negative Zeile mit Bezug auf den ursprünglichen Deal. Eine nachträgliche Bearbeitung der Originalzeile verändert Abrechnungen, die bereits freigegeben und ausgezahlt wurden.
Wann sollten die Zahlen gerundet werden?
Nur einmal, und zwar beim endgültigen Auszahlungsbetrag. Das Runden von Zwischenschritten führt über viele Zeilen hinweg zu Abweichungen – der häufigste Grund, warum ein Provisionsmodell nicht mit der Lohnbuchhaltung übereinstimmt.