What's Wrong With This Picture?
Even if your company (or just the data you work on) isn’t bound by the
regulations imposed by Sarbanes-Oxley (SOX), you still need to maintain
control. This month, we’re providing more concrete examples of the things
that can go wrong keeping transactional data in a spreadsheet.
We’ll also
look at procedures to prepare Excel data for transfer into Access. Note that
transfer of data into Access would NOT satisfy SOX regulations as Access
does not fully support user-level security. However, the example will
demonstrate the same steps needed to port Excel data into an
enterprise-standard database.
For a
review of the differences between
spreadsheets and databases, see our April 2003 article in the
newsletters section of our website. In the spreadsheet below, we’ve
illustrated several of the data corruption and data duplication errors
common to spreadsheets.
You may wish to download the
full
spreadsheet as several of the mistakes mentioned in this article can not
be seen in the excerpt below.
The first issue we’d like to point out is not-quite-duplication. Notice
the customer name Bald Mt. Acct. On one row the
name is completely spelled out, and a new address is used. What is the
correct way to spell the name and which address is correct? The last two
entries for Archer have two different phone
numbers-again, which is correct? In a relational database, the information
for a company is stored in only one place; there is no possibility of
conflicting data. Although it happens in only one place, note that a similar
problem exists for the list price of Widgets. Near the bottom,
Harpsburg Intrastate paid $29.95 per widget!
That’s ten times what everyone else paid. This mistake is very easy to find
and correct. If the list price were stored in one place, however, the
mistake would never have been made in the first place.
You can see in the full spreadsheet (online)
that someone has overtyped the formula calculating Amount Paid on the 3/7
transaction for Bald Mountain to give an additional discount. Now the
discount and Amount Paid don’t calculate properly. This kind of ‘shortcut’
is very common in spreadsheets but it can lead to nearly unsolvable problems
in reconciliation. Databases store only raw values, not calculations. This
means that accurate values must be entered—no shortcuts!
See the article on scrubbing your data for
the tips on correcting these mistakes.