Calculate stock level of alcohol bottles per shots

[bootdat]

Hi
I'd like to know if there is anyway to get a bottle of alcohol stock level when the content is taken by shots ?

For Instance, 24 shots makes up a bottle of dry gin
but in the bar, some people order for the full bottle and some order for shots

BiggiesBites.xlsm
	D	E
14	Selling
15	Item	Out
16	Dry Gin (Bottle)	2
17	Dry Gin (Shots)	6
18	Dry Gin (Shots)	2
19	Dry Gin (Bottle)	1
20	Soda	5
Sheet1

Now, in the inventory
both dry gin (Dry gin (bottle and Dry Gin(Shots) comes from one product which is labeled as Dry Gin (Bottle)

BiggiesBites.xlsm
	H	I	J	K
14	Inventory
15	item	in	out	stock level
16	Dry Gin (Bottle)	10		10
17	Soda	5		5
Sheet1

Cell Formulas
Range	Formula
K16:K17	K16	=[@in]-[@out]

So how can i make the Out in inventory in a way that, when the dry gin (shots) is = 24, it calculates it as one and takes it out from Dry Gin (Bottle)
because 24 shots empties a bottle of dry gin

I'd like to know if there is a sort of formula i can use for that calculation
thank you

 

[bootdat]

Hi
I'd like to know if there is anyway to get a bottle of alcohol stock level when the content is taken by shots ?

For Instance, 24 shots makes up a bottle of dry gin
but in the bar, some people order for the full bottle and some order for shots

BiggiesBites.xlsm
	D	E
14	Selling
15	Item	Out
16	Dry Gin (Bottle)	2
17	Dry Gin (Shots)	6
18	Dry Gin (Shots)	2
19	Dry Gin (Bottle)	1
20	Soda	5
Sheet1

Now, in the inventory
both dry gin (Dry gin (bottle and Dry Gin(Shots) comes from one product which is labeled as Dry Gin (Bottle)

BiggiesBites.xlsm
	H	I	J	K
14	Inventory
15	item	in	out	stock level
16	Dry Gin (Bottle)	10		10
17	Soda	5		5
Sheet1

Cell Formulas
Range	Formula
K16:K17	K16	=[@in]-[@out]

So how can i make the Out in inventory in a way that, when the dry gin (shots) is = 24, it calculates it as one and takes it out from Dry Gin (Bottle)
because 24 shots empties a bottle of dry gin

I'd like to know if there is a sort of formula i can use for that calculation
thank you

For better understanding;

BiggiesBites.xlsm
	D	E
14	Selling
15	Item	Out
16	Dry Gin (Bottle)	2
17	Dry Gin (Shots)	6
18	Dry Gin (Shots)	2
19	Dry Gin (Bottle)	1
20	Soda	5
21	Dry Gin (Shots)	10
22	Dry Gin (Shots)	5
23	Dry Gin (Shots)	2
24	Dry Gin (Shots)	5
25	Dry Gin (Shots)	1
26	Dry Gin (Shots)	1
27	Dry Gin (Bottle)	1
28	Dry Gin (Bottle)	1
Sheet1

The above table consists of number of shots ordered and some for whole bottle

Now below is how i can do the maths

BiggiesBites.xlsm
	G	H
23	Total Shots sold	32
24	Total bottle sold	5
25
26	Total shots sold per bottle	1.3
27	Out for Dry Gin (Bottle)	6.3
28	Stock Level	3.7
Sheet1

Cell Formulas
Range	Formula
H23	H23	=32
H26	H26	=32/24
H27	H27	=H24+H26
H28	H28	=I16-H27

i want my inventory to look somewhat like this :

BiggiesBites.xlsm
	H	I	J	K
14	Inventory
15	item	in	out	stock level
16	Dry Gin (Bottle)	10	6.3	3.7
17	Soda	5		5
Sheet1

Cell Formulas
Range	Formula
K16:K17	K16	=[@in]-[@out]

 

[Seba Robles]

I'm not entirely clear on what you're wanting to achieve but let me know if this helps in any way;

=SUMIFS($C:$C,$A:$A,F$2,$B:$B,$E3)

You can drag down or drag right the formula on cell F3

 

[jtakw]

Hi,

Based on your posted sample, try this:
Adjust the Result cells decimal as you like.

Book3.xlsx
	D	E	F	G	H	I	J	K
14	Selling				Inventory
15	Item	Out			item	in	out	stock level
16	Dry Gin (Bottle)	2			Dry Gin (Bottle)	10	6.33	3.67
17	Dry Gin (Shots)	6			Soda	5	5	0
18	Dry Gin (Shots)	2
19	Dry Gin (Bottle)	1
20	Soda	5
21	Dry Gin (Shots)	10
22	Dry Gin (Shots)	5
23	Dry Gin (Shots)	2
24	Dry Gin (Shots)	5
25	Dry Gin (Shots)	1
26	Dry Gin (Shots)	1
27	Dry Gin (Bottle)	1
28	Dry Gin (Bottle)	1
29
30
Sheet906

Cell Formulas
Range	Formula
J16	J16	=SUMIF(D16:D30,"*(Bottle)",E16:E30)+SUMIF(D16:D30,"*(Shots)",E16:E30)/24
K16:K17	K16	=I16-J16
J17	J17	=SUMIF(D16:D30,"Soda",E16:E30)

 

[bootdat]

I'm not entirely clear on what you're wanting to achieve but let me know if this helps in any way;

View attachment 36272

=SUMIFS($C:$C,$A:$A,F$2,$B:$B,$E3)

You can drag down or drag right the formula on cell F3

Hi,

thanks for your response
unfortunately that wouldn't do it

Hi,

Based on your posted sample, try this:
Adjust the Result cells decimal as you like.

Book3.xlsx
	D	E	F	G	H	I	J	K
14	Selling				Inventory
15	Item	Out			item	in	out	stock level
16	Dry Gin (Bottle)	2			Dry Gin (Bottle)	10	6.33	3.67
17	Dry Gin (Shots)	6			Soda	5	5	0
18	Dry Gin (Shots)	2
19	Dry Gin (Bottle)	1
20	Soda	5
21	Dry Gin (Shots)	10
22	Dry Gin (Shots)	5
23	Dry Gin (Shots)	2
24	Dry Gin (Shots)	5
25	Dry Gin (Shots)	1
26	Dry Gin (Shots)	1
27	Dry Gin (Bottle)	1
28	Dry Gin (Bottle)	1
29
30
Sheet906

Cell Formulas
Range	Formula
J16	J16	=SUMIF(D16:D30,"*(Bottle)",E16:E30)+SUMIF(D16:D30,"*(Shots)",E16:E30)/24
K16:K17	K16	=I16-J16
J17	J17	=SUMIF(D16:D30,"Soda",E16:E30)

Hi, that works
Thanks

 

[jtakw]

You're welcome, thanks for the feedback.

Dry Gin Martini on the House??

 

[bootdat]

You're welcome, thanks for the feedback.

Dry Gin Martini on the House??

Lol

 

[ryan7]

Your till receipts should tell you day to day what you have sold, whether it's a shot or by the bottle, etc..
If you're doing an inventory and stock take you're going about it the wrong way. You need to compare your takings with your inventory and build your costs and retail prices into a workbook

 

[johnnyL]

@ryan7 have you ever watched the show called "Bar Rescue"?

"Starting inventory" - "Sold" often will not equate to "Inventory remaining"

 

[ryan7]

No I have not seen it. You have to take into consideration of your purchases.
That's why bars should perform regular stock takes, to identify deficit or surplus in their inventory.