Local Climate Impacts Profile: Spreadsheet notes

Collecting news-based information

The spreadsheet has been designed to capture information that might typically be found in local newspaper reports. It takes a weather related news story and builds up information on the weather reported, (type, details and impact) but more importantly, on the consequences – what happened as a result – how organisations were affected and how the various departments and services responded. It is this detail that is not routinely recorded at present and will help to build a picture of how businessesand other organisationscurrently deal with the impacts of weather events.

There may only be one consequence reported, i.e. an underpass was closed due to floods; or there may be more than one consequence, i.e. underpass closed and drains backed up on the High St. The spreadsheet takes account of the fact that more than one consequence may occur and that these may require different kinds of responses from different businesses. Capturing a range of consequences affecting different organisations or services can make for a fuller picture of the responses that might be made by different businesses or departments over the short and medium term.

Please note that this spreadsheet is designed to capture and isolate each consequence covered in the news story, each with automated numbering creating new data entry fields[1] (if there is more than one consequence reported.)

Once complete, for a set time period (i.e. 5 years) the spreadsheet can be used to supply information about the consequences of weather events for organisations. This evidence may then be used as to inform the LCLIP report, perhaps summarised by weather type, location, responsible unit or impact.

From a completed spreadsheet, it should be possible to list some instances when a particular organisation, business or service has been affected by a weather event. The detail of the consequences, their impact on the performance of an organisation or business can be explored and used as a basis for subsequent interviews with staff and added to other sources of information and other available records.

Once complete the spreadsheet yields:

  • a list of consequences that have occurred in the locality over the time frame covered;
  • the organisation or responsible agency required to make a response or affected by the consequences that occurred (e.g. a department or other organisation e.g. Environment Agency, local authority etc.)
  • If required – a qualitative (best judgement) assessment of the significance of the consequence according to risk criteria current within the business or by a simple grading (high, medium, low) for the organisation affected or required to act.

The spreadsheet will also be able to report on:

  • the weather type (high temperatures, excessive rainfall)
  • the impacts as a result of the weather (flooding, damage to infrastructure, etc)
  • detail of consequence (summary of what happened)
  • location of consequences (the predefined picklist can be edited in the ‘Lists’ worksheet)
  • the date the consequence occurred

Additional columns can be added, if required, to include space to record additional comments or data.

There is no specific need to gather local weather data, unless it is very easily obtainable, simple to include and adds value to the evidence you are collecting.

BEFORE USING THIS SPREADSHEET

A number of decisions must be taken to adapt this spreadsheet to suit your precise requirements and objectives. The ‘Lists’ worksheet contains default lists of locations, impacts and responsible units and agencies. Please take the time to consider these and, if necessary, to determine your own lists for recording ‘location’; how you want to divide up and identify the geographic area covered, and ‘responsible units/agencies’; how to define the responsible units and agencies to suit your reporting requirements and at what scale do you want to record the organisational response (e.g. directorate level, department level, including other organisations or partners?) For further guidance see notes below.

Spreadsheet guidance

The UKCIP spreadsheet is in Excel format. It has a data entry sheet and a data summary sheet.

There is a ‘help sheet’ within the spreadsheet.

The spreadsheet has been designed for ease of use with automatic numbering[2] and closed fields to allow for sorting and analysis.

Before using the spreadsheet there are decisions to be taken on how best to enter data on:

1)location; ensure location data entered is suitable for the organisation/sinvolved and for your purposes, e.g. you may wish to use the data captured to plot events on a map orsummarise them by administrative area. It may also be useful to be able to report when events have occurred more than once in the same location.

2)Department / person responsible for affected part of the business; It is preferable to be able to identify the department within the business/organisation affected. You can view and edit lists of titles in the ‘Lists’ worksheet – you may need to amend these to reflect the structure of your business or organisation. The columns allow for more than one organisation to be linked to the consequence in order to reflect the reality that a number ofdepartments may be required to respond. Please consider how best to do this in line with the scope and aims of the LCLIP. N.B. The spreadsheet should not simply contain a long list of emergency responses, the aim is to pick up which services within the responsible business/s were required to respond immediately and over the longer term. This information can be amended following interviews but an informed guess about which services are likely to have been affected will enable you to identify ‘who’ could be interviewed to provide further details. (Please note: if an external person is undertaking this task then the person overseeing the LCLIP project may have to step in to assist in identifying relevant business areas.)

