Property Management

Case Study

The Challenge

Ashdown Phillips uses a variety of different systems, including TRAMPS and Monarch to manage their properties. In order for them to provide accurate reporting to their clients, they had to implement a cumbersome process that involved exporting files from both systems, pre-processing them manually to re-order the data into a consistent format across both, combine the files using lookups and finally apply filtering, aggregation and formatting operations. This process was previously done manually, which not only takes a long time, but can easily go wrong if any step of the process is not performed perfectly. Therefore it could take up to an hour each week, when done perfectly, to even longer, if mistakes were found in the outputs and the process had to be restarted.

The Desired Outcome

Ashdown Phillips wanted a solution that could automate the processing of the inputs, and make sure that the output is perfectly generated every time. It should be easy-to-use in order to minimise training requirements, and robust enough that the chance of error were minimised. Ideally the process should run in a few minutes, if not quicker.

The Solution

After analysing the source files, we decided that a Microsoft Excel-based tool, using VBA, would be most appropriate solution. This allowed us to take advantage of the built-in dialog options to allow the user to select the files to import, as well as using VBA to apply an algorithm that would sort the files into the consistent format using a number of rules, and finally automating the aggregation, filtering and formatting operations to ensure the consistency of outputs every time the tool was run.

The Impact

A process that used to take a couple of hours each week now takes a few seconds. The outputs are also now far easier to check, and the error rate has reduced to near zero, increasing the productivity and reducing frustration of the users.

• Greatly increased production - compared to spending hours performing manual operations and checking each result, the solution takes seconds to run.
• Error rate minimised - by using an algorithm which applies hard and fast rules in order to process the inputs, nothing gets missed.
• Easy-to-use - a simple user interface with clearly labelled buttons makes it almost a joy to use.

The project took less than a week to implement from project sign-off, and has gone on to save huge amounts of time for Ashdown Phillips.