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:
which contains the lowest granularity time detail as well as period hierarchies e.g., weeks, financial years, etc.
which contains the different scenario types e.g., actual, budget, forecast etc.
Which includes the item that you are planning or measuring e.g., revenues, expenses, quantities, KPI’s etc.
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.
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 % =
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.
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:
Or using a small multiples visualization like Acterys Variance:
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:
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:
- Edit Table in Power BI: this option only works in Power BI Desktop and is not suited for planning requirements apart from simple parameters
- PowerApps which can be used for simple requirements where the task is about collecting single entries or records.
- 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 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).
- Data model automation for planning purposes for nearly all major EPR and SaaS service solutions
- 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