Inventory Management in Excel for Indian SMBs

Last updated: 27 June 2026

Excel is the most widely used inventory tool in Indian small businesses — because it works offline, needs no subscription, and every accountant already knows it. This guide covers every layer of stock management in Excel: the stock register, reorder formulas, FIFO valuation, GST-linked records, expiry tracking, and multi-godown sheets — with free downloadable templates for each.

Key takeaways

• A well-structured Excel stock register can replace basic inventory software for most businesses with fewer than 5,000 SKUs.
• Use SUMIF or XLOOKUP to calculate live stock balances from transaction data — never just edit a running total manually.
• Indian businesses need a GST-linked stock register that records the HSN code, tax rate and ITC-eligible status of each item.
• Set a reorder point (ROP) using lead time and daily usage so Excel flags low-stock before you run out.
• FIFO and weighted average are both permitted for tax (s.145A, ICDS II, AS-2); LIFO is not allowed in India. FIFO is preferred — not required — for perishables and pharma.
• Expiry tracking in Excel using TODAY() and conditional formatting is sufficient for pharma/FMCG businesses with moderate SKU counts.
• Multi-godown tracking is possible in Excel using a single master sheet with a "Location" column.

Fact box. An Indian SMB stock register should record, at minimum: item code, description, HSN code, unit of measure, opening stock, receipts (GRN), issues/sales, closing stock and the GST rate applicable to each item. Without the HSN column, the register cannot support GSTR-1 filing or ITC reconciliation.

What is a stock register and why does every Indian SMB need one?

A stock register is a running record of every item you hold — what came in, what went out, and what balance remains. For Indian businesses it serves three purposes at once:

1. Operations: Know when to reorder; avoid stockouts and overstocking.
2. Accounts: Value closing stock accurately for P&L and balance sheet.
3. Tax compliance: Reconcile purchases (ITC claims) with sales (GSTR-1 outward supplies); support GST audit if required.

Under GST, the stock register is an implied mandatory record. Rule 56 of CGST Rules requires every registered person to maintain accounts of production, manufacture, purchase, supply and stock of goods.

Fact box. CGST Rule 56 requires every GST-registered business to keep accounts of stock of goods in hand, production, manufacture, purchase and supply. There is no mandated format, so an Excel register is fully acceptable provided it covers the required data fields.

What should a basic inventory sheet include?

At minimum, your Excel stock register needs these columns:

The HSN column is often missing in SMB registers — without it you cannot reconcile stock consumed with GSTR-1 outward supply data by HSN.

How do I calculate live stock balance in Excel?

Use SUMIF against a transactions table — never edit a running total by hand, because that breaks auditability.

Setup (two-sheet model):

Sheet 1 — Transactions (one row per movement):

Sheet 2 — Stock Summary (one row per SKU):

Closing Stock (Qty) =
  Opening_Qty
  + SUMIF(Transactions[Item Code], [@ItemCode], Transactions[Qty_IN])
  - SUMIF(Transactions[Item Code], [@ItemCode], Transactions[Qty_OUT])

If you use a single "Type" column with IN/OUT:

=Opening
  + SUMIFS(Trans[Qty], Trans[ItemCode], [@Code], Trans[Type], "IN")
  - SUMIFS(Trans[Qty], Trans[ItemCode], [@Code], Trans[Type], "OUT")

This means your stock balance recalculates automatically whenever you add a new transaction row.

How do I set a reorder point (ROP) in Excel?

The reorder point is the stock level at which you place a new purchase order. The formula is:

ROP = (Average Daily Usage × Lead Time in Days) + Safety Stock

In Excel, create two helper columns on your stock summary sheet:

=Daily_Avg_Usage * Lead_Time_Days + Safety_Stock

Then add a status column:

=IF(Closing_Stock <= ROP, "REORDER NOW", "OK")

Apply conditional formatting (red fill) to that cell when the value is "REORDER NOW" for a visual alert that is visible without reading every row. See the dedicated guide: Low-Stock Alert Formula in Excel »

What valuation method should I use — FIFO or weighted average?

Both are valid under Indian accounting standards. The choice affects your closing stock value and therefore your taxable profit.

For a step-by-step Excel implementation of both, see FIFO & Weighted-Average Stock Valuation in Excel »

How do I track GST in my stock register?

Your stock register and your GST returns must agree. Key links:

