MR: How can i show a GL balance in Asset or Liabilities depending if the balance is debit or credit

(0) Share

Report

Posted on by Veronika Filonenko

1,261

Hi all,

I have a GL account which depending on its balance nature we need to show either in Assets or Liabilities part of the Balance sheet.

I am looking for a formula in MR to help me manage this?

I though that this would be the way

-in the row definition Column G: Print control: DR/ CR: Print only debit/ credit balances in a row

- in column definition YTD, which should show me the Balance of my account which is 323,599$ CR

But no, the result is that it separates the debit transactions from the credit transactions. And shows amounts (not the balance as I need) on both lines in the Asset and Liabilities section. This is not what I need.

What I expected is to see just the balance of the account which is 323,599.5$ CR appear in the liabilities part.

Or if my balance is 5000$ DR for the next month, I expect to see the amount in Asset section.

Any clue someone?

*This post is locked for comments

I have the same question (0)

All responses (21)

Answers (0)

Veronika Filonenko 1,261 on at

Like (0)

Report

This is what i came up with

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Veronika, we have the same problem. Could you please share the solution you used?

Thanks in advance

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi airagos,

Please have a look at the following site and the comment. dynamicsax-fico.com/.../01 hope this helps.

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the reply Ludwig. I followed the link and were able to get the same results but I guess our issue is a bit different. Our "bank account" total is made up of several dynamics accounts, however some accounts have negative balances and we don't want to include those in the total. For example, if the Bank Account is made up of 3 accounts:

Account1 (140001) has a balance of $2000 USD

Account2 (140002) has a balance of (CR) -$800 USD

Account3 (140003) has a balance of $3000 USD

Then, we would expect Bank Accounts to show a total balance of $5000 USD and the Account2 balance should go the liabilities side for (CR) -$800.

If the next balance all the accounts have a positive balance

Account1 (140001) has a balance of $2000 USD

Account2 (140002) has a balance of $500 USD

Account3 (140003) has a balance of $3000 USD

Then, we would expect Bank Accounts to show a total balance of $5500 USD.

Our row definition is +Account = [14????]

Thanks in advance for your help!

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello airagos,

What I get from your example is that the balance on each of your account decides whether it will be shown on the asset or the liability side, right?

I belive this should be possible by using several of those if-statements for the different rows.

Alternatively, Anna made a comment on the German Website where she setup two rows for each of the bank accounts and separated them by specifying that the first one (on the asset side) should only show debit balances and the other one only credit balances. Maybe that is an alternative that works for you. I guess that it might be worth testing.

Hope this helps,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Ludwig. Yes, the balance of the account decides whether it gets included in the total for "Bank Account" (where bank account is the row description for +Account = [14????]). I've been trying several ways and none of them work. For the formula (IF statements) to work, I'd have to place each Dynamics account in one line and then create a formula to summarize them. But this happens to too many accounts (hundreds) in many places of the GL. Additionally, If we do that, we have to change the report every time a new account in the range is created.

About the comment Anna made, could you please elaborate on how to separate debit and credit balances? If we use the print control "DR", then only the debit transactions/entries are taken into account and the balance shown is not the correct one. Is there other way to achieve that? I'm sorry, for some reason I can't see the comment myself (the page shows as comments off)

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hello airagos,

I see. Can you help me better understanding your setup requirements?

Above you show your bank accounts line by line. Your last reply seems however, to indicate that you summarize the different bank accounts and Report only on the sum of those accounts. I think I got a bit confused about this and it would be great clarifying this for me.

You mentioned that you do not want to include each and every new bank account that you setup. Have you tried classifying those bank accounts that belong somehow together through a main account category in the COA? As MR can refer to those account categories it should not be necessary manipulating the Report each and every time a new bank is setup provided that you setup the MR report with reference to those main account categories.

Another question relates to the bank account and the number of accounts affected by the requirement you have. Why is it that you have so many accounts affected? Do you setup many new ledger accounts each month or is it just an internal or external reporting and accounting requirement?

Would be great if you could update us.

Many thanks,

Ludwig

Was this reply helpful? Yes No

Community Member on at

Like (0)

Report

Hello Ludwig,

