farmsoft
Food stock control template
Food stock control spreadsheet template, Food inventory reports, labels, invoice, bill of lading - design your own food inventory reports, or copy existing templates and edit them as you like.

Food stock control template

FREE TRIAL

Restaurant Inventory Spreadsheets: How To Maintain And Monitor Them For Growth

restaurant inventory spreadsheets

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.

Consumption report is one of the most important restaurant inventory spreadsheet, which you must start using right away!

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.

To keep a vigilant eye on your stock, you ought to have restaurant inventory spreadsheet like the Perpetual Inventory Form

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:

In order to have a detailed account of all each item, its quantity and its cost that go into preparing a dish, you can use recipe costng form, which is a type of restaurant inventory spreadsheet.

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.

sales report is one of the important restaurant inventory spreadsheet that will help you to keep a track of the expiry dates off all the items in your inventory.

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. The Food Stocktake template helps you in tracking the food stock in your restaurant. The food can be categorized under different categories like fish, meat, sweets and desserts, fruits and vegetable, dairy, bakery etc. You can even add more values to these categories. The categorization of food stock helps you in analysing the product purchase cost, stock available and gross profit organized by specific categories. You can use this food stock template to manage the food variances and improve your profitability. It gives a quick overview of the Cost of Inventory at the opening, Forecasted Profit Margins, Actual Profit Margins or Losses and Cost Variances. All this information can be tracked on weekly basis and for a period of four to five weeks.


The Food Stocktake template helps you in tracking the food stock in your restaurant. The food can be categorized under different categories like fish, meat, sweets and desserts, fruits and vegetable, dairy, bakery etc. You can even add more values to these categories. The categorization of food stock helps you in analysing the product purchase cost, stock available and gross profit organized by specific categories. You can use this food stock template to manage the food variances and improve your profitability. It gives a quick overview of the Cost of Inventory at the opening, Forecasted Profit Margins, Actual Profit Margins or Losses and Cost Variances. All this information can be tracked on weekly basis and for a period of four to five weeks.


Restaurant Inventory Spreadsheet to Get Started with Inventory Management

By Anita Price  July 9, 2020

  • Blog
  • Email*

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.

Restaurant labor

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.

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

Stock usage 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 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.

Basic Inventory Procedures

A key component in effective kitchen management is inventory control. By knowing what supplies are on hand at a given time, the manager will be able to plan food orders, calculate food costs since the previous inventory, and make menu item changes if needed. By keeping an eye on inventory, it is possible to note potential problems with pilferage and waste.

Managing inventory is like checking a bank account. Just as you are interested in how much money you have in the bank and whether that money is paying you enough in interest, so the manager should be interested in the value of the supplies in the storeroom and in the kitchen.

An inventory is everything that is found within your establishment. Produce, dry stores, pots and pans, uniforms, liquor, linens, or anything that costs money to the business should be counted as part of inventory. Kitchen items should be counted separately from the front of house and bar inventory and so forth.

Regardless of the size of your operation, the principles of inventory control are the same. In larger operations there will be more people and sometimes even whole teams involved with the various steps, and in a small operation all responsibility for managing the inventory may fall on one or two key people.

Effective inventory control can be broken down into a few important steps:

  1. Set up systems to track and record inventory
  2. Develop specifications and procedures for ordering and purchasing
  3. Develop standards and procedures to efficiently receive deliveries
  4. Determine the frequency and processes for reconciling inventory
  5. Analyze inventory data and determine any areas for improvement

Setting Up Systems to Track and Record Inventory

One of the reasons you take inventory is to determine food costs and to work out cost percentages. There are several procedures that simplify finding the value of goods in storage. These techniques are based on keeping good records of how much supplies cost and when supplies were purchased.

The temptation in small operations is to treat inventory control casually. Perhaps there are only one or two people doing the purchasing and they are usually aware of the supplies that are on hand. This doesn’t eliminate the need to track purchases against sales to see if you are managing your costs as well as you can.

Almost all inventory control procedures are time consuming. Moreover, such records must be kept up-to-date and done accurately. Trying to save a few hours by cutting back on the time needed to keep inventory records may be money poorly saved.

The simplest method for tracking inventory is using a spreadsheet. A simple spreadsheet might list all of the products that are regularly purchased, with the current prices and the numbers on hand at the last inventory count. The prices can be updated regularly as invoices are processed for payment, and a schedule can be set to count the product on hand.

In large operations, the systems need to be more sophisticated as there are more people involved. Purchases might be made by a separate department, inventory records might be kept by a storeroom clerk, and the tracking and counting of inventory might be tied to a system using scanners and barcodes, which in turn may be linked with your sales system so that there is always a record of what should be in stock.

No matter the depth of detail used, having a system to track inventory gives managers a good idea of supplies on hand and a tool to use to manage costs.

