Formel für Zahlungsberechnung

Bild

Betrifft: Formel für Zahlungsberechnung
von: Mike
Geschrieben am: 22.06.2015 18:34:12


Guten Tag zusammen,
bin in der glücklichen Lage eine Excelkalkulation verbessern zu dürfen und muss gerade eine Formel schreiben, an der ich mir die Zähne ausbeisse...vielleicht hat hier jemand ja eine Idee, mir sind nach einigen Stunden knobeln diese leider ausgegangen. :( Vielen Dank an alle, die sich damit befassen!
Hier ist schonmal die Datei: https://www.herber.de/bbs/user/98388.xlsx
Und hier die Problemstellung:
Ich benötige eine Formel um die "Payment received per month ausrechnen zu lassen.
Die Basis dafür ist einersets "Payment Terms from Customers (in month)" und andererseits "Payment Milestones".
Bsp.: Wenn nun die Payment Terms also zwei Monate sind und der erste Payment Milestone im Monat "1" 10% sind,dann ist das erste "Payment Received" im Monat 3
(da gemäß Zahlungsbedingungen ja 2 Monate Aufschub ok sind). Wären die Payment Terms 3 Monate, müsste unser "Payment Received" folglich im 4. Monat sein.
Das wichtigste ist, dass die Payment Milestones (10% im Monat 1, 20% im Monat 10,...) lediglich Beispielwerte sind, die Formel muss sich also automatisch anpassen,
dass auch andere Payment Milestones (z.B. 20% im Monat 2, 40% im Monat 7 usw.) korrekt berechnet werden.
Vielen Dank für jede Hilfe,
Mike

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Michael
Geschrieben am: 22.06.2015 20:53:49
Hallo Mike,
die Formelprofis können's bestimmt eleganter (und der Befehl ist bei großen Datenmengen zu langsam), aber gib in E8 ein

=WENN(BEREICH.VERSCHIEBEN(E7;0;-$B$6+1)<>"";$B$2*BEREICH.VERSCHIEBEN(E7;0;-$B$6+1);"")
und kopiere das Ding nach rechts.
Die Prüfung auf "" funktioniert bei den Spalten A und B nicht; um das zu umgehen, kann man zwei leere Spalten C und D einfügen und ausblenden, dann tut die Formel schon ab C8.
Schöne Grüße,
Michael
P.S.: vielleicht sollte man den Text ändern auf Payments expected, weil ich "received" erst eingeben würde, wenn die Zahlung auf dem Konto ist. Man weiß ja nie.

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Mike
Geschrieben am: 22.06.2015 21:32:32
Hallo Michael,
Vielen vielen Dank für deine schnelle Rückmeldung und die super Lösung! Du bist meine Rettung!
Eine kleine Änderung ist mir aufgefallen, und zwar müssen die "Payment received" praktisch eine Zelle nach Rechts, sprich wenn wie im Beispiel "Payment Terms" 3 Monate sind, sollte der erste Wert in F8 stehen, also eins weiter rechts. Wie passe ich die Formel dahingehend am besten an?
Viele Grüße und vielen Dank
Mike

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Michael
Geschrieben am: 22.06.2015 22:16:07
Hi Mike,
mach an *beiden* Stellen jeweils das "-1" aus, das paßt den Monatswert in B6 auf Spalten links/rechts an.

=WENN(BEREICH.VERSCHIEBEN(E7;0;-$B$6)<>"";$B$2*BEREICH.VERSCHIEBEN(E7;0;-$B$6);"")
Aber: wenn er im Mai zahlen soll und 3 Monate Zahlungsziel hat, dann stimmt es ja, wie es ist, naja, einigermaßen vom 1.Mai bis 30.Juli jedenfalls...
Happy Exceling,
Michael

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Mike
Geschrieben am: 22.06.2015 22:44:32
Hallo Michael,
ich beginne die Zusammenhänge zu verstehen...ich habe sogar eine Spalte C und D eingefügt, sodass es bei 0 Monaten Payment Terms auch richtig die "Payments received" anzeigt. Das einzige Problem ist aber, dass ich, wenn ich zB. 3 Monate Payment Terms hab, es "von vorne" weiterzählt, sprich im 4. Monat ein Payment received wie gewünscht aber im 1. Monat nun auch ein Payment received, bei 4 Monate Payment Terms dann analog im 4. Monat wie gewünscht, aber auch im 2. Monat UND im 1. Monat nun. Gibt es dafür auch eine Lösung?
Viele Grüße und nochmals vielen Dank!
Michael

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Michael
Geschrieben am: 22.06.2015 23:15:17
Hi Mike,
es is schon spät und begreif's nicht - kannst nochmal die geänderte Datei hochladen?
Schöne Grüße,
Michael

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Mike
Geschrieben am: 22.06.2015 23:28:51
Guten Abend Michael,
garkein Problem, habe mich auch nicht besonders eindeutig ausgedrückt.
Hier das upgedatete File: https://www.herber.de/bbs/user/98395.xlsx
In Zelle E8 sind jetzt 65.000 die nicht darin sein sollten, wenn ich als Payment Term "4" nehmen würde, wäre ein Wert in E8 UND in F8, was nicht sein darf, da es ja keinen Sinn macht.
Wie bekommt man diese vorderen Werte weg, sodass lediglich im Falle von Payment Term 4 der Wert in Zelle I8 steht und in den vorderen Zellen nichts?
Viele Grüße
Mike

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Michael
Geschrieben am: 23.06.2015 14:18:23
Hallo Mike,
jetzt verstehe ich, was Du meinst. Hm, das mit den zwei Spalten war ein Denkfehler oder eine "unerlaubte Abkürzung" meinerseits.
Bei P-Terms 4 bräuchte man dann 3 Spalten, und dadurch, daß Du das "-1" rausgemacht hast, sind die 65000 in E8 völlig "korrekt", denn die Formel schnappt sich die "100%" aus B7.
Also gut, es ist Quatsch, immer irgendwelche Spalten einzufügen, also nehme ich sie (C+D) raus und überlege mir ne saubere Formel: es geht darum, die Spaltennr., in der sich die Formel befindet mit dem P-Term abzugleichen: schreib mal zum Verständnis

=WENN(SPALTE()-$B$6>=3;"ja";"nein") 
[wenn die Spalte, in der sich die Formel befindet minus P-Term die Spalte C oder weiter rechts ergibt]
in F9 und kopiere es ein paar Spalten nach links und rechts. Anstelle des "ja" setzt Du nun die andere Formel ein, und die Geschichte sollte passen:
=WENN(SPALTE()-$B$6>=3;
WENN(BEREICH.VERSCHIEBEN(G7;0;-$B$6)<>"";
$B$2*BEREICH.VERSCHIEBEN(G7;0;-$B$6);"");"")
Schöne Grüße,
Michael

Bild

Betrifft: AW: Formel für Zahlungsberechnung
von: Mike
Geschrieben am: 23.06.2015 21:04:38
Hallo Michael, kurze Rückmeldung: Es funktioniert einwandfrei! Vielen vielen Dank!
Gruss
Mike

Bild

Betrifft: gerne, vielen Dank für die Rückmeldung owT
von: Michael
Geschrieben am: 24.06.2015 07:46:07


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formel für Zahlungsberechnung"