Super-Sale-WocheClaude Skills – 20 % RABATT
Tips

Abweichungsanalyse in Excel erstellen: Schritt für Schritt

Powerdrill Bloom·
Abweichungsanalyse in Excel erstellen: Schritt für Schritt

Um eine Abweichungsanalyse in Excel durchzuführen, stellen Sie Budget- und Ist-Zahlen für jede Position nebeneinander. Subtrahieren Sie das Budget vom Ist-Wert, um die absolute Abweichung zu erhalten. Teilen Sie diese Abweichung durch das Budget, um den Prozentsatz zu ermitteln. Kennzeichnen Sie dann jede Zeile als vorteilhaft oder unvorteilhaft und erläutern Sie die größten Lücken.

Dieser Leitfaden führt Sie durch die Formeln, das Tabellenlayout und ein vollständiges Praxisbeispiel. Er deckt auch ab, wie das Ergebnis grafisch dargestellt wird und wie eine Umsatzlücke in Preis und Volumen aufgeteilt wird. Die Excel-Schritte stammen von den Microsoft-Supportseiten, Stand 10. November 2026.

Was eine Abweichungsanalyse zeigt

Eine Abweichung ist die Lücke zwischen dem, was Sie geplant haben, und dem, was tatsächlich passiert ist. Eine Abweichungsanalyse listet diese Lücken Zeile für Zeile auf und erklärt diejenigen, die von Bedeutung sind.

Die meisten Teams vergleichen Ist-Werte mit einer von drei Baselines:

  • Budget. Der zu Beginn des Jahres festgelegte Plan.
  • Forecast. Ein aktualisierter Plan, der im Laufe des Jahres erstellt wurde.
  • Vorperiode. Letzter Monat, letztes Quartal oder der gleiche Zeitraum des Vorjahres.

Der Microsoft-eigene Copilot-Skill für diese Aufgabe beschreibt den Job gut. Sein Abweichungsanalyse-Skill „vergleicht Ist-Ergebnisse mit Budgets, Forecasts oder Vorperioden und erklärt Differenzen nach Preis, Volumen, Mix, Rate oder Timing“.

Das Ergebnis ist in der Regel eine Tabelle plus einige schriftliche Anmerkungen. Die Tabelle zeigt jede Lücke. Die Notizen erklären die drei oder vier Punkte, bei denen Führungskräfte handeln müssen.

Die Kernformeln

Drei Formeln erledigen den Großteil der Arbeit. Angenommen, das Budget steht in Spalte B und der Ist-Wert in Spalte C.

Kennzahl Formel Was sie aussagt
Absolute Abweichung =C2-B2 Wie weit der Ist-Wert vom Budget entfernt ist, in Währung
Prozentuale Abweichung =IFERROR((C2-B2)/ABS(B2),"") Wie groß die Lücke im Verhältnis zum Plan ist
Status =IF(D2=0,"Plangemäß",IF(D2>0,"Über Plan","Unter Plan")) In welche Richtung sich die Zeile bewegt hat

Die ABS-Funktion sorgt dafür, dass das Vorzeichen korrekt bleibt, wenn ein Budget negativ ist. IFERROR lässt die Zelle leer, wenn das Budget Null ist, anstatt einen Fehler anzuzeigen.

Die Richtung ist nicht gleichbedeutend mit gut oder schlecht. Umsatz über Plan ist eine gute Nachricht. Kosten über Plan sind eine schlechte Nachricht. Die nächsten Abschnitte zeigen, wie man damit umgeht.

So richten Sie das Arbeitsblatt ein

Ein sauberes Layout macht das Kopieren der Formeln einfach. Verwenden Sie eine Zeile pro Position und diese Spalten:

  1. Position. Umsatz, Herstellungskosten, Personal, Marketing und so weiter.
  2. Typ. Geben Sie „Umsatz“ oder „Kosten“ ein, damit Excel weiß, welche Richtung vorteilhaft ist.
  3. Budget. Der geplante Betrag für den Zeitraum.
  4. Ist. Der Betrag aus Ihrem Buchhaltungsexport.
  5. Abweichung. Ist minus Budget.
  6. Abweichung %. Abweichung geteilt durch Budget.
  7. Vorteilhaft oder unvorteilhaft. Eine Kennzeichnung basierend auf Typ und Richtung.
  8. Erklärung. Eine kurze Notiz für wesentliche Zeilen.

