Gold Scheme Excel Sheet: Free Format and Guide 2026

It is the last week of the month. A customer stands at your counter and says she paid her March instalment in cash. Your Excel file shows March as blank. Your staff member thinks he entered it, but in a different copy of the file. Nobody is lying. The file is simply built the wrong way.

A good gold scheme Excel file needs only three sheets: Members, Payments and Summary. People go on one sheet, every payment gets its own line on the second, and the third adds things up. You can download the free gold scheme Excel format and follow this guide with the file open.

What should a gold scheme Excel sheet contain?

A gold scheme Excel sheet should contain three sheets in one file. The Members sheet lists each customer once. The Payments sheet lists every payment as its own line, with date, member number, slot, month, amount, mode, receipt number and who entered it. The Summary sheet shows what is due, received and pending.

The picture below shows the three tabs, with the Payments sheet open because that is the one you will use every day.

Most jewellers start with one big sheet. The customer's name goes in the first column and the months run across the top, like a paper register turned sideways. It looks tidy for the first few months. Then a customer pays twice in one month, or pays half, or holds two slots, and there is no box for it. So someone types over a cell, and the history is gone.

Keeping people and payments on separate sheets fixes this. A person is entered once. A payment is entered once. The same Excel format works for a monthly scheme of any length, and it works as a kitty Excel sheet too, because each slot has its own lines.

The Members sheet: one line per person

This sheet answers one question: who is in the scheme? It has five columns.

• Member number. A short code you give once, such as M-014. It never changes and never repeats. This is the key that ties the whole file together.
• Name. The customer's full name, as they would like it on a receipt.
• Phone. The number you will call or message for reminders. Check it with the customer at the counter, because a wrong digit means a reminder that never arrives.
• Date joined. The day they enrolled. It tells you which instalment they should be on today.
• Slots held. A slot is one share in the scheme. Some customers take one slot, some take two or more for family members. Write how many this customer holds.

The common mistake here is using the name as the key. It works until you meet two customers with the same name. Then a payment lands against the wrong person, and you find out only when one of them comes to redeem. Use the member number everywhere, and treat the name as a label.

The Payments sheet: one line per payment

This is the heart of the file. Every time money arrives, you add one new line at the bottom. It has eight columns.

• Date. The day the money actually arrived, not the day it was due.
• Member number. The same code as on the Members sheet. Type the code, not the name.
• Slot. Which slot this payment is for. If a customer holds two slots and pays for both, enter two lines.
• Month. The instalment number this payment covers, such as 3 for the third instalment. This is not always the calendar month, because people pay late and some pay ahead.
• Amount. What was received, in rupees. Numbers only, with no text in the cell, or the totals will not add up.
• Mode. Cash, UPI or card. Use the same spelling every time so you can filter by it.
• Receipt number. The number on the receipt you gave the customer. Your record and the customer's proof should match.
• Entered by. The staff member who typed the line. When a question comes up, you know who to ask the same day.

The mode column earns its place at the end of each day. Filter the day's lines by cash and the total should match the cash in your drawer. Filter by UPI and it should match what your bank or UPI app shows. If they do not match, you find the gap tonight, while everyone still remembers the day. This daily matching has a technical name, reconciliation, but it only means checking that your record and the real money agree.

The Summary sheet: totals that are calculated, not typed

The Summary sheet is where you look, not where you type. It shows four things: what is due this month, what has been received, what is pending, and the same picture by slot.

In the free file, the Summary sheet has one line per slot. For each slot it adds up the amounts entered on the Payments sheet for that slot and that month. When you add a payment line, the received figure updates on its own. Pending is simply what is due minus what has been received.

The common mistake is typing totals by hand. A typed total is correct on the day you type it and wrong the day after. A calculated total is always as correct as the payment lines behind it. If a total looks wrong, fix the payment line, never the total.

Here is the full column list for each sheet, with the mistake to avoid on each one.

If you have not opened the file yet, get the three sheet Excel file here. It has the headers in place and three example rows, clearly marked as examples. Delete those rows before you enter real customers. There are no macros, so it opens without any security warning about code.

The one rule: one line per payment, never overwrite

If you remember only one thing from this guide, make it this. Every payment gets a new line. No old line is ever typed over.

Here is a worked example. The amounts are examples only. A customer with member number M-014 holds slot 21 and pays 3,000 rupees a month. In April she pays 2,000 in cash on the 1st and the remaining 1,000 by UPI on the 9th. In a one box per month sheet, your staff would type 2,000 and later change it to 3,000. The file would then say she paid 3,000 on one day, in one mode, with one receipt. None of that is true.

In the three sheet format you enter two lines. One line shows 2,000, cash, with its receipt number. The second line shows 1,000, UPI, with its own receipt number. The Summary adds them to 3,000. If she ever asks, you can show her both payments, with both dates.

What about mistakes? Suppose a staff member types 30,000 when the amount was 3,000. Do not change the cell. Add a new line with minus 30,000 and a note that it cancels the wrong entry, then add the correct line. It feels slower. But a file that shows its corrections is a file you can trust in a dispute. This is the same habit that keeps a paper book honest, and we cover it in the guide on how to maintain a gold scheme register.

How to set up the file in your first hour

You can have this running before your next collection day. Work through these steps in order.