yes, sorry, it's a bit confusing. Let's see I can put it in a better way. I'm just going to use the example of the bank accounts to make simple but we have many cases where this applies.

For argument sake, let's say we have 3 bank accounts with overdraft. At any given time, if need be, they can be overdrawn, which means that their balance can be a credit and in that case the balance of those accounts should be reported as a liability (in the line Overdrafts), otherwise they'll be reported as assets (in the line Bank Accounts).

For period July, We'd like to see the report looking like this:

Assets

Bank Accounts $8000

Liabilities

Overdrafts -$500

Based on the following balances of the 3 bank accounts:

Account	Name	Balance
140001	Chase	$3000
140002	Citi	$5000
140003	Credit Swiss	-$500

For August, it’d look like this

Assets

Bank Accounts $1000

Liabilities

Overdrafts -$5500

Base on the following ending balances of those accounts:

Account	Name	Balance
140001	Chase	$1000
140002	Citi	-$5000
140003	Credit Swiss	-$500

Now, let’s say that in September they open another account with Bank of Tokyo, then the account should be included in the report automatically and in the correct section depending on its balance.

Account	Name	Balance
140001	Chase	$1000
140002	Citi	-$800
140003	Credit Swiss	-$300
140004	Bank of Tokio	$3000

In which case, the report should look like this:

Assets

Bank Accounts $4000

Liabilities

Overdrafts -$1100

Our report row definition looks like this:

A Row Code	B Description	C Format Code	E Format Override	F Normal Balance	G Print Control	H Column Restriction	I Row Modifier	J Link to Financial Dimensions
100	ASSETS	DES
101	Bank Accounts							+Account = [14????]
106		DES
108	LIABILITIES	DES
109	Overdrafts							+Account = [14????]

Of course, this reports the same number in both sides. When I used the formula (IF/THEN/ELSE), the balance only gets reported on one side but base on the total of all the accounts, not on the balance of each account.

In this particular case, we are talking about only 3 or 4 accounts, however, there are other lines that include hundreds of accounts and have the same behavior (though they are not bank accounts).

I hope this makes it clear and not more confusing :)

Thanks again in advance for your help

Was this reply helpful? Yes No

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi airagos,

I just replicated your example and setup a MR Report based on the following transactions

Given that row setup I got the following abbreviated report

The issue with this is that newly created accounts need to be included once they are created, which requires an adjustment of the row structure - as you mentioned.

The alternative would be a row structure that does not show all individual accounts but rather reports the sum of all bank accounts. This can be setup as follows:

The result if a MR Report that Looks as follows:

The major advantage of the second approach is that it automatically picks up new accounts without requiring a report modification. The disadvantage is that you cannot see the individual bank accounts. Yet, as far as I am informed reporting on the total balance should be sufficient for external balance sheet / report addressees.

If you want to report on the individual accounts than the fastest way of modifying the report structure would be copying it to Excel, modifying it there and copying it back to MR.

What are your thoughts on these two approaches?

Best regards,

Ludwig

Was this reply helpful? Yes No

Community Member on at

Like (0)

Report

Hi Ludwig,

Thanks a lot. I really appreciate your effort in trying to help us.

Approach number one produces the totals we need. What I see is that if we summarize at the grand total of Assets and Liabilities we have the following results:

Option 1
	07-2016	08-2016	09-2016
Assets	8,000.00	1,000.00	4,000.00
Liabilities	(500.00)	(5,500.00)	(1,100.00)

Option 2
	07-2016	08-2016	09-2016
Assets	7,500.00		2,900.00
Liabilities		(4,500.00)

Then, yes the option 1 produces the results we need but it is impractical to use in the cases where there are hundreds of accounts being summarized. It is also an issue that we could not drill down to see each account but at this point, I'd be happy if I can get the report to work without the drilling down.

In summary, we would need the totals of Option 1 in the format of Option 2. :(

Do you know if there is a limitation in the number of lines of MR? At this point, I feel like the only option viable is number 1 (as impractical as it can be) but we have close to 20000 accounts in Dynamics that will have to be included in the report. In addition to that, we would need to create the extra lines for the formulas (is there a limitation on the complexity or length of a formula?).

Again, Thanks a lot for your help!