Die 5 besten Tipps und Tricks für Planung, Budgetierung und Prognosen in Power BI und Excel

In diesem Blog berichte ich über Erfahrungen, Tipps und Tricks bei der Implementierung von Planungs-, Prognose- und Budgetierungssystemen mit Power BI und modernen Excel-Funktionen.

Tipp 1: Es steht und fällt mit dem Datenmodell

Wie bei jedem Aspekt des Leistungsmanagements in Unternehmen ist das Datenmodell von entscheidender Bedeutung. Ein gut konzipiertes Datenmodell führt zu guter Leistung, einfach einzurichtenden Berichten und Dateneingabeformularen und hoher Benutzerakzeptanz. Umgekehrt können Sie mit einem schlechten Modell keine Anstrengungen auf der Logik- und Front-End-Seite machen. Aus der Sicht der Modellierung haben wir noch keine Planungsanforderung gefunden, bei der ein gut konzipiertes Sternschema nicht die beste Wahl ist. Ein Sternschema besteht aus einer Faktentabelle mit den Transaktionen und Dimensionen, die diese Transaktionen definieren, wie in Abbildung A dargestellt.

In der Planung haben Sie entweder eine einzige Faktentabelle mit Transaktionen aus allen Szenarien oder mehrere Faktentabellen, die verschiedene Szenariodaten enthalten, z. B. eine für Istwerte, eine für Budget usw. Bei den Abmessungen haben Sie fast immer die folgenden Maße in jedem Modell:

Einfaches Sternschema für Finanzplanungsanforderungen in Power BI

Zeit

der die Zeitangaben mit der niedrigsten Granularität sowie Periodenhierarchien, z. B. Wochen, Geschäftsjahre usw., enthält.

Szenario

die die verschiedenen Szenariotypen enthält, z. B. Ist, Budget, Prognose usw.

Konto/Maßnahme

Dazu gehört das Element, das Sie planen oder messen wollen, z. B. Einnahmen, Ausgaben, Mengen, KPIs usw.

Organisatorische Einheit

Hier werden typischerweise Konzernunternehmen, Tochtergesellschaften und Kostenstellen erfasst

Je nach den spezifischen Anforderungen werden Sie weitere Details wie Produkt, Vertriebsteams, Mitarbeiter und vieles mehr haben.

Ein umfassendes Planungsmodell kann sehr komplex werden, wie in Abbildung B dargestellt, aber Lösungen wie Acterys Apps und Power BI Sync für beliebige Daten in Power BI können dabei helfen, dieses automatisch auf der Grundlage der wichtigsten Buchhaltungs- und SaaS-Systeme zu erstellen.

Tipp 2: Abweichungsberechnungen und Visualisierung

Die Abweichungsberechnungen und die Visualisierung sind relativ einfach, wenn der obige Modellierungsansatz mit einer einzigen Szenariodimension für alle Faktentabellen verwendet wird. Sie können die Szenario-Dimension direkt für Szenario-Vergleiche verwenden oder eine einfache Szenario-Filterkennzahl in der Struktur BU = CALCULATE(PlanningFactTable'[Measure],’ScenarioDimension'[Name]=“Budget“) für alle zu vergleichenden Szenarien erstellen.

Diese explizit definierten Szenarien ermöglichen es Ihnen dann auch, Ihre Abweichungsberechnungen einzurichten, in denen Sie die Ist- mit der Zielerreichung vergleichen.

Für finanzielle Vergleiche in der Gewinn- und Verlustrechnung empfehle ich Ihnen, in Ihrem Modell eine Vorzeichenkonvention zu verwenden, die Einnahmen und Ausgaben nach Vorzeichen unterscheidet. Dies kann zu Diskussionen mit Buchhaltern führen, ist aber meiner Meinung nach die klarste und einfachste Art, mit Abweichungen umzugehen.

Sie können dann die folgenden einfachen Berechnungen verwenden, die mit sehr geringem Aufwand die richtige Abweichung (absolut und relativ) ergeben:

Absolute Varianz Δ

VAR Δ = [AC]-[BU]

VAR % =

WENN (ODER ( [AC] <> LEER (), [BU] )

<> BLANK (),

DIVIDE([VAR Δ], ABS([BU])))

(die Verwendung der Funktion ABS() stellt sicher, dass im Falle von zwei Negativwerten / Ausgaben die Abweichung mit dem richtigen Vorzeichen angezeigt wird)

Das Acterys Reporting Visual berechnet diese Abweichungen automatisch und fügt außerdem Visualisierungen hinzu, die die IBCS-Prinzipien widerspiegeln.

Acterys Reporting Power BI Visual mit Abweichungsberechnung

Tipp 3: Varianzaufhellung mit benutzerdefinierten Werkzeugspitzen

Um den Benutzern zu helfen, die Haupttreiber für Abweichungen schnell zu identifizieren, verwende ich gerne benutzerdefinierte QuickInfos in Power BI, die die mit der Abweichung verbundenen Transaktionen auflisten, nach Relevanz sortiert und mit einer geeigneten Visualisierung kombiniert. Entweder in einer Tabelle:

