Datageek III challenge 2014: Dutch Government expenditure

Social as Government Budget is about making choices for causes

Every September the new political year starts and plans are being published on the plans for the coming year. Always this leads to a massive amount of articles with opinions. You can download the files with numbers but inevitably you have to wade through a massive amount of numbers.

With SAP Lumira I did an analysis on the expenditure plans and tell the story I found. This under the motto “I will analyze this so you don’t have to”.  This means that the resulting story is so self-explanatory that you don’t need to dive in the data yourself to understand it. If you really want to you can read on after the story where  I will shed some light on the process on how I came to find the story.

Visualizing the infograph.

My goal was to tell a complex story so I needed to add a lot of information. My self-made scope was that it had to fit on a iPad. Despite the amount of information the people reading it should not feel overwhelmed when looking at the screen.

Tell the story

So what’s the story about? It is a story how you can start at a general picture at the top where you notice that the budgeted expenses are less than the commitments. In the original file you can find these like this :

So why is that important? Well after looking up in the budget manual. It appears that commitments are laws that say. “we are going to spend this much to these causes”. So If the budgeted expense is less then you need to change laws to make this work. Meaning that causes are getting less then was committed before. The other way around (more budget) means the basically that the minister of that department has some loose change to spend.

So the first question is, Does the government have commitments they have to stop for the next years?

The answer is clearly yes.

So the natural next question is. Where is the biggest gap between the budgeted expenses and commitments? That’s were part 2, the treemap comes in.

You clearly see by color that “Onderwijs Cultuur & Wetenschap” Meaning Education, Culture and Science is one of the largest budget and moreover the color indicates that there’s a relative big gap between budget and commitment.

So that leads us automatically to the next logical question: Where in that departments are the cuts?

In the horizontal bar chart we see the difference between budget and commitment. It is clear that on the one hand “Cultuur” (culture) is facing cuts but on the other hand “onderzoek en wetenschappen” (research and science) there is some money that still can be freely spend.

The final question is, well if they have such gaps, what does their budget look like? On the right you find the final answer. Science is increasing and keeps increasing, Culture takes a hit, but evens out after that.

In my analysis I basically went through the same steps as I put in the infograph. If you’re interested I’ll share some tidbits of things that I did during the analysis.

Preparing Data

The source files are freely available on http://opendata.rijksbegroting.nl/. There you will find a link “Begrotingsstaten” containing a CSV file with all the numbers.

There are two issues I had to solve

1. 1)      The numbers are doubled twice
2. 2)      Some numbers are x1000, some are x million

Additionally I wanted to compare Expenses to Commitments and therefore I created a Keyfigure for Expenses and for Commitments.

Undoubling

The amounts per “onderdeel” were totaled to “BegrotingsStaat” as you see below.

<screenshot_double>. Additionally there was a BegrotingsStaat with value “Rijk” that was a summation from the other “Begrotingsstaten”. The others were “onderdelen” for these records.

I filtered out all rows. Used Begrotingsstaat = “Rijk” for the top graphs and excluded these rows for the drilldown.

Toscale

The amounts in the rows were sometimes times 1000 (“x1000”) and sometimes times 1.000.000 (“miljoenen”). I used calculated dimensions to get these numbers in line like this:

I used this form to avoid nested IF THEN statements. As if only one can be true the other will evaluate to 1 and won’t influence the outcome as the outcome gets multiplied by one. This structure works well to avoid complexity in your formula’s as nested IF THEN’s get very complicated when you get a few layers deep.

Creating a Keyfigure for Expenses and for Commitment

Finally I wanted to compare the both so I made a filtered keyfigure for both by using this formula :

This concludes my contribution to the Geek challenge.

Hope you enjoyed it!

Best regards,

Jeroen