Brendan Walpole

Revenue, spending or entitlement – what’s the country’s problem?

No comments

The workbook used throughout this article is available here.

I saw this video the other week where Paul Murray from Sky News concluded that we don’t have a revenue or a spending problem, ultimately we have an entitlement problem.

This pronouncement was based on ABS data showing that total taxes paid in Australia, to all three levels of government had increased every single year (it starts only 25 seconds into the video). Being a bit of a sceptic, my first thought was “what is he hiding or spinning?” and it didn’t take long to figure it out. The easiest way to sell a story based on a trend is usually to select your data carefully. In this case, the first year presented was 2009-10, otherwise known as the year after Lehman Brothers collapsed. So surely tax revenue was low then and is now recovering? So off I went on a data hunt.

Data sources

All numbers used in this post have been obtained from the Australian Bureau of Statistics.
Tax figures were obtained from series 5506.0 – Taxation Revenue, Australia.
Government sector incomes and expenses are from item 5512.0 – Government Finance Statistics, Australia.
GDP is from item 5206.0 – Australian National Accounts.
Inflation is contained in item 6401.0 – Consumer Price Index.
Population is from item 3101.0 – Australian Demographic Statistics.
The workbook that consolidates the relevant bits of data from these sources, and contains the calculations and charts used in this post can be downloaded here.

Data issues

There are minor discrepancies between the tax income figures contained in items 5506.0 and 5512.0. These are partly due to one level of government receiving tax from another, but there are other differences that I did not identify (yet). The maximum difference between the two in any one year is 1.8%. When looking solely at tax income, I have used item 5506.0 data as it went back to 1993, whereas 5512.0 only went back to 1999 in its current form.
GDP data uses the “Current Prices” sets rather than “Chain Value” or “Real,” so as to present fair comparisons with taxes and expenses which are also presented in “Current Prices.”

Government income and expenses

Sure enough, Sky News’ use of FY2010 as a starting point was critical to their story.
screenshot govt income raw
Total tax revenue decreased in both 2008-09 and 2009-10, although it only took one year to again be higher than the previous peak in 2007-08. Local government revenues were quite robust, increasing by 7.5% and 6.5% in those years. The last time tax revenue growth for local governments was less than 5% p.a. was in 1998-99 – and you wonder why there is a lot of talk about a broad-based land tax!
Apart from 2008-09 and 2009-10, the only other time any level of government decreased tax revenue since 1993 was state taxes in 2000-01, due to the introduction of the GST being credited at the Commonwealth level, with various state taxes reduced or eliminated. And the increase in Commonwealth taxes in this year more than made up for the states’ reduction.
Overall government income has been more stable. Since 1999, the lowest increase in total government income has been 0.4%, and only 4 times in 16 years has growth been lower than 4%.
Government expenses have also only increased since 1999, by a minimum of 1.2% in 2012-13 and a maximum of 15.4% in 2008-09.
chart govt expense raw


Looking at raw numbers like this probably doesn’t tell a fair story. As the economy grows, you would expect taxes and other government income to grow too.
chart govt income gdp
This chart shows a slightly different story – that the Government-sector share of the economy has, from an income perspective, been declining over the last 15 years, with a slight reversion over the last 5 years.
chart govt expense gdp
Until 2007-08, Government expenditure was declining in a roughly similar pattern to income, although somewhat smoother. This sharply reversed in 2008-09 as the Rudd government stimulated the economy with pink batts, school halls and cash handouts, and has only partially given back this increase in the following years.

Inflation and population growth

Instead of comparing Government income and expenses to the size of the economy, they can also be measured based on the population.
chart govt income inflation population
chart govt expense inflation population
Unlike the GDP ratios, these show income and expenses increasing slowly over time. The pattern can be most clearly seen when based on per capita numbers.
chart govt income expense per capita
Figures here are based on real 2012 dollars. What is clear is that over a long period of time, governments have been earning and spending more per person, with the actions taken during the GFC having a significant effect on financial performance.


So what can we take away from this.
Restricting (or expanding) your range of data can enable you to tell the story you want
Raw numbers are not always the best things to use, unless it helps your story – comparisons to other numbers are usually useful
Compared to the overall size of the economy, Government revenue and expenses have not really been increasing that much
Per person, revenue and expenses have generally been increasing over time.
This also takes me back to a recent conversation with a colleague regarding statistics, and how in a lot of cases, you can use different techniques to come up with what you think is the “right” answer. Their response was that there was only one right answer. Taxes and spending have again shown that, regardless of the data, what is “right” often depends more on a point of view than raw numbers.

Govt tax and spending workbook

195.73 KB 683 downloads
Brendan WalpoleRevenue, spending or entitlement – what’s the country’s problem?
read more

The pension asset test and taper rates

No comments

The workbook used throughout this article is available here.

The Coalition and the Greens have cut a deal to implement the Government’s proposed changes to the pension system, specifically through the “Asset Test”. After the Greens repeatedly said there was no deal. This is what makes politics interesting, apart from watching “The Killing Season” of course.
As you’ll have probably seen elsewhere, the major change is to the “taper rate,” but there are also changes to threshold levels at which the full and part pension are paid. If you want to read some neutral coverage of the deal, you can try this article from the AFR, the positive spin from the Government here, positive spin from the Greens, and Labor calling it either a dirty deal, a grubby deal or sometimes both filthy and dirty. (Political disclaimer – For the avoidance of doubt, I am not associated with any political party and seek to criticise all parties equally when the opportunity arises, such as my critique of both Labor and Liberals alike for the metadata retention regime.)

