SeanDamnit
New Member
Hey Kids,
A co-worker of mine suggested I share my dashboard with this group - as this may be useful to some members, and I'm hoping to get feedback on the effectiveness of the way I present the data.
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. This is heavily influenced by dynamic reporting guides I found on this site.
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 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.
A co-worker of mine suggested I share my dashboard with this group - as this may be useful to some members, and I'm hoping to get feedback on the effectiveness of the way I present the data.
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. This is heavily influenced by dynamic reporting guides I found on this site.
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 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.
