Stock Item Information Pro

On this Page:
 Stock Item Info
 Stock Item Last Cost
 Stock Item Turnover
 by Cost Centres
 Stock Item Profit
 Different Periods

The following functions compliments the functions we have seen above to provide additional useful reporting data.

All of the following requires the Pro version. see Prices

 

Get Stock Item Information

Syntax: =CXL.ItemName( CompCode, ItemCode )
Performance Rated:C

This will get the Stock Item Name

Syntax: =CXL.ItemVendorCode( CompCode, ItemCode )
Performance Rated:C
Syntax: =CXL.ItemVendorName( CompCode, ItemCode )
Performance Rated:C

QuickBooks only.

Syntax: =CXL.ItemSalesPrice( CompCode, ItemCode )
Performance Rated:C
Syntax: =CXL.ItemQtyOnHand( CompCode, ItemCode )
Performance Rated:C
Syntax: =CXL.ItemIsTracked( CompCode, ItemCode )

Returns 1 if Stock Qty is tracked.


Performance Rated:C

All return various figures

Parameters:

Parameter Description
CompCode Short code assigned by Consolidated XL, or by you to identify your company.
ItemCode Stock Item code
Stock Item Name if you don't use Stock Item Codes

Get Stock Item Last Cost

The Last Cost is based on the Last Price paid on a purchase invoice, which may have been adjusted due to currency variances. Consolidated XL syncs 5 years' worth of history. Therefore Last Price and Profit for Stock items may not be populated in the first initial year of setup until a Purchase invoice has been posted.

Syntax: =CXL.ItemLastCost( CompCode, ItemCode )
Performance Rated:D

Current Last Cost.

Syntax: =CXL.ItemLastCostForPeriod( CompCode, ItemCode, Year, Period, [Options] )
Performance Rated:D

Current Last Cost at end of period.

Syntax: =CXL.ItemLastCostForDate( CompCode, ItemCode, GetDate, [Options] )
Performance Rated:D

Current Last Cost at specific Date.

Parameters:

Parameter Description
CompCode Short code assigned by Consolidated XL, or by you to identify your company.
ItemCode Stock Item code
Stock Item Name if you don't use Stock Item Codes
GetDate Date to get LastCost at particular date
Year 0 gives current year, -1 Previous …
2023 gives specific Year
Period 0 Current Period
1-12 gives specific Turnover for Period
Or week no if Option set to “W”
[Options] Optional
“” or beginning with “A” gives by Financial Accounting Period
beginning with “AQ” Financial Accounting Period Quarter
beginning with “CQ” Calendar Period Quarter
beginning with “C” gives by Calendar Month
beginning with “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
Combination of Options:
The options can be combines "C,toUSD" would produce a report in USD in Calendar Periods

Get Stock Item Turnover

Stock Turnover figures are summarised on a Accrual basis. So any Sale or Purchase is shown, and may be revalued if curreny comes into play.

Syntax: =CXL.ItemSalesVal( CompCode, ItemCode, Year, Period, [CostCentre],[Department], [Options] )
Performance Rated:B
Syntax: =CXL.ItemSalesQty( CompCode, ItemCode, Year, Period, [CostCentre],[Department], [Options] )
Performance Rated:B
Syntax: =CXL.ItemPurchaseVal( CompCode, ItemCode, Year, Period, [CostCentre],[Department],[Options] )
Performance Rated:B

Parameters:

Parameter Description
CompCode Short code assigned by Consolidated XL, or by you to identify your company.
ItemCode Stock Item code
Stock Item Name if you don't use Stock Item Codes
Year 0 gives current year, -1 Previous …
2023 gives specific Year
Period 0 Current Period
1-12 gives specific Turnover for Period
100 Total up to current Period
106 give periods 1-6
112 All 12 periods
126 weeks 1 to 26 if asked by weeks
[CostCentre]/[Department] Optional - Filter by Cost Centre/Department
AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Options] Optional
“” or beginning with “A” gives by Financial Accounting Period
beginning with “AQ” Financial Accounting Period Quarter
beginning with “CQ” Calendar Period Quarter
beginning with “C” gives by Calendar Month
beginning with “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
Combination of Options:
The options can be combines "C,toUSD" would produce a report in USD in Calendar Periods

Examples: Get Stock Sales Turnover for TSHIRT1

Example Query Function and Parameters
Get "TSHIRT1" Sales Turnover Current Year, YTD =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",0,0 )
Get "TSHIRT1" Turnover for Current Year, Period 1 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",0,1 )
Get "TSHIRT1" Turnover for 2023, Period 2 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,2 )
Get "TSHIRT1" Total Turnover for 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,112 )
Get "TSHIRT1" Total Turnover for 2023, Period 1-6 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,106 )
Get "TSHIRT1" Total Turnover for Current Year and Period =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",0,0 )

