help building inventory control spread sheet

Hello guys i need some help here as i'm not sure what functions to even look for since i have a couple of potential obstacles.

I am trying to create a spread sheet to control my inventory at a bar, i have daily beer inventory and a weekly liqour inventory.

Within this spread sheet i want to be able to do the following info:

1 individual unit pricing, price per case vs price per unit. maybe a drop down function?

2 comparison in sales vs count. when counting inventory physically you divide an opened bottle in tenths, but a traditional 750 ml bottle has 16 shots. when inputting my sales i would like it to compare with my physical inventory and warn me in some way about any inconsistencies. if at all possible using certain margin for error since physical inventory is taken by looking at bottle and making a reasonable guess as to the amount inside it.

3 cocktail based inventory control. cocktails use multiple liqours, not always full shots. is there a way where if i input in my sales for example a *** on the beach the expected inventory is reduced by 1 shot of vodka and half a shot of peach schnapps?

4 a separate sheet within the book that compares sales vs inventory, giving me an expected inventory column based on last physical count of inventory, this expected inventory column i would like to compare itself with new physical inventory column and warn me if there is any discrepancy up to a certain margin for error.


thank you all for the help in creating this it woudl help me a lot

i found one thing that does the whole cocktail recipe thing, and calculates cost for me. is there a way i can implement this save each individual cocktail name and then reduce it from my previous period inventory count?

http://www.spreadsheet123.com/downlo...ost-calculator

i have a starting inventory to start with it just doesn't have the functions i would like to add to it, for some reason i can't attach the file here so im posting a we transfer link to the excel sheet
http://we.tl/CA9gmONGMK