Budgeting, Planning, and Forecasting (BP&F) is critical for any business’s success. An organization’s full growth potential cannot be realized without thorough planning, analysis, and the correct financial leadership. It’s as simple as that.

That being said, BP&F is often considered a siloed process that doesn’t require direct interaction with other departments.  That’s a mistake. In fact, BP&F can be used for getting insights from all departments of the company including marketing, sales, production, and even operations.

But, without the right tips, implementing BP&F can be a challenging task.

Top Tips for BP&F in Power BI and Excel

In this blog, I am covering learnings, tips, and tricks for implementing planning, budgeting, and forecasting in Power BI along with modern Excel features.

Tip 1: BP&F Stands & Falls With The Data Model

As with any aspect of corporate performance management the data model is crucial. Having a well-designed data model will lead to good performance, reports, and data entry forms that are easy to set up and have 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 in figure A.

In Power BI 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 and one for budget, etc. With dimensions you nearly always have the following dimensions in any model:

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.

Tip 2: Variance Calculations & Visualization Plan, Budget, and Forecast in Power BI

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 set up your variance calculations comparing Power BI actuals vs forecast for 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: Budget/Plan Variance Drill Down With Power BI Custom Tool Tips

For helping users quickly identify the key drivers for variances to plans and targets, 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 in Power BI

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))

Then 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))

To further improve things, we can add  another measure where the forecast line starts at the cut-off date with a dashed line style:

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

Tip 5: Entering Planning and Write Back in Power BI

For enabling write back to budget and forecast in Power BI desktop and online, 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 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 Power BI analysis tool 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).

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.

How Acterys Helps FP&A in Power BI?

Acterys is a cloud or on-premise based service for data discovery and planning. It enables business users to generate professional data models that integrate all your relevant data sources with connectors to a variety of accounting systems and other sources.

To try out all the Acterys features for Power BI budgeting and forecasting, you can start a trial here.