The Scene
Executives at You Drive Out, a chain of used car lots, are brainstorming about how to ensure better
financial performance.
Alicia is the chief financial officer (CFO), and Manuel is the controller.
Alicia: Manuel, I’m thinking about switching our minimum credit score cutoff from 430 to 450 to
improve our overall loan performance, that is, to increase the proportion of payments that our
customers make on loans.
Manuel: You want me to compare the loan default rates for these?
Alicia: Yes, that’s the idea.
Manuel: Any particular format?
Alicia: No, but whatever you create should show the difference in overall loan currency with the impact
on sales for the two cutoff numbers. And be sure to do the analysis by dealer with the dealer street
address. Pivot tables could be useful. It would also be helpful to see the analysis by vehicle category.
Requirements
1. Plan the analysis by completing the following steps based on the conversation:
a. State the overall objectives of the analysis.
b. Identify assumptions embedded in the conversation.
c. Verify that attributes required for the analysis are available in credit Score data.xlsx.
Data definitions for the attributes are available in Table 1, and a note about primary keys and data types
in Microsoft Excelt and Accesst is available in Appendix A.
d. Based on credit score values, which set of loans should be compared to which other set of loans?
e. Decide the use of each attribute in the analysis. That is, how is each attribute relevant to the analysis?
f. Scan the data values to find outliers or other situations at odds with assumptions embedded in the
conversation.
g. Specify formulas (spreadsheet) or expressions (database manager) for new variables to be
constructed from the attributes supplied in the file creditScoredata.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 pivot tables
2. Use your responses in the first requirement to guide an analysis that compares the loan performance
for the two cutoff points for the loans in the file (creditScoredata.xlsx) and provide the executives the
results in a form that makes sense to them. Use 9/1/2013 as the reporting date. Perform the analysis as
follows:
a. Develop a spreadsheet model for the analysis.
Include formulas and columns, pivot tables, etc. as needed.
3. Write a one–paragraph memo to the CFO with your recommendation and rationale for it.
4. Based on your experience in the case:
a. Characterize criteria for choosing analysis features.
b. Identify audit and control issues associated with spreadsheets
