Build a data warehouse and data entry / report user interface

General information for the business: Dog Rocks Distribution Ltd is a distributor of pet products through wholesale and retail in the UK, continental European markets, USA and Canada.
Database management system (DBMS): Microsoft Access
Description of requirements/functionality: The Problem:
There is no easy way currently for the business to interrogate sales, analyse trends or do any gap analysis.
The following data is required to be captured:
• Sales order number
• Sales order date
• Currency
• Customer
• Customer type (e.g. Big Box retailer, wholesaler / distributor, independent etc.)
• Geography
• Item reference (sku)
• Item description (via a look up table)
• Order volume
• Order price
• Order value
The company is looking for a supplier to build a data warehouse and reporting analysis tool using inexpensive and readily available commercial software packages (the working assumption being Access). The solution should be available for use by the end of December 2016.
 
Data Entry:
Legacy Data: Dog Rocks Distribution Ltd. has a variety of different ways of accessing data the legacy data required to populate the history
• Historical data for UK and Europe for the period 2016 is available in excel format from the company’s financial systems (Xero) – sample file available
• It may also be possible to access data pre 2016 for the UK and Europe from Sage
• Historical data USA and Canada is available in excel format from the company’s financial systems (Quickbooks) – sample file available
• Data can also be downloaded in excel format from May 2016 within a commercial order and despatch system (Unleashed) at a sales order and line item level for the UK and European markets – sample file available
• Spreadsheets summarising volume data per month at an account level go back over several years
It is anticipated that legacy data will be entered / uploaded by the developer.
Ongoing Data Entry: Data from January 2017 onwards needs to be captured at order and line item level and will be entered as sales orders are received by the office based sales team who have reasonable Microsoft Office skills but don’t currently use a database product (e.g. Access).
The database design needs to facilitate the addition of new products, customers, markets and currencies.
Data should be entered via specifically created data entry forms with prescribed formats for the data, drop downs and suggestions for data such as company name to ensure data integrity.

Reporting:
Reports will be “run” by the sales management team and used to analyse trends, performance versus plan, analyse gaps and provide research prior to sales reviews or customer meeting.
The data needs to facilitate a standard suite of reports as detailed below, with the query details (hierarchy, period, reported fields etc.) being presented via a data entry form:
• Report data should be capable of being generated to facilitate analysis via any one of a number of filters (e.g. period, customer, product, geography) and provide comparisons from period to period.
• The standard reports should be set up as auto updates with totals by territory, customer and by line item / sku in monthly and quarterly formats.
• Report of total sales: Month and Like for Like (L4L) in equivalent prior period in volume and $/£
• Report of total sales: Bespoke period / Year to Date and L4L in equivalent prior period in volume and $/£
• Report of total sales: Quarter and L4L in equivalent prior period in volume and $/£
• Report of total sales: Year and L4L in equivalent prior period in volume and $/£
• Value sales should be presented in the currency of sale (currently £ Sterling and USD)
The data should be capable of being presented in tabular or graphical format and should be capable of being exported to Excel for further analysis.
Extra notes: General information for the business:
Dog Rocks Distribution Ltd is a distributor of pet products through wholesale and retail in the UK, continental European markets, USA and Canada.
Database management system (DBMS): Microsoft Access
Description of requirements/functionality: Business Overview:

The Problem:
There is no easy way currently for the business to interrogate sales, analyse trends or do any gap analysis.
The following data is required to be captured:
• Sales order number
• Sales order date
• Currency
• Customer
• Customer type (e.g. Big Box retailer, wholesaler / distributor, independent etc.)
• Geography
• Item reference (sku)
• Item description (via a look up table)
• Order volume
• Order price
• Order value
The company is looking for a supplier to build a data warehouse and reporting analysis tool using inexpensive and readily available commercial software packages (the working assumption being Access). The solution should be available for use by the end of December 2016.