+1(978)310-4246 credencewriters@gmail.com
  

Must be completed within the first hour. This assignent is a excel spreadsheet. Comes with questions that must be answered.1.CreditRiskData

Months Months

Loan Purpose Checking Savings Customer Employed Gender Marital Status Age Housing Years Job Credit Risk 1d. Employment 1e. Value

Small Appliance $0 $739 13 12 M Single 23 Own 3 Unskilled Low 1a. Unemployed

Furniture $0 $1,230 25 0 M Divorced 32 Own 1 Skilled High 1b. New Car/ $5,000+ Checking

New Car $0 $389 19 119 M Single 38 Own 4 Management High 1c. Total Savings – Women

Furniture $638 $347 13 14 M Single 36 Own 2 Unskilled High

Education $963 $4,754 40 45 M Single 31 Rent 3 Skilled Low

Furniture $2,827 $0 11 13 M Married 25 Own 1 Skilled Low

New Car $0 $229 13 16 M Married 26 Own 3 Unskilled Low

Business $0 $533 14 2 M Single 27 Own 1 Unskilled Low

Small Appliance $6,509 $493 37 9 M Single 25 Own 2 Skilled High

Small Appliance $966 $0 25 4 F Divorced 43 Own 1 Skilled High

Business $0 $989 49 0 M Single 32 Rent 2 Management High

New Car $0 $3,305 11 15 M Single 34 Rent 2 Unskilled Low

Business $322 $578 10 14 M Married 26 Own 1 Skilled Low

New Car $0 $821 25 63 M Single 44 Own 1 Skilled High

New Car $396 $228 13 26 M Single 46 Own 3 Unskilled Low

Used Car $0 $129 31 8 M Divorced 39 Own 4 Management Low

Furniture $652 $732 49 4 F Divorced 25 Own 2 Skilled High

New Car $708 $683 13 33 M Single 31 Own 2 Skilled Low

Repairs $207 $0 28 116 M Single 47 Own 4 Skilled Low

Education $287 $12,348 7 2 F Divorced 23 Rent 2 Skilled High

Furniture $0 $17,545 34 16 F Divorced 22 Own 4 Skilled High

Furniture $101 $3,871 13 5 F Divorced 26 Rent 4 Skilled High

Furniture $0 $0 25 23 M Married 19 Own 4 Skilled High

Furniture $0 $485 37 23 F Divorced 27 Own 2 Management High

New Car $0 $10,723 11 15 M Single 39 Rent 2 Unskilled Low

Business $141 $245 22 33 M Single 26 Own 3 Skilled Low

Used Car $0 $0 19 58 M Single 50 Other 4 Skilled High

Used Car $2,484 $0 49 46 M Single 34 Other 1 Skilled Low

Small Appliance $237 $236 37 24 M Single 23 Rent 4 Skilled Low

Small Appliance $0 $485 19 12 M Single 23 Own 2 Skilled Low

Education $335 $1,708 37 7 M Single 46 Other 4 Skilled High

Small Appliance $3,565 $0 31 32 M Single 35 Own 3 Skilled Low

Small Appliance $0 $407 13 2 F Divorced 28 Own 2 Skilled Low

Business $16,647 $895 16 34 M Single 25 Rent 4 Skilled Low

Business $0 $150 49 46 F Divorced 36 Rent 4 Skilled High

Small Appliance $0 $490 5 41 M Single 41 Own 1 Unskilled Low

Furniture $0 $162 25 1 M Divorced 54 Own 1 Skilled High

Small Appliance $940 $715 9 40 F Divorced 43 Own 2 Unskilled Low

Small Appliance $0 $323 49 42 M Married 33 Own 1 Skilled High

New Car $0 $128 13 74 M Single 34 Own 3 Skilled High

Other $218 $0 49 0 M Single 39 Other 4 Unemployed Low

Used Car $0 $109 25 26 M Single 34 Own 3 Unskilled Low

Small Appliance $16,935 $189 37 60 M Single 30 Own 2 Skilled Low

Furniture $664 $537 31 33 M Single 48 Own 2 Skilled High

Furniture $150 $6,520 12 1 F Divorced 19 Own 1 Skilled Low

Small Appliance $0 $138 7 119 M Married 29 Rent 2 SkiEXAM 1 INSTRUCTIONS

Tab 1 – Use the basic Excel functions (i.e. VLOOKUP, SUMIF,etc.) to calculate the missing
values including:

 1a. #Unemployed (having 0 months of employment)
 1b. #New Car/$5000+ Checking (Use Loan Purpose and Checking)
 1c. Total Savings – Women (Use Savings and Gender)
 1d. Employment (Use Months Employed to indicate “YES” or “NO”)
 1e. Value (Use Checking and Savings to find total)

Tab 2-4 Use Data Visualization to create a PivotTable and insert a minimum of 2 of the
chartvisualization types (i.e. Line, Bar, Pie, Column, Area, Scatter, Bubble, Conditional
Formatting, and Frequency Distribution) on each tab to display a graphical depiction of the data.
PLEASE NOTE: YOU MUST USE DIFFERENT CHART TYPES ON EACH TAB.

Tab 5 Calculate the values below (Use HLOOKUP etc.)

 5a. Find Item Cost and Order Date
 5b. Find Item Description and Cost per order

Tab 6 What can you conclude about internet usage based on the data for each? (Use your
knowledge of evaluating data)

 6aii. 35-44 year age group
 6aiii. 55 and over age group
 6bii. Less than High School Graduates
 6biii. Bachelor’s Degree or higher

Tab 7 Create a formula that will use MATCH and COUNTIF based on the Input Field to
calculate the following:

 7a. Row Number – First
 7b. Row Number – Last
 7c. Cost per Order for First Row
 7d. Cost per Order for Last Row

HINT: You will need to reference the Input Field in your formula so that you can change only
that field and update all values.

  
error: Content is protected !!