Adjusting entries template in Excel

Adjusting entries template in Excel

The month does not close when the last receipt is filed. There is always something that happened during the period and is still not written down in the accounting records: the depreciation of the month, an expense that has been incurred but not paid, a service delivered that has not been invoiced yet, receivables that are becoming hard to collect, inventory that was consumed without anyone recording it. If those items are left for later, the result for the period comes out inflated or short, assets carry a value that no longer belongs to them, and the accounting starts showing an old picture of the business.

This adjusting entries template in Excel is the register where those movements are written one by one, with the same structure as a journal: number, date, adjustment type, account code, account, description, debit, credit and a column that checks on its own whether each entry balances. Below, a summary totals the movements, counts the entries recorded and, above all, counts the ones that came out unbalanced.

What an adjusting entry is

An adjusting entry is an accounting entry made at the end of a period to bring the pending items up to date. It does not change the nature of what happened: it only writes it where it belongs and in the period it belongs to. Every adjustment touches at least two accounts and always for the same amount on the debit side and on the credit side, because that equality is what makes the accounting readable and addable.

The difference from any other entry lies in the reason. A normal entry records an event that already has its document: a sale with its outgoing goods, a purchase with its invoice, a payment with its receipt. An adjustment records the passing of time or the recognition of something that was known but was not written down. The depreciation of a machine does not arrive with a receipt from anyone: it is calculated because the machine was used all month. A service delivered in December that will be invoiced in January has no document in December either, but the revenue has been earned and the cost has already been consumed.

That is why adjustments are recognised by type. In this sheet the type comes from a short list: depreciation, deferred items, accrual, provision, inventory consumption or other. That list is not decoration: it groups what was adjusted and helps check that no front was left untouched. A month with fifteen accrual entries and no depreciation entry is a warning sign, not good news.

Why they are made when closing the month or the year

Adjustments exist because accounting runs by periods and each period must carry its own. If the depreciation of the month is not recorded, the expense of the month sits below reality and the asset keeps a value higher than the one it has. If an accrued expense is not recorded, the liabilities of the business look better than they are. If earned revenue is not recorded, the result of the month falls short and the next invoice lands in a period where the work was not done.

When closing the month, the adjustment is the last pass before handing over figures. Closing the year matters even more, because the result for the year that goes to the owners, to the bank or to the accountant comes out of that accounting and does not forgive a forgotten expense. An adjustment discovered in December that belonged to January forces twelve months to be corrected and the correction to be explained; an adjustment made on the last day of each month explains nothing, because the close was already right.

There is a second, less technical reason: the adjustment leaves a trace of the judgement. When someone asks months later why the expense of that month went up, the answer is in the adjustment register, with its date, its type and its description. Without that register, the explanation turns into a conversation from memory in which nobody wins, and the criteria applied are left without an owner.

How the sheet is built

The sheet is a single register with ten columns. The first seven are typed in; the rest sort and check the information.

The adjustment type comes from a list, and the code and the account are typed in or taken from the business chart of accounts. The description is not an ornament: it is the phrase that will explain the adjustment when nobody remembers it any more, so it is worth writing the reason and not repeating the name of the account.

The entry balance column is automatic. It compares the debit and the credit of that same entry number and warns when they do not match. It does not compare the total of the sheet, and that decision is the key: this is why an out-of-balance entry shows up entry by entry and not as a difference at the end that nobody can trace. The formula range is expandable, so new rows can be added without rebuilding the sheet.

The example: three entries that balance

The three entries in the example are the most repeated ones of any close. Entry 1 recognises the depreciation of the period: 300,000 on the debit side of depreciation expense and 300,000 on the credit side of accumulated depreciation. Entry 2 builds the receivables provision: 100,000 debit to the expense and 100,000 credit to the provision. Entry 3 takes out of inventory what was consumed: 250,000 debit to cost of sales and 250,000 credit to inventory.

The totals come to 650,000 on each side. The summary below reports three entries recorded and none out of balance, which is exactly what a healthy close should look like. Each line of the register shows its balance cell in order, and that column is the one to look at before saving the file.

The mistyped entry: what the balance column is for

Suppose entry 2 is typed with 150,000 on the debit side against 100,000 on the credit side. The balance cell of that entry will not show a silent difference: it says to review, and the summary below counts it. The result is a register with two entries in order and one out of balance, and the warning appears on the only line that matters.

Why check entry by entry and not by the total? Because a total can balance with a mistyped entry. It is enough for another entry to carry the error in the opposite direction for the two mistakes to cancel out: one 50,000 too high on the debit side and another 50,000 too low, and the final sum looks flawless while both entries are wrong. That is the hidden imbalance a close cannot afford, and it is exactly the one the balance column uncovers. An entry is a unit: either it balances on its own, or it does not.

When the cell warns, the path is simple: look at the debit and the credit of that number, fix the amount or the account and look again. It is not fixed by adding a difference line or changing the total, because that turns a typing error into a real one that nobody will be able to explain later.

The summary below

The summary does not repeat the register: it watches over it. It totals the debit and the credit, counts the entries recorded and counts the ones left out of balance. It also builds a small table with the debit and the credit by adjustment type, which is the one that shows how the work of the close was spread.

The table by type also helps detect what is missing. If depreciation and accrual show up in the month but provision and inventory consumption do not, the summary leaves the question ready: was there really nothing to provide for this month? The register forces nothing, but comparing types helps avoid closing just to close.

The adjustments that repeat most

• Depreciation: the part of the asset consumed during the period is charged to expense and credited to accumulated depreciation.
• Deferred items: the share of a payment made in advance that already belongs to the period is charged to expense.
• Accrual: an expense or a revenue of the period is recognised even if the document arrives later, against a payable or a receivable.
• Provision: a probable loss is recognised, such as receivables that are becoming hard to collect, so the result does not look better than it is.
• Inventory consumption: what was consumed or sold is taken out of inventory against cost of sales.
• Other: any adjustment that does not fit the previous ones, with the description well written because nobody is going to guess it.

Good habits when recording an adjustment

Number the entries in order and do not repeat a number: the number is what makes the balance column know what to compare with what. Date the adjustment inside the period it corrects, even if it is written later, because a March expense cannot be recorded with an April date without dirtying both months.

Write the description with the reason and not with the name of the account. Attach the calculation, even if it is on a separate sheet or in a note beside it: the amount of the adjustment has to be rebuildable. And do not leave an out-of-balance entry for the next day; a pending imbalance is the one that ends up covered with an invented balancing line, and that line is the one nobody can defend afterwards.

Internal control

This sheet is a working and internal control tool: it does not replace an official document or a filing, and it is not presented to any authority. Its value is that it keeps an orderly record of what was adjusted, with its number, its date, its type and its calculation, so that the close can be reviewed and explained.

With the adjustments recorded and in order, the next step is to move them into the journal, which is where entries live with the rest of the accounting. After that come the worksheet, which sorts the already adjusted balances, the closing entries, which bring the income statement accounts to zero, and the income statement for the year. If the adjustments are right, every step that follows comes out right; if they are wrong, the error is dragged all the way to the last paper the business signs.