We actually are quite savvy with Vlookup, Subtotal, a little bit with pivot tables, and manipulation of data to get a report that is usable to make decisions with. But the time spent each time with a new data set (in our case, a new time period - monthly, quarterly, seasonally, etc.) is extremely labor intensive and involves creating several temporary working work sheets to get to the final report that we use.
I am looking for someone to design a solution for us so that we can simply dump in new data at any time and have the reports generate dynamically. Basically creating a Query, but within Excel is possible.
We have three channels of our business – our own website , Amazon.com, and a small wholesale (sell to stores) business.
The sales reports available from Amazon outputs sales data in a different format and collation from the other two channels.
The sales data is also output at a granular level - e.g. Women’s Crew Neck Tee, Color White, Size Medium. While we do need analysis at this level, we also need analysis at one level up – e.g. how did the Women’s Crew Neck Tee (all colors & sizes) sell vs the Women’s V-neck Tee (all colors & sizes)?
1) If at all possible, we would like to use Excel instead of Access. We have PC & Mac users and are not very familiar with Access. Would be open to another solution if suggested, just putting the strong Excel preference out there.
2) We would like to be able to “run” the reports dynamically. Maybe we want to see the reports for Oct 1-7 and then again for Jan 1-Dec 31. “Running” the report would simply be opening the spreadsheet and dumping in new data (new time period) to replace what was currently in it.
3) Once new data is dumped in, we want to do minimal manual manipulation of the reports to get the current results.
4) Slicing and dicing will include:
a. Doing lookups from 3 different sales data sets we dump into the worksheet as well as one static product data table.
b. Hiding or avoiding zero values, #N/A, #REF, #DIV errors.
c. Rolling up (sub totaling) granular data (sku’s) to higher level data (styles, brands, categories).
d. If possible, using weighted average calculations for some sub-total fields.
5) Ideally we would like the output reports to show only the sku’s that are contained in the sales data that we dump for the time period. If this is not possible or creates too much complexity, we can live with having zero values for the sku’s with no data for that time period.
Please see attached worksheet for an outline. Prices, costs, and sales data are all dummy and disguised for confidentiality purposes.