fbpx
Search
Close this search box.

Source vs. Use of Funds – 14 charting alternatives

Share

Facebook
Twitter
LinkedIn

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.

data-for-source-vs-uses

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.

data format for mirrored stacked chart.

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

data for butterfly chart

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.

How to make butterfly chart - demo

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

This post is inspired by a forum question from Yaswanth92.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

Leave a Reply