Module 2 Excel assignment

Module 2 Excel Assignment instructions: Credit risk assignment: 1. Download the excel file Credit risk data. 2. Create different pivot tables to answer the following questions using the Credit risk data (1 st worksheet): a. How many loans are for new car? (Create and paste the pivot table that gives the count of each type of loan) 104 Row Labels Count of Loan Purpose Business 44 Education 23 Furniture 85 Large Appliance 4 New Car 104 Other 6 Repairs 12 Retraining 2 Small Appliance 105 40 Grand Total 425 b. What is the average checking and savings account amount based on the type of loan? (The numbers in the pivot table should be formatted to currency with two decimal places) 1,048.01 & 1,812.56 Row Labels Average of Checking Average of Savings Business 1,764.70 1,421.82 Education 726.70 1,337.39 Furniture 740.04 1,916.22 Large Appliance - 4,555.50 New Car 1,359.78 1,518.74 Other 379.17 979.67 Repairs 339.83 753.50

Retraining 322.00 301.50 Small Appliance 1,167.70 2,393.32 Used Car 427.98 1,778.68 Grand Total 1,048.01 1,812.56 c. What is the are the majority of loans for this bank? For Hight Risk loans – large appliances, new cars, education, other, furniture, business. i. (paste a pivot chart that shows the percentage of high and low risk loan for the different type of loan) Busine ss Education Furnit ur e Lar ge Applian ce New Car Other Repai rs Retrai ni ng Sma ll Appliance Used C ar 0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 70.00% 80.00% High Low d. Write a short recommendation you would make to the Bank executives base on your analysis of the data. Support your reasoning with the pivot tables you calculated for the problem. - A recommendation that I will give to the bank executives based on my analysis of the data would be focus in on the high risk credit use and see what can be done to tackle these high percentages. HR Data assignment: 1. Open the HR Dataset V13 (click on the second worksheet in the Credit Risk data) 2. Create different pivot tables to answer the following questions about the HRDataset a. What are the top 3 positions in the company based on the count of position? (Create a pivot table and chart showing the percentage of the top 3 position in the company) - Production Technician I

IT Director 1 Software Engineering Manager 1 Data Architect 1 IT Manager - Support 1 Director of Sales 1 CIO 1 BI Director 1 Director of Operations 1 IT Manager - Infra 1 Grand Total 310 b. What is the top sources of recruitment for the IT/IS department? - 13 Count of RecruitmentSource Column Labels Row Labels Admin Offices Executive Office IT/ IS Productio n Sale s Software Engineering Grand Total Billboard 13 2 1 16 Careerbuilder 1 1 Company Intranet - Partner 1 1 Diversity Job Fair 4 4 17 2 2 29 Employee Referral 13 16 2 31 Glassdoor 5 9 14 Indeed 8 8 Information Session 2 2 4 Internet Search 1 4 1 6 MBTA ads 15 1 1 17 Monster.com 1 2 16 4 1 24 Newspager/Magazine 17 1 18 On-campus Recruiting 1 11 12 On-line Web application 1 1 Other 1 6 2 9 Pay Per Click 1 1 Pay Per Click - Google 1 1 1 10 6 2 21 Professional Society 4 15 1 20 Search Engine - Google Bing Yahoo 1 22 2 25 Social Networks - Facebook Twitter etc 9 2 11 Vendor Referral 8 6 1 15 Website Banner Ads 2 4 7 13 Word of Mouth 13 13 Grand Total 10 1 50 208 31 10 310

c. Are there areas of the company where pay is not equitable? - If we compare the CEO making $80 an hour to an Administrative Assistant making about $14 an hour it shows that there is some inequity in pay. Row Labels Average of PayRate President & CEO $ 80.00 IT Director $ 65.00 CIO $ 65.00 IT Manager - Support $ 64.00 BI Director $ 63.50 IT Manager - Infra $ 63.00 Sr. DBA $ 61.65 Director of Sales $ 60.00 Director of Operations $ 60.00 Principal Data Architect $ 58.50 Enterprise Architect $ 58.20 Sales Manager $ 56.75 Area Sales Manager $ 55.22 Data Architect $ 55.00 Shared Services Manager $ 55.00 Sr. Network Engineer $ 54.46 Senior BI Developer $ 51.17 Software Engineer $ 51.07 Production Manager $ 49.68 BI Developer $ 45.25 Data Analyst $ 45.00 IT Manager - DB $ 41.50 Database Administrator $ 41.12 Network Engineer $ 38.82 Data Analyst $ 37.51 Sr. Accountant $ 34.95 IT Support $ 34.61 Software Engineering Manager $ 27.00 Accountant I $ 26.83 Production Technician II $ 25.41 Production Technician I $ 19.10 Administrative Assistant $ 14.22 Grand Total 31.23351613 Answer each bullet point in the word file and paste the corresponding pivot table under each bullet point. Turn in both the word and excel file.