Financial Reporting - Balance Sheet issues
When writing the Balance Sheet report there is a line item called Profit Period. There are 2 ways to calculate the profit period:
1: All GL's that belong in Revenue, COGS, and Expenses to obtain the Net balance. This is a great way to perform a check & balance against the General Ledger
OR
2: Take all Assets - all Liabilities - all Equity=Profit Period
I'm able to get the calculation in and it works as expected until I we get to 12/31/xx. At the end of each year we have to close each GL for the year. In doing so, all GL's that belong in Revenue, COGS, and Expenses have to be zeroed out to begin the new year with $0. That end of year value gets moved into an Equity GL 3 account which is on the Balance Sheet.
In order to obtain the correct Profit Period calculation throughout the year, I have to exclude any transactions that have a particular transaction type. In this case, I exclude transtype 'Ending Balance'.
The problem now resides on that last transaction. The End of Year transaction "Ending Balance" gets moved from Revenue, COGS, and Expenses GLs into an Equity GL - Retained Earnings. This movement gives the transtype 'Ending Balance', which is what I have to exclude to obtain the correct aggregation for the rest of the year/months.
In the current state of my widget, I need that final EOY number BEFORE it gets moved into the Equity GL account. Writing this query in SQL is no problem at all, however, Sisense wants to return the cumulative number and not that yearly number. I can even isolate this 1 line entry for each year within SQL. However, Sisense is still relatively new to me & I’m unable to obtain the correct calculation for that end of year date 12/31/xx. This number has to be yearly calculations not the cumulative number from the begging of time.
Any help is greatly appreciated.
-
Hi Tonya
If i'm understanding this correctly- all you need to do is only include the transactions for the year AND exclude the 'Ending Balance' Journal entry from the close of year.
If you add to filters to your formula - one for excluding end of year and another to only include transactions in a specific year you should be able to get to the number you are looking for.
How are you looking to represent this number?
regards
Ramon
-
Hi Ramon,
I actually found a work around. It's not ideal but it is currently working.
Since I was able to isolate the 1 transaction for the last day that "moves" the Profit Period amounts to a particular GL, I was able to show that 1 GL on the Balance Sheet.
Ideally we would like to have 1 line for Profit Period, however, I was able to show the 2nd line as "Year End Close". The profit period line item is zero for the last day and "Year End Close" shows that yearly aggregated amount.
Sisense Shows it this way:
This is the view the Executives are use too seeing:
Tonya
Please sign in to leave a comment.
Comments
2 comments