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!
-Angela
Thank you for your help!

Hi Angela,

My post yesterday appears to have got lost. So here goes again.

The basic idea is to have a chart of account (COA) look up sheet where you
provide a table of account codes, descriptions, and the group a group code
for the P&L/BS and another one for the cashflow.

On the COA sheet you have an sumif for each account line linking the coa to
the TB.
This will pick up any summing error and differences. Because you can total
that and ensure that the COA summary list balances.

Then you have another sheet summing the COA (lets call it GCOA) to the
P&L/BS groupings summing on the P&L/BS groupings and then you link the P&L/BS
to the lines in the GCOA (this is fixed and the tb only need to change).

With the sumif use the columns so that changes are picked up.

As in =sumif(TB!A:A,A1,TB!D:D)

Another option that is easier to check is to use the extended TB approac
where you still use the COA grouping sheet but put each p/l b/s line as
column headers and pick up the lines using ifs from the TB

ie
A B C D E
1 A/c Desr Code Amt Cash
2 -- -- -- -- BS_CASH
3 1001 Cash BS_CASH $500.01 $500.01

C3=vlookup(A3,COA!A:C,3,false)
E3=if($C3=E$2,$D3,"")

You then sum the lines at the bottom and link those lines.

If you send me a sheet I will try and show you to
his (remove the .cutthis).

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Ang" wrote:

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!
-Angela
Thank you for your help!

Martin - This worked GREAT! Thank you SO much for your assistance!!

"Martin Fishlock" wrote:

Hi Angela,

My post yesterday appears to have got lost. So here goes again.

The basic idea is to have a chart of account (COA) look up sheet where you
provide a table of account codes, descriptions, and the group a group code
for the P&L/BS and another one for the cashflow.

On the COA sheet you have an sumif for each account line linking the coa to
the TB.
This will pick up any summing error and differences. Because you can total
that and ensure that the COA summary list balances.

Then you have another sheet summing the COA (lets call it GCOA) to the
P&L/BS groupings summing on the P&L/BS groupings and then you link the P&L/BS
to the lines in the GCOA (this is fixed and the tb only need to change).

With the sumif use the columns so that changes are picked up.

As in =sumif(TB!A:A,A1,TB!D:D)

Another option that is easier to check is to use the extended TB approac
where you still use the COA grouping sheet but put each p/l b/s line as
column headers and pick up the lines using ifs from the TB

ie
A B C D E
1 A/c Desr Code Amt Cash
2 -- -- -- -- BS_CASH
3 1001 Cash BS_CASH $500.01 $500.01

C3=vlookup(A3,COA!A:C,3,false)
E3=if($C3=E$2,$D3,"")

You then sum the lines at the bottom and link those lines.

If you send me a sheet I will try and show you to
his (remove the .cutthis).

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Ang" wrote:

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!
-Angela
Thank you for your help!