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!
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!
