Weekly Food Perpetual Inventory
Daily or Weekly Versions Available
Are you struggling with a high food cost?
Is your bottom line in the RED?
Are you losing sleep at night all because you cannot figure out why you are losing money and who might be responsible for it?
Workplace Wizards have created the weekly food perpetual inventory spreadsheet system.
Please do not confuse this with a normal weekly or monthly inventory. This system is completely different in every way. A normal inventory consists of physically counting every food and drink item and then calculation a food cost percentage.
Food Cost Formula
(Beginning Inventory + Purchases) – Ending Inventory on Hand = Cost of Goods (COGS) Than Cost of Goods Sold / Food Revenue = Food Cost Percentage.
Without your inventory, you will not be able to sell food or drinks. Treat the inventory as you would treat your wallet-protect it.
A perpetual inventory is tracking food items throughout the week, daily or per shift.
There are two versions of the Food Perpetual Inventory Systems.
(Daily or Weekly)
Both versions are exactly the same with the exception that one system is a Daily Perpetual Inventory and the other is a Weekly Perpetual Inventory system
Weekly Food Perpetual Inventory System
Special Low Price
Food Perpetual Inventory Home Page
The home page above is where you access the entire system
There are five weeks in this system; normally there will be 4 weeks of each month. Although some weeks may dip into week five as per the calendar year.
How does the perpetual inventory work?
You start off by making a list of the top 5, 10 or more food items that you want to track.
Typically, you want to track the most expensive food items in your restaurant or track items that are convenient for people to steal. If you do a normal inventory and you have a POS system that can give you a readout of the items that are short or missing, then you can add those items into the perpetual inventory to track.
Once you have the entire list of food items that you want to track, then you are now ready for the next step. In order for the system to generate useful numbers, the below information needs to be entered into the product page
- Product Name.
- Unit Measure.
- Serving Size.
- Servings Per Unit.
- Retail Price Unit.
- Cost Per Case, Each or Portion.
See the example below.
Food Perpetual Product Page
Once you enter the information on to the product page this process only needs done once. All the information on the product page will populate to every sheet in this system.
Now it is time to do the beginning inventory. See the example below
Week One Beginning Inventory Sheet
The first 6 columns that are color shaded above are protected and populate from the product page. The columns that are in the white shaded above are cells for you to enter in your inventory. The last column above will calculate automatically as you enter into the inventory. The last column inventory totals will populate to the week one final input sheet.
Now it is time to do the Ending Inventory inventory. See the example below
Week One Ending Inventory Sheet
The first 6 columns that are color shaded above are protected and populate from the product page. The columns that are in the white shaded above are cells for you to enter in your inventory. The last column above will calculate automatically as you enter into the inventory. The last column inventory totals will populate to the week one final input sheet
Week One Final Input Sheet
The white shaded cells are for you to input information.
All colored cells are protected.
- Purchases – The entire purchase dollar amounts though out the week.
- Vendor free products – Any free or promotional products by the case or each. Also enter all free or promotional product dollar amounts.
- Transfers in / transfers out by the case or each.
- Enter waste dollar amounts: food comps, waste, breakage, etc.
- POS sold from POS readout. Enter in each product sold amount from the POS.
Total servings used minus POS readout for each individual product shows the final shortage or overage of the inventoried item. It will show how many items you may have lost along with the dollar figure attached to it.
If you did the inventory at the beginning of the shift and conducted the ending inventory at the end of the shift and you show a shortage, then you have pinpointed when and who is responsible for the shortage. Now you can watch closely that particular shift to further understand what may be causing your issues.
Possible reasons why you are short product?
- Food waste.
- Customer Voids.
- Customer Comps.
- Server of Cook Issues.
This no joke, 70% of restaurants inventory shortages are employee related. It is not to say that theft is a large percent of it, it is a mix of reasons that may cause inventory shortages.
In a years’ time you may be out thousands of dollars in inventory that gets converted into cash.