How to Set Up a Restaurant Purchasing Spreadsheet That Works

A restaurant purchasing spreadsheet works when every item is entered once, on one master list, along with its vendor, the unit you count it in, the unit you order it in, and how many count units are in one order unit. Everything else, including the weekly count, the order sheet, and the order log, pulls from that list instead of retyping it.

Most purchasing spreadsheets fail for a simpler reason than bad formulas. The same item shows up on four tabs with three spellings and two units, and nobody knows which copy is current. This guide lays out a five-tab setup you can build in Google Sheets or Excel, walks through an illustrative order with the arithmetic, and covers the places these sheets usually break.

The short answer: five tabs

The rule that holds it together: type an item's details only on the Items tab. The other tabs look them up by item name.

Tab 1: Items (the master list)

This is the tab everything else depends on. Suggested columns:

Columns E through G are where most ordering mistakes start. If you buy cream by the case but count it by the quart, the sheet has to know there are 12 quarts in a case, or someone has to remember it every time. Our guide to restaurant inventory units covers how to choose count and order units.

Column H is the amount you order up to. Where that number comes from is its own topic. If you don't have targets yet, setting restaurant par levels walks through it. For now, a reasonable starting number for your top items is enough to get the sheet working.

Turn the Vendor column into a dropdown that pulls from the Vendors tab. That way nobody can type "Broadline" on one row and "broadline " on the next. Both Google Sheets and Excel can do this with data validation. Microsoft's help page on creating a drop-down list shows the Excel version, and Sheets has the same feature under Data > Data validation.

Tab 2: Vendors

One row per vendor:

• Vendor name (exactly as it appears in the Items dropdown)
• Ordering method: portal, app, email, text, or phone
• Each cutoff as a day and a time, for example "Mon 4:00 PM"
• Delivery days
• Rep name, phone, and email
• Order minimum or delivery fee, if there is one
• Notes, such as a holiday schedule or a separate account number

The minimum matters for the order sheet, as the example below shows. If you haven't pinned down every vendor's cutoffs and delivery days yet, our guide to building a vendor ordering schedule goes through that step by step.

Tab 3: Count

Keep this tab simple enough to fill in on a phone in the walk-in:

Put the count date in a cell at the top (say B1 of a header row, with the item list starting on row 3). Showing the count unit right next to the entry box is the most useful thing on this tab. It's the difference between someone writing "2" for two bags and "2" for two cases.

After each order goes in, copy the Count tab's values into a Count History tab with the date, then clear column D. That gives you a record of what was on the shelf each week without letting old numbers sit where the next order will read them.

Tab 4: Order Sheet

This is where the math happens. One row per active item, sorted or filtered by vendor. The suggested quantity is calculated in count units first, then converted to order units and rounded up.

The formulas for row 2, assuming the Items columns above and item names starting in row 2:

B2  =VLOOKUP(A2, Items!$A:$J, 2, FALSE)
C2  =VLOOKUP(A2, Count!$A:$D, 4, FALSE)
D2  =VLOOKUP(A2, Items!$A:$J, 8, FALSE)
E2  =MAX(0, D2 - C2)
F2  =ROUNDUP(E2 / VLOOKUP(A2, Items!$A:$J, 7, FALSE), 0)
G2  =VLOOKUP(A2, Items!$A:$J, 5, FALSE)
I2  =IF(H2="", F2, H2) * VLOOKUP(A2, Items!$A:$J, 9, FALSE)

