Hardware Store Management System in Excel: GST Invoices, Contractor Credit, Ageing, Stock and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: hardware-store-management-system-template.xlsx: a 10-sheet system with example data and real formulas (Read me, Setup, Items, Customers, Invoices, Payments, Ledger, Stock, Tax invoice, 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

Amir Hardware, Sambhaji Nagar, August to October 2026 (made-up data). Six items, three contractors and cash sales. GST rates are inputs; confirm each item's rate.

Sales before GST are ₹2,14,445 (₹73,185 in October) with a gross margin of ₹42,240. For a simple shop counter, see the shop billing system; for one ledger without GST, the khata book.

2. Build it sheet by sheet

Step 1. Items and Customers

1. Items: Code, Item, Unit, Cost, Price, GST rate, Opening, Reorder level. Keep each GST rate as an input and check it against the current rate list.
2. Customers: ID, Name, Phone, Credit limit, Type. Cash customer has a limit of 0.

Step 2. Invoices: one row per line

1. Headers: Date, Invoice no, Customer ID, Code, Item, Qty, Price, Taxable, GST, Total, Cost, Key. E5 fills the item with XLOOKUP.
2. H5 taxable =F5*G5, I5 GST =ROUND(H5*XLOOKUP(D5,Items!$A$5:$A$10,Items!$F$5:$F$10,0),2), J5 total =H5+I5.
3. K5 cost of goods and L5 the key =B5&"-"&COUNTIF($B$5:B5,B5) for the printable invoice.

Step 3. Ledger: outstanding and ageing

1. C5 billed =SUMIFS(Invoices!$J$5:$J$3000,Invoices!$C$5:$C$3000,A5), D5 paid from Payments, E5 outstanding =C5-D5.
2. F5 0-30 days: =MIN(E5,SUMIFS(…,Invoices!$A$5:$A$3000,">="&(Setup!$B$3-30))). MIN keeps a bucket from showing more than is owed, since payments clear the oldest bills first.
3. G5 31-60 days the same way with MIN(E5−F5,…), and H5 the rest, over 60 days.
4. J5 flag: =IF(AND(I5>0,E5>I5),"Over limit",IF(H5>0,"Over 60 days","OK")).

Step 4. Stock and Tax invoice

1. Stock: D5 sold with SUMIFS, E5 closing, F5 order =IF(E5<=Items!H5,"Yes","No") (reorder sheet), H5 gross margin and I5 margin %.
2. Tax invoice: type the invoice no in B4; B8 =XLOOKUP($B$4&"-"&A8,Invoices!$L$5:$L$3000,Invoices!$E$5:$E$3000,"").
3. Totals split GST half and half into CGST and SGST for a sale inside Maharashtra. AH-026 for Salman Builders: taxable ₹9,100, CGST ₹819, SGST ₹819, total ₹10,738.

Step 5. Dashboard

1. KPI cards: sales before GST, October sales =SUMIFS(Invoices!$H$5:$H$3000,Invoices!$A$5:$A$3000,">="&DATE(2026,10,1)), margin %, outstanding, over 60 days and flagged customers.
2. Charts: outstanding by customer and sales by item. For a full GST register, see 21.5.

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. Ageing without MIN. If a contractor paid most dues, the last 30 days of bills may be more than what is owed. MIN caps each bucket at the balance.
2. One GST rate for every item. Pipes, paint and tools can have different rates. Keep the rate per item and confirm it.
3. Typing payments on the invoice row. Payments often cover several bills. One Payments row per receipt keeps the ledger right.
4. Cash sales left out. Bill them to a Cash customer so stock and sales stay complete.
5. IGST for a sale inside the state. Inside Maharashtra the GST splits into CGST and SGST; IGST applies to sales to another state.

5. More real examples

Example 1: Electrical shop. Same layout with wires, switches and fittings; add a Size and rating column for wires and switches.

Example 2: Building material supplier. Add a Site column on Invoices and a site-wise report, as in the construction site system.

Example 3: Paint dealer. Add a Shade column and track tinting charges as a separate item.

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 ageing report in Excel?

SUMIFS bills by date band (last 30 days, 31 to 60, older), and cap each band with MIN so the buckets add up to the outstanding.

How do I split GST into CGST and SGST?

For a sale inside the state, half the GST is CGST and half is SGST: ₹1,638 becomes ₹819 + ₹819.

How do I flag customers over their credit limit?

=IF(AND(limit>0,outstanding>limit),"Over limit",…), as in Ledger J5.

Are the GST rates in the template current?

They are example inputs. Check the rate for each item with your accountant and type it on Items.

How do I show sales for one month?

SUMIFS with a date condition, for example ">="&DATE(2026,10,1).

7. Quick quiz

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