AD Case Study

Requirement: Summary and combined them (in short)

Assumptions

Customers with credit scores lower than 430 are making fewer loan payments and defaulting on their loans. Increasing the credit limit will increase sales (loan currency). Since the CFO wants the analysis by location, she may be thinking that a store(s) may be performing better in the area of defaulted loans than other stores.

Attributes Required and Loan Comparison

Based on a review of the attributes included in creditScore.xlsx and the data definitions provided in Table 1, the required attributes are available. Based on the credit score values, we need to compare loans with credit scores between 430 - 449 to loans with credit scores of 450 and above.

Use and relevance of Loan Sheet

Loan Sheet

loan ID - used to verify loan status, and % of completed payments on the sales spreadsheet.

Dealer ID - used on the pivot table to identify which location had the most current/defaulted loans.

Loan Amount - used with the attribute "downPayment" to determine the sales price of the vehicles.

Down Payment - used with the attribute "

loan Date - used with the added attribute "currentDate" to determine how many months the loan has been active.

Number Payments - used to determine the number of remaining payments (added attribute).

Payments Made - used to determine the following added attributes:

Credit Score - will be used to determine which credit scores are tied to defaulted loans.

Use and Relevance of Dealer Sheet

dealerID - Used in the pivot table on the "sales" tab to determine which location had the highest percentage of current and defaulted loans.

Use and Relevance of Acquisitions Sheet

acquire Date - Used with vehicle ID and sale Date to determine how long the car was on the lot until it was sold.

category - Used with creditScore, dealer ID, and vehicle ID, to determine the ranking of preferred vehicle types based on the credit score.

cost - Used with sales Price to determine profit margin.

make/model/year - Used with category and cost to determine which vehicles are the most popular and result in the largest profit margin.

Use and Relevance of Sales Sheet

vehicle ID - use with loanID to determine:

Loan amount

Loan status

Sale date

Sales price

Down payment

Amount financed.

sale Date - use with attribute "acquireDate" to determine how long the car was on the lot before it sold.

SaleDealerID - Use with sale Date, vehicle ID, loan ID, and loan Status to determine which location sold a particular car, what date it was sold, and if the loan is in current or defaulted status.

Use and Relevance of Acquisitions Sheet

acquire Date - Used with vehicle ID and sale Date to determine how long the car was on the lot until it was sold.

category - Used with creditScore, dealer ID, and vehicle ID, to determine the ranking of preferred vehicle types based on the credit score.

cost - Used with sales Price to determine profit margin.

make/model/year - Used with category and cost to determine which vehicles are the most popular and result in the largest profit margin.

Data Values Outliers/ Other

15 Loans that are in default

14 of those accounts had scores of 430-445

1 account had a credit score of less than 430

The CFO also notices that a particular location is at an increased risk of default.

Location data and percentage of total loans indicated that one location had 10% of loans defaulted.

Modeling or database Querying? Which is better?

Our opinion is that database querying is a better model. Data provided by spreadsheets can often be limited and all variables are often not accounted for. There is an increased risk of excel crashing if one excel document contains thousands of rows of entries. Pivot tables are complex but very useful.

Recommendation to CFO:

Based on the results of the analysis of all three YouDriveOut locations of loans credit scores ranging from 425 - 449, and 450 and above, below are my findings/recommendation:

Findings: All customers with a credit score of 450 and higher have a loan status of "Current".

Of the 15 loans that are in a "Default" status, 93% have a credit score between 430 to 445.

Loans in a "Default" status currently make up 21% of our total loans.

Missed currency on our defaulted loans is $29,115.00, or 7% of the total loan currency of $360,545.00. Per the findings listed above, I recommend increasing our minimum credit score from 430 to 450.

Criteria for choosing Analysis tools

loanID - this is the criteria that was able to link the loan and the vehicle information.

loan Amount - represented how much people were borrowing.

loan Currency - the amount of money that has been received towards the loan balance.

% loan Currency - how much of the loan should have been paid based on the loan date and the current date (we used 9/1/2013 as per case study instructions).

% Expected loan Currency - how much of the loan has actually been paid back.

loan Status - determines if the loan is in a "current" or "default" status.

CreditScore - filter based on credit score to get detail of the loan.

Audit and control issues in spreadsheets and databases

-Potential for Human error

- Potential for computer crashes and loss of data

- Excel formatting issues may occur due to different versions of excel

- All computers may not be able to run large spreadsheets effectively without lagging or other issues occurring.

Conclusion

Databases and spreadsheets are crucial in the accounting world. Day-to-day operations are run using these critical systems and without them, companies would be much less efficient. Databases are more effective than spreadsheets when collecting large amounts of data over a long period of time.