Share:

Share on facebook
Share on twitter
Share on linkedin

Microsoft Excel vs. Power BI: Is it Time to Make the Switch?

Whether you are  budding a startup that has secured Series B funding or a global enterprise, it is likely that some or most of your routine financial reporting, planning, and analysis (FP&A) tasks are done in MS Excel.

As we have covered before (5 Tips For Better Using Excel), the traditional “Copy paste into Excel” approach is not suitable to handle processes that require extensive external data access as nearly every FP&A process does. As indicated from research, on average, a medium-sized organization (≈1000 employees strong) draws data from about 400 sources for business intelligence and analytics. So let’s say if 10-15% of these sources contribute data to FP&A tasks, imagine the complexity you’ll have to deal with when preparing and managing data through spreadsheets for effective business intelligence.

In such scenarios, a data model-driven approach is much more suitable. This approach, where Excel is just a container for automatically updating data, guarantees significant productivity gains, better insights and happier staff, not to mention avoiding  very costly errors. The next question is, if Excel is still the best tool for the job and should you consider new self-service analysis solutions, that could do the job better?

The obvious candidate here is Power BI. The self-service analytics solution that is now dominating in that space according to Gartner (Gartner Magic Quadrant Analytics) and also confirmed in studies among FP&A professionals as the most relevant tool considered.

Power BI Preferred-Analytics Environment for FP&A – BPM Partner Survey 2021

In this article we will provide an overview of how the two solutions compare in light of typical FP&A scenarios.

Microsoft Excel vs. Power BI: Overview of Key Capabilities

In essence, both tools can perform data analysis from various datasets and present the information in tables, visual charts and graphs.

Excel is a powerhouse when it comes to creating complex tabular reports and performing ad hoc or quick computations, thanks to its wide range of functions and expressions available. In contrast, Power BI’s strength lies in more extensive, interactive analytics capabilities.

While Excel and Power BI have some shared components e.g., the data modeling engine: y, there are very significant differences as well.

As such, each software makes for a better choice in specific scenarios, which we’ll explore in the light of key capabilities these tools offer to users.

Simple Star Schema For Financial Planning Requirements in Power BI

Ability to Model Business Data for Reporting

At the start of any reporting or analysis effort, the first step should be to build a data model that optimizes the data for the analysis process  and integrates different sources. The Power Query data modeling feature is available in both Excel and Power BI but works slightly differently. Let’s take a look:

Data Model Types

A key differentiator to keep in mind is the structure of the data model: are you querying a single table or a full model with relationships between the tables. Both options Excel (Excel Data Model aka PowerPivot) and Power BI (Analysis Service) support a full relational (star schema) model (IMHO one of the biggest differentiators to Google Sheets which can’t offer anything similar).

Data Model Limits / Storage

The data model tables are limited in Excel by the number of Excel worksheet rows or by the capacity of the local data model (“Power Pivot”) associated with the workbook. There is no client-server support i.e., users cannot use the data model outside the workbook. For local requirements, Power BI Desktop also uses a local Analysis Services instance as the data storage engine in import mode (in live connect DirectQuery mode there is no limit) but it supports deploying the model to the Power BI service where it is available in a true client server fashion. This means that users can access the model from other clients than the original Power BI desktop file, like in other Power BI reports or even from Excel.

Automated Data Model & Report Generation For ERP, CRM, SaaS

Automated Data Model Generation

Designing an effective data model can be a very complex task that requires significant expertise of the modeler particularly in light of complex ERP systems and integrating other sources (CRM, POS, etc., etc.) into one data model. Solutions like Acterys Apps offer ready-made best practice data models even including Power BI and Excel reports and can help streamline this process.

Data Source Integration

Over the years, Excel has evolved from just being able to copy-paste data from other workbooks and text files to offering native connectivity to import external data. Thanks to the Power Query feature, you can bring data from unstructured file formats, SQL and SAP databases, Azure cloud, and even from Power BI itself into Excel.

A key feature missing in the Excel data model standard Power Pivot -compared to Power BI- is the DirectQuery connection type. This allows you to connect to a source “live” and use the latest version of the data in reports without physically moving it into the data model.

Now, Power BI supports connectivity with hundreds of databases, cloud services, flat files, web apps & CRMs, OData feeds, and much more. The list is so long that Microsoft had to break it down into sections, sorted in alphabetical order, to make it easier to scour through it. It even supports connectivity using DirectQuery. The tool provides a single platform that can help create a centralized dashboard containing all your business KPIs, market trends, forecast projections, and more in a highly visual, interactive manner.

