Changing content of accounts

Changing content of accounts

Didn't find your answer?

I have a spreadsheet which converts an input TB into a presentable set of accounts, through an 'accrual and prepayment' adjustment sheet. My problem is that each year some of the headings of income and expense change due to funding sources and availability. I then have to redesign the layout as well as add new accounts in the TB. Is there a facility to ease this? I've looked at pivot tables but it doesn't seem appropriate.
John Watson

Replies (2)

Please login or register to join the discussion.

avatar
By John Watson
23rd May 2006 16:35

New accounts layout
I didn't give enough information!!
The TB is manually entered in quarterly cumulative columns against a Description but in account code order. The spreadsheet then groups or adds together like income and expense areas to make the order of things more readable. There is a 'working' sheet for accruals and prepayments that I don't want to put in and out of the Nominal (no reversing journal facility). The presentation sheet for P&L and Balance Sheet then picks up the relevant figures for publication. There is also a Budget worksheet. The problem is that each year the presentation changes with headings changed/added/deleted, which will also result in new Nominal codes having to be introduced.
The changes usually means a rewrite of the worksheets and a manual trawl through to make sure that it's all valid and complete.
I probably can't avoid rethinking it each year.
I keep looking at pivot tables but haven't worked through them enough to understand them properly.
Thanks for the thoughts.

Thanks (0)
avatar
By Richard Willis
19th May 2006 09:49

There are several options.
Hi John

It is difficult to give definitive advice without knowing exactly how your TB is laid out or how big it is. Assuming that it is a simple Code/Description/Bal layout,I would first add a column and allocate an anlaysis code to each account (unless you can export existing ones with the TB).

This can then be used to either create a pivot table or, as you have excluded this as an option, to sort the balances across columns representing the analysis headings.

Assuming that the balance is in col.C and the code in D, add the analysis code to each column: I usually put it above the column description so that you don't have to print it, e.g. In row 1. Then, in the first analysis cell (say F5) type =IF($D5=F$1,+$C5,0).

Copy this across all the columns, then the whole width down all the rows. Format the cells so that zero is blank. Total the rows and add the totals back to the left and do a check total with the TB bal.

If you want to do the WHOLE job in one sheet (excluding the pretty bit), then insert columns after the TB bals in which to post columnar journals (AJE1, AJE2, etc.), add a total column after them, and apply the above to the total column, changing the relative cell references to suit.

It's cumbersome but, to a journeyman accountant, it works well for small-medium schemes.

p.s. In case it's not obvious, the pretty bit just picks up the balances from the spreadsheet. ALSO, if you want to add next year to this year, import the TB into another worksheet and use =VLOOKUP to pick off the balances. If you copy/paste special/values the existing bals. first, to your PY column, you can bring in the new bals in place of the old ones and use the layout again (having saved the whole thing with a different name, of course!)

Thanks (0)