What is an inventory format in Excel?
An inventory format shows what stock you have, what to buy and what your stock is worth. In a factory, it covers raw material, bought-out parts, consumables and finished goods.
It works best as three sheets in one Excel file:
- Item Master: one row per item, filled once.
- Stock Movements: one row per GRN, issue slip, production entry or invoice.
- Stock Summary: one row per item, with closing stock, value and reorder status by formula.
What each column in the inventory format means
Item Master columns
| Column | What to enter | Example |
|---|---|---|
| Item code | One unique code per item. Never reuse it. | RM-001 |
| Item name | Name with size or grade. | MS sheet 2 mm |
| Category | Raw material, bought-out part, consumable, finished goods or scrap. | Raw material |
| Unit | One unit per item, used in every entry. | kg |
| Rate (₹) | The rate you value stock at. Keep one method all year. | 62 |
| Reorder level | The stock level at which you buy again. | 500 |
Stock Movements columns
| Column | What to enter | Example |
|---|---|---|
| Date | The day the material moved. | 03-10-2026 |
| Document no. | GRN, issue slip, production entry, invoice or challan number. | GRN-0412 |
| Item code | Picked from an Item Master drop-down list. | RM-001 |
| Movement type | Stock in, issue, production output, dispatch, return or adjustment. | Stock in |
| Qty in / Qty out | Quantity in the item's unit. Fill only one. | 2,000 in |
| Store | Where it sits, if you have more than one godown. | Main store |
| Remarks | Supplier, job worker, batch or adjustment reason. | Batch no. |
Stock Summary columns
One row per item. Item details come from the Item Master, and opening stock from a physical count or last month's closing. Stock in, stock out, closing stock, value and status are formulas. See the difference between opening stock and closing stock.
Excel formulas for closing stock, stock value and reorder alerts
The formulas use this layout. Summary sheet: A item code, E opening, F in, G out, H closing, I rate, J value, K reorder level, L status. Movements sheet: A date, C item code, E qty in, F qty out, G store.
What is the formula for closing stock in Excel?
Closing stock: =E2+F2-G2. Stock value: =H2*I2. Copy both down, then total column J for the value of all your stock.
Totals of stock in and stock out
Stock in: =SUMIFS(Movements!E:E,Movements!C:C,A2). Stock out: =SUMIFS(Movements!F:F,Movements!C:C,A2). Pull item names from the Item Master with XLOOKUP, or VLOOKUP in older Excel.
How do I set a reorder alert in Excel?
In column L, enter =IF(H2<=K2,"Reorder","OK"). To colour the row red, go to Home → Conditional Formatting → New Rule → Use a formula, and enter =$H2<=$K2. To set the reorder level itself, see our minimum stock level guide.
Can Excel handle multiple stores or godowns?
Yes. Add a store condition to SUMIFS, for example =SUMIFS(Movements!E:E,Movements!C:C,A2,Movements!G:G,"Main store"). Log a transfer as an out from one store and an in to the other.
How do I maintain daily stock in Excel?
Enter every movement on the day it happens, with its document number. For stock on a past date, add Movements!A:A,"<="&$N$1 to SUMIFS, with the date in N1.
How to make and fill the inventory format, step by step
- Give each item one code. Check the Item Master before adding a new one.
- Fix one unit per item. If you buy sheets but issue by kg, convert to kg on entry.
- Take opening stock from a physical count.
- Log every movement the same day with its goods received note, issue slip or invoice number. Between stores, use a material transfer note.
- Never type over formula cells. Lock them with Review → Protect Sheet.
- Count physically each month. Post differences as adjustments, as our stock reconciliation guide shows.
If you also make what you stock, use our bill of materials format for each product.
When Excel stops keeping up, and where TranZact fills it in for you
Excel slips when several people edit the file, stock sits in more than one store, or closing stock stops matching the count.
TranZact is manufacturing AI software, used by 10,000+ manufacturers. Each column of this sheet maps to a feature in its inventory management.
| In your Excel sheet | In TranZact |
|---|---|
| Stock in rows with GRN numbers | Stock in, via MRP report → indent → RFQ → PO → GRN |
| Issue to production rows | Issue |
| One item's movement history | Bin card |
| Store / godown column | Multi-store |
| Reorder level and IF formula | Reorder and AI stock alerts |
| Closing stock × rate | Inventory ageing & valuation |
| Batch numbers in remarks | Batch tracking (batch-wise stock on Scale+ and above) |
| "At job worker" store | Sub-contracting |
| Copying figures to your accounts | One-way sync to Tally or Zoho Books, with no double entry |
Accounts stay in Tally, and operations run in TranZact. It also integrates with Busy, Gmail, Excel and Google Sheets.
The Grow plan is ₹8,333/month billed yearly for 4 users. It includes stock tracking, inventory ageing & valuation and 65+ reports. There is no free plan; see all plans and prices. TranZact goes live in 2–3 weeks, and your data stays in India on AWS servers in Mumbai.
[[IMAGE {"type":"steps","title":"One month in the stock sheet","items":["Count stock to set opening balance","Log each GRN, issue and dispatch daily","SUMIFS totals stock in and out","Status flags items at reorder level","Count again and post adjustments"]}]]
Worked example: one month of stock at a small fabrication unit
This unit makes electrical panel enclosures from MS sheet and angle. All numbers are illustrative.
Table 1: Item Master
| Item code and name | Category | Unit | Rate / reorder level |
|---|---|---|---|
| RM-001 MS sheet 2 mm | Raw material | kg | ₹62 / 500 |
| RM-002 MS angle 40x40x5 mm | Raw material | kg | ₹58 / 300 |
| BO-001 M10 hex bolt | Bought-out part | nos | ₹4 / 1,000 |
| CN-001 Welding electrode 3.15 mm | Consumable | kg | ₹180 / 20 |
| FG-001 Electrical panel enclosure | Finished goods | nos | ₹2,400 / 10 |
Table 2: A few of the month's movements
| Date and document | Item | Movement | Qty |
|---|---|---|---|
| 3 Oct, GRN-0412 | RM-001 MS sheet | Stock in | +2,000 kg |
| 6 Oct, ISS-0218 | RM-001 MS sheet | Issue to production | −1,300 kg |
| 20 Oct, PRD-0040 | FG-001 Enclosure | Production output | +40 nos |
| 27 Oct, INV-1187 | FG-001 Enclosure | Dispatch | −36 nos |
Table 3: Stock Summary at month end
| Item | Opening + in − out = closing | Stock value | Status |
|---|---|---|---|
| RM-001 MS sheet | 1,200 + 2,000 − 2,600 = 600 kg | ₹37,200 | OK |
| RM-002 MS angle | 450 + 500 − 700 = 250 kg | ₹14,500 | Reorder |
| BO-001 M10 bolt | 3,000 + 0 − 1,800 = 1,200 nos | ₹4,800 | OK |
| CN-001 Electrode | 35 + 25 − 42 = 18 kg | ₹3,240 | Reorder |
| FG-001 Enclosure | 8 + 40 − 36 = 12 nos | ₹28,800 | OK |
Total stock value is ₹88,540. MS angle and welding electrodes are at or below their reorder levels, so both go on this week's purchase list.
What GST expects your stock record to show
Under CGST Rule 56(2), registered businesses other than composition taxpayers must keep a stock account. It shows opening balance, receipts, supplies and closing balance, plus goods lost, stolen, destroyed, written off, or given as gifts or free samples. It covers raw materials, finished goods, scrap and wastage. In this format, receipts are stock in and supplies are dispatches. Losses, write-offs and samples are adjustments with the reason in remarks. Confirm the details with your CA.
Common mistakes in an inventory sheet
- Two codes for one item. Stock splits, and neither total is right.
- Mixed units. Kg one day and sheets the next makes closing stock meaningless.
- Issues to production not logged. The sheet shows more raw material than the store holds.
- Job-work material marked as used. It is still your stock. Move it to an "At job worker" store with the challan number.
- Typing over formulas. One typed number breaks every month after it.
- No document number. A wrong figure can't be traced back.
- No physical count. Small gaps grow into big ones.
Keep the sheet, or let the system keep it
Start with the free format today and keep it honest with a monthly count. When the file needs more chasing than it saves, see your own items running in TranZact.
Don't be the bottleneck. Become the AI-Run Factory.