In summary:

  • There are far less connectors supported in Excel Power Query than in Power BI. There is no support for custom developed connectors.
  • There is no live connect support (Direct Query) in Excel Power Query as to update data, all records for the model have to be loaded again as opposed to just loading the new data for the current requirement e.g. filtered report.

In either case “having a connector” doesn’t mean you have the right data model immediately available. You will still need to invest significant work towards designing the right data model, integrating different sources and calculation logic.

Logic

Defining calculation logic is similar in Excel Data Model and Power BI:  both use the Data Analysis eXpression (DAX) language designed to write customized formulas and help perform aggregations. Certainly a strong feature that enables  defining logic within and across tables with mostly lightning fast calculation times thanks to its intelligent compression capabilities.

Security

Model results in Excel and Power Pivot need to be very critically reviewed from a security perspective: the data “lives in the workbook”.

Excel offers all the standard features to secure your files and their contents. You get file-level protection, which involves file encryption with a password, granting read-only access, and even controlling what users can do within a spreadsheet so that no important formula or data is deleted or changed. However, this level of security is just not enough when it comes to sensitive data, like customers’ personal or credit information.

From our practical experience of 20 years working with clients very rarely appropriate security measures are setup for Excel workbooks. So, if someone has access to the workbook, they have access to all the data as there is no concept of granular security. In essence, you have the workbook; you have the data and that can be a massive amount of it. With Excel Data Model/ PowerPivot models, you can easily handle tens of millions of rows. Nice for the users from an analytics power perspective; not so nice when you can lose the entire underlying data set that’s now accessible to anyone with access to the workbook.

This is similar in Power BI’s import mode but very different when deployed on the Power BI Server where exact granular security rights can be defined using Row Level Security (RLS). In Direct Query mode, there is no data stored in Power BI: users require successful authentication with the underlying data source.

Live Data Streaming

Some companies require time-sensitive data reporting. For example, NYSE or London Stock Exchange cannot afford to work with outdated data, as the market conditions change by the minute.

Unfortunately, there is no easy way to refresh data in Excel automatically. You have to use Visual Basic for Applications (VBA) and code to automate repetitive processes. And as you can imagine, when large datasets and multiple sources are involved, this could quickly become a time-consuming and overwhelming task.

When real-time data updates are required, Power BI offers built-in features that make this task easy and manageable. It supports multiple ways to refresh data:

  1. Setting up live streaming services
  2. Defining automatic refresh setting intervals like 1 hour, 10 minutes, down to one second and also define a time and day-wise frequency.
  3. Through the Change Detect feature where you define a measure to check for changes along with how frequently it should check the source for it. However, this functionality is limited to DirectQuery sources and selected LiveConnect scenarios only.

Work with Big Data

Storing the data in Excel is useful for smaller datasets and once off requirements with less than 1 million records or are under 500 MB workbook size. Using the Excel Data Model (Power Pivot), limits greatly increase to potentially hundreds of millions of rows but in these regions, users will experience instabilities. Excel was never designed as a big data store.

Power BI excels in this area. The platform is built to handle Big Data, breaking the barriers to handle data far beyond these limits. We are talking about billions of records here. Thanks to its robust compression algorithms that intelligently reduce data size and cache it on Import, it adds immense value in enterprise environments that work in fast-evolving, dynamic markets.

Data Security and Governance Policies

If you want to implement a set of data governance policies and secure data on a granular level, Power BI offers several features to achieve that, such as:

  • Cloud-based security provisions to allow IT admins to enhance organizational oversight
  • Role-based authentication to effectively control who can view and edit the reports
  • Row-level security to restrict access to certain areas of the report, which is also applicable to models
  • Session monitoring to see user activity in real-time to mitigate risk

You can even apply your governance policies when data is exported from Power BI and add sensitive content labels to critical data, giving your data reports the all-round security it needs.

Accessibility and Team Collaboration

Both solutions offer very comprehensive collaboration options:

Excel offers  live collaboration and great sharing capabilities e.g. Excel Online in the web browser respectively integrates with MS Teams. But as you can imagine, in an enterprise, there will be hundreds of such Excel files floating around in the cloud on SharePoint, making it difficult to dig out insights and collaborate on projects.

