Inventory and stock tracker with reorder alerts

Stock is cash sitting on a shelf

For any business that sells goods, stock is the largest asset and the easiest to lose track of. Too little and customers walk away. Too much and your cash is tied up in things that do not sell, that expire or that go out of fashion. Between the two lie breakage, theft and plain miscounting. A stock tracker answers the questions that matter every day: what do I have, what is selling, what must I reorder, and what is it all worth? This workbook answers them without special software.

Two sheets, one clear picture

The Items sheet is your product list. Each item has a code, a name, a unit, a cost price, a selling price, the opening quantity and a reorder level. The workbook works out the rest from your records: the quantity received, the quantity sold, adjustments, the quantity on hand, a status, the value of the stock at cost and the margin. The Movements sheet is the log. Every time stock arrives, is sold or is adjusted, you add a line with the date, the item code, the type and the quantity.

• Received: deliveries from suppliers and returns from customers.
• Sold: sales to customers.
• Adjustment: breakages, losses, expiry and stock-take corrections. Use a negative number for a loss and a positive one for a gain.

The status and the reorder level

Each item's status is OK, REORDER or OUT OF STOCK. REORDER appears when the quantity on hand falls to the reorder level you set, and OUT OF STOCK when it reaches zero. A good reorder level covers the sales you expect during the time it takes the supplier to deliver, plus a small buffer. If an item sells 20 a week and takes two weeks to arrive, a reorder level of about 50 is sensible. The two flags are highlighted in colour, and a count at the bottom of the Items sheet shows how many items need attention.

Setting it up

1. List your items on the Items sheet with their codes, names, cost and selling prices.
2. Count your stock and enter it as the opening quantity.
3. Set a reorder level for each item, starting with your best sellers.
4. Record each delivery, sale and adjustment on the Movements sheet, using the item code from the drop-down list.
5. Check the Status column daily or weekly, and place orders for anything marked REORDER.

Do a stock-take

No record is perfect, so count your stock regularly: weekly for fast movers, monthly or quarterly for the rest. Compare the physical count with the quantity on hand. Where they differ, find out why, then record an adjustment so the tracker matches reality. A pattern of losses on certain items points to theft, damage or a recording problem. Counting also finds slow-moving stock, which is your next job: discount it, bundle it, or stop buying it.

What your stock is worth

The stock value column multiplies the quantity on hand by the cost price. That figure appears in your accounts, and it matters for tax, because your closing stock affects your profit. The cost of goods sold in a year is the opening stock plus purchases minus the closing stock, which is why an honest count at your year-end is not optional. Our guide to the 30 June deadline and the profit and loss statement in this collection show where it fits. Where you are required to issue and receive electronic tax invoices, your purchases and sales in the tracker should match them: see our guide to eTIMS e-invoicing.

Good habits

• Give every item a unique code and stick to it.
• Record movements the same day, not at the end of the month.
• Store stock tidily, with the oldest in front, and watch expiry dates.
• Review slow movers every quarter.
• Keep supplier invoices and delivery notes with the movement record.

This workbook is a stock record and not accounting or tax advice. The example items are illustrations. For stock valuation methods and the tax treatment of stock, speak to your accountant or see the Kenya Revenue Authority.

Related guides, calculators and templates

• Guide: How to build a 12-month cash flow forecast for a small business
• Guide: How to price your products and services: markup, margin and break-even
• Guide: Turnover tax for small businesses in Kenya: rate, limits and when to use it
• Another template: 12-month cash flow forecast
• Another template: Break-even and pricing calculator
• Another template: Budget versus actual tracker

Need something different? Request a custom template, browse all guides or see every Excel template.