Module 2 Excel assignment
.docx
keyboard_arrow_up
School
Sam Houston State University *
*We aren’t endorsed by this school
Course
HLTH-336
Subject
Industrial Engineering
Date
Feb 20, 2024
Type
docx
Pages
5
Uploaded by emilyedo
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
-
Production Technician I
-
Area Sales Manager
Pres
ident & C
EO
CIO
BI Di
re
ctor
Sr.
D
BA
Dir
ect
or
of Oper
atio
ns
Enterpri
se Ar
chi
tect
Are
a Sales Mana
ger
Shared Se
rvices Manager
Senior BI Dev
eloper
Prod
uction Mana
ger
Data A
nalyst Databas
e Adminis
trator
Data A
nalyst
IT Support
Account
ant I
Produc
tion Te
chnician I
$- $10.00 $20.00 $30.00 $40.00 $50.00 $60.00 $70.00 $80.00 $90.00 Total
Total
Row Labels
Count of Position
Production Technician I
136
Production Technician II
57
Area Sales Manager
27
Production Manager
14
Software Engineer
9
IT Support
8
Data Analyst
7
Database Administrator
5
Network Engineer
5
Sr. Network Engineer
5
BI Developer
4
Administrative Assistant
3
Senior BI Developer
3
Sales Manager
3
Accountant I
3
IT Manager - DB
2
Shared Services Manager
2
Sr. DBA
2
Sr. Accountant
2
Data Analyst 1
Enterprise Architect
1
President & CEO
1
Principal Data Architect
1
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help