• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inventory Dashboard - Need Suggestions Review...

Faseeh

Faseeh

Excel Ninja
Dear All,


Few Days back i was assigned a task to prepared a report that was supposed to show the status of "Inventory" over period of last few months. The Inventory or spare parts are checked to find any unusual trend or to see if there exists one or not and allow management to scroll through the data.


I am going to present this file to Technical Director of the company so before i present it to him, i though why not get it reviewed by our fellows at this forum? Here is the file:


http://dl.dropbox.com/u/60644346/Dashboard.xlsx


What i need:


1. Any thing you liked very much(or didn't liked altogether), what and why please tell.

2. I tried to create a sought of heat map by plotting Cost Concentration Plot. What do you think about that?

3. I want you to rate it dashboard from 0 to 9, zero being worst, 9 meaning best?

4. Any other suggestions to improve it.


I will be submitting it by 5th of June, 12.


With Kind Regards,

Faseeh
 
Some initial comments.


Overall, I think it has a very pleasing effect, your use of colours is nicely muted, the labels do not obstruct.


You have mis-spelt contributors in the label for Top 5 Contributors (btw, I would use a numeric 5 rather than Five here, more immediately impacting).


I would use thousands, millions separators in your numbers, again to aid readability. Maybe ditch the decimal place as well (I accept that you might have business reasons for having that).


The heat map does not work for me, what are you trying to say with it? Presumably those colours apply to some threshold, but I cannot see anywhere telling me what that threshold is, or what it means. This might also be where you could use colour, red, yellow, green, but pastel shades of.


I was surprised to not see the Top 5 table change when I changed an item in Select Part dropdown. If that dropdown only applies to the Yearly Plot, maybe they should be grouped, within a frame.


Personally, I would drop the gridlines on the charts - show them how hovering gives the value.


I would rename all of the sheets, looks more professional.


I would ditch the row and column headers on that sheet, make it look less-Excel.


It is difficult to comment on the detail, I do not know your business or your corporate drivers, but I think if you can accompany it with a good presentation it should go down well.
 
Hi Faseeh ,


First , congratulations for two things :


1. Your product which highlights the effort you have put into it ; it almost makes me feel bad to say anything against it !


2. Your attitude in laying yourself open to public review ; I admire it.


Please take my comments in the sense that it is easier to be a critic than a creator !


1. I think you have not made full use of the screen space available ; I find that a lot of the right side of the screen is blank , while the rest of the dashboard has to be viewed by scrolling down ; this according to me is a big no-no. A dashboard should fit into one screen ; adjust your font sizes if need be and remove all redundancies. I think the top portion devoted to the Top Five Contributors is duplicated within the Pareto Analysis for Parts. I think the Top Five Contributors section can be eliminated , and the Pareto Analysis section can be moved into that space.


2. The section in the top left hand side is not labelled ; though I can deduce that it is expenditure on various items , when all other sections have been labelled , this sticks out for not being labelled.


3. The departmental expenditure , item-wise , is taking up space ; I feel that the Pareto Analysis for Departments could have a drill-down facility , so that clicking on any department will open up the expenditure for that department , item-wise.


4. In the chart for Yearly Plot for ... , I have no idea what CPS is , so I cannot appreciate the significance of checking and unchecking the CPS check-box , though I can see that the secondary axis changes when this is done. Nor can I appreciate the red-coloured triangles appearing and disappearing when this check-box is checked / unchecked.


5. There seems to be some problem in the Concentration Map ; I find that the value against Traveller matches with its value in other places ; but for Apron , there's no value in the CM , whereas it's the top item in the expenses list.


6. A minor point - whereas elsewhere you have Items , against the drop-down , it is mentioned as Select Part.


Narayan
 
Hi xld & Narayan,


Thank you very much for taking time to review it i knew it will improve when fellows will see it.


@xld,


Thank you, please see the last uploaded file again, i...


1. Named sheets properly.

2. Removed decimals no need for them, used commas.

3. Corrected mis-spells.

4. Top 05 items: They change with month, try selecting month they will change!

6. Heat Map?? Let me answer it to Narayan as well :)


@ Narray,


1. Yes Sir, the dashboard must be adjusted for space and i can see scrolling down is very negative point here. My Tech. Director has got 21" LCD so should i adjust it to match that screen??


2. Top 05 contributors: Yes there is redundiness!!!! Should i really delete it?? :?


I think its time to answer what is heat map and Pareto was meant for:


@ Narayan & xld,


Actually i was trying to make a chart that shows dept. wise items that make up 20% of expense for every department! This resulted in two diff. Pareto you can see. My intention in making heat map was to show the effect of same chart i was unable to plot. I tried it once but that was some how hectic or beyond my skill level so i dropped the idea and resorted to heat-map.


