Dropping Trial Balance into Financial Statements

[Ang]

I need your expert advice

I have my trial balance in a spreadsheet as follows:
GL Acct# Jan Amt Feb Amt Mar Amt.......Dec Amt YE Total

I want to drop the appropriate line item amounts into the year end P&L,
Balance Sheet, and Cashflow Stmt. Does anyone have any suggestions on the
easiest way to do this without linking each and every cell to the appropriate
cell on the financial statements?

Thank you for your help!
-Angela

 

[Martin Fishlock]

Hi Angela

You need to have a look up code to group your accounts to the financial
statements.

You make a new sheet with your chart of accounts called coa
column A has the account
column B has the description
column C has the financial statement group
column D has the cashflow group

ie you may have

1001, Accounts Receivable Asia, BS_AR, CF_AR

and then you

On the trial balance you insert another column, say in column B, with

vlookup(a2, coa!A:C,3,false)

this will get the grouping

then in the financial statements you use

sumif(trial!!B:B,"BS_AR", trial!N:N)

It may be easier to have a check on your coa sheet to ensure that the
linking tb balances.

I hope that explains it.

If you like send me an example to (e-mail address removed) and I
will show you.