We know… You didn’t get into hospitality to learn Excel and Google Sheets. You probably didn’t get into it as an excuse to do inventory, either, we’re guessing. But that’s just part of what being a go-getting restaurateur is. Some of it is glamorous tasting events, menu-planning and bossing your kitchen staff around. Some of it (most of it) is the unsexy but very, very important work of administration and maintenance. And here you are: doing and having it all.
But don’t think that you need to be an expert in spreadsheets in order to use them effectively for your restaurant or bar. There are plenty of shortcuts that will help you keep track of your inventory while minimizing any human error. So to make your life a little easier, we’ve put together a list of some key inventory excel formulas that will help you save on spreadsheet time so you can get back to sampling local whiskeys and reinventing the Wagon Wheel.
Spreadsheet formulas for bar and restaurant inventory management
Here are a few critical Excel formulas that will help you track inventory with ease.
Quick Sums Formulas
SUM - The most basic inventory formula, allows you to add together all numbers within a specified row or column. To execute, select the last value in said row or column and hit Alt+.
SUMIF - Allows you to add together all numbers that exist within a certain specification ie, a specified patron, item type, date, etc.
SUMPRODUCT - Allows you to calculate averages across a given range of products and actions, such as shipped products, returned products, sales for specific products, etc.
Enter: =SUMPRODUCT(RANGE1,RANGE2)/SELECT CELL
INCOMING STOCK - Allows you to accurately calculate how much new stock is being introduced to your inventory. This allows you to maintain an accurate count of specific items within your physical inventory.
FIND/LOOKUP - Allows you to browse your entire inventory for specific items of search terms. Use FIND to search for exact-word items, or LOOKUP to execute a more general search for cells containing a similar term.
VLOOKUP - Allows you to not only find specific items but also link those items to other related values, such as the individual products within a specific order.
INDEX - Allows you to perform a similar function to the above but can do so nonlinearly (ie from different columns, etc. rather than exclusively left to right).
Enter: =INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
MATCH - Allows you to perform a function identical to the above. Uses the same formula.
LEFT/RIGHT - Allows you to pull a “substring” from a string of data within a given cell, beginning from either the left or the right of that cell. For example in a cell containing “Fosters - 12 Cases” you could pull either the product name or the case number.
Enter: =LEFT(SELECT CELL,NUMBER)
OR: =RIGHT(SELECT CELL,NUMBER)
RANK - Allows you to create an ordered list of products based on a certain specification. As a common inventory excel spreadsheet example, this means inventory levels, quantities sold, or profit margins.
CONCATENATE - Allows you to combine data from different cells into one cell. Sometimes used to create SKUs, product description labels, etc.
Enter: =CONCATENATE(SELECT CELLS YOU WANT TO COMBINE)
LEN - Allows you to quickly count the number of text characters within a given cell. This is helpful for specific product numbers or codes based on length.
Enter: =LEN(SELECT CELL)
COUNTA - Allows you to discover whether any data is missing from your inventory by finding and indicating any empty cells within a spreadsheet.
Enter: =COUNTA(SELECT CELL)
COUNTIF - Allows you to calculate the number of specific cells which conform to a specified standard.
Enter: =COUNTIF (range, “criteria”)
TRIM - Allows you to remove data from a cell such as extraneous grammar or spacing which may make a specific cell invisible in broader searches.
Miscellaneous Inventory Formulas
VALUE - Allows you to change how something is written out within a cell, most commonly shifting a text formatted number to a numerical value.
DAYS - Allows you to calculate the length of time passing (in days) between certain inventory events such as orders for specific products or more general inventory income or turnover.
Enter: =DAYS(SELECT CELL, SELECT CELL)
MINIF/MAXIF - Allows you to determine the lowest or highest value within a specified criteria, such as the lowest purchase amount for a given order or the highest number of units in an outgoing shipment within the last year, etc.
How Provi can help with inventory management
Provi makes beverage ordering easier than ever, with an all-in-one platform that keeps track of every incoming and outgoing shipment and automatically modifies your inventory with each Bloody Mary poured.
Provi lets you ditch the spreadsheet (we should have opened the post with this, huh?) and perform inventory at-a-glance and know what the top sellers are, what’s running low, and even when to reorder so you can have everything you need behind the bar, just when you need it.
See how Provi helps thousands of bars, restaurants and retailers save 200 hours a year with a single app for browsing product catalogs, ordering, and messaging your reps.
Comment to Tell Us Your Thoughts
Whether you’re a bar, retailer, distributor, or brand— growing your company is hard work. Success in the alcohol industry requires a lot of time, great relationships, and staying informed. Get access to all three and prosper using Provi.