| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| format of profit & loss a/c | Charts and Charting in Excel | |||
| Profit and Loss Formula | Excel Worksheet Functions | |||
| Calculating Profit and Loss in one column | Excel Discussion (Misc queries) | |||
| Calculating Profit and Loss in one column | Excel Discussion (Misc queries) | |||
| Calculating Profit and Loss in one column | Excel Worksheet Functions | |||
Hybrid Mode
