Inventory management in Excel: a small-business stock ledger

An attractive spreadsheet can show the wrong stock figure without looking broken. A receipt sits below the formula range, an order is deducted twice, or someone pastes an unfamiliar product code. The answer is a small, understandable ledger whose inputs you can check. This article gives you a design for an online shop selling countable physical goods. It is an operational worksheet design, with chosen exercise quantities, rather than a claim that a spreadsheet meets every accounting or integration requirement.

What belongs in the three tables? #

The item table defines what a unit means. Give each physical variant a stable SKU, a plain description and a base unit such as each. A blue candle and a green candle need separate identities if you buy and pick them separately. Add a location identifier to each summary row when goods are held in more than one place. Keep display names separate from the matching keys. Renaming a product for marketing should not disconnect its earlier movements from its current balance.

The movement table records physical changes: opening count, receipt, completed dispatch, transfer or approved correction. The reservation table records units promised to orders that have not yet been physically dispatched. You also need a blocked quantity per SKU and location; for a small worksheet, make this an explicit controlled field with a note and review date. If blocked items move frequently, give their movements a separately defined state rather than relying on comments scattered through cells.

Give each posting a unique movement identifier. An order number alone may repeat across products and shipments. Keep a reference to the underlying receipt, dispatch or correction, and decide who can approve corrections.

How do you calculate a balance that includes new rows? #

Convert your movement range into an Excel Table and name it Moves. Create a summary table with SKU and Location columns. For a summary row, the proposed physical-balance formula is =SUMIFS(Moves[Qty],Moves[SKU],[@SKU],Moves[Location],[@Location]). It adds signed quantities only when both the SKU and location match. The opening count must be a movement row too; otherwise add your opening balance through a separately documented method. Do not use both approaches together.

Microsoft documents SUMIFS as adding values that meet multiple criteria. Its sum range and criteria ranges must have the same number of rows and columns. Keep those ranges aligned when adapting the formula. The example uses English function names and comma separators; your Excel language or regional settings may use another function name or separator. Select the relevant table columns while building the formula if manually typing it produces a syntax error.

Microsoft also explains structured references: references using table and column names adjust when you add or remove table data, and a calculated column can copy its formula down the table. That is why the design uses Moves[Qty] rather than an arbitrary last row. Add a receipt after the existing final row and check that it genuinely belongs to the table. Then confirm the balance changes for the matching item and remains unchanged for another item.

Why should reservations stay outside physical movements? #

A reservation promises a unit to an order. It does not necessarily remove the unit from your shelf. Choose one physical event for your dispatch posting and write that definition in the workbook instructions. When the order is dispatched, add the negative physical movement and release the corresponding reservation in the same controlled workflow. If you keep the reservation active afterwards, availability is reduced twice. If you release it before recording the departure, availability may briefly become too high.

The summary is physical balance minus blocked quantity minus active reservations. A damaged item is still in the physical balance while you hold it; subtracting it as a disposal and also keeping it blocked would duplicate the restriction. Once you physically dispose of it, record the appropriate negative movement and remove the blocked amount. Similarly, a return can be physically received while still awaiting inspection. Do not make the returned unit available merely because money has been refunded.

For orders with several fulfilment locations, allocate the reservation to the place that will supply the goods. A combined company total can look sufficient while the selected location has nothing available. Keep any unassigned orders visible for a decision. If you hold goods with an AirPost sender, treat that stock as a separate location and agree how movements are confirmed. The AirPost fulfilment page explains the physical arrangement; this Excel design does not promise a spreadsheet connection to the platform.

Which input controls actually stop common mistakes? #

Use controlled lists for SKU, location and movement type. Restrict quantity inputs to the unit your ledger uses. If every product is counted individually, a fractional dispatch should require investigation. Keep identifiers consistently as text when their appearance matters, and compare imported identifiers with the item table before posting. A supplier description is not a stable match key. Review blank references and unexpected signs: a receipt normally adds stock, while a completed dispatch removes it.

Microsoft’s data-validation instructions distinguish error styles. Stop requires the user to correct an invalid direct entry before proceeding, whereas a warning can allow continuation. Choose the behaviour deliberately. Give the user an error message that identifies the problem, such as choose an existing location. A generic invalid entry message creates a support task without helping the person fix the row.

There is a limit you must test: Microsoft says validation messages can be bypassed by copied or filled data. Formula results and macros can also introduce invalid values without those direct-entry messages. Therefore inspect imported rows before admitting them to the live movement table. Paste an unknown SKU in a test copy and verify that your review catches it. A dropdown that works during typing is not enough evidence that an import is safe.

How do you correct an entry without hiding what happened? #

For this workbook design, treat posted rows as a history you preserve. If a receipt recorded nine units but the accepted count was eight, add a linked correction of minus one rather than silently rewriting the nine. Include the original movement identifier, reason and approval reference. That gives you an operational explanation of the current balance. It does not turn an editable workbook into an immutable accounting system; if you need formal audit controls, assess those requirements separately.

Handle transfers with two linked rows: a negative quantity at the source and the same positive quantity at the destination, once the relevant movement is confirmed. In a same-time transfer exercise, the company total stays unchanged while both location totals change. For goods travelling between places, define an in-transit location and the actual confirmation points. Otherwise both locations may show the units or neither may show them. Keep the transfer reference visible until the receiving side has confirmed its count.

What should your workbook pass before you rely on it? #

1. Enter the exercise opening count, receipt and dispatch; verify a physical balance of 23.
2. Record 2 blocked units and 6 active reservations; verify availability of 15.
3. Append a new receipt row and check that the table reference includes it.
4. Post a dispatch and release its reservation; verify neither omission nor duplicate deduction.
5. Paste an unknown SKU and a duplicate MovementID into a test copy; check the import review catches both.
6. Add a linked correction and verify another person can explain the balance from the recorded rows.

Use one agreed live workbook and test whether a backup owner can process a receipt without your help. If concurrent editing or automatic imports exceed your controls, assess an inventory system against the same cases. The Shopify inventory-software comparison provides a separate selection framework.

How do fulfilment costs fit into an inventory sheet? #

Keep quantity and shipment costs in separate fields. For an exercise with ten small customer parcels, the AirPost platform fee of €0.85 plus the recommended small-parcel sender fee of €2.25 gives €3.10 per parcel, or €31.00 across ten parcels. These euro-denominated components were checked against the constants and pricing structure on 14 October 2026. Actual sender fees are agreed with the sender; labels and materials are additional.

Ten parcels do not imply ten product units. Record dispatched order-line quantities and track replenishment transport separately. Before allocating a first batch, read how much stock to send a sender. Your worksheet should explain both the units held or promised and the shipments producing your costs.