Shops · cafés · pubs · online sellers · trades

Stock control in Excel – reorders, stock take and closing stock value

Stock on hand is only right if every purchase, sale, return and breakage is recorded. The workbook builds each product's balance from its movements, tells you what to reorder and values the closing stock at the year end the way your accountant needs it.

  • 200 products and 2,000 movements
  • 20 suppliers with lead times
  • Blind count sheet and recounts
  • FIFO or weighted average, written down to NRV
Add to basket
Stock Control & Stock Take – Dashboard
10sheets
200products
0macros

Reorder before you run out

REORDER lists products below their reorder level or likely to run out within the supplier's lead time, with a suggested quantity based on recent sales and stock already on order. PURCHASE ORDER fills in an A4 order for one supplier from those suggestions.

Inside the workbook

The year-end stock take

  1. Print STOCK TAKE in location order without book quantities
  2. Count every location and enter the figures
  3. Recount where the variance is large
  4. Check the list of products not counted yet

VALUATION then values closing stock at the count date at FIFO or weighted average cost and writes it down where the net realisable value is lower.

Slow stock and dead stock

ANALYSIS ranks products with an ABC analysis and shows stock turnover and days of stock by category. Slow and dead stock is listed, so you can decide what to discount before it ties up more cash.

Workbook facts

  • START
  • SETTINGS
  • PRODUCTS
  • MOVEMENTS
  • STOCK TAKE
  • VALUATION
  • REORDER
  • PURCHASE ORDER
  • ANALYSIS
  • DASHBOARD
File format
.xlsx
Tabs
10
Capacity
200 products · 2,000 movements · 300 count lines · 20 suppliers
Requires
Excel 2016+ / Microsoft 365
Macros
None

Questions people ask

Does it work for a café or a pub?
Yes. Products can be counted in units, bottles, kilos or any unit you choose, and wastage is a movement type of its own.
Where does closing stock go on the Form 11?
In the extracts from accounts on the Form 11. Your accountant will use the valuation from the count date.
Do I have to enter every single sale?
No. Sales can be entered one by one or as a till total for a period, with trade prices where you use them.

Stock figures you can trust at the year end

Add the stock control workbook to your basket and continue to payment.

Add to basket