Is Excel Safe for Your Gold Scheme? Problems and Solution

It is the fifth of the month. A customer stands at your counter with a receipt in her hand and says she paid last month. Your staff opens the Excel file, finds her row, and the cell is empty. Nobody can say if the payment was never typed, was typed and lost, or is sitting in another copy of the file on another computer.

So is a gold scheme Excel file safe? It is safe enough for a small scheme that one careful person runs with a few strict habits. It stops being safe when two people edit it, when copies start to travel, or when nobody knows where last night's backup is.

Is Excel safe for a gold scheme?

Excel is safe for a gold scheme when one person enters the payments, there is one master file, and a dated backup is made every evening. It becomes risky when the file is copied, shared on phones, or edited by several staff. The program is reliable. The way a shop uses it on a busy day is the weak point.

Ink stays. If someone changes a figure in a paper register, you can see the cutting. An Excel cell has no memory. You type a new number, press Enter, and the old number is gone without a mark.

That matters more in a scheme than in any other part of your shop. In a gold saving scheme the customer gives you money for many months before she takes any jewellery. For all those months, your record is the only proof on your side of the counter. I say this to every jeweller I work with: the software does not run the scheme, trust does. Your file is where that trust is kept.

This post is about the safety of the file. If you still need the layout itself, with the three sheets and the columns, start with our free Excel format for a gold scheme and come back here.

What are the five ways an Excel file loses data?

An Excel scheme file loses data in five common ways: an older copy is saved on top of the new one, a payment row is typed over, a formula becomes a typed number, a sort mixes up the rows, or the only computer fails. None of these needs a virus or a thief. Each one happens during normal work.

The picture below shows all five.

1. An older copy is saved on top

This is the one that hurts the most. Two copies of the file exist, one is a few days old, and the old one is put back under the same name. Every entry made in between is gone. The worked example below shows how easily it happens.

2. A payment row is typed over

A customer pays for month four, and the staff member types the new amount into the same cell that held month three. Or a wrong amount is corrected by typing over it. Either way, you cannot prove what was there first.

3. A formula becomes a typed number

The total at the bottom of a column is a formula. One day someone clicks on it and types a number, perhaps to make the sheet agree with the cash in hand. From that day the total stops adding new payments. Nothing on the screen warns you.

4. A sort mixes up the rows

Someone selects only the name column and sorts it from A to Z. The names move. The payments beside them do not. Now every payment sits next to the wrong member. Once the file is saved like that, only a backup brings the right order back.

5. The only computer fails

The whole scheme lives on one counter computer or one pen drive. A hard disk fails or a pen drive is lost. With no second copy, the record of every member is gone in one day.

A worked example: the Thursday copy

All numbers here are examples. A shop runs a scheme with sixty members, each paying ₹3,000 a month. Staff 1 enters payments on the counter computer. On Monday evening the owner copies the file to a laptop to look at the totals at home.

From Tuesday to Thursday, Staff 1 enters fourteen payments at the counter. On Thursday night the owner remembers two members who paid him directly by UPI. He adds both to the laptop copy. On Friday morning he copies the laptop file back to the counter computer. The computer asks if he wants to replace the file. He clicks yes.

The counter file now has the two payments and none of the fourteen. Two weeks later a customer comes in with her receipt, and the file says she has not paid.

What saves this shop is not Excel. It is the receipt book and the UPI statement. Someone has to sit with both, find the fourteen payments, and type them again. If the receipts were not numbered, even that is guesswork. A single file with no backup is one click away from a bad month, and the customer is the one who finds the mistake.

How do you make a gold scheme Excel file safer today?

You make a gold scheme Excel file safer with eight habits: keep one master file, let one person enter, put a password on the file, lock the formula cells, add rows and never overwrite, back up every evening, match with cash every week, and put a member number and receipt number on every row.

Here is how to do each one.

1. One master file. Decide where the real file lives and tell everyone. Delete or move every other copy. To look at figures at home, open the same file from shared online storage. Do not carry a copy.
2. One person enters. Choose one staff member who types payments. Others may open the file to read it. When that person is on leave, hand over to one named person.
3. Password on the file. Excel lets you set a password that is needed to open the file. In most versions on a computer you will find it under File, then Info, then Protect Workbook. Share the password with the owner and one staff member. Keep it written down in a safe place, because a forgotten password can lock you out of your own file.
4. Lock the formula cells. Excel has a Protect Sheet option under the Review menu. You choose the cells where staff may type, and the cells with totals stay locked. This stops a total being typed over by accident.
5. Add rows, never overwrite. Every payment is a new row. Every correction is also a new row, with a note that says which receipt it corrects. The wrong row stays where it is. A good kitty ledger kept for each member follows the same rule on paper.
6. Back up every evening. At closing time, save a copy with the date in the file name. Keep it away from the counter computer, in online storage or on a drive that goes home with the owner. Once a month, open an old backup to check that it really opens.
7. Match with cash weekly. Once a week, compare the amount received in the file with the cash book and the bank or UPI statement. A gap found after one week can still be traced.
8. Numbers on every row. Each payment row carries the member number and the receipt number. Numbers let you trace any row back to a receipt, even after a bad sort.

