Financial Data analytics. Excel
ISSUES IN ACCOUNTING EDUCATION American Accounting Association Vol. 32, No. 1 DOI: 10.2308/iace-51385 February 2017 pp. 59–66
Analyzing Data for Decision Making: Integrating Spreadsheet Modeling and Database Querying
A. Faye Borthick Georgia State University
Gary P. Schneider California State University, Monterey Bay
Therese R. Viscelli Auburn University
ABSTRACT: Although students begin with a spreadsheet to analyze the effect of a change in credit score cutoff on loan performance on sales, they discover that this approach becomes unwieldy as the analysis becomes more complex. As more attributes from more sheets are required, the spreadsheet solution becomes difficult to implement or audit. The case thus motivates students to develop the database skills of importing the spreadsheet data into a database manager, joining tables, and performing the analysis using database querying. The case requires students to think critically to model the business situation in spreadsheet formulas and database query expressions, structure relationships among the attributes across data tables, and manipulate attributes to achieve the business objective. The case is suitable for courses in accounting systems, managerial accounting, decision making, and database systems. Objective questions are provided for assessing students’ ability to analyze transaction records using both spreadsheet and database tools.
Keywords: business analytics; credit score analysis; critical thinking; data analytics; data reporting; decision making; querying; spreadsheet model.
THE CASE
The Scene
Executives at YouDriveOut, 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.
The authors are indebted to Ron Clark, John Morris, Ann O’Brien, Gary Smith, Lori Holder-Webb (editor), Valaria Vendrzyk (associate editor), anonymous reviewers, and participants at a session of the 2013 American Accounting Association Annual Meeting for helpful comments about this project. The case received the 2016 Accounting Information Systems Section Midyear Meeting Outstanding Education Case Award sponsored by the Institute of Management Accountants.
The case received the 2016 Accounting Information Systems Section Midyear Meeting Outstanding Education Case Award sponsored by the Institute of Management Accountants.
Editor’s note: Accepted by Lori Holder-Webb.
Submitted: June 2014 Accepted: January 2016
Published Online: January 2016
59
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 creditScore.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 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
h. If you had your choice of spreadsheet modeling or database querying, which would you choose and why?
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 (creditScore.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.
b. Import the data (loan, dealer, sales, and acquisitions sheets) into a database manager.
c. Query to analyze the data in the database manager.
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 tools.
b. Identify audit and control issues associated with spreadsheets and databases.
5. Answer questions your instructor will provide about the business problem and its analysis.
6. Optional: In addition to the analysis performed on the supplied data, what other analyses would be relevant to
YouDriveOut management in deciding whether to change the credit score cutoff?
60 Borthick, Schneider, and Viscelli
Issues in Accounting Education Volume 32, Number 1, 2017
APPENDIX A
Note about Primary Keys and Data Types in Microsoft Excelt and Accesst
In Accesst, each table has a primary key that uniquely identifies each row. The best practice is to designate an attribute that
makes business sense, e.g., loanID uniquely identifies a loan, distinguishing it from all other loans in the table. If no attribute
makes business sense, Accesst will generate a primary key consisting of a sequence number beginning with 1.
Number representation matters because different representations may give different results in the far-right decimal digits, a
condition that is sure to befuddle naı̈ve users. Getting different results from the same calculations in different programs is not a
comforting outcome. Microsoft Excelt represents numeric values as double-precision floating-point numbers, which gives 15
digits of precision with an 11-bit exponent and a 52-bit mantissa. Notwithstanding this level of precision, the representation is
generally inexact, which leads to round-off and truncation errors in calculations, including with financial data.
The default data type when numeric data are imported from Microsoft Excelt into Microsoft Accesst is double-precision
floating point. This representation is undesirable in Accesst because of the imprecision of the values and the unnecessary use of
TABLE 1
Data Attributes creditScore.xlsx
Sheet/Attributea Explanation
loan: Loans that the company has made to customers buying its used vehicles loanID Unique identifier for a loan
dealerID Unique identifier for a dealer
loanAmount Dollar amount of a loan extended to a customer for a vehicle
downPayment Dollar amount of the down payment a customer made for a vehicle. The sum of loanAmount þ downPayment ¼ the sale price of a vehicle sold on loan.
loanDate Date a loan was initiated
monthlyPayment Dollar amount of the monthly payment from a customer to repay (satisfy) a loan, including principal and
interest
numberPayments Number of monthly payments required for a customer to pay off a loan
paymentsMade Number of monthly payments a customer has made as of the reporting date
creditScore A score created by a credit-rating agency, e.g., Equifax, Transunion, or Experian, representing the credit
worthiness of an individual. A typical range is 300-850 where the lower the score, the less credit worthy the
individual is deemed to be.
dealer: Dealers that sell vehicles for the company dealerID Unique identifier for a dealer
name Name of the dealer
street Street address of the dealer
sales: Sales of vehicles vehicleID Unique identifier for a vehicle
saleDate Date a vehicle was sold
saleDealerID Unique identifier for the dealer selling a vehicle
loanID Unique identifier for a loan associated with the sale of a vehicle
acquisitions: Vehicles that the company acquired, usually by purchasing them at auction vehicleID Unique identifier for a vehicle
acquireDate Date the company acquired a vehicle
category Category of a vehicle: hybrid, sedan, SUV, or truck
cost Acquisition cost of a vehicle
make Brand of a vehicle
model Model of a vehicle
year Model year of a vehicle
a Sheet names appear in bold.
Analyzing Data for Decision Making: Integrating Spreadsheet Modeling and Database Querying 61
Issues in Accounting Education Volume 32, Number 1, 2017
disk space. Each double-precision floating point value takes up 8 bytes of disk space in Accesst. To avoid using double-
precision floating-point representation when importing data from Excelt to Accesst, reset the data type in Accesst when the
data are exact, e.g., dealerID, loanID, and dollar amount attributes in this case. Here are the integer data type possibilities with
the ranges of values they can represent:
Microsoft Accesst
Integer Data Type (Length) Range of Representation
Byte (1 byte) 0 to 255
Integer (2 bytes) �32,768 to 32,768 Long integer (4 bytes) �2 billion to 2 billion
62 Borthick, Schneider, and Viscelli
Issues in Accounting Education Volume 32, Number 1, 2017
CASE LEARNING OBJECTIVES AND IMPLEMENTATION GUIDANCE
This case prompts students to make sense of a business situation and analyze transaction data to provide insight for the
business to guide action, as organizations are doing (Chen, Chiang, and Storey 2012; McAfee and Brynjolfsson 2012;
Davenport 2013), an approach known as data-driven decision making (Redman 2008; Dearborn 2015). Not only will
accountants encounter data analytics applied to corporate strategy, but they will also see them applied in accounting and
finance. For example, in addition to supporting business strategy, chief financial officers need to use information to meet their
regulatory requirements (Gnatovich 2007; Johnson 2012), perform finance functions (Griffin and Danson 2012; Cokins 2013),
and detect enterprise fraud (Griffin 2012). PricewaterhouseCoopers (PwC 2015) has offered a white paper on the implications
for accounting work in a data-driven business environment, and the business press has reported significant changes in
accounting work, shifting to fewer accountants doing more analytic tasks (Johnson 2015; Mims 2015; Monga 2015).
Although the analysis in the case could be performed completely with a database query manager, practicing accountants
often begin their analyses with spreadsheets because they are skilled with spreadsheets and often receive data in spreadsheet
files. Students who have work experience, including internships, have seen the pervasive use of spreadsheets in accounting. In
fact, the familiarity is so strong that many students believe that a spreadsheet program is the only analysis tool they will need in
practice. Thus, this case is designed to prompt students to reevaluate that belief and recognize the need to become skilled with
both spreadsheet modeling and database querying as analysis tools. In this case, when the spreadsheet model becomes unwieldy
for integrating needed data attributes, students import the spreadsheet data into a database query manager to perform the
analysis. Furthermore, as spreadsheet solutions become more complex, the likelihood of design errors that are difficult to detect
increases (Panko and Aurigemma 2010). A database querying approach is often more desirable in complex situations because
all the data manipulations are documented in the queries, making them easier to audit.
From the conversation, students build mental situation models that enable them to make inferences about the business
process, an experience that helps them develop situation models for the next business situation they encounter (Zwaan and
Radvansky 1998; Barsalou 1999).
The specific learning objectives of the case are for students to learn to:
1. Plan data analysis based on the business situation and the available data.
2. Analyze the data to compare loan performance for the two cutoff points for the credit scores:
a. Develop a spreadsheet model for the analysis.
b. Import the spreadsheet data into a database manager.
c. Develop the analysis with database querying.
3. Write a one-paragraph memorandum to the CFO with a recommendation to raise the minimum credit score or to keep
the current minimum score and a rationale for the recommendation.
4. Based on their experience in the case, they learn to:
a. Characterize criteria for choosing analysis tools.
b. Identify audit and control issues that can arise when working with spreadsheets and databases.
In Borthick’s (1996) categorization of learning objectives for the accounting information systems (AIS) course, the case
requires (1) extracting or creating needed information and (2) analyzing information to solve business problems. These
objectives incorporate the elements of thought for critical thinking established by Paul (1990), Elder (1999), and Nosich (2011).
The terms critical thinking and analytical thinking are often used interchangeably although critical thinking is more commonly used in non-computational disciplines than analytical thinking.
Level of Difficulty
The case is intended for students who can perform the spreadsheet operations of creating formulas with IF statements,
VLOOKUP statements, and date built-in functions, generating pivot tables, formatting attributes, and naming, saving, and
retrieving files. The required database query operations are importing data, joining tables, developing expressions, using built-
in functions, applying Group By, formatting and sorting results, and naming, saving, and retrieving queries.
Implementation Approaches
Although the requirements direct students to prepare the spreadsheet version first and then do the analysis with database
querying, students who already have database querying expertise could be permitted to choose which software tool to use first,
based on their understanding of the analysis requirements. The spreadsheet-first approach works well for students with little or
no database querying expertise. When they reach the point where the limitations of the spreadsheet approach become apparent,
they are receptive to pausing their work long enough to develop sufficient database querying expertise to analyze the data with
Analyzing Data for Decision Making: Integrating Spreadsheet Modeling and Database Querying 63
Issues in Accounting Education Volume 32, Number 1, 2017
the query manager. When our students have not had database querying experience, we have employed this pause-and-develop
approach, i.e., they pause the analysis long enough to develop querying skill. To develop database querying skill, we have used
Borthick, Jones, and Kim’s (2001) case on assuring compliance for responses to website referrals, which requires about ten
hours. Bradford, Samuels, and Wood’s (2008) Beta Auto Dealers case could also be used for this purpose. Students who are
unfamiliar with pivot tables need about an hour to develop proficiency with them.
The second approach, allowing students to choose the software tool, is ideal for students who are already proficient with
both spreadsheet modeling and database querying. Posing the software choice to students gives them an opportunity to match
the tool to the task and to experience how well the tool matches the task.
Use in Courses
The case is suitable for courses in AIS, managerial decision making, and database systems. It has been used in over 20
sections over three years in undergraduate AIS courses at two large public universities by three instructors. It has been used at a
third large public university by a fourth instructor in a capstone course in a master’s accounting degree program. It can be
completed individually or in teams, in class or outside of class, or in a combination of the two. In our experience, completion
time ranges from 4–8 hours for students once they have the needed spreadsheet and database query skills. We make the case
materials available to students on a course website.
Students have answered the practice questions in university-provided learning management systems (LMS) that time and
score the questions and enable students to see the feedback for each question. The practice questions, which can be offered at
any time during students’ work on the case, illustrate the kind of thinking students need to do to perform the analysis and give
students practice for quiz and exam questions if they are used.
Efficacy
Practitioner Reaction
Fourteen practitioners (partners and other high-level professionals from Big 4 and regional accounting firms and finance-
accounting professionals from corporations) reacted to the case in a group setting. They thought it would be a superb learning
experience for solving business problems on the job because it captured major features of their work environments. They saw
the case as authentic because the assignment was given in a brief conversation with minimal instruction, it concerned a real
problem, and it required analyzing data. They confirmed that their environments were characterized by messy data that did not
uniformly comply with organizational policies. They were unanimous in wanting to hire graduates who could accept an
assignment in a short conversation, get relevant data, analyze the data, recognize when the data did not match everyone’s
assumptions, accommodate mismatches in the analysis, and report analysis results succinctly with a recommendation and a
rationale for it. Because they believed that developing data analytic expertise was a developmental process, they recommended
that all accounting courses include authentic learning experiences like this one that require students to solve business problems
by analyzing data with software tools.
Faculty Reaction
One professor’s reaction to the case, upon first seeing it, was:
I have to use this case in both my AIS and my IS courses. I will no longer have to spend time trying (often
unsuccessfully) to get students to understand the differences between Excel and Access and actually have them
appreciate the power and practical way you can use a relational database . . . I like the idea of equipping accountants with the tools to analyze large datasets. Further, I like that the case seeks to show the shortcomings of traditional
spreadsheet models.
The case asks students to analyze loan data to support a recommendation to either raise the minimum cutoff score for loans
extended to customers buying used cars or leave the credit score cutoff unchanged. In our first uses of the case, we observed
students being able to think through the logic of what to compare (loan completion rates of loans with credit scores in a stratum
at the existing lower cutoff with the next higher stratum) but having difficulty instantiating their logic in a spreadsheet model.
The logic required designating the relevant strata of credit scores, calculating the currency of payments for each loan, and
calculating the percent of the down payment for each loan from the down payment and the loan amount. Although the students
had used and developed spreadsheets in several courses before this one, they apparently overestimated their facility with
spreadsheet model building. Students were much more successful in instantiating the logic after we added Requirement 1.a.–
1.g., which prompts them to plan the analysis in a productive way.
64 Borthick, Schneider, and Viscelli
Issues in Accounting Education Volume 32, Number 1, 2017
Many of our students begin the course believing that spreadsheet models are the only analysis tool that they need. Before
we started using this case, many students were skeptical of the need to develop database querying skills. We found that students
were more receptive to developing database querying skill after having experienced the need for it in a learning experience such
as this case. It also helped that the term business data analytics was appearing in the business press.
Student Reaction
Students have had a wide range of reactions to the case. Some were glad they could learn to make sense of a business
situation and develop the spreadsheet and querying expertise to offer insight for this kind of problem. Others were sure that the
case was not accounting because it did not focus on learning to perform standard accounting calculations from a textbook. In
contrast, students who had been on internships before taking the course wished they had taken the course first so that they could
have been more productive on the job with data analysis tasks.
The last resistance to learning to query a database disappeared as students created the VLOOKUPs required to match
category values across the sheets: one VLOOKUP to link category from the acquisition sheet to the sales sheet and one VLOOKUP to link category from the sales sheet to the setup sheet, where all the other attributes are available. This situation prompted a discussion of the appropriateness of a spreadsheet model for all analysis situations. Students agreed that a way was
needed to link the loan data attributes with attributes in the other sheets. At this point, students continued the analysis with a
database query manager that could perform this function with a single command and began to develop database query skill for
this purpose. To develop the necessary database query expertise for analyzing the loan data by dealer, students worked through
Borthick et al.’s (2001) case on assuring compliance for responses to Web site referrals.
Students were relieved that database tables could be joined so easily without the tedious steps involved in making
spreadsheet VLOOKUPS and pivot tables do the categorization. Showing how cumbersome a spreadsheet model could be
compared to the ease of using a different software tool intrigued students by the idea of developing expertise with the other
tool—a database query manager. The idea that spreadsheets and databases could be used together through importing/exporting
data motivated them to enhance their skills with both the spreadsheet and the database manager.
In the first year of case use, few of the students had experience developing pivot tables, which are ideal for showing the
results by cutoff score strata. Notwithstanding their lack of experience with pivot tables, these students seemed eager to learn
how to use them in analyzing the credit score cutoff decision, probably due to having heard about them from internship co-
workers and recruiters. By the third year of case use, we observed very few students admitting to not being familiar with pivot
tables.
Case Use Where Students Developed Spreadsheets/Queries
One of the sections that used the case was a capstone course in a master of accountancy program at a different large public
university. Even though these students had completed an AIS course that included database querying, they all chose to perform
the analysis with a spreadsheet. These graduate students had just as much difficulty with the mathematical modeling as the
undergraduate AIS students exhibited, that is, they needed substantial help with the mathematical modeling required to create
the new attributes to represent credit score strata, loan currency, and proportion paid down. In post-case interviews, the
capstone course students reported positive experiences with four-person groups in constructing computational models for the
business problem and working with other group members to develop the models.
The difficulty with mathematical modeling experienced by students at both undergraduate and graduate levels suggests that
accounting programs should give all students more experience in modeling business situations so that they can contribute to the
analyses that are increasingly becoming the basis for data-driven business decisions. An educational diet of memorizing
accounting procedures does not appear to prepare students for analyzing accounting data in situations for which no standard
accounting computation or schedule applies.
SUMMARY
The contribution of this case is as a learning experience for developing data analytics expertise with both spreadsheet and
database query software and for deciding how to use the two tools together to minimize the overall analysis effort. The case is
appropriate for students who have mastered basic spreadsheet modeling and database querying skills. Students begin the case
by developing analysis objectives from a brief conversation that reveals the business situation and then developing a
spreadsheet model to analyze transactions that represent customers’ loan payment history. When they attempt to integrate
dealer and vehicle category attributes into the analysis, students discover that they need to join data tables, a task for which their
spreadsheet models are unwieldy. If they do not have sufficient database querying skill to join tables of loan history and vehicle
and dealer attributes and continue the analysis, then they pause the analysis long enough to develop the needed querying skill.
Analyzing Data for Decision Making: Integrating Spreadsheet Modeling and Database Querying 65
Issues in Accounting Education Volume 32, Number 1, 2017
This case enables students to experience, in an authentic way, the need for two approaches to data analysis: spreadsheet
modeling and database querying. The use of both approaches gives them the ability to develop criteria for deciding which tool
to use for which analyses, including migrating data between the two tools to take advantage of the distinct capabilities of each.
TEACHING NOTES AND STUDENT VERSION OF THE CASE
Teaching Notes and the Student Version of the Case are available only to non-student-member subscribers to Issues in Accounting Education through the American Accounting Association’s electronic publications system at http://aaapubs.org/. Non-student-member subscribers should use their usernames and passwords for entry into the system where the Teaching Notes
can be reviewed and printed. The ‘‘Student Version of the Case’’ is available as a supplemental file that is posted with the Teaching Notes. Please do not make the Teaching Notes available to students or post them on websites.
If you are a non-student-member of AAA with a subscription to Issues in Accounting Education and have any trouble accessing this material, please contact the AAA headquarters office at info@aaahq.org or (941) 921-7747.
REFERENCES
Barsalou, L. W. 1999. Language comprehension: Archival memory or preparation for situated action? Discourse Processes 28 (1): 61–80. Borthick, A. F. 1996. Helping accountants learn to get the information managers want: The role of the accounting information systems
course. Journal of Information Systems 10 (2): 75–85. Borthick, A. F., D. R. Jones, and R. Kim. 2001. Developing database query proficiency: Assuring compliance for responses to website
referrals. Journal of Information Systems 15 (1): 35–56. Bradford, M., J. A. Samuels, and R. E. Wood. 2008. Beta Auto Dealers: Integrating disparate data to solve management problems. Issues
in Accounting Education 23 (2): 309–326. Chen, H., R. H. L. Chiang, and V. C. Storey. 2012. Business intelligence and analytics: From Big Data to big impact. MIS Quarterly 36
(4): 1165–1188.
Cokins, G. 2013. Driving acceptance and adoption of business analytics. The Journal of Corporate Accounting & Finance 24 (2): 69–74. Davenport, T. H., ed. 2013. Enterprise Analytics: Optimize Performance, Process, and Decisions Through Big Data. Upper Saddle River,
NJ: FT Press.
Dearborn, J. 2015. Data Driven: How Performance Analytics Delivers Extraordinary Sales Results. Hoboken, NJ: Wiley. Elder, E. 1999. Critical Thinking: Basic Theory and Instructional Structures Handbook. Tomales, CA: Foundation for Critical Thinking. Gnatovich, R. 2007. Making a case for business analytics. Strategic Finance (February): 47–51. Griffin, J., and F. Danson. 2012. Analytics and the cloud—The future is here. Financial Executive 28 (9): 97–98. Griffin, R. 2012. Using Big Data to combat enterprise fraud. Financial Executive (December): 45–47. Johnson, J. E. 2012. BIG DATA þ big analytics ¼ big opportunity. Financial Executive (July/August): 50–53. Johnson, K. S. 2015. Outdated: The plain-vanilla accountant. Wall Street Journal (May 19): B7. McAfee, A., and E. Brynjolfsson. 2012. Big Data. Harvard Business Review (October): 59–68. Mims, C. 2015. Data is now the new middle manager. Wall Street Journal (April 20): B1, B2. Monga, V. 2015. The new bookkeeper is a robot. Wall Street Journal (May 5): B1, B7. Nosich, G. M. 2011. Learning to Think Things Through: A Guide to Critical Thinking Across the Curriculum. Upper Saddle River, NJ:
Prentice Hall.
Panko, R. R., and S. Aurigemma. 2010. Revising the Panko-Halverson taxonomy of spreadsheet errors. Decision Support Systems 49 (2): 235–244.
Paul, R. 1990. Critical Thinking: What Every Person Needs to Survive in a Rapidly Changing World. Tomales, CA: Foundation for Critical Thinking.
PricewaterhouseCoopers (PwC). 2015. Data Driven: What Students Need to Succeed in a Rapidly Changing Business World. Available at: http://www.pwc.com/us/en/faculty-resource/assets/pwc-data-driven-paper-feb2015.pdf
Redman, T. C. 2008. Data Driven: Profiting from Your Most Important Business Asset. Boston, MA: Harvard Business Review Press. Zwaan, R. A., and G. A. Radvansky. 1998. Situation models in language comprehension and memory. Psychological Bulletin 123 (2):
162–185.
66 Borthick, Schneider, and Viscelli
Issues in Accounting Education Volume 32, Number 1, 2017