fbpx
Search
Close this search box.

How Would You Visualize Product Sales Data? [Excel Challenges #2]

Share

Facebook
Twitter
LinkedIn

We have a new Excel Challenge folks!

I know our friends in US are away celebrating Memorial Day weekend. But that should not leave rest of us from fun. So, we have a new Excel Challenge. This time, you need to make a chart, to visualize product sales data.

The data you need to use:

Is this,

Excel Challenge #2 - Visualize Sales Data

Download Excel File with this data.

The Instructions:

1) Make one chart (you can make panel charts too, but not a dashboard or multiple charts) to visualize the product sales breakup in our data. Your chart should help answer any of these questions:

  • What is the overall composition of revenue, how it is changing over time?
  • Which products bring in more $s?
  • Trend of sales from Jan to May

2) You are free to make charts that answer additional questions. Just think like a owner of a company that sells these products (think like me, because these are the sales of some of our products) and try to make sense of the data.

3) You can use Excel or Tableau or your favorite visualization tool to make this chart.

How to submit your work?

  1. Prepare your chart and save the workbook
  2. Send an email to chandoo.d @ gmail.com with the subject “EC 2 – Solution”
  3. Or upload your work to skydrive.com, share it with public and post the URL here thru comments.
  4. Note: Submit your work by 6th June , 2011 This contest is closed now.

What do you get?

I will post a collection of all your entries on Chandoo.org for our readers to see. I will also pick one random winner from the best charts.

The random winner will receive an Amazon Kindle Reading Device – Wi-Fi Version

Fine Print

  • Note: Submit your work by 6th June , 2011 This Contest is closed now.
  • You can submit multiple entries
  • Or upload your work to skydrive.com, share it with public and post the URL here thru comments.
  • Email your workbook to chandoo.d @ gmail.com with the subject “EC 2 – Solution”
  • Please do not use add-ins or macros to generate the charts.
  • Please send unlocked files only. You also agree that Chandoo.org can publish your files for other to learn.

So go ahead and visualize the product sales data. Enjoy!

Previous Visualization Challenges

Charting is one of my favorite areas. Charts are very good for discovering patterns and communicating. We run contests on charting all the time. Browse some of our previous contests,

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.