ROUNDUP(number, 0) rounds up to the next whole number, and it works the same way in Excel and Google Sheets (Microsoft's ROUNDUP reference). That's what turns 2.25 cases into an order of 3.

Column H is there because the suggestion is a starting point, not the final word. Sometimes you skip a case because a big party cancelled, or add one because the vendor is out next week. Typing in H instead of over F keeps the formula intact for next time.

Add a subtotal per vendor at the bottom of each vendor's group, so you can compare it with the minimum on the Vendors tab before you send the order.

Tab 5: Order Log

The order sheet gets overwritten every week. The log doesn't. Right after you send an order, add one row per item:

This is the tab that answers "Did we order cream on Monday?" and "What were we paying for chicken thighs in March?" It's also the tab that most often gets skipped on a busy day, which is a big reason spreadsheets stop working, as covered below.

Worked example: one order day, two vendors

This is an illustrative example. The items, targets, pack sizes, prices, and the vendor minimum are made up to show how the sheet works. They aren't typical figures for any distributor.

Setup:

• Two vendors: a meat purveyor and a broadline distributor.
• The broadline distributor has a $200 order minimum (illustrative).
• Targets are already set in count units on the Items tab.
• Nothing from a previous order is still waiting to be delivered.
• Every item is ordered in whole units only.

What's on the Items tab

What the Order Sheet calculates

The morning count comes in as 5 bags of chicken, 7 quarts of cream, 4 jugs of oil, and 26 sleeves of deli containers.

After delivery, the kitchen would have 17 bags of chicken (5 + 12), 19 quarts of cream (7 + 12), 6 jugs of oil, and 38 sleeves of deli containers (26 + 12).

Checking the vendor subtotals

Meat purveyor:  3 × $72.00                       = $216.00
Broadline:      $48.00 + (2 × $39.50) + $36.00   = $163.00
Broadline minimum:                                 $200.00
Short of minimum:  $200.00 − $163.00             = $37.00

The sheet did its job here. It caught that the broadline order is under the minimum before it went out. What to do about it is a judgment call:

• Pull forward something you'd order next time anyway, like a dry good that's close to its target.
• Check whether the minimum is a hard rule or a fee. If it's a small delivery charge, it may be cheaper to pay it than to stock extra product.
• Rethink the deli containers. The sheet suggests a full case of 12 sleeves to cover a 4-sleeve gap. Containers don't spoil, so that's usually fine. If storage is tight, you could type 0 in Final Order and pick them up next order instead.

None of those calls belongs in a formula. That's what column H is for.

Where purchasing spreadsheets usually break

Units drift

One week someone counts cream in quarts, the next week in cases, and the order comes out wildly wrong. Showing the count unit next to every entry box on the Count tab prevents most of this. When you change an item's pack size, change it on the Items tab only.

Blank counts get treated as zero

If someone skips an item, the order sheet may read the blank as zero on hand and suggest a full order. Use conditional formatting to highlight empty cells in the On Hand column. Then add a cell at the top with something like =COUNTBLANK(D3:D200) labeled "Items not counted," and don't build the order until it reads 0.

The count is from last week

If column D wasn't cleared after the last order, the sheet happily builds an order from old numbers. Add a warning to the Order Sheet that checks the count date:

=IF(Count!B1<>TODAY(), "Count is not from today", "")

Adjust the check if you count the evening before you order.

Someone types over a formula

A manager overwrites the suggested quantity in column F with "3" to fix one order. Next week that cell is still 3, whatever the count says. Keep overrides in column H, and protect the formula columns. Both Google Sheets and Excel let you lock ranges so only certain people can edit them.

Names don't match exactly

"Heavy cream" on the Items tab and "Cream, heavy" on the Count tab means the lookup can't find a match and returns an error. Build the Count and Order tabs from the Items list instead of typing names, and fix errors when they first show up rather than deleting the row.

Items bought from more than one vendor

A lookup returns the first match it finds, so an item listed twice can pull the wrong vendor or price. If you buy the same thing from two vendors, give each version its own row with a distinct name, for example "Heavy cream (broadline)" and "Heavy cream (dairy)." Then mark the one you don't normally use as inactive. To decide which version is the main one, compare the vendors by cost per usable unit.

Product that's already on order

The sheet only knows what's on the shelf. If Monday's order hasn't arrived by Wednesday's count, Wednesday's order will double up. If your delivery gaps make this common, add an "On order" column and change the Short formula to =MAX(0, D2 - C2 - [on order]). Otherwise, check the Order Log before sending.

Copies multiply

"Order Guide FINAL," "Order Guide FINAL v2," and the copy someone downloaded to their laptop all disagree within a month. Keep one shared file. Don't email attachments, and use the file's version history instead of saving copies.

The log stops getting filled in

The order log is the first thing to go on a slammed Friday, and it's the only record of what was ordered and at what price. If it's consistently skipped, the spreadsheet is no longer tracking purchasing. It's only calculating orders.

When a spreadsheet stops being enough

A well-built spreadsheet is a perfectly good purchasing system for a lot of restaurants. If one person orders, the item list is a few dozen products, and the log actually gets filled in, there may be no reason to change.

These are the signs it's starting to cost more time than it saves:

• More than one or two people order. Each person has their own habits, and fixes in one copy don't make it to the others.
• Formulas keep breaking. Every time someone sorts, inserts a row, or types over a cell, the person who built the sheet has to repair it.
• The order log is mostly empty. You can't answer "what did we order last Tuesday?" without digging through emails, texts, and vendor portals.
• Vendor information lives somewhere else. Cutoffs are on a sticky note, rep numbers are in someone's phone, and the Vendors tab is out of date.
• You've added a location. Keeping item lists, units, and vendors in sync across separate files gets messy fast.

What Proteus Kitchen does differently

Proteus Kitchen is built around the same structure as the five tabs above, without the formulas to maintain:

• Inventory items are stored with their vendor and order unit. If your team counts an item differently, you can set a separate count unit and how many count units are in one order unit, for example 6 packages per case.
• Vendors are saved with their ordering method, delivery days, one or more order deadlines (each a day and a time), contact information, and notes.
• Purchase orders are built by vendor. Proteus shows each item's current amount, its target, and a suggested quantity, and keeps track of what's been ordered.
• Purchasing history stays with the orders, so the log doesn't depend on someone remembering to fill it in.
• No POS integration is required.

If you already have a purchasing spreadsheet, you don't have to rebuild it by hand. Proteus offers Free Restaurant Setup: send your current inventory spreadsheet, order guide, or vendor list, and the team will help import your inventory and vendors.

Next step: build the Items tab first

You can set up the rest of the workbook in an afternoon, but only after the master list is right:

1. Pull your last month of invoices and list every item you actually bought.
2. For each item, fill in the vendor, order unit, count unit, and count units per order unit.
3. Add a starting target for your top 10 or 20 items.
4. Build the Vendors tab, then the Count and Order Sheet tabs that look up from Items.
5. Run two or three order days with it, check where the suggestions were off, and fix the Items tab rather than the order sheet.