Hello - Taking the Impact column as a base in consideration with the Account/Department/Intercompany i need to derive the Journal type column.
Note: The intercompany column plays an important role only in the case of 6 and 7 where Accounts and departments are same but one side the intercompany value is blank and other side is populated.
The below are the different kind of scenarios of journal postings happen in the system.
For example: Journal entry 1, 8, 5, 6 and 7 are B/S Only but having different scenarios.
Journal Entry | Account | Department | Intercompany | Nature | Impact | Journal type |
1 | 110000 | 12345 | DEF | Asset | B/S Only | B/S Reclass |
1 | 120000 | 12345 | DEF | Asset | B/S Only | B/S Reclass |
8 | 230000 | 54321 | LMN | Liability | B/S Only | GND Reclass |
8 | 130000 | 54321 | LMN | Asset | B/S Only | GND Reclass |
2 | 410000 | 12134 | Revenue | IS Only | I/S reclass | |
2 | 420000 | 12134 | Revenue | IS Only | I/S reclass | |
9 | 440000 | 54321 | Revenue | IS Only | GND Reclass | |
9 | 540000 | 54321 | Expense | IS Only | GND Reclass | |
3 | 100000 | 21223 | GHI | Asset | B/s and IS | MAN PL ADJ |
3 | 400000 | 21223 | Revenue | B/s and IS | MAN PL ADJ | |
4 | 220000 | 31234 | Liability | B/s and IS | MAN PL ADJ | |
4 | 500000 | 31234 | Expense | B/s and IS | MAN PL ADJ | |
5 | 100000 | 99999 | Asset | B/S Only | Book to Book transfer | |
5 | 100000 | 88888 | Asset | B/S Only | Book to Book transfer | |
6 | 100000 | 12345 | ABC | Asset | B/S Only | Intercompany transfer |
6 | 100000 | 12345 | Asset | B/S Only | Intercompany transfer | |
7 | 200000 | 12345 | DEF | Liability | B/S Only | Intercompany transfer |
7 | 200000 | 12345 | Liability | B/S Only | Intercompany transfer |
This is how i need to define the Journal type. Please help
@ashaik1 based on this logic
same accounts, same departments but one line is having intercompany and other line does not - Then journal type is Intercompany transfer
then why isn't 3 also intercompany - is i t because of the differnet accounts?
Also what is the logic for MAN PL ADJ classification?
Also will there always be 2 lines?
A couple of ideas. As the previous comment noted, we're missing some of the logic to just write it. I assume Man PL ADJ is some form of manual JE and that those are a pain to classify by nature and also that the reason JE 3 isn't Intercompany is because the manual code overrides Intercompany.
1. Multi row formulas. The mult row tool lets you write the logic in place but you're going to have to nest some conditionals. I would likely approach it as writing a formula that works for the second row in each JE (which you can find by checking for blanks in row +1 on one of the data columns) and then a second formula that backfilled that into the first row of each.
2. Transform the data so that each JE is a single row with the information from both sides. This will let you use regular formulas and not worry about the conditionals. Assuming your entries are all two sided the attached workflow does the transformation.
For MAN PNL ADJ - journal entry is posted between Asset and Revenue or Liability and Expense or vice versa. journal entry 3 cannot be intercompany transfer as it is not same account and same department. there could be more lines in a journal entry.Thanks
Added the formula tool to the transform example and put in the logic to transform back to JE format.
The nested IF is a monster and may not contain all your cases even so.
That said, it classifies the example rows properly.
Should just be tuning the logic and the output formats from here.
You also might want to do some amount of data prep on the incoming fields in case you get different versions of capitalization, spacing, etc. As written it is going to be sensitive to that stuff.
For MAN PNL ADJ - journal entry is posted between Asset and Revenue or Liability and Expense or vice versa. journal entry 3 cannot be intercompany transfer as it is not same account and same department. there could be more lines in a journal entry.Thanks