<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=118821590194430&amp;ev=PageView&amp;noscript=1">


The Craft

The ultimate resource for alcohol beverage news, trends and reports for bars, distributors and suppliers.

step-3-communicate

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

  1. 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+.
  2. SUMIF - Allows you to add together all numbers that exist within a certain specification ie, a specified patron, item type, date, etc.

    Enter: =SUMIF(RANGE,CRITERIA,[sum_range])
  3. 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

Stock Formulas

  1. 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.

    Enter: =SUMIF(INCOMINGS[PRODUCT CODE];[@[PRODUCT CODE]];INCOMINGS[QUANTITY])
  2. OUTGOING STOCK - Similarly to the above formula, this one helps you to accurately calculate how much stock is being taken out of your inventory. This allows you to avoid over-selling your inventory.

    Enter: =SUMPRODUCT(($B5=item)*(movtype=“Outgoing”)*(quantity))

  3. STOCK LEVEL - Allows you to easily get an accurate idea of the current number for each individual product within your inventory by combining the output from the first two formulas.

    Enter: =[@[INITIAL STOCK]]+[@INCOMINGS]-[@OUTGOINGS]

Finding and Matching Formulas

  1. 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.

    Enter: =FIND(TEXT,WITHIN_TEXT,[START_NUMBER])

    OR: =SEARCH(TEXT,WITHIN_TEXT,[START_NUMBER])
  2. 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.

    Enter: =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, [RANGE_LOOKUP])
  3. 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))
  4. MATCH - Allows you to perform a function identical to the above. Uses the same formula.
  5. 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)

Priority/Computing Formulas

  1. 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.

    Enter: =RANK(SELECT CELL,RANGE_TO_RANK_AGAINST,[ORDER])
  2. AVERAGEIF - Allows you to calculate the average (inventory level, quantity sold, profit margins, etc.) from a list of products.

    Enter: =AVERAGEIF(SELECT CELL, CRITERIA,[AVERAGE_RANGE])
  3. 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)
  4. 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)
  5. 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)
  6. COUNTIF - Allows you to calculate the number of specific cells which conform to a specified standard.

    Enter: =COUNTIF (range, “criteria”)
  7. 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.

    Enter: =TRIM(“text”)

Miscellaneous Inventory Formulas

  1. VALUE - Allows you to change how something is written out within a cell, most commonly shifting a text formatted number to a numerical value.

    Enter: =VALUE(“text”)
  2. 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)
  3. 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.

    Enter: =MINIFS(RANGE1,CRITERIA1,RANGE2)

    OR: =MAXIFS(RANGE1,CRITERIA1,RANGE2)

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. 


Get a grip on inventory food costing formulas in our other insight.

Related Posts

How to (Profitably) Price Wine for Your Bar or Restaurant

There’s undoubtedly a fine art to pricing wine. It’s not an easy task in the slightest, and it’s also the key ‘make or b...

How to Set Pars for Bar Inventory

Maintaining a thriving bar or restaurant with a decent profit margin and happy customers involves far more math and data...

Must Have Bar Equipment

There’s this psychological concept that claims that the more you know about a topic, the less confident in your knowledg...

Discover Provi Marketplace

Simplify the Way You Stock Your Bar

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.

Learn More

Comments

Comment to Tell Us Your Thoughts