Case Study – Retailer Management Information

Aim

The aim of the project was to deliver speedy and effective management information, and to eliminate the significant amount of manual work being carried out to prepare spreadsheets and reports to give visibility of the business performance.

There were two key elements – the product-based sales analysis on a branch-by-branch basis, and the staff targets and performance in each branch.

Solution

A database system, using Microsoft Access as a front-end was developed.  This was chosen because of the ease of use, and speed of development as well as the flexibility to change elements quite quickly.

The system took the form of a data warehouse, which held the core business data in an appropriate manner so that it could be analysed and ‘diced and sliced’ in almost any manner required.  The system replicated the standard daily reports on screen, eliminating the need to build spreadsheets, and provided ad hoc query and analysis capabilities.

Because the data warehouse was housed in SQL Server, there are a wide variety of methods in which the data could be interrogated and presented, from spreadsheets and screens, to dashboards and even web-based solutions in the future.

Method

The data warehouse was built to house not only the daily sales numbers for the various branches, but also product details and reference data such as the targets, set by branch and by individual staff member.

Because of an issue with accessing the company’s point-of-sale (EPOS) system directly (requests to do so were rejected) it was necessary to develop an import routine, whereby the EPOS generated a text file of data that could be loaded each day.  There were two such files – one containing the branch product sales, and the other containing staff sales.

Once loaded, the data was available for immediate analysis, and the daily management reports could be viewed by any system user.  The intention was that the data would be current to the close of business on the previous trading day.

Screenshots

The Home screen, showing the default management information reports – daily and weekly branch sales, and performance against target.

 

The target setting screen, showing the weekly target, and a colour-coded indication of whether daily targets have been set for a given week.

 

Daily target setting, which analyses the performance in the same week in the previous year, and defaults to the same percentage breakdown.  These suggested figures can be accepted or manually overridden.

 

The ad hoc product view – this showed current (30-day) and lifetime sales, as well as indicating whether the product had been used in any seasonal promotions.

 

The staff target screen.  This generates the core management information concerning transactions, average sale values and volumes, and measures staff performance against their daily targets allocated by the branch manager.

For more information, and a conversation about how I might be able to help your business,. please contact me here