The Excel formulas behind a free inventory tracker for small shops

Most small shops in Pakistan don't need software on day one. They need to know three things: what's left on the shelf, how much they made, and what to reorder.

I built a free Excel sheet that answers those, and this post walks through the handful of formulas that make it work - useful if you ever build a tracker for a family business or a client. Download the sheet free to follow along.

The layout: three input sheets, one dashboard

• Products - one row per item: SKU, name, cost price, sale price, opening stock, reorder level
• Purchases - one row per delivery: date, SKU, quantity, unit cost
• Sales - one row per sale: date, SKU, quantity, price, payment method
• Dashboard - calculated, nothing typed

The shop owner only ever adds rows. Everything else is a formula, so nothing gets overwritten.

1. Stock in = opening + bought − sold

On the Products sheet, for the SKU in column A:

Purchased (H):

=IF(A2="","",SUMIFS(Purchases!C:C,Purchases!B:B,A2))

Sold (I):

=IF(A2="","",SUMIFS(Sales!C:C,Sales!B:B,A2))

In stock (J):

=IF(A2="","",F2+H2-I2)

SUMIFS adds up the quantity column wherever the SKU matches. The IF(A2="","",…) wrapper keeps empty rows blank instead of showing zeros all the way down.

2. Look up the product name and cost from the SKU

On the Sales sheet, the owner types only the SKU; the name and cost fill in:

Product (G):

=IF(B2="","",IFERROR(INDEX(Products!B:B,MATCH(B2,Products!A:A,0)),"Unknown SKU"))

Cost (H):

=IF(B2="","",IFERROR(C2*INDEX(Products!D:D,MATCH(B2,Products!A:A,0)),0))

Profit (I):

=IF(B2="","",E2-H2)

INDEX/MATCH works in every Excel version and in Google Sheets. "Unknown SKU" makes typos obvious instead of silently wrong.

3. Status and a red row when it's time to reorder

Status (M):

=IF(A2="","",IF(J2<=0,"Out of stock",IF(J2<=G2,"Reorder","OK")))

Then conditional formatting on the whole table with the formula =AND($A2<>"",$J2<=$G2) and a light red fill. The owner doesn't read a report; the row just turns red.

4. The dashboard: today and this month

Sales today:

=SUMIFS(Sales!E:E,Sales!A:A,TODAY())

Sales this month:

=SUMIFS(Sales!E:E,Sales!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Profit this month:

=SUMIFS(Sales!I:I,Sales!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Stock value:

=SUM(Products!K:K)

To reorder:

=COUNTIF(Products!M:M,"Reorder")

">="&DATE(...) builds the criterion "on or after the 1st of this month" as text, which is how SUMIFS takes comparisons.

5. Small touches that matter to non-technical users

• White cells for input, grey cells for formulas, and a "Start here" sheet that says so in one line.
• A dropdown for payment method (Cash, Card, Bank transfer, JazzCash, Easypaisa, Credit) using data validation, so the cash total on the dashboard is always right.
• Frozen header rows so the column names never scroll away.

When a spreadsheet stops being enough

A sheet breaks down when there are two counters, more than one branch, or staff who shouldn't see profit. That's what Tilly POS is for. But for a shop that's just starting, these few formulas do the job - and the sheet is free: Shop Inventory & Sales Tracker.