Inventory Management in Excel: A Stock Ledger Example

An Excel inventory ledger should record each receipt, issue and return with its item, unit, location and document reference, then calculate and reconcile the resulting balance. Editing only a current-stock cell removes the movement history needed to explain a difference.

Begin with a small, defined scope: one location and a controlled set of items, for example. Agree who enters movements, when entries are due and who approves corrections. Excel supports collaboration and automation in suitable setups; the operating rules determine whether the workbook stays reliable.

Define the ledger fields

For more than one item or location, add item code, unit, location and any required lot/status fields. A single running-balance column across mixed items does not calculate stock for each item. Use a controlled item/location ledger or a correctly filtered summary of the movement table.

Check the calculation with one item

In this illustrative example, one item has 100 pieces at the start. The team receives 60, issues 90 to a job and accepts 10 unused pieces back into the same usable-stock location. The closing balance is 100 + 60 − 90 + 10 = 80 pieces.

In Excel, enter =F2+D3-E3 in F3 and copy the relative formula down for this single-item example. Protect formula cells from accidental edits and check the first and last result. Sorting only part of a range can separate a quantity from its document; keep the record together as a table.

Keep held stock and transfers understandable

A return is not automatically usable. Record inspection or disposition where required before adding it to an available-stock view. A transfer between locations needs a paired source and destination reference; adding it to receipts without removing it from the source overstates total stock.

ERPNext's stock-entry documentation provides examples of different material-movement purposes. Use the process distinction even if your current records remain in Excel.

Reconcile against a physical count

Define the count time and how movements during the count are handled. Compare the counted item, unit, location and status with the ledger at that same boundary. Investigate missed receipts, late issues, returns and unit errors before approving an adjustment.

Keep the original discrepancy, reason, approver and adjustment reference. ERPNext's stock-reconciliation documentation illustrates the role of a controlled reconciliation record. A balancing entry should not hide the cause.

Decide when the workbook needs a stronger process

Review repeated missed movements, conflicting versions, unsupported permissions or difficulty connecting stock to orders and quality. These are requirements to resolve, not proof that every spreadsheet must be replaced. Include the time spent checking and correcting the records when evaluating options.

Use the inventory-accuracy checklist to define the discrepancy process and simple inventory software guide when considering a connected system. Bring this ledger and a real exception to an Optiwise inventory demo.

Questions from manufacturing teams

What is the basic inventory formula in Excel?

For a defined item, unit and location, closing balance equals opening balance plus receipts minus issues, including approved returns or corrections in the appropriate direction. Keep the underlying movement records.

Can I use one running balance for every item?

A single mixed-item running balance is not stock for each item. Separate the item/location scope or use summaries filtered by the correct item, unit and location.

Does Excel prevent multiple people from managing inventory?

Excel can support shared working and other controls depending on the setup. Define entry, permissions, versioning and reconciliation responsibilities, and test whether they meet the actual operating needs.