Halten Sie Budget und Ist für denselben Zeitraum und dieselbe Kontenzuordnung bereit. Eine Abweichungsanalyse ist nur so gut wie diese Übereinstimmung. Wenn die Personalkosten im Budget auf zwei Konten aufgeteilt sind, teilen Sie sie in den Ist-Werten genauso auf.

Formatieren Sie die Tabelle mit Strg+T als Excel-Tabelle. Neue Zeilen übernehmen dann automatisch die Formeln.

So berechnen Sie Abweichungen in Excel

Sobald das Layout steht, sind die Schritte kurz. Angenommen, die oben genannten Spalten beginnen in Zeile 2, mit Typ in B, Budget in C und Ist in D.

  1. Geben Sie in E2 =D2-C2 für die absolute Abweichung ein.
  2. Geben Sie in F2 =IFERROR((D2-C2)/ABS(C2),"") ein und formatieren Sie es als Prozentsatz.
  3. Geben Sie in der Spalte Status die unten gezeigte Prüfung auf Vorteilhaftigkeit ein.
  4. Kopieren Sie die Formeln in jede Zeile nach unten.
  5. Fügen Sie eine Ergebniszeile hinzu und berechnen Sie deren Abweichung aus den Gesamtsummen, nicht durch Addieren der Prozentsätze.

Hier ist die Prüfung auf Vorteilhaftigkeit für Zeile 2 der Spalte Status:

=IF(E2=0,"Plangemäß",IF(B2="Umsatz",IF(E2>0,"Vorteilhaft","Unvorteilhaft"),IF(E2<0,"Vorteilhaft","Unvorteilhaft")))

Zuerst wird die Spalte Typ gelesen. Beim Umsatz ist eine positive Abweichung vorteilhaft. Bei Kosten ist eine negative Abweichung vorteilhaft, da Sie weniger ausgegeben haben als geplant.

Prozentsätze erfordern in der Ergebniszeile Vorsicht. Das Addieren von Zeilenprozentsätzen führt zu einem falschen Ergebnis. Teilen Sie stattdessen die Gesamtabweichung durch das Gesamtbudget.

So kennzeichnen Sie die relevanten Abweichungen

Nicht jede Lücke benötigt einen Kommentar. Die meisten Teams legen eine Wesentlichkeitsregel fest, wie zum Beispiel „über 5 % und über 2.000 $“. Zeilen, die beide Kriterien erfüllen, erhalten eine Erklärung.

Eine einfache Formel markiert diese. Fügen Sie eine Spalte Wesentlich hinzu und geben Sie =AND(ABS(F2)>0,05,ABS(E2)>2000) in Zeile 2 ein. Die Zelle zeigt TRUE für wesentliche Zeilen an.

Farben machen die Tabelle schneller scannbar. Die bedingte Formatierung von Excel kann Symbolsätze hinzufügen. Microsoft gibt an, dass ein Symbolsatz dazu dienen kann, „Daten zu kommentieren und in drei bis fünf Kategorien zu klassifizieren, die durch einen Schwellenwert getrennt sind“.

Um einen hinzuzufügen, wählen Sie die Spalte Abweichung % aus. Gehen Sie dann zu Start > Bedingte Formatierung > Symbolsätze. Microsoft merkt an, dass im Satz mit 3 Pfeilen „der grüne Aufwärtspfeil höhere Werte darstellt“. Kehren Sie bei Kostenzeilen die Symbolreihenfolge um, damit ein Anstieg als Warnung angezeigt wird.

So erklären Sie die Treiber

Zahlen zeigen, wo die Lücke liegt. Treiber erklären, warum. Die Skill-Beschreibung von Microsoft listet fünf gängige Treiber auf: Preis, Volumen, Mix, Rate und Timing.

Preis. Sie haben zu einem anderen Preis verkauft oder gekauft als geplant. Preisabweichung = (Ist-Preis − Budget-Preis) × Ist-Menge.

Volumen. Sie haben eine andere Menge verkauft oder verbraucht. Volumenabweichung = (Ist-Menge − Budget-Menge) × Budget-Preis.

Mix. Der Anteil von Produkten, Regionen oder Kunden hat sich geändert. Eine Verschiebung hin zu margenschwächeren Artikeln senkt den Gewinn, selbst wenn das Volumen gleich bleibt.

Rate. Ein Kostensatz hat sich geändert, zum Beispiel ein Stundenlohn oder eine Lieferantengebühr.

