Archive for June, 2010

The whole of government accounts: an exercise in elimination

June 25, 2010

When the large sample of COINS data was published on the 4th June it was accompanied by a guide to the data. The guide is very useful, but one thing it doesn’t explain in very much detail is where the COINS data comes from. The guide lists the inputs:

COINS – the Combined On-line Information System – is used by the Treasury to collect financial data from across the public sector to support fiscal management, the production of Parliamentary Supply Estimates and public expenditure statistics, the preparation of Whole of Government Accounts (WGA) and to meet data requirements of the Office for National Statistics (ONS).

But in what form are each of these different types of financial data  entered into COINS? To answer this question for the Whole Of Government Accounts (WGA) data, I’ve been looking more closely at the data gathered for this exercise.

After reading the WGA materials on the Treasury and the Communities and Local Government web pages, and chatting to some very helpful members of the WGA team, my understanding of the WGA exercise is that it identifies exchanges of funds between public bodies. These exchanges include the flow of funds from Central Government bodies down to Local Authorities and all the exchanges of funds between departments. When the transactions between public bodies are identified, the WGA exercise makes some adjustments to avoid double counting the money. So, if body A gives money to body B, then WGA would be responsible for subtracting the amount body B received from body A’s total.

As we know the COINS data is made up of spending or income records for each department. In these department records there is a Counter Party ID (CPID),  if that identifies another government department that means that some funds have been exchanged between the two departments.

There are scripts used on the COINS data to look for  eliminations using the CPID code.  which has the code in every department’s spending, if money of the public body money was exchanged with and the WGA team perform lots of checks on this. You can see this process happening in the adjustments table in COINS.

There are two guides to the WGA, one for  local authorities and the other for central government departments.

Central Government Accounts

The process of WGA for Central Government departments is simply that each central government department is required to fill in a C-Pack once a year, which is a spreadsheet constructed by the WGA team.

Point2.4. of the Guide for the C-Pack (PDF) says:

The key deliverable is the C-Pack, and the upload of Resource Accounts data and CPID data into COINS.

Local Authority Accounts

The WGA process for Local Authorities is a slightly different exercise. The Local Authority is asked by WGA to fill in an L-Pack once a year.  If you follow that link to the L-Pack excel spreadsheet that the Communities and Local Government branch of WGA prepare, they you will see that it is quite a complex looking creature. I’m going on a training course to understand it better, but I do know that the results of every local authority filling out this form amounts to quite a significant documentation of public spending and income.

In fact the WAG guide for local authorities states:

Local government controls over 50% of public sector fixed assets, accounts for about 25% of net public expenditure and represents 10% of UK GDP.

Now, here is the interesting part. The Local Authority spending and income that is recorded in the L-Pack is not in the COINS data that was published recently.

Now, I thought this missing detail in COINS might be because the WGA would be published separately.

There is a WGA report expected in spring 2011, but on further investigation it transpires that the level of detail will be the same as company accounts. We will get some extra details in this report, for example spending on PFIs will be included for the first time. But essentially we will miss out on all the lovely detail from the L-packs and C-packs.


The WAG is an exercise in eliminating excess data that clouds the picture of public spending and income.  The WAG team’s work seem to keep process of reporting spending and income more manageable. This is completely understandable. But on the other hand it would be great to have this detail of exchanges of funds so we can understand public spending as it really is.

UPDATE: I now understand the auditing going on it Whole Of Government Accounts better.

Auditing, I believe, means matching up buyers and providers:

A perfect match is:
Barnet Council purchases £5.5 m from Enfield Council.
Enfield Concil sales £5.5 m to Barnet Council.

The COINS scripts would eliminate this to zero as perfect match.

Another example:
Barnet Council purchases £5.0 m from Enfield Council.
Enfield Council sales £5.5 m to Barnet Council.
COINS would eliminate 5.0m and and put 0.5M into suspense.
The the suspense needs to be investigated more to see where the mistake is.
This investigation is the job of the Whole of Government’s Account team.
You can set a tolerance in COINS, which is the extent of the difference between two accounts it will put into suspense. The tolerance was set to 5.0m for 2008/09 accounts. I will be set to 1.0m for
2009/10 accounts.

COINS: the ins and outs

June 15, 2010

It’s been over a week, now, since the government spending recorded in the COINS database was free for everyone to see.

Before COINS was published I’d been looking into the different data gathering processes that ended with the data being stored in COINS. So you can imagine that having the actual COINS data is the final piece in the puzzle to understanding those processes. In short: it is all starting to make more sense.

In the next two blog posts here, I will describe two important processes that involve gathering data to be stored in COINS.

I’ve drawn a rough sketch of the processes in this diagram (click on the image to see more detail):

The two processes are:

  1. The Whole Of Government Accounts data gathering exercise which is performed by a branch of the Treasury. I’ve discovered this is an exercise in aggregating local spending, and avoiding double counting of spending in central government departments.
  2. The estimates and budgeting process which determines the resources departments are allocated.

I’m also writing a much more detailed guide to COINS, after more detailed talks with the very helpful people at HM Treasury, that is published on the Open Knowledge Foundation blog and will be published shortly on

COINS: a perfect example of a mystery

June 7, 2010

TV shows like Lost use mystery in a pretty smart way: giving tantalising clues as they go along and always making you want to watch more, to get some kind of satisfaction of knowing enough.

I’ve been looking into public spending and the mystery of how public spending records are stored is very similar to the TV shows.

I’ll tell you the story of the COINS database, which is a store of public spending and planning data, and you can judge for yourself how similar the story is to a gripping TV show.

The way COINS is described in documentation is that it’s a store for government spending records. In the background to reports on public expenditure, like the PESA report, and many of the Office of National statistics reports on government spending, the guidance says the reports rely heavily on COINS data.

I asked for the COINS schema, but what I got was the dimensions of the OLAP database and no description of the fields meant, so that was the first mystery. Then I requests and got the COINS training notes, but with all the screen shots and lots of the descriptions redacted, so this was the second mystery.

When I met some people from the Treasury to talk about COINS some more and they told me about the thousands of spending codes in COINS, which I then requested, but with a number of them redacted, this was the third mystery.

Then, on Friday 4th June, we got the COINS data itself, well a sample from this year, and some COINS guidance(PDF) to go with it. The guidance says:

It is possible that you won’t be able to recreate the numbers that Treasury or ONS have published. This is because:

• COINS has a single structure that is updated to reflect the latest classification of spend and organisation of government. The snapshots taken by the Treasury at defined points (e.g. to enable reports to be published) contain certain key fields, which then reference the latest structure. Any changes to the structure since the report was published may mean that it is not possible to recreate published figures;
• of the time difference between the publication of aggregated information and this release of data, with the potential that data have been updated between the differing points in time.
• Not all the data used to calculate these numbers are sourced from COINS.

So it’s not possible to check the figures against the published reports, like PESA, to check we understand the data published. Another mystery.

There is a description of useful combinations of the fields in COINS that would give accurate public spending figures from COINS — which means we are getting there.

So, what do you think? It will be satisfying when we understand all the spending!