The company’s need is to more easily analyze sales, for example annual, quarterly and monthly trends of volume and frequency by customer type, customer, product group and product.
The Sales system generates some reports but they are fixed format and the flexibility to view the
information from different perspectives is limited. Trend reports are currently not available.
Data for this come from three different files:
The Solution is of low complexity and would likely require less than a day of development effort.
Downloadable Extract files
You may download any or all of these files. Each file relates to the explanations and images that follow.
Downloadable Reports file
Explanations and partial images follow the Extract files explanations/partial images
The extract from the Sales system contains a record for each sales transaction. It includes the Customer ID and the Product Code but no other customer and product information. The transformation process links this sales data to the Customer and Product data to provide more information in the reports.
For this illustration the Customer data extract contains various basic information about each customer, including the Type (Direct, Retailer, Wholesaler).
The Product data extract identifies the product’s description, and the product group it belongs or maps to.
The Reports file contains four sample MS Excel Pivot Table based reports. Each report can be filtered by and drilled down to various dimensions and attributes.
Any report can be revised using the normal Excel Pivot Table features.
New reports can be added relatively quickly, by copying an existing report or tab, and then renaming and revising the copy.
Note the series of 'slicers' at the top of each report. These are used to filter to one or more values for the dimension/attribute identified by each slicer Header. The filter then applies not only to the report below the slicers, but to all or most reports within this particular sample Reports file.
Which report(s) a Slicer is connected to and filters is controlled by the user.
Each Report image and explanation below identifies the Report in the Reports file, by the tab name.
This report provides an annual trend of various sales measures (coming from data extracts) and metrics (generated via pivot report formulas):
The report can be filtered to any combination of values identified in the Slicers, e.g. filter to a particular Customer, one or more Product Groups, etc.
Note the three slicers related to the Sale Date: Sale Year, Sale Quarter, Sale Month. While this particular report trends by year, a report can easily be produced to show quarterly or monthly trends. As well the Sale Quarter or Sale Month slicer can be used in this report for example to view an annual trend for a particular quarter or month only. This is shown in a later partial image below.
In this partial image of the 'by Customer' report, the user has drilled down the ‘Customer C000005’ Cust Name (clicked on the + sign on the left) to the Product Group, Product Code and Product Description.
This report is similar to the ‘by Customer’ report, but the primary dimensions are Product Group, Code and Description, followed by the Customer related dimensions.
In this partial image of the 'by Product' report, the user has drilled down the ‘Retailer’ Cust Type for Product Description ‘Product 1’ (clicked on the + sign on the left) to the Parent CustID, Customer ID and Cust Name.
This report is similar to the by Customer report, but the data is sorted in descending order of the ‘Total Sale’ measure (for all three years together), for all dimensions. The report shows that Cust Type ‘Wholesaler’ has the highest Total Sale amount, followed by ‘Retailer’.
Within ‘Wholesaler’, Parent CustID ‘C000003’ has the highest Total Sale, followed by ‘C000009’.
Within ‘Customer C000003’, Product Group ‘Prod Group 1’ has the highest Total Sale followed by ‘Prod Group 3’.
Within ‘Prod Group 1’, Product Description ‘Product 10’ has the highest Total Sale followed by ‘Product 1’.
This report is similar to the by Product report, but the data is sorted in descending order of the ‘Total Sale’ measure (for all three years together), for all dimensions. The report shows that Product Group ‘Prod Group 3’ has the highest Total Sale amount.
Within ‘Prod Group 3’, Product Description ‘Product 2’ has the highest Total Sale, followed by ‘Product 8’.
Within ‘Product 2’, Cust Type ‘Wholesaler’ has the highest Total Sale followed by ‘Retailer’.
This partial image of the ‘by Prod in desc Total Sale’ report has been filtered to ‘Qtr 1’ in slicer Sale Quarter. The content of the report is automatically resorted based on the ‘Total Sale’ amounts for Qtr 1 only (for all years together). The Product Group with the highest Total Sale amount is now ‘Prod Group 1’, followed by ‘Prod Group 3’.
Powered by GoDaddy