Timing. Geld wurde zwischen Zeiträumen verschoben. Eine März-Rechnung, die im April bezahlt wurde, erscheint in beiden Monaten als Lücke.

Preis und Volumen zusammen erklären die gesamte Umsatzlücke. Diese Prüfung lohnt sich jedes Mal, da sie die Mathematik bestätigt.

Ein Beispiel für eine Abweichungsanalyse

Hier ist ein Praxisbeispiel für ein fiktives Unternehmen, Cedar and Pine Coffee. Dessen Finanzleiter vergleicht die Ist-Werte des dritten Quartals mit dem Budget.

Position Typ Budget Ist Abweichung Abweichung % Status
Umsatz Umsatz 250.000 $ 236.500 $ −13.500 $ −5,4 % Unvorteilhaft
Herstellungskosten Kosten 100.000 $ 97.200 $ −2.800 $ −2,8 % Vorteilhaft
Personal Kosten 60.000 $ 64.800 $ 4.800 $ 8,0 % Unvorteilhaft
Marketing Kosten 20.000 $ 26.000 $ 6.000 $ 30,0 % Unvorteilhaft
Miete Kosten 15.000 $ 15.000 $ 0 $ 0,0 % Plangemäß
Betriebsgewinn 55.000 $ 33.500 $ −21.500 $ −39,1 % Unvorteilhaft

Die Gesamtsumme stimmt. Der Umsatz verlor 13.500 $, die Herstellungskosten sparten 2.800 $, das Personal kostete 4.800 $ mehr und das Marketing kostete 6.000 $ mehr. Zusammen ergibt das −21.500 $, was der Gewinnlücke entspricht.

Drei Zeilen bestehen die Wesentlichkeitsregel von über 5 % und über 2.000 $: Umsatz, Personal und Marketing. Die Herstellungskosten veränderten sich nur um 2,8 %, daher erhalten sie keinen Kommentar.

Die Umsatzlücke erfordert einen genaueren Blick. Das Budget ging von 50.000 Einheiten zu 5,00 $ aus. Im Quartal wurden 43.000 Einheiten zu 5,50 $ verkauft.

  • Volumenabweichung. (43.000 − 50.000) × 5,00 $ = −35.000 $.
  • Preisabweichung. (5,50 $ − 5,00 $) × 43.000 = +21.500 $.
  • Gesamt. −35.000 $ + 21.500 $ = −13.500 $, was der Umsatzzeile entspricht.

Die Preiserhöhung hat also Bestand, kostete aber Volumen. Die Notiz an die Führungskräfte sagt genau das aus. Das Marketing gab 30 % mehr aus als geplant, dennoch sanken die Absatzzahlen. Das wirft die Frage auf, welche Kampagnen funktioniert haben.

Das Personal benötigt eine eigene Prüfung. Die Lücke von 4.800 $ könnte ein Ratentreiber sein, wie eine Lohnerhöhung, oder ein Volumentreiber, wie Überstunden. Der Finanzleiter fragt den Betriebsleiter und erfährt, dass zwei Filialen Wochenendschichten hinzugefügt haben. Damit handelt es sich um eine Volumenlücke, die an die Öffnungszeiten gebunden ist, und nicht um eine Lohnerhöhung.

Die abschließende Zusammenfassung für die Führungskräfte passt in eine Zeile. Der Gewinn lag 21.500 $ unter Budget, da eine Preiserhöhung mehr Volumen kostete als sie einbrachte und Marketing sowie Wochenendschichten über Plan lagen.

So schreiben Sie die Abweichungsnotizen

Die Tabelle ist nur die halbe Abweichungsanalyse. Führungskräfte lesen die Notizen, daher sollte jede drei Fragen beantworten: Was hat sich verändert, warum hat es sich verändert und was passiert als Nächstes.

Eine gute Notiz folgt einem einfachen Muster:

  • Was. „Das Marketing gab 6.000 $ mehr aus als budgetiert, 30,0 % über Plan.“
  • Warum. „Zwei bezahlte Kampagnen liefen länger als geplant.“
  • Nächstes. „Wir haben eine Kampagne am 3. Oktober pausiert und erwarten, dass das 4. Quartal plangemäß abschließt.“

Halten Sie jede Notiz auf zwei oder drei Sätze begrenzt. Verwenden Sie dieselbe Reihenfolge der Zeilen wie in der Tabelle, damit die Leser die Notizen den Zahlen zuordnen können.

