Source vs. Use of Funds – 14 charting alternatives

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

#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

#8 – Half a pie each…

#9 – Sunburst chart

#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.

Like charts & visual story telling? Check out these amazing examples

If you are in to data analytics & visualizations, you are going to love Chandoo.org. We got tons of examples and tips. Start with these…

• Examining the grammy bump in Excel

• How people spend money? – Panel chart

• Awesome Budget vs. Actual chart template

• More charts & dashboards

This post is inspired by a forum question from Yaswanth92.