Inventory and Warehouse Management System in Excel: Stock In, Stock Out, Days of Cover, Reorder, ABC and Challan

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: inventory-warehouse-management-system-template.xlsx: a 9-sheet system with example data and real formulas (Read me, Setup, Items, Stock in, Stock out, Stock, Delivery challan, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Salman Distributors, Nagpur, 1 to 17 October 2026 (made-up data). 10 SKUs in four categories, 7 goods received notes (GRNs) and 8 delivery challans.

1,069 units went out on 8 challans. The one-sheet idea is in 21.3 inventory reorder sheet; days of cover at company scale is in the supply chain project. A shop with a billing counter should use the shop billing and inventory system.

2. Build it sheet by sheet

Step 1. Setup and Items

1. On Setup type the as-of date in B3, the period start in B4 and safety days in B6 (15). B5 counts days in the period: =B3-B4+1 (17).
2. On Items row 4: SKU, Item, Category, Bin, Unit cost, Opening stock, Reorder level, Lead time (days). Category has a drop-down from Setup.
3. Lead time is how many days the supplier takes. It decides when to order more than the reorder level does.

Step 2. Stock in and Stock out: the two logs

1. Stock in: Date, GRN no, SKU, Item, Qty, Value, Supplier. D5 finds the item name =XLOOKUP(C5,Items!$A$5:$A$14,Items!$B$5:$B$14,"Check SKU"); F5 value = qty × cost.
2. Stock out: Date, Challan no, Customer, SKU, Item, Qty, Value at cost, Key. One row per SKU per challan.
3. H5 numbers the lines of each challan: =B5&"-"&COUNTIF($B$5:B5,B5) gives DC-107-1, DC-107-2 and so on.

Step 3. Stock: closing, cover and reorder

1. Row 4 headers as in the picture. D5 in =SUMIFS('Stock in'!$E$5:$E$500,'Stock in'!$C$5:$C$500,A5), E5 out the same way on Stock out (SUMIFS), F5 closing =C5+D5-E5.
2. H5 daily use =E5/Setup!$B$5 and I5 days of cover =IFERROR(F5/H5,999). 999 means "not moving".
3. K5 order now: =IF(OR(F5<=Items!G5,I5<=J5),"Yes","No"). Shampoo has 6.2 days of cover but a 12-day lead time, so it is Yes even though 8 is under the reorder level anyway.
4. L5 suggested order =IF(K5="Yes",MAX(0,ROUNDUP(H5*(J5+Setup!$B$6)-F5,0)),0): enough for the lead time plus 15 safety days.

Step 4. ABC class

1. M5 use value =E5*Items!E5 (units out × cost).
2. N5 class: =IF(SUMIF($M$5:$M$14,">="&M5)/SUM($M$5:$M$14)<=0.7,"A",IF(…<=0.9,"B","C")). SUMIF with ">=" adds this item and every bigger one, which is the running share without sorting.
3. A items (Biscuits, Soap, Shampoo, Tea here) get a weekly count; C items once a month.

Step 5. Delivery challan

1. Type a challan number in B4. B5 and D5 pull the date and customer with XLOOKUP.
2. Rows 8 to 15: B8 =XLOOKUP($B$4&"-"&A8,'Stock out'!$H$5:$H$500,'Stock out'!$D$5:$D$500,"") returns line 1's SKU; the bin comes from Items so the picker knows where to go.
3. E17 totals the quantity. Print A1:E20 (print area).

Step 6. Report and dashboard

1. Report rows 5 to 8 by category: SKUs, stock value, use value (SUMIFS) and items to order (COUNTIFS). Rows 11 to 13 count A, B and C items.
2. Dashboard cards: stock value, items to order, lowest cover =MIN(Stock!I5:I14), the item with lowest cover via INDEX and MATCH, units dispatched and number of challans.
3. Chart 1 at A8: days of cover by item. Chart 2 at A25: stock value by category.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Typing closing stock. The moment someone types a number in Closing, the logs and stock stop agreeing. Fix the log instead.
2. Ordering only at the reorder level. A supplier with a long lead time needs an earlier order. Compare days of cover with lead time too.
3. One row per challan with many items. SUMIFS needs one SKU per row. A challan with 4 items is 4 rows.
4. Mixing units. If Items says carton, every log row must be cartons, not pieces.
5. Never counting. Do a cycle count of a few bins every day; adjust with a log row marked "count difference".

5. More real examples

Example 1: Spare parts store in a factory. Stock out becomes issue to machine. Add a Machine column and a cost report by machine with SUMIFS.

Example 2: Pharma distributor. Add lot number and expiry columns on Stock in and a near-expiry flag, like the pharma project.

Example 3: Two warehouses. Add a Location column on both logs and closing stock per location with SUMIFS on SKU and location.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate closing stock in Excel?

Opening + SUMIFS of stock in − SUMIFS of stock out for that SKU: =C5+D5-E5. Never type it.

What is days of cover?

Closing stock ÷ average daily use. 60 units with 7 a day used is about 8.5 days before you run out.

How much should I reorder?

One simple rule: daily use × (lead time + safety days) − closing stock, rounded up. The safety days are your choice; 15 is an example.

How do I do ABC analysis without sorting?

Use value = units out × cost. SUMIF(range,">="&M5)/SUM(range) gives the running share; up to 70% is A, up to 90% B, the rest C.

Can I print a delivery challan?

Yes. Type the challan number on the Delivery challan sheet; the line key fills up to 8 lines.

7. Quick quiz

Ten questions on this page. Sign in to save your score.