Hello,
I'm doing the inventory for a coc ktail bar, and we work with many recipes for drinks, of course, and I would like to create a system that gives me a better view of how much we sold, how much we have and what I need to order making life a bit easier.
The main Idea for inventory control is to do a weekly count to get a Starting Value, then add everything that you receive that week, and at the end of the week count again to have your ending value.
Start + received - End = Usage (amount used that week). this data I want to compare it to our sales of the POS system.
This is where the tricky part (for me) comes in.
I've created 3 sheets, Summary - Sales - Received - Data
in the Data sheet i've made list of all the products in the bar, and another table of the ****tail recipes
'Received' just holds the product name, category and amount
Sales holds the amount of times the product is sold but here also comes the ****tails in to play.
So lets say we sell 3 Negroni this week. I want that the amount of spirit used in the recipe multiplied by the number of times sold and added up in the 'total' column in Summary sheet of the used spirit (90ml bombay ,90ml cinzano and 90ml campari).
Now doing this for 1 drink is not the problem, the problem is that I have multiple recipes using the same products.
Martini with Gin for example (70ml Bombay - 10ml Noilly Prat)
How do I gather all the correct information and put it in their designated cell...
Hope my explanation is clear enough
I'm doing the inventory for a coc ktail bar, and we work with many recipes for drinks, of course, and I would like to create a system that gives me a better view of how much we sold, how much we have and what I need to order making life a bit easier.
The main Idea for inventory control is to do a weekly count to get a Starting Value, then add everything that you receive that week, and at the end of the week count again to have your ending value.
Start + received - End = Usage (amount used that week). this data I want to compare it to our sales of the POS system.
This is where the tricky part (for me) comes in.
I've created 3 sheets, Summary - Sales - Received - Data
in the Data sheet i've made list of all the products in the bar, and another table of the ****tail recipes
| Product name | Category | Volume | Price | Supplier |
| Bombay Sapphire | Gin | 750ml | $15 | Europea |
| Cinzano Rosso | Vermouth | 750ml | $13 | Europea |
| Campari | Amari | 750ml | $13 | Europea |
| Coc ktail Name | Vol.1 | Ingr.1 | Vol.2 | Ingr.2 | Vol.3 | Ingr.3 |
| Negroni | 30ml | Bombay Sapphire | 30ml | Cinzano Rosso | 30ml | Campari |
'Received' just holds the product name, category and amount
Sales holds the amount of times the product is sold but here also comes the ****tails in to play.
So lets say we sell 3 Negroni this week. I want that the amount of spirit used in the recipe multiplied by the number of times sold and added up in the 'total' column in Summary sheet of the used spirit (90ml bombay ,90ml cinzano and 90ml campari).
Now doing this for 1 drink is not the problem, the problem is that I have multiple recipes using the same products.
Martini with Gin for example (70ml Bombay - 10ml Noilly Prat)
How do I gather all the correct information and put it in their designated cell...
Hope my explanation is clear enough
Last edited:
