Ergebnis 1 bis 1 von 1
  1. #1
    Office-Hilfe.com - Auskenner
    Registriert seit
    30.12.2016
    Beiträge
    146

    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})%)

    (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 beim "Bruttoausweis": ({0.3.4.5.8}-{0.0.3.4.5})%.

    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.

    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.
    Geändert von lupo1 (22.08.2017 um 12:41 Uhr)

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
Excel Ticker - News, Tipps und Tricks zu Microsoft Excel | SMS kostenlos versenden | Forenuser - Die Foren Findmaschine