Retail

APS Merchandising System

Background

This system was originally written for a UK retail chain who currently use it to plan stock at about 350 stores throughout the UK. It has been in operation for three years. The current system has been adapted to be used by a wide range of retail operations with various back-end computer systems.

What is it?

The APS Merchandising system is:

It is a STAND-ALONE system that extracts data from external systems, so if you change other applications you can continue to use APS Merchandising.

What does it do?

Each week, the system adds data from external systems into a single database holding all the necessary data for merchandisers to plan future stock and orders. The database information held is shown in this table.

A Microsoft Excel spreadsheet is created for each Department each week. Merchandisers can then use this spreadsheet 'off line' - at their desk, on their laptops or at home - to plan for that department. When they have finished their work, the system reads the data back in from the spreadsheet and updates the database.

Each Departmental spreadsheet has a worksheet for each category within the department. There is also a Departmental Summary sheet and a Print Layout sheet. Most planning takes place at Category level, though changes for the whole department can also be made - either by applying a % change to all categories, or by specifying the new Departmental figure.

Company consolidation of all departments and groups of departments provides management with an invaluable complete historical and projected plan for the company.

Please see the enclosed spreadsheet to get a better idea of the system. If there are any points that need further explanation, please feel free to call us.

Where does it get the information from?

External data is imported weekly. This can be done directly via an ODBC link to external data sources or via intermediate CSV files. Any systems that have ODBC drivers for the databases can be used to add data. These include ORACLE, SQL Server and all main stream commercial databases.

The data in the APS system is held in a Microsoft SQL Server database. This allows authorised users to query the data with any data access tools such as those in Microsoft Word or Excel.

Security

The system holds a list of Windows users who are able to access the system. Users must be given explicit access to view or update a department.

When an updatable version of the spreadsheet is produced, the system 'books it out' to that user. If a second attempt to create the spreadsheet is made, the user is warned that the department has already been booked out. When the week is closed, the system warns of any spreadsheets that have been booked out but not imported back in.

What data is entered by the merchandisers?

Historical data is protected and can not be changed. Future figures are a combination of known commitments and calculated estimates that can be overridden by the merchandisers.

Merchandisers can control the following:

The 'Planned Sales' figures are set in advance and can be 'locked' up to a specified date to prevent changes. The 'Forecast Sales' figures are an estimate of expected sales and can be amended each week.

VAT and Margin are first set at Department level. This acts as a default for all categories in the department. The Category VAT and Margin can then be adjusted as necessary to override the departmental figures (the margin is used as a default value for future estimates and can be amended weekly - the actual margin is shown for historical data).

The formulae used to calculate future data are embedded in the excel spreadsheet and can be amended to suit your requirements.

Departmental Structure

Departments can be combined to form groups that can be reported on. For example:

Spreadsheets and reports above Department level can not be used to input data to the system.

What else does it do?

Weekly 'Flash Sales' spreadsheet

Comprehensive data relating to this weeks activity - please see sample spreadsheet.

The structure of this spreadsheet is to some extent user customisable.

Custom Spreadsheets

In addition to the defined Department / Category workbooks, the system will also produce spreadsheets combining similar elements from different departments (user defined).

Weekly Departmental Print

If desired, spreadsheets can be batch printed each week either at Company, Departmental or Category level.

Relocate Categories

Categories may be moved retrospectively between departments. In addition, the contents of one category may be added into another. Alternatively, a percentage of the category can be moved to another department or combined with another category in the same or another department.

Flexible Year End and 445 Accounting Structure

The structure of each financial year may be defined and changed retrospectively. Whilst the structure is based on periods comprising a whole number of weeks, the number of weeks in a period may be set from 1 to 5 and the number of periods in the year set from 1 to 13. This allows users flexibility in 53 week years but is also invaluable in the case of a change in financial year start, where a short year may be needed.

Report Generator for Excel

User defined ad-hoc reports delivered in an Excel spreadsheet. Any database fields can be selected. Data can be selected, sorted and consolidated in any combination of Year, Week, Department or Category. Of course, once the data is in the Excel spreadsheet, it can be manipulated in any way the user wants.

How much does it cost?

As a rough guide the system will cost about £20,000 per site. It may be used by any number of users at that site. The price includes a number of days assistance in system customisation and configuration. The customer may need to purchase Microsoft SQL Server and client access licences if they do not have a copy already. For a smaller number of users, a licence free version of the SQL Server database is available. Most users need only connect to the database for a few minutes to create the spreadsheets and again a few days later to import them.

How do I find out more?

If you would like to discuss any aspects of the system further or would like a demonstration, please contact us.

Valid XHTML 1.1! Valid CSS!

Site created by Matt Austin Web Designs