Provisions template in Excel: receivables and inventory

Provisions template in Excel: receivables and inventory

When receivables start to age and inventory sits still in the warehouse, the uncomfortable question shows up: how much of that will actually be collected? Carrying every balance at full value on paper inflates the result for the period and hides a surprise for the day a customer does not pay or a product stops selling. A provision exists for that: to put in writing a reasonable estimate of what will probably not be recovered.

This provisions template in Excel gathers in a single file the two sheets that task needs: the doubtful receivables sheet, with its days past due and its percentage, and the inventory sheet with obsolescence measured in months without movement. Both come with the columns ready, the calculation formulas, the total row and a summary block that shows how much the provision for the period added up to and how much has to be adjusted.

What a provision is, and what it is not

It helps to start by clearing up the most common misunderstanding. A provision is an estimate: the prudent recognition that part of the receivable balance or of the inventory will probably not turn into cash. It is not a payment, because no money leaves the cash box at that moment, and it is not a debt either, because nobody can demand its collection. It is simply the way to make the figures for the period look closer to reality instead of showing the full face value as if everything were going to be recovered.

Three consequences follow from that nature, and they organize the whole format. The first is that the percentage applied is not a fixed piece of data: it is a policy of the business, which is why it lives in an editable cell that does not come preloaded. The second is that the provision changes from one period to the next, because the balance changes and because the criterion can be tightened or eased depending on how receivables or the warehouse behave. The third, and perhaps the most important, is that recording a provision does not erase the right to collect: the customer still owes the same and the product is still on its shelf, available to be collected or sold if the situation improves.

Why the percentage is typed in and not calculated automatically

If the sheet came with a built-in percentage, it would be deciding on behalf of the business something that only the business can decide. Collecting in thirty days is not the same as collecting in ninety, and a spare part that has been idle for a year is not the same as one idle for four months. Every company, and sometimes every line, has its own criterion. That is why the column shows up empty, formatted as a percentage and ready to be filled in, while the format only multiplies the balance by whatever is typed. This keeps the estimate in the hands of the people who know the business.

The same logic applies to the inventory sheet, where the percentage rests on the months without movement. Obsolescence is not an exact date, it is a trend: the longer a product stays without leaving, the more likely it is that it will no longer sell at the cost at which it is valued.

Who it is meant for

It serves anyone keeping the books of a small business and closing month by month: the office accountant, the administrative assistant, the owner who reviews the numbers personally. It also serves whoever manages receivables and needs to see in a single sheet which customers are furthest past due and how much they represent. And it serves whoever handles the warehouse, because idle inventory is money asleep.

No spreadsheet expertise is needed. All you have to do is type balances, dates and percentages; the formulas handle the rest, and the file can be reused every month by replacing the data.

What the file includes, sheet by sheet

The columns of the receivables and inventory sheets

How the calculation works, with numbers

Let us look at the receivables sheet at a sample closing date. Customer A owes 1,000,000 with twenty days past due and the policy applies one percent, so the line calculates 10,000. Customer B owes 800,000, also at one percent, which gives 8,000. Customer C carries 600,000 past due by eighty-six days, and because of its age it falls into the five percent band: 30,000. Customer E owes 300,000 past due by one hundred and forty-seven days, and since it is the oldest band it is provided at twenty percent: 60,000.

The provisionable receivables add up to 2,700,000 and the calculated provision at the closing date reaches 108,000. Now the figure almost nobody remembers comes into play: the previous provision. If the prior closing already had 90,000 recorded for these same lines, the adjustment for the period is not 108,000 but 18,000, because the only thing that has to move this month is the difference. Had the previous provision been larger than the calculated one, the adjustment would be negative and would speak of a release, that is, of an estimate more pessimistic than necessary.

The inventory sheet works the same way, but the risk signal is the months without movement. A batch of twenty-five ream packs valued at 300,000 had gone fourteen months without leaving and is provided at twenty percent, which gives 60,000. Sixty folders valued at 210,000 have been idle nine months and fall into the ten percent band, that is, 21,000.

With those two sheets, the provision for the period is made up of 108,000 from receivables and 81,000 from inventory, each with its own adjustment calculated separately against the previous provision of its own sheet.

Step by step

1. Open the receivables sheet and record, line by line, the customer, the document, the due date and the outstanding balance of every invoice still open at the closing date.
2. Look at the days past due column: it is calculated on its own from today's date and the due date. Check that no line is left without a due date, because without it there is no way to measure the age.
3. Type the policy that applies to each band or each customer in the percentage column. Remember that it is an editable cell: the template does not preload it because that decision belongs to the business.
4. Review the calculated provision for each line and compare it with the criterion applied. If a large balance ended up in the youngest band, it is worth asking whether the criterion is the right one.
5. Type in the previous provision column the figure that was already recorded for those lines at the prior closing. The adjustment for the period comes from there, and that is the figure that really moves the month.
6. Repeat the exercise on the inventory sheet with the products counted, their value and their months without movement, and close with both summaries at hand to review the total effect of the period.

Tips and common mistakes

• Do not confuse the provision with the write-off. Estimating what will probably not be recovered is one thing; writing the balance off the books is another. The write-off is decided separately, with its own support.
• Always write the third party's name the same way. If the same company appears under two different names, it will read as two customers and the totals per line will be split.
• Leave a trace of the criterion. If you raised a percentage this month, note in the observations field why you did it. A provision with no explanation is hard to defend later.
• Mind the closing date. The days past due depend on today's date, so the file moves on its own with time; keep a copy per month.
• Do not provide for the whole receivables balance as if everything were past due. Balances that are current rarely need a provision.
• Check the previous provision cell by cell. If the adjustment comes out huge and unexplained, the most likely cause is that the prior closing figure was copied wrong or belongs to other documents.
• Remember the nature of the tool. This format is for internal control and helps organise the estimate; it does not replace any official record or document.

When it is time to move to software

The template works well as long as the volume is reasonable and the closing is monthly. Once receivables run into several hundred documents, or when several people record collections at the same time, keeping the file up to date becomes a job in itself. That is where control suffers: parallel copies appear, someone types a payment into the wrong version, and the provision for the month ends up calculated on data that is no longer the real data.

A system that carries receivables and inventory as part of its daily operation avoids that wear, because the information updates itself with every movement and the estimate is calculated on data that is always current. Kardex Tauro points exactly at that: it brings receivables, inventory and the accounting record into one place, so that when closing time comes the provision is a query and not a reconstruction. In the meantime, this template plays its role as an internal control tool and helps you learn to look at receivables and the warehouse with judgement.

In the end, the question is not an accounting one but a business one: of everything you are owed and everything you hold in store, how much do you really expect to recover? Writing it down, reviewing it every month and comparing it with what was already recorded turns a balance into a decision.