How to calculate your grant funding per head of the population

 

94 grantmakers are now publishing their funding data as open data via 360Giving, making it possible to get a richer, fuller picture of the funding landscape across the country. And for individual funders themselves, publishing data in a standardised format makes it easier to analyse the data in order to improve their grant-making. A winner all round! We have put together a step-by-step guide below as to how you can calculate your grant funding data based on per head of the population below – hope you find it useful!

One challenge is how to present and communicate this data in an accessible and meaningful way, so that it can inform decision making for staff, donors, trustees and charitable groups.

Uploading grants data to Local Insight is an easy way to map grants data, as well as have the data instantly aggregated to the local areas you care about – whether that’s wards, parishes, or funding patches.

We have added Big Lottery grant funding as a dataset to Local Insight for all users and loaded this data in based on per head of the population.

Why calculate grant funding as per head of the population?

Calculating your grant funding as per head of the population is a useful way to ensure that you are comparing like for like, when looking at the distribution of grant funding across geographies with differing population sizes.

This can be particularly pertinent when looking at wards, which can differ greatly in size, with the biggest ward containing 43,359 people (City & Hunslet, Leeds) to 162 people (St Martin’s, Isles of Scilly) at the other extreme.

For example, if you saw that City & Hunslet had received funding of £4,000,000 during 2017 and St Martin’s had received £48,600 – at first glance, it may seem St Martin’s are getting a rough deal.

However per head of the population, this equates to £92 per head for City & Hunslet and £300 per head for St Martins.

Step-by-step: Your guide to calculating grant funding data per head of the population

The step by step guide below is for data that you hold at LSOA level. If you hold your grant-making data at postcode level, there are a few additional steps to take, in order to convert your postcode data to LSOA data. If you would like some advice on this, get in touch on info@ocsi.co.uk

What you will need:

• Your grant-making data, ideally in an Excel spreadsheet, with a corresponding LSOA code for each row of data (the beneficiary location).
• ONS Small Area Population Estimates (the link to the most recent data is in the guide below).

1. Decide which time-point to analyse

• Keep it simple at the start and choose a finite time point; perhaps either a calendar year or a financial year for which you have collected data.
• Filter your data and copy the data for your selected time-point into a new spreadsheet.

2. Check data for any missing cells

• Copy the column that holds your grants awarded data and the column that holds your LSOA codes (it should look something like this: E01031005) into a new Excel document
  

  Fig 1: Beneficiary location & grants awarded

• Ensure the columns are named clearly.
  • (If you don’t hold your data at LSOA level, get in touch at info@ocsi.co.uk to talk through your data.)
• Dependent on how many rows of data you have, you can check manually to see if any geographic data is missing
  • For missing data, you can assign an LSOA. For example, if you know the postcode of the beneficiary location, there are postcode to LSOA lookups available. Simply search for the postcode you are interested in, and find the corresponding LSOA code (word of warning: it is a pretty large file).
  • Alternatively, you can pop your postcodes into the IMD by postcode lookup, which will produce a file with the corresponding LSOA (as well as deprivation data on that postcode too).
  • If you have no information on the beneficiary location available, remove the whole row of data.

3. Reformat your data to remove duplicate rows of LSOAs

• It is possible that multiple grants were given to the same LSOA. In order to have one summed grants awarded figure for each LSOA, you need to: 
  • Highlight the columns with your location data and amounts awarded data
  • Create Pivot Table by going to Insert -> PivotTable
  • You will then need to use the PivotTable Fields to format your data
    1. Drag and drop the data about the location of your grants into the ‘Rows’ box
    2. Drag and drop the amount awarded data into the ‘Values’ box
    3. Ensure that the Values box states the data is being summed (See Fig2: if it isn’t as default, click on the drop-down within the ‘Values Box’ -> click Value field settings and select Sum from the choices) 

• This table then will give you a summed total for each LSOA and this is the data you will want to use in order to calculate grant funding per head of the population
• Copy the data from the Pivot Table and paste as values (to do this, right click on the cell you want to paste the data into -> click on ‘Paste Special’ -> select ‘paste as values’ option) into a new sheet.
• This is the sheet you will now work from.

4. Download population data

• Download the Mid Year Population Estimates for small areas, published by ONS (using the time-point that most closely resembles the grant-making data).
  • The raw data can be downloaded from here.
• Open sheet ‘Mid-2017 Persons’ (or the respective sheet for the year you are looking for)
• Copy columns containing the LSOA code information (called LSOA11CD) and the data on total population (called All ages) into the Excel document with your summed grants awarded and LSOA data.
  • Leave a few blank columns in between the grants data and the population data.

5. Match the LSOAs from the funding data to the corresponding population data

You can do this using a vlookup in Excel. The following guidance assumes you have the data in the following columns 

A – Beneficiary location (LSOA codes)

B – Amount Awarded

H – Area Codes (for the population data)

I – All ages population data

• Select all the cells that contain the population data (Columns H & I)
• Turn this data into a table by clicking Insert -> Table
• In column C, next to your first row of data, enter this formula: =VLOOKUP(A2,Table1,2,FALSE)
• Double click on the bottom right corner of the cell you entered the formula in to complete the rest of the column.
• This should give you the corresponding population estimate for each of the LSOAs in Column A.
• Name the column clearly (e.g. population estimate)

6. Calculate the amount of grant funding per head

• You should now have an Excel document with three columns of data
  • Column A: LSOA code
  • Column B: Grant funding (£)
  • Column C: Population estimate
• In column D (you could call this ‘funding per head’), enter the formula to divide the grants data by population data (=B2/C2)
• Double click on the bottom right hand corner of the cell, to copy the formula to the rest of the column
• Adjust the formatting so that the column displays as £
  • To do this highlight the column, right click and select format cell, then select Currency under the category heading and select the pound symbol.

Next steps

Hoorah – you now have your grant-funding data per head of the population.

From here, the world is your oyster:

• Use your data to create charts, data visualisations and maps, which can be used in reports and presentations
• Use your data to calculate values for other geographies, such as wards
• Compare your grants per head data to your raw grants data to get a different view of cold spots
• Compare your grants per head data to other datasets such as the Index of Multiple Deprivation, to see whether your funding is matching local need
• Go one step further and tailor the population figures to eligible grantees (for example, for funds that are specifically for women, use the female population estimates).

And if you want to do all of these things quickly and easily (with heaps of extra benefits thrown in), sign up for a free trial of Local Insight and we will show you how to do just this.

---

Featured posts