Arbeitsprobe · Excel und Kalkulation
Produkt- und Marktplatz-Kalkulator
Eine Arbeitsmappe, die man herunterladen und aufmachen kann. 40 Artikel mit absichtlich unsauberen Rohdaten gehen hinein; heraus kommt eine Kalkulation, die je Artikel Deckungsbeitrag, Marge und Mindestverkaufspreis ausweist – und eine Liste dessen, was in den Daten fehlt. Die Rechenlogik stammt aus einer Datei, die ich 2018 im Autoteilehandel selbst gebaut habe. Die Zahlen darin sind erfunden.
Neun Jahre Autoteilehandel, davon der größere Teil mit Preisen und Produktdaten. Für den Aufbau des Marktplatzvertriebs habe ich damals eine Kalkulationsdatei angelegt: je Warengruppe ein Aufschlag, der mit der Abnahmemenge sinkt, ein Faktor für die Warengruppe, Umsatzsteuer darauf und zum Schluss die Rundung auf eine feste Leiter von Preispunkten – damit kein Artikel mit 13,47 Euro im Shop landet. Getrennt ausgewiesen wurden Aufschlag und Marge, weil beides regelmäßig verwechselt wird.
Diese Datei kann ich nicht zeigen: Sie enthält Einkaufspreise und Lieferantenkonditionen eines früheren Arbeitgebers. Was ich zeigen kann, ist die Logik. Sie steckt hier in einer Mappe mit erfundenen Artikeln – und ist um die Marktplatzseite erweitert, die es 2018 in dieser Form noch nicht gab: Provision je Marktplatz und Warengruppe, Zahlungsgebühr, Versandstaffel nach Gewicht.
Der zweite Teil der Mappe ist die Datenseite: Die Rohdaten enthalten eine Warengruppe in vier Schreibweisen, eine Zahl als Text, drei fehlende Werte, eine doppelte Artikelnummer und einen negativen Bestand. Nichts davon wird stillschweigend repariert. Was fehlt, bleibt leer und steht benannt in einer eigenen Spalte.
Was echt ist und was Beispiel
- Echt: die Rechenlogik – Staffelaufschlag, Warengruppenfaktor, Umsatzsteuer, Preisleiter, getrennte Ausweisung von Aufschlag und Marge.
- Echt: die neun Jahre Handelserfahrung, aus denen sie stammt. Das Arbeitszeugnis nennt bei der Datenpflege „große Sorgfalt und Genauigkeit".
- Erfunden: alle 40 Artikel, alle Einkaufs- und Verkaufspreise, alle Provisionssätze, die Versandstaffel und die Preisleiter.
- Nicht enthalten: jede Angabe aus einem Arbeitgebersystem.
Die Mappe in Zahlen
- 9 Blätter, 40 Artikel, 64 Preispunkte
- 38 Artikel rechnen durch, 2 bleiben bewusst offen
- 8 Prüfungen, 6 Funde in den Beispieldaten
- Keine Zelle mit fest eingetragener Zahl in den Rechenblättern
Teil 1 – Datenaufbereitung und Excel-Technik
| Blatt | Was darin passiert |
|---|---|
01_README | Herkunft, Datenweg, Rechenschritte, Annahmen. Wer die Mappe weitergibt, gibt die Erklärung mit. |
02_RAW_DATA | 40 Artikel, wie sie ankommen. Die Fehler darin sind Absicht. |
03_CLEAN_DATA | Warengruppe über eine Zuordnungstabelle vereinheitlicht, Textzahlen gewandelt, je Zeile ein Befund. |
04_PARAMETER | Alle Stellschrauben an einem Ort: Zuordnung, Provision, Zahlungsgebühr, Versandstaffel, Umsatzsteuer, Staffelaufschläge, Preisleiter. |
05_KALKULATION | Zwanzig Spalten, ein Rechenschritt je Spalte. |
06_PIVOT | Erlös und Deckungsbeitrag nach Warengruppe und Marktplatz. |
07_DASHBOARD | Sechs Kennzahlen und ein Säulendiagramm. |
08_CHECKS | Acht Prüfungen. Sie ergänzen keinen Wert, sie zeigen nur, was fehlt. |
09_SENSITIVITAET | Was passiert, wenn sich Provision, Versand, Verkaufs- oder Einkaufspreis bewegen. |
Die Rechenkette
| Schritt | Rechnung | Warum so |
|---|---|---|
| Zielpreis brutto | (Einkauf + Staffelaufschlag) × 1,20 × Warengruppenfaktor | So rechnet die Originaldatei. Der Aufschlag ist ein Betrag, der Faktor ein Vielfaches – zusammen decken sie feste und anteilige Kosten ab. |
| Preispunkt | XVERWEIS auf die Preisleiter, Vergleichsmodus 1 | Aufrunden auf den nächsten definierten Punkt. Krumme Preise gehen so nie in den Shop. |
| Provision | Netto-Erlös × Satz aus 04_PARAMETER | XVERWEIS auf den zusammengesetzten Schlüssel „Marktplatz|Warengruppe" – eine Matrix ohne verschachteltes WENN. |
| Versand | XVERWEIS auf die Gewichtsstaffel, Vergleichsmodus 1 | Die erste Staffel, in die das Gewicht passt. Fehlt das Gewicht, bleibt die Zeile leer – es wird nichts geschätzt. |
| Deckungsbeitrag | Netto-Erlös − Einkauf − Provision − Zahlung − Versand − Handling | Alles, was tatsächlich an der Sendung hängt. |
| Marge und Aufschlag | DB / Netto-Erlös · DB / Einkauf | Zwei verschiedene Zahlen. Beide stehen da, weil ihre Verwechslung im Handel teuer ist. |
| Mindestpreis | (Einkauf + Versand + Handling) / (1 − Provision − Zahlung) × 1,20 | Der Bruttopreis, bei dem der Deckungsbeitrag genau null ist. Darunter ist jeder Verkauf ein Verlust. |
Drei Dinge sind nicht in der Datei – und das steht auch drin
Die Mappe wird von einem Python-Skript erzeugt. Formeln, Tabellen, Formate und das Diagramm entstehen dabei vollständig. Power Query, PivotTable und VBA lassen sich so nicht erzeugen: Excel legt sie in eigenen, binären Abschnitten der Datei ab, die keine offene Bibliothek schreiben kann.
Statt so zu tun, als seien sie enthalten, liegen sie fertig daneben:
eine .m-Datei mit der Power-Query-Abfrage, die dieselbe
Bereinigung als wiederholbaren Ladeschritt macht, und ein
.bas-Modul mit einem Makro, das die Mappe aktualisiert,
nach Deckungsbeitrag sortiert und einen datierten Kurzbericht anlegt.
Beides ist in Excel in einem Schritt importiert; die Anleitung steht in
01_README und im Kopf der jeweiligen Datei. Die PivotTable
ist in 06_PIVOT als Formelmatrix bereits gerechnet und
lässt sich mit zwei Klicks danebenlegen.
Teil 2 – Die Kalkulationslogik aus dem Handel
Eine Kalkulation ist erst dann brauchbar, wenn sie die Frage beantwortet,
was passiert, wenn sich etwas verschiebt. Marktplätze heben Provisionen
an, Versanddienstleister ihre Tarife, Wettbewerber senken Preise. Blatt
09_SENSITIVITAET rechnet vier solcher Bewegungen durch –
einzeln und alle zugleich.
| Szenario | Deckungsbeitrag | Veränderung | Marge |
|---|---|---|---|
| Ausgangslage | 209,01 € | – | 24,8 % |
| Provision steigt um 2 Prozentpunkte | 192,17 € | −16,84 € | 22,8 % |
| Einkaufspreis steigt um 5 % | 190,76 € | −18,25 € | 22,7 % |
| Verkaufspreis sinkt um 5 % | 171,69 € | −37,32 € | 20,4 % |
| Versand wird je Sendung 1,00 € teurer | 171,01 € | −38,00 € | 20,3 % |
| alle vier zugleich | 98,60 € | −110,41 € | 11,7 % |
Vier Bewegungen, von denen keine dramatisch klingt, halbieren den Deckungsbeitrag. Bemerkenswert ist die Reihenfolge: Ein Euro mehr Versand je Sendung kostet mehr als zwei Prozentpunkte Provision – weil der Versand ein fester Betrag ist und deshalb die billigen Artikel trifft, nicht die teuren. Genau solche Effekte sieht man nur, wenn die Kosten je Position getrennt geführt werden statt als Pauschale.
Annahme dieser Tabelle: Die Preispunkte werden nicht neu gerundet. Wer den Verkaufspreis tatsächlich senkt, landet auf der nächsten Stufe der Preisleiter; die Wirkung ist dann etwas größer oder kleiner als hier gerechnet. Die Zahlen beziehen sich auf die 38 durchgerechneten Beispielartikel und bedeuten außerhalb dieser Mappe nichts.
Grenzen
- Keine Berufserfahrung im Controlling. Kalkuliert habe ich im Verkauf und beim Aufbau des Marktplatzvertriebs, nicht in einer Controlling-Abteilung.
- Keine Originaldatei. Was hier liegt, ist eine Nachbildung der Logik mit erfundenen Zahlen, keine historische Datei.
- Excel-Niveau ist eigene Angabe. Power Query, Pivot, XVERWEIS und Makros beherrsche ich nach eigener Einschätzung sicher. Ein Zeugnis, das das bestätigt, gibt es nicht – die Mappe hier ist der Ersatz dafür: Sie lässt sich öffnen und nachprüfen.
- Kein Skonto, keine Retouren, keine Lagerkosten. Bewusst weggelassen. Eine Arbeitsprobe soll die Logik zeigen, nicht jeden Sonderfall.
Herunterladen
Drei Dateien. Die Arbeitsmappe funktioniert für sich; die beiden anderen ergänzen Power Query und das Makro, wenn Sie das sehen möchten.
Arbeitsmappe (.xlsx, 37 KB) Power-Query-Abfrage (.m) Makro-Modul (.bas)
Die Mappe wird von skripte/excel-kalkulator-bauen.py erzeugt
und von skripte/excel-kalkulator-pruefen.py nachgerechnet –
das zweite Skript hat beim Bauen zwei Mal Alarm geschlagen, weil die
Aufschläge zu knapp bemessen waren. Passend dazu:
Einstieg für Daten und Prozesse ·
für Web und Inhalt ·
dasselbe Prinzip als Werkzeug im Browser.