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

SeanDamnit

Board Regular
Joined
Mar 13, 2011
Messages
151
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: https://www.dropbox.com/s/x41pvp7kr6jkp1g/Equipment 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/LOCATION...it'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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top