Tax reconciliation template in Excel

Tax reconciliation template in Excel

When the year closes, the accounting result and the declared tax base almost never match. The books recognise expenses that the tax rules do not accept, record income that stays outside the base and apply depreciation criteria different from the ones used when declaring. Those differences are not mistakes: they are the reason a tax reconciliation exists. Without it, the figure that gets filed has no explanation, and any later review turns into a reconstruction of several months.

This tax reconciliation template in Excel gathers in a single sheet the record of concepts that explain, one by one, why the accounting result is not the tax base. It comes with the calculated tax value column, the flag that marks each difference as permanent or temporary, the count of the rows that carry an increase and a decrease at the same time, and a summary block that checks the calculated total against the declared base.

What the tax reconciliation is and what it is for

The tax reconciliation is the bridge between two figures that come out of the same business but are read with two different sets of rules. On one side sits the accounting result: what the company earned according to its own records, with the criteria it applies every month. On the other sits the declared tax base: the number on which the final report is calculated. Between the two there is a gap that cannot be left unexplained.

The reconciliation exists to walk that gap in writing. Every item that separates one figure from the other is recorded as a line, with its amount and with its reason. The result is a document that answers three questions: how much the adjustments that raise the base add up to, how much the ones that lower it add up to, and what the nature of each difference is. Once that path is built, the reconciliation stops being a mismatch and becomes an explanation.

It is also useful for conversation. The person who signs the filing is not always the person who keeps the books, and the accountant needs to see the detail in order to support each adjustment. A tidy sheet, with the concept, the value and the type of difference in separate columns, makes that conversation short and verifiable instead of a chain of emails with loose figures.

Why the accounting result is not the tax base

The underlying reason is that the two figures answer to different purposes. Accounting seeks to show the situation of the business on economic grounds: it recognises an expense when it was incurred, even if it has not been paid yet, and it estimates the wear of an asset over its real useful life. The tax base follows another purpose and does not always accept the same criteria.

Two kinds of adjustment come out of that. The first are expenses that exist in the books and are not accepted for tax: they were incurred, they are correctly recorded and they reduced the accounting result, but when the base is built they are left out. The second are income items that exist in the books and are not taxed, or were already taxed at another moment. The first push the base up; the second push it down.

A third source of difference also shows up: measurement criteria. The same asset can be depreciated at a different pace under the accounting criterion and the tax criterion, and that difference of pace has nothing to do with an error. The template does not judge either of them: it only records the difference and flags it so that it stays in sight.

How the sheet is built

The file is a single sheet with seven columns. Each row is a concept that produces a difference, and the columns make clear which part is book value, which part is the adjustment and how the tax value of that line ends up.

Every column is typed in except the tax value, which works itself out from the others. Further down, the summary block records nothing new: it adds the totals, counts the problem rows and compares the calculated result with the declared base.

What is added and what is subtracted

The two columns that do the work are the adjustment ones. The increase column takes the values that raise the base: expenses recognised in the books that are not accepted for tax, or criteria that lead to a larger measurement. The decrease column takes the ones that lower it: income that is not taxed or was already taxed, and criteria that lead to a smaller measurement.

The tax value of each line comes from taking its book value, adding the increase and subtracting the decrease. In the end, the sum of the tax value column has to give the base on which the report is built. An example with the figures for the year makes it clear:

The table reads in two directions. Across, each row shows where its tax value comes from. Down, the totals column shows that the two hundred and seventy thousand of increases weigh more than the two hundred thousand of decreases, and that is why the base rises above the accounting result. If the accountant asks why the tax value is higher, the answer is already written in the table itself.

Permanent differences and temporary differences

The type of difference is not decoration: it is the part that gets consulted the most afterwards. A permanent difference is one that never reverses. An expense that is not accepted and never will be accepted leaves a fixed mark between the two figures, and that mark repeats year after year. A temporary difference is one that reverses later: today's difference cancels itself out with the passage of time, so that in the long run the books and the base look at the same amount again.

Telling them apart matters because it changes how the result is read. If most of the differences are temporary, the gap between the accounting result and the base is explained by a matter of timing, not of substance. If the permanent ones dominate, there are items that will always separate the two figures and it pays to keep them in mind when projecting. The third option in the list, "other", leaves room for cases that do not fit cleanly into either of the two.

The increase and the decrease do not fit in the same row

One rule orders the whole record: in the same row, the increase adjustment and the decrease adjustment cannot both be filled at once. A line that adds and subtracts at the same time stops being one difference and becomes two differences squeezed into a single cell, and the trace of each one is lost. When a case like that shows up, the right move is to split it into two rows: one for the adjustment that pushes the base up and one for the adjustment that pushes it down.

The summary watches that rule. It counts how many rows carry an increase and a decrease at the same time and reports them. If the count is zero, the record is clean. If it comes out as anything else, the sheet points at the problem instead of hiding it, and whoever reviews it knows exactly where to look.

The check against the declared tax base

The last piece of the format is the check. Once the adjustments are added up, the sheet takes the calculated total and crosses it with the tax base that was declared. If the two figures match, the reconciliation fully explains the difference between the accounting result and what was reported. If they do not match, part of the gap is still unexplained, and that is where the pending work shows up: almost always an adjustment that has not been recorded yet.

The summary also groups the increases and the decreases by type of difference. That mini table answers a question that usually arrives later: of everything that moved the base, how much belongs to differences that will reverse and how much to differences that will not. With the figures of the example, the split comes out like this:

The two tables are read together. The first shows how the base is reached; the second shows what the nature of the pieces that moved it is. Neither of them replaces the accountant's judgement, but both make that judgement rest on something visible.

What makes this sheet an internal control

It is worth being clear about what this file is and what it is not. It is an internal control sheet and a conversation piece with the accountant: an orderly record of the differences, with their amount, their nature and their reason. It is not a filing, it does not replace an official document and it is not presented to any authority. The filing is put together by whoever carries the responsibility of signing it, with the criteria and the support that apply; this template is the working material that arrives before that step.

That separation is an advantage, not a limitation. Because it is an internal sheet, it can carry whatever level of detail is useful: one row per item, one note per difference, a mark for the review. And because it is internal, it can be updated as often as needed while the close approaches, without the rigidity of a document that is presented only once.

A habit that brings order to the close

Keeping the reconciliation in a spreadsheet has a benefit that is not obvious at first: it forces every difference to be named. An adjustment that cannot be explained in a note is probably not well understood, and a difference that is not known to be permanent or temporary is a question that has not been put to the accountant yet. The format turns both of those into a concrete task that can be settled in the same sheet.

The reconciliation also improves with habit. The first year every line costs; from the second year on, many of the differences repeat and the sheet almost builds itself, because the work is checking what was already there and confirming whether it stays the same.

In the end, the question the format answers is simple: why is what the business earned not the same as what is declared? Answering it in writing, line by line, with the type of each difference and a check at the foot, is what separates a figure that is explained from a figure that is merely asserted.

A system that keeps the books and the stock as part of its daily operation makes that review shorter, because the data the adjustments come from is already up to date. Kardex Tauro aims at that point: it gathers the accounting record and the movement of inventory in one place, so that when the close arrives the reconciliation is a review and not a reconstruction. In the meantime, this template plays its part as an internal control.