Implement Tracking Category Budgets in Xero using Bi4Cloud
Tracking Category in Transactions
Xero provides categorisation of transaction activity with a feature of Tracking Category in transactions. There can be two separate tracking categories and these can have up to 100 values. This is represented schematically below. ( See also Analysis Code comparisons )
Tracking categories can be used for salespeople, regions, activity centres, divisions, departments etc. in commercial organisations and programs, funding sources and grants in not for profit organisations.
Tracking categories are setup within Xero in Accounting > Advanced > Tracking Categories
In our example we've created a tracking category for Region and Salesperson. We have named these Region_TC and Salesperson_TC so as to highlight the usage in the examples. The tracking categories can be given any name although we have avoided putting spaces in names.
Transactions in Xero can have tracking categories against each transaction line. Tracking Categories are entered at line detail
Bi4Cloud provides reporting of these tracking categories in the Analyse Sales report. This permits reporting by all the transaction dimensions in various formats including Pivot by each of the tracking categories
Sales Pivot By Tracking Category 1 vs Tracking Category 2
Sales Pivot Tracking Category vs Month
Sales Pivot Tracking Category with Group by Month
Bi4Cloud provides a powerful ability to generate Profit and Loss Reports by these tracking categories. Here are some examples.
Profit and Loss Group By Tracking Category pivot by Month
Profit and Loss Group By Tracking Category 1 pivot by Tracking Cataegory 2
Tracking Categories in Budgets
Xero provides entry of Budgets by Tracking Category. A budget tracking category is selected and the user can enter budget figures for each GL account in Xero.
Although these budgets are in the Xero company file there is no access to the Tracking Category Budget from the Xero API.
Add-on solutions like Bi4Cloud cannot access this information through the Xero API.
To provide a solution here we have a Monthly Budget capability build into BI4Cloud to cater for inputing Tracking Category budgets.
This is accessible in Bi4Cloud in the Cool Stuff Features (Enterprise 5 user version).
Select Tracking Category Budgets
You will see each of the tracking category you have defined in Xero. Click through against a tracking group and each tracking category will be displayed. Choose the Budget link in red.
The GL Accounts are then displayed and you can enter monthly budget figures or alternatively Export and Import from a spreadsheet. See controls in red
Once complete press Save Changes and then Close and a small bar chart indicator will show your entries.
Once all budget entry is complete press Save and Exit and the budgets will combine with the Xero actuals (note we don't write them back into Xero ). You can also press Refresh as this also merges the Xero company data with the Bi4 Budgets.
You can then run Profit and Loss Reporting as before but now the Budget information is available at Tracking category level. Here are a few examples showing P&L Actuals and Budgets by tracking category.
Profit and Loss Actuals Filter by Tracking Category
Profit and Loss Actuals Budget Group by Tracking Category
The above screenshot shows the Analyse By and Group By dimension selections that produced the P&L Report.
All the standard Bi4Cloud facilities of choosing Analyse, Group and Time dimensions are available. You also have the ability to drill through to detail level on any reports.
Reporting Functionality with Tracking Category
Bi4Cloud uses a business intelligence (BI) tool to implement reports. This BI tool summarises your data in various dimensions of the underlying Accounting system. You can choose the dimension to Analyse by, Filter by or Group by which gives you complete report flexibility.
This is represented schematically here.