Power BI with your accounting data

The 5 Ws of Power BI for SMBs and accountants

Posted by Hugh Johnson on 01-Apr-2018 15:07:12
Hugh Johnson
Find me on:

In this post I use the "5 Ws" to explain from the perspective of a small to medium sized business the overall concept of Microsoft Power BI.

 

Power BI

  1. What is Power BI?
  2. Who is Power BI for?
  3. When is Power BI used?
  4. Where would you use Power BI?
  5. Why would you use it? 

 

 

1. What is Power BI?

Power BI is suite of integrated software products from Microsoft designed to support creating and sharing data analysis.  Think of it perhaps as Excel on steroids (which is not a bad analogy since it has really come out of the Excel stable).  It is classed as a business intelligence system and it provides within the one family, all the tools that you need to:

  • Gather data from multiple sources
  • Combine, enrich, summarise and otherwise analyse that data
  • Create stunning interactive visual representations of your analysis
  • Share your analysis across your business through published reports, dashboards and apps
  • Automate refreshing your published reports and dashboards as your business data changes

Power BI has many competitors, but it is very quickly growing mindshare across the business intelligence community.  I recently did some analysis of Google Search trends, and almost half Google Searches on the topic of Business Intelligence were related to Power BI.  As an illustration, the video below shows an example customer analysis and sales forecast produced from Sage 50 sales data.  The report is identifying where the sales team may need to be proactive in the coming month.

 

The main two Power BI components relevant to a small to medium sized business are:

  • Power BI Pro - a cloud-based service for publishing, sharing and accessing dashboards & reports.  You can do some quite extensive customisation of a report within the constraints of the underlying data model that was created using Power BI Desktop. 

There are other components and options available that are aimed more towards larger enterprises and I will not go into them in this summary blog.  Power BI is increasingly being used in small to medium sized businesses in particular for sales and sales-related reporting.  Take a look at this example that we have built for Accounts Receivable reporting from Sage 50 Accounts:

Accounts Receivable Reporting Back to top

 

 2. Who is Power BI for?

Microsoft targets four main types of user:

  • The Analyst - who will use Power BI Desktop
  • The Business User - who will use Power BI Pro
  • The IT/Data Compliance Officer - to look after making sure that data is available and only available to those authorised to access it.
  • The Developer - who may want to embed charts or analysis into his or her app.

In the context of a small to medium sized business and accountants in practice, the first two are the most relevant.

The Analyst

The Analyst uses Power BI Desktop to prepare reports for publishing to Power Pro for the Business User.  The Analyst does not just create reports, but also sets up an analytical model, or dataset, that contains the raw business data, relationships in the data and additional calculations relevant to the business.  This sounds complicated, but rather like Excel it doesn't have to be to begin with.  It is easy to start small and if you are someone who is comfortable with numbers and Excel worksheets then you should be able to pick this up no problem.  One of the beauties of the system is that a well-constructed model gives the Business User a lot of scope to customise his or her dashboards and reports without the danger of screwing things up.

The Analyst Accountant

In my opinion, an accountant in practice could take on the Analyst role for a small to medium sized business really well, especially if the data to be analysed is the company's accounting data.  There are economies of scale here since one accountant serving many companies using the same accounting software such as Sage 50 could use a single model to serve many clients. 

The Business User

The Business User can access Dashboards and Reports published to Power BI Pro on the web, or via the Microsoft Power BI native apps for smartphones and tablets.  The Business User can customise the reports and dashboards created by the Analyst.  He or she can even create completely new reports based on the dataset published by the Analyst.

Back to top

 

3. When is Power BI used?

A well set-up solution will be used in two scenarios:  regularly and ad-hoc.  One of the nice things about the datasets in Power BI Pro is that you can schedule automated data refreshes.  If your accounting data is your dataset, why not schedule a refresh every night so that you wake up to an up-to-date view as of the close of business yesterday?  


Excel is so often the tool of choice for performing some quick, ad-hoc data analysis.  Unfortunately though it is often no so quick, and there may be quite a time-consuming exercise to source and process your data before you can start with your analysis.  Now if you have previously taken the trouble to build out your data model in Power BI Desktop, then running up a quick piece of ad-hoc analysis is really easy and fast.  Adding this new analysis to your standard report set is also incredibly simple. 

