Item Sales with additional category
This is nervewracking- and I apologize in advance for the lengthy post!
I'm a chef at a concert venue, with a background in computers, who has very basic actual Excel skills. I've pushed myself time and time again to learn how to turn the data from our food sales into something I can utilize for food prep and ordering- and over the past few years I've built something that works well for what we need.
The workbook I use now has a 'Master' sheet with a column for each show, and a row for each different item we sell. There's a sheet for each show which I populate manually from a Square report that I quickly clean, organize, and copy/paste- the only other thing I do on these sheets is input the total number of attendance and sum the total revenue.
The show columns on the master sheet use a VLOOKUP to pull the item sales data from each show sheet- this combined data is what gives me the 'Item Sales Per 1k Guests' which is the most helpful piece of information and the real purpose for this post.
Our GM is leaving- and I think I want to push for the position. He is only around for 12 more days and then potentially supporting in a consulting role for another week or two after that. Somebody has to very quickly gain an understanding of the beverage sales and ordering volume.
I've tried just turning my workbook into a beverage workbook but quickly ran into a problem- we sell the same beers in different sizes ('Item Variations'). My food sales formula won't work- so I decided to turn to the internet to investigate a different formula/option and hopefully learn even more about Excel which is honestly something I love.
Everything pointed to Power Query and Pivot Tables, so I learned as much as I could and have now figured out how to pull a full food and beverage item sales report from square, drop it into a folder, refresh data in the new workbook and see the sales update in Excel. Neat.
The problem is I don't know how to turn this automatic data import into a clean 'sales per 1k guests' number on the dashboard sheet with the pivot table and slicer.
So the question is: What is the cleanest way to populate the dashboard sheet with item sales per 1k guests for each of our items sold?
AI has me going down multiple different methods of even getting the square reports into the workbook- and can't stop making suggestions that aren't relevant to my actual end goal.
Should I just keep on with the Power Query imports and manually adding attendance from a table on a separate sheet? If so, what do I need to use in the 'Values' section of the Pivot Table. Ideally, this workbook should scale with each shows report import and provide a very clean and simple number that lets me wrap my head around how much of each item to plan on selling for a determined number of incoming guests.
Additionally, this is something that should be scalable because I will turn into a few other tools: one that takes a quick on-hand count sheet and compares it against what we've ordered in total so far (with the guest attendance we've served so far) to determine how much to order for any desired amount of people (usually 20,000 people or so). I also want to turn it into a prep calculator based on projected incoming guests, using the 'Item Sales Per 1k Guests' and the recipe requirements for each of those items. That will come down the road, I just don't want to cut myself off from building these.
If this is too broad, I can provide whatever I need to from what I have built so far- but I'm hoping this community can help point me in the right direction for building this foundation.