This wizard will create a Hot Linked Xero, QuickBooks or Sage Online Consolidated P&L / Income Statement or Sales Report in seconds, which can be optionally analysed by Cost Centre or Department in Sage Accounting, known as Tracking Codes in Xero, Class/Locations in QuickBooks.
Income Statement aged by Accounting Quarters for two companies. Cost Centres can be optionally analysed accross or down the sheet. You can then customise your self, add Data from other sources, graphs, conditional formating etc.
Follow the Gettings Started to install Consolidated XL into Excel for Office 365.
From the Consolidated Panel, Log in using Xero, QuickBooks or Office 365 credentials.
Next Connect to Xero, QuickBooks or Sage Company to sync with your own data, and take advantage of a 5 day evaluation period.
You laways have access to 3 demo companies, for you to try out while you are waiting for your data is synced, encrypted, and processed.
Press the Consolidated XL button on the "Home" toolbar, within excel, to view the Consolidated XL task panel.
Next selected "Wizards" from the menu and then "Income Statement" or "Sales Turnover".
Select option from Wizard Panel
The first page summaries the wizard’s function. Press [Next]
The second page allows you to select one or more companies:
Select one or more Companies
If you have Cost Centres enabled in Xero (Tracking Codes), QuickBooks (Classes and Locations) or Sage you now have the option to break the report down by these either across the sheet, or down the sheet:
Cost Centre Analysis or Not
If you opt to perform analysis by Cost Centres to include in analysis, next select one or many, or all Cost Centres:
Select Cost Centre to Analyse (optional)
Select how many periods, and how to age those periods. CXL will age by Financial Accounting Periods/Quarter, Calendar Months/Quarter or Weeks:
Select age the periods, and number of years to list.
The final page allows you perform some format options, such as the number of decimal places, and filter out an GL Accounts which are redundant (no value for x years):
Formatting Options
When [Create] is pressed, Consolidated XL creates a sheet called "CXL Lookups" which lists a consolidated list of GL accounts and Cost Centres for the selected companies using the CXL.CodeSpill() function.
Now you can customise the spreadsheet it creates. Add data, conditional formating, charts, etc. You can easily rearange the data since the values in the cells are retrieved by the GL codes listed in column B and the period and const centre data in the rows 3-8 at the top of the sheet.
The result of the Wizard
Income Statement aged by Accounting Quarters for two companies.
When the sheet is opened Consolidated XL will refresh the data in a mater of seconds to give you the lastest information in your sheet.
Clicking on CXL figures, and selecting "Charts" or "Trans" will drill down into the data, allowing you to visualise trends or view the detail:
Drill down to Quick Charts to see trends Pie Chart broken down into Cost Centres Use [Options] menu to copy charts and paste into e-mail etc.
Drill down to Journals for selected cell period and GL Account. Click on link to view Journal detail. Use [Options] menu to copy and paste Transaction list into sheet
While not required, it may be helpful to complete the Training on functions and how they are used so that the concepts are more clearly understood.
Online Training on CXL functions and how to use them.