kaufmännisch kalkulieren und runden

[OMGMikroWeich]

Hallo zusammen,

 

vorab gesagt bzw. geschrieben, ich habe mir das, was ich an Excel kann, selber beigebracht, da zu der Zeit, als ich studiert habe, Excel noch nicht wirklich viel verwendet wurde. Insofern gilt, eine einfache Formel ist kein Problem, doch je mehr Verschachtelungen, desto mehr Probleme habe ich. Zudem habe ich bisher kaum wirklich mit "echten" Excel-Formeln gearbeitet, sodaß ich auch eigentlich und im besten Fall nur die Funktion WENN kenne und leidlich anwenden kann.

 

Da ich die Anleitung bzgl. Crossposting gelesen habe, ist das hiesige Forum das einzige, wo ich diese Frage stelle.

Ich versuche seit ein paar Tagen eine Formel zu erstellen, mit der ich kalkulieren kann. Nach allem was ich bisher versucht habe, finde ich die Lösung nicht oder es gibt ggf. keine Lösung.  :34:  :20: :22: :39: :69:

Problem:

Einkaufspreise (folgend "EK" genannt) für unterschiedlichste Artikel von ca. 0,45 € rauf bis ca. 850,-- € (zur Info, Verkaufspreise nenne ich "VK").

 

Nun soll in einem ersten Schritt der nicht gerundete Verkaufspreis ohne MWSt errechnet werden.

 

Klar ist dabei, daß z. B. der Multiplikator für einen Artikel, dessen EK bei 0,45 € liegt, ein anderer ist, als der Multiplikator für einen Artikel, dessen EK bei 850,-- € liegt.

 

Mit anderen Worten, ich möchte mit Intervallen arbeiten.

 

Z. B.

alle Artikel <50,00 € mit 15 multiplizieren.

alle Artikel ≥50,00 € und ≤499,99 mit 6 multiplizieren.

alle Artikel ≥500,00 € und ≤849,99 mit 5 multiplizieren.

 

Und z. B. genau hier habe ich ein Problem. Wenn ich einen Artikel mit dem EK von 500,00 € und einen anderen Artikel mit dem EK von 499,99 € habe, dann ergibt die folgende Formel die folgenden Resultate:

 

Formel:

=WENN(A19<50;A19*15;WENN(UND(A19>=50;A19<=499,99);A19*6;WENN(UND(A19>=500;A19<=849,99);A19*5;0)))

Ergebnis EK 500,00 €: VK = 2.500,00 €

Ergebnis EK 499,99 €: VK = 2.999,94 €

Ich erreiche mit der o. g. Formel erst wieder ab einem EK von 416,66 € einen niedrigeren VK als bei EK 500,00 €, dann nämlich 2.499,96 €.

 

Mit anderen Worten, es existiert eine „Lücke“ von 500,00 € minus 416,66 € = 83,34 €, in der die o. g. Formel nicht wirklich Sinn macht.

 

Und wenn ich stringent kalkulieren möchte, dann sollte natürlich ein Artikel, der im EK weniger als ein anderer kostet, auch nicht für einen höheren VK kalkuliert werden.

 

Gibt es eine Funktion, mit der man diese „Lücke“ so „überbrücken" kann, daß die stringente Kalkulation zwar erhalten bleibt, ein Artikel, der im EK weniger kostet als ein anderer aber auch nicht für mehr kalkuliert wird, als eben dieser Artikel, der im EK höher liegt?

 

Nun könnte ich natürlich die Intervalle in der o. g. Formel so klein machen oder die Multiplikatoren so fein einstellen, daß es eine kaufmännisch vernünftige Kalkulation ergibt. Allerdings kann ich dann auch gleich jeden Artikel einzeln kalkulieren, dann benötige ich auch keine Formel…

 

In einem zweiten Schritt möchte ich dann auf den bisher nicht gerundeten Verkaufspreis ohne MWSt die MWSt aufschlagen. Kein Problem, das bisherige Ergebnis einfach nur * 1,19.

=WENN(A19<50;A19*15;WENN(UND(A19>=50;A19<=499,99);A19*6;WENN(UND(A19>=500;A19<=849,99);A19*5;0)))*1,19

 

In einem dritten Schritt möchte ich dann den nun eigentlich fertigen VK kaufmännisch vernünftig runden.

Und dabei stoße ich dann wieder auf dasselbe Problem, wie auch schon oben beim ersten Schritt.

Daß ein Artikel (nicht nur) in Deutschland der Höhe des Preises entsprechend gerundet wird, ist Standard, es existieren im Einzelhandel keine Preise, die auf den Pfennig genau kalkuliert sind (naja, Benzin an den Tankstellen mal ausgenommen...)

 

Formel:

