Creating a profit and loss from MSQuery Data
 

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

Creating a profit and loss from MSQuery Data
 

I can try giving a small nudge (not quite up to kick-start standards) by
saying that I think you're going to need a worksheet in the workbook that is
basically a couple of tables of accounts. One for Accounts that represent
income, and another table for accounts representing expenses. Might even be
to your advantage at a later date to put each table on its own worksheet.

You're also going to want to look into the use of the SUMPRODUCT() function
to get the totals of all entries for any given account as a single value.
Those formulas could be on a hidden, or just generally not viewed, "helper"
sheet, then on your main sheet you could have a summary of the total of all
expenses/income. Or maybe not - the sheet with the SUMPRODUCT() formulas on
it could act as your summary sheet.

You may even have to resort to the use of at least one macro to determine
the used range of the raw data after the query has been updated - that could
be used to redefine the area referenced by a named range that represents the
latest query results. The name itself could then be used in other formulas.

There is usually help to be had at (remove spaces) HelpFrom @ jlatham site.
com

Hope this gives you some food for thought.


"c ryner" wrote:

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

Creating a profit and loss from MSQuery Data
 

Thanks for the boost. I knew this would be involved but I just didn't know
where to start. I appreciate your words of wisdom. Thanks Again. If I get
too stuck I might need a little more but I will get through it!

"JLatham" wrote:

I can try giving a small nudge (not quite up to kick-start standards) by
saying that I think you're going to need a worksheet in the workbook that is
basically a couple of tables of accounts. One for Accounts that represent
income, and another table for accounts representing expenses. Might even be
to your advantage at a later date to put each table on its own worksheet.

You're also going to want to look into the use of the SUMPRODUCT() function
to get the totals of all entries for any given account as a single value.
Those formulas could be on a hidden, or just generally not viewed, "helper"
sheet, then on your main sheet you could have a summary of the total of all
expenses/income. Or maybe not - the sheet with the SUMPRODUCT() formulas on
it could act as your summary sheet.

You may even have to resort to the use of at least one macro to determine
the used range of the raw data after the query has been updated - that could
be used to redefine the area referenced by a named range that represents the
latest query results. The name itself could then be used in other formulas.

There is usually help to be had at (remove spaces) HelpFrom @ jlatham site.
com

Hope this gives you some food for thought.


"c ryner" wrote:

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