Good morning Community –
I have a challenge that I have been grappling with for a few days that I have yet to crack and wanted to solicit some ideas from all of you.
What I am trying to do is for each Record ID, answer some questions about the Base Entity. A base entity is any entity at tier 0 and all the units above it is, in some way, its owners. A base entity will have a Regarded Owner(s), a Regarded CFC(s), First Tier CFC(s) and a US Shareholder(s).
Also, the below represents some of the more complex scenarios, most of the work is a lot more linear (except for the top-tier CFC) then this, but these are troubling me the most.
Any time you see Tiers listed more than once, that represents a split-ownership. So, in Scenario 1:
- Tier 2 AU700A is owned by both AU700 and T-TNH
Scenario 1 | | | | |
| | | | | | |
RecordID | TIER | GTN | CY_ENTITY TYPE | | |
21 | 6 | T-TNC | US | <--USSH | |
21 | 5 | NL881 | CFC | <-- First Level CFC | |
21 | 4 | NL123 | FDE | | |
21 | 3 | AU700 | CFC | | |
21 | 3 | T-TNH | US | <--USSH | |
21 | 2 | AU700A | CFC | <-- Regarded CFC & First Level CFC |
21 | 1 | AU700B | CFP | <-- Regarded Owner | |
21 | 0 | AU07390 | FDE | <-- Base Entity (always Tier 0) | |
| | | | | | |
| | | | | | |
| | | | | | |
| | | | | | |
Scenario 2 | | | | |
| | | | | | |
RecordID | TIER | GTN | CY_ENTITY TYPE | | |
193 | 5 | BSR | US | <--USSH | |
193 | 5 | IPG | US | <--USSH | |
193 | 4 | CI414 | CFC | <--First Tier CFC | |
193 | 4 | IPG | US | <--USSH | |
193 | 3 | BSR | US | <--USSH | |
193 | 3 | CI414C | CFC | <--First Tier CFC | |
193 | 3 | T-TNC | US | <--USSH | |
193 | 2 | BSR | US | <--USSH | |
193 | 2 | CO580 | CFC | <--First Tier CFC | |
193 | 2 | IPG | US | <--USSH | |
193 | 1 | CO658 | CFC | <-- Regarded Owner, Regarded CFC, and First Tier CFC |
193 | 0 | VN658 | BRANCH | <-- Base Entity | |
| | | | | | |
| | | | | | |
Questions I Need to Answer: | | |
1) Who is the Base Entities Regarded Owner? | |
2) Who are the Base Entities Regarded CFC? | | |
3) Who Are the Base Entities First Level CFC? 4) Who are the US Shareholder? | | |
| | | | | | |
Rules | | | | | |
Regarded Owner is a CFP or CFC that is closest in the structure to the base entity. (AU700B is the regarded owner |
Regarded CFC is a CFC that is closest to the Base Entity. (AU700A is the regarded CFC) |
A First Level CFC is a CFC who's direct Owner is a US company (AU700A and NL881 are first level CFC's) |
A US Company is a US Shareholder (USSH) | | |
Challenge
- In scenario 1, I have been trying to figure out a way to look up the entire chain to get to the top level CFC’s and have not had much luck. So starting with Tier 3 (AU700) which is a CFC, I need to look the rest of the way up the structure to see if there are any more CFC’s – which there is. I then need to look above that and see if there are any CFC above that or a US entity. If there are more CFC’s then I need to keep looking up the chain, id it a USSH, then I am done. I though using a Max tier would work, but then I would miss Tier 2 AU700A which is also a First Tier CFC.
- Once I find a regarded owner, I should stop looking for a regarded owner going forward.
I was thinking some sort of iterative macro, but not sure how to look up and down the entire structure. Not just one above or one below. Maybe there is a way using an iterative macro with a Multi-Row formula tool, but not sure about that either.
At the end of the day, I would like to see in a new column (it could be a seperate column for each question) which entity satisfies the answer to one of my questions.
Thanks,
Seth