=WENN(A19<50;A19*15;WENN(UND(A19>=50;A19<=499,99);A19*6;WENN(UND(A19>=500;A19<=849,99);A19*5;0)))*1,19

Beispiel Ergebnis bei einem EK von 812,39 €: VK inkl. MWSt = 4.833,72 €

Nun macht es nicht wirklich Sinn, die 4.833,72 € auf

4.832,99 € oder auf

4.832,90 € oder auf

4.830,00 € oder auf

4.829,00 € zu runden.

 

Generell erreicht man runden durch hinzufügen von z. B. „0", „1", „2", „-1" oder auch „-2" an der richtigen Stelle der Verschachtelungen.

 

Sinnvoll wäre es, wenn das Ergebnis 4.800,00 € oder auch 4.790,00 € wäre.

 

Auch klar ist, daß ein Artikel mit einem per Formel kalkulierten VK von z. B. 20,53 € dann zu 19,90 € oder auch 19,99 € abgerundet werden sollte.

Hingegen sollte ein Artikel mit einem per Formel kalkulierten VK inkl. MWSt von z. B. 23,53 € dann zu 23,50 €, 22,99 € oder auch 22,90 € abgerundet werden.

 

Gibt es für diese Problematik eine Lösung, also die „eierlegende Wollmilchsau“? Oder muß man doch alles einzeln kalkulieren?

 

Vorab vielen Dank :78:  & herzliche Grüße aus Köln in die digital reality!

[BoskoBiati]

Hallo,

1. Die von Dir angesprochene Lücke wird sich nicht füllen lassen, das ist einfach so, es sei denn, Du baust einen Grenzwert ein, über den der VK im Zweifel nicht drüber bzw. drunter sein darf.
2. Du alleine weißt, wie Du wann runden möchtest. Wenn Du das auflistest, kann man eine Formel erstellen.

Code:

=A19*WENN(A19<50;15;wenn(A19<500);6;WENN(A19<850);5;0)))
=a19*(15-(A19>=50)*9-(A19>=500)*1-(A19>=850)*5)

[OMGMikroWeich]

Hallo Edgar,

 

danke für Deine Antwort!

 

Leider verfestigt Sie meine Ahnung, daß das, was ich suche, wohl nicht oder nur schwer zu erzielen ist.

 

Ich hatte gedacht, daß es vielleicht eine Möglichkeit gibt, einen Logarithmus entsprechend in eine Excel-Formel einzubauen. Soweit meine mathematische Bildung mal ging und noch besteht, halte ich das Logarithmus-Verfahren für einen gangbaren Weg. Wenn ich mich richtig erinnere, ist bei wachsenden Zahlenreihen der Gebrauch des Logarithmus für das von mir beschriebene Problem der stringenten und lückenlosen Kalkulation bei stark unterschiedlichen EK's anwendbar. Und sollte ich damit richtig liegen, wie muß dann die Formel in Excel aussehen…???

 

Die beiden von Dir freundlicherweise zur Verfügung gestellten Formeln

 

=A19*WENN(A19<50;15;wenn(A19<500);6;WENN(A19<850);5;0)))

 

und

 

=a19*(15-(A19>=50)*9-(A19>=500)*1-(A19>=850)*5)

 

funktionieren bei mir nicht, warum auch immer. Leider verstehe ich eben auch nicht genug von der Materie, um feststellen zu können, warum.

 

Danke & Gruß,

 

Ben

[Jockel]

Hallo Ben, da hat's Edgar gut mit dir gemeint, und dir ein paar Klammern zu viel zur freien Verfügung zugestanden (2 Klammern müssen raus)... ... probier mal

=A19*WENN(A19<50;15;WENN(A19<500;6;WENN(A19<850;5;0)))

Die zweite Formel sollte rechnen...

[OMGMikroWeich]

@BoskoBiati

Hallo Edgar,

die eine der beiden Formeln von Dir funktioniert doch bei mir, nämlich die
=a19*(15-(A19>=50)*9-(A19>=500)*1-(A19>=850)*5)

Ben

[OMGMikroWeich]

Hallo Ben, da hat's Edgar gut mit dir gemeint, und dir ein paar Klammern zu viel zur freien Verfügung zugestanden (2 Klammern müssen raus)... ... probier mal

=A19*WENN(A19<50;15;WENN(A19<500;6;WENN(A19<850;5;0)))

Die zweite Formel sollte rechnen...

Hallo Jockel,

danke für den Hinweis!

Ich hatte schon ein wenig mit der nicht funktionierenden Formel herumprobiert und hatte den Verdacht, daß ggf. die Anzahl der Klammern nicht stimmt, weil es um drei öffnende und fünf schließende Klammern ging.

Gruß,

Ben