Lots of restaurants control their food cost using a target food cost percentage combined with a purchase recap and an ending inventory value. They use Excel to do the calculations for the ending inventory.
If you use the calendar for inventory cutoffs, you will be counting the stock on various days of the week. You need to make sense of the count for any given day of the week. For example, we'd expect to find high inventory levels closer to the weekend and lower levels early in the week at many dinner houses.
One simple exercise can greatly improve your knowledge of how your food cost varies. You need to get a feel for the 25 items you spend the most amount of money on over the entire year. Vendor tracking reports and invoice reviews can quickly isolate these items.
Closely track the cases purchased for each of these 25 items in a separate Excel file or worksheet. The data would include the date, number of cases and the cost (use the extension figure). Each month, you need to recap the purchases for each item. All we need is the summary data: total cases and total cost.
On your inventory matrix, add a column for PURCHASED to the right of the inventory extension column. For each of the top 25 items, add the total purchases amount in the new column.
Create another column to the right of PURCHASED called DAYS. For each of the top 25 items, you will divide the inventory total by the purchased total in parentheses and multiply by the days in the month. For example, if your inventory for burger patties was $1,200 and you purchased $3,000 in a 30 day month, your number of days would equal 12 days.
Put the number of days for each of the top 25 items in context. Is the item frozen, fresh, canned or dry? Most fresh items should yield a low number of days. You would not want to see 45 days of fresh boneless, skinless chicken breasts. The freezer may have been stocked due to an especially low cost on a small number of selected items. Make sure the cost per case for all over stocked frozen items justifies the quantity purchased.
Fresh fish, poultry and meat should have less than 7 days in stock. Remember all over stocked items are using cash which could be used in other areas.
Logos, product and company names mentioned are the property of their respective owners.