Use Case: Sales Ledger - Customer Sales by Location

A common request by internal management or external auditors is to determine customer sales by location for a given period of time. This reporting can assist with the calculation of sales by region, sales by location for sales tax, sales by country, or to determine sales trends for a particular geographical area.

Connected's Sales Ledger provides an excellent tool to assist with this analysis.

The following will show the process of how to use the Sales Ledger to create a detailed sales by location report, by customer invoice address. This type of report can be totaled and also contains all the detailed data to back up those totals.

Setting up the Sales Ledger with Invoice Location Data

Using Connected's Sales Ledger allows access to invoice information that includes either the Billing Address, Shipping Address, or both, for each invoice.

To prep the Sales Ledger for a location analysis, the following example can be used.

Add the data columns to the Sales Ledger required for the analysis. In this example, the following columns were used. 

Invoice No

Invoice Date

Customer Code

Bill To Name

Invoice Subtotal

Freight

Tax 1

Tax 2

Bill To City

Bill To State/Prov

Bill To Zip/Postal

Sales Rep

Ship To City

Ship To State/Prov

Ship To Zip/Postal

Reference: Learn how to add/change columns in Sales Ledger >>  Ledger and Query Windows - KB

Once the columns are selected, enter any invoice filters required.  For example, if Sales by State/Province is required for one year, enter a one year date range in the Sales Date Interval  field. In this example, a date range of one year, all invoice types, and all Posted/Closed invoices were included.

Reference: Use "Connected View" to save the options for future use >> Using Connected Views 

The following is an example of a saved "Sales By Location" Ledger view using the data columns listed above.  

Exporting the Sales Ledger

Once the Sales Location data is loaded it can be exported to Excel for the final analysis.

To export data to either a Text File or Excel, click the icon in the top right, as shown below.

When export is selected a choice to either export the Entire Header or Column Labels Only can be made.

Using Excel to Create Reporting Totals

Once the data is exported to Excel, the following steps can be used to achieve a quick location analysis.

Totaling in Excel can be done in multiple ways:

• Sort and manually sum fields
• Subtotals
• Pivot Tables

For more information on how to use these features in Excel, please refer to internal or online Excel help articles. The following is a useful article on using these operations in Excel >> How to Make Subtotal and Grand Total in Excel (4 Methods)

The following example shows how to Subtotal the exported data by State/Prov:

1) Highlight all data columns and select the "Subtotal" icon in Excel.  This can be located in different spots depending on the version, platform, and customization done to Excel.

2) Select the Subtotals, as per the screen below:

3) The data should appear similarly to the example below:

The following shows an example of how a Pivot Table can easily show the same information.

For more information on how to use these features in Excel, please refer to internal or online Excel help articles. The following is a useful article on using these operations in Excel >> How to Make Subtotal and Grand Total in Excel (4 Methods)