I’m trying to make a pie chart that describes the reasons for changes in sales volume of a product from 2010 to 2011, and I’m having some trouble making one for this particular set of circumstances.
Let’s say overall sales were down by 10%. This -10% change in volume can be broken down like this:
-8% is due to prior customers buying less
-5% is due to prior customers stopping buying the product entirely
+3% is from new customers buying the product.
This adds up to -10%. Now, if all these factors were directionally the same, I could easily make a pie chart that attributed changes to various factors: for example, “40% of the loss was due to A”, “30% to B”, etc. But since there are some factors that are positive and some that are negative, I’m not sure how I can describe this situation. Would I say “-50% of the overall volume change was due to C”? Because that sounds horrible.
Maybe I wasn’t clear; it’s not the % changes themselves that I want to display. I want to display to what degree we can attribute change to different factors. As in, “we can attribute 75% of the -10% change in sales to prior customers buying less.” In other words, even though the total change was -10%, I want to be able to assign percentages to the factors within that -10%-- factors that should add up to 100%.
In this example, at least, you’re trying to quantify why sales went down … but you’re also talking about why sales went up. Now, obviously, either total sales went down, or total sales went up, but they didn’t go both ways.
Similarly, if there’s a -10% change, that can’t be because +3% new customers bought the product. The -10% change is despite those new customers; those new customers are a mitigating factor. You might want to mention them as a factor when analyzing sales performance, but they don’t belong in the group of causes for a sales decline.
Now, if there was a -10% change in volume, it can’t be made up of an 8% drop due to existing customers buying less and a 5% drop due to customers buying nothing – those numbers don’t add up. Was there in fact, an 8% drop in the number of existing customers who bought the product but didn’t buy as much, and a 5% drop in the number of existing customers who didn’t buy anything? If that’s the case, you’ll need to determine what percentage of the total drop in volume each of those factors accounts for, given that you also did see an increase in total volume from other sources.
I’ll let someone else do the calculations, because my head starts to spin and I’m sure it’s easier with the real numbers in front of you.
As someone who had to present this kind of shizzle a lot in my former jobs, I would not do a pie chart in isolation: I’d do three bars perched atop or beneath the X axis, representing the two negatives and one positive, then a final composite bar representing the net result of -10%.
If you want a pie chart, add it to the same slide afterwards with “% impact” as labels. ETA: following the same color scheme as the first chart and maybe with the positive segment split out from the other two.
I’ve created an excel spreadsheet to illustrate what I mean. (Note I couldn’t get Excel to make the right kind of composite bar, so I just went for a solid one.)
The pie chart is probably really inaccurate given the negative figures and whether or not the proportion should be compared with the whole, but it gives a rough idea of the impact of the three factors that would be comprehensible to a sales team: mathematical accuracy is less important than comprehension.
Thanks everyone. I think this helps… jjimm, I’m curious about your Excel spreadsheet though. I noticed that you took the absolute values of your percentages, and then used those to calculate the influencing factors chart. Can you explain why you think it’s legitimate to use the absolute values in this?
I’m creating an index called “effect on sales”. The three alterations to sales figures given in your OP are 8%, 5% and 3% (doesn’t matter if they’re positive or negative since each represents a proportion of the effect).
Since it’s now an index, and the units are relative to each other, it doesn’t matter if they’re %ages or not. I merely removed the % sign for convenience. Their values now just represent “proportion of effect on sales” relative to each other and their total; in index terms their total is 16. Now I calculate new %ages based on the proportion of each factor on the whole: -8% in the original represents 8 “effect index” out of 16, which is 50% of the “effect on sales” index.
This might not be what you’re looking for, but if I wanted to illustrate to a sales force what the most important factors are in terms of year-on-year alteration of sales revenue, that’s how I’d illustrate it. Presumably with such a visual snapshot, management could then extrapolate “well we need to put more resources into persuading existing customers to spend more, while using fewer resources in recruiting new customers - and we can leave retention to the marketing department” and the like.
Your problem statement is pretty succinct, and doesn’t really need a chart. A commented sum would do fine:
100,000 2010 Sales Total
(8,000) 8% decrease due to prior customers buying less
(5,000) 5% decrease due to prior customers buying nothing
3,000 3% increase due to new customer purchases