auf Basis von Zahlungseingängen die Provision für Mitarbeiter berechnen

[kjwagner]

Liebe Exelfreunde,

Ich sitze zwischen den Feiertagen gerade an einem Excel-Master-Sheet in dem ich die Provisionszahlungen an unsere Mitarbeiter berechnen und verwalten kann.
 
Folgende Problemstellung:
 
Ich möchte auf Seite 1 „erfolgreiche Projekt 2020“ unter „I“ nur noch den Zahlungseingang unserer Kunden vermerken, z.B. „Projekt X, KaDeWe, 18.000 €, vom 01.12.2020“ mit „Bezahlt am“ 15.12.020, und dann auf Seite 2 „Umsatz & Zahlungen an Mitarbeit“ angezeigt bekommen wie viel Provision der Vertriebler in Monat Dezember überwiesen bekommt. Dazu gibt es Stichtage die berücksichtigt werden müssen (Seite 2 und/oder Seite 3)
 
Es dreht sich also alles am die Zellen „='erfolgreiche Projekt 2020'!$I:$I“ als Ausgangspunkt und um „Auszahlung im Monat“ auf Seite 2 „Umsatz & Zahlungen an Mitarbeit“.
 
Wer kann helfen? Anpassungen an den Tabellen, zusätzliche Spalten, Zeilen etc sind herzlich Willkommen.
 
Ich komme mit meinem WENNSUMME, diversen Formeln und googlen leider nicht über meiner Grenzen hinaus und bin dankbar für jede Unterstützung!!!!
 
Besten Dank und viele Grüße
 
Kilian 


  Excel Problem Zahlungserfassung & Auszahlungen an Mitarbeiter.xlsx (Größe: 19,41 KB / Downloads: 12)
 
In den von mir verfassten Dokument (siehe Anhang) seht ihr alle relevanten Daten und Informationen.
 
Seite 1 „erfolgreiche Projekt 2020“
èVon „Projekt“ bis „Projektstart“ wird alles vom Verkäufer eingetragen. Wichtig für meine Problemstellung sind „Erfolgsfall“ und „Bezahlt am“. Letzteres Datum will ich nach Zahlungseingang eintragen und direkt auf Seite „Umsatz & Zahlungen an Mitarbeiter“ für den jeweiligen Mitarbeiter die Auszahlung ermittelt bekommen!
 
Seite 2 „Umsatz & Zahlungen an Mitarbeiter“
èHier wird die Provision der Mitarbeiter berechnet.
Die Provision wird quartalsweise berechnet, aber monatlich ausgezahlt (!!!)
Es sind für die Berechnung der Provisionszahlungen nur bereits eingegangene Zahlungen zu berücksichtigen.
 
Um Provision in einem Quartal zu bekommen, muss ein Mitarbeiter >7.000 € Umsatz machen. Für den Bereich 7.001 € bis  40.000 € bekommt er 10 %, von 40 k bis 70 k 15 %, von 70 k bis 90 17,5 % und alles größer 90 k in einem Quartal wird mit 20 % vergütet. In einer Tabelle sieht das wie folgt aus:
 
0 € - 7.000€       7.001€- 40.000€             40.001€-70.000€           70.001€-90.000€             größer 90.000€
 0 %                            10,0%                         15,0%                           17,5%                            20,0%
 
Die entsprechende Formal zu Berechnung der Provision existiert bereits (z.B. siehe Formel in Celle C/D/E 7)
à =SUMMENPRODUKT((C5%%>{0.0,75.4.7.9})*(C5%%-{0.0,75.4.7.9})*({0.10.15.17,5.20}-{0.0.10.15.17,5})/1%)
Provisionsbasis ist gleich die Summe aus den Umsätzen des jeweiligen Quartals (Beispiel Jan + Feb + Mär)
 
Seite 3 „Daten“
è Einige relevanten Daten

[LCohen]

C12[:D12]: =SUMMENPRODUKT((C4%%>{0.0,7.4.7.9})*(C4%%-{0.0,7.4.7.9})*({0.10.15.17,5.20}-{0.0.10.15.17,5})/1%)
E12: =C7-C12-D12

Danke für die Nutzung der mir wohlbekannten Provisionsformel.

[kjwagner]

Hi LCohen,

ja, besten Dank dafür. Das erspart mir bereits seit über einem Jahr extrem viel Zeit und Nerven. Spätestens bei jeder monatlichen Abrechnungen werde ich an daran erinnert! ;)

