Inventory Valuation Workbook

At year end someone has to explain why stock on the ledger is not stock on the shelf, and which cost method moves profit. Stock count sheets answer the first half. This workbook values the stock under both methods from the same movement log and shows the gap, the count variance and the write-down.

What is inside

• Settings: period dates, the method you want in the headline figure and your ledger balance.
• Items: up to 100 items with opening quantity, opening unit cost, counted quantity and, if you want the write-down test, the net realisable value per unit.
• Movements: up to 1,000 receipts, issues and adjustments, oldest first, with a Problem column that flags missing items, bad dates and issues that exceed stock.
• Valuation: per item, the weighted average cost recalculated after every receipt and the FIFO cost of each issue, with closing value and cost of goods sold under both methods, the difference between them, the count variance and the NRV write-down.
• Reconcile: the roll-forward from opening to closing and the comparison with your ledger balance.
• Dashboard (three native charts) and Checks.

How it is deeper than the cheaper Stock Count Sheets and Reorder Planner: that product counts and reorders stock. This one values it, with a true moving weighted average and a closed-form FIFO layer calculation that needs no macros.

Facts and settings: weighted average here is the perpetual (moving) average, not a periodic average. Net realisable value is your own estimate. Add landed costs into the unit cost before you paste. Choose the method with your accountant and follow your accounting standards.

What it is NOT: it is a calculation tool, not accounting, tax or legal advice. It is not connected to your bank or accounting system, it is not an FTA-approved system and it is not an e-invoicing tool (e-invoicing is on the Zeyto roadmap only). Where a rule matters it is a setting you enter, with a note to confirm it with the FTA, your accountant or your auditor.

Two workbooks: an EXAMPLE copy filled with invented data so you can see every sheet working, and a BLANK copy with the same formulas ready for your own data. Formulas only: no macros, no volatile functions, no add-ins. Input cells are coloured, formula sheets are protected without a password, and a Checks sheet turns green only when every list ties out.

How it was checked: recalculated with an independent Python formula engine and 407 outputs (per-item weighted average and FIFO cost of goods sold and closing value, count variance, NRV write-down, reconciliation and chart series for 24 invented items) were compared with a separate Python model that simulates the FIFO queue and the moving average, with no difference. Because the 1,000-row workbook is very slow to evaluate with that engine, the check was run on a copy with 420 movement rows built from identical formulas, and the shipped file has 1,000 pre-built rows. There is no Excel or LibreOffice on the build machine, so it was not opened in them and the charts were not viewed on screen.

Limits: one cost per receipt line, no batches, expiry, serial numbers or multiple warehouses, and returns are entered as Adjust in or Adjust out. Calculation can feel slow in Google Sheets with all 1,000 rows used.