Data Analysis: UK covid-19

Introduction

Last week, the SAGE Group (Scientific Advisory Group for Emergencies) was reported as saying that the number of new hospital admissions for the UK could reach 3,000 per day. That is a large number because, as at 12th December 2021, new hospital admissions per day were 900 and they were occurring at the rate of 0.0205, admissions lagged by 7 days, allowing for the covid-19 gestation period of an average of 7 days: that is new admissions per day, lagged by 7 days divided by new cases per day. That was 900 admissions/43,992 cases.

I decided to explore the data for two reasons:

• to see how SAGE might have arrived at their forecast of 3,000 admissions per day
• to see how well or how badly the UK was doing in general

The Current Situation

The following graph shows the latest situation in the UK as at 19th December 2021

The data start in March 2020 so we see just about the entire case and admission history of the covid-19 pandemic here.

However, notice that hospital admission, the blue data series in the graph above, have essentially levelled out since September 2021 even though the number of cases has been rising over the same period.

Exploring the relationship between new cases and new admissions, over the period of the pandemic from March 2020, reveals the following:

That is the truth of the situation but we seem to be seeing three clusters of data, at least. That is not helpful here since we are not concerned with providing the most definitive explanation of what we see. I experimented a little with the data and in the end, created this new graph of the same cases v admissions data but for the period 1st April 2021 to 19th December 2021 only, as follows:

Regression Equation

That's a more manageable view of the data although there may still be separate clusters in there, I have not explored them: I would do that in Power BI, by the way rather than Excel, which is what I am using for this page. You might have noticed that I have added a regression line to this second graph and, with an R squared value of 0.8077, the equation is Y = 0.0186X + 129.12. The R squared value is high, the equation is a linear equation but I had not carried out any detailed regression analysis at that point. Using LINEST(), however, we find this:

The F statistic supports our view that the model we have created is significant and the t statistic of the b coefficient is very high at 32.75 and that supports the view of a significant model. Not perfect but significant.

3,000 Admissions?

We are in a position to address the forecast from SAGE of 3,000 potential hospital admissions per day now that we have our model. I found that new cases per day of 154,349 would lead to 3,000 new admissions per day. This is how I did that:

I set up the following range, with my regression equation taken from the graph, in cell N21. In cell N20, I typed in a number as a proxy for the X value, new cases per day. Any value would do, by the way because I then called on Goal Seek to tell me exactly how many new cases per day there would have to be to lead to new admissions per day of 3,000

The image above shows my final answer, 154,349 cases, leading to 3,000 admissions. To use Goal Seek to help me:

Data Ribbon

What if analysis

Goal Seek

Then complete the dialogue box that opens, in this way:

Tell Goal Seek that you would like the result in cell N21 to be 3000 and to do that, you need to know what the new cases figure has to be, by changing the value in cell N20 to the appropriate value.

Click OK and Goal Seek finds the answer quickly.

Credibility of the Result?

What do we think about this result, of 154,349? It would be a nightmare if it came to pass, wouldn't it. After all, the current new cases per day are hovering around the 90,000 mark, although they have been increasing of late. One aspect that is not found in the data is that some newspapers are reporting that the figure of 3,000 new admissions per day is the worst case scenario since, SAGE says, the UK government is asking for that figure rather than anything less aggressive.

Conclusions

In the end, the analysis I carried out was neither extensive nor onerous. However, given what I seem to have found, the prognosis is frightening for many people. While the covid-19 vaccination programme in the UK has meant that, although the number of cases has been rising quite quickly of late, the admission rate is very low: at just over 2% of cases.

Finally, government can ask for worst case scenario data in order to convince people to get vaccinated, to stay at home more ... but for SAGE, if new cases per day do not get anywhere near 3,000, the public will surely give less credence to any future forecasts from them.

Download: here is my spreadsheet file that contains data from SAGE and it shows all of the analysis above and a lot more from me.

Duncan Williamson

22nd December 2021