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.
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!)