Hast Du eine Idee wie das Problem zu lösen ist?

Liebe Grüße und nochmals besten Dank für deine Lösung,

Kilian

[LCohen]

Ich habe Dir die monatlichen und die Quartalsabschlusszahlung geliefert (Lösung!). Du musst sie nun im Block auf die anderen Quartale kopieren (Problem?).

[kjwagner]

Danke für den Hinweis, allerdings liegt mein Problem darin, dass ich auf Seite 2 direkt die "Umsatzzahlen" der Verkäufer sehen möchte wenn ich Sie auf Seite 1 eintrage!

Beispiel "Armin" - Armin verkauft am 01. Januar 2021 ein Projekt für 18.000 € an Lidl. Dann trage ich auf Seite 1 ein:
[
Bild bitte so als Datei hochladen: Klick mich!
]
Projekt "A1" 
Kunde "Lidl"
Umsatz "18.000 €)
Umkosten "0 €"
Provisionsbasis "18.000 €"
Erfolgsfall "01.01.2021"
Projektstart "01.03.2021"
Bezahlt am " / " --> kommt die Zahlung rein, dann BOOM, auf Seite 2 in C4 bzw. D4 wenn es nach dem 24.01. ersten war
Verkäufer "Armin"

Problem 1. Dann wär es klasse, wenn das sofort bei "Armin" auf Seite 2 erscheint. Durch eine Formel/Verweis etc.. Das bekomme ich nicht hin.

Problem 2. Bezahlt der Kunde noch bis zum 23.01.2021, bekommt Armin seine Provision (1.100 € ) in dem Monat bezahlt. Trage ich am 23.01., nach dem Zahlungseingang von Lidl, in der Liste das Datum ein, soll das für den Zahlunglsauf im Januar ( Zelle C12 ) erscheinen. 
Bezahlt der Kunde erst am 27.01. fällt es in den Februar 2021 ( Zelle D12 )

Das ist doch möglich, oder?

LG und einen guten Start in die Woche!

Kilian

[LCohen]

Datumsabgrenzungen sind mir für eine Antwort zu billig. Das erledigst Du fein allein.

[LCohen]

Crossposting: ms-office-forum.net/forum/showthread.php?t=371977

[kjwagner]

Hab es jetzt wie folgt gelöst:

1. Problem: Wenn "Datum >= Stichtag"  ist, dann Bezahlung im Folgemonat
Lösung:
"Bezahlt am" (Spalte K): Datum eintragen
"Zahlungslauf" (Spalte L): =WENN(K4="";"offen";WENN(TAG(K4)<24;K4;K4+12))
"Zahlungslauf Monat" (Spalte M): =TEXT(L4;"MM.JJ")

Gibt mir dann die richtigen Daten aus "Bezahlt am" (Spalte K) bzw. für leere Zellen "offen" aus.

2. Problem: Summen für Somme aus Umsätzen zum Zeitpunkt der Erfolgsfälle bzw. Zahlungseingänge
Lösung: mit Formel =SUMMEWENNS

Zahlungslauf - Bespiel "Armin" für das Jahr 2020
Zelle "S4" für Januar 2020 =SUMMEWENNS($O$4:$O$28;$N$4:$N$28;"Armin";$M$4:$M$28;R4)

Freue mich übrigens sehr über private Nachrichten wie "Kleiner, geh deine Schulaufgaben selber machen", "krauen haft" das so etwas gefragt wird oder "Das erledigst Du fein allein." 

Ich werde in Zukunft dabei bleiben und immer Verweise & Hilfestellungen anbieten wann immer ich es kann, egal wie banal das Thema für mich ist. Wahrscheinlich wird das in naher Zukunft nicht in Excel sein, aber vielleicht bei anderen Themen ;)

Wünsche euch ein guten Start ins neue Jahr!

Kilian

[LCohen]

Freue mich übrigens sehr über private Nachrichten wie "Kleiner, geh deine Schulaufgaben selber machen", "krauen haft" das so etwas gefragt wird oder "Das erledigst Du fein allein." 

"Das erledigst Du fein allein" kam von mir, aber nicht als PN, sondern für alle lesbar im Forum.

Jetzt muss ich mich also verteidigen dafür, ich hätte Dir eine PN geschickt. Das ist nicht der Fall. Ich habe auch keine PN von Dir bekommen. Aber den anderen Lesern muss es so vorkommen.