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.
In this blog, I am covering learnings, tips, and tricks for implementing planning, budgeting, and forecasting in Power BI along with modern Excel features.
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:
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.
[ctabutton title=”5 Best Practices for Connected Planning, Budgeting, & Forecasting ” content=”Download this whitepaper to discover the perfect recipe for driving Planning, Budgeting & Forecasting transformation.” button=”Get Whitepaper” url=”https://landing.acterys.com/planning-budgeting-forecasting-best-practices”]
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.
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:
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]):
Then the Budget after cut-off date:
And finally the combined forecast:
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:
To further improve things, we can add another measure where the forecast line starts at the cut-off date with a dashed line style:
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
[ctabutton title=”Finance Transformation for Agile Planning & Reporting” content=”Discover how finance transformation is facilitates dynamic planning processes” button=”Read Whitepaper” url=”https://landing.acterys.com/finance-transformation-whitepaper”]
For enabling write back to budget and forecast in Power BI desktop and online, you have essentially 3 options:
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).
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.
[ctabutton title=”Need Tailored Reporting, Planning & Analytics Solutions?” content=”Talk to our solution experts now” button=”Book a Meeting” url=”https://acterys.com/book-a-meeting/”]
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.
© 2024 Managility Pty Ltd All rights reserved.
Automated page speed optimizations for fast site performance