COVID-19 What If Analysis

A link to the visualisation referenced in this blog can be found at the following link – https://public.tableau.com/profile/jack.parry#!/vizhome/COVID19WhatIfAnalysistab/LandingPage

Following the government announcement last week that the earliest date we’re looking at non-essential shops to re-open would be 1st July, I started to think about how this re-opening might be modelled in Tableau. After a bit of thought and some ideas from a recent engagement with a customer, I came up with the following visualisations which take in the effect of lockdown and also try to forecast what performance may look like once restrictions are lifted. I felt this guide may be helpful to explain some of the thought process behind the visualisations.

General elements and assumptions

The dashboards have been designed to be fairly simple and just visualise the effect of lockdown at a high level. Not all the scenarios portrayed are going to be relevant and correct for every retailer, however, I think they can go someway to stirring ideas for how business may start to go about some of this analysis.

The four dashboards are all very similar, visualising this year sales and performance for a retailer. Sales, profit and profit ratio are compared to last year performance at a week level. For each store there are in-store purchases and online purchases. From 23rd March 2020 (when UK lockdown was implemented) it is assumed that all in-store sales and profit are zero. Online performance has stayed as normal.

The user can then tweak parameters to see how future performance may look. They can set when the stores will re-open (as we are well aware that this may not be 1st July) and what that performance will look like compared to last year (it has been assumed that future sales would be very similar compared to last year if there is no impact). These are changes are adjusted at the week level, so if it is assumed sales will be 90% compared to last year, total sales and profit for each week will be reduced by 10%.

There are of course other factors which contribute towards profit and it’s not necessarily the case that 10% less sales would mean 10% less profit, however, that is the assumption that is taken for these visualisations. As mentioned, I’m trying to provide a simple overview of how this may look. Now we’ll move on to each of the scenarios.

In-store effects of lockdown

This is the simplest of the four scenarios and looks at having a decreased in-store revenue once lockdown measures are lifted. By default, the forecast says that the stores will re-open on 1st July (before this in-store sales and profit will be zero) and then revenue will be back to a similar level as last year. In the settings the user can change the date stores will re-open and adjust what forecasted revenue will look like when they do.

This scenario only looks at a decrease in revenue taking into account that when stores do re-open, there will likely be a decrease in performance as social distancing measures will still be in place. Consumers may still not want to leave the house for too much other than for essentials.

In-store and online effects of lockdown

The second scenario builds on the first scenario and brings in the idea that lockdown may have had an effect on online sales. Although a retailer may be expecting a decreased in-store revenue, they might be seeing increased online sales. In this scenario there is a date and revenue slider for both online sales.

If the retailer was seeing an increase in online sales over the last few weeks and expected this to continue, they could set the online date to June and say they’re expecting a 20% increase in online sales. They could then set the date for when their stores may re-open and what performance may be explore what the overall trends will look when taking into account both online and offline sales.

Effects of lockdown on store type

A retailer may be expecting performance to be different across different geographical locations and store types once restrictions are lifted, this scenario tries to capture that. Stores have been assigned into four buckets, local and superstores in a city and rural locations. Stores will all re-open on the same date, however, the performance for each of these stores can be adjusted to different levels.

Once restrictions are lifted, it may be that the local stores perform better, as consumers are not willing to travel further to the bigger, busier stores. On the other hand, it may be that rural areas are expected to perform better than in a city, as the residents of cities are fearing the virus may spread more easily. There may even be increased performance at certain store types as consumers rush back to those stores. This scenario aims to capture these possibilities.

Consumer age effects of lockdown

The final scenario looks at how consumer demographics may respond differently to an ease of restrictions. Of course, not all retailers will capture this information. It was thought this would potentially be captured by those that have loyalty schemes / points cards that are swiped during a transaction, or with retailers that send e-receipts.

Like the store type scenario, there are four buckets for customer ages. The user can adjust when stores will re-open and then adjust performance for each of the age brackets. It may be that younger consumers are going to be rushing to the shops once they can, whilst older generations will be more cautious and stay home.

Those are the four scenarios I’ve decided to focus on. As mentioned, there isn’t going to be a perfect way to capture all scenarios and make a best fit for all retailers and there are big assumptions made. I hope this goes someway to help spur some ideas as to how people may begin to model and forecast this analysis in Tableau!

Leave a Reply