Test your own file in ten minutes

Before you trust the file, test it. Do this once, without telling the staff in advance.

1. Search for the file name on the counter computer. Count the files that appear. If you see more than one, with names like "final" or "new" or "copy", the first leak is already open.
2. Pick three receipts from last month's receipt book. Find each one in the file. All three should be there, with the same amount and the same month.
3. Click on a total. Look at the bar above the sheet. If the entry starts with an equals sign, it is a formula. If it shows a plain number, someone has typed over it.
4. Ask where last night's backup is. Then open it. A backup that nobody has ever opened is a hope, not a backup.
5. Compare one week. Take the amount received for any one week in the file, and compare it with the cash book and bank statement for the same week.

If your file fails two or more of these, fix the habits first. Do not buy anything yet.

Risk, cost and fix in one table

The table below puts each risk beside what it costs you and its fix. The first five rows are the five leaks. The last two are risks where no data is lost, but trust is.

What can Excel not do, however careful you are?

Excel cannot tell you who changed an entry, cannot stop the file being forwarded, cannot send a receipt or a reminder, and cannot show the customer her own balance. It only knows what someone typed into it.

Nobody knows who changed what. A plain Excel file saved on one computer does not record who changed a cell or what it said before. So an honest mistake and a dishonest change look exactly the same. The Entered by column helps, but anyone can type any name in it. A locked sheet stops accidents. It is not built to stop a person who wants to change a figure.

The file is forwarded on a phone. A scheme file holds names, phone numbers and balances. Once it is sent on WhatsApp or email, a copy sits on a phone you do not control. Rules about customer data differ by country and state, so ask your own adviser what applies to your shop. The safe habit is to share a printed report, and keep the file in the shop.

It records, it does not act. Excel will not give the customer a receipt, and it will not remind her that the due date is near. Somebody has to read the sheet and pick up the phone, which is why we wrote a separate guide on sending payment reminders without chasing people.

It only knows what is typed. Cash goes in the drawer, UPI goes to the bank, card payments arrive in a settlement. The file sees none of them until a person types them in. This gap between money received and money recorded is one of the main reasons jewellers lose money in a manual scheme.

Mistakes owners make when they try to secure the file

• Giving the password to everyone. A password that the whole shop knows protects nothing.
• Keeping the backup on the same computer. If the computer fails, the file and its backup fail together.
• Backing up with the same file name. Then last night's backup replaces the one before. Put the date in the name, so you can go back several days.
• Fixing the total instead of finding the gap. When the file and the cash do not agree, the wrong answer is to type the right total. Find the missing row.
• Hiding columns to protect them. A hidden column can be shown again in two clicks. It is tidy, not safe.

When is Excel enough, and when is it time to move?

Excel is enough when you run one scheme in one shop, one person enters every payment, you know most members by face, and your weekly match with cash agrees. It is time to move when a second person needs to edit, customers ask for their balance on the phone, or the match stops agreeing.

I will be honest about this. If your file passes the ten minute test and your scheme is small, you do not need software today. Keep the eight habits and look again in six months.

These are the usual signs that you have outgrown the file:

• A second or third person has to enter payments, or a second branch starts collecting.
• Customers call to ask how much they have paid, and staff have to open the file to answer.
• Reminders take up hours of calls every month.
• Payments come by cash, UPI and card, and matching them takes longer each month.

If two or three of these sound like your shop, compare your choices in our guide to paper, Excel and software side by side. When you start talking to vendors, take the checklist of what to look for in scheme software with you.

The clean file you have built is not wasted. Member numbers and receipt numbers on every row are what make a move easy. Our guide on moving a running scheme into software explains the steps week by week.

What software does about the same five leaks

Good scheme software removes the leaks by design, not by habit. There is one record, so there is no older copy to save on top, and totals are worked out by the system.

In GoldKitty, our gold scheme software, every payment gets a numbered receipt in sequence with no gaps, and the receipt also goes to the customer on WhatsApp. Cash, counter UPI and in app payments land in one list. Backups are taken daily, and you can export all your data at any time, because your members and your records belong to you. Pricing is given after a demo on your own scheme.

Software has limits too. It is a running cost, staff need training, and it will not fix a scheme whose rules were never written down. Before going live, run mock collections from start to finish.

Our take

Excel is a good servant and a poor guard. With one careful person and the eight habits above, it will carry a small shop a long way.

But a scheme is a promise kept over many months, and a promise needs a record nobody can argue with. The day your customer finds a mistake before you do, the file has already cost you more than any software would. If you would like to see your own scheme with receipts, reminders and one clean list of payments, you can book a demo of GoldKitty on your own numbers, or look at the other products we build at appico.

Frequently asked questions