• Purchase receipts feed GSTR-2B (your input tax credit). Record the supplier's GSTIN, invoice number, date, HSN code and tax amount on every GRN row.
• Sales issues feed GSTR-1 (outward supplies). Record HSN, quantity, taxable value, CGST/SGST or IGST on every sales row.
• ITC eligibility: Not all stock carries eligible ITC. Mark items that fall under Section 17(5) blocked credits (e.g. items for personal use, certain food items) in a separate column.

For a complete template and formula set, see Stock Register with GST in Excel »

What Excel techniques work best for Indian inventory?

Dropdown lists (Data Validation)

Use Data Validation → List for the Item Code and GST Rate columns. This prevents typos that corrupt your SUMIF results. GST Rate dropdown should list: 0, 5, 18, 40 (the four current slabs from 22 Sep 2025).

Named ranges

Name your transactions table Trans and your summary table Stock. This makes formulas readable and easier to audit.

Conditional formatting

• Red when Closing Stock ≤ ROP (reorder alert).
• Orange when days to expiry ≤ 30 (for pharma/FMCG).
• Yellow when stock is zero (stockout).

Pivot tables for reports

A pivot table on the transactions sheet gives you:

• Stock movement by item, by supplier, by date range.
• Category-wise stock value.
• Top-moving vs. slow-moving items.

Can I track inventory across multiple godowns in Excel?

Yes — add a "Location" column to your transactions sheet. Then use SUMIFS with both item code and location to get godown-wise balances:

=SUMIFS(Trans[Qty_IN], Trans[ItemCode], [@Code], Trans[Location], "Mumbai Godown")
- SUMIFS(Trans[Qty_OUT], Trans[ItemCode], [@Code], Trans[Location], "Mumbai Godown")

A pivot table gives a cross-tab view (items × locations) instantly. For a complete walkthrough, see Multi-Warehouse / Godown Stock Sheet in Excel »

What are the limits of Excel for inventory?

Excel works well up to a point. Watch for these breaking points:

How Ankeshan helps: Ankeshan runs natively inside Excel and adds a live GST-rate table, automatic GSTR-1/3B reconciliation and reorder alerts — all in the spreadsheet you already know. It's launching soon; join the waitlist.

Guides in this cluster

Frequently asked questions

Is Excel a legal stock register for GST purposes? Yes. CGST Rule 56 requires stock accounts to be maintained but does not mandate a specific software or format. An Excel register that records the required fields (item, HSN, quantities, values) is fully acceptable. Keep it backed up and available for audit.

Which GST rates apply to stock items in 2026? From 22 September 2025, India has four main GST slabs: Nil (0%), 5%, 18% and 40%. The earlier 12% and 28% slabs were abolished. Use the CBIC rate finder to confirm the rate for each HSN code before setting it in your register.

How many digits of HSN code do I need in my stock register? If your aggregate annual turnover is up to ₹5 crore, use 4-digit HSN codes. Above ₹5 crore, use 6-digit codes. This aligns with the GSTR-1 Table 12 reporting requirement.

Can I use a single Excel file for multiple godowns? Yes. Add a "Location" column and use SUMIFS to filter by both item code and location. A pivot table gives you a real-time cross-tab of stock by godown. See the multi-godown guide.

What is the best valuation method for my Excel stock register? FIFO and weighted average are both permitted under Indian tax and accounting standards (s.145A, ICDS II, AS-2); only LIFO is disallowed. FIFO is strongly preferred and standard practice for items with expiry dates (pharma, FMCG, food), while weighted average is simpler for general trading and manufacturing. Pick one and apply it consistently within a financial year.

Do I need to maintain batch numbers in my stock register? Only if you deal in pharma, food, or other regulated goods. However, any business where expiry matters should track batch numbers and manufacturing/expiry dates. See Batch & Expiry Tracking in Excel ».

Sources

• CGST Rules 2017, Rule 56 — Accounts and Records (cbic.gov.in).
• CBIC GST rate notifications, effective 22 September 2025 (56th GST Council).
• GSTN — HSN reporting requirements, Table 12, GSTR-1 (gstn.org.in).
• CBIC e-invoicing portal: einvoice.gst.gov.in.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: GST in Excel pillar » · Stock Register with GST in Excel » · FIFO & Weighted-Average Valuation » · Low-Stock Alert Formula »