How to use it
- Add your shop name. Type your shop name if you want it at the top of each sheet. It is optional.
- Download the template. Press Download .xlsx under the register you need. The file is made in your browser and saves to your Downloads folder.
- Type over the sample rows. Open it in Excel or Google Sheets, delete or type over the sample lines, and fill only the white columns. The blue columns (closing stock, balances, GST) calculate themselves.
- Check the red cells. Stock at or below the reorder level shows REORDER in red. In the udhaar khata, overdue customers turn red, and the Customer dues sheet shows each person's total.
What each template calculates
| Template | You type | It calculates |
|---|
| Stock register | Item, opening stock, stock in, stock out, reorder level, cost price | Closing stock, REORDER / OK status, stock value, items to reorder |
|---|
| Udhaar khata | Date, customer, credit given, payment received, pay-by date | Balance per customer, days overdue, total due, customer-wise dues sheet |
|---|
| Daily cash book | Opening cash, cash in, cash out, category | Running balance and cash that should be in the drawer |
|---|
| GST purchase register | Supplier, GSTIN, invoice, HSN, taxable value, rate, intra / inter-state | CGST, SGST, IGST, invoice total and total input tax credit |
|---|
The formulas inside
| Column | Formula (row 5) |
|---|
| Closing stock | =D5+E5-F5 |
|---|
| Reorder status | =IF(G5<=H5,"REORDER","OK") |
|---|
| Customer balance | =SUMIF(B$5:B5,B5,E$5:E5)-SUMIF(B$5:B5,B5,F$5:F5) |
|---|
| Cash balance | =$B$2+SUM(D$5:D5)-SUM(E$5:E5) |
|---|
| CGST (intra-state) | =ROUND(F5*G5/200,2) |
|---|
Guides: reorder level and safety stock formula, how to recover udhaar from customers, end-of-day cash reconciliation and input tax credit for retail shops.
Questions
Are these Excel templates free?
Yes. Every template downloads free with no sign-up. If you leave your WhatsApp number you get all four in one workbook, and we tell you when we add new templates; that is optional.
How do I make a stock register in Excel?
Use one row per item with opening stock, stock in (purchases) and stock out (sales). Closing stock is opening + in − out. Add a reorder level column and a formula that shows REORDER when closing stock is at or below it. The stock register template here has all of this ready.
How do I keep an udhaar khata in Excel?
Write one line for every credit bill and every payment, with the date and customer name. A SUMIF formula then gives each customer's running balance. The udhaar template does this and adds a Customer dues sheet with each person's total credit, total paid and amount due.
What should a GST purchase register have?
Invoice date, supplier name and GSTIN, invoice number, HSN, taxable value, GST rate, and CGST + SGST or IGST. Keep it month by month and match it with GSTR-2B before you claim input tax credit. This template calculates the tax and totals the ITC for you.
Do the templates work on a mobile phone?
Yes. They open in the Excel or Google Sheets app on Android and iPhone, and the formulas keep working. Typing many rows is easier on a computer.
Why does the Days overdue column change every day?
It uses TODAY(), so it counts the days from the Pay by date to the date you open the file. Leave Pay by empty for customers without a due date.
When should I move from Excel to billing software?
When you type the same sale twice, once on the bill and again in the stock register, or when the udhaar sheet grows past a few dozen customers. Billing software updates stock and dues from the bill itself, so the registers never fall behind.
Related
