How to Build an FPO Stock Register in Excel

A useful stock register records movements, not just a manually typed balance. Every receipt, issue, return or approved correction should explain why the quantity changed. Begin with one store and a few products, verify the method, then expand it.

Set up consistent columns

Create columns A to H as Date, Item code, Batch code, Unit, In quantity, Out quantity, Reference and Reason. Keep one movement per row. Use a separate item list that defines product descriptions and the unit used for each item. Do not mix bags and kilograms in the same quantity column without an approved conversion method.

Record opening stock as a clearly labelled opening entry supported by a physical count. Supplier deliveries need receipt references; dispatches need issue or delivery references. Returns and damaged stock should have distinct reasons so they remain visible.

Make data entry less error-prone

Microsoft explains how to create Excel tables, which can make a growing ledger easier to filter and manage. Data validation can restrict entries to a list or permitted numeric values. Use it for item codes, units and movement reasons. Menu wording varies across Excel versions.

Enter genuine dates and non-negative quantities. Put a movement in either In or Out, not both. Check exceptions before closing the day. Lock calculation cells against accidental editing, while remembering that worksheet protection is not a substitute for secure file access.

Calculate an item-and-batch balance

If the item code to summarise is in K2 and its batch code is in L2, an illustrative formula is:

=SUMIFS(E:E,B:B,K2,C:C,L2)-SUMIFS(F:F,B:B,K2,C:C,L2)

The SUMIFS function adds values matching the stated criteria. This example assumes the ledger covers one store and each item uses one consistent unit. Add location criteria when managing several stores. Some installations use semicolons instead of commas in formulas.

For example, 600 kg opening stock plus 400 kg received minus 250 kg dispatched gives an expected 750 kg. A negative balance should trigger investigation rather than being silently changed to zero.

Reconcile with the store

Count stock at an agreed interval and compare it with the calculated balance. Investigate missing entries, wrong units, weighing differences and unrecorded damage. Do not overwrite history to make the figures match. Record an authorised adjustment with its reason, date and reference.

Daily and weekly controls

• Enter movements promptly from supporting records.
• Check duplicate references and incomplete rows.
• Confirm the file has one controlled working version.
• Back it up in a protected location.
• Review differences with the storekeeper and approver.

Frequently asked questions

Does quantity balance show inventory value? No. Valuation needs a separate, consistently applied accounting method and review.

Can several people edit offline copies? That creates conflicting versions. Nominate one entry owner or use a controlled shared system with appropriate access and change history.

Prepared with AI assistance for the CAFIC knowledge series. Examples are illustrative. Check current official requirements and local conditions before acting on regulatory or technical guidance.