Office: Suche Formel zur automatischen Berechnung von Preisen mit 2 Bedingungen

Hallo zusammen,

ich suche nach einer passenden Formel (oder einem Skript), das mir Preise für verschiedene Kundengruppen automatisch berechnet.

Ich habe eine Excel mit folgenden Preisen:

Einkaufspreis
Verkaufspreis
Preis für Kundengruppe 1
Preis für Kundengruppe 2

Die Preise habe ich jeweils brutto und netto drin.

Ich möchte lediglich den netto-EK sowie den brutto-VK eingeben, den Rest soll mir Excel ausrechnen.
Das mache ich bei der KG1 derzeit wie folgt:

=RUNDEN(J1/100*(100-(T1*0,4*100));-1)-1

Damit erreiche ich, dass die Kundengruppe 1 40% des Rabatts erhält, der zwischen dem EK und dem VK möglich ist.
Also wenn ich 30% Marge auf ein Produkt habe, dann bekommt KG1 40% von den 30%.
Zudem runde ich auf die nächste 9, damit es schöne Preise ergibt.

Was mich nun an dieser Sache stört ist, dass gerade bei niedrigen Preisen ich drauf zahle.
Denn wenn ich einen VK von €11,97 (netto) und einen EK von €8,55 (netto) habe, dann berechnet er mir einen Preis für die KG1 von €9,- (brutto), was netto nur €7,56 sind.

Das sollte vermieden werden. Es müsste also eine Bedingung rein, dass der Preis für diese KG NIEMALS kleiner sein darf als der EK.

Andersrum darf er auch nie höher sein als der VK. Das passiert nämlich auch. Beispiel:
VK €6,71 (netto; entspricht €7,99 brutto), EK €4,95 (netto). Preis für KG1: €9,- (brutto, da er ja immer auf die nächste 9 rundet). Der Kunde der KG1 würde also mehr zahlen als nötig.

Wie könnte ich denn nun diese 2 Bedingungen mit einbringen?

Danke euch schon mal :)

 

vielleicht reicht aufrunden?

Gruß Hajo

 

Hallo Desecrator,

ich denke, dass deine Formel nicht ganz das wiederspiegelt, was du erreichen willst :) (na ja, sonst würde ja auch ein anderes herauskommen :D). OK ich denke, dass die folgende Tabelle ein sinnvolles Resultat bringt:

[TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]EK[/TD]
[TD="align: right"]VK[/TD]
[TD="align: right"]Marge[/TD]
[TD="align: right"]Netto (30%)[/TD]
[TD="align: right"]Netto (40%)[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10,00 €[/TD]
[TD="align: right"]14,00 €[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]12,80 €[/TD]
[TD="align: right"]12,40 €[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]10,00 €[/TD]
[TD="align: right"]14,00 €[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]12,79 €[/TD]
[TD="align: right"]12,39 €[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]27,65 €[/TD]
[TD="align: right"]44,80 €[/TD]
[TD="align: right"]62%[/TD]
[TD="align: right"]39,69 €[/TD]
[TD="align: right"]37,89 €[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]8,55 €[/TD]
[TD="align: right"]11,97 €[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]10,89 €[/TD]
[TD="align: right"]10,59 €[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]4,95 €[/TD]
[TD="align: right"]7,99 €[/TD]
[TD="align: right"]61%[/TD]
[TD="align: right"]7,09 €[/TD]
[TD="align: right"]6,79 €[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR="bgcolor: #eeaaaa"]
[TD]Zelle[/TD]
[TD]Formel[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]=B2/A2-1[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]=B2-((B2-A2)*30%)[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]=B2-((B2-A2)*40%)[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]=B3/A3-1[/TD]
[/TR]
[TR]
[TD]D3[/TD]
[TD]=RUNDEN(B3-((B3-A3)*30%);1)-0,01[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=RUNDEN(B3-((B3-A3)*40%);1)-0,01[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]=B4/A4-1[/TD]
[/TR]
[TR]
[TD]D4[/TD]
[TD]=RUNDEN(B4-((B4-A4)*30%);1)-0,01[/TD]
[/TR]
[TR]
[TD]E4[/TD]
[TD]=RUNDEN(B4-((B4-A4)*40%);1)-0,01[/TD]
[/TR]
[TR]
[TD]C5[/TD]
[TD]=B5/A5-1[/TD]
[/TR]
[TR]
[TD]D5[/TD]
[TD]=RUNDEN(B5-((B5-A5)*30%);1)-0,01[/TD]
[/TR]
[TR]
[TD]E5[/TD]
[TD]=RUNDEN(B5-((B5-A5)*40%);1)-0,01[/TD]
[/TR]
[TR]
[TD]C6[/TD]
[TD]=B6/A6-1[/TD]
[/TR]
[TR]
[TD]D6[/TD]
[TD]=RUNDEN(B6-((B6-A6)*30%);1)-0,01[/TD]
[/TR]
[TR]
[TD]E6[/TD]
[TD]=RUNDEN(B6-((B6-A6)*40%);1)-0,01[/TD]
[/TR]
</tbody>[/TABLE]

Zeilen 5 und 6 sind genau deine Beispiele, wo du über die falschen Ergebnisse gestolpert bist.
Und falls ich dich falsch verstanden habe, einfach noch einmal anhand eines Beispiels hier posten.

 

Hallo,

das Thema wurde hier:

http://www.office-loesung.de/ftopic497885_0_0_asc.php&sid=db4bea4202e01172e62708d55da9df0f
http://www.ms-office-forum.net/forum/showthread.php?t=284919

schon ausführlich erörtert, so gar mit einer toll grauslichen formel!

 

Das klingt wie ein Plan...

(btw: Kalkulationen sind immer Netto...)

Das klingt wie ein Plan, der nicht funktioniert... ;-)

Wenn durch Gewährung von Rabetten, Skonti oder sonstigen Nachlässen ein Verlust beim Verkauf entsteht, dann ist die Kalkulation an sich bereits Murks.

Ich verrat Dir mal ein Geheimnis: Ein Rabatt, der dem Kunden gewährt wird, ist vorher bereits in den Preis mit einkalkuliert! Wenn er dann doch nicht gewährt wird, ist es ein zusätzlicher Gewinn...

Ich hab mir mal für Schulungs-Zwecke eine Excel-Mappe zusammengestellt, in der die einzelnen Modelle der Kalkulation aufgeführt sind. Da kannst Du
a) ausgehend von einem vorgegebenen Einkaufspreis den "echten" Einkaufspreis = Bezugspreis berechnen (Bezugskalkulation).
b) ausgehend von einem vorgegebenen Bezugspreis den minimalen Verkaufspreis berechnen (Absatzkalkulation vorwärts).
c) ausgehend von einem vorgegebenen Verkaufspreis den maximalen Bezugspreis berechnen (Absatzkalkulation rückwärts).
d) ausgehend von einem vorgegebenen Ein- und Verkaufspreis die Gewinnspanne berechnen (Differenzkalkulation).

Innerhalb der ganzen Kalkulationen gibt es etliche Parameter, an denen gedreht werden kann. Bei einigen wirst Du ev. Wikipedia oder sonstige Quellen benötigen, wenn die kaufmännischen Begriffe nicht gleich ganz klar sein sollten. Die ganze Doku dazu ist nicht in der Mappe enthalten.

Rechts neben den Berechnungen sind auch die Prozent-Schemata in ausgegrauter Schrift dargestellt. Sinn: Es ist nicht immer ganz klar, wo die 100% sind und von wo nach wo gerechnet wird. Schulung eben...

Gruß, Michael