Hi Guys, this blog post and the above video are my entry for the SAP Data Geek Challenge. It’s my first attempt at doing something like this and I hope it’s interesting. I work in Scotland for a pretty large organization, although our presence is limited to Scotland so I don’t have the experience of working at a multi-national level. I work with Utilities supplies and I had the idea that I could use the visualizations in SAP Lumira to explain the relationship between degree days and our gas consumption levels. The video above is the main portion of the entry but I thought I would write out this blog post for anyone unable to view the video (almost all streaming media is blocked in our organization).
The Data Set
For the data set I’ve prepared a load of sample data (partly with the help of generatedata.com) and I thought it would be funky to assign the supply points to Scottish Castles, plus I looked up the latitudes and longitudes so I could try out the bubble map. The data provides the total cost and consumption, per month, per supply point. I’ve grouped the sites into regions and assigned an energy manager to each region (since it’s sample data I just picked out famous people from each region). Finally I’ve added in a degree day value, as different sites are subject to different weather conditions they can have different degree day values but for the sample data I’ve just used the same degree days across all sites.
Visualizing the Gas Supply Portfolio
To start with I have a few visualizations to help us get a feel for the portfolio:
Bubble map to demonstrate site dispersal across the country and highlight the sites with the highest consumptions.
Pie Chart showing the different regions and Energy Managers
The Degree Day Effect
Our gas supplies tend to primarily be used for heating systems and as a result we see a noticable increase in gas consumption during colder weather. Degree days are a measurement of the air temperature in relation to a baseline and we use these to measure the effect that the weather has on our gas consumptions. The bar graph below helps demonstrate the relationship between the degree days and gas consumptions:
Scatter Chart showing Degree Days vs Consumption (Note: I would like to be able to add a best fit trendline)
So using by combining the degree days and consumption values we can use linear regression to predict what the consumption would be for any given degree day value. For budget setting/forecasting purposes we usually form a conservative degree day profile based on past years which is between the average and worst years but this gets tightened up as the year progresses and we get the actual degree day values.
Using CUSUM to Visualize Performance
CUSUM is a shortened form of Cumulative Sum (of the difference/variance). In the following visualizations I used [Actual Consumption] – [Target Consumption] to give the consumption variance with a negative variance representing a drop in consumption and a positive variance representing an increase in consumption.
The first example shows a site where they’re gas consumption has dropped compared to what we forecast:
The next example shows a site where gas consumption has risen:
However the following Bar graph combines the CUSUM with the monthly variance to suggest that something is being done to bring the consumption back into line (the gap between the actual and the forecast is dropping):
And finally, a wee shot at trying to show the variance chart (note: this one isn’t cumulative) with two different colours for reduced/increased consumptions:
If you would like to know more about the use of degree days the Carbon Trust website has an interesting guide:
Degree Day Values can be obtained from here:
I hope this has been interesting and would welcome any feedback.
P.S. One visualization which I forgot to include in the video was a line chart showing the cost of gas over a period of time, with a couple of points standing out as price increases implemented by the supplier: