Tyre Stock Excel Sheet: Track Sizes and Reorder Points With AI

Keep one row per size, brand and season, with on-hand and on-order counts, then calculate each row's reorder point as average weekly sales times lead time plus a safety margin, rounded up to whole pairs. Flag a row when stock plus orders falls to that point. AI writes and checks the formulas; you set the rules.

A blank stock template with columns for size and quantity is easy to find. What makes the sheet useful is the logic behind it: weekly demand from your own sales log, a lead time for each supplier, a flag for sizes that have stopped selling and a date check on old stock. The ten stock rows that test that logic in the worked example are invented, so replace every figure with your own.

Follow me on Instagram@sagnikteaches

Why a tyre needs size, brand and season on one row

A tyre is not one product with a size attribute. A sidewall code such as 205/55R16 91V packs several facts into a few characters. Tyre makers' guides, Pirelli's among them, list the markings in a fixed order: nominal section width, height-to-width aspect ratio, wheel diameter, load index and speed index. So 205 is the section width in millimetres, 55 is the aspect ratio (the sidewall height as a percentage of the width), R marks radial construction, 16 is the rim diameter in inches, 91 is the load index and V is the speed rating. A customer's vehicle needs the right width, profile and rim, and may need a particular load index or speed rating, so a row has to carry enough detail to avoid selling the wrong tyre.

Connect on LinkedInSagnik Bhattacharya

The sheet therefore treats a row as a combination of size, brand and season (summer, winter or all-season). A brand's summer and winter tyres in the same size sell on different calendars, and two brands in the same size compete for the same customers, so mixing them on one row would blur both. If a load index or speed rating matters for your customers, add it to the size text on the row, or give it its own column and include it in the formulas' criteria.

Subscribe on YouTube@sagnikteaches

The sheet also records one more fact that most templates leave out: the age of the oldest tyre in the batch. The last four digits of the DOT string on the sidewall are the date code, with the first two the week and the last two the year. Pirelli's own example is 1119, the eleventh week of 2019. Pirelli also notes that a tyre's performance and safety decline with age even when it is not used, so the sheet reports the age and your own written policy decides what to do about it.

The Stock tab, column by column

The workbook has four tabs, plus an optional fifth. Stock holds one row per size, brand and season. Sales is a log you paste from your till, with one line per tyre line sold: date, size, brand, season and quantity, and returns entered as negative quantities. If your till export has no season field, add one with a lookup from the Stock tab. Settings holds the rules in one place. Matrix is the size-and-brand view for scanning. The optional fifth tab lists the rows to order, with =FILTER(Stock!A2:P200,Stock!O2:O200="Reorder","Nothing to order"); FILTER needs Microsoft 365, Excel 2021 or later, or Google Sheets. The Settings values below are the ones used for the worked example, and they are all yours to change.

These are the Stock columns and the formulas for row 2. They work in Excel 2019 and later and in Google Sheets. Enter sizes and DOT codes as text, because Excel will otherwise drop the leading zero from a code such as 0124.

A  Size (text, e.g. 205/55R16)
B  Brand
C  Season
D  On hand
E  On order
F  Lead time (days)
G  Sold in window     =SUMIFS(Sales!$E:$E,Sales!$B:$B,$A2,Sales!$C:$C,$B2,Sales!$D:$D,$C2,Sales!$A:$A,">="&TODAY()-7*Settings!$B$6)
H  Weekly demand      =G2/Settings!$B$6
I  Last year, next 4 weeks, per week
   =SUMIFS(Sales!$E:$E,Sales!$B:$B,$A2,Sales!$C:$C,$B2,Sales!$D:$D,$C2,Sales!$A:$A,">="&EDATE(TODAY(),-12),Sales!$A:$A,"<"&EDATE(TODAY(),-12)+28)/4
J  Planning demand    =MAX(H2,I2)
K  Lead-time demand   =J2*F2/7
L  Safety stock       =J2/7*Settings!$B$2
M  Reorder point      =CEILING(K2+L2,2)
N  Position           =D2+E2
O  Reorder?           =IF(AND(J2>0,N2<=M2),"Reorder","OK")
P  Order quantity     =IF(O2="Reorder",CEILING(M2+J2*Settings!$B$3-N2,2),0)
Q  Last sale          =MAXIFS(Sales!$A:$A,Sales!$B:$B,$A2,Sales!$C:$C,$B2,Sales!$D:$D,$C2,Sales!$E:$E,">0")
R  Days since sale    =IF(Q2=0,"never",TODAY()-Q2)
S  Oldest DOT code (text, e.g. 0124)
T  Stock age (weeks)  =IF(S2="","",INT((TODAY()-(DATE(2000+VALUE(RIGHT(TEXT(S2,"0000"),2)),1,1)+(VALUE(LEFT(TEXT(S2,"0000"),2))-1)*7))/7))
U  Weeks of cover     =IF(J2=0,"no demand",D2/J2)
V  Stock flag         =TRIM(IF(AND(D2>0,OR(R2="never",R2>Settings!$B$4)),"Slow mover ","")&IF(AND(D2>0,T2<>"",T2>Settings!$B$5),"Old stock ","")&IF(AND(D2>0,J2>0,U2>Settings!$B$7),"Overstock",""))

Column I is the seasonal view: how fast this row sold during the four weeks that start one year ago today. Column J takes the higher of the recent run-rate and that seasonal rate, which is the simplest way to stop the sheet underbuying just before a season starts. Column Q looks only at lines with a positive quantity, so a returned tyre never counts as a recent sale, and columns R, T and U should be formatted as plain numbers, not dates. For a new shop with no history, add a column for a manual weekly demand and let J use it when filled, with a formula such as =IF(X2<>"",X2,MAX(H2,I2)).

The size-and-brand matrix: one tab to scan

The Stock tab is for editing and the Matrix tab is for looking. It lists sizes down the side and brands across the top, with on-hand counts in the cells, so whoever is on the counter can see at a glance whether a size exists in any brand. Fill sizes in column A and brands in row 1, then use one formula for every cell.

Matrix!B2:  =SUMIFS(Stock!$D:$D,Stock!$A:$A,$A2,Stock!$B:$B,B$1)

With the ten invented rows used below, the matrix reads as follows (on hand only; units on order are not counted here).

The matrix is also an honesty check. If a size you know you stock shows zero across every brand, the spelling in the Stock tab is probably different from the one in the Matrix, a problem that returns later in this tutorial.

Reorder point in plain arithmetic, and why tyres go up in pairs

A reorder point answers one question: at what stock level must I order so that the new tyres arrive before the shelf is empty? The answer has two parts. The first is demand during the supplier's lead time, which is the daily rate times the days between ordering and having the tyres on the rack. The second is safety stock, a cushion for weeks that sell faster than average. The Stock tab computes both and adds them in columns K to M, rounding up to the next pair.

Take one row from the worked example, 205/55R16 Brand B (summer). Over eight weeks it sold 40 tyres, which is 5 a week. Its supplier delivers in 2 days. Demand during the lead time is 5 times 2 divided by 7, or 1.43. The safety cushion is 4 days of demand, which is 5 divided by 7 times 4, or 2.86. Together they come to 4.29, which rounds up to a reorder point of 6. The shop holds 6 on the rack and has none on order, so its position (6) has reached the reorder point (6), and the sheet says Reorder. The order quantity rebuilds stock to the reorder point plus two weeks of demand: 6 plus 10, minus the 6 already held, is 10 tyres.

Two points explain the rounding to pairs. Most tyre sales are in twos or fours, so a single leftover tyre is a stranded asset until another one is sold. And some suppliers ship in pairs or fours. If your wholesaler ships in fours, change the 2 in the CEILING formulas to 4. The wider theory of reorder points, including how to estimate safety stock from sales variation, is covered in using AI to set reorder points and prevent stockouts; the tyre sheet uses the simplest version that works.

How many safety days to hold is a judgement, and 4 is only a starting point. A better estimate comes from your own history: compare your busiest recent week for a size with its average week, multiply the difference by the lead time in weeks to get a cushion in tyres, then divide by the average daily sales to express it in days. Take 215/60R16 Brand C from the worked example below, which averages 3 a week on a 7-day lead time: a busiest week of 6 gives a cushion of 3 tyres, and at 3 a week (about 0.43 a day) that is 7 days, nearly twice the default. If a few sizes need far more cushion than the rest, give them their own safety-days column. Tighten or loosen the cell after a quarter, once you have seen how often the shelf ran empty.

Seasonal sizes: last year's window beats the eight-week average

The most expensive mistake in a tyre stock sheet is to plan winter tyres from summer sales. Take 225/45R17 Brand A (winter). Over the past eight weeks it has sold 16 tyres, or 2 a week, because most customers wait for the first cold snap. A plain average would give a reorder point of 4, and with 8 on the rack the sheet would say everything is fine. Last year, in the four weeks starting today's date, the shop sold 60 of them, or 15 a week.

With column I in place, planning demand becomes 15 a week. Lead time is 5 days, so lead-time demand is 15 times 5 divided by 7, or 10.71, safety stock is 8.57, and the reorder point is 20. With 8 on hand and nothing on order, the sheet says Reorder, and the order quantity is 20 plus 30 (two weeks of demand), minus 8, which is 42 tyres. The plain average would have placed no order at all, and the shop would have discovered the mistake on the first frosty morning with a queue of customers at the counter. If you want to take those winter sales further, the tyre sales promotion ideas for each season show the offers that bring customers in at that moment.

Seasonal demand has a mirror image. After the season, the same column makes the sheet cautious about reordering a size that sold well last month but will not sell for another nine months. If last year's next four weeks were quiet, planning demand stays low, and the sheet will not tie cash up in stock the market has stopped buying.

Ten stock rows, one Monday morning: the worked reorder

Here is the complete picture for ten invented rows on an illustrative Monday at the end of October. Lead times are 2, 5 or 7 days depending on the supplier, and planning demand is the larger of the recent weekly rate and last year's seasonal rate. The sheet flags three rows for reordering and asks for 60 tyres in total.

Read the table the way you would on a Monday morning. The 60 tyres to order are 10, 42 and 8. The row for 225/45R17 Brand B shows why the on-order column matters: it holds 6 on the rack and 8 coming, so its position is 14 against a reorder point of 10, and the sheet rightly says OK. If the position formula had ignored units on order, it would have read 6, flagged a reorder and asked for 18 tyres that nobody needs.

The flags column tells you what to do with the other rows. The 215/60R16 row needs an order, but its four tyres in hand are 147 weeks old, so sell those first and tell the person at the counter. The 235/55R18 row has not sold for 140 days and is 243 weeks old: do not reorder it, and decide whether to clear it or return it to the supplier. The 175/65R14 row sells half a tyre a week with 16 on the rack, which is 32 weeks of cover, and the 245/40R18 row has just crossed the 104-week line at 106.

Flags for slow movers, old stock and overstock

The three flags in column V come from three different questions, which is why one flag column with a single rule is not enough. Slow mover asks whether anything has sold in the last 90 days. Overstock asks how many weeks the current stock would last at the planning rate, which catches the size that sells a little but not enough; using the planning rate rather than the recent one keeps winter stock from being flagged in the quiet weeks before winter. Old stock asks how many weeks have passed since the oldest tyre was made, using the DOT code and your own age limit. The 104 weeks in Settings is an example threshold, not a rule from any manufacturer.

Each flag has a different action. A slow mover is a candidate for a clearance offer or a supplier return, and the tutorial on finding dead stock and slow movers with AI covers the commercial side. Overstock means pausing orders and perhaps bundling the size into a promotion. Old stock means checking the batch, selling the oldest tyres first and deciding under your own policy what is still suitable to sell as new. None of the flags should trigger a price cut automatically: they start a decision, and you make it.

Three jobs for AI: write, audit and summarise

An assistant earns its place in this sheet in three jobs. The first is writing formulas from a plain description. The second is auditing a sheet for errors a tired eye misses. The third is turning the reorder list into the text you send to suppliers. Excel's own AI is moving too: the =COPILOT() worksheet function was retired on 14 September 2026, so use Excel's Copilot side pane or any chat assistant in another tab, as described in Excel COPILOT function retired: Copilot pane or an AI add-in.

Job one: write the formula. Describe the columns, the rule and the check you want, and ask for the anchors to be explained.

I have an Excel sheet called Stock with columns A Size, B Brand, C Season, D On hand, E On order, F Lead time (days), and a sheet called Sales with A Date, B Size, C Brand, D Season, E Qty. Settings!B6 holds the number of weeks of history (8).
Write the formula for Stock!G2: units sold in the last Settings!B6 weeks for the size AND the brand AND the season in A2, B2 and C2. Use SUMIFS. Tell me which references need $ anchors. Give me one check I can run to confirm the column is right.

A good answer returns the SUMIFS formula from the column list above, explains that the Sales ranges need anchors so the formula can be filled down, and suggests the tie-out check: the total of column G on Stock must equal the total of Sales quantity in the same window. Add that check in a spare cell on the Stock tab outside column G (Z1, say, since X is kept for manual demand), because it catches more errors than any other test.

Tie-out (should be 0):  =SUM(Stock!G:G)-SUMIFS(Sales!E:E,Sales!A:A,">="&TODAY()-7*Settings!B6)

Job two: audit the sheet. Paste the header row and the row 2 formulas as text, describe what each column should do, and ask for inconsistencies only.

Here are the column headings and the row 2 formulas of my tyre stock sheet, and a description of what each column should do. List anything that looks wrong or inconsistent: mixed units (days against weeks), references missing a $ anchor, text where a number is expected, columns that ignore on-order stock, and formulas that would break when copied down. Do not rewrite the sheet. For each issue give the column, the problem and the fix in one line.
[paste]

A realistic answer from an assistant looks like this, and shows the sort of findings worth taking seriously.

Column S: DOT codes appear to be stored as numbers. A code such as 0124 would lose its leading zero. Fix: store as text, and keep TEXT(S2,"0000") in the age formula.
Column G: the SUMIFS matches on size only. Fix: add the brand and season criteria, or every brand's row will show the size's combined sales.
Column K: lead time is divided by 7, so F must be in days, not weeks. Confirm F's heading says days.
Column P: order quantity rounds to pairs. Confirm your supplier does not ship in fours.

Treat each finding as a question to check against the sheet. The second one is a common and costly error, because it makes every brand in a size look as busy as all the brands put together.

Job three: turn the reorder list into supplier emails. Paste the Reorder rows with the supplier for each brand and ask for one email per supplier, with the quantities exactly as given and a one-line reason for each row that you can sanity-check before sending.

Write one order email per supplier from the rows below. Do not change any quantity. List each line as: size, brand, quantity. End with the total number of tyres. Then, separately and for me only, give a one-sentence reason for each line based on the figures.
ROWS: 205/55R16 | Brand B | 10 | Supplier 1 | weekly demand 5.0, ROP 6, position 6
225/45R17 | Brand A | 42 | Supplier 1 | weekly demand 15.0 (last year's seasonal rate; recent 2.0), ROP 20, position 8
215/60R16 | Brand C | 8 | Supplier 2 | weekly demand 3.0, ROP 6, position 4

EMAIL TO SUPPLIER 1
Hello [first name], please could you supply the following, for delivery as soon as possible:
205/55R16, Brand B, 10
225/45R17, Brand A, 42
Total: 52 tyres. Thanks, [your name], [shop]

EMAIL TO SUPPLIER 2
Hello [first name], please could you supply the following, for delivery as soon as possible:
215/60R16, Brand C, 8
Total: 8 tyres. Thanks, [your name], [shop]

REASONS (for me only)
205/55R16 Brand B: position has reached the reorder point of 6; orders back to 16.
225/45R17 Brand A: last year's seasonal rate of 15 a week is far above the recent 2; position 8 is well under the reorder point of 20.
215/60R16 Brand C: position 4 is under the reorder point of 6.

The reasons are the value in that output. If a one-line reason does not make sense, the row is probably wrong, and finding out before the order goes is much cheaper than finding out when 42 tyres arrive.

Where AI-built stock formulas go wrong, and how to test them

Most failures in a sheet like this are silent. The cells show numbers, the numbers look plausible, and the shop orders the wrong amount for a month before anyone notices. These five are the ones to test for.

1. The size-only SUMIFS. An assistant drops the brand and season criteria. In the worked example, 205/55R16 sold 104 tyres in the window across brands A and B. With the brand criterion missing, each row shows 104, so Brand B's weekly demand reads 13 and not 5. Its reorder point jumps to 12 and the order grows from 10 tyres to 32. The tie-out cell catches this at once, because it stops showing zero and reports how many tyres have been counted twice.
2. Size spellings that do not match. The till exports "205/55 R16 91V" but the Stock tab says "205/55R16", so SUMIFS finds nothing and demand reads zero. A zero demand gives a reorder point of zero, and the sheet quietly stops ordering the best-selling size. The tie-out cell goes negative in this case, which is your warning. Normalise the sizes first, either with an assistant or with a drop-down list in the Sales tab.
3. On-order ignored. A position formula that reads only on-hand stock reorders what has already been ordered, as the 225/45R17 Brand B row shows (18 tyres nobody needs).
4. Numbers stored as text, or text as numbers. A DOT code typed as a number drops its leading zero, an on-hand count pasted as text sums to zero, and a date pasted as text is ignored by the window test. The audit prompt above looks for both.
5. Newer functions in an older Excel. FILTER and MAXIFS depend on the version. If a formula returns #NAME?, the function is not available in that copy, so ask the assistant for an equivalent that works in your version.

The size-spelling problem has a quick fix with an assistant. Paste the distinct size strings from the till export and ask for one standard form, with anything uncertain marked for you to check.

Convert each tyre description to the format WIDTH/PROFILE+R+RIM, for example 205/55R16. Put any load index and speed rating in a separate column. If a line cannot be parsed with confidence, write CHECK in the size column. Do not guess.
205/55 R16 91V
205/55R16
195 65 15 T
225/45ZR17 94W

205/55R16 | 91V
205/55R16 |
195/65R15 | T   (note: R assumed; the original had no construction letter)
225/45R17 | 94W   (note: ZR in the original; confirm the rating with the supplier)

Notice the last two lines. The assistant says it assumed radial construction on the third and dropped the Z from ZR on the fourth, which is exactly the kind of loose end you want surfaced and not hidden. Strictly, your own rule asked for CHECK on the third line, so treat that assumption as unconfirmed until someone has read the tyre itself. Apply the fixed names to the till export and the Sales tab, then re-run the tie-out check.

Keeping the counts honest: adjustments, returns and a weekly count

A sheet is only as accurate as the counts it starts from. Tyres leave the building in ways a sales log does not record: warranty swaps, damaged stock, transfers to another shop, a pair set aside for a customer. Add an Adjustments tab with date, size, brand, season, quantity (negative for tyres that leave, positive for any that come back) and a reason, and apply each one to the on-hand count when you log it, so the count and the log always agree. Never overwrite on-hand with a typed number without logging why.

Once a week, count the ten fastest-moving rows by hand and compare them with the sheet. A difference of two on a row that sold 8 a week is information: perhaps a pair was moved without a record, or a sale was entered against the wrong brand. Log the variance and its likely cause. After a month, an assistant can read the adjustments log and tell you which cause is most common, which turns a weekly chore into a list of process fixes. The method for counting fast without stopping trade is in running a stocktake faster with AI and a phone scanner.

When the sheet should give way to a till or inventory system

A spreadsheet suits one person updating one file. It strains in five situations: several staff edit stock during the day and overwrite each other, you need barcode scanning at the point of sale, you hold stock across more than one site, the weekly paste from the till takes more than a short coffee break, or you spend more time fixing the sheet than reading it. At that point look at the inventory reports in your till or point-of-sale system, many of which can suggest reorder points, and port the rules across: the 8-week window, the seasonal view, the safety days and the three flags.

The logic transfers even when the tool changes, which is the reason to write the rules down before choosing software. If you decide the sheet is enough, the general ChatGPT and Excel guide covers how to work with an assistant on a workbook safely. Either way, test every new formula on one row by hand, keep the tie-out cell at zero and read the flags before you place an order.

Questions tyre shops ask about stock sheets and reorder points

How many weeks of sales history should the sheet use?

Eight weeks is a good start because it smooths one-off spikes while still reacting to change. Put the number in one settings cell so you can test six or twelve. For seasonal sizes, also look at the same weeks last year, because a recent average understates demand just before a season starts.

Why round reorder quantities to pairs?

Most tyre sales are in twos or fours, and some wholesalers ship that way. Rounding up to a multiple of two stops the sheet suggesting a single tyre that leaves a customer short of a pair. If your supplier ships in fours, change the rounding to four in the formulas.

Do I need a different row for each load index and speed rating?

If two tyres share a width, profile and rim but differ in load index or speed rating, give them separate rows when customers' vehicles may need one or the other. Check each vehicle's requirement before swapping one for the other. Keeping them separate also keeps their sales history and reorder points accurate.

Can I trust AI-written spreadsheet formulas?

Trust them after testing. Run three checks on every new formula: calculate one row by hand, confirm column totals tie back to the sales log, and look for text typed where a number belongs. The assistant is good at drafting and explaining formulas, and the checks catch the quiet mistakes.

Further reads

• Can AI Manage Salon Inventory? Colour Stock and Reorder Points — See reorder points applied to colour stock in a salon
• Boutique Inventory Management: AI for Size Runs and Reorders — Handle size runs and reorders in a clothing boutique
• Hardware Store Inventory Management: SKU Clean-Up With AI — Clean up messy SKUs before they break your formulas
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Pirelli's guides to tyre markings and the DOT date code (size, load index, speed rating, week-and-year date code, ageing even when unused), checked October 2026; Microsoft's note that the COPILOT worksheet function was retired on 14 September 2026. The stock, sales and lead times are invented for illustration.