Five Tips on Budgeting, Planning, Forecasting (BP&F) in Power BI, Excel

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.

Key Takeaways

1. Budgeting, Planning, and Forecasting (BP&F) are essential for realizing an organization’s growth potential. BP&F is not just a financial function but a strategic imperative.

2. BP&F should not operate in isolation; it should involve collaboration with all departments, including marketing, sales, production, and operations.

3. The success of BP&F heavily relies on a well-designed data model ensures good performance, user-friendly reports, and easy data entry forms. A star schema, comprising fact tables and dimensions, is often the best choice for planning requirements, ensuring clarity and efficiency in modeling.

4. Variance calculations and visualization are simplified when using a single scenario dimension across fact tables. This approach allows for straightforward scenario comparisons and enables the calculation of variances between actuals and forecasts.

5. Effective forecasting involves setting a cut-off date for using current actual results and transitioning to a forecast scenario. Calculating “Period to Date” actuals and combining them with forecasts is a common approach.

6. Enabling write-back to budgets and forecasts in Power BI is crucial for planning processes. Options like PowerApps and Acterys offer solutions for different planning requirements. 

FAQs

1. What is the primary focus of integrating planning processes with analytics in finance departments?

The primary goal is to leverage data-driven insights for effective decision-making and to adapt and implement changes in business operations and growth strategies.

2. What are the key drawbacks of using traditional spreadsheet software like Excel for financial planning?

Excel has limitations such as data constraints, error-prone manual data entry, performance issues with large data volumes, and limited data modeling capabilities.

3. How can an analytics-enabled planning solution benefit businesses?

Such solutions offer timely reporting, data visualization, and data-driven insights, helping businesses manage risk, exercise decision agility, and achieve cost control.

4. What role does AI-backed analytics play in integrated business planning?

AI-backed analytics enhances integrated planning by providing insights into historical data, allowing for the modeling of what-if scenarios and rolling forecasts, and enabling organizations to adapt to changes more effectively.