Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.
This problem is sent by Fiona.
Situation: Our commissions are growing way faster than revenues
Let’s say you are looking revenues & sales commissions of your company for last few years. The data looks like this:
And you want to highlight the fact that commissions are growing faster than revenues.
So you plot YoY growth rates for revenues & commissions.
Problem: The chart of YoY growth rates is not convincing
Take a look at the chart. It doesn’t convey the message that we want. At best it says “revenue growth is less than commission growth”
How to convey the message “Commission growth is a problem for us”?
Option 1. Use indexed charts
When comparing 2 sets of values (that are in different order of magnitudes) over time, we can use indexed charts. They can tell the story of how the values have changed over time clearly.
Here is the indexed chart for our data:
How to create this chart?
Simple. Just follow below steps.
- Calculate index values. Assume first year value for each series as 100 (so revenues = 100, commissions=100 in year 2010)
- For next year, calculate the value as this year value / first year value
- Plot these indexed values on a line chart
- Adjust the line chart axis minimum to 1 (or 100%) if all values are >1
- You are done
Option 2. Visualize ‘for every % in revenue growth, commission grows by…”
We can calculate what is the change in commission growth rate for every % growth in revenues & plot this. This will depict the situation in a powerful & dramatic fashion, like this:
How to create this chart?
Even simpler. Just do these steps:
- Calculate % values by dividing YoY commission growth with YoY revenue growth
- Plot this as a column chart
- Draw a line at 100%
- Add a text box at this line and write “Ideal” on it.
- You are done.
Download Revenue vs. Commission growth charts
Click here to download the example workbook. Examine the formulas & chart settings to learn better.
How would you present this information?
My favorite approach is to use indexed charts. They are designed for this exact purpose.
What about you? How would you visualize this kind of information? What charting techniques will you use to get your message across? Please share your inputs in the comments section.
30 Responses to “Great News: Chandoo becomes MVP”
Congratulations! It's well deserved. 🙂
This is amazing. Hearty congratulations and a rocking new year ahead!!!
Congrats! I have learnt alot from your site... and the most important is learning how to achieve the most with the simplest concepts.
Thanks for the excel calendar. Is it possible to get a simple big fonted calendar printable on an A4 size paper without any distracive notes or visuals? BTW, I have already signed up for your newsletter. With warm regards and
Gratefully yours
50+ year old CHarish.
Hey Chandoo,
Great to hear that. Congratulations! The best new year gift, I would say. Keep it up, u've been doing extraordinary work for the excel users community.
Regards,
Pankaj Verma
Congrats dude... fantastic news!
congratulations! your site is great, this is well deserved
Rich
I recently found your site, I visit many. The tips that you provide are in the top 1% of all the sites I visit. Keep on Excelling.
Arnold
South Africa
Congratulations, Chandoo! That's a great way to start the year and make the PHD even better.
Congratulations, Chandoo.
Your site is one of most useful on the net. Happy new year and lot of ideas you will present for us.
Congrats.
Just read your name in an email from Abhishek. Well deserved.
Congratulations, and Happy New Year.
Greetings from Rio de Janeiro my friend! You trully deserve it!
Nive way to start 2009! Keep up the good work!
FC
That was quite forseeable , so you have now really got your PhD in excel.
Anyways Chandoo you have made excel a real Fun doo
I will like you to write some more on INDEX and MATCH function in near future.
@Hey Chandoo ! Congrates....
Ab to treat mangta hai !
Well deserved Chandoo!!
Congrat's!! Very well deserved 🙂
i always browsed mr.excel and used to see MVP writtne below names of people who used to solve queries in excel forum there......i just used to admire as to what they have special in them that they are MVP......
but now i got my answer...............u deserve it man..........
@All: thanks everyone 🙂
Congratulations Chandoo, nice job!
Chandoo,
A well deserved recognition and a good start to the New Year. Continue your good work.
Subbu
Many Congratulations.
You deserve a Ph.D. 🙂
congrats.....
Congrats dude. Rock on!
[...] charting community in 2007 and has been growing strongly ever since. In year 2009, I have received the MVP award from Microsoft. Just few days back I have become a dad [...]
Respected sir,
I am impressed!.... Good job done.. Keep it up...
Sir, How to be a MVP certified person. What level of knowledge is required for it? send me links if possible.
Please reply...
Regards,
Dipak Khalasi.
Dipak -
The first thing you need to cultivate is the ability to search the web effectively. You could start by Googling "Microsoft MVP".
[...] boy and girl which has been made hectic and incredibly fun ever since to their life.He has been awarded MVP status in 2009 by Microsoft(and renewed in 2010,2011 & 2012).His MVP profile is here.If you want to contact him direct then [...]
Congrats Chandoo!!
[…] Chandoo becomes MVP […]