Share:

Share on facebook
Share on twitter
Share on linkedin

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel

In this blog I am covering learnings, tips, and trick from implementing planning, forecasting and budgeting systems with Power BI and modern Excel features.

Tip 1: It Stands & Falls With The Data Model

As with any aspect of corporate performance management the data model is crucial. Having a well design data model will lead to good performance, reports and data entry forms that are easy to setup and high user acceptance. Vice versa no efforts on the logic and front-end side will save you with a bad model. From a modelling perspective we have yet to come across a planning requirement where a well-designed star schema is not the best choice. A star schema consists of a fact table with the transactions and dimensions that define these transactions as shown figure A.

In planning you either have a single fact table with transactions from all scenarios or multiple fact tables that contain different scenario data e.g., one for actuals one for budget etc. With dimensions you nearly always have the following dimensions in any model:

Simple Star Schema For Financial Planning Requirements in Power BI

Time

which contains the lowest granularity time detail as well as period hierarchies e.g., weeks, financial years, etc.

Scenario

which contains the different scenario types e.g., actual, budget, forecast etc.

Account/Measure

Which includes the item that you are planning or measuring e.g., revenues, expenses, quantities, KPI’s etc.

Organizational entity

Here typically covered are group companies, subsidiaries, and cost centers

Depending on the specific requirements you will have other details like product, sales teams, employees, and many more.

A comprehensive planning model can get very complex like shown in figure B but solutions like Acterys Apps and Power BI Sync for any data in Power BI can help to create this automatically based on major accounting and SaaS systems.

Complex Planning Model Power BI

Tip 2: Variance Calculations & Visualization

Variance calculations and visualization are reasonably simple when the above modelling approach with a single scenario dimension used across all fact tables is followed. You can use the scenario dimension directly for scenario comparisons or you can create a simple scenario filter measure in the structure BU = CALCULATE(PlanningFactTable’[Measure],’ScenarioDimension’[Name]=”Budget”) for all scenarios that you want to compare.

These explicitly defined scenarios allow you then also to setup your variance calculations comparing actuals to target achievement.

For income statement financial comparisons my recommendation here is to use a sign convention in your model that differentiates revenues and expenses by sign. This can lead to debates with accountants but is in my view the clearest and easiest way to handle variances.

As you can then use the following simple calculations that will show the right variance (absolute and relative) with very little effort:

Absolute Variance Δ 

VAR Δ = [AC]-[BU]

VAR % =

IF (OR ( [AC] <> BLANK (), [BU] )
<> BLANK (),

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

(using the ABS() function ensures that in case of two negatives / expenses the variance is shown with the right sign)

The Acterys Reporting visual will calculate these variances automatically and also add visualizations that reflect IBCS principles.

Acterys Reporting Power BI Visual with Variance Calculation

Tip 3: Variance Drill Down With Custom Tool Tips

For helping users quickly identify the key drivers for variances I like to use custom tooltips in Power BI that list the transactions related to the variance, sorted by relevance, and combined with appropriate visualization. Either in a table:

Power BI Custom Tooltip with sorted variance details

Or using a small multiples visualization like Acterys Variance:

Acterys Reporting with graph tooltip

Tip 4: Forecast Calculations & Visualization

A common requirement in planning processes are forecasts where you want to calculate the revised targets for future periods based on actual data or new developments. Typically, this involves a “cut off date” until which you want to use the current actual results and a forecast scenario (often an adjusted budget) up until the period (financial year/period) end date.

For the cut-off date you can either use the latest date of actual data (well described in this post: Showing actuals and forecasts in the same chart with Power BI – SQLBI) or have a parameter table (e.g. Power BI Edit Table or for more comfort and changing dates without Power BI Desktop: an Acterys dimension) where users can enter or select the cut off date from a list.

To get to the forecasted total results you can calculate the “Period to Date” of your Actuals plus the total for the rest of the forecasting period.

The required calculations will look similar to this:

Actuals until cut-off date (‘Forecast Parameters'[Closing Date]):

AC PTD = CALCULATE(If(max(Date[Date])<=max(‘Forecast Parameters'[Closing Date]),[AC],0))

The Budget after cut-off date:

Outstanding BU = CALCULATE(If(max(Date[Date])>max(‘Forecast Parameters'[Closing Date]),[BU],0))

And finally the combined forecast:

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

KEEPFILTERS is only required if you have a single fact table with multiple scenarios. Using SUMX() makes sure that the values are correct on aggregate levels.

To show a year to date prediction line you can add a YTD calculation like:

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

And to top it all off in a chart  with a clearly formatted, continuing line you can add another measure where the forecast line starts at the cut-off date:

FC = if(max(DimDate[Date])>=max(‘Forecast Parameters'[Closing Date]),[FC YTD],BLANK())

Leading to a result like this:

Actual Budget projected Forecast in Power BI

You can further improve this to get to rolling forecasts as described here: How To Implement Rolling Forecast in Power BI with Acterys

or realize asymmetric reports as covered here: How to Realize Dynamic Asymmetric Reports in Power BI / Excel | Analytics | Planning | Power BI | PowerApps | Azure Consultants – Managility

Tip 5: Entering Planning and Write Back in Power BI

For enabling write back from Power BI you have essentially 3 options:

  1. Edit Table in Power BI: this option only works in Power BI Desktop and is not suited for planning requirements apart from simple parameters
  2. PowerApps which can be used for simple requirements where the task is about collecting single entries or records.
  3. For scenarios that require top down/bottom up entry, real time simulations across different scenarios with large numbers of concurrent users, Acterys is typically the optimal option.
Acterys Architecture

Acterys enables users to automatically create best practice data models from nearly any source that can be easily edited and extended by business users with a variety of workflow and governance features (e.g. detailed write back user rights and every transaction logged).

Planning Dashboard in Power BIVery affordable and with nearly instant outcomes as it includes:
  1. Data model automation for planning purposes for nearly all major EPR and SaaS service solutions
  2. Best practice templates for specific planning areas like: 3-way forecasting (Income, Balance Sheet, Cash flow relationship), HR, Driver Based, CAPEX and many more planning areas.

 

To start a trial just go to https://acterys.com

 

 

 

 

Recent Posts

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel In this blog I am

Planning and Analytics [Infographic]

It is not uncommon for companies to have separate planning and analytics processes or legacy solutions that result in more

Materialize and Synchronize Power BI with a Data Warehouse “Materialize” is a database concept that refers to storing the result

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.

Tags