When DataSights met James Howlin, director of Cake Accounting, he was looking for a Xero Reporting solution to have more visibility over his client’s numbers, to provide more timely and proactive advice. As a subscriber over the years using a number of Xero Reporting Add-Ons, James had a clear vision of his requirements but had only found limitations in his reporting journey up until this point.

James contacted us to get hold of our “Free Excel Xero Download Tool”. The limitation with this tool, is that it directly downloads Xero data from Xero’s API for only one Xero File at a time. After a quick chat with James, we quickly ascertained that what his problem needed was not an Excel plugin, but rather multi-file Excel automation combined with customisable KPIs sent to clients over e-mail and bespoke PowerBI reports embedded in a white labelled reporting portal. Using this combination James could:

  • Access all his clients Xero data and add extra datasources to the Xero data
  • Use built in Excel functionality to view any data
  • Use built in Excel functionality to view cashflow actuals + AR/AP
  • Easily create customisable KPI dashboards customisable per client
  • Easily create fully customisable PowerBI advisory reports, where his clients only see their data, but he can easily view all the data
  • Easily create internal use reports for operational efficiencies, such as when clients have last reconciled their data
  • Send clients daily emails of KPI dashboards
  • Easily onboard new clients

 

DataSights split out the solution into three distinct areas;

Step 1 – Excel Automation

Step 2 – Report KPI Automation over e-email

Step 3 – Interactive Insights in a white labelled reporting portal

 

Step 1 – Excel Automation

To investigate numbers, build excel based reports, automate existing reports. These were a few of the requirements solved with this product.

Using built-in Excel functionality James is able to access all his clients data in Excel with the click of a “Refresh All” button. All the raw data is accessible so it was invaluable in building up the Profit & Loss and Balance Sheet reports, which use the full JournalLines history NOT just the respective report figures from Xero.

Also available is a cashflow actuals plus Accounts Receivable & Accounts Payable data view. Because the raw data is used whether it is Spend/Receive Money bank transactions OR PaymentsOnAccountsReceivable/Payable OR Invoices/Bills owed or payable, the ability to drill down to the underlying data and pick up invoice numbers for example is invaluable in understanding the numbers behind a cashflow forecast.

 

Excel view

You can access ALL your clients Xero data using built in Excel functionality

Filtering Excel

Simply click “Refresh All” and easily switch between Xero files. No more “Get and Transform” csv files

 

Step 2 – Reports and Dashboards over e-mail

Developing the reports and dashboards was the second step. Armed with a list of KPIs and reports provided by James, DataSights then went to work creating views on his clients’ data that would help guide each of the businesses along the growth path that James sets out for them.

Alongside the standard numbers reporting of

  • Actual vs Target revenue
  • GP%
  • NP%
  • Receivable / Payable days
  • Total Cash

James also included the requirements of

  • Leads
  • Lead conversion %
  • Customer Acquisition Cost
  • Client Lifetime Value
  • # of New Customers

 

We wanted to have all metrics comparable Month on Month and Year on Year including each client’s full Xero history. We also wanted all reports to be interactive, leveraging the power of Power BI. Whilst there are a range of KPIs & reports James wants to automate, this initial set created enough of a value add to mark the first iteration ready for client consumption.

Once these were done we setup an e-mail schedule and the KPI dashboards are now emailed every day to Jame’s clients.

Dashboard

Revenue and Customer KPIs and Reports

Emailed Dashboards

KPI Dashboard emailed to CakeAccounting and their clients on either a daily or weekly schedule

 

One particular area of interest to note was the Leads data capture. Whilst most CRMs APIs can be used to augment the Xero data in the database, James wanted to use Microsoft PowerApps to capture lead information. This meant a simple, uniform and free mechanism for CakeAccountings clients to capture lead information thus automating crucial KPIs as part of the service.

Lead Capture

A mobile app we created that CakeAccountings clients use to capture Lead information on the go.

Step 3 – Reporting Portal

Finally came the distribution of the reports. Both internally for CakeAccounting and for their clients.

  • White labelled
  • Easy to setup
  • Slick

Thats what we wanted to deliver and we believe we have! The key features are;

  • When James logs in he can see the reports displaying all his clients data
  • When James logs in he can switch between all his clients dashboards
  • When James’ clients log in, they see the same reports but only with their data
  • When James’ clients log in, they see only their dashboard

This means James isn’t setting up ANYTHING report wise when a new client is onboard, because its always the same reports. Simply a login and permissions. But what James does setup (if he wants) is a customisable dashboard that can be unique to each client. Which looks awesome on mobile too! This dashboard can be any KPI or report from the main set of reports and is updated every 15 minutes from the database.

Embedded Reporting Portal

An embedded PowerBI dashboard. Completely customised for one of CakeAccountings clients

 

Mobile

The mobile view of a clients dashboard in the DataSights reporting portal

In summary, the journey with CakeAccounting so far has been truly enjoyable. Although the Xero Reporting Add-On space appears saturated when looking at the surface, scratching a bit deeper reveals a raft of frustrations. James saw the potential in tools like PowerBI with its Microsoft backing and self-service analytics capabilities and we see the potential in providing a set of services combined with PowerBI/Amazon Quicksights/Google DataStudio to cost effectively change the way that Xero and in fact SME reporting is done, essentially ushering in a cost affordable solution of Big Data for SMBs.