@Xerces1066
Well, here's a start at showing how Excel might handle that with a single table to handle sales and returns...and then using Pivot Table to report on the volume of related transactions by hour per day. I've not tried to deal with stock or inventory levels....because that requires more definition. Even this is a situation where I've kind of "guessed" at what might be relevant. It's meant primarily to demonstrate a way to think about the task.
So there are three new spreadsheets in this workbook:
- A business table sheet that provides some basic information used for data validation on the transaction sheet. Each of those tables can be extended to add other things, or other pieces of info per item, etc.
- A transaction sheet to track sales, return to stock, bringbacks, by hour, by item. This can also be enlarged if you want to track by store, etc.
- A Pivot Table that summarizes transactions. This can be fine-tuned and extended as well.
What I want you to see is that instead of beginning with what is basically an "output" sheet into which you enter hourly data, already (in effect) summarizing raw data but doing so "manually," Excel really is good doing that kind of summary for you. Just capture each transaction as it happens.....let Excel do the "heavy lifting."
(As an aside: It probably would have been more discreet to leave the name "Costco" off your workbook--as a Costco member, I find it kind of surprising that Costco is asking an employee who is not in IT to create a spreadsheet to track sales and inventory. Have you checked to see whether something doesn't already exist along these lines? Hard to believe it doesn't, given Costco's fame at tracking all of this kind of stuff.)
