coc ktailbar Inventory

Shivex

New Member
Joined
Mar 31, 2016
Messages
19
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
Product nameCategoryVolumePriceSupplier
Bombay SapphireGin750ml$15Europea
Cinzano RossoVermouth750ml$13Europea
CampariAmari750ml$13Europea

Coc ktail NameVol.1Ingr.1Vol.2Ingr.2Vol.3Ingr.3
Negroni30mlBombay Sapphire30mlCinzano Rosso30mlCampari

'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:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,418
Office Version
  1. 365
Platform
  1. Windows
I have a suggestion. Let's make all the tables official Excel Table, which will allow us to do things more easily.

Also, let's make the recipe table a vertical table. Having the ingredients splayed out to the right is inconvenient for looking up data. Would this be acceptable for the ingredients table? Notice that it actually can be sorted in any way and we will still be able to get our lookups done.

[Why the heck is this Forum's profanity filter censoring this word, ****tail? I Frenchy-fied it to accommodate the situation.]

MrExcel posts18.xlsx
BCD
3CoquetelIngredientVolume
4NegroniBombay Sapphire30
5NegroniCinzano Rosso30
6NegroniCampari30
7MartiniBombay Sapphire70
8MartiniNoilly Prat5
9MartiniLillet5
10G&TBombay Sapphire60
Sheet2
 
Last edited:

Shivex

New Member
Joined
Mar 31, 2016
Messages
19
I have a suggestion. Let's make all the tables official Excel Table, which will allow us to do things more easily.

Also, let's make the recipe table a vertical table. Having the ingredients splayed out to the right is inconvenient for looking up data. Would this be acceptable for the ingredients table? Notice that it actually can be sorted in any way and we will still be able to get our lookups done.

[Why the heck is this Forum's profanity filter censoring this word, ****tail? I Frenchy-fied it to accommodate the situation.]

MrExcel posts18.xlsx
BCD
3CoquetelIngredientVolume
4NegroniBombay Sapphire30
5NegroniCinzano Rosso30
6NegroniCampari30
7MartiniBombay Sapphire70
8MartiniNoilly Prat5
9MartiniLillet5
10G&TBombay Sapphire60
Sheet2

Yeah horizontal or Vertical doesn't make a difference for the Recipe, as long as it gets the job done :P. but how do we proceed from here
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,418
Office Version
  1. 365
Platform
  1. Windows
I created a simple setup here. There are three tables. I hope you know how Excel Table nomenclature works.

When you paste from here into Excel, I think to will have to use the operation Insert | Tables and then name each. Here, one is named Ingredients, the next Sales and the last Products. The formula in the SalesVolume column of Products (starting in cell K4) calculates how much was consumed by the sales of those coquetels with those ingredient volumes.

MrExcel posts18.xlsx
BCDEFGHIJK
3CoquetelProductVolumeCoquetelSalesUnitsProductCategorySalesVolume
4NegroniBombay Sapphire30Negroni3Bombay SapphireGin1180
5NegroniCinzano Rosso30Martini7Cinzano RossoVermouth90
6NegroniCampari30G&T10CampariAmari90
7MartiniBombay Sapphire70MaiTai2Noilly PratVermouth35
8MartiniNoilly Prat5LilletAromatised wine35
9MartiniLillet5Bacardi WhiteRum80
10G&TBombay Sapphire60Bacardi DarkRum40
11MaiTaiBacardi White40CuarcaoLiquer30
12MaiTaiBacardi Dark20
13MaiTaiCuarcao15
Sheet2
Cell Formulas
RangeFormula
K4:K11K4=SUM(TRANSPOSE(Sales[SalesUnits])*((TRANSPOSE(Sales[Coquetel])=Ingredients[Coquetel])*(Ingredients[Product]=[@Product]))*Ingredients[Volume])
 

Shivex

New Member
Joined
Mar 31, 2016
Messages
19

ADVERTISEMENT

Yes I think this will do the trick!

Thank you, you've saved me many headaches !
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,418
Office Version
  1. 365
Platform
  1. Windows
You're welcome. As your project proceeds, don't struggle - come back and ask more questions. And don't drink all that booze. There - I saved you another headache!

This can probably be done in a more efficient way than formulas by using Power Query or Power Pivot. If your data-set is huge, using their Data Model that would be a better way to go. Let us know - there are thousands of terrific videos about it all on youtube that I can direct you to.
 

Shivex

New Member
Joined
Mar 31, 2016
Messages
19

ADVERTISEMENT

oh please do, i would love to learn more about power Query or Power Pivot
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,418
Office Version
  1. 365
Platform
  1. Windows
The most valuable use of youtube (aside from drunken music video sessions) is education. The best I've encountered are MrExcel and ExcelIsFun., This one is helpful too from Gharani. Subscribe to those excellent resources to get a daily dose of Excel.

There are many playlists inside ExcelIsFun that you can search through. You can even download the exemplar files. I found this one all about PQ for you. But watch others, too. Stuck at home while trembling in fear of the plague, we can be learning things, which is the best use of our time I think.
 

Shivex

New Member
Joined
Mar 31, 2016
Messages
19
Great! Yeah, this was exactly my thinking, better use the time for something usefull. I've got to know excel decently over the last few years but I know a lot of the things I do can be so much easier. I will have a look at those links, thank you very much
 

Forum statistics

Threads
1,147,455
Messages
5,741,218
Members
423,649
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top