Power BI with your accounting data

Five reasons to use Power BI for your management report

Posted by Hugh Johnson on 25-Feb-2019 08:57:18
Hugh Johnson
Find me on:

Financial reporting is one area where Power BI really comes into its own.  If you are struggling to get your management report done, apart from really good visualizations, here are five reasons why you should take a look at Microsoft Power BI.   The image below is an example profit & loss report done in Power BI.

One Page Profit and Loss - Power BI

Ad-hoc reporting

Financial reports, once published, have a tendency to generate additional questions.  Once set up, a well-constructed analytical model in Power BI will enable you to respond to all nature of ad-hoc questions that may arise from your management report.  The reason is simple. With Power BI you can inspect a complete dataset of your transactions and their related elements such as customers, products or dates, all in one go.  

All the data is together in a single relational model.  This makes it possible to look at trends and slice your data by different time periods or angles, such as customer, product, general ledger code or department / tracking code.  Do you want to group a few sales and purchase codes for a product profitability report? No problem.

Dates table relationships to Product Sales, Sales Orders, Purchase Orders, Periods and TransSplits

Combine multiple data sources

Your accounting software may be at the centre of all your management report, but for most businesses it is not everything.  You may have critical operational data elsewhere in systems for point of sale (POS), staff rostering, customer ticketing, inventory management, purchasing,or customer relationship management.

Get Data in Power BI

One of the great strengths of Power BI is its ability to pull data in from a wide variety of sources into a single model.  This enables you to drill down, for example, on a staff costs line to see how that was made up by staff category, member and hours.

Fine-grained reporting

It is one thing to look at an accounts receivable report and see an exceptionally high Days Sales Outstanding number.  It is another thing to be able to drill down and see the individual transactions and their details. Generally, when working with SME data, it is no problem to include the most fine-grained level of detail into your Power BI data model.  If your data model includes all of your transactions down to their line-item level, then you will have the ultimate flexibility for your reporting. You can reconstruct any view of your P&L or Balance Sheet and slice your reporting by any angle.

Invoice line item details in Power BI

Trend analysis

How do you track trends at the moment?  Are you saving weekly or monthly snapshots of your business in Excel and then piecing together some kind of trend analysis?  This approach can be error-prone and with it you also lose the fine-grain detail of your data. It then becomes impossible to drill down and understand the details behind any trend.

Rolling 12 month trends in Power BI

A Power BI dataset typically includes a complete set of your transaction line items, their dates, and how they relate to other elements such as customers, suppliers, products and your nominal ledger.   With this you can easily create reports to track rolling trends for just about any aspect of your transactions. In addition, you don’t need to wait a few months to start seeing a new trend that you manually record period by period.  

With all of your transactions in the same model, you can easily summarise your data by any time period for trend analysis, for example:

  • Daily, weekly, monthly, quarterly, annually

  • Rolling 28 days, 91 days, 52 weeks

  • YTD vs same period last year

  • This month vs last month

  • Last month vs same month last year

Automation

Finally, with every reporting cycle, you probably end up doing the same things;

  • Extracting data from your accounting software and maybe other systems.  

  • Manipulating the data to get it ready for your reporting

  • Importing the new data into your reports

  • Cross-checking the numbers

  • Distributing the reports

Ok, so Power BI does not yet know out of the box how to cross-check your numbers for you, though can create your own measures to look for potential problems or issues in your report.  There may or may not be an off-the-shelf way to get the data out of your accounting software or other systems, but if you can get it into Excel or CSV files you can automate the rest.

Scheduled refresh in Power BI Pro

 

 

 

 

Topics: Power BI Desktop, Power BI Pro

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts