Inventory Management Spreadsheet Template for Excel: Stock Levels, Reorder Points and Supplier Orders

Overview

A useful stock control spreadsheet should do more than list product names and quantities. It should provide a clear chain from product master data to purchasing action. A well-designed workbook can help you answer five operational questions:

• How many units are physically available?
• How many units are reserved, damaged or otherwise unavailable for sale?
• What quantity is already on purchase order?
• Which products are approaching their reorder point?
• Which supplier should receive the next order, and for how much?

For a free spreadsheet template for business use, create separate Excel sheets rather than putting every field into one long table. A practical workbook might contain these tabs:

1. Product Master: SKU, description, category, supplier, unit cost, selling unit, lead time, minimum order quantity and target stock level.
2. Stock Movements: dated receipts, sales or usage, returns, adjustments and transfers.
3. Purchase Orders: supplier orders, order dates, expected delivery dates, quantities and receipt status.
4. Stock Summary: current stock, available stock, stock on order, reorder point, suggested order quantity and stock value.
5. Stocktake: counted quantity, system quantity, variance, reason and approval.
6. Action List: low-stock products, overdue deliveries and unresolved variances.

Use Excel Tables for each data range. Tables expand as new rows are added and make formulas easier to read. Give each table a clear name, such as tblProducts, tblMovements and tblOrders. Keep one product identifier, such as a SKU, consistent across every sheet.

How to estimate stock levels and reorder points

The central calculation is the reorder point: the stock position at which a new order should normally be placed. A straightforward formula is:

Reorder point = average daily usage × supplier lead time in days + safety stock

Average daily usage should be based on a defined period, such as the last 30, 60 or 90 days. The right period depends on how frequently demand changes. A longer period may smooth unusual peaks, while a shorter period may reflect recent trading more closely. Record the chosen period in the assumptions section so that another person can understand the result.

In Excel, if the product’s average daily usage is in H2, lead time is in I2 and safety stock is in J2, the reorder point formula is:

=H2*I2+J2

To estimate the available stock position, separate physical stock from commitments:

Available stock = physical stock − reserved stock − damaged stock

Then calculate the projected position after incoming orders:

Projected stock = available stock + confirmed stock on order

A product can be flagged for review when available stock is at or below its reorder point. If available stock is in K2 and the reorder point is in L2, use:

=IF(K2<=L2,"Review order","OK")

Do not automatically treat every flagged line as an order. Check open purchase orders, minimum order quantities, discontinued items and known demand changes first. A purchase quantity can be estimated as:

Suggested order quantity = target stock level − projected stock

Where the result is negative, return zero. In Excel:

=MAX(0,TargetStock-ProjectedStock)

If a supplier requires a minimum order quantity, use a separate field and decide whether to round up to that quantity. For products sold in cases or packs, round to the appropriate ordering unit rather than ordering a fractional quantity.

Periodic versus perpetual stock reviews

Many small businesses use a hybrid approach: record receipts and issues daily, then conduct a formal review weekly and a full stocktake at an agreed interval.

Inputs and assumptions

Calculations are only as reliable as the inputs behind them. Add an assumptions block to the Product Master or a separate Settings sheet. Include:

• Demand basis: the period used to calculate average daily usage.
• Lead time: the expected number of calendar or working days from order placement to receipt. Use one convention consistently.
• Safety stock: the buffer held for demand variation or supplier uncertainty.
• Target stock level: the desired position after replenishment.
• Unit cost: the cost basis used for stock valuation, with a note explaining whether it is the latest cost, standard cost or another internal assumption.
• Ordering units: individual units, cases, pallets or another purchasing unit.

Safety stock can be entered as a practical quantity where data is limited. If demand and lead-time records are reliable, you can use a more analytical method based on variation in usage and supplier delivery performance. The important point is to document the method and review it when operating conditions change.

For stock value, a simple management estimate is:

Stock value = quantity × unit cost

Use the same valuation assumption across the workbook and label the output as an estimate where appropriate. Do not mix selling prices, VAT-inclusive prices and purchase costs in the same stock-value calculation. If VAT treatment affects your internal cost basis, document the choice and keep the calculation separate from sales reporting. Related planning can be connected to a small business budget and cash flow forecast template.

Adjustments for damaged, returned and reserved items

Use separate movement types rather than editing the current stock number manually. A movement log might include date, SKU, movement type, quantity, reference and note. Treat each movement consistently:

• Receipt: adds stock when goods are accepted into inventory.
• Sale or usage: reduces stock when goods leave inventory.
• Customer return: adds stock only when the item is inspected and available for resale.
• Supplier return: reduces stock when goods leave the business.
• Damage or write-off: reduces usable stock and records the reason.
• Reservation: does not reduce physical stock, but it reduces available stock.
• Stocktake adjustment: reconciles the system quantity to an approved physical count.

This approach preserves an audit trail and prevents unexplained changes. Protect formula columns and use data validation for movement types to reduce entry errors.

Worked examples

Assume a product has 120 units physically available. Ten units are reserved for confirmed customer orders and five are damaged. The available stock is therefore:

120 − 10 − 5 = 105 units

Average daily usage is 8 units, supplier lead time is 12 days and safety stock is 30 units. The reorder point is:

8 × 12 + 30 = 126 units

Because available stock of 105 is below the reorder point of 126, the line should appear on the action list. Suppose there are already 40 units on a confirmed purchase order. Projected stock is:

105 + 40 = 145 units

If the target stock level is 180 units, the suggested order quantity is:

180 − 145 = 35 units

The buyer should still check the supplier’s minimum order quantity, ordering pack and expected demand before placing the order. If the minimum order is 50 units, the workbook can display 50 as the proposed quantity, with an explanation that the amount has been rounded to the supplier constraint.

For a stocktake, suppose the system shows 105 available units but the approved physical count is 101. The variance is:

101 − 105 = −4 units

Record the variance with a reason, such as damage not recorded, picking error or counting mistake. After approval, enter an adjustment movement rather than overwriting the original system figure.

When to recalculate

Use the workbook as a working process, not a one-off download. Import or enter new sales, usage, receipts and returns whenever the business process allows. At a minimum, review the action list weekly for fast-moving or important items and monthly for slower-moving ranges.

Recalculate the model when:

• supplier lead times change or deliveries become less predictable;
• sales volume, seasonality or customer mix changes;
• prices or unit costs change;
• minimum order quantities, case sizes or suppliers change;
• new products are introduced or old products are discontinued;
• a stocktake identifies repeated variances;
• reserved, damaged or returned stock is not being recorded consistently.

Before each purchasing cycle, complete this short checklist:

1. Import the latest sales, usage, receipt and return data.
2. Reconcile open purchase orders against supplier confirmations and deliveries.
3. Update physical counts and investigate material variances.
4. Review lead times, safety stock and target levels for products with changed demand.
5. Filter the Action List for low stock, overdue orders and missing supplier data.
6. Prepare the next purchase-order shortlist and record the decision or reason for deferral.

Review the workbook structure and example categories at least annually, or sooner when the inventory process changes. Check that formulas work in the Excel version used by the team, that table references still expand correctly and that conditional formatting highlights the intended risks. For connected reporting, a sales KPI dashboard template in Excel can provide demand context, while a monthly variance analysis template can help explain differences between expected and actual stock-related costs.

The result is a stock control spreadsheet that supports a clear decision: what needs attention now, what is already covered by incoming stock and what should be ordered next.