Microsoft Power BI is a powerful analytics and business intelligence tool that helps users connect, model and visualize business data and integrate those visuals into commonly used apps and services. Businesses looking to make data-driven decisions rely on the platform for meaningful solutions and insights. But even if you’re using Power BI planning and forecasting to streamline your financial planning and analysis (FP&A) process, there’s probably more you can do to maximize its impact.
We’ll dig into 13 important questions to consider when using Power BI for planning, including the two main types of planning solutions in Power BI. We’ll also discuss the data integration and underlying data models you should know about if you plan to use Power BI for business data integration and management.
Power BI isn’t just an analytics platform. With the right approach, it becomes an extremely versatile and powerful platform for planning, forecasting and write-back.
Currently, there are two main types of planning solutions in Power BI:
Visual-only personal storage uses data storage that’s attached to the specific user for sharing data that can only be exported to Excel or text files.
For either client-server or visual-only, read on for key questions you should ask, especially when looking to implement planning, write-back and forecasting in Power BI:
The planning process typically requires tight integration with the systems that contain the actual data that you want to use for comparison. Enterprise planning scenarios require a closer look at the underlying technology:
You want to avoid extensive moving of data between data stores, particularly if they are proprietary, without the support of commonly used ETL tools and front ends. The visual-only approach is typically focused on simulations by a single user. As data isn’t stored on a central server, the only way to share results is by exporting to Excel workbooks or text files.
An open, accessible data model means data visibility isn’t limited to Power BI but can be used with any front end with up-to-date data access in a single, “one version of the truth” data model. This can include Excel, workflow processors or any other reporting or analytics solution.
Check how easy it is to link to or integrate data from the relevant systems in your process, such as finance or marketing. Is a best practice Power BI model automatically created? Ideally, this process occurs even with data entry forms and reports.
With proven connectors to key systems (enterprise resource planning, marketing, etc.), you can avoid extensive efforts to write custom extraction transformation and loading (ETL) packages and ongoing updates. Make sure the integration:
Keep in mind that these integrations are quite complex and shouldn’t be underestimated, even for SMB systems.
The data model is the heart of your planning application. Verify that your modeling approach uses a central model that supports concurrent use and can be easily adapted by business users without requiring specific IT skills.
If you need to go beyond simple simulation by a single user, a shared data model will be required. The preferred choice is Power BI, which provides the option to use different components such as hierarchies (product, customers, etc.). Re-creating everything from scratch in a proprietary planning app is typically not optimal.
Additionally, check whether the vendor provides commonly required calculation logic (e.g., three-way forecasting) or if you can use existing logic in the model, e.g., DAX measures. If functionality is part of a proprietary (web) application, it’s typically much harder to customize than solutions built on widely used standards.
Different approaches integrate with Power BI differently. When looking at solutions, ensure that the approach supports key Power BI features. You want to avoid solutions that run legacy web apps in iframes in Power BI or, worse, offer a separate application with a user interface similar to Power BI.
Make sure that your solution supports:
Specialized IT skills are expensive and rare. You typically want to avoid a solution that requires technical specialists — for example, working with proprietary data stores or maintaining common tasks. Ongoing model maintenance should run within Power BI or other environments accessible to business users.
Planning is typically more than just entering and aggregating a few planning assumptions. In addition to detailed data, users will likely require the option to enter and simulate on aggregated levels (“top down”). They’ll also want the option to break down these assumptions according to the desired allocation method (e.g., based on last year’s results, using a particular seasonality, etc.). Consider the provided calculation logic and extensibility apart from data entry.
Having a proven logic is crucial. Ask about data entry and allocation options, such as Power BI demand forecasting or three-way planning logic (the relationship between income statement, balance sheet and cash flow). You want to ensure basic three-way processes are covered, such as calculating the implications of higher revenue on your balance sheet and cash flow.
It’s essential to understand the authentication platform in place. Are standards such as Microsoft Entra ID (formerly Azure Active Directory) supported, allowing existing network users and groups to be used? Or do you need to maintain a separate security layer within the planning app? Planning requires defining read and write permissions. Does the solution support this?
Security is paramount, but wasting time maintaining security layers beyond corporate standards usually isn’t justifiable, especially when you’re using proven standards with governance options, such as multifactor authentication or biometric credentials.
Does the solution provide full transparency with detailed audit trails for every change, including who entered or changed data?
One of the key reasons for moving from a spreadsheet to a professional planning solution is transparency. Enterprises can’t afford unintended changes or edits that can be traced..
Make sure that the planning solution supports on-premise and cloud deployment.
Planning data is as sensitive as it gets. Even with the best governance, many companies aren’t prepared to have cloud aspects involved in the solution. Confirm that both deployment options are supported.
From a quality perspective, you should check whether the solution is listed in Microsoft’s AppSource. Only custom visuals and apps listed there have gone through an extensive quality check by Microsoft.
Now that you know the top questions to consider before adopting a solution, you’re ready to make informed decisions about your Power BI predictive forecasting and planning process.
Remember that you don’t have to solve all of these challenges on your own. Plenty of resources are available, whether you’re looking to improve sales forecasting in Power BI, create more accurate time series forecasting or seek additional data visualization and forecasting options.
If you need support answering questions about Power BI or your financial planning, budgeting and forecasting process, we’re here to help. Connect with us to learn how the smart, fast and effective xP&A suite of tools can help you better understand your business and enable strategic growth.
© 2024 Managility Pty Ltd All rights reserved.
