Budget Forecasting in Power BI: An Introduction

The average financial planning and analysis (FP&A) employee spends 75% of their time on data collection and administration, with only 25% for adding business value through analysis. With Power BI forecasting, you can streamline your financial planning processes and help organizations make more timely decisions.

Let’s explore Power BI’s potential for reducing the time and costs associated with FP&A, including how it can help you improve budgeting and forecasting processes. 

Why Use ‌Power BI for Budgeting and Forecasting?

Let’s start by examining why Power BI is a useful tool for budgeting and forecasting.

Power BI is known for its ease of use and rich feature set. Users can bring historical data into the application, model data and transform it into visual reports and dashboards. Power BI provides connectivity to 600+ sources, including major accounting and finance systems. 

Reporting and visualizations in Power BI are valuable for analyzing business operations, especially at companies with an existing Microsoft-based infrastructure for data management, project management, communication and collaboration. These organizations often have in-house talent who are trained on Power BI, creating an opportunity to unlock even more value.

That said, Power BI’s primary value in these organizations is for displaying existing budget numbers in businesses that don’t often change values. Things get tricky when the process involves getting data from multiple sources, handling data at different granularities or periodically loading new financial data as it comes into your source databases. The process becomes more complex still when financial data is primarily maintained in Excel workbooks — think of the common difficulties with managing spreadsheets.

Is Power BI the Right Tool for Budgeting and Forecasting?

Power BI wasn’t built specifically for FP&A, so using it out-of-the-box for budgeting and forecasting isn’t as easy as we might hope. The good news is Power BI is extensible, which means you can evolve its functionality with add-ons and integrations. 

But there’s another way to expand Power BI’s functionality. 

It’s often easier and faster to leverage established business performance management solutions that are built upon Power BI and designed to unleash its business planning and budgeting potential. With the Acterys xP&A Suite, users can tap into capabilities such as write-back and planning, which extends Power BI into a full-fledged corporate performance management solution and offers a convenient way to budget, forecast and more. 

By using these extended solutions, Power BI’s forecasting model becomes a better option than the standalone, specialized solutions typically available on the market.

8 Benefits of Budgeting and Forecasting in Power BI with Acterys

With Acterys and Power BI combined, you get a planning powerhouse that makes it easier to build sustainable budgets, predict the impact of market shifts and plan accordingly.

If you’re exploring how to make budget projections more efficiently and effectively, check out these benefits of using Acterys with Power BI. 

All-In-One Solution

Take advantage of a single unified platform to consolidate data, transform it into interactive reports and manipulate it in real time for budgeting and forecasting with market-leading SQL Server technologies (available on Azure and on-premise). 

Instant Connectivity

Start building your budget and forecasts in minutes with one-click ERP/accounting software connectors. Alternately, use our proprietary Instant Link technology for easy integration with all your source systems.

Smart Data Warehouse

Access dedicated SQL-based data warehouses containing all the financial data from linked SAP. Oracle, Workday, Xero, Workflow Max or MYOB entities, which can be scheduled for real-time data using Acterys Power BI Sync. 

Write-Enabled Planning

Leverage Power BI Apps and Custom Visuals focused on write-back and planning, a two-way (read/write) Excel integration and best practice process templates — all to streamline your budgeting and forecasting processes.

Built-in Workflows

Get started quickly with easy-to-use, automated workflows for cost center, capital expenditures, cash-flow budgeting and forecasting, as well as legal consolidation.

Ready-to-Use Templates

Use fully editable Power BI financial reporting templates, as well as Power BI custom visuals that enable comprehensive planning features and editing directly on your Power BI report.

Cloud and On-Prem Availability

Find all relevant data sources in a “single version of the truth” data model for the cloud and on-prem. This adaptive capacity adds extra processing power in real time for more demanding budgeting and forecasting cycles. 

Fluid Collaboration Between Teams

Enjoy an integrated ecosystem of the latest Microsoft technologies, including Teams, Power Apps and Power Automate. Set up real-time collaboration, use powerful workflows and enjoy tailored mobile and web analytical applications.

Interested in what Power BI forecasting can do for your business? See how KMG Chemicals performs global forecasting in a day with Acterys, Power BI and Excel. 

How Do You Integrate Financial Sources Into Power BI?

Connecting to a financial source in Power BI is easy. Let’s take a look at the basic integration process:

1. Log in to your Power BI app, go to the Home ribbon, and view the list of available connector options after clicking the “Get Data” button.
2. After selecting the source, click the “Connect” button and specify the credentials to establish a connection to the source.
3. Power BI will then display the available data sets for bringing data into the workspace.
4. After selecting the data set, click the “Load” button to load the data. Then, you’ll be asked to Transform Data to clean and load data into the Power Query Editor.

The process becomes even easier and faster with Acterys. For example, imagine you need to integrate data from Xero. Here’s how you can automate the process using Acterys: 

1. Go to app.acterys.com/onboarding and fill out the form to get the activation email.
2. Your tenant will now be provisioned. This will take a few minutes. Once finished, you’ll receive an email with your tenant details.
3. Then log into your Acterys account to view the Acterys Modeller.
4. Here, you’ll see all the connectivity, integration and modeling options for your financial data source.
5. Connect to your Xero account, and Acterys Modeller will handle the rest for you, including importing your data into the smart data warehouse, which is stored effectively in a standard database technology. This is a key difference between Acterys and many specialized planning tools, which often include proprietary databases that are difficult to maintain.

