To some extent, Microsoft’s Power BI can be considered ‘Excel adjacent.’ An integrated suite of data management software systems, Power BI includes as two of its biggest features Power Query and Power Pivot – both of which also appear in Excel.

However, where Power BI builds significantly on Excel’s capabilities is in its evolution of the programming language Data Analysis Expressions (DAX), first released in 2009 as part of Microsoft’s “Project Gemini”, designed to enhance data modeling and analytics.

In short, DAX enables Power BI users to compile, interrogate and visualise data in far more flexible and intuitive ways than are possible in Excel, which is largely restricted to the dimensions of row, column and sheet.

“Power BI can handle data in dimensions that Excel was never set up for,” says Liam Bastick FCA, Director at global financial modelling and training experts SumProduct. “Excel is a great, simple piece of software – but it is now more than 40-years-old. Power BI is more complex and powerful.”

Indeed, the more dynamic nature of Power BI presents distinct advantages for financial planning and analysis (FP&A).

The best functions for FP&A

Among the functions that lend themselves readily to FP&A tasks, Bastick is particularly enthusiastic about five:

1. The ability to slice and dice data in many dimensions 

“If you get adventurous in Excel,” Bastick notes, “you can create three-dimensional arrays using Data Tables. But it’s very time consuming, and you can’t go much further than that. The humble spreadsheet’s capacity to interpolate and extrapolate data or produce forecasts is very constrained.”

Power BI surpasses those limitations, he says. For example, what if a retail company has gathered a stack of data on a customer? Perhaps it has captured their gender, their height, how often they come into the store, how many children they have, their spending capacity and their favourite products.

“Professionals have certainly tried to slice and dice information by a similar variety of datapoints in Excel,” Bastick says, “But that would’ve been laborious, manual work. You’d have to tell your director, ‘Give me three days.’

"In Power BI, though, you can represent all your relevant figures in what’s called a Data Cube, which allows you to slice and dice instantaneously. In combination with DAX, that greatly enhances the user experience on FP&A tasks, which may well involve the sort of demographic data seen in our retail example.”

2. Drill-down capabilities 

Leading straight on from – and in many ways related to – slicing and dicing, Power BI enables users to explore datapoints at various levels, ranging from broad, top-line summaries to far more granular information, such as isolated transaction details.

Bastick points out: “Power BI’s flexibility supports deep and thorough evaluation through the use of built-in facilities such as outlier analysis, the Insights tool and Explain the Difference waterfall charts.”

3. Self-service analytics 

For many accountants engaged with FP&A, this provides a welcome cure for a nagging pain point. “In the past, when Microsoft was evolving Power BI, professionals tended to use software such as Tableau to create bespoke reports that they were finding it too difficult to produce in Excel,” Bastick explains.

“In fact, they would typically get their IT teams to create those reports for them – but would have to wait for IT to run the data each time. And if they wanted to make any changes to the report, they’d have to go to the back of the queue again and wait.”

Once Power BI’s capabilities had developed by learning a little bit of coding, accountants could carry out what’s called self-service business intelligence. Bastick says: "That enabled accountants to create their own, bespoke reports very quickly, without having to rely on IT.” 

One of the biggest frustrations with the old way of working, he notes, was that IT didn’t necessarily understand what accountants wanted. But with self-service analytics, requirements won’t get lost in translation.

4. Data integration 

Again with an eye on report creation, Power BI can connect seamlessly with a host of different data sources, from enterprise resource planning systems to spreadsheets.

That effectively centralises financial information, ensuring that the accountant’s analysis is more comprehensive and reliable, compared to what is achievable on a myriad linked spreadsheets – with the attendant risk of mis-references and other errors.

Walking Insights through a screenshare of tables on his own Power BI account, Bastick says: “These are all connected. They could be drawn from an SAP system, Excel, various locations on the internet – lots of places – whereas Excel tends to be one sheet after another. So, it brings together crucial streams of data, such as my control accounts, inventory and dividends, regardless of where they originate.”

5. Scenario analysis 

“Tools such as Slicers, Switches and Bookmarks make it easy to model different scenarios, such as changes in revenue or expenses,” Bastick explains. “That helps teams to anticipate and plan for potential best- and worst-case outcomes.”

Don’t sleep on Power BI’s ‘measures’

For his concluding message, Bastick urges accountants not to look solely at Power BI’s functional capabilities. Another set of tools they must master are measures – formulas that enhance the drill-down process in rigorous detail, and could therefore be of immense use in FP&A.

“They enable you to focus your data scrutiny even more tightly on a particular store, product or customer, for example,” Bastick says. “So, rather than dwelling on specific functions, I’d advise accountants to learn about and understand the power of measures, and how they can help you improve your data analysis.”