STUMP » Articles » Geeking Out: Improving Public Pension Dataviz » 29 June 2019, 09:24

Where Stu & MP spout off about everything.

Geeking Out: Improving Public Pension Dataviz  


29 June 2019, 09:24

This piece was brought to my attention:

Not All Cities Have A Pension Problem

The Top 21% of Cities With High Pension Funding Levels Deserve Attention and Usually Praise

With so much well-deserved negative attention focused on cities with huge unfunded pension overhangs, it’s probably a good time to draw attention to the cities that are not burdened by pension liabilities. Cities, which maintain adequate pension set-aside contributions based on reasonable actuarial assumptions, usually fit the bill of practicing responsible management and earn a pat on the back.

Only about three percent (3%) of all cities with populations of at least 30,000 have fully funded their total pension liabilities. Some of these have even overfunded their plans. Another 18% of these cities have funded their pension liabilities in the 90% to 99% range. That means that approximately 21% of all cities are in very good to excellent shape on pensions.

The people who pointed this out to me had questions re: pension obligation bonds (given that’s how a few of the plans managed to get to 100% fundedness), the assumptions used to value these, etc. I will likely talk about that… another time.

Because this is what I want to talk about:


You know what? I’m going to leave the critique as an exercise to the reader.

What I’m going to do is make a few versions of this visualization, ending with the one I prefer.


Let’s make sure I can match the original. It won’t look exactly alike, because it kind of looks like some elements may have been copied & pasted into the final image, as opposed to being produced together … or it may have been assembled in Powerpoint. No matter. I’ll use Text Boxes for what I need.

I may not have matched all the fonts, but that’s not my objection in the first place.

Ok, I will point out one thing: look at the percentages. Those are not percentages I calculated, but the ones Excel will automatically put on the slices if you ask for them in data labels.

No, I did not reproduce the sourcing, etc., right now. I’m going to focus on the main visualization and message.

I did not exactly match the colors, but I assume he used an older version of Excel, and thus had different color defaults. I just used the default colorway chosen when you make a pie chart.


Okay, let’s keep the pie chart to begin with, but make it better. I will make only a few changes… I leave it up to the reader to determine what I did.


So, here is where I start making key changes, because I get a different message from the data:

Note that I also changed the color (and pulled out the small bit of fully-funded plans).

There is no meaning to city plans being better than 90% funding any more than 80% funding. I am especially unimpressed given how many public pensions were >100% funded after our last bull run peaking in 2000/2001 (depending on timing of the fiscal year).

That only 41 plans are fully-funded is sad. I wouldn’t crow that only 3% of city pension plans are able to be fully-funded in a decade of market gains.


I hate pie charts. I detail why here. I think percentages, with respect to what percent of city pension plans are fully-funded, is a bit irrelevant. Just give me the count. And let me look:

I think that’s a lot easier to “read” than angles in a pie chart.


There are two purposes of data visualization. I’ll just copy myself:

Humans are very visual creatures. Even people who are blind have a grasp of spatial locations of entities. We are very good at gleaning quantitative patterns when we see them in graphical form, with quantities in relationship to each other, as opposed to looking at disembodied numbers on a page. People are very good at discerning visual patterns as well.

Given that, there are two main reasons for visualizing data:

Analysis of data —see if patterns and relationships really are there, as opposed to being statistical artifacts,


Communicating results —show other people important information, to help them understand better.

When I critique dataviz here, it’s always with respect to the second goal, because what visualization approach works for you to help you analyze… go for it. Communication is very different from exploring and analyzing the data.

If we visualize data to communicate truth, we want to make sure people can see the relationship between the numbers.

I don’t think people see angles very well, especially when you’re trying to get people to compare, in their heads, 722 vs. 41. That’s fairly abstract to most people. In fact, a table with sorted values will pretty much always be easier to read than a pie chart. So do that instead.

But if you really need to visualize components, column/bar charts are better.

I want to point out one choice I made that you may think odd: I got rid of grid lines or even the vertical scale. That’s because I have the exact numbers themselves on the columns. The grid lines & vertical scale provide no extra information, just visual clutter.

So now I leave it to you, the audience, to think through my choices.

There is one set of choices I’m not happy with, but I didn’t want to put more work into it. Can you guess what it is?

As always, you can contact me at or tweet at me: meepbobeep

Underlying spreadsheet with the charts.

Related Posts
Mornings with Meep: Data Visualization
Use Data Visualization Responsibly
The End of an Era: Where are the 80% funding myths of yesteryear?