Any sensible information professional keeps different kinds of data in different places. So activity is all in the main database, but things like performance assumptions and pathway changes are kept in different tables. Or you get the data on the fly by asking colleagues. You certainly don’t keep it all together in a single table.

But when it comes to planning for next year, you do need to stitch it all together somehow. In a home-grown spreadsheet model (and in most commercial planning software) this is a fiddly and error-prone affair. We wanted Gooroo Planner to change all that, and in this post we’re going to explain how it works.

You start using Gooroo Planner by flooding the system with some very basic historical activity data. You can use patient-level data straight from your main database (this is anonymised and therefore not confidential), or load up some simple statistical data if you prefer. The important thing is to import this basic data at the right level of detail, so if your final report needs to be at HRG and specialty level, then you need to load the initial data by HRG and specialty; but if just specialty-level will do, then do it by specialty.

When you’ve created your basic dataset, you can start chucking in everything else you need: demand assumptions, average lengths of stay, waiting list sizes, whatever you like. This is the clever bit, and it uses the Update button on the main Planner workflow. What makes it so powerful is that you can throw in data at any level of detail you like, and Update will automatically adapt it to match your main dataset. So you can use assumptions that are too broad, or too detailed, or even a mixture of the two.

The Update button will do all of this automatically, without you having to think about it. But out of interest, let’s take a look under the bonnet and see what Planner is doing inside.

For example, let’s say you want your plan to be broken down by organisation, specialty and activity type. So you load up some basic activity data at this level of detail. At this stage it is best to use codes instead of names, to ensure accurate data matching (don’t worry: these codes can be turned automatically into plain English when Planner builds your final report). With your basic dataset in place, you now have the framework that all subsequent data will hang from.

In CSV form, a simple example of this basic dataset, with a header and three services, might look like this. The headers in the first line are the ones used internally by Planner (and the system will give you plenty of help with those); the first three headers describe the organisation, specialty, and activity type; and the last two describe waiting list activity and non-waiting list activity.

HeadUnit,HeadSpec,HeadType,PastWLActiv,PastNonWLActiv
AAA,100,Elec,100,10
BBB,100,Elec,200,20
AAA,110,Elec,300,30

Now let’s say you want to assume that non-elective demand is going to grow at 3% a year, elective at -1%, and daycase at 2%. In the past you would have had to go through all the services in your model (and there might be hundreds of them) editing in all the right numbers. Not in Gooroo Planner: just use the Update button to load up those assumptions exactly as they stand, and it will automatically detect the format and apply the right numbers to the right services. So for the above example you would Update with the following CSV file:

HeadType,Growth
NonElec,0.03
Elec,-0.01
Day,0.02

and that would turn your dataset (which only contains elective data) into this:

HeadUnit,HeadSpec,HeadType,PastWLActiv,PastNonWLActiv,Growth
AAA,100,Elec,100,10,-0.01
BBB,100,Elec,200,20,-0.01
AAA,110,Elec,300,30,-0.01

Now let’s try something much more complicated. Let’s say you have some care pathway changes to add in, and they are expressed as reductions in demand, broken down by HRG, specialty, and activity type. So now you have some extra detail (HRG) and some missing detail as well (organisation). You can throw it in just as it stands. Planner will automatically detect what’s going on, and adapt the data so that it fits.

For example, let’s say that two of your pathway assumptions are:

• a 10 spell reduction for HRG AA111, specialty 100, activity type Elec; and
• a 20 spell reduction for HRG BB222, specialty 100, activity type Elec.

You would Update using the following CSV file:

HeadCost,HeadSpec,HeadType,FutExtraDem
AA111,100,Elec,-10
BB222,100,Elec,-20

First, Update eliminates the HRG data, leaving:

HeadSpec,HeadType,FutExtraDem
100,Elec,-30

Then it spreads this pathway change across the organisation codes in our main dataset. So now our main dataset looks like this:

HeadUnit,HeadSpec,HeadType,PastWLActiv,PastNonWLActiv,Growth,FutExtraDem
AAA,100,Elec,100,10,-0.01,-10
BBB,100,Elec,200,20,-0.01,-20
AAA,110,Elec,300,30,-0.01,

As you can see, the extra demand has been apportioned pro rata with the activity at each organisation.

Different kinds of data need different rules when it comes to apportioning them to different services in Update. So in the above examples, demand growth was simply copied across each service, but the pathway change had to be distributed between them. All those different rules are coded right into the system so that it all works out correctly.

It goes without saying that such powerful data handling was a real challenge to implement as a fully-automated Update function. But we went ahead and did it anyway, so that you don’t have to. All you need to do is hit the Update button, and Planner does the rest.

Of course, if you want to really want to roll your sleeves up and pick through your datasets manually, you can. At any time, you can just download your dataset, edit it using your favourite database or spreadsheet software, and then load it back up again. With Gooroo Planner, you are always in control.