We build a number of financial reports direct from the GLPeriodBal table. The table basically summarises Debits and Credits against accounts by month. System Year End journals (basically transferring profit to retained earnings) do not appear to get recorded in the GLPeriodBal table, nor as for as I can figure, are they recorded as a journal anywhere.
This is OK when reporting a balance sheet over a year – (Opening Balances are stored in Period 0), but when I want to report over multiple years a straight cumulation of debits and credits fails to yield correct results past a year end boundary.
Does anyone know where the year end journals are stored?
And a dumb finance question from an engineer; what is so special about year end that it can’t be handled by a normal journal?
I just dealt with this a month or so ago. I can look it up tomorrow but I though the transfer opening balances transferred the income statement account balances to the starting period of retained earnings like you mentioned. Not sure it’s stores the transaction anywhere, I usually review the log from the process.
I believe that table also has the activity for the month for the balance sheet accounts, not the monthly balance which is your opening balance plus activity. So, if you want your cash balance for march, you need your period 0 balance plus period 1 2 and 3 activity. Since your income statement doesn’t get closed out to retained earnings until the end of the year, you are going to need to show a balance for the year to date earnings to plug into the equity section as well. Just sum up all of the income statement accounts and the total of all of those is your earnings number. So to summarize, for every one of your balance sheet accounts you need the opening balance for the year plus every period of activity through the month you are presenting. Then, the sum of all of your income statement accounts for all periods in that year through the month you are presenting.
So that is what you need to do, but the next question is where do you want to do it and that depends on what your using for the financial reports. If you are trying to bring the BAQ into excel via rest then you would probably be fine doing that calculation in the spreadsheet.