Free Excel templates

Inventory Valuation and Reserve Model

Standards, purchases, aging and the count in; capitalized variances, LIFO reserve, write-down, reserve and the inventory note out, with a Checks tab.

Type the standards, the actual costs, the purchase lots, the service parts aging and the count once, and the workbook closes the inventories: the capitalizable variance pool spread over the units made, fixed overhead held at normal capacity, a FIFO roll-forward of finished goods through the count, LIFO layers with the reserve and a liquidation, FIFO, weighted average and LIFO side by side, a lower of cost and net realizable value test and an excess and obsolete reserve by age. In the worked case the 2026 wrench unit cost comes to $103.616.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 154 free models.

Inventory_Accounting_Model.xlsx (30 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Inventory Accounting Handbook, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 155 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

8 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Standards, normal capacity, actual costs and production, blade purchases lot by lot, the service parts aging and the year-end count, in yellow cells.
Variances
Price, usage, rate, efficiency and overhead variances; fixed overhead allocated at normal capacity; the capitalizable pool and the variance per unit.
Finished goods
The FIFO roll-forward in units and dollars, the count and shrinkage, the lower of cost and net realizable value test on the discontinued model, and the period costs.
LIFO
LIFO layers peeled newest first, the LIFO reserve, the liquidation effect and the lower of cost or market test on the pool.
Methods
FIFO and LIFO side by side on the same purchases, with the weighted average for the first year.
Reserve
Service parts aged by months since last movement, the reserve by bucket and its roll-forward.
Note
The inventory note by class, the FIFO amount as supplemental information, turnover, days in inventory and gross margins.
Checks
Roll-forwards, pool allocation, layers, count, reserve and note totals, with an overall status.

What the worked case returns

MeasureValue
Capitalizable variance pool 2026$542,400
Wrench unit cost 2026, standard plus variance ($ a unit)$103.616
Fixed overhead not allocated below normal capacity$1,500,000
Finished goods after the write-down$2,543,936
Write-down of the discontinued model$46,464
Blades at LIFO$367,500
LIFO reserve$217,500
Service parts reserve at year end$232,500
Total inventories$5,081,736

The LIFO reserve is the gap between what the blades would cost on FIFO and what the books carry on LIFO: $585,000 less $367,500 gives $217,500. It barely moved in 2026 because old, cheap layers were liquidated while purchase prices rose.

How to use it on your own case

  1. Enter the standards, normal capacity and the actual costs and production in the yellow cells of Inputs.
  2. Enter the blade purchases lot by lot, the service parts aging and the year-end count.
  3. Read Variances: the pool is spread over the units made, and fixed overhead the plant could not carry stays out.
  4. Read Finished goods, LIFO and Reserve for the closing balances, the write-down, the LIFO reserve and the reserve roll-forward.
  5. Check that the Checks tab reads ALL PASS before you take any figure into a note or a memo.

Questions people ask about it

Why is fixed overhead not spread over actual production?

Because the rule allocates it on normal capacity. When the plant runs below its normal range, the unallocated part is a period cost. In the case $1,500,000 went to cost of sales instead of the units.

What does the LIFO reserve tell me?

How far LIFO cost sits below FIFO cost. The change in the reserve over a year equals LIFO cost of sales minus FIFO cost of sales; the Checks tab tests that tie.

Does the model decide my excess and obsolete reserve?

No. It applies the rates you type to the aging and rolls the reserve forward. The rates are an accounting estimate that management sets and supports with a back-test.

Go further

The free workbooks of The Inventory Accounting Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Inventory Accounting Handbook. See the book.