Inventory Valuation Rollforward & Lower of Cost or Net Realizable Value (LCNRV) Calculator Excel Workpaper
01_Executive_Summary_KPIs — Interactive Spreadsheet
| # | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | INVENTORY VALUATION ROLLFORWARD & LCNRV RESERVE WORKPAPER | |||||||||||
| 2 | US GAAP ASC 330 / IFRS IAS 2 Audit Lead Schedule | Entity: Apex Precision Manufacturing Corp. | Period Ended: September 30, 2026 | WP Ref: WP-INV-100 | |||||||||||
| 3 | ||||||||||||
| 4 | Prepared By: | S. Walker, CPA (Senior Auditor) / 10/12/2026 | Valuation Method: | FIFO (First-In, First-Out) at LCNRV | Lead Schedule Status: | TIED & RECONCILED ($0.00 VARIANCE) | ||||||
| 5 | Reviewed By: | E. Bennett, CPA (Audit Partner) / 10/16/2026 | Audit Standard: | US GAAP ASC 330 / IFRS IAS 2 (LCNRV Testing) | GL Control Tie-Out: | Accounts #12010-#12050 & Reserve #12090 | ||||||
| 6 | ||||||||||||
| 7 | GROSS INVENTORY AT COST | TOTAL LCNRV & AGING RESERVE | NET CARRYING VALUE (B/S) | RESERVE COVERAGE RATIO | GL TIE-OUT VARIANCE | |||||||
| 8 | $4,825,450.00 | $686,035.00 | $4,139,415.00 | 14.2% | $0.00 | |||||||
| 9 | ||||||||||||
| 10 | 1. INVENTORY PORTFOLIO SUMMARY BY ASSET CLASS & GL RECONCILIATION | |||||||||||
| 11 | Inventory Category / Asset Class | GL Account # | Active SKU Count | Gross Qty on Hand | Total Gross Cost ($) | Weighted NRV ($) | Required Reserve ($) | Net Carrying Value ($) | Reserve % | GL Trial Balance ($) | Tie-Out Variance ($) | |
| 12 | Raw Materials & Precision Components | 12,010 | 84 | 18,450 | $1,142,850.00 | $1,108,200.00 | $127,080.00 | $1,015,770.00 | 11.1% | $0.00 | ||
| 13 | Work-in-Process (WIP - Production) | 12,020 | 32 | 6,120 | $895,400.00 | $864,100.00 | $63,775.00 | $831,625.00 | 7.1% | $0.00 | ||
| 14 | Finished Goods (Warehoused Products) | 12,030 | 116 | 24,800 | $2,386,500.00 | $2,198,400.00 | $491,780.00 | $1,894,720.00 | 20.6% | $0.00 | ||
| 15 | Packaging, Tooling & Operating Supplies | 12,040 | 28 | 9,400 | $182,300.00 | $3,400.00 | $182,300.00 | 1.8% | $0.00 | |||
| 16 | In-Transit Inventory (FOB Shipping Point) | 12,050 | 12 | 3,200 | $215,000.00 | $0.00 | $215,000.00 | 0.0% | $0.00 | |||
| 17 | TOTAL GROSS INVENTORY PORTFOLIO | 272 | 61,970 | $4,825,450.00 | $4,568,000.00 | $686,035.00 | $4,139,415.00 | 14.2% | $4,825,450.00 | $0.00 | ||
| 18 | INVENTORY SUBLEDGER TO GENERAL LEDGER TIE-OUT CHECK: TIED EXACTLY TO TRIAL BALANCE ($0.00 VARIANCE) [TICKMARK: ✓] | $0.00 | ||||||||||
| 19 | ||||||||||||
| 20 | 2. VALUATION ALLOWANCE & OBSOLESCENCE RESERVE ROLLFORWARD (ASC 330 / IAS 2) | |||||||||||
| 21 | Valuation Reserve Component / Tier | Reserve GL # | Beg Balance (01/01/26) | Add: Period Provision (+) | Less: Realized Write-Offs (-) | Less: Recoveries / Reversals | Ending Reserve Balance | Prior Quarter Reserve | Net Q3 Reserve Change ($) | GL Reserve Balance ($) | Tie-Out Variance ($) | |
| 22 | Tier 1: Specific LCNRV Impairment Shortfall | 12,091 | $125,000.00 | $148,700.00 | $138,000.00 | $10,700.00 | $148,700.00 | $0.00 | ||||
| 23 | Tier 2: Slow-Moving Inventory Aging Reserve (91-180 Days) | 12,092 | $54,000.00 | $68,300.00 | $62,000.00 | $6,300.00 | $68,300.00 | $0.00 | ||||
| 24 | Tier 3: Excess & High-Risk Aging Reserve (181-360 Days) | 12,093 | $72,000.00 | $85,600.00 | $78,500.00 | $7,100.00 | $85,600.00 | $0.00 | ||||
| 25 | Tier 4: Obsolete & Scrapped Product Reserve (>360 Days) | 12,094 | $65,000.00 | $57,800.00 | $61,000.00 | -$3,200.00 | $57,800.00 | $0.00 | ||||
| 26 | Tier 5: Shrinkage & Cycle Count Variance Allowance | 12,095 | $18,500.00 | $19,700.00 | $19,000.00 | $700.00 | $19,700.00 | $0.00 | ||||
| 27 | TOTAL INVENTORY VALUATION ALLOWANCE (CONTRA ASSET #12090) | $334,500.00 | $133,700.00 | $83,900.00 | $4,200.00 | $380,100.00 | $358,500.00 | $21,600.00 | $380,100.00 | $0.00 | ||
| 28 | INVENTORY VALUATION RESERVE TO GL TRIAL BALANCE TIE-OUT CHECK: TIED EXACTLY TO GL #12090 ($0.00 VARIANCE) [TICKMARK: ✓] | $0.00 | ||||||||||
Free Instant Download
No email signup • Unlocked formulas
Built-In Excel Formulas & Calculation Logic
Unlocked native Microsoft Excel functions pre-programmed inside this workbook:
=MAX(0, SellingPrice - CostToComplete - DisposalCost) Calculates net realizable value under ASC 330 by subtracting estimated costs of completion and selling/shipping disposal expenses from estimated transaction selling price, bounded at zero.
=MAX(0, FIFOUnitCost - UnitNRV) Measures the per-unit lower of cost or net realizable value deficiency. If FIFO unit cost exceeds unit NRV, an impairment write-down is triggered; otherwise, the cost basis is preserved at $0.00 impairment.
=MAX(TotalLCNRVShortfall, AgingReserveAmount) Applies conservative US GAAP valuation standards by selecting the higher of the specific LCNRV market deficiency or the formulaic turnover aging-matrix reserve, guaranteeing adequate reserve coverage.
=BegReserve + CurrentProvision - WriteOffs - Recoveries Maintains the complete accounting rollforward for the contra-asset valuation allowance (GL #12090), reconciling period expense provisions to realized scrap write-offs and trial balance balances.
Full Workbook Print Layout Preview (.XLSX)
Overview & Industry Standard Alignment
Inventory valuation is among the highest-risk accounting areas subject to intense external audit scrutiny, regulatory compliance reviews, and financial restatements. Under US GAAP ASC 330 (simplification issued in ASU 2015-11) and IFRS IAS 2, entities utilizing First-In, First-Out (FIFO) or Average Cost must measure inventory at the Lower of Cost or Net Realizable Value (LCNRV). When market demand declines, product revisions supersede active components, or disposal costs increase, inventory carrying values must be written down immediately through cost of goods sold. Relying on static ERP inventory snapshots without audit-trail lead schedules frequently leads to undetected valuation deficiencies and audit adjustments. This workbook provides a complete, institutional-grade CPA workpaper designed to bridge the gap between transactional warehouse subledgers and external financial reporting. It structures a multi-tier inventory rollforward across Raw Materials, WIP, Finished Goods, Packaging, and In-Transit items, establishing mathematical proof of beginning-to-ending cost accumulation, direct labor absorption, overhead application, and COGS relief. The accompanying LCNRV calculation engine stress-tests every SKU against post-period selling prices, completion costs, and aging brackets. Equipped with standard Big-4 audit tickmarks, cross-sheet tie-out banners, inventory turnover and Days Sales of Inventory (DSI) efficiency metrics, and pre-formatted month-end adjusting journal entries (AJEs), this template equips Controllers, CPAs, and Finance Directors with an authoritative, audit-ready workpaper ready for Board and auditor presentation.
Practical Operational Use Cases:
CPA External Audit & Quarter-End Close Lead Schedules
Serve as the primary audit workpaper (Lead WP-INV-100) substantiating gross inventory balances and contra-asset valuation allowances for interim reviews and year-end GAAP audits with full trial balance tie-outs.
ASC 330 & IFRS IAS 2 Lower of Cost or Net Realizable Value Testing
Systematically evaluate warehouse SKUs where market prices have softened or newer product revisions have launched, calculating the exact required write-down per unit and overall portfolio reserve impact.
Slow-Moving & Obsolete Inventory Reserve Modeling
Establish a transparent, policy-governed aging reserve matrix (0-90, 91-180, 181-360, and >360 days) to identify dormant stock and calculate reserve provisions before physical scrapping occurs.
Manufacturing Cost Rollforward & Turnover Analytics
Track quarterly direct material inputs, absorbed direct labor, applied overhead, COGS deductions, inventory turns, and Days Sales of Inventory (DSI) across diverse manufacturing divisions.
Designed For:
What’s Inside the Workbook (Tab-by-Tab Breakdown)
Executive Summary, KPI Cards & GL Lead Schedule
Contains the CPA engagement block, 5 high-impact KPI cards, gross inventory summary by asset class (GL #12010-#12050), and the valuation allowance rollforward table (GL #12090) with automated $0.00 tie-out checks.
Multi-Division Activity Rollforward & DSI Analytics
A comprehensive operational lead schedule detailing cost accumulation, labor/overhead absorption, COGS relief, scrap deductions, annualized turns, and Days Sales of Inventory (DSI) across operating divisions.
Itemized SKU LCNRV Testing & Aging Reserve Engine
The calculation engine evaluating 20 representative high-value SKUs across raw materials, work-in-process, and finished goods against selling prices, completion costs, and aging brackets.
Adjusting Journal Entries & GAAP Policy Framework
Provides ERP-ready month-end adjusting journal entries (AJEs), standard CPA audit tickmark legend, and formal accounting policies under US GAAP ASC 330 and IFRS IAS 2.
Practitioner Tips & Best Practices
-
✓
Always corroborate estimated selling prices in Tab 03 with post-balance sheet sales invoices (subsequent period collections) to satisfy external auditor verification procedures.
-
✓
For Work-in-Process (WIP) inventory, ensure costs to complete reflect full standard direct labor and applied overhead, as omitting completion costs will artificially inflate Net Realizable Value.
-
✓
Under US GAAP, once inventory is written down to Net Realizable Value, that reduced value becomes the new permanent cost basis; subsequent market price recoveries cannot be reversed upward.
-
✓
Perform physical cycle counts and scrap write-offs prior to calculating your final aging reserve provision so that retired inventory is relieved from gross balances first.
Frequently Asked Questions
What is the difference between Lower of Cost or Market (LCM) and Lower of Cost or Net Realizable Value (LCNRV)?
Historically under US GAAP ASC 330, inventory was evaluated under the Lower of Cost or Market (LCM) framework, which defined market as replacement cost constrained between an upper ceiling (Net Realizable Value) and a lower floor (NRV less normal profit margin). However, under ASU 2015-11 (now permanent in ASC 330), all entities measuring inventory under FIFO or Average Cost must measure inventory simply at the Lower of Cost or Net Realizable Value (LCNRV). The ceiling and floor test now applies only to entities using LIFO or the retail inventory method.
How is Net Realizable Value (NRV) calculated under US GAAP ASC 330 and IFRS IAS 2?
Net Realizable Value is defined as the estimated selling price in the ordinary course of business, less reasonably predictable costs of completion, disposal, and transportation. In mathematical terms: NRV = Estimated Selling Price - Estimated Costs to Complete - Estimated Costs to Sell/Transport. If NRV is lower than the FIFO carrying cost, an impairment loss must be recognized immediately in Cost of Goods Sold.
Can an inventory write-down be reversed if market prices subsequently recover?
The accounting treatment differs between US GAAP and IFRS. Under US GAAP ASC 330, once inventory is written down to Net Realizable Value, the reduced amount becomes the new permanent cost basis, and GAAP explicitly prohibits reversing write-downs even if prices recover. Under IFRS IAS 2.33, previously recognized write-downs may be reversed up to the amount of the original write-down if the conditions causing the impairment cease to exist.
How does this template handle both specific LCNRV impairment and general aging reserves?
The template uses a conservative dual-criteria evaluation in Tab 03: =MAX(TotalLCNRVShortfall, AgingReserveAmount). If an item has suffered specific price deterioration greater than its formulaic aging reserve, the full LCNRV deficiency is booked. Conversely, if an item still has a high market selling price but has sat in the warehouse past 180 or 360 days, the aging matrix reserve takes precedence to account for latent obsolescence.
Where does the inventory valuation reserve appear on the balance sheet?
The inventory valuation reserve (often titled Allowance for Obsolete and Excess Inventory, GL #12090) is classified as a contra-asset account directly offsetting gross inventory on the balance sheet. Gross inventory at FIFO cost less the contra-asset valuation allowance equals the Net Carrying Value of inventory presented in current assets.
How does the template ensure that subledger schedules tie to the general ledger?
Tabs 01, 02, and 04 feature automated reconciliation and cross-footing checks. In Tab 01, the schedule gross cost is compared to the GL Trial Balance input cell, calculating =GrossCost - GLBalance. If the difference is zero, the cell displays 'TIED EXACTLY ($0.00 VARIANCE)' and tickmark [✓]; if any discrepancy exists, it triggers a 'VARIANCE ALERT' warning.
Is this workbook compatible with Google Sheets and Microsoft Excel for Mac?
Yes. The workbook is engineered with 100% native standard OpenXML formulas (SUM, SUMIF, COUNTIF, MAX, MIN, IF, IFERROR) and contains zero VBA macros or proprietary COM add-ins, ensuring seamless compatibility across Excel for Windows (2016-365), Excel for Mac, and Google Sheets.