Restaurant Inventory Spreadsheet to Get Started with Inventory Management

A restaurant inventory spreadsheet is no match for restaurant inventory software to track your inventory, but if you’re a growing restaurant business, it’s a good place to start to set the groundwork for a digital solution.

Restaurant inventory management is the process of monitoring the food and beverage ingredients in your restaurant. Monitoring your inventory documents what food and beverage product is coming into your restaurant, what is leaving your restaurant as product sold, and what remains on your shelves and refrigerator. Knowing these essential metrics enables you to make more informed supply orders, and tight inventory control helps you reduce food waste, adding to your bottom line.

While tracking inventory manually can be fairly straightforward, there are a few key terms that can help you and your staff better understand the process. For each metric, you can either calculate it by quantity or dollar value. However, you should choose one unit of measurement and stick with it for consistent reporting.

Sitting Inventory

Your sitting inventory is the amount of unused product that is sitting in your shelves, refrigerator or walk-in.

Depletion

Depletion is the amount of food and beverages your restaurant uses over a set period of time.

Usage

Your usage shows the general rate of use for your inventory, calculated with the following formula:

Sitting Inventory ÷ Average Depletion (for a specific period of time) = Usage

Variance

Variance represents the unaccounted-for difference in the food you actually used versus how much you should have used. You can calculate the variance by inventory quantity or dollar value. The cost variance in actual versus theoretical inventory usage is calculated by taking your product cost – the cost of product it takes to make the dishes sold according to recipe — and subtracting the cost of product your staff actually used to create the dishes sold. For example, if your inventory shows that you used $600 worth of ingredients to make your signature lobster pasta entrée during a certain time period, but you should have only used $540 worth of ingredients to make that menu item in the same time period, your cost variance is $60. 

Restaurant inventory spreadsheet basics

Inventory counts track the amount of inventory your restaurant has at a given time. In the absence of restaurant inventory management software, a restaurant inventory spreadsheet serves to help you manage your restaurant inventory manually.

Creating Your Restaurant Inventory List

Using the downloadable spreadsheet above, here are the steps for creating a restaurant inventory spreadsheet.

1. Determine what food categories you’ll need on your spreadsheet based on your restaurant type, e.g., no liquor category needed for fast casual, if you have an extensive bar, you might want to separate beer and wine from liquor, etc. The downloadable spreadsheet includes the following categories (but you can add or replace these with your own):

Meat/Seafood – Dairy – Produce – Dry Goods – Grocery – Bread –  Beer/Wine/Liquor – Menu Item Supplies (takeout and delivery packaging, napkins, utensils, etc.)

2. Create a table or a chart with columns (vertical) and as many rows (horizontal) as you have brands of items. Include separate sections for each category (e.g., meat, bread, dairy, produce, etc.), then create eight columns for specific item category (e.g., beef, milk, lettuce, etc.), item name, description, unit of measure, count number, unit price and total, and category total. (For a more detailed spreadsheet that simplifies ordering and facilitates accounting, also add columns for vendor item number, vendor, case cost, vendor pack size, default purchase unit of measure, inventory unit of measure, split unit of measure, cost account name and inventory account name.)

3. Create a formula in the total column by multiplying the count number by the unit price. Also create a formula totaling all items in each category, plus a grand total for all categories.

4. Determine the frequency of your inventory counts (daily, weekly, monthly) and create a copy of the inventory spreadsheet to use as a template.

5. Print a copy of the spreadsheet to manually record the inventory items. Enter each of your food and beverage items into the proper categories, assign a count unit and unit price to each, and complete the other columns for each item.

6. Copy the numbers into your computer spreadsheet. The inventory spreadsheet will automatically calculate the item extensions (count unit multiplied by the unit price) and total all items by category as well as calculate a grand total for all food and beverage items.

7. Create a formula in the total column by multiplying the count number by the unit price. Also create a formula totaling all items in each category, plus a grand total for all categories.