1. Save the downloaded file with a clear name, such as the scheme name and the year.
2. Delete the three example rows on the Members and Payments sheets.
3. Give every current customer a member number and enter them on the Members sheet.
4. Enter past payments from your register, one line each, oldest first. If the scheme is already some months old, this is the slow part. Do it once and do it carefully.
5. Check the Summary against your register for five customers you know well. If the totals match, carry on.
6. Freeze the header row from the View tab, so the column names stay visible as the list grows.
7. Decide who owns the file and tell the whole team.

How do you protect a gold scheme Excel file?

Protect the file in three ways. Give it one owner, so there is only one true copy. Back it up at the end of every working day, to a place outside the shop computer. Lock the formula cells on the Summary sheet, so nobody can type over a total by mistake. These three habits prevent most Excel losses.

One owner

One person enters payments into the master file. Everyone else gives that person a slip or the receipt book. This sounds strict for a small shop. It is also the single best protection you have, because the moment two people save two copies, you no longer know which one is right.

A daily backup

At closing time, save a copy with the date in the file name. Keep it somewhere other than the shop computer, such as a pen drive that goes home with the owner or an online storage folder. A computer can fail or be stolen. Your customers' money records should not go with it.

Locked formula cells

In Excel, open the Review tab and choose Protect Sheet for the Summary sheet. Set a password that only the owner knows. Once the sheet is protected, its cells cannot be edited until the owner removes the protection. Leave the Payments sheet open, because that is where daily entry happens. You can also put a password on the whole file from the File menu, under Info, so it cannot be opened by anyone who finds the computer on.

A word on customer details. The Members sheet holds names and phone numbers. Do not send the file around on WhatsApp or email. Treat it like the cash drawer.

What breaks first as your customers grow?

A gold scheme spreadsheet usually breaks in a set order. First it works well with one person. Then two people make two copies. Then a formula gets typed over. Then you notice Excel does not send reminders or receipts. Last, month end checking starts to take days. Knowing the order helps you spot the stage you are in.

1. One person, one file

This stage works well. You know every member by name. You enter each payment yourself, and you can check the whole list by eye. If this is your shop today, Excel is a sensible choice and you should not feel pushed to change.

2. Two people, two copies

The scheme grows, so a second person starts helping. Each saves their own version. One copy has Monday's payments and the other has Tuesday's. Which one is right? Usually neither. A shared online spreadsheet can ease this one problem, but it does not solve the next three.

3. A formula is overwritten

Someone in a hurry types a number over a total. The cell still shows a number, so it looks fine. Nobody notices for weeks. Every figure you read from that cell in the meantime is wrong. Locking the Summary sheet, as described above, is your defence.

4. No reminders, no receipts

Excel records. It does not chase or confirm. It will not message a customer before the due date, and it will not send a receipt after payment. So your staff spend their time on phone calls and WhatsApp messages, and missed instalments slip through. We look at this in detail in where the money leaks in a gold scheme.

5. Month end takes days

The file is now too big to check by eye. Matching cash, UPI and card against hundreds of lines takes days, and the person doing it is the person you most need at the counter. At this stage the scheme cannot grow past what one person can hold in their head.

Common mistakes in a jewellery scheme Excel file

• Merging cells to make it look neat. Merged cells break sorting and filtering. Keep every line plain.
• Using colour as data. A red cell for a late payer means something only to the person who coloured it. Write it in a column.
• Typing text in the amount column. An entry such as 3000 cash is text, not a number, so it is left out of the total. The mode has its own column.
• Skipping the receipt number. Without it, your line cannot be matched to the customer's proof.
• Entering payments in a batch at the end of the week. By Friday, nobody remembers Monday. Enter on the same day.
• Keeping the rules of the scheme only in your head. Rules for saving schemes differ by country and state. Confirm yours with your own adviser, write them down, and make the file follow them.

When Excel is enough, and when it is not

You do not need software if one careful person runs the scheme, in one shop, with one file, and month end checking takes an hour or two. A clean gold saving scheme Excel sheet, built as above, will serve you well. Spending on software at this stage would be money better kept for stock.

Start looking at other options when a second person needs to enter payments, when a second branch starts collecting, when you are making reminder calls every week, or when a customer has questioned a payment and you could not settle it on the spot. Our comparison of paper, Excel and software sets the three side by side so you can judge by your own shop.

If you do decide to look, go in with a list. The buyer checklist for gold scheme software covers what to ask before you pay anyone, including us.

Our take

The software does not run the scheme, trust does. A well kept Excel file is a record nobody can argue with, and that matters more than the tool it lives in. The habits in this guide, one line per payment, a member number, a receipt number, a daily match, are the same habits good software is built on. Learn them in Excel and nothing is wasted.

When the file starts to hold you back, that is where GoldKitty, our gold scheme software for jewellers, comes in. We built it hand in hand with a working jewellery store. Cash, counter UPI and in app payments land in one list. Customers get numbered receipts and reminders on WhatsApp, even if they never install the app. It is white label, which means your customers see your shop's name and not ours. It is also set up for each shop according to its country and its scheme rules.

You do not have to wait for the scheme to end. A running scheme can be brought in with its slots, payments and past winners, and a clean Payments sheet makes that move far easier. If you would like to see your own numbers in it, you can book a private GoldKitty demo. It is one of several ready products built by appico. Until then, keep the file clean, back it up tonight, and never type over a line.

Frequently asked questions