5 Best Practice Bookkeeping Tips for when you are Reporting in Power BI


#DISCLAIMER – This article is written from the perspective of an Accounting Firm servicing the Small & Medium-sized Enterprises (SME).


When you enter the world of Business Intelligence, it quickly makes you review your clients bookkeeping practices. It could be cleaning up existing poor practices or thinking more strategically about what reporting the client needs downstream. You will realise, bookkeeping practices of old, are no longer hidden on a desktop version of accounting software. Welcome to the cloud, and welcome to live data.


Client bookkeeping can come from several sources;

  • accounting firm has a bookkeeping division internally

  • you work closely with a bookkeeping business

  • client has their own internal bookkeeper

Depending on the bucket your client is in, will depend how much improvements you can in fact deliver. The latter being the most difficult to implement change with.


My 5 Best Practice Bookkeeping Tips for when you are reporting in Power BI.


  1. Post inter-entity transactions to separate account codes


When working with consolidated entities, it is very easy to bang them together in Power BI and have a view of the profit and loss. What you quickly pick up in doing this, is that revenue and expenditure are both overstated due to inter entity transactions.


The quickest and easiest way of removing them from the equation is by having them in a separate account code. Adding in the ‘Description’ of the account code, a naming convention like ‘Inter Entity’, can then allow you to slice your chart of accounts. Rules in Power BI, like IF the account description contains the words ‘Inter Entity’ then label them as this form of transaction. A filter will remove them from the view and you can discuss consolidated performance with you clients in a live and dynamic way.


  1. Ensure you use the Google Maps API when adding new contacts


Inside Business Intelligence, having an understanding of the location of contacts is extremely valuable. It is very common to see a contact list in an accounting software with gaps and errors. By making the effort to use the Google Maps API, it guarantees correct placement of the address line items (Address lines, State, Country, Postcode, etc). It opens the door to much better reporting. Sales by suburb. Average quotes accepted by suburb, etc.


  1. Use Tracking Categories


There is so much upside in strategically sitting down with a client and deciding what type of information they would like to report on. Some of the common Tracking Category items I see are regions and types of sales specific to that business type.


Having this custom form of reporting can open the door to actionable KPIs for the client.


  1. Use quotes in operational systems


Encourage your clients in service based businesses to use the quote system and educate them on converting these quotes to jobs. That may be in Workflow Max, SimPro, or another operational system. The value in doing this is significant. Being able to have a view of the sales funnel, conversions, and most importantly how well you are quoting to actual. It can prevent clients taking on unprofitable jobs with a knowledge like this.


  1. Automated Data Entry


You should have most of your client base on an automated data entry program. Examples are Receipt Bank and Hub Doc, but there are more on the market.


The efficiency gains are incredibly valuable, but from a Business Intelligence perspective, it opens the door to live data. You can see where your client sits materially at any point in time, because of programs like this, coupled with automatic reconciliations of bank feeds.


BONUS TIP – Clean up your client data


Go through client contact lists, chart of accounts and ensure alignment with consolidated groups.


You do not want duplicate customers and suppliers in the books. Review the chart of accounts for correct assignment to expense/overhead/direct cost/revenue/sales. Review consolidated entities for consistency on the chart of accounts. Quality naming conventions ensure when consolidated, they go in the same accounts. Validity of data adds reliability to the reports.