Division Profit & Loss with Budgets using Xero Tracking Categories + 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 ?
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.
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
In Xero you can enter an Overall Budget or Budgets by Tracking Category.
A tracking category budget is created by entering a budget description, selecting the Tracking Category ( eg Event, Venue, Cost Centre) and then choosing which Tracking Option it is for ( eg. Scanton, Miami, North, South, East) and then the user can enter budget figures for each GL account and period in Xero.
The Profit and Loss reports in Xero can be setup to run for one or more Tracking Category and Tracking Option eg. A Profit & Loss for Tracking Category "Event" Option "King Lear".
You can then select a Budget column to appear in the Profit & Loss in Xero by selecting the name of Tracking Category Budget. However you cannot have multiple columns of Tracking Categories or have their Budgets in columns nor can you use Tracking Categories Actuals and Budgets in Group Totals.
For simple situations this functionality may be adequate but if there are many Tracking Category values it can become difficult to maintain budgets and reports.
If for example you have 6 Cost Centre tracking options and you want to report on these in Xero then you will need to create 6 budgets in Xero and define 6 Profit & Loss reports, each filtering for the particular tracking category option.
For more complex situations such as Divisional reporting the BI4Cloud solution can provide more extensive flexibility and capability.
The examples at the head of the article show multiple columns of Tracking Categories and Options and Tracking Category Levels. These can be compared to rows of other Tracking Categories and Tracking Categories Levels and they can contain both Actuals, Budgets and Variances as well as Reforcecasts, Revenue, COGS, Expenses Profit and Margin %.
There are several options available
- Enter the Overall Budget in Xero and read this into Bi4Cloud ( No Divisional Reports )
- Enter Tracking Category Budget in Xero ( Divisional Reports )
- Enter Tracking Category Budget in Bi4Cloud
Firstly in Bi4Cloud you need set the company file to use Tracking Category budget by selecting the toolbox icon and then choosing Tracking Category. In specific versions of Bi4Cloud allow you to choose from
- Use the Overall Budget from Xero. Set Bi4 Monthly Budget to No
- Use Tracking Category Budget from Xero. Set Bi4 Monthly Budget to Tracking Category - Xero
- Enter Budgets in Bi4Cloud and Budget by one category by specifying TC1 or TC2
- Enter Budgets in Bi4Cloud with a combination of both categories in a hierarchy you specify as either TC1+TC2 or TC2+TC1
When Divisional Budgets using Tracking Categories have been entered in Xero and these have been selected to be the active budget then there can be both Budget and Reforecast budgets. With this option set then the Tracking Category budgets with '*Budget*' in their name will be the Budget values and Tracking Category budgets with '*Reforecast*' in their name will be the ReForecast value.
Xero permits a completely free-form entry of budgets. You can enter budgets that overlap on the same tracking category and option for the same date. When this is encountered the import will determine the budget to import by looking for the Tracking Category which has the most Tracking Options budgeted.
Example :
- You have Tracking Categories Region and Sales Rep.
- Region has options [North|South|East|West] and
- SalesRep has options [Smith|Jones|Hussien].
- Budgets have been entered for East, West, and North (3x) and
- Budgets entered for Smith, Hussien (2x).
The system counts more budget entries for the options of Region and so this will be the Tracking Category imported. If duplicates exist for the same day then only the latest dated entries will be selected. This prevents data duplication. This technique was chosen rather than the alternative the entering a list of budgets for each Tracking Category for each year and maintaining it each and every year. Name your Budgets with a 'Budget' and 'ReForecast' in it their name and you're done.
The Direct import feature may need to be enabled on request by contacting support@bi4cloud.com
If the Budgets source is Xero then a manual refresh will need to be run to make your selection the active budget for reporting.
If you selected budget entry into Bi4Cloud then to enter or load Tracking Category budgets go to Cool Stuff Features (available in BI4Cloud 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.
Legacy Data - Alternative options to Tracking Categories
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.
Sign up for your free 14 day trial at Bi4Cloud.com and try it with your Xero company today.