Sales Analysis

Menu 11.13 10 x 20 Report Menu

Customer Reports
1. 10 x 20 Customer / Category Report
2. Category Sales Report by Customer
3. Category Sales Report by Category

Product Category Reports
11. Product Category Sales Report by Warehouse
12. Product Category Sales Report by Category
13. List of Items by Product Class with Sales Last 12-Months

10 x 20 File Update Procedure
21. Run Ten by Twenty Build Now

 TEN BY TWENTY REPORT PROCESS
SCOPE: To produce a report showing the relationship between the top 10 product categories sold, compared to a selectable group of customers (up to 20 per report) and their amount of purchases within those top 10 categories.  

REQUIREMENTS: A laser printer is needed to print this report in landscape mode.

TEN BY TWENTY REPORT PROCESS: This report originally began life as a Ten-by-Ten report showing a store’s top ten customers versus the store’s top ten product sales categories.  As with most things if ten is good, twenty is better.  So now we have the 10 by 20 report.  Furthermore, you may select a group of 20 customers to display based on their total sales ranking (i.e. customers 1-20 or 41-60, etc).

To produce this report, we have created two new files in your database: Prod.Cat.Sales and the Cust.Cat.Sales.  In the product category sales file(Prod.Cat.Sales) is a summary by product class code of all product sales for the last 12 months.  (The product class codes are the codes maintained in the Product Master maintenance screen at menu path 12.3.2.2 field #28).  In the Customer category sales file(Cust.Cat.Sales)  is a summary by product class of all customer sales for the last 12 months.  A script for updating the summarized sales data will be put in your month-end procedure to refresh the data monthly. 

A new menu path has been set up to contain the 10x20 report and the other reports available from this new data.  Menu path 11.13 will bring you to the Ten By Twenty Report menu. 

Now let’s talk about the report itself.   The header line states that this report represents ALL companies combined for each customer.  What this means is that if you have a customer that buys product from more than one of your branches, that data is all combined into one Sales dollar total. 

The next header line states that the customers selected were from Sales sequence number 1 to 20.  The customers are sorted by descending sales dollar total.  For the example report above, the first twenty customers with the most sales dollar volume were selected.  If customers 101 to 120 were selected then they would represent those customers with the hundred-and-first through the hundred-and-twentieth sales dollar volume. 

Now we come to the TOP TEN CATEGORIES 12MO.SALES$ header line.  The columns that follow this description represent the previous 12 months sales dollar totals for the product class in the same column two lines down.  As we see on this report the 12-month sales dollars for the 999 class is $2,007,612.  The 999 category for this Johnstone store is where all non-catalog Johnstone products fall.  The high total under the 999 category indicates a variety of products without an accurate product class code, hence the importance of maintaining Product Class codes for all of your products even your non-catalog and non-stock items.  This will result in a more accurate account of your top categories.

The next category (after the 999 category) is 315, which had a 12-month Sales dollar volume of $1,331,283, and as the next header line shows (TOP TEN CATEGORIES GROSS PROF%) a gross profit of 19.53 percent. 

The body of the report contains the customers selected and their sales dollar totals.  As we look at the line for customer 0001 we see that he had a 12-month sales volume of $92,401.  Of that, $14,038 were product purchases in the 999 category, $7,632 were product purchases in the 315 category, etc.  As you can see in the product class-listing, (see page 5 of this bulletin), the 315 category is Air Conditioning Equipment.

From the customer line totals you can see what your customers are spending (or not spending) on your top ten selling product classes.  With the most significant opportunity for growth being with “existing products to existing customers”, this report will show you opportunities to get more of a customer’s business in your top selling categories. 

Well, there it is. The 10 x 20 Customer Report will help you find opportunities to increase your business with your existing customers.  This report will be available on your system prior to the annual meeting in release # U2002.5, and you will be able to access this report and other 10 x 20 Customer Reports on

Menu Path 11.13.

Of the JUG Committee members currently using this feature, one has found the Report on Menu path 11.13.2, Category Sales Report By Customer, very useful in comparing what a good customer buys vs. another customer.  This report will display the product classes and the sales by percent of total customer purchases.  It is easy to compare the percentages of your customers that consider you their “primary supplier” vs. those who do not. 

Menu 11.13.13 - List Of Items By Product Class With Sales Last 12 Months.  This report will print a list of products for a specific classification.  The report can be used to determine which products may be incorrectly classified.  For example, several stores have used class 999 to categorize miscellaneous products that did not fall into a standard classification.  As a result, sales dollars maybe overstated for the 999 classifications. 

(updated 3/22/2006)

 
  |