Considering that restaurant inventory is one of the crucial areas which often falls into the trap of theft and pilferage, you ought to have a close check on it. The types of restaurant inventory spreadsheets are –
- Perpetual Inventory Spreadsheet
- Recipe Costing Card
- Waste Sheet
- Consumption Report
- Variance Report
- Expiry Report
These restaurant spreadsheets help you monitor the operations of your restaurant and allow you to have tight control over your stock and inventory.
Earlier, when restaurants were managed manually, restaurant inventory spreadsheets were used to maintain the stock consumption and keep track of the reports. Now, the restaurant inventory management software comes equipped with detailed reporting and provides the auto-generated reports of the restaurant inventory spreadsheets.
Essential Restaurant Inventory Spreadsheets That You Must Maintain and Track
When you are trying to keep a tight track of your stock and inventory, you must, under all circumstances, create these restaurant inventory spreadsheets. These sheets include:
1. Consumption Report
Your inventory management software must have a consumption report feature. This report will help you to have a complete track of the stock that comes in, the amount, and the price at which it is bought. This amount gets wasted, the amount returned to the base kitchen or the vendor for specific reasons; in short, it contains the detailed report of all the items in your inventory. For your convenience, below we have provided an example of how a consumption report should look like ideally.
2. Perpetual Inventory Form
The perpetual inventory system updates the inventory accounts and the available stock after each purchase or sale. You will have to create a table or a chart with 11 columns (vertical) and as many rows (horizontal) as you have brands of items. The perpetual inventory provides a highly detailed view of changes in inventory with immediate reporting of the amount of inventory in stock. It accurately reflects the level of goods at hand.
Using a perpetual inventory system makes it much easier for a restaurant to use the economic order quantity (EOQ) to purchase inventory. EOQ is a formula that managers use to decide when to buy stock, and EOQ considers the cost to hold inventory, as well as the firm’s cost to order stock. For your convenience, below, we have provided an example of how a perpetual inventory form should ideally look like.
3. Recipe Costing Card
A recipe costing card will help you to cut down on your daily costs to a great extent. Recipes for all your dishes and the bar drinks must be included in the recipe costing card. The cards would consist of every detail relating to every item; these come down to the ingredients that go into the dish, the quantity of each item that needs to be used, and even the things that will be used for garnishing.
This form also contains the costs of each item as purchased, the size of the portion, the value of the part, the selling price, and the food cost percentage. Making these cards and training your staff on how to use them eliminates the instances of incurring unwanted waste and over-pouring of drinks while ensuring that the consistency of each item is maintained. Plus, it provides a great training tool for new bartenders and other staff who do not have enough experience.
This is how a recipe costing card should look like:
4. Expiry Report
An ideal expiry report should contain the item code, the item name, the quantity, the unit, and the amount of each item. It also must mention the day it was purchased, manufactured, and the date by which it needs to be consumed. And once an item reaches its expiry date, the POS will automatically send you a real-time alert.
This report has two advantages; firstly, once you have the expiry date of the items available in stock, you will understand which items you need to use first to avoid the unwanted generation of waste. Secondly, this report will refrain you from using expired products, which might be detrimental to your customers and, in turn, to your restaurant’s health.
For your convenience, we have below given an example of how an expiry report must look like, that will help you to keep a tab over the expiry dates of all your inventory items.
5. Waste Sheets
Kitchen wastes can always be avoided. Waste includes dishes and drinks that are made incorrectly by untrained staff. These sheets must consist of detailed information. Once you keep track of what gets wasted and you will see a drop in waste, along with an automatic decrease in your overall cost.
Moreover, if you maintain such an account, your employees will try and follow the recipe management to avoid all the instances of waste generation. Create your very own waste sheet and see how daily tracking can reduce the overall generation of waste in your restaurant.
6. Variance Report
Variance Reports are restaurant inventory spreadsheets that give the difference between the Ideal Stock that should be consumed during a day and the actual Physical Stock that remains at the end. Recipe management is an integral feature of Inventory Management as it keeps track of the amount of stock consumed while preparing each dish.
Integrated with the POS, this feature will give you detailed accounts of the amount of actual stock consumed. Following which you can view and monitor Variance Reports that provide the difference between the Ideal Stock that should be consumed during a day and the actual Physical Stock that remains at the end. Ideally, a variance report should look like the following.
Steps to Maintain Restaurant Inventory Spreadsheets
When creating a restaurant inventory spreadsheet, make sure that you have these basic things included in it, which will help you to keep a check on the inventory.
- Enter all your inventory items in the proper categories. Includes both food and beverage and assign a count unit and unit price for each.
- Then, create a copy of the food and beverage inventory template for each month’s use.
- The monthly inventory sheet will automatically calculate the item and the total of all things by category and will calculate the total for all Food items and Beverage Items.
- 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 numbers for each month of operation.
A restaurant inventory management system is ideally equipped with the spreadsheets for restaurants that would give you a detailed picture of your stock and inventory consumption.
Analyzing The Inventory Reports
Now, the most important thing to do is to keep a close watch on these restaurant inventory spreadsheets and reports to keep track of your stock consumption and wastage. You can do this with the help of a smart restaurant POS system that generates these reports for you in an easy to understand format.
Using these reports, you can identify what are the areas where your food costs are highest due to wastage or internal pilferage and make smart data-driven decisions to reduce them.
Read this detailed guide that talks about how you can make decisions to control your food costs and ensure profitable operations.
