Transcript Fees and Financial Aid

Spreadsheet errors
Natalie Melamed
Designing financial models that work
Spring 2010
Spreadsheet errors exist in all spreadsheets,
consistent with other human cognitive domains
An audit in a mining
company found errors in
about 30% of the
audited
spreadsheets****
20% to 40% of all
spreadsheets
contain errors*
90% of all spreadsheets
with more than 150 rows
that a consulting
company audited
contained errors**
A Price-Waterhouse
consultant audited 4 large
spreadsheets and found
128 errors***
Humans make mistakes, hence most spreadsheets
are bound to contain errors
Source> *Freeman, 1996; **Panko, 2005b; ***Ditlea, 1987; ****Dent , 1995)
A spreadsheet error can cost someone career
or even freedom
“While modeling possible
profitability scenarios for the client,
one input error could sway our final
client recommendation”
Management Consultant
“ I made an error in cell reference when
doing DCF analysis, which would have
resulted in an incorrect recommended
acquisition price for our client! Good
thing, my mistake was caught by my
boss!”
Investment Banker
“Incorrectly calculating stock options
expense could materially affect the
calculated profitability of my employer
company, which would result in
providing false financial reporting to
the SEC”
Sr. Accounting Manager
While it is difficult, it is very important to minimize
spreadsheet errors
Source> classmate interviews and personal experience
There are numerous types or errors that
require different error prevention techniques
Type
Qualitative errors
Personal example
Descri
p-tion
Mechanical errors
Logic errors
Quantitative errors
Omission errors
Interpretation errors
 Data entry typos
 Wrong cell reference
 Mistakes in reasoning
 Excluding calculation
or data
 Errors interpreting
spreadsheet output
 The stock option expense
calculation spreadsheet
system required a lot of
manual data entry and
data referencing to
spreadsheets in multiple
workbooks, which resulted
in many broken links on
top of simple data entry
errors
 In the spreadsheet
forecasting gas station
operating expenses in
2020, I applied a quarterly
electricity cost increase to
an annual forecast model
 In the stock option
expense calculation
spreadsheet system, I
forgot to include a
modification of a large
stock option grant to VP
Engineering, which
resulted in understatement
of expense for the quarter
of $170,000
 My subordinate misread
the output of my stock
options expense calculation
model and booked an
annual stock options
expense into the general
ledger instead of booking
only the amount for rourth
quarter
Different controls are required to battle different
types or errors
Source> http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm Journal of End User Computing; Special issue on
Scaling Up End User Development Volume 10, No 2. Spring 1998, pp. 15-21 Revised May 2008; personal expirience
To prevent spreadsheet errors, we need to first
understand what is causing them
Sense of urgency
Spreadsheets are frequently
created under a time pressure
Lack of organized approach
to spreadsheet creation
Microsoft Excel
Excel User Error!
Overconfidence of the
spreadsheets creators on the
extent of their competency
Lack of review
Knowing that no one will
ever look at the model to
review it
Educating spreadsheet creators on main causes of
errors can be error-prevention already
Source> Natalie’s analysis
The best way to minimize spreadsheet errors is
to implement front- and back-end controls
Detection controls
Preventative controls
Prevent errors while the spreadsheet
is created
•Design spreadsheet
•Walk someone through the
spreadsheet creation process
•Document the spreadsheet map
•Use cross-footing
Error
minimization
Detect errors before spreadsheet
results are used, but after the
spreadsheet is completed
• Compare spreadsheet output
with ballpark estimate
• Audit cells for formula
consistency
Employing error prevention and
detection controls simultaneously can
decrease prevalence of errors in the
spreadsheet
Source: Natalie’s analysis