How to convert trial balance amounts into natural debit/plus and credit (minus) sign

[Taxguy03]

I have a trial balance that is NOT in debit and credit format. I need to change the signage.

1. Liability starts with account 200000 to 399999. Any amounts in these accounts need to be credit with a (minus) sign.

2. Revenue starts with 400000 to 499999 & "ICPRev". Any amounts in these accounts need to be credit with a (minus) sign.

Assets (acct#1*) and expense (acct#5* to 9*) are already correctly presented with Dr/plus. So, we can ignore them.  

I'm trying to use the IF then condition from the Formula Tool but no luck. Hope someone can help. Thanks in advance.

[RolandSchubert · Accepted solution]

Hi @Taxguy03 ,

the problem seems to be related to data types. Account is a string data type, to compare it against a nueric range, you have to convert to numbers using ToNumber.
In addition, I think the [ABC CO] column has to be multiplied by -1. I've modified your workflow. Hope, this is helpful.

Best,

Roland

[Taxguy03]

Hi Roland,

Good morning. This is a great solution. I very much appreciated. Other quick question.

I have a General Ledger Account which is has string data type. The account name is >>  ICPRev. It's a revenue accounts so needs to be minus sign.

Is there a formula to flip the sign for this account (ICPRev) ? You will see that account in the excel file that was originally attached.

Thank you an advance for your help.

[RolandSchubert]

Hi @Taxguy03 ,

you could add one more condition to the IF/ELSEIF/ELSE that already exists:

ELSEIF [Account] = 'ICPRev' THEN
[ABC CO] * (-1)

In case there are additional accounts you need a sign flip, you can alse use:

ELSEIF [Account] IN('ICPRev', 'additional account') THEN
[ABC CO] * (-1)

(replace 'additional account' by the accounts you need)

Does this help?

Best,

Roland

[Taxguy03]

Hi Roland,

So sorry to bother you again. I'm sure I'm doing something wrong. I put a second conditional formula as  you suggested (workflow attached), but getting Unknown variable "ELSEIF" error.

 Help, please help?

[RolandSchubert · Accepted solution]

Sorry, I think, I didn't explain it well. What I meant was, that you add an additional "ELSEIF" to the existing formula, not as a separate formula. It's done now and seem to work.

[Taxguy03]

Thank you, Roland. You saved my tons of hours. Hope to talk to you soon again and have a great day for now.

[Taxguy03]

Hi Roland,

Sorry to bother you again. I have another issue which I need your help. I used your workflow from Monday and added few tools at the front of the workflow to convert  a current columnar format trial balance to stack format (comparable 2018 & 2019). The output doesn't seem right. I don't think the accounts are lining up correctly as I may not have the  Summarize tool condition properly setup.

I really hope you can help.

Attached:

1. Workflow

2. Sample raw data

3. Output should be (answer)

[RolandSchubert · Accepted solution]

Hi @Taxguy03 ,

happy to help, don't worry.

I modified the workflow a bit, especially changed "Join" to "Union" for the files after adding a year.  Added some comments. Let me know, if it works.

Best,

Roland

[Taxguy03]

Hi Ronald,

I have another favor to ask. I need to verify the (income) loss by Entity by performing summation for any account that is greater than 399999 (basically any accounts that starts with *4 to *8, the  revenue and expense accounts). I have attached the stack Trial balance you helped to put together. You can ignore accounts from *1 to *3.

Hope you can help.