How to draw a funnel plot in Microsoft Excel
"...a graphical aid for institutional comparisons..."
In an article called ‘Funnel plots for comparing institutional performance’ published in the journal Statistics in Medicine eight years ago (Statist. Med. 2005; 24:1185–1202), David Spiegelhalter wrote:
Funnel plots are now commonly used in the NHS for exactly this purpose. Here are step-by-step instructions on how to draw them using Microsoft Excel (we’ve used Excel 2003 here but you could easily adapt them for 2007 or 2010).
You can use this Excel workbook to make better sense of the instructions:
What’s the data about?
Take a look at the Data (1) worksheet. You’ll see that we’ve taken 26 consultant physicians at Anytown Royal Infirmary who were attributed with 20 or more inpatient discharges in calendar year 2010. And we’ve counted how many of these discharges were subsequently re-admitted within seven days of discharge.
Calculate each physician’s re-admission rate
In the Data (2) worksheet we’ve expressed—in column D—each consultant’s re-admission rate as a proportion of his or her discharges. You can multiply this proportion by 100 to arrive at a more “user-friendly” percentage if you like. But in the interests of making the calculation in Step 4 easier to explain, we’ll leave it as a proportion for the moment.
These individual consultants’ re-admission rates will be the dots in the funnel plot.
Calculate the overall re-admission rate
In the Data (3) worksheet, we’ve simply taken the value that we calculated in row 32 at the bottom of column D in the Data (2) worksheet and replicated it in column E.
This overall, all-consultants rate will be the horizontal solid line in the funnel plot.
Calculate the standard error
In this example we’ve used the basic formula for calculating the standard error for a proportion. So in cell F4 for example, we've typed =SQRT(E5*(1-E5)/B5).
The thing to remember with this formula is that you need to use the overall, all-consultants, p; but you use the individual consultant’s n. When we draw the control limits in this funnel plot, we are going to assume that each consultant has the overall re-admission rate of 0.042 (expressed as a percentage: 4.2%) rather than their own actual, observed re-admission rate, which for Consultant A, for example, would be 0.046 (or 4.6%). That's the important point to make about funnel plots. We're saying: let's assume that each individual consultant were performing at the overall, average level. What would be the range within which we could still describe this performance as average, taking into account sample size?
The "conventional" way of calculating standard error would be to use the p for each individual consultant as well as the n for each individual consultant. In fact, this is what we’d do if we were drawing a caterpillar plot. Whereas with a funnel plot we take the overall p and only the n from each individual consultant.
Calculate the 95% control limits
It’s a fairly strongly-held convention that funnel plots have two sets of control limits drawn on them. The first ones are the 95% limits. And these have been calculated in columns G and H of the Data (5) worksheet. They are the values in column E (the overall re-admission rate of 0.042) plus or minus 1.96 standard errors (the figure that we just calculated in column F).
Calculate the 99.7% control limits
The second set of control limits are the 99.7% limits. These are shown in Data (6) and are calculated in the same way as the 95% limits except that we multiply the standard error by 3 instead of 1.96.
Sort the data
The only thing that differentiates Data (7) from Data (6) is that we’ve sorted the data in ascending order of the number of discharges (column B). The reason we’ve done this is because when we come to draw the chart, we need to join some of the dots together with lines, and these lines will look crazy-ridiculous unless we first of all sort the data.
Draw the graph
A funnel plot in Excel is a scatterplot. So you need to select the data series in columns B, D, E, G, H, I and J and then choose X-Y (Scatter) from the chart type options to you. It’ll also make life easier if you can choose an X-Y chart that already joins the dots, because there’s only one of the series (the data in column D) that you don’t want to show as a line.
Interpret the graph
One of the best explanations we know of why the control limits on funnel plots are shaped like funnels can be found right here.
[15 June 2012]
|© Kurtosis 2011. All Rights Reserved.|