Consolidated Dashboard

By Malcolm Souness - Director, 221b Limited

In my first role as an energy supply analyst, a series of reports for the Board were produced using separate spreadsheets and datasets. One thing that was missing was the ability to cross check and validate data for board reporting.

The approach taken to resolve this shortfall was to bring the wholesale reconciliation, price, retail volume, budget volume and hedge forecasting reports into one consolidated model.

For this the SSRB (Spreadsheet Review Board) standard model was adapted and used to track forecast and actual items for each month over time:
  1. Retail book volume
  2. Generation book volume
  3. Hedge book volume
  4. Value at risk
  5. Average Spot prices
  6. Market submissions
  7. Market allocations
The great thing about building this model was that legacy time series were brought into one location, with specific extracts and charts to support Board reporting and budget development. Another feature was the automatic update and standardisation of charts as data were added to the model.

From an operational perspective, the model is useful for cross checking wholesale market submissions against budget and actual volumes achieved.

During the second year, I noticed the budget retail volumes were consistently out of alignment. To address this the daily average billed volume per customer segment was applied to the number of days per month in a crude attempt to normalise the volume. This worked, resulting in accurate retail revenue and volume forecast in the corporate model.

In my next role as Reconciliation Manager with a larger retailer (without generation), there were a series of reports used for reconciliation reporting. Again, the SSRB model was implemented and used for tracking retail volumes and wholesale electricity and gas submissions, along with aggregate wholesale market allocation reports.