My dashboard - or the practical implementation of what I've learned here


Board Regular
Mar 13, 2011
Hey Kids,

I've been coming here a lot with questions, and a co-worker recommended I show my most ambitious excel project to members of this site, in case anyone here can benefit from some of the practical stuff I've learned from here, or if any member of this forum has suggestions that can improve what I have here.

Dashboard can be found here: Aged Inventory Report - 09262013.xlsm

Yes it has macros. No it probably doesn't contain viruses. I always recommend scanning documents from strange internet-folk ;).

Some Background:
My position at my company is inventory fullfillment - we sell cell phones at a retail level. It is my job to determine how much stock our stores have, and decide whether or not I need to invest in more when comparing their sales relative to their current inventory.
This report takes our sales history - at the Store/SKU level - and compares it to current inventory on hand to determine our Days of Stock (or days until we run out of stock at current sales pace). This of course influences my ordering.

The report is split in two pivot tables - left breaks the data down by REGION/DISTRICT/STORE/SKU, the right breaks down by PRODUCT CATEGORY/SKU/'s the same data, however. (note that CATEGORY is something defined by the cell phone provider we work for. Required is inventory we MUST have in stock, Recommended, Clearance, and Demo (filtered by default) are exactly what they sound like).

To further assist me in getting the full understanding of sales trends, I added a simple line graph in the upper right showing daily sales volume (Blue) and a 7 day rolling average (red). This graph changes depending on what you are selecting in the pivot - select any particular product to see it's company-wide sales. Drop that product down to the Store level in the pivot to see sales for that particular product at that particular store. Continue to drop lower to get more specific info. Click the graph to expand/collapse. Thanks to forum member JS411 specifically for helping me develop a method for creating this.

That graph is where I feel I need the most help - is this the best way to present this data to THE POWERS THAT BE, and to local retail management who need to understand why I either am ordering, or am not ordering specific product?

A quick note on the Days of Stock calculation - normally this is calculated very simply - (Inventory on Hand / Average Daily Sales This Period = Days of Stock). However sometimes a product is brand new and only was physically received partway through the report period. In this case I trend out the sales for the sake of the denominator, using a process to determine the date of the first instance of inventory for that particular SKU at that particular location, and using an accelerated sales pace to get a more accurate Days of Stock number.

This report is generated in it's entirety with 1 click via a VBA and a SQL download of our databases raw sales and inventory data. I AM NOT A PROGRAMMER AND HAVE NEVER TAKEN ANY CODING CLASS! So I'm sorry if the coding you see isn't up to par with a pro...I have no formal training, just a Google search bar and an inherent laziness that drives me to make things easier.

Any comments, suggestions, etc are much appreciated. Thanks!

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back