Once your data is in the Acterys database, it’ll be available for all kinds of financial planning and analytics processes, including budgeting and forecasting. As an added benefit, Acterys will automatically transform your data into a functional data model that you can customize based on your requirements in our dedicated Modeller web app.

How Do You Add a Forecast in Power BI?

Power BI has a built-in function for creating dashboards to visualize your forecasts and help you predict future needs. To add a forecast in Power BI, follow the steps outlined in the previous section to connect to your data source. From there, follow these steps:

1. Select the visualization you want to use to view the forecast — a line chart, for example — and add your values to each axis.
2. Go to the Analytics pane and select the “Forecast” option.
3. Click “Add” and configure as per your requirements.

The line chart will reflect the changes and show the forecast data based on the data set from the financial source.

While the process is easy in Power BI, budgeting and forecasting are more complex in real-world scenarios. This process often requires getting data from multiple sources and spreadsheets, performing consolidation processes and enabling complex data modeling. You also need a workflow in place that automatically refreshes your data sets. This ensures that budgets reflect the most recent business developments and that you create accurate forecasts.

All of this is possible with tools like Acterys. You can extend Power BI’s capabilities and make it planning-enabled with our writeback technology, opening a whole new world of opportunities for budgeting and forecasting processes.

Let’s take a look at how you use Power BI and Acterys to perform monthly and yearly budget assessments on revenue, expenses and other key elements, based on your required forecast length. 

Creating Monthly or Yearly Budgets and Forecasts

First, open Power BI and connect to the data warehouse (Azure/SQL) to view and select the data models you need for building a budget. Acterys provides numerous custom Power BI visuals that make Power BI planning-enabled with writeback and data entry capabilities. 

Using visuals like Acterys Matrix and Acterys Table Edit allow you to immediately start building your new budget and profit from unparalleled options — simple manual edits, relative changes, complex constraint satisfaction scenarios and more — combined with the analytics prowess of Power BI.

Take advantage of the integrated nature of Acterys, which enables you to use smart links between different model aspects. For example, you can link detailed sales results in your financial reports or the overall connection between income statement, balance sheet and cash-flow items. 

In addition to planning directly in the visual, the Acterys Variance visual also enables you to quickly see the differences (through relative, absolute and waterfall display). Prior-year actuals are just one example. This visual allows you to adjust financial numbers, see the impact in real time and allocate your financial resources for the upcoming period effectively.

Simulating Drivers and Assessing Impact in Seconds

Integrated Planning with Acterys Matrix, Table Edit and Variance

You can use your current budget as a baseline to build forecasts for upcoming quarters or the fiscal year. Acterys’ scenario planning capabilities help you create budgets and tweak them after accounting for multiple variables affecting business conditions. Acterys’ visual planning functionality also dramatically enhances the speed and ease of forecasting. You can adjust forecasts with a simple drag-and-drop process.

Creating Monthly or Yearly Budgets and Forecasts

First, open Power BI and connect to the data warehouse (Azure/SQL) to view and select the data models you need for building a budget. Acterys provides 8 custom Power BI Visuals that make Power BI planning-enabled with writeback and data entry capabilities. (See all Acterys Power BI Visuals Here)

Using visuals like Acterys Matrix, Acterys Table Edit, and others, you can immediately start building your new budget and profit from unparalleled options: from simple manual edits to relative changes, complex constraint satisfaction scenarios and much more combined with the analytics prowess of Power BI.

The integrated nature of Acterys enables you to use smart links between different model aspects, for example linking detailed sales results in your financial reports or the overall connection between income statement, balance sheet, and cash flow items.

In addition to planning directly in the visual, the Acterys Variance visual also enables you to quickly see the differences (through relative, absolute, and waterfall display). For example, with prior year actuals. This allows adjusting the financial numbers, seeing the impact in real time, and then allocating your financial resources for the upcoming period effectively.

Simulating drivers and assessing impact in seconds

Immediate Impact Analysis: Acterys Visual Planning

You can use your current budget as a baseline to build forecasts for upcoming quarters or the fiscal year. Acterys’ scenario planning capabilities help you create budgets and tweak them after accounting for multiple variables affecting business conditions. Acterys’ visual planning functionality also dramatically enhances the speed and ease of forecasting. You can adjust forecasts with a simple drag-and-drop process.

Manage Scenarios: Acterys Copy

The best part about working with Acterys Power BI is that you don’t need to write any complex DAX to perform calculations as the product handles all of it for you to show results instantly. With its powerful planning engine, you get unparalleled read and write back speeds, complemented by the adaptive cloud capacity and processing speeds of the cloud to scale up and down as per your budgeting and forecasting needs.

Unlock Greater Value with Power BI Forecasting

Budget forecasts are essential, as they help your business make timely decisions even when operating in highly volatile market situations. Acterys’ two-way write-back technology and custom visuals can help you perform budgeting and forecasting in Power BI with ease and speed.

Want to explore the Acterys platform? Sign up for a 14-day free trial to see how it makes the entire budget forecasting process smooth and fast.