Back to top

 

4. Where would you use Power BI?

One of the nice things is that you can access your dashboards and reports on  the web or via the native Microsoft Power BI apps for smartphones and tablets.  So as long as you have an internet connection you can use Power BI anywhere.  It is simple to customise the experience for smartphones and tablets, perhaps to concentrate on delivering important KPIs.  On some platforms you can even set up alerts to notify you if a critical measure goes above or below a certain threshold.

Back to top

 

5. Why would you use Power BI?

Three reasons why you as a Business User would use it:

  1. To receive proactive notifications about your company's performance on your iPhone via the Power BI app.  If you are wanting to track a particular number, such as daily sales or margin, you can set up these measures with alerts in your iPhone app to tell you when certain goals are met, or if there is deviation from a certain expected range.

    Setting up Power BI notifications in your iPhone 
  2. To grasp an instant overview of what is happening in your business through stunning and engaging visualisations of your company data.  Power BI does this really well.  There are dozens of visualisations to choose from and even perhaps rather mundane yet bewildering reports such as an Aged Debtors report can be turned into something more understandable and actionable with the correct visuals. 

    Power BI Visuals
    Through these visualisations you can pack a lot of very intuitive information into a single page, conveying the most important information really quickly but still with the ability to drill down to the details.  Here are some examples:
  3. To spot and quantify new trends in your business as they happen.  If your year-on-year sales are up it is easy to feel good about your business.  Perhaps too easy.  What if the underlying trend had reversed in all or some of your business?  It may still take a while before this showed up in your weekly, monthly or quarterly sales figures.  Set up correctly, tools like Power BI can help you to spot and quantify underlying changes much faster than more traditional business reporting.  Take a look at the example below.  It is a customer sales heat-map that analyses buying patterns over the last year to identify anomalies - good or bad.  It identifies sales in the last 28 days that were more than one standard deviation above or below the average 28-day sales for that customer.  It is automatically updated every day and acts as an early-warning for changing customer buying patterns.  It is a very simple way to help focus discussions in your sales meetings or with your customers.

    Customer sales heat-map produced by Power BI

The image above is a visualistion from the Accounting Insights for Sage 50 content pack.  You can try out a demo account with this visualisation here.

Three reasons why you as an Analyst would benefit from Power BI:

  1. To combine related data from separate sources into a single model.

    If you have several tables of related information then within Power BI Desktop it is very easy to define a relational model so that you can analyse this data together.  

    Power BI Desktop Modelling View
    Adding a Dates Table to your model enables you to combine and visualise data that are not directly related along a common dates axis.  For example if you plotted cumulative sales and cumulative receipts on the same graph, it would be very easy to spot trends for improving or worsening collections activities.

  2. To make otherwise complex data transformations very simple

    In the example in the video below, I have table extracted from Sage 50cloud Accounts that contains textual information about the financial year and its periods.  I wanted to extract key pieces of data, such as the start and end dates for each period (in date format) and the number of days in each period.  I tried this first in Excel using various text manipulations.  It worked, but was a very protracted and laborious process.  I also tried it in Power BI Desktop using Query Editor and it was extremely simple to do.   The video below shows how I did this, and will give you some idea of how you can transform data in Power BI without writing any complex code.



  3. To create, publish and share your reports, including your Excel spreadsheets in a very open, yet controlled way.

    Power BI provides superb facilities to publish and share your reports, even your Excel spreadsheets or just elements of them.  You can control very tightly who has access to what dashboards and reports, you can schedule data refreshes and you can add row-level-security to enable for example a sales rep only to see his or her customers.  You can even publish into a Power BI dashboard the value of a single cell in Excel and have this refreshed automatically!  

 Back to top

If you are using Sage 50 Accounts, we make it very easy for you to get started with Power BI so that you can analyse your Sales, Purchase and Financials data.  Why not give it a try? 

Use free for 30 days

 

Topics: Sage 50, Power BI, Sage 50cloud, Accounting, Reporting, Power BI Pro, Power BI Desktop, accountant, accountants

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