Konzept für eine Preiskalkulation

Question

Hallo,

Ich soll ein Kalkulationsschema für unsere Druckerei erstellen.

Was ich habe, sind die Kosten der einzelnen Produktionsschritte in cent pro Quadratmeter in Excel-Tabellen. Diese sind jedoch zum einen abhängig von der Formatgröße und zum anderen von den zu produzierenden Stückzahlen.

Wir haben 5 Haupt-Formatgrößen und alle anderen Formate sollen sich an diesen Formatgrößen orientieren, Wenn also z.B. das zu produzierenden Format 53 m² hat, soll die Kalkulation mit den Kosten für 50 m² rechnen usw.

Es gibt auch Kosten wie z.B. die Druckkosten, die sich sowohl an den Hauptformaten als auch an der Stückzahl orientieren. Andere wiederum orientieren sich nur an den Hauptformaten oder an der Stückzahl.

Mir fällt einfach nicht ein, wie ich das Ding aufziehen soll.

Hat da jemand Erfahrungen?

Gruß
Ingo

Answer 1

Da ich noch keine genau Vorstellung habe, wie eine konkrete Rechnung durchzuführen ist, empfehle ich dir einfach nur dich mit den Excel-Funktionen (Einfügen > Funktion) zu befassen und zu sehen, was das brauchst, beziehungsweise, was nützlich sein könnte, denkbar wären Funktionen, wie WENN, SVERWEIS oder VERGLEiCH, die in der Hilfe dokumentiert sind;

VERGLEICH

Sucht Werte innerhalb eines Bezugs oder einer Matrix. Verwenden Sie VERGLEICH immer dann statt einer der VERWEIS-Funktionen, wenn Sie die Position eines Elements in einem Bereich und nicht das Element selbst benötigen.

Syntax

VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)

Suchkriterium ist der Wert, aufgrund dessen der gewünschte Wert in einer Tabelle gesucht wird.

Suchkriterium gibt den Wert an, gegen den Sie bestimmte Elemente der Suchmatrix abgleichen möchten. Wenn Sie beispielsweise in einem Telefonbuch die Telefonnummer eines Bekannten suchen, verwenden Sie dessen Namen als Suchkriterium, obwohl die Telefonnummer der Wert ist, den Sie eigentlich wünschen.

Suchkriterium kann ein Wert (eine Zahl, eine Zeichenfolge oder ein Wahrheitswert) oder ein Bezug auf eine Zelle sein, die eine Zahl, eine Zeichenfolge oder einen Wahrheitswert enthält.

Suchmatrix ist ein zusammenhängender Zellbereich mit möglichen Vergleichskriterien. Suchmatrix muss eine Matrix oder ein Bezug auf eine Matrix sein.

Vergleichstyp ist die Zahl -1, 0 oder 1. Vergleichstyp gibt an, auf welche Weise Microsoft Excel die Werte in einer Suchmatrix mit den Suchkriterien vergleicht.

Ist Vergleichstyp gleich 1, gibt VERGLEICH den größten Wert zurück, der kleiner gleich Suchkriterium ist. Die Elemente der Suchmatrix müssen in aufsteigender Reihenfolge angeordnet sein: ...-2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR.

Ist Vergleichstyp gleich 0, gibt VERGLEICH den ersten Wert zurück, der gleich Suchkriterium ist. Die Elemente der Suchmatrix dürfen in beliebiger Reihenfolge angeordnet sein.

Ist Vergleichstyp gleich -1, gibt VERGLEICH den kleinsten Wert zurück, der größer gleich Suchkriterium ist. Die Elemente der Suchmatrix müssen in absteigender Reihenfolge angeordnet sein: WAHR, FALSCH, Z-A,...2, 1, 0, -1, -2,... usw.

Fehlt das Argument Vergleichstyp, wird es als 1 angenommen.

Hinweise

VERGLEICH gibt als Ergebnis die Position zurück, die der jeweils gefundene Wert innerhalb der Suchmatrix einnimmt, und nicht den Wert selbst. Zum Beispiel gibt VERGLEICH("b";{"a"."b"."c"};0) den Wert 2 zurück; dies ist die relative Position, die "b" innerhalb der Matrix {"a"."b"."c"} einnimmt.
VERGLEICH unterscheidet beim Abgleichen von Zeichenfolgen (Texten) nicht zwischen Groß- und Kleinbuchstaben.
Findet VERGLEICH keinen übereinstimmenden Wert, gibt die Funktion den Fehlerwert #NV zurück.
Ist Vergleichstyp gleich 0 und als Suchkriterium eine Zeichenfolge angegeben, darf Suchkriterium die Platzhalter Sternchen (*) und Fragezeichen (?) enthalten. Ein Sternchen ersetzt beim Abgleichen eine beliebige Zeichenfolge; ein Fragezeichen ersetzt ein einzelnes Zeichen.
Beispiel

Das Beispiel ist möglicherweise leichter zu verstehen, wenn Sie es in ein leeres Arbeitsblatt kopieren.

Wie wird's gemacht?