8. Calculate the change in inventory from the prior period and make an adjusting entry into your accounting system to provide accurate Cost of Goods Sold (CoGS) numbers. (See details in the next section.)

How to Calculate a Cost of Goods (CoGS) Report

Your CoGS is made up of the products you purchase to create the menu items your restaurant sells. This includes your food and beverage ingredients, as well as supplies such as napkins, coffee filters, etc. If you’re currently doing delivery and takeout, you must also factor packaging, utensils and other extras that aren’t part of your dine-in CoGS.

To calculate your CoGS totaled during a given period, use the following formula:

Beginning Inventory + Additional Purchases Made During the Period – Ending Inventory = CoGS

In this uncertain time when you’re not at full dining room capacity and may have added off-premise options, you will need to start tracking new patterns in inventory levels and adjust your orders accordingly.

How to Create A Stock Usage Report

A stock usage report displays the stock usage for a particular account and stock count. This can be beneficial in comparing stock counts to see a trend and help forecast future item orders. Your stock usage report should include these 11 metrics to determine your usage:

• Item category: the item categories counted in the stock count
• Beginning inventory: the end inventory count from the last stock count
• Purchases: the items that were purchased since the last stock count
• Total: the total amount of items (beginning inventory plus purchases)
• Ending inventory:  the actual amount of inventory counted in the stock count
• Use $: the volume of items that were used (total minus ending inventory)
• Sales: the sum of the items that were sold since the last stock count
• % Sales: the item category’s use (#6) divided by the sales amount (#7)
• Inventory change: the change in inventory from the beginning count to the end count
• Average inventory: the average of the beginning inventory and end inventory
• Turns: the use $ divided by the average inventory

If you’re using inventory management software, the stock usage report is created for you in the system if you simply run the report.

How to analyze your restaurant inventory reports

Once you have your numbers, it’s imperative that you keep a close watch on your restaurant inventory spreadsheet and reports to track your stock consumption and waste. Even if you don’t have a restaurant inventory management system, you can do this with a restaurant POS system that generates some simple inventory reports.

Insights about past and current inventory allows you to make better choices to improve your food cost in the future. In particular, tracking theoretical vs. actual food cost variance, can help you spot trends over periods of time and make improvements to your food cost. Using these reports, you can identify the areas where your food costs are highest due to waste, errors, over portioning and theft to help you make smart data-driven decisions to reduce them.

Why it might be time to consider restaurant inventory management software

Restaurant inventory management software provides a systematic, accurate method for tracking food and beverage inventory and automates many parts of the process. Cloud-based restaurant inventory software replaces the spreadsheet and clipboard process with simplified, partially automated, and streamlined inventory tracking. As restaurant technology evolves in the industry, restaurant and food inventory management software can play a key role in accurately monitoring food costs and helping to reduce waste, errors, over portioning, and theft.

Consider choosing an inventory management platform that has a mobile app, allowing your team members to move around the walk-in cooler and dry storage without lugging a clipboard or laptop.

Also look for an inventory management system that integrates with your POS system for recipe costing and menu engineering. Your POS system contains valuable historical data about sales, accounting, and customer behavior that can be used to make better operational decisions in the future. While some inventory management platforms are partly compatible with POS systems, avoid custom workarounds and imports by choosing a restaurant management platform that includes accounting and inventory management, and fully integrates with your POS system.

Conclusion

Managing inventory with spreadsheets is possible but tracking your inventory with a restaurant inventory management system benefits your restaurant in a multitude of ways, including improving order accuracy, identifying and correcting waste, streamlining your budget, and adding to customer satisfaction by ensuring menu items aren’t out of stock.

If you would like to easily track your inventory and gain insight into your operations, consider a restaurant-specific inventory management solution. Restaurant365 incorporates restaurant accounting software and restaurant operations software into an all-in-one, cloud-based platform. For more information, schedule a free demo.