Incoming Inventory

The primary reason for establishing a consistent method for accepting ordered goods is to ensure that the establishment receives exactly what has been ordered. Errors frequently occur, and unless the quantity and quality of the items delivered are carefully checked against what was ordered, substantial losses can take place. When receiving procedures are carefully performed, mistakes that could cost the restaurant time and money are avoided. In addition, an effective receiving method encourages honesty on the part of suppliers and delivery people.

Invoices

The most important document in determining if the goods received are the goods ordered is the invoice. An invoice is an itemized list of the goods or products delivered to a food preparation premise. An invoice shows the quantity, quality, price per kilogram or unit, and, in some cases, the complete extension of the cost chargeable. Only by carefully comparing and checking can you be sure that the information on the invoice tallies with the products received. This comparison may require that items be weighed and/or counted.

Whenever possible, the receiver should check the invoice against the purchase order or purchase request slips. This will ensure that the quantity and price of the goods shipped match those listed on the order form. If the invoice is not checked against the purchase order when the goods arrive, there is the potential that you will be missing products you need or receive products that were not ordered or are in incorrect quantities.

In addition, the quality of the goods should be determined before they are accepted. For example, boxes of fresh produce and frozen foods should be opened and inspected to ensure quality.

When you are satisfied that the delivery is in order, sign the invoice. In most cases, the invoice is in duplicate or triplicate: you keep the original and the delivery driver retains the other copy or copies. Once you have signed, you have relieved the delivery company of its responsibilities and the supplies now belong to your company. You may, therefore, become responsible for any discrepancies between what is on the invoice and what has been delivered. It is good practice to bring any discrepancies or errors to the attention of the driver and have him or her acknowledge the mistake by signing the invoice. If a credit note is issued, that should also be marked on the invoice by the driver.

Note: Do not sign the invoice until you are sure that all discrepancies have been taken care of and recorded on the invoice.

Take the signed invoice and give it to whoever is responsible for collecting invoices for the company.

The receiving of deliveries can be time consuming for both the food establishment and the delivery service. Often the delivery people (particularly if they are not the supplier) will not want to wait while these checks are done. In this case, it is important that your company has an understanding with the supplier that faults discovered after the delivery service has left are the supplier’s problems, not yours.

Once the invoices have been signed, put the delivered products in the proper locations. If you are required to track incoming inventory, do so at the same time.

Outgoing Inventory

When a supply leaves the storeroom or cooler, a record must be kept to track where it has gone. In most small operations, the supplies go directly to the kitchen where they are used to produce the menu items. In an ideal world, accurate records of incoming and outgoing supplies are kept, so knowing what is on hand is a simple matter of subtraction. Unfortunately, systems aren’t always that simple.

In a smaller operation, knowing what has arrived and what gets used every day can easily be reconciled by doing a regular count of inventory. In larger operations and hotels, the storage rooms and coolers may be on a different floor than the kitchen, and therefore a system is needed that requires each department and the kitchens to requisition food from the storeroom or purchasing department, much like a small restaurant would do directly from the supplier. In this model, the hotel would purchase all of the food and keep it in a central storage area, and individual departments would then “order” their food from the storerooms.

Requisitions

To control inventory and to determine daily menu costs in a larger operation, it is necessary to set up a requisition procedure where anything transferred from storage to the kitchen is done by a request in writing. The requisition form should include the name and quantity of the items needed by the kitchen. These forms often have space for the storeroom clerk or whoever handles the storeroom inventory to enter the unit price and total cost of each requested item (Figure 1).

In an efficiently run operation, separate requisition forms should be used by serving personnel to replace table supplies such as sugar, salt, and pepper. However, often personnel resist using requisition forms because they find it much easier and quicker to simply enter the storage room and grab what is needed, but this practice leaves no record and makes accurate record keeping impossible. To reduce the possibility of this occurring, the storage area should be secure with only a few people having the right to enter the rooms, storage freezers, or storage refrigerators.

Figure 1: Sample Requisition Form

Date: _____________

Department: Food Service

QuantityDescriptionUnit CostTotal Cost6 #10 cansKernel corn25 kgSugar20 kgGround beef6 eachPork loins

Charge to:

Catering Dept.

C. Andrews

Chef

Not only does the requisition keep tabs on inventory, it also can be used to determine the dollar value of foods requested by each department and so be used to determine expenses. In a larger operation where purchases may be made from different suppliers at different prices, it may be necessary to tag all staples with their costs and date of arrival. Expensive items such as meats are often tagged with a form that contains information about weight, cost per unit (piece, pound or kilogram), date of purchase, and name of supplier.

Pricing all items is time consuming, but that time will soon be recovered when requisition forms are being filled out or when the stock has to be given a monetary value. In addition, having prices on goods may help to remind staff that waste is costly.

Inventory Record Keeping

There are two basic record keeping methods to track inventory. The first is taking perpetual inventory. A perpetual inventory is simply a running balance of what is on hand. Perpetual inventory is best done by keeping records for each product that is in storage, as shown in Figure 2.

A sample perpetual inventory form for canned peaches. Image description available.
Figure 2: Perpetual inventory form. [Image description]

When more of the product is received, the number of cans or items is recorded and added to the inventory on hand; when some of the product is requisitioned, the number going out is recorded and the balance is reduced. In addition, the perpetual inventory form can indicate when the product should be reordered (the reorder point) and how much of the product should ideally be on hand at a given time (par stock).

In small operations, a perpetual inventory is usually only kept for expensive items as the time (and cost) of keeping up the records can be substantial.

The second inventory record keeping system is taking a physical inventory. A physical inventory requires that all items in storage be counted periodically. To be an effective control, physical inventory should be taken at least monthly. The inventory records are kept in a spreadsheet or in another system reserved for that purpose.

The inventory sheet (Figure 3) can list the items alphabetically or in the order they will appear on the shelves in the storage areas.

Figure 3: Physical Inventory Form

Physical Inventory Form: MarchProductUnitCountUnit PriceTotal ValueLima beans6 #104 1/3$23.00$99.60Green beans6 #103 5/628.95110.98Flour25 kg bag314.8544.55Rice50 kg bag132.5032.50Total$593.68

In addition to the quantity of items, the inventory usually has room for the unit cost and total value of each item in storage. The total values of the items are added together to give the total dollar value of the inventory. This is also knows as extending the inventory. The total value of the inventory is known as the closing inventory for the day the inventory was taken. This amount will also be used as the opening inventory to compare with the next physical inventory. If the inventory is taken on the same day of each month, the figures can be used to accurately determine the monthly food cost.

The physical inventory is used to verify the accuracy of the perpetual inventory. For example, if 15 whole beef tenderloins are counted during a physical inventory, but the perpetual inventory suggests that there should be 20 tenderloins on hand, then a control problem exists and you need to find the reason for the variance.

Computerized Inventory Control

Most people today use computerized systems to calculate, track, and extend inventory. These systems enable the restaurant to have a much tighter and more accurate control over the inventory on hand and the costs of that inventory. Having access to information such as ordering history and the best price paid is just one of the benefits of these systems. They can also help the purchaser predict demand levels throughout the year. These programs in many cases are also integrated with the point-of-sale (POS) system used to track sales, and can even remove an item from a computerized inventory list when the waiter registers the sale of any menu item on the restaurant terminal. That is, if a customer orders one chicken dish from the menu, all the items required to make one portion of the chicken are discounted from inventory. This provides management with an constant up-to-date perpetual inventory of most inventory items.

Smaller operations will use a spreadsheet application to manage inventory, so you should also be familiar with a program like Microsoft Excel if you are responsible for ordering and inventory. The information required for the program to do the calculations properly is available from the invoices received with your supplies. That is, the quantities and prices of the goods you most recently received should be entered into the computer program either by you or by the restaurant’s purchaser. These prices and quantities are automatically used to calculate the cost of the goods on hand. This automated process can save you an enormous amount of time and, if the information entered into the computer is accurate, may also save you money. In any inventory system, there is always a possibility for error, but with computerized assistance, this risk is minimized.

Pricing and Costing for Physical Inventory

The cost of items purchased can vary widely between orders. For example, cans of pineapple might cost $2.25 one week, $2.15 the second week, and $2.60 another week. The daily inventory reports will reflect the changes in price, but unless the individual cans have been marked, it is difficult to decide what to use as a cost on the physical inventory form.

There are several different ways to view the cost of the stock on the shelves if the actual cost of each item is difficult to determine. Most commonly, the last price paid for the product is used to determine the value of the stock on hand. For example, if canned pineapple last cost $2.60 a can and there are 25 cans on hand, the total value of the pineapple is assumed to be $65 (25 x $2.60) even though not all of the cans may have been bought at $2.60 per can.

Another method for costing assumes the stock has rotated properly and is known as the FIFO (first-in first-out) system. Then, if records have been kept up-to-date, it is possible to more accurately determine the value of the stock on hand.

Here is an example showing how the FIFO system works.

Example 10

The daily inventory shows the following:

DateNumber and value of cansOpening inventory, 1st of month15 cans @ $2.15 = $32.25Received on 8th of month24 cans @ $2.25 = $54.00Received in 15th of month24 cans @ $2.15 – $51.60Received on 23 of month12 cans @ $2.60 – $31.20

If the stock has rotated according to FIFO, you should have used all of the opening inventory, all of the product received on the 8th, and some of the product received on the 15th. The 25 remaining cans must consist of the 12 cans received on the 23rd and 13 of the cans received on the 15th. The value of these cans is then

12 cans @ $2.60          = $31.20

13 cans @ $2.15          = $27.95

Total                             = $59.15

As you can see, the choice of costing method can have a marked effect on the value of stock on hand. It is always advisable to use the method that best reflects the actual cost of the products. Once a method is adopted, the same method must be used consistently or the statistical data generated will be invalid.

Costing Prepared or Processed Items

When you are building your inventory forms, be sure to calculate the costs of any processed items. For instance, sauces and stocks that you make from raw ingredients need to be costed accurately and recorded on the spreadsheet along with purchased products so that when you are counting your inventory you are able to reflect the value of all supplies on the premises that have not been sold.

(We will discuss more about calculating the costs of products and menu items later in this book.)

Inventory Turnover

When accurate inventory records are kept, it is possible to use the data in the records to determine the inventory turnover rate. The inventory turnover rate shows the number of times in a given period (usually a month) that the inventory is turned into revenue. An inventory turnover of 1.5 means that the inventory turns over about 1.5 times a month, or 18 times a year. In this case, you would have about three weeks of supplies in inventory at any given time (actually 2.88 weeks, which is 52 weeks ÷ 18). Generally, an inventory turnover every one to two weeks (or two to three times per month) is considered normal.

A common method used to determine inventory turnover is to find the average food inventory for a month and divide it into the total food cost for the same month. The total food cost is calculated by adding the daily food purchases (found on the daily receiving reports) to the value of the food inventory at the beginning of the month and subtracting the value of the food inventory at the end of the month.

That is,

average food inventory = (beginning inventory + ending inventory) ÷ 2

cost of food = beginning inventory + purchases − ending inventory

inventory turnover = (cost of food) ÷ (average food inventory)

Example 11

A restaurant has a beginning inventory of $8000 and an ending inventory of $8500. The daily receiving reports show that purchases for the month totalled $12,000. Determine the cost of food and the inventory turnover.

Cost of food = $8000 + $12 000 − $8500 = $11 500

Average food inventory = ($8000 + $8500) ÷ 2 = $8250

Inventory turnover = $11 500 ÷ $8250 = 1.4

The turnover rate in the example would be considered low and would suggest that the business has invested too much money in inventory. Having a lot of inventory on hand can lead to spoilage, high capital costs, increased storage space requirements, and other costs.

Inventory turnover rates are not exact, for a few reasons. One is that in many food operations, accurate inventory records are usually kept only for more expensive items. Another is that the simple food cost used in the calculation does not truly reflect the actual food cost. (Food costs are discussed in another chapter in this book.) In addition, not all inventory turns over at the same rate. For example, perishables turn over as quickly as they arrive while canned goods turn over more slowly.

Even though turnover rates are not exact, they do give managers at least a rough idea of how much inventory they are keeping on hand.


FREE TRIAL

Producepak food manufacturing app

Read more
Read more
Read more
Read more
Read more
Read more
Read more
Read more
Read more
Read more

Producepak provides a simple solution for food manufacturers, processors, food packers, fresh produce packers, pack-houses, fruit & vegetable packers, and food packers. Producepak concentrates on food safety, food inventory control, food expiry management, first in first out stock rotation, food order management, food production management in batches, food shipping & sales, food export / import. Use the Producepak Quality Control module to improve food safety, or turn on a simple food safety checklist to ensure consistent quality food packing and production.

Producepak food manufacturing app is a simple to use solution for buying, and selling food inventory, fresh produce, seafood, meats, and flowers. The traceability built into Producepak provides instant recalls, accurate food traceability, and easy to produce audits, and mock audits. Producepak provides tools to pack food, fresh produce, flowers, and hops. You can use Producepak for food manufacturing, configure the bill of materials for each food product line you manufacture, and manage the food manufacturing process by projecting required raw ingredients requirements and scheduling batches and purchase orders for raw food manufacturing processes. Accurate fresh produce inventory management reduces waste through better FIFO stock rotation, stock-takes, and inventory alerts.

Increase the efficiency of food inventory using options like scanning incoming bar-codes to reduce data entry & errors. Guarantee food quality with quality control testing systems. Customer feedback management, supplier quality, customer qulity standards.

Producepak food manufacturing app can project required inventory (and shortages), schedule orders to be packed in batches , automatic alerts to production line managers, inventory teams telling them which inventory needs to be moved to which production line; guarantees the correct product and quantity is packed on time. Shipping teams are guided through the dispatch process from picking using a phone or tablet (optional bar-code scanning), automatic picking, thru bill of lading, invoice, and automatic shipping notifications for customers, transport, and sales teams. Automatic generation of food labels, bill of lading, invoice, picking documents and more; reduces administrative burden. Easy audit & recall systems reduces compliance costs.