How do I use the Advanced Management Report template?

Learn how to use the DataDear template ‘Advanced Management Reporting’ which includes a set of management reports customisable with reporting sections and groupings.

This template helps you build a set of Management Reports (Profit & Loss and Balance Sheet) for your organisation or group of organisations over multiple periods. This will help you analyse and understand how a business or group of organisations is evolving over time.

:bulb: This is a paid Xero template which can be downloaded from the Templates Library. Template is designs for both single organisations and for a group of organisations / companies. It can be trialed and refreshed a few times before purchasing.

:bookmark: It is recommended to familiarise yourself with the following articles:

Understanding the Template

Familiarise yourself with the template before getting started.

Tab and cell color legend

To help users quickly identify which tabs and columns need to be or can be populated and edited, we have created the below legend.

Colour Cell Name Description
Cell - Light Blue Xero DataDear Feed Any cells coloured in light blue (Xero Blue) is information from your Xero organisation which will be populated via DataDear. These can be found in the Accounts, Profit and Loss and Balance Sheet tabs as you will be required to run these reports.
Cell - Dark Blue Manual Input Any cells and tabs coloured in darker blue require manual input. For example the Defined Categories tab [change colour of tab], where you can choose the categories you need and in the Accounts tab to map accounts using the chosen categories.
Cell - Grey Calculated Field Cells coloured in grey require NO manual input. These cells are formula driven and offer important indication to which accounts might still need mapping and how other accounts have been mapped.
Tab - Grey Notes & Controls Tabs with this colour require manual user input and contain information and checks that the user will need to achieve the desired reports.
Tab - Blue DataDear Feeds These tabs contain Data Dear reports which will be refreshed when refreshing the report.
Tab - Dark Blue Reports Tabs with this color are the Reports tab, the end results.

Parameters & Notes Sheet

This sheet is password protected but there is no password - this is done to prevent any unnecessary tampering with the setup. Can be easily unlocked from the Review tab in the toolbar.

This is where you define parameters to be used across the reports:




Date: This is last date of the reporting period. The default period for the report is set to capture the entire year of 2019. However, this can be changed with ease. In the Date cell on the right, input the final date of the reporting period you desire.

Conditions – Period: Here the user has 3 options to input. “Year to date”, “For the month” or “7 Days to Date”. The default in the template is “For the month” which works well for getting reports for every month for an entire year.

Comparative Periods - Profit and Loss Periods: Here the user has 4 options. “Previous Weeks”, “Previous Months”, “Previous Quarters” or “Previous Years”.

Comparative Periods - Total Periods: This is number of comparative periods to compare. 1 means only current period with no comparative periods whilst 12 can be used to compare months over a year.

This sheet also contains:

  • Step by Step section explaining the necessary steps to get started

  • Insert Controls section - these are optional (but recommended) controls that you can build using custom formulas for your model to ensure the data add ups correctly. Such as Balance Sheet Assets are equal to Liabilities and Equity.

  • Account Mappings - shows the number of accounts that haven’t been mapped for report section, account group and sub-group.

  • Notes contains additional important information to know before using the template.

Reporting Groups Sheet

This sheet contains the reporting groups which you can modify according to your reporting needs. The template already contains sections and groupings for both the Income Statement Mappings and the Balance Sheet Mappings.

Account Reporting Section
Any changes to this list CAN impact your calculated items in the Pivot Table e.g. Gross Profit & EBITDA. Please refer to this article to learn how to modify your calculated items.

Account Grouping
The report is currently designed to group your accounts through this list. You can change the report grouping mechanism as explained further in this article.

:information_source: You are free not to use all 3 categories provided in this sheet.

Once you’re happy with your groupings, and the Parameters in the previous tab, follow the steps below to refresh your template with data from Xero

  1. Login to DataDear

  2. Select the organisation or a DataDear group (multi-currency groups are currently not supported)

  3. Use the Refresh > All sheets

:warning: The workbook might take a few minutes to load depending on the size of date range chosen and the organisation or group of organisations chosen.

Accounts Sheet

When opening this sheet for the first time, it will contain accounts from a DataDear test group. On refreshing all sheets as explained in the previous step, these will be replaced with accounts from your selected company/group.

  1. For columns M, N and O, use the drop-down to select the respective sections and groupings for each account code. The drop-downs are defined in the Account Grouping sheet.

  2. Keeping in mind that the list of Accounts is a full list of all the accounts of the organisation or group of organisations, not all accounts need to be mapped. Certain accounts might be empty or archived and no longer being picked up by the P/L and B/S. The “NEED Mapping” column is where you can define which accounts needs to be mapped.
    image

  3. Using the auto filter in the headers of the sheet can help expedite the process of mapping the necessary accounts.

  4. Once you have completed the mapping go through the P/L and B/S and check the mapping has filtered through.

Profit & Loss Sheet

This Tab requires no manual input, but does require reviewing. After the mapping has been done in the “Accounts” sheet, it is good practice to go through Profit & Loss grouping categories and seeing that the grouping categories have filtered through.

image

Balance Sheet

This Tab requires no manual input, but does require reviewing.

Report Cover Sheet

This sheet is populated automatically after inputting the relevant dates in the first sheet “Parameters & Notes”. You may also enter your Customer or Practice logo so this will feature in the report cover.

Report 1 - Profit & Loss Sheet

:bulb: This report is built using Excel pivot tables and a basic knowledge of pivot tables is required if you plan to modify the report.

To refresh the data in this report right click on the pivot table click Refresh. All data including the slicers at the top of the sheet will be refreshed.

image

The slicer at the top of the screen are an easy and quick way to filter through your data. Clicking on the individual slicer will show you the data related to that slicer. To show all the data just click at the top right corner of the slicer window and this remove all filters from the data displayed.

image

Report 2 - Balance Sheet

:bulb: This report is built using Excel pivot tables and a basic knowledge of pivot tables is required if you plan to modify the report.

To refresh the data in this report right click on the pivot table click Refresh. All data including the slicers at the top of the sheet will be refreshed.

image

1 Like