Shop Billing and Inventory System in Excel: Items, Purchases, Sales, Stock Reorder, Profit and Daily Cash Book

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: shop-billing-inventory-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Items, Purchases, Sales, Stock, Cash book, 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

Rani Kirana Stores, 1 to 17 October 2026 (made-up items and prices).

Milk sells the most (₹5,250) but wheat atta earns the most profit, because margins differ. For customers who buy on credit, add the khata book; the one-sheet version of reorder levels is the brief 21.3 inventory reorder sheet.

2. Build it sheet by sheet

Step 1. Items: the master

1. On Items, type the as-of date in B3. From row 6: Item code, Item, Unit, Buy price, Sell price, Opening stock (1 Oct), Reorder level.
2. Margin H6: =(E6-D6)/E6; colour it red below 10% so thin-margin items stand out.

Step 2. Purchases and Sales

1. On Purchases, type date, item code and qty. Item C5: =XLOOKUP(B5,Items!$A$6:$A$15,Items!$B$6:$B$15,"Check code"); rate E5 from the buy price; amount F5 =D5*E5 (XLOOKUP).
2. On Sales, the same with the sell price. Amount F5 =D5*E5, cost G5 =D5*XLOOKUP(B5,Items!$A$6:$A$15,Items!$D$6:$D$15,0), profit H5 =F5-G5, and the payment (Cash or UPI) from a drop-down (drop-down lists).

Step 3. Stock: closing and reorder

1. On Stock, per item: opening =Items!F6, purchased D5 =SUMIFS(Purchases!$D$5:$D$300,Purchases!$B$5:$B$300,A5) and sold E5 =SUMIFS(Sales!$D$5:$D$500,Sales!$B$5:$B$500,A5) (SUMIFS).
2. Closing F5 =C5+D5-E5; stock value H5 =F5*Items!D6; reorder I5 =IF(F5<=G5,"Yes","No").
3. Highlight the whole row with a formula rule =$I5="Yes" (formula rules). Toor dal: 40 + 0 − 32 = 8, reorder level 10: Yes.
4. Sales J5 and profit K5 per item with SUMIFS; totals in row 15.

Step 4. Cash book

1. On Cash book, type the cash in the drawer on the first morning in B3.
2. Opening B6 =B3, then B7 =F6: today opens with yesterday's close. Cash sales C6: =SUMIFS(Sales!$F$5:$F$500,Sales!$A$5:$A$500,A6,Sales!$I$5:$I$500,"Cash"); UPI D6 the same with "UPI".
3. Type cash spent in E6; closing F6 =B6+C6-E6. UPI goes to the bank, so it is shown but not added to the drawer.

Step 5. Dashboard

1. KPI cards: sales =Stock!J15, gross profit =Stock!K15, margin =B5/A5, items to reorder =COUNTIF(Stock!I5:I14,"Yes"), stock value and the latest cash =INDEX('Cash book'!F6:F8,COUNT('Cash book'!F6:F8)) (KPI cards).
2. Most profitable item: =INDEX(Stock!B5:B14,MATCH(MAX(Stock!K5:K14),Stock!K5:K14,0)) (INDEX and MATCH). Charts: profit by item and closing stock.

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 by hand. Closing stock must come from opening + purchased − sold. Count the shelf monthly and compare; a difference means a missed entry or loss.
2. Judging items by sales alone. Milk sells the most but earns less than wheat atta. Look at the Profit column before deciding what to push.
3. Adding UPI to the cash drawer. UPI money goes to the bank. Only cash sales minus cash spent change the drawer.
4. Using today's buy price for old stock value. Fine for a small shop, but if prices jump, note that stock value is at the current buy price in Items.
5. No reorder level for items that sell rarely. Every item needs a reorder level, even salt. Without it, the flag can never turn on.

5. More real examples

Example 1: Medical store. Add Lot no and Expiry to Purchases and a near-expiry flag; see the medicine stock reminder for the home version.

Example 2: Stationery shop at exam time. Raise reorder levels for the season in Items; the flags update at once.

Example 3: Vegetable stall. Use kg with two decimals and a daily Wastage column in Stock that is subtracted from closing.

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 make an inventory sheet in Excel?

Keep an item master, log purchases and sales as rows, and compute closing stock = opening + SUMIFS(purchased) − SUMIFS(sold) for each item.

How do I flag items to reorder?

=IF(closing<=reorder level,"Yes","No") with a conditional formatting rule that colours the whole row.

How do I calculate profit per item?

Profit on each sale = qty × (sell price − buy price); total it per item with SUMIFS.

How do I keep a cash book in Excel?

Each day: opening cash (yesterday's closing) + cash sales − cash spent = closing cash. Keep UPI separate.

Can I print a bill for a customer?

Yes: add a bill number to Sales and a print sheet like the one in the restaurant system.

7. Quick quiz

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