ICAEW.com works better with JavaScript enabled.

Add some Power to Audited Accounts with Power BI

Author: Eden Smith

Published: 02 Oct 2023

At Eden Smith we encourage our clients to build a sustainable data strategy. We believe in a value-driven approach that transfers knowledge and empowers your team. In this article, we explore how you can use Microsoft Power BI to streamline the process of creating audited accounts at a low cost.

Chat GPT has exploded. We struggle to get through a meeting about data without a client, VP or senior stakeholder asking how A.I or Chat GPT can be used in their business. Generative A.I seems to have captured the imagination however to deploy effectively requires foundational data work - a case of not running before you can walk.

It's important to take note and remember all the opportunity, time saving, accuracies and efficiencies that can be gained by using simple modern data approaches in our everyday processes.

As a Chartered Accountant, you do not need to understand the advances of A.I to stay ahead of the curve. In the rapidly evolving world of finance and accounting you can leverage the power of modern technology, such as Microsoft Power BI, to streamline the process of creating audited accounts.

Here are some low-cost quick wins that will build efficiencies and quickly realise a return on investment.

Before we create fancy visuals

The strength of Power BI, and other available visualisation tools, derives from their ability to bring accounts to life and to be able to interact with the financial data. We can explore where there may be challenges, opportunities, and efficiencies within an easy to digest format. However, we believe modern data practices offer amazing opportunities - within the accounting and auditing - that can be realised before you can even say, "Do you like my new dashboard?"

Repeatable processing

The first advantage comes from the logical step-by-step process that, once deployed, will repeat the same calculation, transformation or check on millions of rows. Think of it like double clicking on the bottom left of a cell… just for every row of every spreadsheet you want to apply that calculation. And the best bit? There is no need to rewrite a complex formula. It’s repeatable and scalable. The next month/quarter/year that you run the checks or transformations it will run the exact same process. Every time.

These logical steps can be deployed on a daily refresh up to 8 times a day with a pro license, or 48 times a day if the business deploys a premium capacity license.

In Power Query (the user query language used across Microsoft), within Power BI, we call this the 'Applied Steps' and it's visible to the user within the right-hand panel of a query editor. This brings me onto the next power… Peer review and explainable processes.

Screenshot of the Query Settings in Power BI

Visible and trackable

In traditional Excel style, you're faced with a spreadsheet, with an unexplainable number of formulas, each referring to an alternative cell, another formula or another calculation in a spreadsheet and look up. With Query editor, in Power BI, not only can we physically see and jump between various applied steps, but we can physically add code comments. This makes it easier to review with other auditors/accountants. It also helps you understand your own workings, in the future.

Screenshot of code comments in Power BI

This increases efficiency when changes occur. Whether that be regulation changes to the amount or, the way categories are calculated or, a redefinition of a particular spend. Power BI's query editor allows for 'dynamic' processing based on the data as opposed to scripted hard-coded formulas. When a change occurs, we can go into the applied steps, change the element we need to adapt and apply across all other processes and reports.

Screenshot of Advanced Editor in Power BI

Data Products and Client Customisation

Microsoft's 'Workspace' approach, combined with great security features such as Row Level Security (RLS), allows our businesses to deploy multiple reports with simplified, secure, and controlled methods. This gives your accountancy two powers: creating multiple reports off one dataset, and increased and traceable information security.

Create multiple reports off one dataset

If you are using Power BI and do not currently separate your published data sets and reports, do it NOW!

How? Publish with no visuals, just the dataset. Then open a new PBIX and connect to a 'Live Power BI Dataset', connecting to the previously published dataset. Only then should you build your visuals.

Screenshot of Get Data dialogue box in Power BI

Why? This approach means you can develop multiple reports from one dataset. If a change occurs you only need to change the dataset and it will reflect in all connected reports, no need to track and change every report with that calculation. This approach also allows you to give some creative freedom to your accountants to create their own reports or customise to the customer, without jeopardising the integrity and accuracy of the data.

Increased and traceable information security

In an age where a data breach is more likely to come from an employee or customer accidently emailing an attachment (just look to the NI police force as a recent example), Power BI workspaces and RLS ensure that even if a report or dataset is forwarded to someone else, the security and RLS integrity is maintained.

This is further secured if your organisation uses Microsoft Information Protect (MIP) where the level of data classification follows the data and report, even if downloaded and shared separately.

Screenshot of data hub

Now you can make it fancy

We can all look to how A.I can change, enhance, and innovate in our environment. However, the reality of the situation is that most of the clients we work with can gain an advantage and make their service more powerful but simply starting with the basics.

At Eden Smith we encourage clients to start by adopting some of the simpler practices that have faster learning curves, such as Power BI. The key to the power is not to view implementing a tool like Power BI as a technology advancement or a way to create a cooler look for your clients and internal stakeholders. Instead view it as an opportunity to apply your accounting knowledge and best practices into more automated, robust, and explainable processes that leverage technology. This way you can spend more billable time consulting with clients on how best to manage their accounts instead of on robotic admin processes.