Trennen Sie einmalige Lücken von Trends. Eine einmalige Reparaturrechnung ist etwas anderes als eine Lohnerhöhung, die sich jeden Monat wiederholt. Markieren Sie sich wiederholende Lücken deutlich, da sie den Forecast verändern.

Schreiben Sie die Notizen zusammen mit dem Verantwortlichen der jeweiligen Zeile. Das Finanzteam sieht die Zahlen, aber der Budgetverantwortliche kennt den Grund. Ein kurzes Gespräch mit jedem Verantwortlichen macht aus einer Vermutung eine Erklärung.

Fügen Sie schließlich oben eine einzeilige Zusammenfassung hinzu. Für das obige Beispiel würde sie lauten: „Der Betriebsgewinn im 3. Quartal lag 21.500 $ unter Budget, hauptsächlich aufgrund eines geringeren Volumens und höherer Marketingausgaben.“

Das Timing ist ebenso wichtig wie die Formulierung. Die meisten Teams führen jeden Monat eine Abweichungsanalyse durch, nachdem die Bücher geschlossen wurden. Ein monatlicher Rhythmus erkennt Probleme, solange noch Zeit zum Handeln ist.

Quartalsberichte gehen tiefer. Sie ergänzen den Preis-Volumen-Split, einen Reforecast und eine Überprüfung, ob das Budget selbst noch sinnvoll ist. Jahresendberichte betrachten das gesamte Jahr im Vergleich zum ursprünglichen Plan.

Verwenden Sie jedes Mal dieselbe Vorlage. Wenn die Spalten und Regeln fest bleiben, nimmt eine Abweichungsanalyse jeden Monat weniger Zeit in Anspruch, und Trends sind über Zeiträume hinweg leichter zu erkennen.

So stellen Sie die Abweichungen grafisch dar

Ein Diagramm hilft Führungskräften, den Weg vom Budget zum Ist-Wert zu sehen. Am besten eignet sich ein Wasserfalldiagramm.

Microsoft sagt, dass ein Wasserfalldiagramm „eine laufende Summe anzeigt, während Werte addiert oder subtrahiert werden“. Es fügt hinzu, dass „Wasserfalldiagramme auch als Bridge-Charts bezeichnet werden“.

Um eines zu erstellen, listen Sie die Schritte vom Budgetgewinn zum Ist-Gewinn auf. Beginnen Sie mit 55.000 $, addieren Sie dann den Effekt jeder Zeile auf den Gewinn und enden Sie bei 33.500 $. Wählen Sie die Daten aus und wählen Sie Einfügen > Wasserfall einfügen > Wasserfall.

Legen Sie den ersten und letzten Balken als Summen fest. Die Microsoft-Seite merkt an, dass die Start- und Endspalten „oft auf der horizontalen Achse beginnen“, während die mittleren Balken schweben. Erhöhungen und Verringerungen sind farblich kodiert, sodass die größten Treiber hervorstechen.

Für die monatliche Verfolgung eignet sich auch ein einfaches Säulendiagramm der Abweichung %. Unser Leitfaden zum Erstellen eines Budget-vs-Ist-Berichts deckt den vollständigen Monatsbericht rund um diese Tabelle ab.

Abweichungsanalyse mit KI durchführen

Die Formeln dauern Minuten, sobald die Tabelle existiert. Der langsame Teil ist oft das Zuordnen von Konten, das Finden von Treibern und das Schreiben der Notizen. Ein KI-Tool kann diese Schritte aus Ihren Exporten entwerfen.

Hier ist die Methode in drei Schritten unter Verwendung von Powerdrill Bloom.

Schritt 1: Laden Sie Ihr Budget und Ihre Ist-Werte hoch

Exportieren Sie das Budget und die Ist-Werte aus Ihrem Buchhaltungs- oder Planungstool. Das AI Budget Analysis Tool akzeptiert „Excel-, CSV- oder PDF-Exporte aus Buchhaltung und FP&A“.

Beziehen Sie Mengen- und Preisdaten ein, wenn Sie einen Preis-Volumen-Split wünschen. Behalten Sie die Kontonamen in beiden Dateien identisch bei.

Hochladen von Budget- und Ist-Dateien für eine Abweichungsanalyse in Powerdrill Bloom

Schritt 2: Fragen Sie nach den Abweichungen und Treibern in natürlicher Sprache

Beschreiben Sie das gewünschte Ergebnis. Zum Beispiel: „Vergleiche die Ist-Werte des 3. Quartals mit dem Budget nach Positionen. Markiere Zeilen über 5 % und 2.000 $ und teile die Umsatzlücke in Preis und Volumen auf.“

