Inventory & Stock Control
Made for small shops and makers holding stock.
A stock list with cost, selling price and quantity on hand, a log of stock coming in and going out, and a summary showing what is running low and what the stock is worth. The summary carries a chart that redraws itself as you type.
Of the 29 columns, you fill in 18 and the other 11 work themselves out, and all 13 figures on the summary do too. There is room for 2,300 rows of your own.
£4.99one payment, yours to keep
- Opens in Excel, Google Sheets and Numbers
- Delivered straight away
- No macros, nothing to install
- Change it free before you buy
Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.
Already have a spreadsheet of your own? Check it for mistakes, free.
- A new row is typed into the Products tab, one cell at a time, in the white cells a person fills in.
- The shaded cells in that row fill themselves in.
- The figures on the Summary tab change to include the new row.
- The chart, Stock value by category, redraws with it.
Everything inside, before you pay
This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 2,300 rows. Open any tab to see its columns.
Products
18 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| SKU | Your own code for the product. Any short label works.Type any text. | |
| Product | Keep each name unique. Stock movements are matched to a product by this name.Type any text. | |
| Category | Pick one: Food, Drink, Household, Toiletries, Stationery, Gifts or Other. | |
| Supplier | Type any text. | |
| Unit | Pick one: Each, Pack, Box, Kg or Litre. | |
| Cost Price | Type an amount in £. | |
| Sell Price | Type an amount in £. | |
| Margin | Profit as a share of the selling price.Fills itself in. | Worked out from this row's Cost Price and Sell Price. The formulaIF(OR(ISBLANK([cost_price]),ISBLANK([sell_price])),"",IFERROR(([sell_price]-[cost_price])/[sell_price],0)) |
| Opening Count | How many you had when you started using this file.Type a number. | |
| In | Fills itself in. | The total of Quantity on Stock Movements where Product matches this row's Product and In Or Out is “In”. The formulaIF(ISBLANK([product_name]),"",SUMIFS([Stock Movements!quantity],[Stock Movements!product],[product_name],[Stock Movements!movement_type],"In")) |
| Out | Fills itself in. | The total of Quantity on Stock Movements where Product matches this row's Product and In Or Out is “Out”. The formulaIF(ISBLANK([product_name]),"",SUMIFS([Stock Movements!quantity],[Stock Movements!product],[product_name],[Stock Movements!movement_type],"Out")) |
| In Stock Now | Fills itself in. | Worked out from this row's Product and Opening Count, using Quantity, Product and In Or Out on Stock Movements. The formulaIF(ISBLANK([product_name]),"",[opening_stock]+SUMIFS([Stock Movements!quantity],[Stock Movements!product],[product_name],[Stock Movements!movement_type],"In")-SUMIFS([Stock Movements!quantity],[Stock Movements!product],[product_name],[Stock Movements!movement_type],"Out")) |
| Reorder At | When stock falls to this number or below, the product is flagged for reordering.Type a number. | |
| Order Up To | The level you like to restock to.Type a number. | |
| Units To Order | Fills itself in. | Worked out from this row's Product, In Stock Now, Reorder At and Order Up To. The formulaIF(ISBLANK([product_name]),"",IF([on_hand]<=[reorder_level],MAX(0,[target_stock]-[on_hand]),0)) |
| Value At Cost | Fills itself in. | Worked out from this row's Product, In Stock Now and Cost Price. The formulaIF(ISBLANK([product_name]),"",ROUND([on_hand]*[cost_price],2)) |
| Value At Retail | Fills itself in. | Worked out from this row's Product, In Stock Now and Sell Price. The formulaIF(ISBLANK([product_name]),"",ROUND([on_hand]*[sell_price],2)) |
| Status | Fills itself in. | Worked out from this row's Product, In Stock Now and Reorder At. The formulaIF(ISBLANK([product_name]),"",IF([on_hand]<=0,"Out of stock",IF([on_hand]<=[reorder_level],"Reorder now","In stock"))) |
Stock Movements
11 columns
| Column | What goes in it | Worked out as |
|---|---|---|
| Date | Type a date. | |
| Product | Pick one of the Product entries on Products. | |
| In Or Out | Pick one: In or Out. | |
| Reason | Pick one: Purchase, Sale, Customer return, Return to supplier, Wastage or Stocktake adjustment. | |
| Quantity | Type a number. | |
| Unit Cost | What each unit cost you on this movement.Type an amount in £. | |
| Line Value | Fills itself in. | The Quantity times the Unit Cost. The formulaIF(ISBLANK([quantity]),"",ROUND([quantity]*[unit_cost],2)) |
| Change | Positive for stock coming in, negative for stock going out.Fills itself in. | Worked out from this row's Quantity and In Or Out. The formulaIF(ISBLANK([quantity]),"",IF([movement_type]="Out",-[quantity],[quantity])) |
| Month | Fills itself in. | Worked out from this row's Date. The formulaIF(ISBLANK([movement_date]),"",TEXT([movement_date],"YYYY-MM")) |
| Reference | Delivery note, order number or till receipt.Type any text. | |
| Notes | Type any text. |
Summary
13 figures
| Figure | Worked out as |
|---|---|
| Products tracked | The number of rows on Products with Product filled in. The formulaCOUNTA([Products!product_name]) |
| Units in stock | The total of In Stock Now on Products. The formulaSUM([Products!on_hand]) |
| Stock value at cost | What you paid for everything currently on the shelves. The total of Value At Cost on Products. The formulaSUM([Products!stock_value]) |
| Stock value at retail | The total of Value At Retail on Products. The formulaSUM([Products!retail_value]) |
| Profit if all sold | Worked out from Value At Retail and Value At Cost on Products. The formulaSUM([Products!retail_value])-SUM([Products!stock_value]) |
| Average margin | The average of Margin on Products. The formulaIFERROR(AVERAGE([Products!margin_pct]),0) |
| Lines to reorder | Products at or below their reorder level. The number of rows on Products where Status is “Reorder now”. The formulaCOUNTIF([Products!status],"Reorder now") |
| Lines out of stock | The number of rows on Products where Status is “Out of stock”. The formulaCOUNTIF([Products!status],"Out of stock") |
| Units to order | The total of Units To Order on Products. The formulaSUM([Products!order_qty]) |
| Units received this month | Worked out from Quantity, Month and In Or Out on Stock Movements. The formulaSUMIFS([Stock Movements!quantity],[Stock Movements!month_key],TEXT(TODAY(),"YYYY-MM"),[Stock Movements!movement_type],"In") |
| Units gone out this month | Worked out from Quantity, Month and In Or Out on Stock Movements. The formulaSUMIFS([Stock Movements!quantity],[Stock Movements!month_key],TEXT(TODAY(),"YYYY-MM"),[Stock Movements!movement_type],"Out") |
| Cost of stock out this month | Worked out from Line Value, Month and In Or Out on Stock Movements. The formulaSUMIFS([Stock Movements!line_value],[Stock Movements!month_key],TEXT(TODAY(),"YYYY-MM"),[Stock Movements!movement_type],"Out") |
| Movements recorded | The number of rows on Stock Movements with Date filled in. The formulaCOUNT([Stock Movements!movement_date]) |
More in Business
-
Freelance Invoice & VAT Tracker
Tracks every invoice sent, what is outstanding, what is overdue, and how much VAT is due this quarter, with a reusable client list.
22 figures work themselves out£4.99
-
Small Business Bookkeeping
A simple set of books for a one-person business: record every payment received and every business cost, tag each one with a category, and see totals by category, totals by month and profit so far this year ready for the tax return.
26 figures work themselves out£4.99
-
Job Quotes & Margin Tracker
A workbook for a tradesman: record every quote with its customer, labour hours and rate, materials and quoted price; see the margin made on each job and the win rate on quotes.
28 figures work themselves out£4.99
-
Etsy Seller Profit Tracker
Record every Etsy sale with fees, postage and materials, and see what each order and each item actually earns.
35 figures work themselves out£4.99