Preparing a year end accounts model

I’ve written more generally about how to have a smooth year end accounts process. This is a more specific piece about how to build an audit-friendly year end accounts model.

I’ve included examples for download to illustrate the principles.

September 2023 update: If I was writing this post now, I would be using XLOOKUP instead of VLOOKUP and TEXTJOIN instead of CONCATENATE.

Why use a year end accounts model?

I have evolved this Excel model as a result of five years of audit and then ten years in various accounts roles which always included financial reporting. My bugbears from my audit days were;

• Lack of clarity about how the figures in the final accounts were derived. Even if there were links to the TB it was often done quite simply (eg “TB!A3+TB!A4”). This means as an auditor you have to check each part of the formula. Furthermore, it won’t be robust if you run a new TB with new account codes. It is also hard to check easily that all codes have been used once and only once.

• Manual adjustments added into the spreadsheet. This is a recipe for errors, yet it was surprising how often I saw experienced accounts staff do this. Often they would remember to adjust the balance sheet and the I&E, but would forget to adjust the notes. It’s also very difficult to maintain an audit trail this way.

More generally it seemed that when it comes to statutory accounts people seem surprisingly unwilling to apply good Excel practice. For example, I would often come across sheets where people had typed in numbers.

I think there are a number of reasons for this. People tend to inherit a year end accounts model and feel it is easier to use this than create their own. You only prepare statutory accounts once a year, so it can seem like creating a proper model is a waste of time.

However, in my experience it always saves time and error, and it’s something that you can prepare ahead of the year end.

How I structure a year end accounts model

I have attached a very simple cut down year end accounts model to illustrate the points I’m going to make. I’ve created a version with VLOOKUPs and a version with Power Query. For the purposes of this blog, it doesn’t matter which one you look at.

Regardless of what type of account I’m preparing, whether it’s simple sole trader or complicated multi company consolidations, I always have the same four basic sheets or components:

• A chart of accounts mapping
• A journal sheet
• An extended trial balance (ETB) sheet which incorporates information from the first two and a trial balance from the accounting system
• A check / control sheet

The extended trial balance

The extended trial balance drives the accounts. I link every single number in the accounts to the ETB, using the SUMIF formula in most cases. Including the notes.

Every number? Well, I might exclude some of the non-TB numbers such as number of shares in issue. But if the number exists in the accounting system, I will link it to the extended trial balance. Also, avoid the temptation to link directly to a cell in the ETB. Use a VLOOKUP or SUMIF or other formulas to make sure you get the correct account code(s) even if more rows are added into the data.

I extend this principle to also link all the dates in the accounts to one cell on the check sheet. This just makes it easy to update next year. This includes the dates at the top of the schedules and those incorporated into bits of text. For example, if you have something that says “Balance as at 31^st March 2019”, either reword to something like “Closing balance as at 31 March”) OR use Concatenate to link to a year end date on the check sheet.

While the extended trial balance is the key driver of the accounts, it would not function without the other three components of the model.

Chart of accounts

The chart of accounts should map each nominal account code into as many different columns as you need. I also incorporate a high level check column. For example, if all I have to produce is a balance sheet, I will have four columns;

• Account Code;
• Account description;
• High level classification (Balance Sheet / I&E / Reserves); and
• Balance sheet classification.

For something more complicated I might add “I&E breakdown”, “note 2 breakdown” etc. In the model I’ve shown a balance sheet note column only. For a real statutory accounts model, I’d probably have a lot more columns, to capture different breakdowns needed within the I&E.

In theory you can build this sort of thing into accounts packages but I’ve found this tricky in practice. Firstly, because classifications can change. Not just the debtor code with a credit balance issue (which most systems can handle) but because you may need to reclassify your expenditure following audit. Secondly, no accounting system I’ve yet seen allows as many classifications as you will need even for a fairly simple set of accounts.

It might seem tempting to add this data directly to the Trial balance. However, you should always keep it separate and either use a lookup or Power Query to pull the data through to the extended TB. This is because your Trial Balance might get longer and all of a sudden, your mapping is inaccurate.

Journal sheet

The exact format of this will depend on the format of your finance system journal input templates. Mirror that, so that it’s easy to post the journals when you’ve completed the year end process. The key thing for the model to operate is that the sheet contains the account code and the adjustment value. You can also add links to supporting workings if you want. You then add a journal column to your extended TB and a SUMIF formula to pull in the values.

Your extended TB now has mapping and space to make journal adjustments in a robust way. Add a total column so auditors can see the balance before your manual journals and the balance afterwards.

Check sheet

Lots of checks, for example, note consistency, can be done on a check sheet. Depending on the complexity of your accounts, you may just have a couple of checks or you may have dozens. The point is, it should be easy for you to review at a glance and check that the last journal you added didn’t unbalance the accounts.

This is also where the high level checks come in. It’s helpful to see the I&E and BS totals in summary, because your accounts pages are derived from your mapping and this will show you if a mapping is missing or is inconsistent.

Why is this accounts model audit-friendly?

Auditors start from the overall set of accounts and want to see that it maps to a trial balance (ideally one they can run). This method is easier for them to check.

They will often ask for “lead schedules” to support standard balances in the statutory accounts. With this model, the Extended Trial Balance contains all the lead schedules. The auditors just need to filter the relevant column by the relevant category to see the list of balances that make up that number.

This method also gives a good audit trail for journals and it makes it clear where there have been adjustments that you haven’t yet posted to the system. When I prepare supporting reconciliations I usually show the balance as reconciled to the accounts system and then copy the relevant journal entries underneath, so that they can see how the final figure has been derived.

You’ve also built in some checks for them, which is nice.

Other tips

Transaction detail

In this example I’ve used a trial balance. However, another option is to build up the TB from a transactional report, which would allow me and the auditors to drill down into the detail. In this case, I would definitely use Power Query to map the chart of accounts at transactional level.

This may not be practical depending on the size of your data. If you provide a separate transaction listing to your auditors it is very helpful to them if you use the same chart of accounts mapping. That way, they can easily filter it to match the same headings you have used throughout the accounts.

Text based notes

Remember that, through the magic of “CONCATENATE” it is possible to combine text and formulas. For example you may have a note under the main staff costs note that sets out the amount owing to pension schemes at year end. Just because it is predominantly text doesn’t mean it shouldn’t be linked to the TB.

Rolling forward to next year’s accounts

I’ll admit it.  I copy and paste values from last year’s accounts into the prior year columns. In most cases the rolling up activity of the accounts software makes using the ETB method for prior year comparators impractical. It’s possible, but I’ve never yet come across an example where it isn’t quicker to copy and paste. The auditors are going to manually check all of these figures to last year’s accounts anyway so you’re not making their lives any easier by trying to build a model for this.

If you’ve built all your dates to link to the checksheet, you should just need to update the year end date on the checksheet, put the new TB in, and you’re good to go!

A final note on rounding

As an auditor it was drilled into me to make sure the face of the accounts adds up. Those of us who have prepared accounts know that there are always rounding “errors”. There is no clever way round this. Using Excel’s ROUND functions will not solve it, I’m afraid, and often make the errors worse.  

As you have to adjust cells here, breaking all the cardinal rules of Excel, leave a comment in the cell so you know you’ve done it. And most importantly, given that it is manual and involves destroying the integrity of your workbook,  don’t bother doing any rounding adjustments until the very end, once the auditors have reviewed your accounts. You’ll need to leave them something to do, after all.