28 Responses to “How Would You Visualize Product Sales Data? [Excel Challenges #2]”

  1. [...] Chandoo’s excellent Excel and Data blog has a data challenge to win a Kindle. I’m not entering but thought I’d show what Tableau can do with just a few hours of work. The challenge was to create a single chart that showed the data story the best. Below is what I think works, but the visualization also provides the ability to look at the data in different ways (running sum, difference from prior month, and so on) to explore it further (can this still be considered a single chart?). [...]

  2. Michael Azer says:

    Hey Chandoo, I sent you an email with my solution.
    Can't wait to get the Kindle 🙂

  3. Shellie says:

    Just to clairfy - does Per unit Revenue mean the amount of revenue each unit of a product produced? ie. Dashboard Tutorial #1 in Jan generated 29x$37= $1073 Revenue

  4. Chandoo,

    are you sure that you want to add up QUANTITIES of different products and not REVENUE?
    Hope for reassurement in time for end of contest.
    Thank you in advance.

    Best regards,

    hgl

  5. Chandoo says:

    @Shellie.. you are right.. Per unit revenue means unit price. You can multiply that with number of units to get total revenues.

    @Heinz... I am not sure I understand your comment. But you are free to make any type of chart as long as you can justify what information it provides

  6. Hello Chandoo,

    thanks for answer. But may I try to ask for your enlightenment ?
    1. In your answer to Shellie you say: "Per unit revenue means unit price" and "You can multiply that with number of units to get total revenues". That leaves me a bit confused about the type of the value in your table. Would you help out with an additional comment?
    2. The last row in your table sums up quantities of 8 different products. In school they taught me never to add apples and oranges, because the 'result' wouldn't make sense. Maybe I don't grasp the idea behind the calculation here. Could you clarify?

    Thank you in advance.

    Best regards,

    H.G. Lamy

  7. Tessa Sprunken says:

    Hi Chandoo,

    I have uploaded my entry (created with NeoOffice) on skydrive: http://tinyurl.com/5v3kd7v
    There is a pdf-screenshot and .odf-workbook

    Kind regards,
    Tessa

  8. Chandoo says:

    @Heinz... Each sale is by one customer (well, we have a very few customers buying multiple copies, but you can ignore that). So, the totals indicate the total number of customers we have.

    I added up the numbers because, that way I know how many units I sold (together) thus I know how many customers we had in a give month. I know the total does not make sense, but it is there just for info.

  9. Sayyad says:

    Chandoo, you've not mentioned when the results will be declared.

  10. Hi Chandoo!
    I've just seen this thread! A quick make up for this contest! I've posted my file at https://cid-4bb533dbfb43de31.office.live.com/view.aspx/.Documents/mohanvisualize-product-sales-data.xlsx. Hope, to see the best one among the entries.
    Krishnasamy mohan

  11. Nuruddin Kajani says:

    Hi Chandoo,

    Sorry for joining in so late, nice contest, hope to see amazing stuff.

    Office firewall cannot access Skydrive, So sent my entry via email.

    Regards,
    Nuruddin

  12. Meedan says:

    Desperately waiting to see different charts prepared by everyone & results.
    When will it be displayed ?

  13. Arpita says:

    Hi Chandoo,

    When will the different entries be posted ? Had mailed you my entry last week

    • Chandoo says:

      @Arpita & all.. Thanks for participating in this. I received more than 50 entries, so it will take time to sort thru the emails and post the results. Please give 2 weeks time.

  14. Fred says:

    Just sent one in (i know it's late). I'm not trying to join the contest now. But just want to show that there is a price sensitivity between total revenue and quantity sold due to changes in prices. Too often marketing or sales would raise product price just because it was selling hot in the short run. but fail to realize that customers may take a flight for competitive products, even if there is a slight change in prices.

  15. Gadadhar says:

    Chandoo,

    When will you post the winning entries

    Gada

  16. Vicky says:

    Ten days over ---> Still not able to see entries.

    Please confirm how much more time will it take.

  17. Hui... says:

    @All

    Chandoo recieved over 50 entries and they are still coming in.
    I am sure he is busy assessing them as well as running the Chandoo.org site, Chandoo.org Forum and Excel classes as well as his regular Excel Training courses.

    I am sure he will get to it as soon as he can.

  18. Sachin says:

    Hi Hui,

    Thanks for the information.

    In case if it is at all possbile that Chandoo or you can confirm the date when results will be out so that we dont have to peek every now & then to know whether the results are out or not.

    Please if at all possible.

    • Hui... says:

      @Sachin
      I'd recommend peeking every day!
      As there is always new and exciting posts coming and we would hate you to miss them! 🙂

      Next weeks sneak peek
      + Automating Data Analysis without VBA
      + User Poll (Accidentally published this week by mistake and quickly withdrawn)
      + Automating monotonous tasks with VBA

      I wouldn't miss any of them!!!

  19. Sachin says:

    Hui,

    That sounds interesting.

    By sneak peek I meant to say for only this particular thread. You know it takes so much pain to find this thread & goto last comments to see if Chandoo has posted any updates about the results. But everything goes in vain as you dont see any updates. (My hard luck)

    I am not interested in winners but particulary Different Charts which users have posted using their analytical skills & that too 50 of them. I am more excited now !!!

  20. Mike H says:

    @Sachin, I'm sure that Chandoo will send everyone an email when it's done. So, if you don't want to keep on checking back on this thread, then just wait for an email.

  21. Chandoo says:

    @All.. thanks for your patience.. It has been quite intense week as I was busy recording lessons, writing new posts and replying to 50 odd emails each day. But I squeezed sometime yesterday to download all the files and name them. Then I gave them to my assistant (Ravindra), who took screenshots of all the charts. So, now we have 80 charts made by 50 contestants. The next step is to go thru these, open the excel files, learn more about the charts and make a mammoth post.

    However, I cannot do it next week, As I am busy doing an in-house training for a corporate client of mine. So, you need to wait at least 10 days. Sorry to under-estimated the amount of work this takes. But I am learning a lot from your submissions, so it is fun.

    @Sachin... when the results are out, they will be posted in a new article. So no need to check this page. Also, you can subscribe to comments, that way, I send an email when there is a new comment on this page.

  22. Sachin says:

    @Chandoo

    Thank you so much for the update.

    You are doing a wonderful job. May God bless you always.

  23. Gadadhar says:

    Chandoo! We're waiting!

  24. [...] I ran a contest asking you to analyze a bunch of sales data and present your results in charts. We received a total of 78 charts from 45 people. The contest [...]

  25. venkatesh says:

    @Chandoo....

    thankyou so much for bringing variety and making our brains tickle

Leave a Reply