Excel Links – Next Dashboard Contest Edition
Almost an year ago, we had a memorable dashboard contest on Sales Dashboards. We got 32 beautiful, outstanding, well crafted entries and it was a lot of fun learning new tricks & meeting new members of our community. I am planning to have one more dashboard contest and I need your help.
I need you to suggest a topic for the contest (optional: and give some sample data)
Just drop a comment with your suggestions (and point to source data if any). We will have a dashboard contest on one of those topics.
Few Excel Links to keep you busy:
It has been a while since I shared some Excel related links with you. In this post, I am going to share 5 articles that have taught me an important excel trick or data analysis nugget. Go ahead and enjoy these links.
Beginners Guide to Web Analytics (and why you should read it)
Avinash has one of the best blogs on Web Analytics. But I read his blog for another reason too – to understand how to analyze data. In this post, Avinash shares 10 excellent ways in which you can analyze data and understand the patterns. Very insightful stuff.
Start your next dashboard with a sentence
Jorge at Excel Charts recommends writing a simple sentence to describe dashboards. This will help you in constructing better dashboards. Very good advice for dashboard makers.
I have wasted countless hours as a kid playing Tetris. I think when I see a falling brick, I feel the compulsion to arrange it into a cavity. So naturally I am impressed with this Excel Tetris game made by George. Go check it out.
Embed Wordle word clouds in to Excel
Robert at Clearly and Simply shows us how to embed wordle word clouds in to Excel. Very good demonstration of a powerful technique.
Fill a combo box with unique values – Excel Record-set Tricks
Mike shows us how to use record-set feature in Excel to show unique values in combo boxes. Useful technique for those of you building models, complex dashboards using Excel.
Your can also browse more Excel Links here.
Share an Excel Link with us: If you want to share a link with our community, just drop a comment or send me an email at chandoo.d @ gmail.com. I will be glad to share it with all the readers.
Have a good week ahead.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Leave a Reply
|« Mutant Cell Modeling Problem [Homework]||6 Christmas Cards in Excel – Download and Spread Joy »|
24 Responses to “Excel Links – Next Dashboard Contest Edition”
User performance data would be good.
KPIs that sort of thing. It is however very similar to the sales dashboard.
I was looking at last year results the other day. There were some interesting ideas there, I look forward to the challange.
Some possible ideas:-
Google analytics data of some sort. Most of it can be exported to CSV for Excel so it might be interesting pulling out some data on visits etc and creating something useful that Google analytics doesn't already provide.
You've already done some work on comments but it might be interesting seeing what other people can come up with.
Looking forward to the challenge!
How about a project progress measurement dashboard (for pro only) that ease Managers following their projects. Cool things to show are :
- progress (obvious) global and with breakdowns in Lots, Task Types, Functions.
- Budget plan vs realized and history
- Milestones (and their modifications)
- TODO Lists.
For all others, (non pros) I would suggest a "Car consumptions" dashboard.
I Would display :
- Costs by category (insurance, petrol, mechanics...)
- Petrol / miles | Kms between fill-ups
- Petrol / miles | kms between dates
- Cost per month (all inclusive)
- cost per miles|kms all inclusives
- Forecasting next garage stop (mechanics, technical reparation, tires replacement...) automatically based on first occurrence (ie. whet I first changes my tires at a given mileage, when will I change them again...)
I don't care what sport it is, if you get a good numbers with a variety of numbers - there's all sorts of cool dashboardy things you can do.
Here's the baseball database, for example:
Awwwwwww yeah dan l gawn get in on this one and dan l gawn shine!
An idea for the contest could be the folling case:
A Company sells subscriptions for mobile-phone users to download ringtones and mp3-songs.
The subscriptions run a week, the cost per each subscriptions is 3.99 USD / week. Without canceling the subscription, the subscription runs another week and so on.
Show (weekly/monthly) P&L and information about (aggregation of weekly / week number) subscriptions (#) and revenues, profits etc.:
• Sales #
• churn of subscriptions
• gross profits
• breakeven-points (cohort-logic!)
Ranking (tops/lows) showing best performing (P&L-Logic and cohort) of:
• subscriptions (aggregate sales date to week number –> cohort!)
product sales-channel billingpartner sales date reporting date Subscribers billable subscribers Net-revenue USD marketing costs USD (sum of CPO)
Ringtone SEM creditcard 15.04.2010 15.04.2010 5 2 2.37 11.00
Song Affiliate paypal 15.04.2010 15.04.2010 5 2 2.37 11.00
Song SEM creditcard 15.04.2010 21.04.2010 3 1 0.98 0
Ringtone Affiliate paypal 15.04.2010 21.04.2010 3 1 0.98 0
Ringtone SEM creditcard 03.07.2009 03.07.2009 5 2 2.37 11.00
Song Affiliate paypal 03.07.2009 03.07.2009 5 2 2.37 11
Ringtone SEM creditcard 03.07.2009 10.07.2009 3 1 0.98 0
Song Affiliate paypal 03.07.2009 10.07.2009 3 1 0.98 0
I have a practical example. We work in production support project and we need to send weekly status report to the upper management about the project progress. The metrics we look at are the following:
- developer vs tickets fixed
- developer vs priority of the assigned tickets (high,low,medium,urgent)
- priority vs status (e.g. high vs no of closed tickets, high vs no of open tickets,etc)
this will be a simple yet a powerful dashboard. any other possible combinations can be used as well.
I would love to see resumes as dashboards. It is a great way to show off skills when looking for a job. Build create resumes using excel.
I am responsible for reporting training spend across three areas SAP, Non-SAP and Bursaries for 6600 employees.
All data links to each employee number, one employee could complete 20 SAP training courses, attend 3 semindars and have a bursary for tertiary studies.
The Dashboard must group data as number of employees per traning course (SAP, Non-SAP and bursary) then by gender(Male or Female), race (African, Indian, Coloured and White), job grade, department and date.
I would appreciate suggestions as the data can run into 15000 rows and MS Excel 2007 and my lenovo T61 takes a lot of strain populating pivot tables to create reports for a dashbaord.
As idea dashboard on the same principle as the year before.
Idea: Dashboard on the management of delivery service .
-100 People (workers).
-Pyramids by worker.
-Pyramid of seniority.
-Turnover per employee for the year
-Location, distribution of workers by area Geographical breakdown: SOUTH / EAST / WEST / NORTH
-Scoreboard Turnover breakdown by customer
Dashboard-average processing times of a performance between the start date theoretical and actual finish date.
-Technical Manager (4 peopleor more)
-Date of actual start-
-Start Date theoretical
I think it could be a good idea and that imagination can get a lot of things
Its Just keep getting Awesome and Awesome 🙂
Chandoo .. Cricket World Cup 2011 is just on the way ..
How about Dashboard on the same.. with full schedule, teams, stadiums and finally display of final results..
I even thought of writing to you on this.. then suddenly your mail regarding suggestion poped up..
Just have a thought..
@VJ I love that idea! I am going to work on this idea anyway.
Thanks for the idea.
@Tyrone Lewis Move to an Access database for that sort of data. Create a series of queries that breakdown your data better the link in the external data. Base all your reprts on these queries. It will decrease processing speeds. Also make sure you're using named ranges etc.
As a person who has worked in higher education for a number of years, I would love to see an education related dashboard. We participate in the Voluntary System of Accountability, which includes publication of a College Portrait (see ours at http://www.collegeportraits.org/NC/WCU). I would love to see some of this information as a dashboard on our campus. I would also like to see some better ideas on how this data can be presented.
Looking forward to a higher education dashboard!
I am responsible for reporting training spend across three areas SAP,
May god have mercy on your soul.
How about data from a customer experience perspective?
Overall Customer satisfaction, likelihood to recommend - maybe even adding Net Promoter Score. Areas of opportunity, areas of strengths, customer comments with a word cloud.
i have gone through various online sites or links but no one actually provided exact info about how to make Dashboared in excel sheet step -by step.Even if i get any video which provide all info from starting to end of process then it will be appreaciable and i dnt want any dashboared download.So want to know how it prepared????
Higher Education Performance Statistics
I haven't found a nice clean data set yet but I'm still looking...
Hey... Cost controlling it is an area that has lot of potential in all business sectors and also quite needed to recover at home after Xmas extra expenses!
I am a big fun of Chandoo! Thanks
Something I RARELY see being addressed in dashboard forums & blogs, are call center (aka contact center) metrics.
Answered calls to Abandoned calls ratio by a given time period.
Performance by agent
Top ten Support Issues for a given time period
Rate of incoming inquiries.
Would also be nice to see if anyone could incorporate the use of the Erlang C formula with in the dashboard to have a tool for assessing staffing needs based on volume.
I've just realized that your cloud can be also done with that tool : Tagxedo, adding a bit fun since you can shape your text the way you like it.
With bonus season approaching which means tough time for managers, what about a Bonus, Compensation dashboard to ease decisions on who to reward ?
Including key stats on Experience, degrees, benefits by gender/roles...
My suggestion is to Dashboard Sales information which can analyze Data entered
From the Production plant Level (7 plants)
By Profit Center (17 PC's)
By Region (NAFTA,EMEA, APAC)
By Customer (Customer Names and Customer Numbers, customer can appear in multiple plants)
Showing Budget (Plan) by year and by Month
Forecast (By Month)
Actual (By Month)
So you can see by any one of these measure what the sales are for the given month or for YTD
Oh and also be able to compare the data for 3 months out and reference the previous forecast vs the current forecast data to see what changed as well as the last forecast compared to the actual
And I also forgot to mention that of the 7 plant level sheets I am dealing with 5 plant level currencies (USD, EUR, GBP, CNY, JPY)
which I also role all of them up to EUR for reporting to management
I already manage the data now, but I am at a quandry about how to:
1) display graphically for trend analysis
2) Allow for rapid changing between the views people want to see of that data!
(production wants to see data a certain way for production planning, Sales wants to see it another way for etc...)
Thats my two cents worth!