Data Geek III Challenge – Analysing Government Expenses and Investments
I’m from Brazil and now it’s elections time here.
The elections are each four years and in October we’ll choose a new President and new Governors for all States.
There is a Federal Law in Brazil which obliges the Federal and State Government to provide access to financial data (incomes and outcomes).
There isn’t a standard format to provide this data, so, they provide in all sort of formats. They mainly provide an website with the consolidated data. There are few States which provide access to the raw data to be used by the citizens.
I live in Rio Grande do Sul, a State of Southern Brazil. How lucky I’m for my State provides the raw data!
So, I’ll analyse the present Government of my State using SAP Lumira and the public data.
First of all, I got the data from the last 4 years (from 2011 to September 2014), since this Government started, and then I worked hard to prepare everything because the files were huge, 4 files 2 Gb each.
I hope this analysis answer the questions below:
1. How much did the Government spend?
2. How did the Government spend our money?
3. What is the budget for investments?
4. Where did the Government invest the money?
5. Which are the biggest projects?
6. Who are the biggest suppliers?
When I started to work on the data my first impression was: SAP Lumira is awesome!!!!! Very easy to use and very beautiful, the UI is really good.
Well, let’s go there! Grab your beer and let’s check some numbers…
The first chart show us the Outcomes Groups and the amount for each group.
In blue we have the initial budget and the expenses in green.
We can see that the biggest group is PESSOAL E ENCARGOS SOCIAIS, a group to pay salaries and benefits for public employees.
4 groups spent more than their budget, but PESSOAL E ENCARGOS SOCIAIS and OUTRAS DESPESAS CORRENTES!!!
We have already paid almost $1.5 billion BRL just for interests (the State has a huge debt).
More than 50% is just to pay people…
Only 2.52% of the money was spent in investments!!!
Now we can see the relation between Groups and Functions.
Functions are the public sector’s operation area such as Education, Administration, Culture, Health, etc.
Below we have a chart with the invested amount in each Function.
There is a curious fact here which called my attention: the Government has spent more money in Justice sector than in Education… and I’m not talking about Public Safety because there is an specific function for it.
Following the percentages of the investments by Function.
Health has 13.5%, Education has 12.03% and Public Safety has 11.78% of the total amount invested in 4 years.
We’re going to see the top 10 biggest projects, that ones which have received more money from the Government in the last 4 years. The projects are identified by the Functions which they are related.
And the winner is… PAVIMENTACAO DE ACESSOS MUNICIPAIS costing 129,086,988.59 BRL for road paving in the cities.
Finally, we’re going to find out who is the biggest Government supplier till now.
That’s it guys! I hope you have enjoyed and that my analysis could help some people to understand how our present Government has been spending our money.
I had to get over some challenges because the files were too big and SAP Lumira Personal Edition is neither able to load a dataset bigger than 4 Gb nor bigger than 30 millions cells, by default.
It took few hours, but I found out that I could change some parameters in one file to be able to load it.
I’m in love with SAP Lumira!
Congrats to the Lumira team!!! =)