Sage 50 has the ability to consolidate a number of companies data into a ‘group’ company. As Sage admits, this has a number of limitations. This Sage Help article lists 9, the most annoying of which is that “You can't specify a date”. There are other limitations that Sage doesn’t list, but not all is lost – there are ways around these problems.
The fact that you cannot specify a date means that, in practice, it is not possible to run a consolidation as at a month end, and worse, at a year-end; the consolidation process always consolidates the current balances. Another irritation is that once consolidated the transactions in the group company are summed over all the subsidiary companies, giving no analysis of which companies make up the balances in the group company.
The pragmatic solution to this problem is to ignore Sage consolidation completely and simply use Excel – but this overlooks the fact that if Consolidation could be made to work it would bring with it the ability to use Sage’s Profit & Loss, Balance Sheet and other nominal reporting.
The alternative way to consolidate is, in a nutshell:
- Add a group company and restore a backup from a subsidiary
- In the group company, using File > Maintenance > Rebuild (make sure you have a backup, just in case), keeping only Nominal Accounts and Chart of Accounts.
- If any of the subsidiaries have additional nominal accounts, add these to the group company.
- In each subsidiary, run a trial balance as at the period end you want to consolidate, exporting it in Excel format
- We are going to be using Sage’s ability to import Audit Trail Transactions from Excel. This requires a number of compulsory columns and we will also want to add a couple of optional columns. Open each company’s exported trial balance and manipulate it so it has the following column headings
- Type “JD” for debits, “JC” for credits
- Account Reference Leave blank
- Nominal A/C Ref
- Date Period end date for consolidation period
- Reference Suitable reference
- Details Subsidiary company name
- Net Amount Debit or credit value
- Tax Code T9
- Tax Amount 0.00
- In the Group company in Sage, select File > Import > Audit Trail Transactions
- If the column headings have been correctly entered in Excel, the mapping will be automatic
Repeat the import for each subsidiary.
Once imported, there will be a separate transaction for each company in each nominal, making it easier to track down the origin of each entry.
Lastly, some other considerations
The manipulation of Excel to add the required columns is relatively easy to do manually, but could be automated using Excel macros.
A possible embellishment of this approach would be to add a Department for each subsidiary in the group company and include a column headed ‘Department Code’ in the Excel spreadsheet, populated with the department code for the subsidiary.
The above would work if all subsidiaries had more or less identical nominal accounts. If they had radically different nominal accounts some intermediate ‘mapping’ could be done in Excel, or we could provide a ‘mapping’ program.
If you would like any further information about this, feel free to contact us.