Brendan WalpoleThe pension asset test and taper rates
read more

Financial covenants series

No comments

We have just launched a series covering a number of the financial covenants that you might find in loan documents – whether a standard business/corporate loan, a leveraged financing or a project financing. As there are a lot of them (with more to come) they have been put in their own section of the blog.

So head on over to the Financial covenant series section to check it out.

Brendan WalpoleFinancial covenants series
read more

Sensitivities and Scenarios

No comments

Formulas used: Offset, If, Conditional formatting
The workbook used throughout this article is available here.

You’re using a financial model and someone asks you to run a sensitivity on x or to run a scenario. Or someone might refer to setting up some scenarios for analysis. They sound almost the same, but can be a little different in practice.
A sensitivity analysis usually involves looking at one variable in isolation and assessing how it changes your results. Scenario analysis usually involves looking at a combination of variables, and usually a number of pre-set combinations.

Brendan WalpoleSensitivities and Scenarios
read more

Dynamic Charts

No comments

Formulas discussed: Charts, Offset, Ranges
The workbook used throughout this post is available here.

Charts can be a wonderful way of presenting data to an audience – just look at the popularity of infographics. Sometimes, particularly with line graphs, you might want to change the start or end point of the graph, either so you can zoom in on a subset of the data, or to remove some dodgy-looking bits. An example of this would be where you have a model that includes an upfront construction period, with certain things not being calculated until construction completion, and the completion time might not be fixed. In this circumstance, you don’t really want to display all those blank or zero cells (see workbook example 1).
screenshot - chart 1

Brendan WalpoleDynamic Charts
read more

Net Present Value by first principles

1 comment

Formulas discussed: NPV, XNPV
The workbook used throughout this article is available here.

In a previous post we looked at how Excel calculates NPVs and IRRs and some of their weaknesses. In this post we’ll show you how you can manually calculate an NPV so that you have greater flexibility and control, and know exactly what kind of answer you’re getting.

The first part of the workbook is a refresher on how NPV and XNPV produce different results, based on their different assumptions of the start date. XNPV is useful if you are not dealing with constant, annual periods, and means you don’t have to separately calculate a discount rate for the particular period length if you start with an annual rate.
screenshot NPV annual

Brendan WalpoleNet Present Value by first principles
read more

Nothing to hide, everything to fear – why metadata retention is bad for everyone

1 comment

Australian government efforts to implement metadata retention laws are all over the news. If you read the newspapers or their websites, then you know that not even the Attorney-General seems to know what metadata will be collected, the Australian Federal Police says you have nothing to fear and they’re not interested in you, the government is only after terrorists, paedophiles and organised crime, and the data may or may not be used to see if you’re downloading movies. From the same sorts of sources, you’ll also know that the only possible problems with this regime are that journalists and whistleblowers might be more vulnerable, and that it will cost a lot of money to implement, even in this time of “budget crisis.”
Now don’t get me wrong, I can see why our law enforcement and intelligence agencies would like this bill implemented – if I can see a tool that will make my job easier, with no cost to me, I’d want it too. And yes catching terrorists and paedophiles (why does everything always come back to these two groups?) is a good thing. But just because you think spying on every citizen is a bad thing, does not mean you’re on the side of terrorists and paedophiles.

Brendan WalpoleNothing to hide, everything to fear – why metadata retention is bad for everyone
read more

The perils of data analysis – speeding fines and the road toll

1 comment

Excel formulas discussed: Slope, Correl
SPSS formulas discussed: Correlation, Regression, Oneway

I was reading an article on CarAdvice recently that argued that increased speeding fine revenue in Victoria had done nothing to reduce accidents, and that in fact speed cameras were dangerous. The argument was that while deaths had decreased, safer cars had just transferred deaths into hospitalisations; accidents were continuing at roughly the same rate but the outcomes were better. Whilst I would tend to agree with a number of the statements regarding low-level violations (particularly as I recently copped a speeding fine on a motorbike when sunglare meant I couldn’t see the speedo for a minute, and I clearly misjudged engine sound), I thought that some of the logical steps and data analysis (or lack thereof) were worth looking into.
In making the claim that speed cameras don’t reduce the road toll, the article relied on the data in this graph.

Brendan WalpoleThe perils of data analysis – speeding fines and the road toll
read more

Melbourne cup sweep

1 comment

Just in time for the race that stops the nation, here we have a simple sweep spreadsheet you can use instead of ripped up bits of paper at your local bbq.
On opening, make sure you “enable macros.” Then it’s just a matter of pressing the “Start Draw” button at the top of the “People” sheet, type in people’s names as they enter the sweep, and they’ll be allocated a horse. Job done.

If you need to do a redraw for some reason (otherwise known as cheating until you get the “right” horse), press the button again and new horses will be given to everyone.

Happy punting!

Brendan WalpoleMelbourne cup sweep
read more

Dealing with Circular References


Formulas discussed: Algebra, VBA (Copy, Paste, Select, Range, If..Then..End If, For..Next
The workbook used throughout this post is available here.

The problem

When building a model, it is not unusual to, either accidentally or deliberately, build a circular reference. A circular reference is where the result of a formula depends on itself. This could be as simple as B5 = B4+B5, or a complicated loop where cell A references B, which references C, which references D, which references B and E, while E references A, as in the diagram below:

Brendan WalpoleDealing with Circular References
read more