How do you work out whether a lot will expire before it is used?

Divide the stock by the daily usage for its coverage in days and compare it with the days left before expiry. 240 units at 18 a day last 13.3 days; with 14 days to expiry nothing is left, the Adhesive Inventory Shelf Life calculator's own example.

How much stock will be left at expiry?

Stock − daily usage × days to expiry, and never less than zero. 30 bottles used at half a bottle a day for 40 days leaves 10.

What if two lots of the same material are open?

Give each lot the usage it will actually get. Usually the one expiring first takes all the usage until it runs out; the second lot then starts later, so lower its usage or count its days from when it will start.

What can I do with stock at risk?

Move it to a line or site that uses more, ask the maker whether a retest can extend the expiry, or sell or return it. Next time, buy closer to the usage even if the unit price is higher.

When should I update the tracker?

At each stock count, or when usage changes. Change the as-of date, the stock and the daily usage; the days to expiry, coverage and value at risk follow.

Does the workbook use macros?

No. It is a plain .xlsx with ordinary formulas, tables and native charts. The Verify sheet re-checks the formulas against fixed test lots and shows Self-check: PASS.

Planning and Supply Chain

Adhesive Shelf Life Tracker Excel Template

Stock left at expiry = stock − daily usage × days to expiry, and coverage = stock ÷ daily usage. List every lot of adhesive, sealant or coating with its expiry, stock and usage, and see how long each lasts, which will expire with stock on the shelf, what that stock is worth and when each lot runs out.

Free, and a plain .xlsx with no macros to enable. A CSV with live formulas and a printed how-to guide come with it.

Overview

This adhesive shelf life tracker is a working Excel workbook for stores and inventory controllers, process engineers and buyers who look after adhesives, sealants, coatings and other dated materials. Each lot is one row with its material, the dates it was received and expires, the stock on hand at the as-of date and the units a day drawn from it; each material's unit and price come from Setup. The workbook gives the days to expiry, how many days the stock lasts at that usage, the stock that will still be on the shelf at expiry and its value, and the date the lot runs out. It ships with eight lots in a fictional assembler's adhesive store, so every tile and chart is filled in when you open it.

In the example 240 epoxy cartridges used at 18 a day last 13.3 days against 14 to expiry, so all of them are used. A litre of UV acrylic bought in bulk for a price break lasts 60 days at half a bottle a day but expires in 40, leaving 10 bottles worth 1,450. 400 bottles of cyanoacrylate at 12 a day leave 100 at expiry, and PU sealant on a line that slowed in October leaves 30 sausages. Of the 23,714 in stock, 2,460 will expire unused at today's usage.

Usage is assumed steady; lots of one material are not worked through first-expiry-first-out automatically. The download is a plain .xlsx with no macros. A CSV with live formulas is also included for other tools; the dashboard, charts, dropdowns and self-check are in the .xlsx.

What this template includes

Setup sheet with your site, currency and as-of date, and the materials with their unit and price per unit
Lots table, one row per lot: material, received and expiry dates, stock and daily usage, with a Check column
Per lot: days to expiry, coverage, usable days, stock left at expiry, stock used before expiry, value at risk, the run-out date and a status in words
Dashboard with four tiles (lots, stock value, value at risk, lots that will expire with stock), coverage against days to expiry, the value that will expire unused, and the lot table
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference and the Adhesive Inventory Shelf Life calculator (Self-check: PASS)
CSV with live formulas for the Lots table; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it at each stock count to find dated lots that will expire before they are used, to move stock to a line that will use it in time, or to check a bulk buy before placing it.

How to use it

On Setup, type your site, currency and as-of date, and list your materials with their unit and price per unit.
On Lots, give each lot a row: material, received and expiry dates, stock and daily usage.
Fix anything the Check column names.
Open the Dashboard: coverage against days to expiry, and the value that will expire unused.

Frequently Asked Questions

How do you work out whether a lot will expire before it is used?: Divide the stock by the daily usage for its coverage in days and compare it with the days left before expiry. 240 units at 18 a day last 13.3 days; with 14 days to expiry nothing is left, the Adhesive Inventory Shelf Life calculator's own example.
How much stock will be left at expiry?: Stock − daily usage × days to expiry, and never less than zero. 30 bottles used at half a bottle a day for 40 days leaves 10.
What if two lots of the same material are open?: Give each lot the usage it will actually get. Usually the one expiring first takes all the usage until it runs out; the second lot then starts later, so lower its usage or count its days from when it will start.
What can I do with stock at risk?: Move it to a line or site that uses more, ask the maker whether a retest can extend the expiry, or sell or return it. Next time, buy closer to the usage even if the unit price is higher.
When should I update the tracker?: At each stock count, or when usage changes. Change the as-of date, the stock and the daily usage; the days to expiry, coverage and value at risk follow.
Does the workbook use macros?: No. It is a plain .xlsx with ordinary formulas, tables and native charts. The Verify sheet re-checks the formulas against fixed test lots and shows Self-check: PASS.