Speed up the compilation of complex reports

The CEO of a PR firm used to create detailed invoice summaries for each of 20 clients every month. He did this personally because he had a high-end client base and realised it was crucial that the data was complete and correct. This arduous task used to consume up to two days per month (often over a weekend).

We streamlined the data entry process and implemented a comprehensive reporting feature that eliminated copy & paste errors. As a result, accurate and clear invoice summary reports are now created by the Administration Manager at the click of a button in under one minute. Note that, as well as being executed in a fraction of the time, the task is now performed by a less expensive member of staff.

Make your spreadsheets foolproof

An accountant who runs the payroll for small businesses used to receive monthly pay details from his clients in a variety of formats: paper; email; MS Word and MS Excel, frequently with information missing. He wanted to standardise data input by issuing his clients with an Excel workbook 'template' that they could fill in with the names of their employees and the hours that they worked, and then email back to him. However not all his clients were familiar with Excel and quite often the templates were returned with entries missing, names changed and formulas overwritten, thus reducing the effectiveness of the system.

We designed a workbook that held employees names, contracted hours and pay rates on one worksheet and contained a second worksheet into which the client would enter the hours worked by each employee. This considerably reduced the likelihood that clients would fail to include details for a particular employee. Also, we protected those cells that contained formulas so that the clients couldn't inadvertently overwrite them. Finally, we provided a user guide as a source of help to the clients when filling in the workbook. Thus the accountant saved time each month by not having to reconcile inaccurate or missing information with the records held centrally on his payroll system.

Locking down user input

Most of our projects involve taking data from the client's existing spreadsheet and performing 'data cleansing' on it before incorporating in into our eventual solution. This operation invariably highlights many instances where data entered directly into cells has been inadvertently corrupted by user error. Typical examples are: invalid values, e.g. a daily booking of 55 hours (when 5.5 hours was intended); customer names that have been typed with alternative spellings in different cells or have been accidentally copied and pasted with a trailing space character; dates that can never happen (e.g. 30th February). These incorrect entries have a detrimental effect on the operation of the spreadsheet and can be difficult to track down and costly to correct.

As standard, for all our Excel-based spreadsheet applications, instead of the user inputting data directly into worksheet cells, they enter it into boxes on a displayed form. This allows us to implement an array of mechanisms that minimise or eliminate user input errors and the costs associated with them. Typical of these are:

• Data validation - values input by the user can be rejected if they lie outside specified limits or don't conform to a specified format (e.g. postcodes, phone numbers, email addresses).
• Date selection via a pop-up calendar - so that it is not possible for an invalid date to be entered, or a date in an incorrect format (e.g. the US format of month/day/year).
• Dropdown-based selection - where possible, the user selects values from a dropdown, rather than typing them in manually, thereby ensuring they can't be entered incorrectly.
• Data integrity - entered data will only be copied into the worksheet when the user clicks the Enter button. But, and here’s the clever bit, the Enter button is only active if all the entered data for an entry conforms to the pre-defined criteria described above.

These features allow invalid data to be identified at the time of entry (when it can easily be corrected) rather than at the time it is first used (when it can be costly and time-consuming to rectify).

Speeding up processes

A print solutions firm used Excel worksheets as template forms for their enquiry, quotation, purchase order and confirmation forms. When an order was received the sales staff used to manually create these forms and copy & paste the order details into each of them. They then had to manually convert each form to a PDF and open Outlook to email them to one or more destinations. A tedious and error-prone process.

We used several mechanisms to automate the process:

• Automatic generation of forms (when an enquiry is received, the details are stored in a database. This data is used to populate the standard forms as the order is progressed).
• PDF file creation (worksheets are converted to PDF files at the click of a button).
• Emailing data without needing to use copy & paste (at the click of a button, an existing PDF file is automatically emailed to one or more recipients).

These features allow the input data to be copied automatically into as many forms as necessary, without repetitive cut & paste, and the resulting forms to be automatically converted to PDF files and emailed as attachments to addresses selected from a pre-configured database. This considerably reduces the time taken to create the forms, obviating cut & paste errors and means that the whole process can be carried out by less-skilled personnel.

Reconciling manual inputs

One client used to transfer entries from paper format into an Excel spreadsheet; however, they discovered that entries were sometimes being missed if the user was distracted, for example when they had to answer the phone and failed to resume the entries where they had left off. Since the entries in question were timesheet bookings for their clients, overlooking an entry could easily cost them money.

To address this problem we implemented a solution whereby, as each transaction is entered, the system allocates a unique check number which the user writes on the original paper record. This allows later reconciliation of the entered data with the paper equivalent: it is a simple matter to sort through the paper records - if any of them don’t have a check number it is a clear indication that they have not been entered into the system.

Your unique problem goes here

The above examples are just a few of the ways in which we have saved our clients money. If there’s something particular to your office processes that you think would save you time and money if it were automated, then we can probably help.

For more information, see the Case Studies on the Spreadsheets page.