Office: Provisionsberechnung bzw. Abschnittsfaktoren

Helfe beim Thema Provisionsberechnung bzw. Abschnittsfaktoren in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Einleitung und Problematik Gegeben sei folgender Fall: Ein Mitarbeiter bekommt für die Teil-Umsätze ab 00.000 0% 15.000 3% 18.000 4%... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von lupo1, 22. August 2017.

  1. lupo1
    lupo1 Tutorial Guru

    Provisionsberechnung bzw. Abschnittsfaktoren



    Einleitung und Problematik


    Gegeben sei folgender Fall: Ein Mitarbeiter bekommt für die Teil-Umsätze ab

    00.000 0%
    15.000 3%
    18.000 4%
    20.000 5%
    30.000 8%

    Provision. Die kürzestmögliche Formel habe ich dafür vor 11 Jahren hier entwickelt:

    =MAX(A1*{0.3.4.5.8}%-{0.45.63.83.173}*10)

    Es handelt sich um den Vergleich von 5 linearen Funktionen y=mx+n, also von =A1*0%-0 bis =A1*8%-1730.

    Zu Recht werde ich aber jedesmal nach dem zweiten Glied n: -{0.45.63.83.173}*10 gefragt. Dessen Ermittlung ist nicht leicht nachvollziehbar.

    Daher suchte ich nach einer Formel, die besser zu warten ist. Sie soll statt n natürlich x als Parameter haben, weil dessen Grenzen in fast allen Fällen genannt werden. n ist ja nur eine mathematische Größe, mit der bei Provisionsverhandlungen niemand etwas anfangen kann.

    Lösung

    Dies funktioniert so:

    =SUMMENPRODUKT((A1>
    {0.15.18.20.30}*1000)*(A1-
    {0.15.18.20.30}*1000)*(
    {0.3.4.5.8}-
    {0.0.3.4.5})%)


    (SUMME allein reicht hier auch, da Arraykonstanten ausgewertet werden. Bei Bezügen jedoch benötigt man SUMMENPRODUKT.)

    (A1>{0.15.18.20.30}*1000) stellt fest, in welchem Intervall wir sind. Ist A1: Umsatz bspw. 25.000, ergibt sich für die Bedingung {1.1.1.1.0}. Wir sind also im 4. Intervall.

    (A1-{0.15.18.20.30}*1000) ermittelt nun die entsprechenden 4 Summanden: {25000.10000.7000.5000.-5000}. Der letzte, nämlich -5000, fällt weg, weil wir ja vorher nur 4 Summanden ermittelt haben.

    ({0.3.4.5.8}-{0.0.3.4.5})%, also die Provisionssätze m, müssen jetzt kumulativ und nicht einzeln darauf angewendet werden, da die Summanden auch kumulativ ermittelt sind: Die heißen nämlich nicht {15000.3000.2000.5000.0}, sondern {25000.10000.7000.5000.-5000}, siehe im vorigen Schritt. Wir dürfen sie im Ergebnis also nur mit der jeweiligen Änderung zum Vorwert m berücksichtigen: {0.3.1.1.3}%. Dies ist jedoch für den Anwender nicht unmittelbar ersichtlich, und daher belassen wir es oben noch beim "Bruttoausweis": ({0.3.4.5.8}-{0.0.3.4.5})%.
    __________________
    Eine Verkürzung der Formel gelingt neben dem erwähnten m-"Nettoausweis" auch beim Zusammenschieben des Parameters x, so dass wir - wie bei der eingangs erwähnten MAX-Funktion - A1 nur noch einmal referenzieren müssen:

    =SUMMENPRODUKT(WENNFEHLER(EXP(LN(A1-
    {0.15.18.20.30}*1000));)*
    {0.3.1.1.3}%)


    Anwendungsbeispiele:
    Preisvergleich (sogar mit Differenz des resultierenden Durchschnittspreises EK zu VK)
    Office Forum • Thema anzeigen - WENN-Formel kürzen
    __________________
    Mit LAMBDA() - benutzerdefinierte Tabellenfunktion! - geht es seit Dez. 2020 in XL365 auch so:

    =Abschnittssumme(19000;{1;1,5;1,8;2;3}/1%%;{0;0;3;4;5;8}%)
    (
    ergibt 130)

    definiert als
    Abschnittssumme beziehtSichAuf
    =LAMBDA(Suchwert;Werte;Anteile;
    WENN(ZEILEN(Werte)+1=ZEILEN(Anteile);
    SUMME(WENNFEHLER(EXP(LN(Suchwert-Werte));)*(
    INDEX(Anteile;SEQUENZ(ZEILEN(Anteile)-1;;2))-
    INDEX(Anteile;SEQUENZ(ZEILEN(Anteile)-1;;1))));
    "Arg3 muss einen Eintrag mehr als Arg2 haben, und zwar als 0 vorneweg"))

    __________________
    Fazit

    Vorteil 1: Die Formel ist zwar nicht mehr ganz so kurz, aber dafür durch jeden nachvollziehbar und vor allem überhaupt erst erstellbar.

    Vorteil 2: Die linearen Abschnitte müssen nicht mehr nacheinander entweder nur steigen oder nur fallen, sondern dürfen in beliebiger Folge beides: Beispiel: Office-Fragen.de

    Vorteil 3: Die Formel ist immer gleich aufgebaut. Bei der kurzen Variante musste man zusätzlich zum schwierigen n noch manuell MAX (für zunehmende Verläufe) oder MIN (für abnehmende Verläufe) schreiben.

    Vorteil 4: Im Bezug auf die Faktoren ist man völlig frei:

    B1: =SUMMENPRODUKT((A1>C1:L1)*(A1-C1:L1)*(N1:W1-M1:V1))

    lässt sogar Platz für 10 Stufen, obwohl wir im Beispiel nur 5 benötigten. Wichtig: M1 ist immer leer oder 0; die Faktoren beginnen erst in N1.
     
    Zuletzt bearbeitet: 10. Dezember 2020
Thema:

Provisionsberechnung bzw. Abschnittsfaktoren

  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden