ICAEW.com works better with JavaScript enabled.

Using Excel to “map” a trial balance to a set of accounts – part 2

In the first part, we showed how to map a trial balance to a balance sheet and profit and loss account or SOFA. This second article takes this one stage further, showing how to allocate the trial balance (TB) item to a fund and fund category (such as unrestricted or restricted funds).

Part 1 used the data validation and the SUMIF function to allocate each item in the TB to a balance sheet heading or SOFA heading and add the total to the appropriate heading in the accounts.

In charity accounts there are unrestricted, restricted and endowed funds. Each of these categories requires a column in the SOFA and analysing in the balance sheet. With the same method that mapped the rounded TB to a balance sheet item, use the next columns on the TB worksheet to map to a fund and category of fund.

The first task is to create the list of funds. Set this up so that the list is flexible and you can add new funds if required (bear in mind you want to build a spreadsheet that can be used on any charity client). Then create a validation list containing the types of fund categories. Use it to allocate a category to each fund, as illustrated in cell K5 below.

Using Excel to map a TB to a set of accounts Using Excel to map a TB to a set of accounts

Cells J2 to J26 create yet another data validation list for the specific funds. In Column P this list is used to map the trial balance item to the relevant fund. Column R then uses the VLOOKUP formula to determine the category of each fund.

We recommend using range names wherever possible to make formulae meaningful. We named the range of cells J2:L26 “Funds”. The VLOOKUP formula is therefore: =VLOOKUP(P31,Funds,2,FALSE).

The FALSE argument asks for an exact match. If it doesn’t find a match, it returns the error value #N/A. So the formula takes the value of cell P31 [Church Hall Fund] and looks down J2 to J26 [the left hand column of “Funds”] for a match. If it finds a match, it returns the value of the second column. In the illustration above you see Church Hall Fund in cell J12, so the formula returns the value of K12 [Restricted] being the value in the second column for this row. Had the formula been =VLOOKUP(P31,Funds,3,FALSE), it would have returned 0, being the value in cell L12.

To map the totals in the accounts to the correct column, use a new formula: CONCATENATE. This simply adds the contents of two or more cells together.

Using Excel to map a TB to a set of accounts

In this example the formula =CONCATENATE(R30,” ”,O30) takes “Restricted” from R30, adds a space [“ ”] and then adds “Property” from O30. Column S does the same for every trial balance item, copying the formula all the way down the column.

On the balance sheet, combine the SUMIF and CONCATENATE functions to drop the appropriate sum into each cell. So the formula in cell E8 is:
=SUMIF(TB!$S:$S,CONCATENATE(E$4," ",$A8),TB!$N:$N).

This formula searches column S on the TB looking for “Restricted Property” and adds the sum of the rounded TB value in column N for the matching records, dropping the total into cell E8 on the balance sheet.

The formula in cell E9 is =SUMIF(TB!$S:$S,CONCATENATE(E$4," ",$A9),TB!$N:$N). It adds up the values for “Restricted Solar Panels”. Similarly, cell C10 contains the formula: =SUMIF(TB!$S:$S,CONCATENATE(C$4," ",$A10),TB!$N:$N) and adds up the values for “Unrestricted Equipment”.

Using Excel to “map” a trial balance to a set of accounts – part 2

This same approach can be used to “map” all the detailed notes on the SOFA. Column D contains the column for Designated Funds, which has been hidden because this church has none.

Using Excel to map a TB to a set of accounts

While this approach should ensure that the TB is mapped accurately, you should still build in appropriate checks. Under each cross cast total, add a formula to compare the total downwards to the total across. In cell G20 we could put: =IF(G19=C19+D19+E19+F19," ","Error in crosscast").

Using Excel to map a TB to a set of accounts

This formula displays a “ ” [ie, a space] if the totals are equal. If they don’t agree, “Error in crosscast” is displayed in red text. You can also use conditional formatting to turn the cell (or other cells) yellow to make it obvious that there’s a problem. Similarly, you can set up formulae to check that the total funds on the SOFA agree to the balance sheet figures.

Conclusion

Having created a flexible spreadsheet that can be used for any charity, the third final article in this series will look at automating the import routine as well, so that a simple “Right click, refresh” will bring in the latest figures from the accounting software.

July 2014

Read the rest of the articles in this series: