Automated Spreadsheet Validation Mailboxes

Mark Hayes, 4th May 2020.

Many key business processes are initiated via the submission of Excel templates. Although a perfectly acceptable approach, this often leads to considerable support overheads.

Anything from accountancy journals to organisation structure changes and financial restatements are processed in pretty much the same way:

  1. User submits a request.
  2. Request source is validated, for example originating domain.
  3. Request format is validated, e.g. template format and version.
  4. Request is validated against business rules, reference data and submission window.
  5. Validation report issued where appropriate.
  6. Impact report and / or confirmation issued.
  7. Request is either accepted or rejected.
  8. Request recorded for processing and analysis.

Over the years I've built a few mailbox services that handle the validation of such documents, manage the process and communicate the business rules. Without a doubt, these offer some of the best gains I've seen in any process automation scenario.

Improve Flow, Performance and Quality

If your organisation is handling large numbers of template driven documents that need to be validated, processed and reported on, an automated mailbox will not only save money and time, but will increase the visibility of your business rules, improve data quality and empower your users.

Providing a validation and processing service with turnaround times of just a few seconds is crucial to maximising efficiency.

To get in touch please click here...

Context

In most cases the process is supported manually by either a central help-desk or dedicated team. Either way, support staff are tasked with understanding not just complex processes, but anything from dozens to hundreds of business rules.

Regardless of location, on-shore or off, the cost of that support is considerable and turnaround times restrictive, inconvenient and not conducive to the smooth flow of operations.

In the case of SAP journals for example, no meaningful error message is provided by the system should a journal fail to post. Help-desk support staff would manually examine the journal to identify the reason(s) for failure and manually feed back the solution to the end user.

In another case, the organisations groups structure was controlled via change requests submitted as spreadsheets.

Due to the complexity of the subject matter, turnaround times for submission and validation could be weeks before a request was fit for processing.

As well as the difficulties involved in arriving at a valid request, considerable time was also required to manage amd track the progress.

The Solution

Image 1. shows the working model, a simple process where an unattended mailbox handles queries and responses. The only manual effort being the monthly update of the reference data.


Image 1. Automated Mailbox Process.

Automated Mailbox Process

Functionality Delivered

  • 24x7 globally available and fully automated validation and processing services.
  • Automated feedback that points the user to the exact item(s) that failed providing clear explanations as to why and how to resolve the issue.
  • Reference data maintenance front-end.
  • Automated statistics and mailbox status via email.
  • Validation failure analysis for identifying training requirements and problem areas.
  • Ability to flag validations as critical or just warnings.
  • Automated mail-box monitoring providing administrator alerts should a mailbox go off-line unexpectedly.
  • Domain recognition (for external email addresses) allowing administrators to open up, or block, the service to new domains.
  • For structure changes, full impact analysis generated and returned to user within seconds.
  • Turnaround times reduced to seconds.
  • Validation definitions available via email.

Benefits, Workload and Time Savings

  • Approximately 6000 SAP journals validated each month.
  • Help-desk workload reduced to the point where almost all journal queries handled by the mailbox.
  • For end users, the reduction in turnaround times makes it possible for even the most complex journal, or structure change, to be validated and corrected many times in the space of just a few minutes.
  • With the vast majority of query resolution automated, support staff have more time to focus on more complex issues and process improvements.
  • Turnaround times reduced from anything from weeks to seconds.
  • Immeasurable cost and time savings due to frictionless flow.
  • Clear and easy to understand responses help train the users in respect to process and business rules.

System Overheads

  • About one hour per month, or less, updating reference tables.
  • Costs of maintaining automated mailbox and back-end database.

Architecture

  • Database Back-end (SQL Server).
  • Email client (MS Outlook) running on a dedicated virtual machine automatically processing and responding to all requests.
  • Reference data, reporting and mailbox controls provided via a dedicated front-end application.