Power BI Custom Tooltip mit sortierten Abweichungsdetails

Oder Sie verwenden eine Visualisierung mit kleinen Multiplikatoren wie Acterys Variance:

Acterys Reporting mit grafischem Tooltip

Tipp 4: Prognoserechnungen und Visualisierung

Eine häufige Anforderung in Planungsprozessen sind Prognosen, bei denen Sie die revidierten Ziele für zukünftige Perioden auf der Grundlage von Ist-Daten oder neuen Entwicklungen berechnen wollen. In der Regel handelt es sich dabei um einen „Stichtag“, bis zu dem Sie die aktuellen Ist-Ergebnisse und ein Prognoseszenario (häufig ein angepasstes Budget) bis zum Enddatum der Periode (Haushaltsjahr/Periode) verwenden möchten.

Als Stichtag können Sie entweder das letzte Datum der aktuellen Daten verwenden (wie in diesem Beitrag gut beschrieben): Anzeigen von Istwerten und Prognosen im selben Diagramm mit Power BI – SQLBI) oder eine Parametertabelle (z.B. Power BI Edit Table oder für mehr Komfort und Datumsänderungen ohne Power BI Desktop: eine Acterys-Dimension), in der die Benutzer das Stichtagdatum eingeben oder aus einer Liste auswählen können.

Um zu den prognostizierten Gesamtergebnissen zu gelangen, können Sie die „Periode bis Datum“ Ihrer Ist-Werte plus die Summe für den Rest des Prognosezeitraums berechnen.

Die erforderlichen Berechnungen werden in etwa so aussehen:

Tatsächliche Werte bis zum Stichtag („Prognoseparameter“[Closing Date]):

AC PTD = CALCULATE(If(max(Datum[Date])<=max(‚Vorhersageparameter'[Closing Date]),[AC],0))

Der Haushalt nach dem Stichtag:

Ausstehende BU = CALCULATE(If(max(Datum[Date])>max(‚Vorhersageparameter'[Closing Date]),[BU],0))

Und schließlich die kombinierte Prognose:

Vorhersage = CALCULATE(CALCULATE(SUMX(GL,[AC PTD]+[BU]),KEEPFILTERS(Scenario)),all(Scenario))

KEEPFILTERS ist nur erforderlich, wenn Sie eine einzelne Faktentabelle mit mehreren Szenarien haben. Die Verwendung von SUMX() stellt sicher, dass die Werte auf aggregierter Ebene korrekt sind.

Um eine Vorhersage für das laufende Jahr anzuzeigen, können Sie eine YTD-Berechnung wie folgt hinzufügen:

FC YTD = CALCULATE([Forecast],DATESYTD(Datum[Date]),KEEPFILTERS(Szenario))

Und um das Ganze in einem Diagramm mit einer klar formatierten, fortlaufenden Linie abzurunden, können Sie eine weitere Maßnahme hinzufügen, bei der die Prognoselinie am Stichtag beginnt:

FC = if(max(DimDate[Date])>=max(‚Vorhersageparameter'[Closing Date]),[FC YTD],BLANK())

Das führt zu einem Ergebnis wie diesem:

Tatsächliches Budget projiziert Vorhersage in Power BI

Sie können dies weiter verbessern, um zu rollenden Prognosen zu gelangen, wie hier beschrieben: Wie man rollende Vorhersagen in Power BI mit Acterys implementiert

Tipp 5: Eingabe von Planung und Rückschreibung in Power BI

Für die Aktivierung von Write-Back aus Power BI haben Sie im Wesentlichen 3 Optionen:

Tabelle in Power BI bearbeiten: Diese Option funktioniert nur in Power BI Desktop und eignet sich nicht für Planungsanforderungen, abgesehen von einfachen Parametern
PowerApps, die für einfache Anforderungen verwendet werden können, bei denen es um die Erfassung einzelner Einträge oder Datensätze geht.
Für Szenarien, die einen Top-Down/Bottom-Up-Einstieg, Echtzeitsimulationen über verschiedene Szenarien hinweg und eine große Anzahl gleichzeitiger Benutzer erfordern, ist Acterys in der Regel die optimale Option.

Acterys ermöglicht es Anwendern, automatisch Best-Practice-Datenmodelle aus nahezu jeder Quelle zu erstellen, die von Geschäftsanwendern mit einer Vielzahl von Workflow- und Governance-Funktionen (z. B. detaillierte Rückschreibe-Benutzerrechte und jede protokollierte Transaktion) einfach bearbeitet und erweitert werden können.

Planning Dashboard in Power BIVery erschwinglich und mit fast sofortigen Ergebnissen, wie es umfasst:

Automatisierung von Datenmodellen für Planungszwecke für fast alle wichtigen EPR- und SaaS-Service-Lösungen
Best-Practice-Vorlagen für bestimmte Planungsbereiche wie: 3-Wege-Prognose (Einkommen, Bilanz, Cashflow-Beziehung), HR, fahrerbezogene Planung, CAPEX und viele weitere Planungsbereiche.