Examples: Get Stock Qty Turnover for TSHIRT1

Example Query Function and Parameters
Get "TSHIRT1" Sales Qty for Current Year, Period 1 =CXL.ItemSalesQty( “EXAMP01”,"TSHIRT1",0,1 )

Examples: Get Stock Purchase Turnover for TSHIRT1

Example Query Function and Parameters
Get "TSHIRT1" Purchase Cost for Current Year, Period 1 =CXL.ItemPurchaseVal( “EXAMP01”,"TSHIRT1",0,1 )

ToDo: Tutorial and Video Demo of this

Get Stock Item Turnover by Cost Centres

Consolidated XL powerful functions can also analyse the accounting data by Cost Centres and Departments in Sage Accounting, known as Tracking Codes in Xero, Class/Locations in QuickBooks.

We have called them Cost Centres and Departments for consistence across the different Accounting Software packages, and to use the same functions.

For Sage Accounting
Syntax: =CXL.ItemSalesVal CompCode, ItemCode, Year, Period, CostCentre, Department, [Options] )
Syntax: =CXL.ItemSalesQty CompCode, ItemCode, Year, Period, CostCentre, Department, [Options] )
Syntax: =CXL.ItemPurchaseVal CompCode, ItemCode, Year, Period, CostCentre, Department, [Options] )
All Performance Rated:B
For Xero
Syntax: =CXL.ItemSalesVal CompCode, ItemCode, Year, Period, Tracking1, Tracking2, [Options] )
Syntax: =CXL.ItemSalesQty CompCode, ItemCode, Year, Period, Tracking1, Tracking2, [Options] )
Syntax: =CXL.ItemPurchaseVal CompCode, ItemCode, Year, Period, Tracking1, Tracking2, [Options] )
All Performance Rated:B
For QuickBooks
Syntax: =CXL.ItemSalesVal CompCode, ItemCode, Year, Period, Class, Location, [Options] )
Syntax: =CXL.ItemSalesQty CompCode, ItemCode, Year, Period, Class, Location, [Options] )
Syntax: =CXL.ItemPurchaseVal CompCode, ItemCode, Year, Period, Class, Location, [Options] )
All Performance Rated:B

Examples: Get Turnover for Stock Item "TSHIRT1" for "North Region"

Example Query Function and Parameters
Get "TSHIRT1" Turnover for 2023, Period 1 =CXL.ItemSalesVal “EXAMP01”,"TSHIRT1",2023,1,"North","","" )
Get "TSHIRT1" Turnover for 2023, Period 2 =CXL.ItemSalesVal “EXAMP01”,"TSHIRT1",2023,2,"North","","" )
Get "TSHIRT1" Turnover for Year 2023 =CXL.ItemSalesVal “EXAMP01”,"TSHIRT1",2023,112,"North","","" )

Examples: Get Turnover for Stock Item "TSHIRT1" for combinations

Example Query Function and Parameters
Get "TSHIRT1" Turnover for Year 2023 for "North" =CXL.ItemSalesVal “EXAMP01”,"TSHIRT1",2023,112,"North","","" )
Get "TSHIRT1" Turnover for Year 2023 for "Jim" =CXL.ItemSalesVal “EXAMP01”,"TSHIRT1",2023,112,"","Jim","" )
Get "TSHIRT1" Turnover for Year 2023 for "North" and "Jim" =CXL.ItemSalesVal “EXAMP01”,"TSHIRT1",2023,112,"North","Jim","" )

Get Stock Item Net Profit

The Net Profit is based on the Last Price paid on a purchase invoice, which may have been adjusted due to currency variances. Consolidated XL syncs 5 years' worth of history. Therefore Last Price and Profit for Stock items may not be populated in the first initial year of setup until a Purchase invoice has been posted.

Syntax: =CXL.ItemProfitVal( CompCode, ItemCode, Year, Period, [Options] )
Syntax: =CXL.ItemProfitMargin( CompCode, ItemCode, Year, Period, [Options] )
Syntax: =CXL.ItemProfitVal CompCode, ItemCode, Year, Period, CostCentre, Department, [Options] )
Syntax: =CXL.ItemProfitMargin CompCode, ItemCode, Year, Period, CostCentre, Department, [Options] )
All Performance Rated:B

Profit is calulated as Sales Price - Last Cost Price. Currency Variance is then accounted for when paid.

Parameters:

