Methoden und Formeln zum Prognostizieren in Excel
Dieser Leitfaden erklärt grundlegende Prognosemethoden, die direkt in Microsoft Excel-Tabellen angewendet werden können. Dieser Leitfaden richtet sich an Manager und Führungskräfte, die die Kundennachfrage vorhersagen müssen. Die Theorie wird mit Microsoft Excel veranschaulicht. Ausführliche Hinweise sind für Softwareentwickler verfügbar, die die Theorie in eine benutzerdefinierte Anwendung übernehmen möchten.
Vorteile der Prognose
Prognosen können Ihnen helfen, die richtigen Entscheidungen zu treffen und Geld zu verdienen oder zu sparen. Hier ist ein Beispiel.
- Optimieren Sie Ihre Bestände
Zeit ist Geld. Platz ist Geld. Daher sollten Sie alle Mittel, die Ihnen zur Verfügung stehen, nutzen, um Ihre Lagerbestände zu reduzieren – ohne natürlich Fehlbestände zu erleiden.
Wie? Durch Prognosen!
Wie man es einfach macht: Beschriftungen, Kommentare, Dateinamen
Mit der Zeit, wenn Ihre Daten anwachsen, werden Sie immer eher verwirrt und machen Fehler. Die Lösung? Vermeiden Sie Unordnung: Eine sinnvolle Verwendung von Beschriftungen, Kommentaren und einer korrekten Dateibenennung kann Ihnen viel Mühe ersparen.
- Beschriften Sie immer Ihre Spalten. Verwenden Sie die erste Zeile jeder Spalte, um die enthaltenen Daten zu beschreiben.
- Unterschiedliche Daten, unterschiedliche Spalten. Platzieren Sie nicht verschiedene Zahlen (zum Beispiel Ihre Kosten und Ihre Verkäufe) in derselben Spalte. Es ist sehr wahrscheinlich, dass Sie sich verwechseln, und es macht Berechnungen und Datenverarbeitung schwieriger.
- Geben Sie jeder Datei einen klar verständlichen Namen. Es erfordert wenig Aufwand und beschleunigt die Arbeit. So werden sie leichter visuell identifizierbar und mithilfe der Windows-Suchfunktion einfacher zu finden.
- Verwenden Sie Kommentare.
Auch wenn Sie normalerweise nicht mit großen Datenmengen arbeiten, ist es sehr leicht, den Überblick zu verlieren. Dies gilt besonders, wenn Sie auf Daten zurückkommen, die Sie vor langer Zeit erstellt haben. Excel bietet eine großartige Lösung: Kommentare.
Klicken Sie einfach mit der rechten Maustaste auf die Zelle, die Sie kommentieren 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. Stückkosten laut Mr Does Schätzungen)
- um zukünftigen Benutzern des Blattes Warnungen zu hinterlassen (z. B. Ich habe einen Zweifel an dieser Berechnung…)
Erste Schritte: ein einfaches Prognosebeispiel mit Trendlinien
Ihre Daten ansehen
Lassen Sie uns nun unsere erste Prognose erstellen. In diesem Abschnitt verwenden wir diese Datei: Example1.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 Stückkosten ähnlicher Produkte (die Stückkosten spiegeln die Qualität des Produkts wider). In der zweiten Spalte finden Sie Daten dazu, wie viel verkauft wurde.
Was wir wissen möchten: Wenn wir ein weiteres Produkt verkaufen, dessen Qualität einem Stückpreis von $150 entspricht, wie viele Einheiten können wir erwarten zu verkaufen?
Wie wir dorthin gelangen: Hier ist es ganz einfach. Wir möchten eine einfache mathematische Beziehung zwischen den Stückkosten und den Verkäufen finden und diese Beziehung dann nutzen, um unsere Prognose zu erstellen.
Zunächst ist es immer nützlich, in Excel ein Diagramm zu erstellen, um einen Blick auf die Daten zu werfen. Ihre Augen sind hervorragende Werkzeuge, die Ihnen helfen können, Trends in wenigen Sekunden zu erkennen.
Dazu wählen wir unsere Daten aus, gehen dann auf Einfügen > Diagramm und wählen die XY(Streudiagramm)-Option. Wir möchten die Verkäufe in Abhängigkeit von der Qualität schätzen, daher setzen wir die Stückkosten auf die horizontale und die Verkäufe auf die vertikale Achse.
Nun halten wir kurz inne und betrachten, was wir sehen: Die Beziehung scheint steigend 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“.
Nun müssen wir die Beziehung auswählen, die am besten zu unseren Daten zu passen scheint. Auch hier verlassen wir uns auf unser Auge: In diesem Fall liegen die Punkte nahezu in einer geraden Linie, also verwenden wir die Einstellung „linear“. Später werden wir andere – komplexere, aber oft realistischere – Einstellungen wie „exponentiell“ verwenden.
Unsere Trendlinie wird nun im Diagramm angezeigt. Ein weiterer Rechtsklick ermöglicht es uns, die genaue Form der Beziehung anzuzeigen: y = 102.4x - 191.64.
Verstehen Sie: Anzahl der verkauften Einheiten = 102.4 mal die Stückkosten - 191.64.
Wenn wir uns also entscheiden, zu Stückkosten 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.
Aber seien Sie vorsichtig: Die Software findet immer eine Beziehung zwischen den beiden Spalten, auch wenn diese in Wirklichkeit sehr schwach ist! Daher ist eine Überprüfung der Robustheit erforderlich. So machen Sie dies schnell:
- Schauen Sie sich zuerst immer das Diagramm an. Wenn Sie feststellen, dass die Punkte nahe an der Trendlinie liegen, wie in unserem obigen Beispiel, gibt es eine gute Chance, dass die Beziehung robust ist. Sollten die Punkte jedoch fast zufällig verteilt und im Allgemeinen weit von der Trendlinie entfernt sein, sollten Sie vorsichtig sein: Die Korrelation ist schwach, und der geschätzten Beziehung sollte nicht blind vertraut werden.
- Nachdem Sie das Diagramm betrachtet haben, können Sie die CORREL-Funktion verwenden. In unserem Beispiel würde die Funktion lauten: CORREL(A2:A83,B2:B83). Ist das Ergebnis nahe 0, ist die Korrelation gering und die Schlussfolgerung lautet: Es gibt schlicht keinen wirklichen Trend. Liegt er nahe 1, ist die Korrelation stark. Letzteres ist hilfreich, da es die Erklärungskraft der von Ihnen gefundenen Beziehung erhöht.
Es gibt subtilere Methoden, um sicherzustellen, dass die Korrelation hoch ist; darauf werden wir später noch 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!
Prognostizieren 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 Zur Installation des Analysis Toolpak.
Leider sind so perfekte Verkaufsdaten mit einer so schönen, einfachen linearen Beziehung im realen Leben ziemlich selten. Lassen Sie uns ansehen, was Excel für kompliziertere Situationen mit komplexeren Daten zu bieten hat.
Weiterführend: das Beispiel der exponentiellen Anpassung
Wie Sie sich vorstellen können, ist ein lineares Modell Ihrer Daten nicht immer wahrscheinlich. Tatsächlich gibt es viele Gründe zu der Annahme, dass sie einem exponentiellen Modell folgen sollten. Viele Prozesse in der Wirtschaft werden durch exponentielle Gleichungen gesteuert (zum Beispiel sind Zinseszinsberechnungen ein klassisches Beispiel).
So führen Sie ein exponentielles Anpassen durch:
- Schauen Sie sich Ihre Daten an. Zeichnen Sie ein einfaches Diagramm und betrachten Sie es. Wenn sie einem exponentiellen Verlauf folgen, sollten sie so aussehen:
Dies ist der perfekte Fall. Natürlich werden die Daten nie genau so aussehen. Aber wenn die Punkte annähernd dieser Verteilung folgen, sollte Sie das dazu ermutigen, ein exponentielles Anpassen in Betracht zu ziehen.
Wie im vorherigen Beispiel können Sie immer ein Diagramm Ihrer Daten erstellen, eine Trendlinie einfügen und statt der linearen Option die « exponential » auswählen. Anschließend entnehmen Sie die angezeigte Gleichung, wie üblich.
- Glücklicherweise können Sie all dies auch direkt mit dem Analysis Toolpak durchführen: Geben Sie alle Ihre Daten in ein leeres Excel-Blatt ein und gehen Sie zu Extras => Datenanalyse
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 zur Hand haben. Excel könnte verlangen, dass Sie die CD einlegen, um die ATP-Dateien zu installieren.
- Öffnen Sie ein Excel-Blatt, gehen Sie zum Menü « Extras » und wählen Sie dann « Add-Ins ». Aktivieren Sie das erste Kästchen im Fenster, beschriftet mit « 2.Analysis Toolpak ».
- Legen Sie Ihre Office-CD ein, falls die Software dies verlangt.
- Das war’s! Beachten Sie, dass Ihr « Tools »-Menü jetzt viele weitere Funktionen enthält, darunter eine Option « Data Analysis ». Diese werden wir am häufigsten verwenden.
Verwendung des Analysis Toolpak (ATP)
… im linearen Fall
Kehren wir nun zu unserem linearen Beispiel zurück. Wenn Ihre Daten « gut aussehen » (siehe obenstehende Abbildung), können Sie das ATP verwenden, um eine direkte Abschätzung der Funktionsform zu erhalten, ohne den « trendline »-Prozess durchlaufen zu müssen.
Öffnen Sie Ihr Datenblatt, öffnen Sie dann das « Tools »-Menü und wählen Sie « Data Analysis ». Ein Fenster erscheint, in dem Sie gefragt werden, welche Art von Analyse Sie durchführen möchten. Wählen Sie für lineare Einstellungen « regression » aus.
Jetzt müssen Sie Excel zwei Argumente übergeben: einen « Y-Bereich » und einen « X-Bereich ». Der Y-Bereich gibt an, was Sie schätzen möchten (d. h. Ihre Verkäufe), und der X-Bereich enthält die Daten, von denen Sie glauben, dass sie 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, daher müssen Sie « $B$3:$B$90 » als Y-Bereich und «$A$3:$A$90 » als X-Bereich angeben. Wenn Sie fertig sind, klicken Sie auf « ok ».
Ein neues Blatt erscheint, das die « Regressionsergebnisse » enthält.
Das wichtigste Ergebnis befindet sich in der Spalte « Coefficients » am unteren Ende des Blatts. Der Achsenabschnitt ist die Konstante und der Koeffizient der « X-Variable » ist der Koeffizient von X (hier Ihre Stückkosten). Somit erhalten wir dieselbe Gleichung, die wir mit der « trendline »-Funktion gefunden haben. Verkäufe = Achsenabschnitt + X-Koeffizient * Stückkosten, d.h. Verkäufe = -126 + 100 * Stückkosten
Dieses Blatt enthält außerdem eine nützliche Kennzahl, die Ihnen Auskunft darüber gibt, wie gut Ihre Schätzung ist: das « R Square ». Liegt dieser Wert nahe bei 1, ist Ihre Schätzung gut, was bedeutet, dass die gefundene Gleichung Ihre Daten recht gut repräsentiert. Liegt er hingegen nahe 0, ist die Schätzung nicht gut, und Sie sollten vermutlich eine andere Anpassungsmethode ausprobieren (siehe unten zur exponentiellen Anpassung).
Diese Methode ist wahrscheinlich schneller als die « trendline »-Techniken. Allerdings ist sie etwas technischer und weitaus weniger anschaulich. Wenn Sie also nicht den Aufwand betreiben möchten, Ihre Daten zu plotten und visuell zu überprüfen, sollten Sie zumindest den « R square »-Wert prüfen.
… unter Verwendung exponentieller Anpassung
Wenn die lineare Schätzung nicht gut funktioniert (zum Beispiel, wenn Sie ein niedriges R-Quadrat, also 0.1, erhalten), möchten Sie möglicherweise die exponentielle Anpassung verwenden.
Starten Sie das Analysis Toolpak wie gewohnt: Öffnen Sie Ihr Datenblatt, dann das « Tools »-Menü und wählen Sie « Data Analysis ». Ein Fenster erscheint, in dem Sie gefragt werden, welche Art von Analyse Sie durchführen möchten.
In unserem exponentiellen Setting wählen Sie « exponential » aus.
Beachten Sie, dass Excel nur einen Eingabebereich abfragt. Wählen Sie die Spalte, die die zu prognostizierenden Daten enthält (d. h. die Stückkosten), und wählen Sie einen „Glättungsfaktor“.
Wie weiß ich, welches Modell ich wählen soll?
Beachten Sie, dass Sie nicht jede Schätzmethode ausprobieren müssen, um diejenige zu finden, die für Sie am besten funktioniert. Dies kann nur durch Automatisierung erreicht werden, da eine so große Zahl von Methoden zur Verfügung steht. Wenn Sie möchten, dass alle Modelle anhand Ihrer Daten bewertet werden, können Sie in Erwägung ziehen, diese an Lokad zu 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.