brunothomas
New Member
- Joined
- Oct 4, 2018
- Messages
- 2
Hello guys,
Every month I need to distribute a number of companies to be assessed by diferently graded analysts. This distribution is based on a matrix that attributes authority according to the internal rating/grade that a given company has and also what exposure that company holds.
<tbody>
</tbody>
For instance, Jr Analysts 1 are only authorized to assess a Grade 5 company that holds up to $1.200.000 in exposure. Grade 5 companies with larger exposures need to be escalated to analysts with higher authority.
<tbody>
</tbody>
What I'm trying to is build a formula that returns in the column "Analyst" the lowest-graded analyst station that has authority to assess each of those companies (in the first case, Company 1 with 2.7MM and Grade 4 should return "Jr Analyst 2").
Thanks in advance!
Every month I need to distribute a number of companies to be assessed by diferently graded analysts. This distribution is based on a matrix that attributes authority according to the internal rating/grade that a given company has and also what exposure that company holds.
Analyst\Grade | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Jr Analyst 1 | 4.000.000 | 4.000.000 | 4.000.000 | 1.600.000 | 1.200.000 | 800.000 | 0 | 0 | 0 | 0 |
Jr Analyst 2 | 8.000.000 | 8.000.000 | 8.000.000 | 3.200.000 | 2.400.000 | 1.600.000 | 0 | 0 | 0 | 0 |
Analyst | 16.000.000 | 16.000.000 | 16.000.000 | 8.000.000 | 3.200.000 | 2.400.000 | 1.120.000 | 0 | 0 | 0 |
Senior Analyst | 36.000.000 | 36.000.000 | 36.000.000 | 24.000.000 | 15.000.000 | 7.500.000 | 2.400.000 | 0 | 0 | 0 |
AVP, Analyst | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Manager | 56.000.000 | 56.000.000 | 56.000.000 | 40.000.000 | 27.000.000 | 15.000.000 | 6.000.000 | 640.000 | 640.000 | 640.000 |
Director | 112.000.000 | 112.000.000 | 112.000.000 | 80.000.000 | 48.000.000 | 22.400.000 | 96.000.000 | 3.000.000 | 3.000.000 | 3.000.000 |
<tbody>
</tbody>
For instance, Jr Analysts 1 are only authorized to assess a Grade 5 company that holds up to $1.200.000 in exposure. Grade 5 companies with larger exposures need to be escalated to analysts with higher authority.
Assessment Distribution | |||
Exposure | Grade | Analyst | |
Company 1 | 2.700.000 | 4 | ? |
Company 2 | 8.500.000 | 7 | ? |
Company 3 | 56.000.000 | 5 | ? |
Company 4 | 500.000 | 8 | ? |
<tbody>
</tbody>
What I'm trying to is build a formula that returns in the column "Analyst" the lowest-graded analyst station that has authority to assess each of those companies (in the first case, Company 1 with 2.7MM and Grade 4 should return "Jr Analyst 2").
Thanks in advance!