Excel may be the finance professional’s bible for financial reporting, but it does have limitations.

Using Excel for month end can be laborious and time-intensive. It involves:

• manual data extraction from various sources and finance systems,
• data collation,
• data cleaning (fixing errors, filling gaps) and
• mapping using VLOOKUPS or calculations to link to different data points. 

In addition, Excel can freeze or slow down if the spreadsheet is particularly large – and it often is.

Duncan Boyne, Senior Power BI Consultant at CPiO, one of the UK's longest-standing Sage partners, describes month-end as a ‘fairly scrappy affair’ when he worked at a previous in-house role.

“I inherited Excel files held together by a combination of macros, pivot tables and what felt like pure luck,” he says. “Using Power BI, month end became calmer, faster and far more reliable. Instead of firefighting spreadsheets, the finance team could trust the numbers and focus on understanding what was changing and why."

Power BI modernises how data is consumed, he explains. It creates scalable reporting which contributes to business growth. “This reinforces how powerful Power BI is when it’s treated as a long-term platform rather than a one-off reporting tool,” Boyne explains.

Five ways Power BI improves month-end

“If there is sensitive data in there, it can be forwarded to anyone,” he says. “But with Power BI, links to reports are sent to specific users. Any subsequent adjustments to a report’s underlying data automatically update, avoiding the need to reissue, and users can only view and interact with the reports. They cannot adjust data which reduces the risk of accidental human error.”

How to use Power BI for month end

Invest time in data cleaning

Stockdale suggests investing time upfront in automating data cleaning processes which can save hours of repetitive work each month.

“Financial professionals spend a lot of time on manual data cleaning,” he says. “Spending time setting up data transformations in Power BI once, means you don’t have to do it again, it’s set up for future use.”

Organise ‘measures’ into tables

In Power BI, a ‘measure’ is a custom calculation created using a DAX formula which is dropped into a visualisation and calculates data based on the report’s settings. Measures are shown by a calculator symbol in the menu.

Stockdale recommends organising these measures into separate tables – or categories – as there are likely to be dozens in any given report or model. Here’s how: 

Break up reporting

Don’t overcrowd a report page with too much visual data – it can make it difficult to interpret and understand, says Stockdale. Instead, break the reporting into smaller chunks, to keep information concise and easy to understand.

Build reports for two audiences

Build reports for the finance team first, CPiO’s Boyne suggests: “They are the people using the data day in, day out. Give them the ability to drill down, validate numbers and explore variances.”

Once the detailed reports are in place, the management dashboard ‘largely writes itself’. He explains: “Reuse the same calculations and surface the key figures as a clear narrative for the C-suite, rather than creating a separate set of numbers.”