If you have any problems or questions about your spreadsheet please call Alison 6777-1069.

The Counting Spreadsheet

The purpose of keeping a count of activities, people and volunteers is to be able to report on “How much did we do?

There are 2 versions – and you need to work out whether you want to collect the full year or just the snapshot. A snapshot requires a minimum of 1 week of counting done 2 times over the year (in 2 different quarters). Snapshot data is recommended for this first year, so that we can trial the tools and identify areas that need to be looked at to make this easier to use. Please note, these are only spreadsheets it is not a database so there are a number of limitations and restrictions in setting up and using the spreadsheets.

For those of you unfamiliar with Excel:

Excel use the terms;

  • workbook (to describe the whole document, a workbook can have one or many worksheets)
  • worksheets (the actual spreadsheets that you enter information into)
  • columns – denoted by a letter of the alphabet and run down the page
  • rows – denoted by a number, and run across the page
  • cells – the places on the worksheet where information is entered. A cell can be identified by its location example – “A5” – meaning the cell is in column A and Row 5.

The ANNUAL counting spreadsheet (workbook) is smaller (approx. 300 Kb) – and does not have the ‘back end’ i.e. the formulas to calculate totals etc. When you are finished your count for the year, you will need to send this to NHT to have the ‘back end’ updated into the spreadsheet.

The ANNUAL file is called:Counting_Annual_250815.xlsx

The SNAPSHOT counting spreadsheet (workbook) is very large (over 20 Mb) this cannot be emailed as it is too large for most email providers. This has the ‘back end’ built in and generates totals.

The SNAPSHOT file is called:Counting_Snapshot_250815.xlsx

This larger workbook can be provided to you in a couple of ways – on a memory stick or through setting up a drop box – ask for assistance if needed.

Both workbooks have the same first three worksheets – and are identified by the tabs at the bottom.

Worksheet1 – Daily datawhere you enter the date, type of access, and the numbers as they are counted

Worksheet2 – metadataA worksheet that you need to amend if you wish to tailor the list of activities so that the list on your spreadsheet represents only your House’s activities. Doing that should make things simpler (and more accurate) for those doing the counting and entering the information.

Worksheet3 – DCS ReportThe report worksheet will automatically pick up the information you enter into the daily data worksheet and generate a report for DCS that you can save, print and send to NHT (who, in the first year will then collate and send all reports to DCS). Note that there are a couple of places that you still need to enter information in, beyond what is automatically there.

You are able modify your spreadsheet through the ‘metadata’ tab. This is to be able to track any specific House activities that may or may not fit into the broad categories. The Categories document you have been provided as a part of this process should be kept near when entering data into the spreadsheet.

The CATEGORIES file is called:Categories_250815.docx

Collecting data from more than one spot OR using a combo of spreadsheet and hard copy:

Your House may have activity occurring in several locations, OR it may be easier to capture some/all information on a hard copy tick sheet and enter it into the spreadsheet at the end of each day or whenever convenient. A tick sheet is available for counting people and volunteers against activities. The info collected on hard copy sheets will need to be entered into the Activity reporting spreadsheet (Counting spreadsheet) to get the complete picture of activity occurring in the House across the snapshot week OR whole year.

The hard copy Activity tick sheet looks like this for each Category of activity:

Information collected via the Activity tick sheet can then be entered into the daily data spreadsheet, which ensures that all your activity information is included in your reporting.

This is how the daily data worksheet looks for the ANNUAL spreadsheet:

The SNAPSHOT spreadsheet has many additional tabs that are not able to be edited, these are used to calculate the totals for the various categories. These tabs are ‘calcs’ and mirror the categories (and colour coded to match).

1

If you have any problems or questions about your spreadsheet please call Alison 6777-1069.

Worksheet 1 – Daily data

  • Enter the name of your House in the Yellow section
  • Information is entered into the rows and columns
  • Sections that are ‘grey’ or ‘#N/A’ are locked and have a calculation embedded – you cannot enter information into these columns or cells

The Date will need to be typed in

The Access Type is provided for you – choose from the options shown when you click the arrow

The Priority column is a way to count how much activity is associated with one of your House’s Priority Areas – this will help with reporting on those Priority Areas. Note that snapshot counts may not capture all Priority Area activity. A separate spreadsheet might be required to use only for a Priority Area in an ongoing way.

Do NOT enter data into the Category column.

The Reason for Access column allows you to type a reason from the list of your House’s activities.

The Volunteer column needs a NUMBER – see the data definition about how to count volunteers, but in summary: “Each volunteer is counted as 1 for each occurrence of participation in a House activity”.

The Volunteer Hours Column required a NUMBER that represents time by the quarter hour. Note that 1 hour and 30 minutes = 1.5 (NOT 1.3).

The People column requires a NUMBER –see the data definition to know how to count a person, but in summary: “Each person regardless of age is counted as 1 against each occurrence of activity that they access the House for”.

Worksheet 2 – metadata

The metadata tab is a list of House activities grouped into Categories for ease of collating and sorting. It is advised to set up this worksheet to make things as easy for your House as possible. You can make sure that your metadata sheet is a list of only your House’s activities by deleting the rows that are not applicable to you and add activities that are not on the list in under the right Category. There is space to add activities under each Category section.

Worksheet 3 – DCS Report

Enter the name of your House in the yellow shaded cell.

Enter information about how your activity is being counted – i.e. Full Year or snapshot

Enter information about consultation activities over the whole year. (You may find this easier if you keep a list of typesof consultation activities and how many of each type, over the whole reporting year)

Reporting timeframes:

This information is required by DCS by 31 July each year, to provide information about KPI 1 – How much did we do?

1