FREE Restaurant Inventory Sheet: A Guide to Inventory Management
Are you currently using a spreadsheet or pen and paper to manage your inventory? You've come to the right place. In this article, we're going to cover everything you need to know to manage your inventory using a spreadsheet. Alternatively, we're going to dive a little bit deeper on how you can scale your inventory operations with a technology solution like MarketMan. Let's dig right in!
Looking for a spreadsheet template? Download MarketMan's FREE inventory Google spreadsheet here.
What is Restaurant Inventory Management?
Restaurant inventory management refers to the process of tracking the stock of ingredients coming into and out of your restaurant. This includes the number of items that you order, what is sold through different revenue centers such as your kitchen and your bar, what is left over, and what is transferred between stores if you have multiple units.
MarketMan, an inventory management solution we will explore, helps you track each item’s usage over time, which you can compare with your sales and investigate discrepancies. It also helps you to know how many ingredients you need to order, minimize waste, and calculate your food costs and cost percentages.
Inventory management is essential to minimize and manage waste and have the right amount of inventory on hand to meet your sales demand, reducing excess inventory, waste, spoilage, and instances of poor inventory portioning or mishandling.
Control your costs by keeping a regular inventory cycle count of the top 10-15 products that comprise most of your food costs. Count the beginning inventory, add purchases during the day, subtract your ending inventory at the end of the day, and you will see the amount of each product that was used during the day. You can calculate both your restaurant and bar’s inventory usage.
Compare this inventory usage number with your POS product usage, and if the actual inventory usage is greater, dig deeper. This may mean there is theft or over-portioning. By identifying these cost variances with a restaurant inventory management system, you can proactively control your food costs in the future.
What is Considered Inventory for a Restaurant?
Inventory includes everything that costs money in your restaurant. This includes your food, liquor, dry storage, cooking utensils, uniforms, or linens. Separate your inventory by items that are found in different areas of the restaurant, such as the back or house or kitchen, front of house, and bar.
Food inventory consists of all the items or raw materials used to make your dishes, or finished goods. When you first buy food, it is considered an asset on your restaurant balance sheet as long as it sits in your restaurant. Once you sell it, it becomes part of your COGS, or cost of goods sold, and is categorized as an expense.
Your COGS calculation is composed of your restaurant inventory. To find your COGS, the equation is COGS= Beginning Inventory + Purchased Inventory – Ending Inventory.
To find your Net Profit, you subtract your COGS and labor costs from your sales, then add back total operating costs. Net Profit = Gross Profit (Total Sales – COGS) – Labor Cost + Total Operating Cost.
A lower COGS means a higher net profit. To lower your COGS, conserve more inventory, buy less inventory, or find ways to lower your food costs. You can do this by practicing effective inventory management.
How to Measure Your Restaurant’s Financial Health
There are many ways to do restaurant accounting and track your establishment’s financial health. You’ll want to keep a restaurant balance sheet, which measures your assets, liabilities, and equity at a given point in time, a cash flow statement, which measures your cash flow, and an income statement, or P&L, which measures your revenue, expenses, and profit in a period of time.
A supplemental way to track your financial health is by using a restaurant sales sheet. A restaurant sales sheet is an excel spreadsheet that allows you to track your daily, weekly, monthly, or quarterly sales. You can track your daily sales and deposits, which you will find reported from your POS system.
Oftentimes, your POS will allow you to generate a daily sales report in which you find your cash, credit card sales, and a summary of sales by category: food, beverages, alcohol, etc. You can then enter this information and aggregate it onto an excel sheet. You can break down your sales in various ways, by category or even individual dishes.
Knowing your periodic sales figures is key to understanding your financials and the health of your restaurant business.
Once you know your sales, you’ll want to enter it into a periodic P&L statement.
A restaurant P&L, or profit and loss statement, is the restaurant’s income statement or statement of earnings. It contains the revenue and expenses of a restaurant in a given period of time, showing your costs subtracted from your sales, and your gross and net profits.
The P&L gives you an idea of your restaurant’s financial health over a period of time, measuring whether or not you are profitable. A P&L is perhaps the most important financial document in your accounting records.
Some key figures on your P&L are your costs, or expenses. These include direct costs, which are costs that go into producing your product, and indirect costs, which are costs that generally keep your business operating. You can aggregate these expenses into buckets of categories on your P&L.
Some restaurant expense categories reflected on an income statement can include: COGS, labor expenses, operating expenses, promotional and marketing expenses, repairs and maintenance, occupancy such as rent and insurance, and corporate overhead such as management fees and franchise royalties.
Here is a breakdown of some common expense buckets and what’s included:
- Labor costs: salaries, wages, benefits, unemployment taxes and service commissions. These are variable costs.
- COGS: food and beverage cost. You can set a goal to keep your food cost calculation at a certain percentage, such as 32%, of your food sales.
- Marketing and Advertising: includes the cost of what you do to attract guests into your restaurant. Includes your menu, table tents, entertainment, music, coupons, website, and ads.
- Occupancy expenses: expenses related to a restaurant’s physical building. Includes taxes, rents, insurance, utilities, signage, and parking fees.
- Repairs and Maintenance: expenses needed to keep the restaurant’s operations. Includes maintenance of the building, dining area, kitchen, food prep, and cleaning equipment.
- Daily administrative costs: Includes admin costs such as office supplies, telephone charges, postage, fees to accountants and lawyers, licensing fees to health departments, and beverage licenses for serving alcohol.
How Do Restaurants Take Inventory?
There are many ways establishments can choose to implement a restaurant inventory system. Restaurants can choose to take inventory via pen and paper and excel spreadsheets, or with an automated software system.
To manage inventory in excel, management or designated personnel will count physical stock of items in the restaurant, jot them down on paper or excel printouts, and enter the food stock count into large spreadsheets in the back office to calculate food costs and make new orders. A typical restaurant inventory sheet includes a list of items, their unit of measure, amount, unit price, and total cost.
Download MarketMan's FREE inventory spreadsheet here.
Although you may choose to use excel restaurant inventory sheets to start off with, keep in mind that this is not an ideal process in the long run. There is an industry term “Death by Excel”- utilizing excel for your inventory management is cumbersome and tedious, haphazard, and prone to human error. We will discuss the limitations of excel and pen and paper later in this article.
How Often Should a Restaurant Do Inventory?
Let’s explore some inventory management best practices.
Create a consistent schedule to conduct regular inventory checks. Different types of ingredients may have different frequencies for inventory tracking, such as daily, weekly, or monthly. You will want to check your perishable goods more often.
A restaurant should take daily inventory of at least the top 10-15 items that comprise its food costs, if not all its food products. Keeping a daily inventory is recommended for both big and small restaurants. You should also keep a daily stock of your bar inventory.
A restaurant should also take full inventory as often as you order items. If you regularly order items five times a week, take inventory of your food before you order to determine the amount you will need to buy. Take at least weekly inventory of all your food products in order to stay on top of your food and beverage costs.
Take monthly inventory of everything, including your food products, ingredients, and cleaning products, to calculate the COGS for your income statements. Additionally, you may have ad hoc inventory checks for specific items to detect shortages, surpluses, and theft.
Here are some other tips for effective inventory management:
- Know and track the specialty ingredients you need to properly execute your core concept. For example, a pizza chain will need batches of fresh marinara and cheese. A steakhouse will need special steaks, spices, and sauces.
- Know inventory terms such as COGS, catch weight, sitting inventory, food cost percentage, inventory turnover ratio, par level, recipes, estimated usage, theoretical vs. actual usage, variance, and waste.
- Organize your supplies so that employees can locate items quickly and everything is in the right place. Make labels for your storage shelves.
- Have the same key employees take inventory. Pick people you trust to take inventory, such as managers or your chef. Train them on effective inventory tracking and finding errors.
- Practice the FIFO method by moving your older ingredients to the front of storage shelves to ensure you use those first.
- Set goal metrics like sales percentages, days in inventory, or turnover. Communicate these goals and work with your staff to reach them.
- Train all your staff to be aware of inventory issues.
- Track your food waste. Create a log of your food waste and waste reasons. MarketMan has a functionality where you can easily create a record of your waste.
- Keep track of demand for inventory and set par levels accordingly, so you know how much to reorder.
- Use a software system instead of pen and paper and spreadsheets. This automates number crunching, gives you automatic reminders when your inventory levels are low, and allows you to have accurate and real-time data, reliability, and scale.
How to Use Your Own Restaurant Inventory Sheet
When you’re a new restaurant owner or one who just started a regular process of inventory management, the process can seem a bit daunting.
Here is how to create your own restaurant inventory list to track your inventory:
- Categorize your inventory: meat, dairy, product, groceries, bread, liquor, dry goods, and supplies
- Make a table with your categories and items on the rows, description, unit of measure, count, unit price, total, and category totals on the columns.
- Make a formula in the totals column by multiplying the count by unit price. Make a formula totaling all items in each category, and a total for all categories.
- Decide how often you will count inventory (daily, weekly, or monthly), and make a copy of this spreadsheet to use.
- Print this spreadsheet to manually record inventory. Complete the spreadsheet.
- Copy your written numbers onto your computer spreadsheet, and the sheet will automatically calculate your total prices, category totals, and final total.
- Calculate the inventory change from the prior period, and make an adjusting entry in your accounting for COGS.
Using this method of excel tracking is good for you to get your feet wet with inventory management. However, as you grow and scale, you will want to transition out of doing inventory via excel and pen and paper and into using a software solution. In the next section, we will explore some limitations of an analog system.
Limitations of Pen & Paper and Excel for Inventory Management
The restaurant inventory sheet is a great start for you to get a sense of how to track inventory simply. However, you will want to transition out of using pen and paper and excel for inventory management once you are more familiar with the process and as your restaurant grows.
There are many disadvantages and limitations with using pen and paper, spreadsheets, inventory forms, evaluations, checklists, and bar inventory spreadsheets.
Using spreadsheets and paper solutions for taking inventory is cumbersome, time-consuming, and prone to human error. Employees take hours per week counting stock, writing down data on paper, and converting it into numbers on multiple spreadsheets. Because the process is tedious, your staff may decide to find shortcuts or eyeball numbers.
An analog system to take inventory with pen and paper also doesn’t scale. It’s not easy to find the data that you need, and it’s hard to keep all the relevant information together. With paper records, you can easily spill food or beverages and lose data without any backups. With a software system, you can keep backups of your data so that you always have a copy of it in case anything happens.
There is also a lack of a central system and accessibility. You will need to keep several spreadsheets and tie data together. Multiple people doing inventory may create different versions of the same spreadsheet with conflicting information.
With an analog system, you can’t easily collaborate or make changes to your inventory as your restaurant or group of restaurants grow, since there is no central system that can be updated across your enterprise. With a software solution, you can easily make changes to one central system without worrying about multiple versions of spreadsheets floating around.
MarketMan- Your Inventory Management and Reporting Solution
A full-fledged inventory system solution like MarketMan will give you all the benefits of a software solution—a central system, more reliability, ease of use, and less chance of human error.
MarketMan helps restaurants keep costs under control and manage inventory efficiently by automating back of house operations.
Here are some of the features of MarketMan:
MarketMan helps you manage and track your inventory consumption in an automated way. The software manages perpetual inventory by syncing with your POS system and automatically deducting ingredients from your inventory every time a sale is made. This allows you to know your usage and sales at any point in time, giving you insight into your overall Food Cost Percentage.
MarketMan also streamlines the inventory counting process. You can take inventory counts on mobile devices, which is easier to use than pen and paper and provides accessibility. Multiple users can take inventory counts at the same time, and your on-hand levels are updated throughout the entire system.
The system will also alert you when your on-hand levels are below par level, allowing you to place new orders immediately. You can be proactive about managing your inventory so you don’t run out of the ingredients necessary to make your sales.
You can also easily track waste and theft, reducing shrinkage. This reduces unforeseen costs. When you monitor the usage of your inventory through MarketMan, you can find inefficiencies and make corrections so that you lower food costs and maximize profits.
Inventory and COGS Reports
MarketMan has robust reporting capabilities to drill down to your food costs, developing actionable insights and driving smarter business decisions. Two of the most common reports are the COGS and Gross Profit Report and the Actual vs. Theoretical (Variance) Report.
COGS and Gross Profit Report:
The COGS and Gross Profit report gives you key insights that help you reduce food costs and drive profits.
The COGS and Gross Profits Report takes your aggregate purchasing data, inventory counts, and aggregate sales data to tell you what COGS and gross profits are.
You can also break down your revenue and COGS by different revenue centers within your restaurant. The revenue tells you the sales generated and COGS tells you the costs of ingredients and food supplies. You can see which categories or items (for example, dairy, produce, pasta, etc.) are causing your numbers to be high.
This gives you a high-level picture of the efficiency and usage of inventory within the business. You can also find out which parts of your restaurant are driving more costs or sales, and act accordingly.
Actual vs. Theoretical and Waste Reports:
The Actual vs. Theoretical (Variance Report) is a comparison summary showcasing discrepancies between theoretical usage and actual usage. In this report, you can find the difference between your theoretical and actual costs, and see what is causing the difference.
By comparing your sales information against your inventory counts for a certain time period, you can see whether there’s any type of product misappropriation that’s not going to its intended purpose—to serve customers. Using this report, you can find out whether you have a waste, over-portioning, or theft issue.
Along with a waste report within MarketMan, you can analyze your logged waste events and reasons, see what’s causing high-cost variance levels, and reduce waste in the future.
MarketMan’s reporting capabilities provide inventory management and food costing insights, analytics tools, and complete visibility over all aspects of your business.
Effective inventory management is crucial to a restaurant’s profitability, growth, and financial health. By following the best practices and tips in this article, and investing in MarketMan as your software solution, you’ll set your restaurant up for success.
Schedule a demo to learn about how MarketMan can help your restaurant manage inventory today.