Can someone point me in the right direction for the best way to program or
formulate a profit and loss (and other financial reports) from raw data
tables.

For example the raw data is available via ODBC in the format of a general
ledger

GLACCT DESC BEGINBAL PD1BAL PD2BAL PD3BAL PD4BAL ......
1000-00 CASH 10002.00 -20.93 23.00 16.04 100.00
2000-00 A/R 2340.00 102.03 27.42 13.49 40.59
4000-01 SALES 1992.93 999.83 288.42 100.00
4000-02 SALES 1992.93 999.83 288.42 100.00
5000-01 COGS 934.23 253.65 35.67 34.64
5000-02 COGS 934.23 253.65 35.67 34.64
6100-01 UTILITIES 100.00 100.00 100.00 100.00
6100-02 UTILITIES 100.00 100.00 100.00 100.00
ETC
ETC

What is the best way to build a report from this data that is flexible
enough to provide a profit and loss that can be updated by simply refreshing
the data query?
I dont want to have to rebuild the report every time a new expense acct is
added.

If I am looking for something like this how should I build the spreadsheet...
I am quite capable with access but I don't know how to structure this in excel
would you make a sheet for the query data, then pull numbers to named ranges
with vlookup from another sheet? Any thoughts or Ideas would be greatly
appreciated.

current ytd later I will
be adding historicals
Sales 1992.93 12312.10 for ytd last year 2
years ago etc
COGS 934.23 6102.21
Gross Profit 1058.70 6209.89

Utilities 100.00 1200.00
....
....
Total Expenses 212.00 2400.00

Net Profit 846.70 3809.89