DOWNLOAD: Inventory Spreadsheet
This Excel spreadsheet contains two worksheets, one for calculating food inventory and the other for bar inventory. The food inventory in broken down into categories for Meat, Seafood, Poultry, Produce, Bakery, Grocery and Non-Alcoholic Beverages. The bar inventory worksheet has categories for Liquor, Beer, Wine and Bar Consumable products.
Each item on the worksheets has columns to indicate the "Order Unit" and price and the "Inventory (or count) Unit" and price. The "Inventory Unit price calculates automatically based on the "Order to Inventory" conversion factor that is entered.
For example, say you order tomato sauce by the case. Each case contains 6 - #10 cans of tomato sauce. When you do a physical inventory you count the number of #10 cans of tomato sauce on the shelf. On the inventory count sheet you'd have the Order Unit, Order to Inventory coversion factor, and the Inventory Unit. See below.
In the above example, the cost of a case of tomatoe sauce is $21.50. There are 6 cans of tomatoe sauce in a case, so the factor to convert the Order Unit (case) into the Inventory Unit (can) is 6. The cost of 1 can is $3.58. 12 cans were counted on the shelf for a value of $43.00.
This makes it much easier to update prices on the count sheet because the Order Unit prices should always be on your invoices. It also improves your chances of obtaining an accurate inventory value.