Free Excel Inventory Spreadsheet Template

Formula, Example, and Workbook Limitations

This free inventory spreadsheet template gives small businesses a clean Excel workbook without complex macros. Start by listing your items on the Products tab, then record incoming orders on the Purchases tab and outgoing orders on the Sales tab while the Overview tab updates automatically. The workbook calculates unit cost using: Average cost = (Opening quantity × Opening average cost + Total purchase amount) ÷ (Opening quantity + Total purchase quantity); outgoing sales do not alter the average cost, and the Sales sheet calculates estimated gross profit as Sales amount − Sales quantity × Average cost. For example, with an opening balance of 10 units at 100, a purchase of 5 units at 130, and a sale of 6 units at 160, the average cost is 110, current stock is 9, inventory value is 990, and that sale yields an estimated gross profit of 300 with a 31.25% gross margin.

Scope and limitations: Unlike the online Weighted Average Inventory Cost Calculator (which averages purchase batches only), this workbook includes Opening Qty and Opening Avg Cost in its formula, so the two match only when opening stock is 0 or its unit cost equals the weighted average of new purchases. The sheet supports up to 30 unique Item Codes (no duplicates, no * ? ~ wildcards, no leading or trailing spaces; enter 16+ digit numeric codes as text with a leading apostrophe '). If any row has missing or invalid data, duplicate codes, or sales exceeding available stock, derived calculations pause and leave stock, cost, totals, and profit blank (instead of showing a misleading 0) until all Data Check counts return to 0. Formula columns are protected without a password, though pasting cells in spreadsheet editors can bypass validation prompts. Verified in LibreOffice Calc; native Microsoft Excel Desktop compatibility has not been separately tested.