"The work you have done has freed up my team to do their jobs. I now have clean reports and importantly happy Sales Managers."
Group Sales Director, Derrick Hardman, Edenred
Sales Reporting Access Database
"The work you have done has freed up my team to do their jobs. I now have clean reports and importantly happy Sales Managers."
4 day process reduced to less than an hour
Global specialises in prepaid corporate services, Edenred, approach Xlteq to streamline, centralise and develop their sales reporting process.
Overview
Our client operates in the Employee Benefits marketplace. They offer services to all sizes of organisation and operate multi product sales. They have a number of sales teams working within different areas both geographically and product mix. Each team utilise different computer systems for keeping track of the sales across each groups of products.
At present, each system operates independently from each other. Each system requiring specific information to be stored and in turn generating different outputs.
Management reports are required by the business on a weekly, monthly, quarterly, and ad hoc frequency. To generate reports for financial reconciliation and for team progress reporting, the outputs from the various systems were assimilated manually into several reporting spreadsheets. This process was time-consuming but also, since a different version of the same spreadsheet was produced each month, there was no way to do comparative analysis going back over time. In addition to the time taken the reports were open to error.
They approached xlteq to automate the reporting process and also to provide a means of storing ongoing data such that the reports could be generated for any time period. It was also a first step towards making the reporting more powerful and generating more frequent reports for each sales team to allow them to assess how likely they were to meet their targets for that month.
The solution proposed was an Access Database to store the ongoing data in such a way as to reduce unwanted duplicated information to a minimum. The reports would still be generated in Excel from the database.
Value to our client
The automated process created by Xlteq has reduced a 4 day monthly process for Edenred to less than an hour in addition to illuminating manual errors.
For a free consultation
Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.
Initial Design Phase
1. Identify the most efficient Database Structure.
2. Design and build an import process
Database Structure
Each system generated lines of data which contained both financial sales information and detailed customer information, as well as the name of the sales executive responsible for that customer. The initial design proposed to separate the client information from the financial information to avoid those details from being stored many times. It was also decided to split the tabulated data, which had a heading for each month of sales and then a value for that month, into a separate line of data for each month. It quickly became apparent that it would not be possible to amalgamate the customer data from each system due to the way it was being stored and referenced and that therefore the customer data for each system would have to be held separately.
This also demonstrates the start of what would become the most challenging part of this design – identifying the primary key for each system – a way to uniquely identify clients and their data such that the two could be stored separately and then reassembled for reporting purposes.
It was decided that the easiest way to store the financial data generated each month was to have a line of data for each sale that month, containing a reference to the client, another to the Sales Person and another to the period to which the value stored referred. Since the various systems reference the Clients in different ways and each Client could be found on more than one system, it was decided to create a separate data table for each system in an effort to keep the data as clean as possible. The one consistency between the systems was the Sales People and even these were referenced by name, rather than any form of ID. The analyst responsible for creating the reports manually had created a list of aliases for the various Sales Personnel, which could be used to allow consistent naming once the outputs from the various systems were combined. Within the database, this allowed us to create unique IDs for each Sales Person and then hold a list of aliases for each one in a separate “Alias” Table, referenced against that ID.
The Client details and particularly the Client Names were more difficult to hold in a way that allowed a unique reference for each. This was partly because there were many thousands of Clients in total and partly because there was no validation in place on at least some of the systems to prevent two client entries from being created for the same Client. (I.e. Two versions of the same client name/details on the same system.) It was decided that at this stage we needed to keep the client information in the same format as for each of the different product group systems as there was no way to combine the information. (Some systems held address details whilst others simply held the Client Name so there were compatibility issues. Also, if a system had a Client ID it would be unique to that system only and could easily refer to another Client in a different system.) We also decided to keep any duplicate client entries as they were since there was no way to be sure that the two entries were actually duplicates.
Three systems had Client Identification numbers and for two of these we were able to just use the ID to reference the Client Information. In the third system it was necessary to create unique records of Client Information in a temporary table before adding this information to the database. The last two systems had no Client Identifier and hence it was necessary to create a Client ID for each product group. Then adding new Client Information to the database could be done using the Client Name, in one case combined with the Revenue Type since these systems contained minimal Client Information in their outputs.
An added complication to maintaining Client Information was the associated Sales Person. All reports referenced Sales by Sales Person and ran a comparison with previous years’ sales. This meant that if the Sales Person associated with a particular client was updated, the update had to be applied across the system and not just for future sales. Since the database held linked Client and Sales Person IDs for each record of data it was necessary to run an update query to “rematch” Sales Person and Client IDs after any new data was imported to the database.
Importing Data
The data for each Product Group was available from different sources and in some cases in more than one format. Hence a variety of importers were required. In several cases, the data was output to an Excel spreadsheet with a series of blank lines and headers above the actual data table itself. Two systems also produced CSV files with leading columns of data populated entirely by the Month and Year combination of each month being output, one for each column of data. It was therefore necessary to write a number of small Visual Basic for Applications (VBA) routines to pre-process these files into a form that could be imported into a temporary Access table. Each temporary table was deleted and recreated for each new Data Import.
Once the data was in Access it could be split as per the design above to add new Client Information to one table and a new record of data for each Client for each Month to the data tables. To create a record of data for each reporting month, an extra field was created to hold the “Period” to which the data referred. This was a numeric field was in the form “YYYYMM”, created at import for each column of data imported from a particular system. Due to problems finding unique references for the records of data coming in it was decided that for each new import, old data held for the same reporting month would be deleted and over-written, rather than just updating this data with an update query. This proved a more reliable method of ensuring the most up to data was held in the database without accidentally generating duplicate records.
Sales People in the import were assigned an ID by means of the Alias table created. However, if a name was found that was not in the Alias table, manual intervention was required to determine whether this was a new Sales Person or a new Alias for an existing Sales Person. This process was managed in several steps. Firstly, a temporary table of all new Sales Person names was generated. Next, an error message was displayed to the user informing them that the data import could not proceed. Next a form was displayed to allow the user to assign each new name as either a new Sales Person or a new alias and if a new name, to assign the correct Division name to that Sales Person. Finally, another message prompted the user to rerun the import. An additional step behind the scenes checked that none of the Sales Person name fields in the imported data were blank and displayed an appropriate error message if they were.
The form design linked to the temporary table of names, the table of Sales People and the table of Aliases. Buttons with appropriate associated VBA allowed the user to transfer new names from the temporary table to either the Sales Person or Alias table with a single button click. This form was also accessible from the Main Menu to allow the user to add a new Sales Person manually or make changes to the Division associated with a particular Sales Person. Another form allowed the user to manage Division names and make any necessary additions and alterations.
Many Client Accounts were dormant or had been moved to attrition but would still have occasional data to be added to the database. This had been handled by means of the Sales Person aliases where a list of aliases referred to Attrition or Dormant Accounts. To facilitate identification of these accounts, two new “Divisions” were added to the database; “Attrition” and “Non-Account”. This allowed the information for these accounts to be grouped and reported separately from everything else.
Report Outputs
Summary Report Sheet
The original Summary Report presented a summary of Sales for each Sales Person in each Division, compared with targets and performance from the previous year. All the information was consolidated on a single sheet and then different sections were deleted before the summary was sent to certain areas of the business. It was therefore necessary to create a Summary Report with this format and structure for each Division as well as a consolidated report showing the information for all Divisions.
Hence a template was designed based on the original report that could be used to generate a separate reporting sheet for each Division (and if necessary, produce a single summary for just one Division). The Template was designed so that the information for the report could be pasted into a series of named worksheets (created when the data was exported from the database as a series of tables) and then the necessary pieces of data could be picked up and combined by means of a number of formulae within the template. The template could also be duplicated as many times as necessary depending on the number of Divisions being reported. Finally, at the end of the process, when each report was complete, they could all be pasted into separate sheets in one consolidated report and also saved as individual files for the various Divisions to see.
Master Report Output
The Master report originally contained all sales information for all Sales Personnel and then allowed appropriate selections to be made. It was therefore necessary for the final version of the file to have filtering in place on each column and suitable SubTotal formulae in place. Generating the outputs for the Master Report was extremely useful in highlighting where the original data had not been imported or stored properly because for a few products, duplicate records of data were created.
Reporting Process
Reporting was controlled by means of a form that allowed the user to select:
- The type of report
- The Month and Year at which the report would end
- The Division (for Summary Reports)
- The Sales Person (for the Master Report)
The first step in the reporting process was to identify the reporting period and recombine the data relating to that period from the database. The month and year were used to generate the start and end period (YYYYMM) for each section of the Summary and Master Reports: Current Year and Quarter to Date and Previous Year and Quarter to Date. The Master Report had an additional requirement to display the total of the Previous Year. These values were stored in a single-record control table and could then be used as the inputs to a variety of queries to generate the data required for each report, from each product type table. Some queries were run within the VBA to allow the inputs to be manipulated whilst others were saved within the database and simply called from the VBA.
Once all the necessary output tables were generated and combined, each was exported to the appropriate template and then any calculations within the template could be run to generate the finished report. All of this was controlled within Access rather than Excel.
The final step was to copy and paste the values and formats to one or more new Spreadsheets before saving these with appropriate names.
The Outputs generated were rigorously compared to those generated by hand for the same period and any discrepancies were investigated carefully. This process was carried out both by Xlteq and the Customer to ensure that the reports were absolutely correct before the database was used exclusively for reporting purposes.
Solution
Once this first phase of the work was complete, the database was split into two parts; one to hold the data and another “Front End” to process data import and export. Hence changes can now be made to the import process and reporting without disturbing the historic data already stored as the structure is not expected to change unless the systems supplying the data change.
Phase two
Next steps will be the ability for increased data imports and the frequency of reporting. There is also a need to begin to consolidate the customer data from the various systems to create a single, unique ID for all clients.
We also envisage automating an email process for daily reporting to Directors, Managers, Team Leaders, and each sales person. This will enable greater transparency and give clear indications of current state of achievement.