This map is meant to show how expense is concentrated to various items in departments.


I will be glad if you can suggest me an alternate layout (adj. for 21" LCD) and a chart that can present the two pareto's together.


Edit: CPS = Cost per Spindle, its a industry standard to present operating cost of a spindle as CPS. and I am working in a Textile Spinning Unit.


Thank you,

Faseeh
 
Faseeh -


First off, I always love seeing the ideas of others - especially when they're willing to share them. I think the overall effect of your work is great, but I do agree with the suggestions above, specifically:

- the concentration report ('heat map' as you referred to it) is difficult to understand. I gather that the darker the blue, the more expensive it is, but not knowing the business may complicate my understanding. For example, does this tell me that the traveller ring is the most expensive inventory you have on hand? If so, I think it's an interesting way of showing the data for sure - I haven't looked at it in this format.

- the 'select part' dropdown should be coupled with the chart as the first reply points out - I was waiting for changes in the tables surrounding the drop-down, but didn't see any.

- I really like the offset/match formula combination you're using. Seems like a simple way of making a VLOOKUP more flexible, though I haven't used the formula yet myself. I'll give it a shot in the next dashboard.

- on your pareto charts, you might want to 'fix' the y axis to stop at 100%? Right now, it goes to 120% and looks a little strange.

- when I change the drop-down, the 'yearly plot' chart title changes formatting to show black text. Perhaps you can lock it to white?


Otherwise, I even like the colors you've used (orange/blue offset each other very well). Best of luck on the updates and the presentation.
 
Hi 2L8IWON,


Thank for your review! Heat map is meant to show the most "Cost Consuming" area for a department. For a list of hand-picked items, it shows what portion of budget is eaten up by that part for a department. Lets say as you said Ring Travelers are the costliest part for a certain department then we should work out how to reduce their consumption as they take the biggest chunk of the department's budget. I hope i am able to explain myself.


Drop downs: I would have coupled the Select Part with chart but is you see this drop down is used in Departmental Expense as well as Pareto chart so i didn't attached it any single thing.


@Hui, Luke M, Old Chippy, Fred & SirJB7,


I am waiting for your comments as well.


Regards,
 
Hi, Faseeh!

I arrived when xld and NARAYANK991 have yet posted here, and not being a dashboard specialist (mine is programming basically) I found I actually noticed half (or less) of the things that they've found before. If I give another look at 1:36AM I think you'd better omit whatsoever I write, so let's see what comes to my mind tomorrow.

Despite of this, congratulations, buddy! A very good work, well done. Of course much better that I could have intended.

Regards!
 
Sorry if my post is too late for your deadline on 5th June. But here it goes.


First up, congratulations on such effort. your dashboards looks classy and allows users to do powerful analysis without wasting too much time. Here are few suggestions I can think of,


The headers ("Expenditure for various items", "Top 5"... etc.) look like buttons. So I tried to click them to realize that they are just headers. May be you wan to format them more like a header than button.


In the Expenditure for various items box, you have lots of numbers (while the focus would be on the middle column - selected month). Why not add some sort of indication thru conditional formatting so that important numbers stand out. For example, you can show a red icon next to items whose expenditure went up by more than 20% compared to previous month.


Congratulations once again, and I hope your technical director likes this.
 
Sorry I was late. I won't repeat others' comments. They are all true. Great job!


Question:

Cells J11, N11 and Q11. do you really want to show the month and not the % difference? It is not quite consistent in apprearance.


For that reason, i am not sure if your formulae from J7:J11, N7:N11 and Q7:Q11 are correct though. you may want to recheck them. It was very easy to spot this error because:

1. it's near the top of the dashboard

2. rank #4 stays at 0% regardless of change in month.

3. rank #5 shows months not % as you may have intened for.


This is my 2 cents. need to get back to my own work. hope this help. :)
 
@ Chandoo G,


Hi,


Thank you so much for taking out time for this. I have still got some 5~6 hrs and i will try to change it they way you have pointed for sure!


@ Fred,


:)Thank you, %age diff. i will have to discuss it with my immediate boss, as now i am running short of time so two things to do in my list, first is that that chandoo pointed and second is that of yours lets see what i can do.


For errors i will certainly check for them!!


I will post the final file for sure to see how it ended up!


Thank you!! :)
 
Hi All,


I ended up with this file and thank God i earned a great applause from my Tech. Director as well as other Snr. Managers, Thanks to every one who contributed to it: :D


http://dl.dropbox.com/u/60644346/Inventory%20Dashboard%20Final.xlsx


Regards,

Faseeh
 
fantastic! Congratulations Faseeh.


Can I feature this dashboard as an example on the blog? If you want to send me another version with dummy data, please do so.
 
You must log in or register to reply here.
Back
Top