Collaboration in Microsoft Teams with Power BI

Power BI offers free options with Power BI Desktop and ability to publish to web. Both not ideal from a security perspective. For secure sharing, with granular security there is no alternative to the Power BI Service.

Power BI Service, enables users to publish data to their desired destination. You can publish single or consolidated reports and dashboards in any dedicated Workspace, allowing a group of people to access and work on it simultaneously. The added benefit is that once you have published your content, the Power BI service will refresh the data automatically, saving you and other team members from resharing it again and again.

Lastly, the Power BI service allows embedding your dashboards on online portals and websites, giving viewers interactive charts and graphs with the same ability to drill down into the data as you could from its app.

 

Write back / Planning

Data written back in Excel is by default stored in the workbook, which is often not optimal for analytics, reusing in other reports/tools and security/sharing. Instead, a central database is a much better option. Power BI also doesn’t offer write-back options by default. Both solutions can be extended with add-on solutions, like Acterys.

Write Back Options From Power BI

Acterys includes automatically generated data models from typically used accounting, ERP and SaaS solutions, respectively providing tools to easily generate them from any source. All of it is stored in a standard SQL database in the cloud, which is often already the standard in many organizations. With this data model, Acterys offers extensive planning, forecasting and write-back options with either 8 custom visuals in Power BI or the same with an add-on that works both in the Excel Desktop version and in Excel Online.

Write-back from Excel and Excel Online

Monitor Critical KPIs and Metrics

The key to success in today’s hypercompetitive markets is the ability to react to change and do it fast. With Power BI, you o get notified (both on the app and via email) when certain metrics or KPIs go below, above, or hit the set threshold. This allows you to take action to remediate the situation proactively before it can lead to any damages.

Let’s say you want to make sure that your overall marketing spending on the current campaign doesn’t go over $10,000. You can set an email alert that can notify you when it is about to hit that mark, allowing you to evaluate areas consuming your budgets and optimize your spending accordingly.

This feature is not available in Excel, so it is a solid point for Team Power BI.

Learning Curve & Usability

Being around for more than 3 decades as part of the Office suite, we already have several generations of Excel users using it for reporting and data analysis. The familiar interface and easy-to-implement processes, like using mathematical formulas on datasets, building pivot tables, and creating graphs, add up to its excellent usability.

Power BI offers a drag-and-drop interface that makes building dashboards and reports easy and fun.

However, to unlock the true power of both these tools, it is recommended to dive deeper into a roster of advanced features. A few of these include Power Query, Power Pivot, DAX language, setting up different visuals, and the data refreshing and sharing processes, among others.

Power BI or Excel for FP&A: All Points Considered…

It really boils down to your organization’s use case. In my opinion a smart “synergetic” use that builds on the same live connection to datasets (as opposed to offline “Copy/paste”!) from both tools is the optimal strategy.

As a guidance when to use what, I propose to ask the following questions:

  1. Is your focus high-level analytics instead of preparing data with formula and calculations?
  2. Are you working on one off or repeating requirements?
  3. Do you require the full flexibility of completely asymmetric Excel reports or can your requirements be covered in a matrix(ces) with rows, columns and filters?
  4. Do you require specialized financial functions only available in Excel
  5. Do you need the ability to refresh data automatically?
  6. Do you require interactive, advanced analysis features in Power BI like unparalleled range of visualization options, far superior drill-through/drill down (e.g. custom tooltips in Power BI), integration of Python and r, etc.
  7. What team collaboration means are relevant for the success of your reporting and analyses efforts?
  8. Is there a need for robust data governance policies and security measures?
  9. Do you want to set alerts to track KPIs?
  10. Are you working with Big Data?

If you’ve nodded yes to half of these questions, the scales are likely to tilt toward Power BI.

If you are interested in automating data models from ERP, CRM and SaaS systems and adding write-back and planning capabilities to Power BI & Excel  request an onboarding session with our solution experts.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Recent Posts

Next Gen Unified Analytics & Planning: Acterys 21.2 The new Acterys version 21.2 marks an unparalleled set of innovations around

Whether you are  budding a startup that has secured Series B funding or a global enterprise, it is likely that

Forward thinking organizations across the globe are now enabling their finance teams to link operations with strategies. Moving beyond their

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel In this blog I am

Planning and Analytics [Infographic]

It is not uncommon for companies to have separate planning and analytics processes or legacy solutions that result in more