The Excel spreadsheet shown above is available for download by clicking here.
Abstract: Restaurants and other hospitality facilities will have part of their F&B budget rotating through container deposits for bottles and cans of pop (aka “soda”) and alcoholic beverages, as well as kegs of those products in states which require those deposits. Some states require twice the deposit amounts per container than some other states, and larger venues and amusement parks may have considerable funds constantly applied to this requirement. A monthly inventory spreadsheet technique assists in keeping track of where these funds are applied.
When I was first asked by an F&B Director to start doing restaurant inventories, it became readily apparent my immediate predecessor in that role hadn’t been as organized as they’d thought. Case sizes weren’t accurate, they had only counted whole cases and ignored any open partials, and apparently there were about 12,000 empty bottles somewhere. Michigan’s return rate on empty bottles is 10 cents, and the inventory spreadsheet I’d been handed had a figure of roughly $1,200.00 in the “Empty bottles (returns)” cell.
If you think about that for a moment, you’ll realize even that’s wrong, as full bottles have the same return rate as empties. Part of any F&B budget in any state with returnables is tied up in those bottles and cans, and even though it’s generally a stable value, it can surge during holidays and national events, such as St. Patrick’s Day and Super Bowl Sunday. States with returnable programs have them applied to beer, pop (aka “soda”), and other products in both cans and bottles. It’s part of the F&B inventory that has to be monitored to prevent shrinkage.
It turned out that, at the time I started my count, there were just over 1,000 empty bottles in storage. Looking at the spreadsheet more closely, I found the formula in the “Amount” cell was being multiplied by $1.00 instead of 10 cents. Correcting this formula brought the value of “Empty bottles (returns)” down to just over $100.00 for my count. Of course, upper management immediately demanded a meeting since my final monthly inventory was so far different from the previous. But all was well, and I ended up being an inventory specialist in various venues afterward.
As mentioned previously, both full and empty cans and bottles have to be accounted for in a returnables state. But brewers also charge deposits for kegs as well, and in Michigan this is about $30.00 per keg. Some bars and pubs I know of have upwards of 20 taps spread out over multiple towers, with the kegs in a beer-only walk-in cooler. Similarly, places like casinos will have a massive keg walk-in in the utility basement level, in their case holding full kegs stacked in the center and the tapped kegs on racks around the perimeter. There may be a couple hundred kegs in one of these walk-ins at a time, not to mention the empty kegs which are stored in a returnables room alongside empty bottles and cans.
It soon becomes apparent that, in such a venue, the $1,200.00 returnables value I’d first seen may seem low, particularly during holidays and events.
When counting kegs, you have to make assumptions about how full a tapped keg is. I’ll generally grab the handle hole at the top of a keg and give it a hard shake. Knowing how a full one feels when doing so, I’ll use values of 0.3, 0.5, and 0.8 for partial kegs. Anything supposedly more accurate than that is either because you’re physically weighing each keg (which is possible but is too OCD for me), or is a result of what’s known as the “CSI Effect“. You’re not that good at feeling what’s in a keg, so don’t do it.
Adding kegs to the returnables calculation brings in a spreadsheet function most people who use Excel aren’t generally used to:
ROUNDUP(NUMBER,NUM_DIGITS)
Some Excel users might be used to using ROUND to round currency values to two digits. ROUNDUP always rounds the value … well … Up. Here’s why we’ll use this function: If you have a keg that you’ve counted as a partial, and have 2.3 as a value for two full kegs and a partial, that’s three kegs, for a deposit of $90.00. ROUNDUP will get us there.
There’s a problem with this though. If you have 2.3 kegs for that beer, and 3.5 kegs for another beer, and then add them together, that’s 5.8 kegs of beer. Using ROUNDUP gets us to six kegs for the deposit, or $180.00.
But that’s wrong. 2.3 kegs is three keg deposits, and 3.5 kegs is four keg deposits. That’s seven keg deposits, not six, or $210.00.
What we have to do is use the ROUNDUP function on each beer’s individual inventory count of the combined full and partial kegs.
If you look at the image at the top of this page you’ll see a column named “Kegs (calc)”. The full and partial keg count for Bud Light is shown as 2.8 in cell F13. Cell G13 in the “Kegs (calc)” column contains the following formula:
=ROUNDUP(f13,0)
The 0 (zero) in this formula tells it to round to the nearest integer, so the function rounds up the value of 2.8 to 3. Further down the spreadsheet you’ll see “Empty kegs”, which is a physical count of used kegs that are in storage. Immediately below this is “Total Full and Partial Kegs (calculated)”. This is a formula which adds up the ROUNDUP calculations for each beer:
=SUM(G13:G18)
Below this, we add the counted empty kegs to the summed full and partial kegs:
=SUM(F20:F21)
To the far right, we’ll multiply this figure by the value of the keg deposit of $30.00. This deposit value of $30.00 is kept in its own cell in case it changes, so:
=(F22*H22)
Bottles are also handled throughout the spreadsheet as well, but are much more straightforward. Similar functionality could also be used for cans if necessary. The value of total beer on-hand plus deposits is the end result.
It’s bad enough to have to climb through all the necessary spaces to get these counts. Unfortunately, it’s necessary. This method ends up being a rather welcome embedded set of formulas for what can be a particularly frustrating count process.