Xero : Tracking Category for Reporting and Budgets

xero-logo-0DE623D530-seeklogo.com.gif.pngDivision P&L Reporting with Budgets using Xero Tracking Categories and Bi4Cloud

Create your own Powerful Reports

Screen_Shot_2021-09-08_at_2.59.29_pm.png

Single click to create a P&L by Tracking Category or by P&L by Account

Screen_Shot_2021-09-14_at_1.34.47_pm.png

Display Transaction Detail 

Screen_Shot_2021-09-14_at_1.35.10_pm.png

How easy is this to implement ?

1) Setup Xero Tracking Categories with Virtual Headers 

2) Create Tracking Category Budgets

1. Setup Tracking Categories

Use Xero's two Tracking Categories and setup up to 100 tracking options. (See also Analysis Code comparisons).

Tracking Categories are analysis codes in Xero and can be used for things like:

  • salespeople, regions, cost centres, divisions, departments etc. in commercial organisations; or
  • 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 one Tracking Category for Events and one for Venues.

Using BI4Cloud you can even create Virtual Header Levels of Tracking Options using a ' - ' in the names and have up to 4 levels.

Screen_Shot_2021-09-16_at_7.58.52_am.png

Tracking Categories are then allocated in Xero to each line of each Transaction. In Xero you can rename existing Tracking Options to take advantage of the BI4Cloud Virtual Header feature but you will need to force a Full refresh of the data to BI4Cloud for it to take effect.

Screen_Shot_2021-09-09_at_6.10.03_pm.png

Once refreshed Bi4Cloud provides reporting of these tracking categories in all reports. This permits reporting by all the transaction dimensions in various formats including Pivot or Filter by each of the Tracking Categories. The video below shows how to use several of these options in action.

Division Reporting  in Action

 

2. Tracking Category Budgets

Xero allows you to enter Budgets by Overall or by Tracking Category.

A tracking category budget is input for the Tracking Category and Option(s) and then the user can enter budget figures for each GL account in Xero.

Screen_Shot_2021-09-15_at_2.33.31_pm_1.png

Within Xero the Profit and Loss reports can be setup to run  for one or more tracking category and options eg. P&L for  Tracking Category "Event" Option  "King Lear".

You can add a Budget column to the a P&L in Xero by selecting the Tracking Category Budget. You cannot line up multiple Tracking Categories and their Budgets in columns nor can you use Tracking Categories in Group Totals.

So if you have, for example, 6 tracking codes you will need to create 6 budgets in Xero and 6 P&Ls each filtering for the particular tracking category option. 

For simple situations this functionality may be adequate but if there are many Tracking Category values this can become difficult to maintain and keep under control.  

For more complex situations or handling many tracking options the BI4Cloud solution shown above can provide more capability. The examples at the head of the article show multiple columns of Tracking Codes and Tracking Code Levels. These can be compared to rows of other Tracking Codes and Tracking Code Levels and they can contain both Actuals, Budgets and Variances as well as Revenue, COGS, Expenses Profit and Margin %.

To provide this functionality in a manageable way we required the budget entry process to support this and so we built an multiple budget capability and do not directly import the budgets from Xero.

Although Bi4Cloud doesn't automatically import Xero Budgets you can get them into Bi4Cloud by exporting from Xero budgets to excel and use Bi4Cloud excel import.

To provide a flexible solution  Bi4Cloud has a Monthly Budget capability build in to cater for inputting Tracking Category budgets.

Firstly you need set the company file to use Tracking Category budget by selecting the toolbox icon and then choosing Tracking Category. Newer versions of Bi4Cloud allow you to choose to budget by one category by specifying TC1 or TC2  or a combination of both categories in a hierarchy you specify as either TC1+TC2 or TC2+TC1.  After choosing press the Disc icon to save.

 

Screen_Shot_2021-09-15_at_5.46.43_pm.png

To enter or load Tracking Category budgets go to Cool Stuff Features (available in BI$Cloud Enterprise 5 user version).

 Screen_Shot_2018-06-19_at_4.20.56_pm.png

Select Tracking Category Budgets

 Screen_Shot_2018-08-07_at_11.57.53_pm.png

You will see each individual tracking category you have defined in Xero. Click through against a tracking group and each tracking option will be displayed. Choose the Budget link in red.

Screen_Shot_2021-09-15_at_5.58.19_pm.png

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

Screen_Shot_2021-09-15_at_6.06.35_pm.png

Once complete press Save Changes and then Close and the previous screen will display with 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.

 Screen_Shot_2018-06-19_at_4.32.09_pm.png

You can then run Profit and Loss Reporting as before but now the Budget information is available at Tracking Category and Tracking Category Level. 

 

Legacy Data

You may have a situation where re-coding all the system transactions to use tracking categories is too BIG a job. If you have separate GL accounts for your divisions then you can use Bi4Cloud GL Custom List Tags to categories each account and then use these as analysis dimensions.

See GL Custom List Tags

 

 bi-logo-trans.png Reporting Functionality with Tracking Category

The BI4Cloud Difference

Bi4Cloud uses a Business Intelligence (BI) tool to implement reports. The BI allows YOU to create your own flexible reports beyond what we have foreseen.

Our BI tool summarises your data in various dimensions of the underlying Accounting system. You can then choose these dimensions to Analyse by, Filter by or Group by which gives you complete report control and flexibility.

This is represented schematically here.

Screen_Shot_2018-06-20_at_11.15.15_am.png

Sign up for your free 14 day trial at Bi4Cloud.com and try it with your Xero company today.

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.

Powered by Zendesk