Workbook
Free Excel Inventory Template
This excel inventory template is a shelf count, not a warehouse system. Type the item, how many you can see, the point where you reorder, and what one unit cost you. Stock value and the Reorder flag come from those four numbers.
Harbor Goods is the shop in the file: twenty ordinary products, priced in dollars. Notebooks are already short. Copy paper is not. Twenty rows is enough to see the rule before you type your own stock. I still write the count on paper if the shop is busy, then type it once.
Download the inventory template
Download the Excel file. If you work in Google Sheets, upload that same workbook to Drive and open it there. Leave the green cells alone.
I leave the sample rows in until I have typed one real line next to them. That way I can see whether my hours or prices sit in the same neighborhood as the example. Then I delete the leftover names. If you share the file, strip the sample clients first.
After you wipe the sample, save a clean copy under a name that includes the month or the client. I have lost a week of hours by typing over last week’s file. The formulas do not care about the file name. You will.
What to type in each column
Item is the name you use in the shop. Ballpoint pens and notebooks A5 are enough. Use the same name you would say out loud to a clerk.
SKU is a short code such as PEN-10 or NB-A5. It keeps two similar items from getting mixed. You invent the code. The sheet does not look one up.
On hand is the count you can see today. Count the shelf, then type the number. Do not type the number you wish you had.
Reorder at is the count that means you should buy more. If notebooks should never fall under 15, type 15. When on hand is 12, the status cell says Reorder.
Unit cost is what you paid for one unit, in dollars, or your own currency. This is not the price on the shelf tag. The retail price has its own column so you do not mix cost and selling price.
Retail price is what the customer pays for one unit. The sheet does not calculate margin. It is here so the row is a complete product card. You can compare it with unit cost by eye.
Stock value is green. It is on hand times unit cost, rounded to the cent. Twelve notebooks at $1.80 cost $21.60 of stock. The total row adds every product.
Status is green. If on hand is less than or equal to the reorder point, the cell says Reorder. Otherwise it says In stock. Do not type these words yourself. Change the counts and the word changes.
Example: a shop with 20 products
Harbor Goods counted the shop on a Monday. Twenty products are in the file. The sheet prices stock at cost, not at the retail tag. Dollars are the sample currency, or your own currency if you edit the header.
| Item | On hand | Reorder at | Unit cost | Stock value | Status |
|---|---|---|---|---|---|
| Ballpoint pens | 48 | 24 | $0.40 | $19.20 | In stock |
| Notebooks A5 | 12 | 15 | $1.80 | $21.60 | Reorder |
| Painter tape | 30 | 10 | $2.10 | $63.00 | In stock |
| LED bulbs 9W | 8 | 12 | $1.25 | $10.00 | Reorder |
| Dish soap | 20 | 8 | $1.10 | $22.00 | In stock |
| AA batteries 4pk | 6 | 10 | $2.40 | $14.40 | Reorder |
| Copy paper ream | 18 | 6 | $4.20 | $75.60 | In stock |
| Trash bags 20pk | 4 | 6 | $3.00 | $12.00 | Reorder |
| Wood screws box | 15 | 5 | $2.80 | $42.00 | In stock |
| Paint roller | 9 | 4 | $3.50 | $31.50 | In stock |
| Latex gloves box | 3 | 5 | $4.00 | $12.00 | Reorder |
| Coffee filters | 11 | 4 | $1.60 | $17.60 | In stock |
| Hand towels | 7 | 6 | $2.20 | $15.40 | In stock |
| Glass cleaner | 2 | 4 | $1.90 | $3.80 | Reorder |
| Extension cord | 5 | 3 | $6.50 | $32.50 | In stock |
| Picture hooks | 22 | 8 | $1.15 | $25.30 | In stock |
| Envelopes box | 10 | 4 | $2.00 | $20.00 | In stock |
| Packing tape | 14 | 6 | $1.70 | $23.80 | In stock |
| Mini flashlight | 1 | 3 | $4.80 | $4.80 | Reorder |
| Plant pot 6in | 16 | 6 | $2.30 | $36.80 | In stock |
Notebooks A5 show the rule clearly. Twelve are on hand and the reorder point is 15, so the status is Reorder. The stock value is still 12 times $1.80, which is $21.60. A low item still has a value. The status only tells you to buy more.
Copy paper is the other kind of row. Eighteen reams are on hand and the reorder point is 6, so the status is In stock. The value is 18 times $4.20, which is $75.60. That is the largest single line in this shop.
The total stock at cost is $503.30. The status column flags 7 products for reorder: notebooks, LED bulbs, AA batteries, trash bags, latex gloves, glass cleaner, and the mini flashlight. Order those before you worry about the rows that already say In stock.
Count the shelf before you open the file, and write the numbers on paper first if the shop is busy. Then type them once. If two people count the same aisle, keep one number. The sheet cannot tell which count is newer.
Excel and Google Sheets
Download excel-inventory-template.xlsx and open it in Excel, or drop that same file into Google Drive and open it with Google Sheets. I do not keep a second copy for Sheets. One workbook is enough.
Stock value multiplies the yellow on-hand count by the yellow unit cost. Status compares on hand with the reorder point. The file name is excel-inventory-template.xlsx. After you upload it to Google Sheets, edit only the yellow cells and watch the green status change when a count crosses the reorder point.
The math uses IF, ROUND, SUM, SUMIF, and COUNTIF. Leave the file as xlsx. A CSV export keeps the numbers and throws the formulas away. Input cells are yellow. Results are green. Type over a green cell and the formula is gone — Undo, or grab the file again.
The sample money is in dollars. If you work in another currency, change the note at the top of the sheet. Adding 2.5 × 95 does not care what you call the unit.
Common inventory mistakes
- Typing the retail price into the unit cost cell. Stock value would then be too high.
- Typing Reorder into the green status cell. Type a lower on-hand count instead.
- Using a reorder point of 0 and then wondering why nothing flags. A point of 0 only flags an empty shelf.
- Counting items you already sold but have not removed from the shelf number.
- Leaving the twenty sample products in the file after you start your real count.
Related templates
If the job in front of you is a little different, these two are usually the next stop.
Workbooks for a small shop, a freelancer, or a contractor. Sample amounts are in dollars.