There are 14 main column headings (excluding number references)

  1. Source: a field to record the source of the information, i.e. the title of the publication from which the story is taken, for future reference[3].
  2. Headline: the second column captures the media story headline. The headline provides immediate guide to content.
  3. Date of story: recorded for reference and to help pinpoint the date of the weather event reported. (Dates are recorded systematically and are therefore searchable)
  4. Summary of the news story: a brief text summary of the news story, e.g. one sentence describing what happened and the consequence/s.
  5. The date of event: if an actual date is given (ie last Tuesday motorists were...) it is useful to record the actual date for reference and in case further analysis is later made. (Searchable column)
  6. Weather type: the spreadsheet offers a pick list of types of weather. This will allow the stories to be sorted by ‘weather type’ at a later date and maybe useful for subsequent reporting. (For effective sorting pick one weather type only)
  7. Weather detail: this box allows for any further weather detail to be recorded by summarising the text taken from the news story (i.e. nearly 20mm of rain fell in just 2 hours and the cricket pitch...). The weather detail reported (which may or may not be accurate) is “20mm of rain in two hours”. If no weather detail is given leave this column blank as this data may only be need for a handful of case studies and can be filled in, if necessary, at a later date.
  8. Impact: a pick list of impacts is provided. Thus allowing sorting according to impact. (This list of offered by UKCIP, there may be other impacts you wish to add). Pick one impact, per row. One story may cover a number of impacts e.g. damage to buildings and damage to infrastructure. Multiple impacts can be selected so that all the consequences of a weather event can be recorded.The previous fields will be automatically filled to save the time and effort of duplicating entries[4].
  9. Consequence detail: multiple consequences can be entered (if reported) each consequence can be taken forward and considered separately
  10. Location: locations can be picked from the list defined in the ‘Lists’ worksheet – you will probably need to edit the list to reflect the location of your business / organisation. Consideration of how location can most usefully contribute to the analysis, e.g. you may want to summarise consequences by district or area within a district. The summary can then be sorted by these locations. This column should not be used to recordspecific street names or post codes, if necessary you think this detail is necessary it can be added to ‘consequence detail’ column.
  11. Responsible unit:choose from a pick list of departments / responsible individuals that responded or were affected. It may be that no organisation is referred to in the story which case guesstimates can be included subject to verification in interviews and discussion with relevant staff. If you cannot guess which department may have been affected by an event, this should bring into question whether this event needs to be entered at all – remember only events with a consequence for the business/s involved in the LCLIP should be recorded.Organisation structure charts/intranets can offer support here.
  12. 2nd responsible unit: It is likely that a short-term immediate response may be required and that a longer term, possibly more strategic response may also be required. This box allows for identification of otherdepartments that maybe involved in the longer term.
  13. Other responsible agencies: A third column allows for any further notes on other organisations or external agencies that may be affected or have had to make a response.
  14. Significance indicator: the final column allows for a first and cursory significance of each consequence or collection of consequences to be made. This assists in sifting and sorting material gathered. In determining significance it is suggested that any risk criteria already defined by the organisation may be used as guidance.
  15. Supplementary information: notes columns for cost/reputation/disruption to service/ staff time and resource. This column should capture information from the article about why the event is significant, e.g. were many man hours expended in the response, what were the financial costs to the authority or businesses based in the region, was the authorities reputation damaged (are there any quotes in the article criticising the authority?). Ideally this data should be captured so that it can easily be totalled, e.g. figures that can be summed in the man hour and cost columns. Data or comments gathered from interviews can also be added to these columns at a later date.

1

[1] Numbering is automatic on the Macro Enabled Spreadsheet. We advise using the Macro Enabled spreadsheet for automatic numbering, summaries and other features. See spreadsheet ‘Front page’ for details.

[2] Numbering is automatic on the Macro Enabled Spreadsheet. We advise using the Macro Enabled spreadsheet for automatic numbering, summaries and other features. See spreadsheet ‘Front page’ for details.

[3] You may also want to save a copy of the story, either on paper or electronically, to refer back to.

[4] Numbering is automatic on the Macro Enabled Spreadsheet. We advise using the Macro Enabled spreadsheet for automatic numbering, summaries and other features. See spreadsheet ‘Front page’ for details.