A bank promotion project involves data collection from a group of customers who received a call about opening new credit lines. Demographics are the basic data, but another factor this bank considers for credit estimation is the type of car customers own. Download Assignment7_DATAFall22.xlsx The following items are collected:

Personal Data:

Age: years

Job: 12 categories

Marital: 4 categories

Education: 8 different categories

Default: if there was a default payment in the record

Housing: house owner or renting (yes/no)

Loan: has an outstanding loan or not (yes/no)

Duration: call time in seconds

Outcome: customer did open the new line of credit or not (yes/no)

Car Data:

Car_value: estimated dollar value of customer car

Mfg_year: manufacturing year of car

Mileage: reported car mileage

Fuel_type: CNG/Petrol/Diesel

Color

Doors

ABS: having ABS system (1/0 as yes/no)

Central lock: having the system(1/0 as yes/no)

Powered_Window: having the system(1/0 as yes/no)

Categorical items include unknown status for those records the bank could not determine the job, marital status, etc.

Build Pivot Tables for each of the following questions. Label each pivot table with the question number on top. (1-9):

1. Count of people at each education level who opened a new credit line and those who did not.
2. Average duration of promotion calls with people with different jobs by their loan status: have an outstanding loan, do not, or the status is unknown.
3. Average age by marital status of people who drive a car valued less than $20,000. Create a pivot chart for this table.
4. Average mileage of the cars based on their fuel type and having ABS system
5. Count of cars having/not having power windows. Use a slicer to show only green and grey cars.
6. Average age of people by their education level. Use a slicer to show only those with a university degree.
7. List of cars by their colors and number of doors, showing the count of cars having central lock in each group.
8. Average mileage of cars by fuel type and manufacturing year. Use conditional formatting to highlight the largest and smallest average mileage of the table by two different background colors.
9. Average estimated price of cars by job and if the owner is also a house owner or rents the residence. Use conditional formatting to highlight the maximum average price.