Year-end closing entries template in Excel

Year-end closing entries template in Excel

When the year ends, the books do not close themselves. The adjusting entries have already brought depreciation, provisions and consumed inventory up to date, but the income statement accounts are still loaded with twelve months of movement: revenue is still sitting there with its credit balance and expenses are still sitting there with their debit balance. The year-end closing is the step that takes those accounts down to zero and transfers the result to equity, so the new year starts clean.

This year-end closing entries template in Excel does that work on two sheets. The first is an entries register with the same structure as the adjusting entries register: number, date, closing type, code, account, description, debit, credit, an automatic balance check and remarks. The second is the closing summary, with closed revenue, closed expenses, the calculated result, the transfer of the result and the two checks that keep you from closing with a figure other than the one your records show.

Before filling it in, it helps to be clear about what happens here, in what order, and why this step leaves no room for improvising: a closing done too early has to be reversed in full and started over.

What the closing does, in three tasks

The closing does three things and all three show up in the register. The first is to take the income statement accounts down to zero: revenue is debited, because its natural balance is a credit, and expenses are credited, because their natural balance is a debit. After those entries no revenue or expense account keeps a balance, and the total of the year's revenue and expenses comes to zero, which is exactly the point.

The second task is to calculate the result: when revenue is higher than expenses there is a profit, and when the opposite happens there is a loss. The third is to transfer that result to equity, where a profit increases retained earnings and a loss reduces them. Equity is not an income statement account, so it keeps its balance: the effect of the year stays there and the following year starts from zero.

Adjusting entries first, closing entries second

The most common sequencing mistake is closing the year with the adjusting entries half done. Adjusting entries and closing entries are not the same thing and they cannot be swapped. Adjusting entries bring the period up to date: the year's depreciation, the allowance for doubtful accounts, an expense incurred and not yet paid, revenue already earned and not yet invoiced, the inventory that was consumed. The closing corrects nothing: it takes the income statement accounts as they stand and drives them to zero.

That is why the order is fixed. First every adjusting entry is recorded, then the worksheet is checked for agreement, and only then are the closing entries recorded. If an adjustment arrives after the closing, the profit already transferred to equity no longer matches reality and the whole closing has to be reversed. That is more work, and riskier, than waiting one more day.

Sheet 1: the closing entries register

The first sheet keeps the ten columns of the entries register, with a type list built for this step: closing of revenue, closing of expenses and transfer of the result. Each entry is numbered, described with the account it touches and filled in with its debit and credit; the balance check column tests the entry itself by comparing the two sides of the same number, so an out-of-balance entry shows up in its own cell and no one has to review the sheet by hand.

Because there are only a few closing entries, the register fills up quickly. The usual pattern is one entry to group revenue, another to group expenses and a third for the transfer of the result. In businesses with many income statement accounts there may be more than one entry per group, and that is fine too: what matters is that every income statement account ends at zero and that the total debit equals the total credit. If a single revenue or expense account still shows a balance after closing, the year was not closed.

Sheet 2: the closing summary and its two checks

The second sheet takes the data from the register and lays it out in six lines: closed revenue, closed expenses, the calculated result for the year, the transfer of the result and the two checks. It is the sheet you look at when someone asks whether the closing came out right.

The two checks exist for a simple reason: not to close with a result that differs from what the records say. The check against the transfer compares the amount that went to equity with the result produced by the closed accounts; the check against the worksheet compares it with the result that was already calculated there. When both of them balance, the closing is consistent with itself and with the rest of the process.

The full example, with figures

Closed revenue adds up to 4,500,000 and closed expenses to 2,700,000. The difference gives a result for the year of 1,800,000, which is the amount the transfer entry carries to the debit side against the credit in equity. With that, the first check balances: the transfer matches the calculated result.

The result on the worksheet is also 1,800,000, so the second check balances as well. Both tests say the same thing by different roads: one looks at the entry that was recorded and the other looks at the result that had been calculated all along. When the two agree, the closing can be treated as finished and the income statement accounts are left at zero.

What changes after the closing

After the entries are recorded, the year's revenue and expense accounts sit at zero and equity shows the result already built in. The income statement for the period still shows the year's movement, because it is put together from the figures before closing; what goes to zero is the account balance, not the report. Next year's balance sheet opens with the income statement accounts empty and the profit already inside equity.

That is the point of closing: separating what happened in one year from what will happen in the next. A year that is closed properly is a year nobody has to reopen to fix.

Mistakes worth avoiding

Almost every closing problem comes from repeating one of these oversights.

• Closing before the adjusting entries are finished: the result ends up calculated on incomplete figures.
• Closing the same year twice: the transfer to equity is doubled.
• Leaving an income statement account with a balance: the closing is not finished.
• Transferring the result to an account other than retained earnings, so equity in the balance sheet no longer agrees.
• Not keeping the register with its balance check: without the file, next year's review starts over from nothing.

Internal control

This template is a working tool and an internal control tool. It does not replace an official document, it is not an official filing and it does not take the place of your accountant's review: it keeps the entries numbered and ordered with the balance check in plain sight, so whoever reviews finds the result check immediately.

When volume grows and several people are recording movements, the template falls short: there is no history, it is hard to see who recorded what, and the file fills up with versions. Kardex Tauro handles the whole cycle and turns the closing into one more step of the system, with balances up to date and the result calculated without retyping anything. Even so, starting with the template is worth it: understanding what the closing does is the previous step to configuring any system well. Kardex Tauro is not here to replace your judgement, it is here to remove the repetitive work.

Download the template, record a test closing with the figures from the example and look at the two checks: if both balance, you already have the last step of the accounting cycle under control.