Prognosemethoden und Formeln in Excel
Dieser Leitfaden erläutert grundlegende Prognosemethoden, die problemlos in Microsoft Excel-Tabellenkalkulationen angewendet werden können. Dieser Leitfaden richtet sich an Manager und Führungskräfte, die den Kundenbedarf vorhersehen müssen. Die Theorie wird mit Microsoft Excel veranschaulicht. Fortgeschrittene Hinweise stehen Softwareentwicklern zur Verfügung, die die Theorie in einer benutzerdefinierten Anwendung reproduzieren möchten.
Vorteile der Prognose
Prognosen können Ihnen helfen, die richtigen Entscheidungen zu treffen und Geld zu verdienen/sparen. Hier ist ein Beispiel.
- Optimieren Sie Ihre Bestände
Zeit ist Geld. Platz ist Geld. Daher möchten Sie alle Mittel nutzen, um Ihre Bestände zu reduzieren - ohne natürlich irgendwelche Fehlbestände zu erleben.
Wie? Durch Prognosen!
Wie man es sich leicht macht: Beschriftungen, Kommentare, Dateinamen
Im Laufe der Zeit, wenn sich Ihre Daten ansammeln, werden Sie immer wahrscheinlicher verwirrt und machen Fehler. Die Lösung? Seien Sie nicht unordentlich: Eine gute Verwendung von Beschriftungen, Kommentaren und die korrekte Benennung Ihrer Dateien kann Ihnen viel Ärger ersparen.
- Beschriften Sie immer Ihre Spalten. Verwenden Sie die erste Zeile jeder Spalte, um die enthaltenen Daten zu beschreiben.
- Unterschiedliche Daten, unterschiedliche Spalten. Geben Sie nicht verschiedene Zahlen (z.B. Ihre Kosten und Ihren Umsatz) in dieselbe Spalte ein. Es ist unglaublich wahrscheinlich, dass Sie durcheinanderkommen, und es macht Berechnungen und Datenverarbeitung schwieriger.
- Geben Sie jeder Datei einen klar verständlichen Namen. Es erfordert wenig Aufwand und beschleunigt die Dinge. Sie sind visuell leicht zu identifizieren und mit der Windows-Suchfunktion leichter zu finden.
- Verwenden Sie Kommentare.
Selbst wenn Sie normalerweise nicht mit einer großen Menge an Daten arbeiten, ist es immer noch sehr einfach, durcheinander zu kommen. Dies gilt insbesondere, wenn Sie zu Daten zurückkehren, die Sie lange zuvor erstellt haben. Excel hat eine großartige Lösung anzubieten: Kommentare.
Klicken Sie einfach mit der rechten Maustaste auf die Zelle, zu der Sie einen Kommentar hinzufügen möchten, und wählen Sie dann “Kommentar einfügen”.
Sie können sie verwenden:
- um den Inhalt einer Zelle zu erklären (z.B. Einheitskosten gemäß den Schätzungen von Herrn Doe)
- um zukünftige Benutzer des Blatts vorzuwarnen (z.B. Ich habe Zweifel an dieser Berechnung…)
Erste Schritte: ein einfaches Prognosebeispiel mit Trendlinien
Anzeigen Ihrer Daten
Lassen Sie uns jetzt unsere erste Prognose durchführen. In diesem Teil verwenden wir diese Datei: Beispiel1.xls. Um die Schritte selbst zu wiederholen, können Sie die Datei herunterladen. Diese Daten dienen nur als Beispiel.
Unsere Daten: In der ersten Spalte befinden sich Daten zu den Einheitskosten ähnlicher Produkte (die Einheitskosten spiegeln die Qualität des Produkts wider). In der zweiten Spalte befinden sich Daten darüber, wie viel verkauft wurde.
Was wir wissen möchten: Wenn wir ein weiteres Produkt verkaufen, das einer Qualität von $150/Einheit entspricht, wie viele Einheiten können wir erwarten zu verkaufen?
Wie wir dorthin gelangen: Hier ist es ziemlich einfach. Wir möchten eine einfache mathematische Beziehung zwischen Einheitskosten und Verkäufen finden und diese Beziehung dann für unsere Prognose verwenden.
Zuerst ist es immer nützlich, in Excel ein Diagramm zu erstellen, um sich die Daten anzusehen. Ihre Augen sind ausgezeichnete Werkzeuge, die Ihnen helfen können, Trends in wenigen Sekunden zu erkennen.
Um dies zu tun, wählen wir unsere Daten aus, verwenden dann Einfügen > Diagramm und wählen die Option XY(Punkte). Wir möchten die Verkäufe als Funktion der Qualität schätzen, daher setzen wir die Einheitskosten auf die horizontale und die Verkäufe auf die vertikale Achse.
Jetzt halten wir ein paar Sekunden inne und schauen uns genau an, was wir sehen: Die Beziehung scheint zu steigen und linear zu sein.
Um eine Vorstellung von der genauen Form der Beziehung zu bekommen, klicken wir mit der rechten Maustaste auf das Diagramm und wählen die Option “Trendlinie”.
Jetzt müssen wir die Beziehung auswählen, die zu unseren Daten zu “passen” scheint (d.h. sie am besten beschreibt). Auch hier verwenden wir unsere Augen: In diesem Fall liegen die Punkte fast auf einer geraden Linie, daher verwenden wir die Einstellung “linear”. Später werden wir andere - komplexere, aber oft realistischere - Einstellungen wie “exponential” verwenden.
Unsere Trendlinie wird nun auf dem Diagramm angezeigt. Ein weiterer Rechtsklick ermöglicht es uns, die genaue Form der Beziehung anzuzeigen: y = 102,4x - 191,64.
Verstehen: Anzahl der verkauften Einheiten = 102,4 mal die Einheitskosten - 191,64.
Also, wenn wir uns entscheiden, zu Einheitskosten von $150 zu produzieren, können wir erwarten, 102,4*150 - 191,64 = 15168 Einheiten zu verkaufen.
Wir haben gerade unsere erste Prognose erfolgreich abgeschlossen.
Seien Sie jedoch vorsichtig: Die Software ist immer in der Lage, eine Beziehung zwischen den beiden Spalten zu finden, auch wenn diese Beziehung in Wirklichkeit sehr schwach ist! Daher ist eine Überprüfung der Robustheit erforderlich. Hier ist, wie Sie dies schnell tun können:
- Zuerst werfen Sie immer einen Blick auf das Diagramm. Wenn Sie die Punkte nahe an der Trendlinie finden, wie im obigen Beispiel, besteht eine gute Chance, dass die Beziehung robust ist. Wenn die Punkte jedoch scheinbar zufällig angeordnet sind und im Allgemeinen weit von der Trendlinie entfernt sind, sollten Sie vorsichtig sein: Die Korrelation ist schwach und die geschätzte Beziehung sollte nicht blind vertraut werden.
- Nachdem Sie sich das Diagramm angesehen haben, können Sie die KORREL-Funktion verwenden. In unserem Beispiel würde die Funktion lauten: KORREL(A2:A83,B2:B83). Wenn das Ergebnis nahe bei 0 liegt, ist die Korrelation gering und die Schlussfolgerung lautet: Es gibt einfach keinen echten Trend. Wenn es nahe bei 1 liegt, ist die Korrelation stark. Letzteres ist hilfreich, da es die Erklärungskraft der gefundenen Beziehung erhöht.
Es gibt subtilere Möglichkeiten, um sicherzustellen, dass die Korrelation hoch ist; darauf werden wir später zurückkommen.
Natürlich können diese letzten Schritte automatisiert werden: Sie müssen die Beziehung nicht notieren und Ihren Taschenrechner zur Berechnung verwenden. Sie benötigen das Analysis Toolpak!
Prognose mit dem Analysis Toolpak
Bevor Sie fortfahren, sollten Sie überprüfen, ob das Excel ATP (Analysis Toolpak) installiert ist. Weitere Informationen finden Sie im Abschnitt Installation des Analysis Toolpak.
Leider sind solche perfekten Verkaufsdaten mit einer so schönen, einfachen linearen Beziehung im wirklichen Leben recht selten. Werfen wir einen Blick darauf, was Excel für kompliziertere Situationen mit komplizierteren Daten zu bieten hat.
Weiterführende Informationen: das Beispiel der exponentiellen Anpassung
Wie Sie sich vorstellen können, ist ein solches lineares Modell Ihrer Daten nicht immer wahrscheinlich. Tatsächlich gibt es viele Gründe zu glauben, dass es einem exponentiellen Modell folgen sollte. Viele Verhaltensweisen in der Wirtschaft werden durch exponentielle Gleichungen gesteuert (z.B. Zinseszinsberechnungen sind ein klassisches Beispiel).
So führen Sie eine exponentielle Anpassung durch:
- Werfen Sie einen Blick auf Ihre Daten. Zeichnen Sie einen einfachen Graphen und betrachten Sie ihn einfach. Wenn sie einer exponentiellen Entwicklung folgen, sollten sie so aussehen:
Dies ist der perfekte Fall. Natürlich werden die Daten nie genau so aussehen. Aber wenn die Punkte dieser Verteilung ungefähr folgen, sollte Sie das dazu ermutigen, eine exponentielle Anpassung in Betracht zu ziehen.
Wie im vorherigen Beispiel können Sie immer einen Diagramm Ihrer Daten erstellen, eine Trendlinie anfordern und anstelle von linear “exponentiell” wählen. Sammeln Sie dann die angezeigte Gleichung wie gewohnt.
- Glücklicherweise können Sie dies auch direkt mit dem Analysis Toolpak tun: Geben Sie alle Ihre Daten in ein leeres Excel-Blatt ein und gehen Sie zu Tools => Data Analysis
Installation des Analysis Toolpak (ATP)
Das ATP ist ein Add-In, das mit Microsoft Excel geliefert wird, aber nicht immer standardmäßig installiert ist. Um es zu installieren, können Sie wie folgt vorgehen:
- Stellen Sie sicher, dass Sie Ihre Office-CD dabei haben. Excel kann verlangen, dass Sie die CD einlegen, um die ATP-Dateien zu installieren.
- Öffnen Sie ein Excel-Blatt und gehen Sie zum Menü “Tools” und wählen Sie dann “Add-Ins” aus. Aktivieren Sie das erste Kästchen im Fenster mit der Bezeichnung “2.Analysis Toolpak”.
- Legen Sie Ihre Office-CD ein, wenn die Software Sie dazu auffordert.
- Das ist alles! Beachten Sie, dass Ihr “Tools”-Menü jetzt viele weitere Funktionen enthält, einschließlich einer Option “Data Analysis”. Diese werden wir am häufigsten verwenden.
Verwendung des Analysis Toolpak (ATP)
… in einer linearen Umgebung
Nun kommen wir zu unserem linearen Beispiel zurück. Wenn Ihre Daten “gut” aussehen (siehe obige Abbildung), können Sie das ATP verwenden, um eine direkte Schätzung der Funktionsform zu erhalten, ohne den “Trendlinien”-Prozess durchlaufen zu müssen.
Öffnen Sie Ihr Datenblatt, öffnen Sie dann das Menü “Tools” und wählen Sie “Data Analysis” aus. Es öffnet sich ein Fenster, das Sie fragt, welche Art von Analyse Sie durchführen möchten. Wählen Sie “Regression” für lineare Einstellungen.
Jetzt müssen Sie Excel zwei Argumente geben: einen “Y-Bereich” und einen “X-Bereich”. Der Y-Bereich gibt an, was Sie schätzen möchten (z. B. Ihre Verkäufe), und der X-Bereich enthält die Daten, die Ihre Verkäufe erklären können (hier Ihre Stückkosten). In unserem Beispiel (siehe example1.xls) befinden sich unsere Verkaufsdaten in Spalte B, von Zeile 3 bis Zeile 90. Geben Sie also “$B$3:$B$90” als Y-Bereich und “$A$3:$A$90” als X-Bereich ein. Wenn Sie fertig sind, klicken Sie auf “OK”.
Es erscheint ein neues Blatt mit den “Regressionsergebnissen”.
Das wichtigste Ergebnis befindet sich in der Spalte “Koeffizienten” am unteren Rand des Blatts. Der Achsenabschnitt ist die Konstante und der Koeffizient der “X-Variablen” ist der Koeffizient von X (hier Ihre Stückkosten). Daher erhalten wir die gleiche Gleichung wie bei Verwendung der “Trendlinien”-Funktion. Verkäufe = Achsenabschnitt + X-Koeffizient * StückkostenVerkäufe = -126 + 100 * Stückkosten
Dieses Blatt enthält auch eine nützliche Zahl, die Ihnen Informationen darüber gibt, wie gut Ihre Schätzung ist: das “R-Quadrat”. Wenn es nahe bei 1 liegt, ist Ihre Schätzung gut, was bedeutet, dass die von Ihnen gefundene Gleichung eine ziemlich gute Darstellung Ihrer Daten ist. Wenn es nahe bei 0 liegt, ist die Schätzung nicht gut und Sie sollten wahrscheinlich eine andere Art der Anpassung ausprobieren (siehe exponentielle Anpassung unten).
Diese Methode ist wahrscheinlich schneller als die “Trendlinien”-Techniken. Sie ist jedoch etwas technischer und viel weniger visuell. Wenn Sie also nicht den Aufwand betreiben möchten, Ihre Daten zu plotten und zu betrachten, stellen Sie sicher, dass Sie zumindest den Wert des “R-Quadrats” überprüfen.
… mit exponentieller Anpassung
Wenn die lineare Schätzung nicht gut funktioniert (z. B. wenn Sie ein niedriges R-Quadrat, d. h. 0,1, erhalten), möchten Sie möglicherweise eine exponentielle Anpassung verwenden.
Starten Sie das Analysis Toolpak wie gewohnt: Öffnen Sie Ihr Datenblatt, öffnen Sie dann das Menü “Tools” und wählen Sie “Data Analysis” aus. Es öffnet sich ein Fenster, das Sie fragt, welche Art von Analyse Sie durchführen möchten.
In unserer exponentiellen Einstellung möchten wir “exponential” auswählen.
Beachten Sie, dass Excel Sie nur nach einem Eingabebereich fragt. Wählen Sie die Spalte aus, die die Daten enthält, die Sie prognostizieren möchten (z. B. Stückkosten), und wählen Sie einen “Glättungsfaktor” aus.
Wie weiß ich, welches Modell ich wählen soll?
Beachten Sie, dass Sie nicht jedes Schätzverfahren ausprobieren müssen, um dasjenige zu finden, das am besten für Sie funktioniert. Dies kann nur durch Automatisierung erreicht werden, da eine große Anzahl von Methoden zur Verfügung steht. Wenn Sie möchten, dass alle Modelle anhand Ihrer Daten benchmarkt werden, können Sie sie an Lokad senden. Wir verfügen über ein leistungsstarkes Computersystem, das alle Modelle “testet” und nur diejenigen auswählt, die am besten mit den Daten Ihres Unternehmens funktionieren.