Financial Data analytics. Excel

1. Plan the analysis by completing the following steps based on the conversation:

a. State the overall objectives of the analysis.

The overall objectives identified in the analysis is increasing the minimum cut off credit score which is currently at a rate of 430 to a level of 450. Additionally, measure the sales of loans relative to the credit scores alongside measuring it on the loan currency to measure the loan sales.

Identify assumptions embedded in the conversation.

The assumptions embedded in the conversation are;

· Increasing minimum cutoff credit score for loan from 430 to 450,

· Comparison of loan default rates for increasing the minimum credit score cut off to improve loan performance, &;

· The measurement analysis done should reflect the overall loan currency with the effect it will have on sales as associated with the two cutoff numbers.

b. Verify that attributes required for the analysis are available in creditScore.xlsx. Data definitions for the attributes are available in Table 1, and a note about primary keys and data types in Microsoft Excel and Access is available in Appendix A.

After verifying the CreditScore.xls, we could find sufficient data & variables available for analysis in increasing minimum cut off, loan default rates based on credit score, and loans defaults based on credit score. The variables “creditScore” which is a ‘score created by a credit-rating agency, e.g., Equifax, Transunion, or Experian, representing the credit worthiness of an individual for which the typical range is 300-850 where the lower the score, the less credit worthy the individual is deemed to be’, “numberPayments” which is the ‘Number of monthly payments required for a customer to pay off a loan’, “paymentsMade” that is the ‘Number of monthly payments a customer has made as of the reporting date’, “loanAmount” that represents ‘the Dollar amount of a loan extended to a customer for a vehicle’, & “downPayment” – which is the ‘Dollar amount of the down payment a customer made for a vehicle’ make it viable to calculate and perform the analysis.

c. Based on credit score values, which set of loans should be compared to which other set of loans?

Based on the credit values, the set of loans that should be compared with other set of loans is the loans with credit score below 450, as to those of the set of loans with the credit scores above the 450.

d. Decide the use of each attribute in the analysis. That is, how is each attribute relevant to the analysis?

‘LoanID’ attribute will be used to identify the loan details, ‘dealerID’ attribute will represent the dealer from whom, the loan has been taken. ‘LoanAmount’ will be used to measure the creditworthiness of the customer based on the amount extended to the customer based on the credit scores. ‘downPayment’ will also help to measure the financial health of the customer as to the proportion of downPayment made relative to the Loan amount taken. ‘loanDate’ will help to determine the loan default rates using the total duration in months for the loan relative to the number of payments made to data against the number of payments to have completed based on the loan term from given date. ‘numberPayments’ will be used along with the loanDate for calculating or determining the loan default rate. ‘paymentsMade’ will also be used to ascertain the loan default rate. The ‘creditScore’ is used to measure the loan performance based on the credit score and thus make conclusions on how creditScore has increased loan performance and reduced default rates.

e. Scan the data values to find outliers or other situations at odds with assumptions embedded in the conversation.

After scanning the data values given, we can find that there is an outlier or other situations with assumptions embedded in conversation. Some loans have credit scores below the previous cut off level of 430 which Alicia might want to look at for better clarity of the situation for improved understanding analysis. This might be due to some problem that the details of management are miscommunicated to other dealers for the loan. The potential reason for credit score of 425 identified in the data values should be checked and made sure for establishing a way of comparisons of the loans to that of the increased credit scores.

f. Specify formulas (spreadsheet) or expressions (database manager) for new variables to be constructed from the attributes supplied in the file creditScore.xlsx. To find web-hosted explanations of specific functions and operations, search the web. In each search, include the version number of the program, for example: Excel 2013 pivot tables.

For the first loan details other variables, for new attributes are as follows;

Credit score strata levels 3 & 4,

strata3levels - = IF (I2<450,"1: <450", (IF (I2<470,"2: 450-469","3: >469")))

strata4levels - = IF (I2<430,"0:<430", IF (I2<450,"1:430-449", (IF (I2<470,"2:450-469","3: >469"))))

pymtsForCurrent: FirstLoan - = MIN(DATEDIF(E2,"9/1/2013","m"), G2)

loanCurrency: FirstLoan - =IF((H2/L2<=1), H2/L2, 1)

vehiclePrice: First loan - =C2+D2

proportionPaidDown: First loan - =D2/(C2+D2)

g. If you had your choice of spreadsheet modeling or database querying, which would you choose and why?

If I had the choice of spreadsheet modeling or database querying, I would choose and spreadsheet modeling because using with MS Excel, it is the best choice with huge data including over thousands of cells. I am very familiar with it, and use of VLOOKUPS function would be much more helpful and easy for sorting and copying data as required for VLOOKUP function. The analysis using spreadsheet modeling can be easily auditable.