Using a database to track medical trials

A few years ago, I was approached by a company that undertook medical trials in the area of cognitive conditions such as dementia to see if I could improve their forecasting process.

Over time they had developed a very sophisticated spreadsheet to predict the number of patients they would be treating in a particular month. I had to admit it was a very comprehensive spreadsheet due to the complex nature of the medical trials. In particular :-

• They conducted trials on a regular basis (say every 3 months)
• Each trial had several stages and at each stage, only a proportion would be accepted to go forward. There intervals of up to 60 days between trials and these durations varied at each stage on a trial.

So if the company wanted to calculate the number of patients expected to attend trials in a particular month (and the revenue) it meant they had to go back over each trial that could still have patients for and sum over all these values.

The challenge

My client asked me if there was a way of making the spreadsheet more user friendly. In particular:-

1. The formulae in the spreadsheet were extremely complicated, meaning that only an individual who was extremely proficient in Excel could make changes
2. There were multiple versions of the spreadsheet to manage the various different scenarios
3. The spreadsheet was becoming very slow to operate.

The solution

I realised straightaway that they had to migrate from excel into a database solution as the only way of making their forecasting process more efficient. We built them a forecasting system using the platform Filemaker Pro. The benefits delivered by the solution were:-

1. You no longer had to amend the spreadsheet (which entailed modifying / adding several new rows / columns) when adding a new trial. Instead the user just had to press a single button and the trial was created.
2. There was no need for the user to have extremely advanced excel knowledge when amending the spreadsheet.
3. The database provided greater flexibility in modelling the predicted demand as it could handle scenarios that excel could not handle easily :-

a) multiple locations of the same trial

b) allow a trial to be modified easily after it started

c) have an infinite number of trial stages on a partticular trial