Customer/Vendor Turnover PRO

The following functions hot link individual Customer/Vendor data by Customer/Vendor Code, or by criteria, for many periods, for a single company, in one call spilling out into adjacent cells.

You will get Excel error "#SPILL!" if the function has not got enough space to write to adjacent cells.

   The Wizard "Customer/Vendor Wizard" by Code or Criteria shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Get Customer/Vendor trunover using excel form Xero or Quickbooks

Get Top 10 Customers Turnover with 2 years Totals, and last 6 Periods

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

Get Top 10 Vendors Turnover for last 6 Quarters.

Contacts

Get Single Contact by Code
Syntax: =CXL.ContactSpill( CompCode, ContactCode, [TypeOfData], [NoYears], [NoPeriods], [Options], [Style], [FromYear], [FromPeriod] )
Performance Rated:A+

Get Top Contact Or By Criteria
Syntax: =CXL.ContactBySpill( CompCode, [TypeOfData], [NoYears], [NoPeriods], [Limit], [Options], [Style], [FromYear], [FromPeriod] , [Filter] )
Performance Rated:A+

And By Filter by Cost Centre/Department

Sorry we don't support CC/Dep tracking on Customer/Vendor data as it could create a lot of data which most people would not need.

Parameters:

Parameter Description
CompCode Single Company code
Short code assigned by Consolidated XL, or by you to identify your company
ContactCode Contact Code
"^" adds Column headings such as Periods
[TypeOfData] For Cust/Vendor ContactBySpill()
"Sales" - gives a list by Highest Sales Turnover for year. Returning: Code,Name,Turnover cols.
"Purchase" - gives a list by Highest Purchase Turnover for year. Returning: Code,Name,Turnover cols.
[NoYears] 0-5 Number of year total columns to show before periods
102 or 105 wil show YTD 2 years or 5 years
If option contains "ShowYTD", then x YTD columns
If option contains "ShowBoth", then x Total and YTD columns
[NoPeriods] By default it will list YTD
if NoPeriods=6 then it lists 6 periods from the current Year/Period
if FromYear/FromPeriod are set then 6 from specified Year/Period
Max value is 3 Years for Weeks (156), 5 years for 12 (60) month periods
[CostCentre]/[Department] Optional - Filter by Cost Centre/Department
AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Limit] Default=10 (Max = 500)
[Options] Optional - Comma delimited list
by default it will list Code, Name, and figures.
"AddTitle" Adds a title at top listing criteria
"NoHeading" adds Column headings such as Periods
"RevPeriods" reverse the order of periods
"RevYears" reverse Year Total/YTD Columns
"=" or "ShowTotal" adds a total at bottom

Enable Columns
"NoCode" Hide Code Column
"NoDesc" Hide Description Column

"ShowYTD" Show NoYears YTD Columns
"ShowBoth" Show NoYears Total and YTD Columns
For Stock Lines ContactBySpill()
"FilterGroup" return under Contact Group (first one assinged)
"FilterSalesGL" return Contact Sales GL Code
"FilterPurchaseGL" return Contact Purchase GL Code
"FilterCurrency" return Contact Currency Code

Period
“A” gives by Financial Accounting Period (Default)
“AQ” Financial Accounting Period Quarter
“CQ” Calendar Period Quarter
“C” gives by Calendar Month
“W” gives by Calendar Week (Week 1 starting first Monday in year)

Currencies:
"GBP", "USD", "EUR" etc will give all figures posted in that Currency
"toUSD", "toEUR" will convert all Home currencies to Currency
"toUSD202304", "toEUR202304" will convert all Home currencies to Currency as of the end of Year/Month 2023 April
Currency Rates are updated every night
Style Optional - Period Headings style
FromYear/FromPeriod Specify the point in time to Query
Limit Limit List to x records (500 is max)
Query Optional
Additional criteria see options

Customer Turnover Drill Down (Pro)

Age Debt and Forecast Debt

Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts
showing the Contact Turnover in a chart.
The "Trans" menu shows all outstanding transactions for the current selected period.
Clicking on the chart columns wil drill down to the transactions in each period.