Erstellen Sie eine leere Arbeitsmappe oder ein leeres Arbeitsblatt.
Wählen Sie das Beispiel im Hilfethema. Markieren Sie jedoch nicht die Zeilen- oder Spaltenüberschriften.

Answer 2

Oder du postest mal die .xls und erklärst, was zu tun ist.

Answer 3

Oder Ihr übergebt das ganze mal einem Fachmann (was EXCEL betrifft, bin ich KEINER - ich spreche also nicht pro domo) und bezahlt ihn dafür anständig!

Answer 4

Hallo Ingo,


Der Aufbau einer Kostenrechnung/Kalkulation ist meistens für den betreffenden Betrieb maßgeschneidert.

Welche Kosten in den m² bezogenen Kosten bereits enthalten sind entzieht sich meiner Kenntnis >danach müsste sich allerdings das Kalkulationsschema orientieren und angepasst werden.

Die Berechnung des Kalkulationsschemas (Von- und In-Hundertrechnung) mit Excel stellt kein Problem mehr dar.

Viel mehr kann ich auf Grund der Informationen aus der Ferne nicht sagen, eines ist sicher, in Zeiten eines starken Konkurrenzdruckes gewinnt die Kostenrechnung/Kalkulation immer mehr an Bedeutung.

Um es interessierten Usern zu ermöglichen gute Tipps geben zu können, würde ich Dir empfehlen eine konkrete Beispieldatei z.B. hier hochzuladen und den bekanntgegebenen Link direkt ins Forum zu stellen.

Infos


Gruss
Paul1

Excel 2003/XP prof.

Answer 5

@Paul1 & @Prof.Frink
Ich habe das Teil mal hochgeladen nach
http://www.file-upload.net/download-3226685/NeueKalkldummy.xls.html

Bislang wurde auf dem Kalkulationsblatt, welches sich schon vonselber rechnet, die einzelnen Preise per anderen Blätter in Cent per m² mit der Hand in das Kalkulationsblatt eingetragen.
Ziel ist, dass man Menge, Format, Anzahl der Druckfarben, Farbdeckung sowie die Konfektionsdaten 0/1 nein/ja eingibt und die Kalulation sich dann selbst die entsprechenden Cent pro m² aus den anderen Datenblätter holt). Ich weiß aber garnicht, ob man Excel überhaupt dazu überreden kann, z.B. bei einer Menge von 136.000 Stück das Blatt Lohndruck 100 zu verwenden, welches für Stückzahlen von 100.000 bis 249.999 Stück gilt.
Ferner weiß ich auch nicht, ob Excel bei den Quadratmetern (per 1000 Stück), hier 90 m², die Spalte mit der Überschrift 88 auszuwählen.
Excel soll anhand der Quadratmeter per mille (1000 Stück) entscheiden, welches Hauptformatspalte es für die Kalkulation nimmt. Es soll immer das nächstliegende Hauptformat sein.

@nostalgiker6
würde ich gerne machen. Aber unserer Firma geht es nicht gut. Wir machen Kurzarbeit und müssen Kosten sparen.

Gruß
Ingo

Answer 6

Hallo Ingo,

Beim Versuch eine brauchbare Lösung zu erarbeiten ist mir folgendes aufgefallen:

von F4 bis H4 (Farbanzahl C, 2, 4,2), von wo kommt dieser Preis her?
Ich nehme an "Lohndruck 100".

Von "Lohndruck 100" (geht von 100.000 bis 249.999 Stück) wurde der Preis von 2 C = 4,2 cent genommen aus Spalte 88 (für 90m² gerundet).
Hier handelt es sich um 30 verschiedene Preise innhalb von 5 Größen und 6 Farb-Gruppen das kann ich mir mit einer einzigen Formel in H4 nicht vorstellen,
noch dazu gibt es 4 verschiedene Lohndruck-Tabellen.

Die anderen Positionen wären mit Wenn/und Abfragen lösbar, da mehrfach die gleichen Preise verwendet werden.

Möglicherweise ist eine Komplettlösung mittels VBA-Makro machbar, kann ich mangels ausreichender Kenntnisse aber nicht einschätzen.

schöne Grüße

Paul1

Answer 7

@Paul1

Die 4,2 Cent kommen aus 'Lohndruck 100', da die Menge 125.000 Stück beträgt und weil 1000 Formate der Größe 325 x 256 mm aufgerundet 90 m² ergeben, kommt die Spalte des Hauptformates 88 in Betracht und da der Preis für einen zweifarbigen Druck (2C).

Die Tabellen mit Lohndruck 50, 100, 250 und 500 hatten wir früher in einer einzigen Tabelle. Ich habe Sie augeteilt wegen der unterschiedlichen preise je Menge.

Ich kann Sie natürlich auch wieder zusammenfügen, wenn das einfacher wird. Man könnte dann in der A-Spalte (oder auch in Zeile 1) die Bezeichnung 501C, 502C, 503C, 504C, 505C, 506C und dann 1001C usw. verwenden.

Ich weiß aber nicht, wie man den Excel dazu überreden kann, bei einer Menge von 125.000 Stück in 2C, dann nur 1002C anzusprechen bei gleichzeitige Berücksicht des Hauptformates 88 m².

