14
Provision ausrechnen Excel 2010
Hallo liebe Leute,
ich bräuchte eure Hilfe bezüglich der Berechnung einer Provision und wie ich diese in Excel darstellen kann.
Ich habe mich wund geklickt und viele Möglichkeiten ausprobiert aber es funktioniert nicht.
Problem:
Vertreter macht einen Umsatz von 700.000EUR
Staffelung:
0 bis 200.000EUR = 5%
200.000,01 bis 400.000 = 4%
400.000,01 bis 600.000 = 3%
600.000,01 bis unendlich = 2%
Der Knackpunkt ist die Staffelung, denn die Rechnung für 700.000EUR würde somit lauten:
200.000*0,05= 10.000
199.999,99*0,04= 8.000
199.999,99*0,03= 6.000
99.999,99*0,02= 2.000 (alles bissl aufgerundet in Excel soll es aber gerne ganau sein)
SUMME= 26.000 EUR Provision
Nächster Knackpunkt: Ich brauche ein variables Gerüst, sodass ich mit Tabellen und Sverweisen alle Rahmenbedingungen immer wieder neu anpassen kann. Die Werte sind also nicht fix. Wenn es eine gute "Wenn-Dann" Formel auch seinen Zweck erfüllt gerne. Aber die Variabilität muss gegeben sein.
Ich habe schon einen guten Ansatz hier gefunden:
Provision ausrechnen im Excel 2007
Bei Mitglied: Biber vom 07.02.2014
ABER seine Formel mit dem S-Verweis funktioniert bei mir leider nicht.
Hier ein Bild wie weit ich bisher gekommen bin (bzw. mein bestes Ergebnis bisher aber ohne Staffelung)
Unter E1befindet sich nur die Formel =C2*D2. Bei der Tabelle kann ich auf die Anzeige der Provisionsrate natürlich verzichten.
Wer auch immer bis zum Ende gelesen und durchgehalten hat... DANKE!
Und wer mir dann noch helfen könnte... TAUSEND DANK!
ich bräuchte eure Hilfe bezüglich der Berechnung einer Provision und wie ich diese in Excel darstellen kann.
Ich habe mich wund geklickt und viele Möglichkeiten ausprobiert aber es funktioniert nicht.
Problem:
Vertreter macht einen Umsatz von 700.000EUR
Staffelung:
0 bis 200.000EUR = 5%
200.000,01 bis 400.000 = 4%
400.000,01 bis 600.000 = 3%
600.000,01 bis unendlich = 2%
Der Knackpunkt ist die Staffelung, denn die Rechnung für 700.000EUR würde somit lauten:
200.000*0,05= 10.000
199.999,99*0,04= 8.000
199.999,99*0,03= 6.000
99.999,99*0,02= 2.000 (alles bissl aufgerundet in Excel soll es aber gerne ganau sein)
SUMME= 26.000 EUR Provision
Nächster Knackpunkt: Ich brauche ein variables Gerüst, sodass ich mit Tabellen und Sverweisen alle Rahmenbedingungen immer wieder neu anpassen kann. Die Werte sind also nicht fix. Wenn es eine gute "Wenn-Dann" Formel auch seinen Zweck erfüllt gerne. Aber die Variabilität muss gegeben sein.
Ich habe schon einen guten Ansatz hier gefunden:
Provision ausrechnen im Excel 2007
Bei Mitglied: Biber vom 07.02.2014
ABER seine Formel mit dem S-Verweis funktioniert bei mir leider nicht.
Hier ein Bild wie weit ich bisher gekommen bin (bzw. mein bestes Ergebnis bisher aber ohne Staffelung)
Unter E1befindet sich nur die Formel =C2*D2. Bei der Tabelle kann ich auf die Anzeige der Provisionsrate natürlich verzichten.
Wer auch immer bis zum Ende gelesen und durchgehalten hat... DANKE!
Und wer mir dann noch helfen könnte... TAUSEND DANK!
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 340520
Url: https://administrator.de/contentid/340520
Ausgedruckt am: 23.11.2024 um 10:11 Uhr
14 Kommentare
Neuester Kommentar
Hallo,
Die eigentliche Loesung hast Du ja schon. Aber!
Warum wird bei Dir die Provision negativer wenn der Erfolg steigt?
Eigentlich ueberhaupt kein Anreiz, fuer das Volk, dafuer ueberhaupt zu arbeiten.
Warten wir mal auf die Taschenrechnerkuenstler.
BFF
P.S.
Neues Berechnungsmodell bei Volkswagen?
Die eigentliche Loesung hast Du ja schon. Aber!
Warum wird bei Dir die Provision negativer wenn der Erfolg steigt?
0 bis 200.000EUR = 5%
200.000,01 bis 400.000 = 4%
400.000,01 bis 600.000 = 3%
600.000,01 bis unendlich = 2%
200.000,01 bis 400.000 = 4%
400.000,01 bis 600.000 = 3%
600.000,01 bis unendlich = 2%
Eigentlich ueberhaupt kein Anreiz, fuer das Volk, dafuer ueberhaupt zu arbeiten.
Warten wir mal auf die Taschenrechnerkuenstler.
BFF
P.S.
Neues Berechnungsmodell bei Volkswagen?
Der Harem für den Vorstand muss ja irgendwie finanziert werden
Hallo Mascha.MG
hab mich jetzt gerade mal Hingesetzt und hab das zum laufenbekommen als Reine WENN Formel runterziehbar und alles unten angehängt ein Link zu einer Google Tabelle in der Meine Tabelle runterladbar ist und 2 fotos (formeln und reines ergebnis) Beachte hierbei bitte wenn du die schwellwerte der Provisionen änderst solltest du sie nur ändern ist das kein Problem einfach in der Tabelle die 600000,01 gegen z.b. 700000,01 tauschen solltest du jedoch eine Weitere schwelle hinzufügen wollen z.b. 800.000,01 = 1% dann musst du denn auch in der Formel anpassen bzw eine weitere Wenn funktion hinzufügen.
MFG Simon Lohr
Download
https://drive.google.com/open?id=0BxqNOD-p4Q-KMGhvbklDenp3WUU
hab mich jetzt gerade mal Hingesetzt und hab das zum laufenbekommen als Reine WENN Formel runterziehbar und alles unten angehängt ein Link zu einer Google Tabelle in der Meine Tabelle runterladbar ist und 2 fotos (formeln und reines ergebnis) Beachte hierbei bitte wenn du die schwellwerte der Provisionen änderst solltest du sie nur ändern ist das kein Problem einfach in der Tabelle die 600000,01 gegen z.b. 700000,01 tauschen solltest du jedoch eine Weitere schwelle hinzufügen wollen z.b. 800.000,01 = 1% dann musst du denn auch in der Formel anpassen bzw eine weitere Wenn funktion hinzufügen.
MFG Simon Lohr
Download
https://drive.google.com/open?id=0BxqNOD-p4Q-KMGhvbklDenp3WUU
Moin Mascha.MG,
ich bekomme es auch nach der im anderen Beitrag vorgeturnten SVerweis-Mimik zum Laufen oder auch nach der Summenprodukt-Variante von Stackoverflow.
Mit einer Hilfsmatrix namens "prov" in der Zellen $G$3:$I$6 mit den Werten wie von dir vorgegeben
"Betragsgrenzwerte" Spalte G ab G3 = 0; 20000,01, 400000,01, 600000,01
"Fixprovisionen" Spalte H ab H3 = 0; 10000; 18000; 26000
"Prozente" Spalte I ab I3 = 5%; 4%; 3%; 2%
kann ich folgende Formel zur Berechnung anbieten (zu provisonierender Wert wird in B3 erfasst):
Ist hässlich, weil gleich drei Sverweis()-Aufrufe auf dieselbe Ziel-Zeile drin sind.
Aber wenn du die Formel 3x liest, dann steht als Pseudo-Code auch nur drin:
Jedenfalls kommt das Gewünschte raus, zumindest bei deinen Beispielwerten. Kollege simonlohr rechnet irgendwie anders.
Grüße
Biber
P.S. Nein, ich habe keinen WENNFEHLER() drumrumgesetzt, weil... wenn ein Depp als "zu provisonierender Betrag" in Zelle B3 den Wert "Leverkusen" eingibt, dann will ich den Fehler nicht abfangen.
P.P.S. Bitte nimm mal das alberne "Hilfe!" aus dem Beitragstitel. Ich mag diese Effekthascherei nicht.
Grüße
Biber
ich bekomme es auch nach der im anderen Beitrag vorgeturnten SVerweis-Mimik zum Laufen oder auch nach der Summenprodukt-Variante von Stackoverflow.
Mit einer Hilfsmatrix namens "prov" in der Zellen $G$3:$I$6 mit den Werten wie von dir vorgegeben
"Betragsgrenzwerte" Spalte G ab G3 = 0; 20000,01, 400000,01, 600000,01
"Fixprovisionen" Spalte H ab H3 = 0; 10000; 18000; 26000
"Prozente" Spalte I ab I3 = 5%; 4%; 3%; 2%
kann ich folgende Formel zur Berechnung anbieten (zu provisonierender Wert wird in B3 erfasst):
=RUNDEN(SVERWEIS(B3;prov;2)+((B3-(SVERWEIS(B3;prov;1)))*SVERWEIS(B3;prov;3));2)Ist hässlich, weil gleich drei Sverweis()-Aufrufe auf dieselbe Ziel-Zeile drin sind.
Aber wenn du die Formel 3x liest, dann steht als Pseudo-Code auch nur drin:
=Runden( fixeProv + BetragVariabelZuProvisionieren * variablerProzentSatz)
=RUNDEN(SVERWEIS(B3;prov;2)+((B3-(SVERWEIS(B3;prov;1)))*SVERWEIS(B3;prov;3));2)Jedenfalls kommt das Gewünschte raus, zumindest bei deinen Beispielwerten. Kollege simonlohr rechnet irgendwie anders.
Grüße
Biber
P.S. Nein, ich habe keinen WENNFEHLER() drumrumgesetzt, weil... wenn ein Depp als "zu provisonierender Betrag" in Zelle B3 den Wert "Leverkusen" eingibt, dann will ich den Fehler nicht abfangen.
P.P.S. Bitte nimm mal das alberne "Hilfe!" aus dem Beitragstitel. Ich mag diese Effekthascherei nicht.
Grüße
Biber
1
Hi Simon,
Hi Biber,
ich möchte mich bei euch beiden bedanken.
(Wie toll das ich die Liste direkt runterladen konnte.
)
Ich werde noch etwas rumprobieren wie das mit dem anpassen ist.
________________________________________________________________________
@simonlohr Habe es ausprobiert aber es kommen leider nicht die richtigen Ergebnisse heraus, aber ich werde nochmal versuchen mit deinem Ansatz weiter zu machen. LG
Hi Biber,
ich möchte mich bei euch beiden bedanken.
(Wie toll das ich die Liste direkt runterladen konnte.
)Ich werde noch etwas rumprobieren wie das mit dem anpassen ist.
________________________________________________________________________
@simonlohr Habe es ausprobiert aber es kommen leider nicht die richtigen Ergebnisse heraus, aber ich werde nochmal versuchen mit deinem Ansatz weiter zu machen. LG
So @Biber ich habe deine Anleitung befolgt aber ich mache irgendwas falsch.
Eigentlich hast du es schon so geschrieben das man es nur noch reinkopieren muss.
Hier der Status:
Könntest du mir vielleicht noch den letzten Anstoß geben? Ich wünschte ich könnte den Fehler selbst erkennen, aber leider ist das hier dann doch etwas zu hoch für mich geworden.
Bin auf deine Antwort gespannt.
LG
=RUNDEN(SVERWEIS(B3;prov;2)+((B3-(SVERWEIS(B3;prov;1)))*SVERWEIS(B3;prov;3));2)
Eigentlich hast du es schon so geschrieben das man es nur noch reinkopieren muss.
Hier der Status:
Könntest du mir vielleicht noch den letzten Anstoß geben? Ich wünschte ich könnte den Fehler selbst erkennen, aber leider ist das hier dann doch etwas zu hoch für mich geworden.
Bin auf deine Antwort gespannt.
LG
Moin Mascha.MG,
also, soweit ich es sehen kann, sind es zwei Fehler, die die Abweichung verursachen.
a) ein Tippfehler von mir bei den "Fixprovisionen", bei dir in Zelle $I$5 muss statt des Wertes 26000 der Wert 24000 stehen.
Siehe deine Herleitung im Eröffnungthread:
Provision, wenn Betrag über 200000=10000
+Provision, wenn Betrag über 400000=+8000
+Provision, wenn Betrag über 600000=+6000
--> unterm Strich = 24000
b) zweiter Fehler:
Bei mir sind die Prozentwerte wirklich Prozentwerte, also eingeben als "Taste 5 und Taste %" gleichbedeutend mit einer Eingabe von "=5/100" oder "0,05" für fünf Prozent.
Du hast die Zahlen 2, 3, 4, 5 in den Provisonen stehen statt bei mir 0.02, 0.03, 0.04, 0.05.
Entweder du erfasst diene Prozente wie ich oder teilst in der "berechneProvision"-Formel an der richtigen Stelle durch 100.
Wenn beides korrigiert ist, dann sollte bei dem Betrag von 700000 angezeigt werden der Wert 26000 Ocken.
Grüßé
Biber
also, soweit ich es sehen kann, sind es zwei Fehler, die die Abweichung verursachen.
a) ein Tippfehler von mir bei den "Fixprovisionen", bei dir in Zelle $I$5 muss statt des Wertes 26000 der Wert 24000 stehen.
Siehe deine Herleitung im Eröffnungthread:
Provision, wenn Betrag über 200000=10000
+Provision, wenn Betrag über 400000=+8000
+Provision, wenn Betrag über 600000=+6000
--> unterm Strich = 24000
b) zweiter Fehler:
Bei mir sind die Prozentwerte wirklich Prozentwerte, also eingeben als "Taste 5 und Taste %" gleichbedeutend mit einer Eingabe von "=5/100" oder "0,05" für fünf Prozent.
Du hast die Zahlen 2, 3, 4, 5 in den Provisonen stehen statt bei mir 0.02, 0.03, 0.04, 0.05.
Entweder du erfasst diene Prozente wie ich oder teilst in der "berechneProvision"-Formel an der richtigen Stelle durch 100.
Wenn beides korrigiert ist, dann sollte bei dem Betrag von 700000 angezeigt werden der Wert 26000 Ocken.
Grüßé
Biber
1
@Biber
Ich verneige mich voller Demut Herr Biber. Es ist vollbracht.
Jetzt hab ich das mit den Fixprovisonen verstanden... Man man das hätte mir ruhig früher einfallen können. Der Aufbau so ist perfekt! So kann ich alles verändern solange die Fixprovisionen auch dazu passen etc.. Zumindest habe ich es jetzt geschnallt.
Wahnsinn ich würde am liebsten einen Präsentkorb überreichen. :D
Ich finde es toll das ihr Excel-Gurus uns in solchen Foren mit Rat und Tat zur Seite steht.
Freu mich so über meine neue kleine Excel-Tabelle.
Ich verneige mich voller Demut Herr Biber. Es ist vollbracht.
Jetzt hab ich das mit den Fixprovisonen verstanden... Man man das hätte mir ruhig früher einfallen können. Der Aufbau so ist perfekt! So kann ich alles verändern solange die Fixprovisionen auch dazu passen etc.. Zumindest habe ich es jetzt geschnallt.
Wahnsinn ich würde am liebsten einen Präsentkorb überreichen. :D
Ich finde es toll das ihr Excel-Gurus uns in solchen Foren mit Rat und Tat zur Seite steht.
Freu mich so über meine neue kleine Excel-Tabelle.
Moin Mascha.MG,
eine Sache können wir noch besser machen.
dieser "Fehler a)" aus dem letzten Kommentar war unnötig/selbst verschuldet.
Ist entstanden, weil ich nicht Excel habe rechnen lassen; dabei ist das Aas darin viel schneller als ich.
Grüße
Biber
[Edit] Geändert: in J3 eine 0 eingeben. Nötig, da in Zelle I3 möglicherweise eine Überschrift=Text steht und keine Zahl und somit #WERT angezeigt wird. Bei mir stehen keine Überschriften, kann ich auch in J3 die Formel lassen. [/Edit]
eine Sache können wir noch besser machen.
dieser "Fehler a)" aus dem letzten Kommentar war unnötig/selbst verschuldet.
Ist entstanden, weil ich nicht Excel habe rechnen lassen; dabei ist das Aas darin viel schneller als ich.
- Füge eine weitere neue Spalte an deine Provisionstabelle mit dem Inhalt "Teilprovisonen".
- bei mir ist das Spalte J. In J3 eine 0 eingeben. Ab J4 Formel eingeben "=RUNDEN(G4-G3;0)*I3" == Runden(BetragAktuelleZeile-BetragVorzeile)*Provisionssatz
- daraufhin sind in der neuen Spalte die Werte 0, 10000, 8000, 6000 zu sehen
- in der Spalte "Fixprovisionen" kannst du dann ab H3 eingeben: "=SUMME(J$3:J3)" und nach unten kopieren
- daraufhin hast du immer die "kumulierten Fixprovisionen" ohne Tippfehler.
Grüße
Biber
[Edit] Geändert: in J3 eine 0 eingeben. Nötig, da in Zelle I3 möglicherweise eine Überschrift=Text steht und keine Zahl und somit #WERT angezeigt wird. Bei mir stehen keine Überschriften, kann ich auch in J3 die Formel lassen. [/Edit]
1Zitat von @Mascha.MG:
@simonlohr Habe es ausprobiert aber es kommen leider nicht die richtigen Ergebnisse heraus, aber ich werde nochmal versuchen mit deinem Ansatz weiter zu machen. LG
@simonlohr Habe es ausprobiert aber es kommen leider nicht die richtigen Ergebnisse heraus, aber ich werde nochmal versuchen mit deinem Ansatz weiter zu machen. LG
in wiefern zeigen sich hier fehler?
Gruß Simon
Moin simonlohr,
vergleiche doch mal die Werte beispielsweise für den Betrag 700000 Taler.
In Maschas Vorgabe soll eine Provision berechnet werden in Höhe von 26000 Talern (wie er im Eröffnungsbeitrag hergeleitet hat).
Bei dir kommen aber in deinem Screenshot für den "Vertreter 3", der genau diesen Betrag 700000 hat, nur 16000 Taler heraus.
Ich vermute, deshalb die Rückmeldung "leider nicht die richtigen Ergebnisse".
Hier ein paar Testwerte mit IMHO den gewünschten Ergebnissen (berechnet nach meinem Algorithmus):
Betrag: 111111 --> Provision: 5555,55
Betrag: 200000 --> Provision: 10000
Betrag: 200001 --> Provision: 10000,04
Betrag: 400000 --> Provision: 18000
Betrag: 500000 --> Provision: 21000
Betrag: 700000 --> Provision: 26000
Halt mal deine Ergebnisse daneben.
Grüße
Biber
vergleiche doch mal die Werte beispielsweise für den Betrag 700000 Taler.
In Maschas Vorgabe soll eine Provision berechnet werden in Höhe von 26000 Talern (wie er im Eröffnungsbeitrag hergeleitet hat).
Bei dir kommen aber in deinem Screenshot für den "Vertreter 3", der genau diesen Betrag 700000 hat, nur 16000 Taler heraus.
Ich vermute, deshalb die Rückmeldung "leider nicht die richtigen Ergebnisse".
Hier ein paar Testwerte mit IMHO den gewünschten Ergebnissen (berechnet nach meinem Algorithmus):
Betrag: 111111 --> Provision: 5555,55
Betrag: 200000 --> Provision: 10000
Betrag: 200001 --> Provision: 10000,04
Betrag: 400000 --> Provision: 18000
Betrag: 500000 --> Provision: 21000
Betrag: 700000 --> Provision: 26000
Halt mal deine Ergebnisse daneben.
Grüße
Biber
1
Hi Biber,
ein Wunder! Ich habe doch tatsächlich die gleichen Ergebnisse.
Nur bei H3 habe ich "=SUMME(J$3:J4)" eingegeben und dann runterkopiert damit wieder 10.000 18.000 & 24.000 bei der Fixprovision rauskommen.
(Bei =SUMME(J$3:J3) habe ich ein Bild reingehängt wie es dann ausgesehen hätte.
Das ist echt klasse so! Ich werde jetzt nochmal ein paar Verkäufe eingeben die nicht so gerade (423.275,56€ oder so) sind. Gestern ist mir entweder ein Rechenfehler unterlaufen oder es muss doch noch was geändert werden, weil die Provision nicht gestimmt hat.
Wenn du möchtest halte ich dich da gerne auf dem laufenden.
Ganz lieben Gruß
______________________________________
Neuer Stand: Ich kann nicht rechnen. :D Aber unsere Tabelle kann. Ich bin richtig glücklich damit.
Alleine wäre ich nie darauf gekommen. Vorallem nicht mit den Sverweisen. Die freuen mich am meisten, denn so erspare ich mir bei Änderungen die super langen Formeln.
ein Wunder! Ich habe doch tatsächlich die gleichen Ergebnisse.
Nur bei H3 habe ich "=SUMME(J$3:J4)" eingegeben und dann runterkopiert damit wieder 10.000 18.000 & 24.000 bei der Fixprovision rauskommen.
(Bei =SUMME(J$3:J3) habe ich ein Bild reingehängt wie es dann ausgesehen hätte.
Das ist echt klasse so! Ich werde jetzt nochmal ein paar Verkäufe eingeben die nicht so gerade (423.275,56€ oder so) sind. Gestern ist mir entweder ein Rechenfehler unterlaufen oder es muss doch noch was geändert werden, weil die Provision nicht gestimmt hat.
Wenn du möchtest halte ich dich da gerne auf dem laufenden.
Ganz lieben Gruß
______________________________________
Neuer Stand: Ich kann nicht rechnen. :D Aber unsere Tabelle kann. Ich bin richtig glücklich damit.
Alleine wäre ich nie darauf gekommen. Vorallem nicht mit den Sverweisen. Die freuen mich am meisten, denn so erspare ich mir bei Änderungen die super langen Formeln.
Moin Mascha.MG,
hm, woher jetzt diese Abweichung kommt, kann ich nicht nachvollziehen.
Ich hatte gestern noch mal in meiner Schrit-für-Schritt-Anleitung korrigiert, dass in J3 eine 0 eingeben werden sollte statt Formel, falls über der Tabelle in Zeile 2 noch Überschriften stehen.
Egal, wesentlich ist, dass in den 4 Zellen für Fixprovisonen die Werte 0; 10000; 18000; 24000 stehen.
Dann kommen auch die Ergebnisse raus, die ich gestern in meiner Antwort an simonlohr gepostet habe und die sich IMHO mit deiner Anforderung decken.
Aber halte mich/uns gern auf dem Laufenden.
Grüße
Biber
hm, woher jetzt diese Abweichung kommt, kann ich nicht nachvollziehen.
Ich hatte gestern noch mal in meiner Schrit-für-Schritt-Anleitung korrigiert, dass in J3 eine 0 eingeben werden sollte statt Formel, falls über der Tabelle in Zeile 2 noch Überschriften stehen.
Egal, wesentlich ist, dass in den 4 Zellen für Fixprovisonen die Werte 0; 10000; 18000; 24000 stehen.
Dann kommen auch die Ergebnisse raus, die ich gestern in meiner Antwort an simonlohr gepostet habe und die sich IMHO mit deiner Anforderung decken.
Aber halte mich/uns gern auf dem Laufenden.
Grüße
Biber
1
