Division P&L Reporting with Budgets using Xero Tracking Categories and Bi4Cloud
Create your own Powerful Reports
- Use Tracking Categories (TC) on Rows and/ or Columns in Pivot Reports.
- Group Tracking Options together using Virtual TC Header Levels in BI4cloud
- Analyse | Pivot | Group by | Filter by both Tracking Categories or TC Headers
- See Division Reporting in Action
- Create incredibly flexible P&L reports
- Create powerful Sales reports
Single click to create a P&L by Tracking Category or by P&L by Account
Display Transaction Detail
How easy is this to implement ?
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.
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.
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.
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.
To enter or load Tracking Category budgets go to Cool Stuff Features (available in BI$Cloud Enterprise 5 user version).
Select Tracking Category Budgets
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.
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 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.
You can then run Profit and Loss Reporting as before but now the Budget information is available at Tracking Category and Tracking Category Level.
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.
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.