Please note that Project assignments are like exams. You are welcome to ask me to clarify the instructions, but not whether you have done something correctly in your project files, just as your other exams will not be reviewed before you submit them.

 

Mac Users:

Refer to the Mac Users page for a list of Excel skills that may be performed differently on a Mac
 

How this will benefit you

Practice creating deliverables used in the workplace
 

Tools you will use

Microsoft Excel
 

Skills you will use

VLOOKUP
Regression
 

Components

Download the Excel starter file: Project File Excel RAW DATA Fall 2024.xlsx 
For this project, think about how you used VLOOKUP in the labs
Select a value from one table
Look up that same value in a second (reference) table
Find other information for that value in the reference table
Add this "other information" to a different column (the column where you are creating the VLOOKUP function)
Complete all the tasks below within the Excel starter file provided

On one New Sheet, create all of the following reference tables to use with VLOOKUP
Separate each reference table with one blank column so it is clear that there are four separate tables
For the Loan Status reference table: Fully Paid will become 1, Charged Off will become 0
For the Loan Term reference table: 36 months will become 36, 60 months will become 60
For the Loan Grade reference table: A will become 1, B will become 2, etc. through G will become 7
For the Home Ownership reference table: Own will become 1, Mortgage will become 2, Rent will become 3

On one New Sheet, use VLOOKUP to transform non-numeric data to numeric data
One column for Loan Status
One column for Loan Term
One column for Loan Grade
One column for Home Ownership
When you are done, there should be four contiguous (next to one another) columns containing number values

Leave the VLOOKUP formulas as is because they will be graded
Make a copy of the sheet with the VLOOKUP formulas
Select all of the formulas and choose Copy
With all the formulas still selected, choose Paste As Values (also called Paste Special, then Paste Values)
Hit the Enter key
This will transform the cell contents from the VLOOKUP formulas to the cell values (the numbers resulting from the formulas)

Create a Regression on a new sheet using the following columns of numeric data to determine which variables may affect loan repayment
Loan Status
Loan Term
Loan Grade
Home Ownership
You will have to decide which are the dependent and independent variables before running the regression
You will not have to analyze the regression output for this project
Name all sheets appropriately (see below)
Your completed Excel file should have five sheets with the following content
Raw Data
Reference Tables
VLOOKUP Formulas
VLOOKUP Values
Regression
 

Upload the Excel file to Canvas

Be sure you have completed all requirements before uploading
Rename the Excel file with the name of the assignment as it appears in Canvas, and then one blank space, and then your name as it appears in Canvas (Example: Project File P2 Excel VLOOKUP and Regression Renee De Roche)
Upload the renamed Excel file to the Canvas assignment page and click Submit
Immediately download your submission from Canvas to confirm that you uploaded the correct file and that it is readable and not corrupted
 

How this will be graded

Graded manually by the instructor
Multiple submissions are allowed before the deadline, but only the last submission will be reviewed and graded
Every time you upload a file with the same name, Canvas will automatically add a number to the end of the file name to differentiate the files (this will not be a deduction in points)
Rubric with 8 criteria categories at 1 point each = 8 points total
Criteria categories
File - correct file name
Sheets - correct sheet names
Reference - correct reference tables
VLOOKUP - correct setup
Paste - correct copy and paste tasks
Regression - correct dependent and independent variables
Regression - correct output
Overall - correct overall setup

this is extract of raw data
Loan Status	Loan Term	State	Loan Purpose	Loan Amount	Interest Rate	Monthly Payment	Loan Grade	Home Ownership	Annual Income	Debt-to-Income Ratio	Open Accounts
Fully Paid	 36 months	CT	major_purchase	3600	6.03%	109.57	A	MORTGAGE	110000	10.52	20
Fully Paid	 36 months	NJ	credit_card	15000	14.65%	517.42	C	MORTGAGE	100500	14.56	8
Fully Paid	 36 months	FL	credit_card	9000	12.42%	300.74	B	MORTGAGE	47000	19.1	7
Fully Paid	 36 months	FL	debt_consolidation	6500	16.29%	229.46	D	MORTGAGE	68000	21.85	11
Fully Paid	 36 months	NY	debt_consolidation	9600	13.49%	325.74	C	MORTGAGE	27000	10.22	12
Fully Paid	 36 months	WA	home_improvement	10000	7.90%	312.91	A	MORTGAGE	46000	10.46	12
Fully Paid	 36 months	NC	debt_consolidation	15000	12.42%	501.23	B	MORTGAGE	80000	5.61	8
Fully Paid	 36 months	CA	debt_consolidation	9000	6.62%	276.34	A	MORTGAGE	45000	22.05	7
Fully Paid	 36 months	NY	other	4500	15.96%	158.12	C	MORTGAGE	65000	14.66	20
Fully Paid	 36 months	TX	major_purchase	3000	7.51%	93.34	A	MORTGAGE	65000	27.91	10
Fully Paid	 36 months	NJ	home_improvement	10000	9.91%	322.25	B	MORTGAGE	120000	7.98	7