Ich habe einfach kein Konzept, wie ich das anpacken könnte mit dem Lohndruck. Das andere dürfte nicht so schwer sein.

Die Tabellenblätter kann man natürlich auch umschreiben nach den Erfordernissen des Kalkulationsblattes.

Wie gesagt, bislang hatten wir die Kalkulation und eine ausgedruckte Preistabelle und haben dann die Daten von der Preistabelle ind das Kalkulationsblatt per Hand übertragen.

Gruß
Ingo

Answer 8

Hallo Ingo,

Wir gehen was Deine Frage betrifft völlig konform!

Mit dieser Formel könnte es gehen:

Im Tabellenblatt Test enthalten

=WENN(UND(H2<250000;I2>65);VERWEIS(F12;$A$2:$A$7;$B$2:$B$7);"")

Die muß nur mehr in der Besipieldatei angepasst werden.

Die Lohndrucktabellen könntest Du so lassen wie sie sind, nur im Kalkulationsblatt müssen wegen der Mengenabstufung in Spalte H
noch 3 Zellen für die noch nötigen Formeln angefügt werden.
H4 ist schon vorhanden z.B. Lohndruck 50
H5 z.B. Lohndruck 100
H6 z.B. Lohndruck 250
H7 z.B. Lohndruck 500

Dies ist erforderlich, da Zelle H 4 alleine nicht genügt, da es 4 Mengenabstufungen gibt, die in der Formel nicht mehr unterzubringen sind (wegen der Abfragen).
Die Abfragemöglichkeiten sind bereits mit den 5 m² Staffelungen fast erschöpft.

Außerdem ist es zwingend, dass in Zelle G4 nicht nur 2 steht, sondern 1C, 2C, 3C etc. (wegen der Vergleichsfunktion)

Ich hoffe, dass mein Vorschlag auch Deinen Vorstellungen entspricht.

Das Übrige glaube ich ist leicht umsetzbar, da sind wir einer Meinung.

Testtabelle

schöne Grüße

Paul1

Answer 9

@Paul1

Erstmal vielen Dank für die Formel. Das hilft erstmal weiter.

Soweit ich Dich verstehe, hast Du keine andere Lösung für das Mengenproblem beim Kriterium Farbanzahl mit Lohndruck als das Kriterium Farbanzahl jeweils 4 mal im Kalkulationsblatt auszuführen, praktisch Farbanzahl 50, 100, 250 und 500, damit die jeweils angesprochene Lohndrucktabelle praktisch einzeln angesprochen werden kann.

Im Kalkulationsblatt könnte ich die Menge in Tausendstück ausdrücken, was auch noch realistischer wäre.

Ich hatte gedacht, dass man eine Formel entwickeln kann:

Wenn Menge in A2 50 bis 99, dann Lohndruck 50
und wenn Menge in A2 100 bis 249, dann Lohndruck 100
und wenn Menge in A2 250 bis 499, dann Lohndruck 250
und wenn Menge in A2 größer 500, dann Lohndruck 500 benutzen.

Leider ist mein Kenntnisstand in Excel nicht so groß, um sagen zu können, ob sowas überhaupt geht.

Gruß
Ingo

Answer 10

Hallo Ingo,

Die Formeln habe ich ja schon entwickelt, es ist egal ob die Mengen in tausend oder genau angeführt werden, die Formel bekomme ich nicht als einzelne in H 4 hin.

Die noch anzupassende komplette Formel (für Lohndruck 100) in H5 lautet:

=WENN(UND(H2>=100000;H2<250000;I2>65);VERWEIS(F12;$A$2:$A$7;$B$2:$B$7);WENN(UND(H2>=100000;H2<250000;I2<=25);VERWEIS(F12;$A$2:$A$7;$F$2:$F$7);WENN(UND(H2>=100000;H2<250000;I2<=30);VERWEIS(F12;$A$2:$A$7;$E$2:$E$7);WENN(UND(H2>=100000;H2<250000;I2<=45);VERWEIS(F12;$A$2:$A$7;$D$2:$D$7);WENN(UND(H2>=100000;H2<250000;I2<=65);VERWEIS(F12;$A$2:$A$7;$C$2:$C$7);"")))))

Hier kann auch statt (H2>=100000;H2<250000;....z.B. H2>=100;H2<250;.. eingesetzt werden, was keine wesentliche Änderung bringt, die Quadratmeterstaffelung muss in der Formel trotzdem eingebaut sein.

Im konkreten Beispiel (Lohndruck 100) muss die Formel in H5 lauten:

=WENN(UND(A2>=100000;A2<250000;D2>65);VERWEIS(G5;Lohndruck 100!$A$2:$A$7;$B$2:$B$7)usw.........

Sollte das Kalkulationsblatt nicht geändert werden dürfen, könnte man die Formeln der 4 Lohndrucktabellen woanders placieren:

z.B. H16 bis H19

und mit der Formel in H4:

=WENN(H16>0;H16;WENN(H17>0;H17;WENN(H18>0;H18;WENN(H19>0;H19;""))))

den Wert ausgeben.


schöne Grüße

Paul1