How to do simple Trend Analysis?

[Bostaevski]

Is there such a thing?

Hi,

This question has nothing to do with poker or gambling but since I know there are alot of statistics gurus here I humbly ask for help.

Background:
I work for an Organ Procurement Organization. We are responsible for coordinating organ donation from deceased donors. From any given donor we are able to procure between 0 and 7 organs with the majority being 3 to 4. One of our key performance indicators is to measure how many organs are transplanted per donor over some given time period. Typically, we look at this figure monthly and also compare by Calendar Year.

Concurrent with a new Director of Hospital Services coming on board, we have noticed an upward trend in our Organs Transplanted Per Donor (OTPD).

For instance in the years 2005, 2006, and YTD 2007 our OTPD has gone 2.93, 2.92, 3.52.

We have approximately 150 donors per year.

The Task:
I need to see if there is a way to do some kind of trend analysis on this data. We would like to try and figure out what are the odds this is an anomaly? How likely is it that we will be able to sustain this OTPD rate? The CEO is going before the Board in a few weeks and she wants to be able to say something like "We are 95% confident that we will hit our goal of 3.50 OTPD this year". Obviously I need to provide her with the analysis to back up her claim.

The Problem:
I have no idea how, nor do I know if this is even really possible [img]/images/graemlins/frown.gif[/img] I feel like I need more than some Excel chart where I add a trend line. I have thought about doing histograms, bell curves, etc, but I do not see how I can use those when looking at trends over time(?). It would be great if this type analysis can be done in Excel 2007. I also have this software called "SPSS 15.0 Family". I have never used it and do not know if it provides anything that can't already be done with Excel.

I also have a book: Elementary Statistics: A Step by Step Approach (6th Ed.) by Allan G. Bluman. There's alot of stuff in it but nothing that really sounds like what I am looking for.

Can anyone help me out?

[AaronBrown]

I don't see a trend, I see two numbers essentially the same, then one jump up. But maybe you see it in the donor-by-donor numbers.

My first advice is that a trend analysis is not appropriate here, because organs per donor is not the key performance indicator. You could inflate it by rejecting any donor for whom you will get three or fewer organs, or by harvesting substandard organs or by murdering healthy people at convenient times.

It's more important to understand the reason for the increase because (a) then you can consolidate and even increase your gains and (b) you know that it is a real improvement and not a statistical artifact. Are you getting healthier donors? Are you processing more efficiently? Are you getting more demand for transplants? You should be able to investigate these things, both from the numbers and other information. This is valuable work.

To do a straight trend analysis, you would first decide whether the data suggest a steady increase or a sudden change with the new DHS.

I assume your data are in Excel with dates in column A and number of organs harvested in B (you might have two or more rows with the same date, if you had more than one donor on that date). Say you have 500 rows, then you select a 2 coumn x 5 row array of cells somewhere and type:

=LINEST(B1:B500,A1:A500,TRUE,TRUE)

Then hit CTRL-SHIFT-ENTER (all three keys at once). The Excel help or your statistics book will explain what the output means.

If you instead want to compare the performance before and after a certain date, you want to do a t-test. Your statistics book will tell you how to do with with the Excel data, it's easier than the regression.

I warn you again, this is not the right way to analyze the data, and, in any case, these techniques can be misleading without some training.

[Paul2432]

This question is a hypothesis rather than trend analyis. You can do this in MS Excel, but you'll need to install the data analysis tool pack (you'll likely need the office CDs). You'll also need the complete data set (i.e. 2,5,3,6,1,3 etc). This will allow you to estimate the variance in the data, which will in turn let you estimate the likelihood that the increase in 2007 is due to chance.

Once you have this enter the 2005/2006 in one column and the 2007 data in a second column. Select "data analysis" under the "tools" menu, and then "t-test: two-sample assuming unequal variance" on the pop-up menu. In the next pop-up window enter the cell range for the 2005/2006 data in the space for variable 1 and, the cell range for the2007 data in the space for variable 2. The field "alpha" is the level of significance (lower is stricter).

For more info on hypothesis tests in general search Wikipedia on "hypothesis test".

Paul

[Bostaevski]

Thanks guys this is good stuff. I went and spoke w/ one of the clinical managers. Turns out, he actually wants the OTPD rate to drop to his magic number of about 3.30

What he wants to see is maybe 5-10% of our donors end up being 0-organ donors. He uses that as a barometer for knowing that we are being aggressive and pushing hard. If our OTPD is too high then we're not being aggressive enough (cherry-picking). If it is too low, we're may be having problems with our donor and case management.

He generally attributes the increase to spending more time on each case, sending 2 recovery coordinators instead of 1, etc.

Anyway I really appreciate your input. There are several areas where I think this stuff will be useful.