Parameter Description
CompCode Short code assigned by Consolidated XL, or by you to identify your company.
ItemCode Stock Item code
Stock Item Name if you don't use Stock Item Codes
Year 0 gives current year, -1 Previous …
2023 gives specific Year
Period 0 Current Period
1-12 gives specific Turnover for Period
100 Total up to current Period
106 give periods 1-6
112 All 12 periods
126 weeks 1 to 26 if asked by weeks
[Options] Optional
“” or beginning with “A” gives by Financial Accounting Period
beginning with “AQ” Financial Accounting Period Quarter
beginning with “CQ” Calendar Period Quarter
beginning with “C” gives by Calendar Month
beginning with “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
Combination of Options:
The options can be combines "C,toUSD" would produce a report in USD in Calendar Periods

Examples: Get Stock Sales Profit for TSHIRT1

Example Query Function and Parameters
Get "TSHIRT1" Sales Profit Current Year, Period =CXL.ItemProfitVal( “EXAMP01”,"TSHIRT1",0,0,”” )
Get "TSHIRT1" Profit for Current Year, Period 1 =CXL.ItemProfitVal( “EXAMP01”,"TSHIRT1",0,1,”” )
Get "TSHIRT1" Profit Margin for Previous Year, Period 1 =CXL.ItemProfitMargin( “EXAMP01”,"TSHIRT1",-1,1,”” )
Get "TSHIRT1" Profit Margin for 2023, Period 2 =CXL.ItemProfitMargin( “EXAMP01”,"TSHIRT1",2023,2,”” )
Get "TSHIRT1" Total Profit for 2023 =CXL.ItemProfitVal( “EXAMP01”,"TSHIRT1",2023,112,”” )
Get "TSHIRT1" Total Profit for 2023, Period 1-6 =CXL.ItemProfitVal( “EXAMP01”,"TSHIRT1",2023,106,”” )
Get "TSHIRT1" Total Profit for Current Year and Perio =CXL.ItemProfitVal( “EXAMP01”,"TSHIRT1",0,0,”” )
Get "TSHIRT1" Total Profit Margin for Current Year and Previous Period =CXL.ItemProfitMargin( “EXAMP01”,"TSHIRT1",0,-1,”” )
Get "TSHIRT1" Total Profit for Previous Year and Previous Period =CXL.ItemProfitVal( “EXAMP01”,"TSHIRT1",-1,-1,”” )

Examples: Get Profit for Stock Item "TSHIRT1" for "North Region"

Example Query Function and Parameters
Get "TSHIRT1" Profit for 2023, Period 1 =CXL.ItemProfitVal “EXAMP01”,"TSHIRT1",2023,1,"North","","" )
Get "TSHIRT1" Profit for 2023, Period 2 =CXL.ItemProfitVal “EXAMP01”,"TSHIRT1",2023,2,"North","","" )
Get "TSHIRT1" Profit Margin for Year 2023 =CXL.ItemProfitMargin “EXAMP01”,"TSHIRT1",2023,112,"North","","" )

Examples: Get Profit for Stock Item "TSHIRT1" for combinations

Example Query Function and Parameters
Get "TSHIRT1" Profit for Year 2023 for "North" =CXL.ItemProfitVal “EXAMP01”,"TSHIRT1",2023,112,"North","","" )
Get "TSHIRT1" Profit for Year 2023 for "Jim" =CXL.ItemProfitVal “EXAMP01”,"TSHIRT1",2023,112,"","Jim","" )
Get "TSHIRT1" Profit for Margin Year 2023 for "North" and "Jim" =CXL.ItemProfitMargin “EXAMP01”,"TSHIRT1",2023,112,"North","Jim","" )

ToDo: Tutorial and Video Demo of this

Different Period Structure

The last parameter of the function allows us to switch to different period structures. "C" for Calendar months, instead of Accounting Period.

Examples: Get Turnover for Stock Item "TSHIRT1" by Calendar Month

Example Query Function and Parameters
Get "TSHIRT1" Turnover for January 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,1,”C” )
Get "TSHIRT1" Turnover for February 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,2,”C” )

Examples: Get Turnover for Stock Item "TSHIRT1" by Quarters or Weeks

And “Q” will give us Quarters and “W” will give by weeks (First week of the year beginning on Monday).

Example Query Function and Parameters
Get "TSHIRT1" Turnover Qtr 1 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,1,”Q” )
Get "TSHIRT1" Turnover Qtr 2 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,2,”Q” )
Get "TSHIRT1" Turnover Week 1 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,1,”W” )
Get "TSHIRT1" Turnover Week 2 2023 =CXL.ItemSalesVal( “EXAMP01”,"TSHIRT1",2023,2,”W” )