P&L / Income Statement /Sales Wizard

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.

Wizard RESULTS - Consolidated P&L or Sales Report:

Formating Options

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.

Step 1 - Install Consolidated XL

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.

Step 2 - Connect to Xero, QuickBooks or Sage Company

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.

Step 3 - Start Wizard

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

Select option from Wizard Panel

Step 4 - Select Companies

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

Select one or more Companies

Step 5 - Select Cost Centres Analysis

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 Center Analysis or Not

Cost Centre Analysis or Not

Step 6 - Select Cost Centres to include

If you opt to perform analysis by Cost Centres to include in analysis, next select one or many, or all Cost Centres:

Select Cost Center to Analyse (optional)<

Select Cost Centre to Analyse (optional)

Step 7 - Select Period Age

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

Select age the periods, and number of years to list.

Step 8 - Format Options

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):

Formating Options

Formatting Options

Step 9 - Finish

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.

Formating Options

The result of the Wizard

Formating Options

Income Statement aged by Accounting Quarters for two companies.

Step 10 - Use

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:

Formating Options

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.

Formating Options

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

Online Training

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.