AI can set reorder points and expiry warnings for vet stock if you give it a practice-system stock export and usage history. It calculates the figures: average daily use, delivery lead time and a safety buffer for reorder points, and a colour code for expiry dates. Staff approve every order, and nothing about clinical dosing or restricted-drug handling goes near it.
The reason this works is that vet stock control is mostly arithmetic on data you already hold. The practice system knows what was dispensed and what is on the shelf. What it often lacks is a readable report that says "order this now" and "use this first". A spreadsheet plus an AI assistant fills that gap in an afternoon, without buying new software.
What to export, and what to leave out
Start with a stock export from your practice management system (PIMS). Names differ by product, but you want one row per stock item, with at least these columns:
- Item name and your internal code
- Category (for example vaccines, parasite control, anaesthetic consumables, dressings, diets)
- Quantity on hand, and the unit it is counted in (box, vial, pack, tablet)
- Supplier and the supplier's pack size
- Cost price per pack
- Batch number and expiry date, where the system records them
- Dispensing or usage history: units used per month for the last 12 months, or a transaction export you can summarise
Leave out client names, patient names and anything that identifies a person. Usage by item and month is all the AI needs. If your system only exports transactions, ask the AI to summarise them to monthly totals first, after you strip the client column yourself in the spreadsheet.
Reorder points in plain arithmetic
A reorder point is the stock level at which you place an order. The standard calculation has three parts, and each is something your team can check:
| Part | How to get it | Illustrative value |
|---|---|---|
| Average daily use | Last 12 months' units used, divided by days open | 3,000 units / 300 days = 10 a day |
| Lead time | Working days from order to shelf, from your supplier's last five deliveries | 3 days |
| Safety buffer | Extra days of stock for a late delivery or a busy week | 2 days |
| Reorder point | Daily use x (lead time + buffer days) | 10 x 5 = 50 units |
The AI's role is to apply this to every row at once and to point out items whose usage is too irregular for a simple average. You decide the buffer by category. A fast-moving consumable with a next-day supplier can have a one-day buffer. A vaccine with an irregular delivery, or a product only one supplier stocks, deserves more. Ask your lead vet which items must never run out, and give those a bigger buffer, whatever the arithmetic says.
Here is a prompt for the first run. It assumes you have uploaded the cleaned export to a tool your clinic approves:
I run stock control for a small veterinary clinic. The attached sheet has one row per stock item with monthly usage for 12 months, supplier lead time in days, pack size and quantity on hand.
For each row calculate:
1. Average daily use (assume 300 working days a year; show the formula)
2. Reorder point = average daily use x (lead time + buffer days). Use a buffer of 2 days unless the Buffer column says otherwise.
3. Suggested order quantity = enough for 14 days, rounded UP to a whole supplier pack
4. A flag if monthly usage varies by more than 50% from the average ("irregular - review by hand")
Output a table I can paste into Excel. Do not give any clinical advice, dosing or substitution suggestions. If a column is missing, tell me which, instead of guessing.Always sanity-check five rows by hand before trusting the rest. Pick one fast-moving item, one slow one, one with a large pack size, one with a short expiry, and one the AI flagged as irregular. If your hand calculation matches the AI's on all five, the formulas are probably right. If you prefer to keep the calculation in the spreadsheet itself, the AI can write the Excel formulas instead of the numbers; the ChatGPT Excel guide covers how to do that and to check them.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowThe expiry traffic-light
Expiry waste is quiet and expensive. A box of short-dated vaccines goes unused behind a newer delivery, and nobody sees the loss until a stocktake. A traffic-light column makes it visible at a glance. Build it from the expiry date and today's date, then colour by days remaining. Illustrative thresholds, for your lead vet to adjust per category:
| Colour | Days to expiry | Action |
|---|---|---|
| Red | Expired, or 30 days or fewer | Remove expired stock from the shelf at once; for 30 days or fewer, use first or return to the supplier if allowed |
| Amber | 31 to 90 days | Move to the front; tell the team; hold the next order unless stock would fall below the reorder point |
| Green | Over 90 days | Normal rotation |
Spreadsheet formulas are simple enough to ask the AI for and to read afterwards. Ask for a "Days to expiry" column and a "Status" column using conditional formatting, and ask it to explain each formula in one line so that whoever maintains the sheet understands it. Remove or reconcile expired stock according to your clinic's written procedure; the sheet only highlights the problem.
Two refinements are worth having. First, one item often holds several batches with different dates, so the sheet should have one row per batch, not per product. Second, vaccines and other refrigerated products usually deserve an earlier amber window, because a short-dated box is harder to use up before it expires. Choose the windows by category and write them in a small settings table on the sheet, so nobody needs to edit formulas to change them.
A usage report from the PIMS export
Once a month, ask the AI for a one-page summary built from the same export. A useful version shows four things: the ten items with the highest spend, the ten with the biggest change in usage against the previous three months, items with stock but no use in six months (possible dead stock), and items bought more than once in a month (possible reorder-point problems). A prompt:
Using the attached stock and usage sheet, produce a one-page monthly report for the practice manager with:
1. Top 10 items by cost of stock used this month
2. Top 10 changes in usage vs the average of the previous 3 months (up and down)
3. Items with stock on hand but zero usage in the last 6 months
4. Items ordered more than once this month
For each, add one line of plain-English context, but no clinical or dosing comment. Finish with three questions the practice manager should ask the team. Do not invent figures not in the sheet.Three questions at the end do more than a long commentary. A good AI answer will say things like "Item X usage doubled; was there a seasonal outbreak or a change in protocol?". That is a question a person answers, not a conclusion to act on.
420 lines and $9,000 a month: a four-vet clinic's first run
Say a four-vet clinic stocks about 420 items and spends roughly $9,000 a month on consumables and medicines. It writes off expired stock a few times a year, and the nurse in charge of ordering spends around two hours a week counting shelves and checking the system.
The practice manager exports the stock list and 12 months of usage, deletes the client columns, and runs the reorder-point prompt. The AI returns suggested points for 380 items, lists 40 it could not calculate because the lead time was blank, and flags 31 as irregular. The nurse checks five rows by hand, finds one where the lead time was entered in weeks rather than days, corrects the column, and reruns. For the 31 irregular items she and the lead vet agree manual levels.
Next, the practice manager sets up the expiry sheet with one row per batch, 612 rows in all. The first traffic-light run shows 14 red and 47 amber. The red rows include three expired items still on a shelf. The team clears them, uses the amber stock first and holds off the next orders for those lines. Say the amber and red items had a combined cost of $1,300 at purchase; using most of the amber stock avoids writing off a good part of that. These numbers are invented to show the sums. Your clinic's own export will give real ones in an afternoon.
After two months the clinic measures three things: the number of write-offs for expiry, the number of times an item ran out, and the nurse's weekly stock time. If the write-offs fall and the stock-outs do not rise, the reorder points are about right. If stock-outs rise, increase the buffer for the items concerned.
Cleaning a messy export before you trust it
Real exports are untidy. Spend the first twenty minutes on cleaning, because every later number depends on it. Ask the AI to list problems rather than fix them silently, so you see what it found. Here is an illustration of the kind of issues a stock export throws up and what a person should decide.
| What the export shows | The problem | Decision |
|---|---|---|
| "Dressing, cohesive 5cm" and "Cohesive bandage 5 cm" | Same item under two names, so usage and stock are split | Merge in the practice system, then re-export |
| Quantity on hand: -4 | Dispensed before receiving, or a missed goods-in | Count the shelf and correct the system |
| Expiry date: 01/01/2000 | Placeholder where no date was entered | Treat as "unknown", never as expired; check the physical box |
| Lead time column blank for a supplier | No data for the formula | Fill from the last five deliveries, or leave the item out of the run |
| Cost price of $0.00 | Free sample or an entry error | Look up the invoice |
The point is to see the clutter before it produces plausible but wrong reorder points. Expiry placeholders deserve special care. A sheet that treats a missing date as an old date will flash red on dozens of items and train the team to ignore it, which defeats the whole idea.
One filled-in slice of the expiry sheet
This is what a few rows of a batch-level sheet look like once the formulas are in place. The items and dates are invented, and today's date in the example is 1 March.
| Item | Batch | On hand | Expiry | Days left | Status |
|---|---|---|---|---|---|
| Core vaccine, 10-dose box | B-4417 | 3 | 20 Mar | 19 | Red |
| Core vaccine, 10-dose box | B-4602 | 8 | 30 Sep | 213 | Green |
| Surgical suture, 3-0, box of 12 | S-0931 | 5 | 15 May | 75 | Amber |
| Wound spray, 250 ml | W-2208 | 6 | 28 Feb | -1 | Red (expired: remove) |
Read it as a nurse would. The older vaccine batch must be used first, so the sheet needs a "use first" instruction, and the system or the shelf label should put batch B-4417 at the front. The 3-0 suture is amber, so the next order for that line waits. The wound spray is expired and leaves the shelf now. Notice that the same product appears twice, which is why batch-level rows matter: a product-level sheet would have shown only the later date and hidden the problem.
Supplier lead times: measure, do not guess
A reorder point is only as good as its lead time, and lead time is the figure clinics are most tempted to guess. Ask the AI to compute it from your own data: for each supplier, the days between each order date and the goods-in date over the last 10 orders, with the average and the longest. Then decide your buffer from the longest typical delay rather than the average.
A short illustration: one supplier averages two days but twice in ten orders took six. A buffer of one day would have run the shelf dry both times. A buffer of three days costs a few extra boxes on the shelf. You pay a small carrying cost for peace of mind, which is a decision for the owner, not for a formula. Also record any supplier cut-off times for same-day dispatch, since an order placed at 4.45 pm may count as the next working day. Reception and nurses should know the cut-off, so it belongs on the sheet's settings tab next to the lead time.
A dead-stock review, step by step
Stock that sits for six months or more ties up cash and may expire unused. A quarterly review takes about half an hour. List every item with stock on hand and no usage in six months, sorted by value. For each, a vet or senior nurse chooses one of four outcomes: keep (a legitimate low-use emergency item), use up (put it forward in protocols where clinically suitable), return or sell back to the supplier if allowed, or write off. The AI helps only with the list and the sums. For example, say the list shows 22 items worth $1,150 in total, of which 9 are emergency items the team wants to hold. The remaining 13 items, worth $620, are reviewed individually, and two are returned. A clinic that does this each quarter rarely meets a large write-off at year end.
Five more checks AI can run on the same data
- Pack-size mismatches. The system counts tablets but the supplier sells boxes of 100. Ask the AI to list items where the reorder quantity is not a whole pack, and see whether the data or the ordering unit is wrong.
- Duplicate items. The same drug entered twice under slightly different names, splitting its stock and usage. Ask for likely duplicates by name similarity, then merge them in the practice system yourself.
- Price creep. Compare the last three purchase prices per item and list those that rose by more than 10 per cent. This helps you review supplier terms and your own price list, which feeds the figures on client estimates.
- Stock-count variance. After a stocktake, compare the physical counts to the system and rank items by value of the difference. The biggest differences are where to look for process errors. The stocktake tutorial covers faster counting.
- Seasonal patterns. Where 12 months of data exist, ask whether usage of particular items rises at certain times, such as parasite control in warm months, and whether your buffer should move with it.
What stays with people
Some parts of vet stock control should not be handed to a spreadsheet or an AI, and the sheet should say so on its first tab.
- Controlled and restricted drugs. These have their own registers and legal rules. Keep them out of the AI sheet, and follow your clinic's procedure and your regulator's guidance. If you want them tracked in the same spreadsheet for convenience, show only a "see register" line.
- Clinical substitution. If a product is out of stock, which one replaces it is a vet's decision. The AI should never propose a substitute.
- Cold-chain handling. Fridge temperature logs and what to do after a failure are procedures, not calculations.
- Recalls and quality notices. Act on the supplier's or regulator's notice. The batch column helps you find affected stock, but a person decides and acts.
- Final orders. A named person approves each order against cash, storage space and any upcoming promotions or closures.
Units, gaps and stale exports: how the sheet misleads
Dirty units. The most common error is an export in tablets with a price in boxes. The reorder-point maths then looks sensible and is out by a factor of a hundred. Check units on a sample of rows, as in the worked example.
Averages that hide gaps. A product used heavily in two months and not at all in ten has the same yearly average as one used steadily. The irregular-usage flag exists for this reason, so never switch it off.
A stale export. Reorder points go out of date as the clinic grows. Rerun the calculation every quarter, or after a new vet or service arrives, because usage changes with them.
Over-trusting a tidy table. An AI table looks authoritative. If a number surprises you, look at the row, not the table.
Sensitive data in the wrong tool. Cost prices and supplier terms are commercially sensitive. Use a business plan of a chat assistant or your office suite's assistant. OpenAI, Anthropic, Google and Microsoft each state that business content is not used to train their models by default (as of November 2026; reread the vendor's privacy page before relying on it). Keep the file off personal accounts.
Setting up a rhythm that lasts
The routine is short. Weekly, five minutes: open the sheet, read the red and amber rows, move short-dated stock forward. Monthly, thirty minutes: export fresh data, rerun the usage report and compare it with the last one, then talk through the three questions at a team meeting. Quarterly, one hour: recompute reorder points, check lead times against your last deliveries and revisit the buffers with the lead vet.
Write the routine on the first tab of the sheet, name the person responsible for each step and save the prompts next to the file. When the person who set it up leaves, the clinic still has the method. If you later want something more automated, the comparison of AI inventory tools shows what dedicated apps offer, and the forecasting tutorial for small shops goes further on seasonal demand. For a broader view of where AI fits in a vet clinic, see AI for veterinary practice owners.
Questions about AI and vet stock control
Can AI decide how much of a drug to order?
It can calculate a suggested reorder point from your usage figures, but the person who orders makes the decision. Seasonal demand, supplier minimums, cold-chain space and any restricted-drug rules all sit outside a spreadsheet. Treat the figure as a prompt to check the shelf, not an order.
Is it safe to upload a PIMS stock export to ChatGPT or Claude?
A stock export holds product names, quantities, batches and dates, and no client data, but check that your export does not include client or patient columns. Use a business plan or an approved tool, and remove any column you do not need before uploading.
What expiry window should trigger a warning?
There is no universal number. A workable starting point is red for expired or within 30 days, amber for 31 to 90 days and green beyond that, then adjust by item. Short-dated vaccines and refrigerated products often need an earlier amber. Choose thresholds with your lead vet.
How often should I run the usage report?
Monthly is enough for most small clinics, plus a quick check before any seasonal surge. A weekly traffic-light review of expiry dates takes five minutes and catches most waste. Run the reorder-point review after any supplier change.
Does this replace the stock features in my practice software?
Not necessarily. If your system already tracks batches and reorder levels, use it and add AI only to analyse exports. The sheet in this tutorial suits clinics whose system gives counts but weak reporting, or who want a second view for checking.
Further reads
- How Pubs and Bars Can Use AI for Events, Rotas and Stock — Stock rules for another expiry-sensitive small business.
- How Small Vet Practices Use AI to Cut Time on Clinical Notes — Another safe, bounded use of AI in the clinic.
- Does AI Stock Ordering Pay Off for an Independent Pharmacy? — A closer parallel: expiry-sensitive medicines stock.
- Veterinary Receptionist Training Manual: From Your Protocols, Using AI — Train reception on stock-out and order-delay messages.
- Veterinary Consent Form Template: Drafting Procedure Consents With AI — Another clinic document that needs version control.
- AI Inventory and Check-Out Reports: A Letting Agent's Guide — Checklist-driven inventory reporting in another trade.
- Vet Practice Management Software (PIMS): Which AI Tools Cost Extra? — Which PIMS already include stock and AI features, and at what cost.
- Tyre Stock Excel Sheet: Track Sizes and Reorder Points With AI — A tested tyre stock sheet: size-and-brand matrix, reorder-point formulas, seasonal demand, slow-mover and DOT-age flags, plus three jobs for AI.
- Veterinary Wellness Plan: What to Include, Price and Launch With AI — A practice-side worksheet for costing a wellness plan from your own prices, plus AI-drafted plan wording and a client FAQ.
- AI App for Veterinarians on the Road: Notes, Quotes, Routes — Three jobs, three kinds of app: voice notes, on-site estimates and a day's route, plus what to do when the signal drops.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: OpenAI business data page, Anthropic privacy centre article on model training, Google Workspace generative AI privacy hub and Microsoft Learn page on Copilot data and privacy, all checked 4 November 2026; standard reorder-point arithmetic. All quantities in examples are invented for illustration.