Charts and Graphs

Source vs. Use of Funds – 14 charting alternatives

Last updated on April 10, 2019

Chandoo

Let’s say you manage a fund or charity. You get money from various places and you use that money for various reasons. How do you tell the story of source vs. uses of funds? In this post, let’s review 14 charting options.

Source vs. Use of funds – Sample Data for this problem

Let’s say you have 3 sources and 3 uses, as shown below. For all these charts, we will use the same data, but may add some simple calculations or change the layout to get the charts.

As I made quite a few alternatives, I broke them in to two kinds of charts.

Simple charts: These are easy to make, easy to read and scale well when you have lots of sources & uses.
Exotic charts: These are somewhat tricky but fun to make, look exotic and raise a few eyebrows in the meeting room.

Simple Charts for Source vs. Use of funds

#1 – Waterfall chart

Waterfall chart is perfect for this kind of data. Simply layout your data like below (you can use SUBTOTAL or SUM formulas to calculate intermediate totals), select everything and insert waterfall chart from Insert ribbon. Mark total columns “as total” and you are done.

#2 – Stacked column chart

Data for stacked column chart - source vs. use of funds

Set up your data in two columns as shown aside and create stacked column chart. Format to your taste. Simple and effective.

#3 Couple of Pies

Use the same data format as “stacked column chart” but instead make two pie charts. If you have just a few options for sources and uses, Pie charts work great.

#4 – Mirrored bar chart

As you can see, sources and uses are two sides of the same coin, so we can go with a mirror metaphor like above. To create this chart, you need data in the format shown below. Once you have the data, just select it and insert a stacked bar chart. Format as you see fit and the mirror is ready.

#5 – Dollar background – stacked chart

This is same is option #2, but with a fancy dollar bill for plot area background and gap width for columns set to 0. When you make the fill colors for series semi-transparent, you get this effect.

#6 – Touching stacked bars…

Again, same format of data as “stacked chart #2”, but we make a stacked bar chart and then set gap width to 0 to get this effect. This works very well and makes it easy to compare.

#7 – Donuts

While I am not a fan of donuts or pies, they work great when you have small set of data like this. Set up your data like #2 stacked chart and instead make donut chart. Format and you get this.

#8 – Half a pie each…

This works very well if source total = use total. Just set up your data like this and make a normal pie chart. Format sources and uses in two colors like above and draw a line in the middle. You get above chart.

#9 – Sunburst chart

This is essentially similar to donuts (option #7) but we need data in a different structure (see aside). Sunburst charts work very well even when you have lots of items in each category or even sub-categories. Keep in mind though, sunburst charts are new to Excel 2016, so if you have older versions, you can’t get these.

#10 – Treemap chart

Same as sunburst charts, treemaps are a new chart in Excel 2016. They can work well when you have many items or even sub-categories.

Exotic charts for Source vs. Use of funds

#11 – Hourglass chart

The idea is Sources flow to Uses. So we make an hour glass shape with regular bar chart (with invisible series on left to push the items to center).

To create this, set up your data like below.

Now, simply create a regular stacked bar chart. Select Gap series, make it invisible (fill color > none), format source and uses series. You are done.

#12 – Butterfly chart

Sources and Uses as wings of butterfly. This is really an area chart, with an invisible series at the bottom to create the effect of butterfly wings. To make this chart, set up your data like below:

Now, Make an area chart. Move gap series to top (so it is really at the bottom when laid out), then make it invisible. See this GIF to understand how it all works to make the butterfly wings.

#13 – Waterfall lines

Instead of waterfall bars, we make this with lines. Unfortunately, this step chart is the trickiest one to create as we use XY plot with error bars to get the steps. The technique has too many steps, so I am not explaining them here, but if you are curious, see this tutorial.

#14 – Cube chart

Here the idea is Sources and Uses are two faces of a cube. Again, this is actually an area chart, but invisible gap series at bottom and a top layer. To make this, set up your data like below (remember, the horizontal axis has to be dates, else you will not get Cube effect).

To be honest, I am not a fan of cube chart, as it is tricky to label things and won’t work for more items. But I wanted to give it a try.

Download all the 14 charts…

I made a simple workbook with all 14 examples. Click here to download the file and play with it to learn. May be try making something on your own and post that in the comments section?

What is your favorite chart?

My favs are #1 (waterfall), #12 (butterly chart) and #8 (half pies).

What about you? What is your favorite chart among all these? Post in comments. Also feel free to make your own and share them so we can all learn.