Die Tool-Seite listet „Abweichung nach Position mit angewandten Wesentlichkeitsschwellen“ als eine Ansicht auf. Sie listet auch eine Reforecast-Arbeitsmappe mit „Originalbudget, YTD-Ist-Werten und revidiertem Jahresende“ auf.

Schritt 3: Prüfen Sie die Zahlen und exportieren Sie den Bericht

Vergleichen Sie die Gesamtsummen mit Ihrem eigenen Blatt, bevor Sie etwas teilen. Exportieren Sie dann das Ergebnis. Die Seite Budget Analysis listet ein „Abweichungs-Dashboard mit hervorgehobenen wesentlichen Zeilen und beigefügten Treibern“ auf.

Für eine fortlaufende Ansicht deckt der AI Finance Dashboard Builder GuV, Cash, Working Capital und Forecast ab. Testen Sie Powerdrill Bloom mit dem Budget und den Ist-Werten des letzten Quartals.

Prüfen der Abweichungssummen vor dem Exportieren des Berichts

Häufige Fehler, die es zu vermeiden gilt

Einige Fehler tauchen in fast jeder ersten Abweichungsanalyse auf.

  • Vorzeichen verwechseln. Entscheiden Sie einmal, ob die Abweichung Ist minus Budget ist, und verwenden Sie dies überall.
  • Jede positive Lücke als gut bezeichnen. Höhere Kosten sind unvorteilhaft, auch wenn die Zahl positiv ist.
  • Prozentsätze addieren. Berechnen Sie Gesamtprozentsätze aus den Gesamtbeträgen neu.
  • Unterschiedliche Zeiträume vergleichen. Ein Year-to-Date-Ist-Wert gegen ein Monatsbudget ergibt eine falsche Lücke.
  • Timing ignorieren. Eine verspätete Rechnung kann zwei entgegengesetzte Lücken in zwei Monaten erzeugen.
  • Alles erklären. Notizen zu kleinen Lücken verdecken diejenigen, auf die es ankommt.
  • Zahlen von Hand tippen. Fügen Sie Ist-Werte aus dem Export ein oder verknüpfen Sie diese, damit ein Tippfehler keine falsche Lücke erzeugt.
  • Die Gesamtprüfung überspringen. Zeilenabweichungen sollten sich zur Gesamtabweichung addieren. Wenn nicht, fehlt eine Zeile oder wurde doppelt zugeordnet.

Häufig gestellte Fragen

Wie lautet die Formel für Abweichungen in Excel?

Für die Budgetabweichung verwenden Sie Ist minus Budget, zum Beispiel =C2-B2. Für den Prozentsatz verwenden Sie =IFERROR((C2-B2)/ABS(B2),"") und formatieren die Zelle als Prozentsatz.

Woher weiß ich, ob eine Abweichung vorteilhaft ist?

Das hängt vom Zeilentyp ab. Beim Umsatz ist ein Ist-Wert über Budget vorteilhaft. Bei Kosten ist ein Ist-Wert unter Budget vorteilhaft. Eine Typ-Spalte und eine IF-Formel können jede Zeile kennzeichnen.

Was ist eine wesentliche Abweichung?

Es ist eine Lücke, die groß genug ist, um erklärt zu werden. Teams legen ihre eigene Regel fest, wie zum Beispiel über 5 % und über 2.000 $. Zeilen, die beide Kriterien erfüllen, erhalten eine schriftliche Notiz.

Welches Diagramm eignet sich am besten für eine Abweichungsanalyse?

Ein Wasserfalldiagramm, auch Bridge-Chart genannt, zeigt, wie jede Zeile die Gesamtsumme vom Budget zum Ist-Wert bewegt. Excel bietet es unter Einfügen > Wasserfall einfügen an.

Kann Copilot in Excel eine Abweichungsanalyse durchführen?

Microsoft listet einen Abweichungsanalyse-Skill für Copilot in Excel auf. Er vergleicht Ist-Werte mit Budgets, Forecasts oder Vorperioden und entwirft Erklärungen zur Überprüfung. Er erfordert eine Copilot-Lizenz.

Quellen: Microsoft Support, Integrierte Skills mit Copilot in Excel verwenden · Microsoft Support, Ein Wasserfalldiagramm erstellen · Microsoft Support, Bedingte Formatierung zum Hervorheben von Informationen in Excel verwenden.