Analysis of Es trends

• About 2 months ago
• Fixnhour Escrow Protection

I'm seeking a data analyst to conduct an in-depth analysis of my customer portfolio.  You can choose to use the excel spreadsheet as a base, and make pivot tables. or use MS access for example, in addition to the excel, for visual representations in graphs, charts. The excel sheet with pivot tables is one deliverable. a powerpoint presentation is the second deliverable which summarises everything and answers all the questions. (no slide limit) just make sure the PPT is easy to read, and is clear with "what does this statement mean" for example between 2018-2020 the risk rating for this line item decreased, and it increased in 2023, this means it took 3 years for it to increase" 

Create pivot tables, which show the trend in the customers of es risk ratings over the years. for example X customer in 2018 was BBB and 2029 was BB etc. Create any good looking visual representation of data apart from the pivot tables in the excel as well to include in the slides. I also want the slides to include customer trends per year, team trends per year and line items per year. Hence, the excel sheet and PPT should correspond with the data and analysis please.

Here is what the columns mean: 

Customer ID: Ignore: not relevant

ES Rating approval: basically this is the date when the customer ES risk rating assignment/review started. not all customers will have for example 2018 or 2019 because they weren't customers then. so take the dates from as far back as available but make sure all years are accounted for. 

Customer Current ES rating: this is the overall rating of the customer. The if there are 8 line items, and they are all AAA except for 1, which is BBB, the lowest overall score is assigned to the customer, hence, BBB

ES theme: divided into 2 types "PS1 etc" (direct customers) and "motivation, etc" (indirect customers. ES theme is also called line items. every line item has an ES rating, if it doesn't please consider it as not relevant. 

ES rating version: so this is IMPORTANT. Basically the ES rating version " 2" corresponds to ES rating approval. which means "2" is the rating in "2018" these all have to be co related to the rating, line item, and team. This portion is CRITICAL for the analysis. you can also Hardcode the excel sheet to reflect the correct data. 

In terms of averages: please note, weighted averages might screw over the interpretation of the data. so if a customer in a team as even 1 AAA rating, the entire weighted average is screwed. so please find a solution to correctly represent the data. don't do weighted averages for the risk ratings.

The portfolio is in 2 types of customers Direct and Indirect. so the analysis of the direct customers is a separate sheet/slides and indirect is separate. PPT is only one. just divided into direct and indirect customers. 

Answer the following questions: 

- Risk rating trends: Identifying patterns in the customers' E&S risk ratings over time.

-How long does it on average to take for a customer that was initially rated below target (CC, CCC, or D) to move within risk Appetite? (AAA, AA, BBB, BB, per sector) Sector here being defined as team names. PE Energy, PE Agriculture food forestry. all team names can be found under Customer team. 

-Which are the line items that are more commonly affected than others, which cause the risk rating to go below risk appetite (per customer team) –  this question should be approach as about starting below risk appetite (CC, CCC, or D)  There are two types of customers. Direct and indirect. Each has a different *ES theme* which are commonly known as line items. Direct Customers (line items are called Ps1, Ps2 as seen under ES theme) and Indirect Customers (line items are called commitment, motivation etc. under ES theme. Line items here are divided into two distinct types. 

-Within the teams, which line items are commonly affected more. For example, is PS1 causing a drop a risk rating in Energy more often or in AFFW. all line items are to be analysed.

-If a customer starts with a rating within risk appetite, which line item commonly causes the risk rating to go below risk appetite (per team) and how long do customers stay with the lower risk rating (do they eventually move back in within risk appetite or do they stay there? 

-Over the last years what are the trends in the risk rating of the customers. Typically how long does it take for a customer to go into below risk appetite or improve to be rated as within risk appetite.  This is different from the above mentioned because earlier we divided the information by teams, now it's a general trend. 

Please note: i need the base excel file you create with the sheets of indirect and direct customers, the excel analysis, and the PPT as a deliverable. 

Summary

Please don't forget: customer trends, risk rating trends, line item trends, these aforementioned trends and their relationship with the teams they are a part of. and team trends.

any questions? please feel free to ask!