DDGRAPHS.XLS

By Vilnis Vesma. July 1999
This Excel spreadsheet (version 2, approx 100 kb) is available for free download for private use. If planning to download, print this page first as a guide to using the spreadsheet It accepts monthly energy consumption figures (in kWh) and the corresponding degree day values, and allows you to experiment setting target characteristics and seeing what effect this has on various recommended charts of the data. The spreadsheet is supplied with a few months' sample data. You can play with this data to familiarise yourself with the various charts.
When you are ready to enter your own data, you begin by setting the correct site title, and zeroing out the various target parameters (k0 for fixed monthly consumption, k1 for the variable coefficient, and the control limit)
Then enter your consumption data in the main data entry table, following the headings shown. In this version of the spreadsheet the consumption data must be for exact calendar months (or close to it) so you may need to do some interpolation of your raw data if meters have not been reliably read near to the end of each month.
Once you have entered you data, you can go back to the top of the sheet and set the target parameters. Using the preview x-y chart provided on the right-hand side of the sheet, set k0 first so that you have a horizontal line intercepting the y-axis at an appropriate place. In this example, the value would be 80000.



Then estimate the slope of the line by dividing the spread in kWh figures by the maximum number of degree days (in this example, 35000/350, or 100). Enter this as your first value for k1. Trim the value of k1 until you have the desired diagonal line on the x-y chart. This becomes your target characteristic line.

To validate the position of your target characteristic line, look at the Cusum chart. Does it have sustained horizontal sections? If so, your target is achievable. Does it drift upwards across the chart as you move from left to right? If so, raise your target characteristic line. On the other hand, if your Cusum chart persistently slopes downwards, you can push your target characteristic lower.

The charts built in to the worksheet are

  • Scatter: an x-y plot with kWh units on the vertical axis and degree days on the horizontal axis. Each plotted point is one month's data. A diagonal straight line superimposed on the chart represents the current target performance characteristic.
  • Trend: twelve-month moving average. This irons out purely annual seasonal effects.
  • Norm: a time-series view with actual consumption superimposed on the expected consumption (calculated from the current target performance characteristic).
  • Deviance a time-series view showing how far actual consumption fell above or below expected. A 'control limit' can also be set which displays as a pair of parallel dashed red lines equispaced either side of zero.
  • Cusum: a time-series view showing the cumulative sum of deviance. Horizontal running indicates compliance with the current target performance characteristic; upward slope indicates waste; downward slope indicates saving.
Degree Days Direct welcome screen