sheetsmith

Templates › Business and freelance

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.

Tabs
3
Columns
29
Fill themselves in
24

£7one 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.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

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.

Products

One row per product you sell. Quantity on hand is worked out from the opening count plus everything logged on the Stock Movements tab. Room for 300 rows.

ColumnTypeWorked out as
SKUtext
Producttext
Categorychoice (list)
Suppliertext
Unitchoice (list)
Cost Pricecurrency
Sell Pricecurrency
MarginpercentIF(OR(ISBLANK([cost_price]),ISBLANK([sell_price])),"",IFERROR(([sell_price]-[cost_price])/[sell_price],0))
Opening Countnumber
InnumberIF(ISBLANK([product_name]),"",SUMIFS([Stock Movements!quantity],[Stock Movements!product],[product_name],[Stock Movements!movement_type],"In"))
OutnumberIF(ISBLANK([product_name]),"",SUMIFS([Stock Movements!quantity],[Stock Movements!product],[product_name],[Stock Movements!movement_type],"Out"))
In Stock NownumberIF(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 Atnumber
Order Up Tonumber
Units To OrdernumberIF(ISBLANK([product_name]),"",IF([on_hand]<=[reorder_level],MAX(0,[target_stock]-[on_hand]),0))
Value At CostcurrencyIF(ISBLANK([product_name]),"",ROUND([on_hand]*[cost_price],2))
Value At RetailcurrencyIF(ISBLANK([product_name]),"",ROUND([on_hand]*[sell_price],2))
StatustextIF(ISBLANK([product_name]),"",IF([on_hand]<=0,"Out of stock",IF([on_hand]<=[reorder_level],"Reorder now","In stock")))

Stock Movements

Every delivery in and every sale, breakage or write off going out. Pick the product from the dropdown so it counts against the right line. Room for 2,000 rows.

ColumnTypeWorked out as
Datedate
Productchoice (from Products › product_name)
In Or Outchoice (list)
Reasonchoice (list)
Quantitynumber
Unit Costcurrency
Line ValuecurrencyIF(ISBLANK([quantity]),"",ROUND([quantity]*[unit_cost],2))
ChangenumberIF(ISBLANK([quantity]),"",IF([movement_type]="Out",-[quantity],[quantity]))
MonthtextIF(ISBLANK([movement_date]),"",TEXT([movement_date],"YYYY-MM"))
Referencetext
Notestext

Summary

What the stock is worth and what needs ordering, updating as you fill the other two tabs in.

MeasureWorked out as
Products trackedCOUNTA([Products!product_name])
Units in stockSUM([Products!on_hand])
Stock value at costSUM([Products!stock_value])
Stock value at retailSUM([Products!retail_value])
Profit if all soldSUM([Products!retail_value])-SUM([Products!stock_value])
Average marginIFERROR(AVERAGE([Products!margin_pct]),0)
Lines to reorderCOUNTIF([Products!status],"Reorder now")
Lines out of stockCOUNTIF([Products!status],"Out of stock")
Units to orderSUM([Products!order_qty])
Units received this monthSUMIFS([Stock Movements!quantity],[Stock Movements!month_key],TEXT(TODAY(),"YYYY-MM"),[Stock Movements!movement_type],"In")
Units gone out this monthSUMIFS([Stock Movements!quantity],[Stock Movements!month_key],TEXT(TODAY(),"YYYY-MM"),[Stock Movements!movement_type],"Out")
Cost of stock out this monthSUMIFS([Stock Movements!line_value],[Stock Movements!month_key],TEXT(TODAY(),"YYYY-MM"),[Stock Movements!movement_type],"Out")
Movements recordedCOUNT([Stock Movements!movement_date])