Metrics

Client Information

Franchise with 6 locations that provides massage and ancillary services.

Opportunity (problem statement)

Client produces a weekly metrics report for each location in Excel.  Client manually captures data from multiple reports from each location and performs some manual calculation to obtain some of the metrics.  Each new month requires manual formatting and formula entry as well as redirecting all of the charts to a new data range.


This process can occupy much of a manager's time for 2 days each week and, unfortunately, leads to some errors in the reports.

Objectives

  • Reduce processing time
  • Eliminate errors
  • Simplify the presentation
  • Reduce complexity
  • Facilitate adding a new location

Solution

Clear Data Incorporated created an Excel/VBA system that automatically (one button push) extracts the required data from the source reports and stores the data in tables.  The presentation is now entirely in one worksheet instead of multiple workbooks with multiple worksheets.  The user simply selects the Month and Location and enters the year - the system immediately shows the results and updates all of the charts.

Results

  • The update process now takes about 10 seconds instead of 2 days
  • The system works the same every time - no errors
  • One worksheet in one workbook now handles all locations, replacing a workbook (with multiple worksheets) per location
  • Adding a new location would take seconds