Should spreadsheets be controlled? If so, how?

[Mark Meer]

I'm certain the answer is "it depends"...

...but I'm curious for examples of what circumstances people control electronic spreadsheets (e.g. MS Excel), and how these controls are exercised.

The main concern is that while spreadsheets are great tools for data analysis, they are (in most cases) being continually updated. This, combined with their electronic format, makes it difficult to subject spreadsheets to the usual controls of review, approval and version-control.

Is there some sort of acceptable "relaxed" controls that can be used for electronic spreadsheets?

For example:

Individual supplier records and purchase order records are maintained as paper forms.
These records are reviews/approved, dated, signed and filed.

But in addition, a spreadsheet is maintained to track the status of all purchases and link the suppliers to orders (allows to quickly determine purchase history).
Is this spreadsheet subject to change-control (given that it may be being updated on a daily basis)?

 

[Steve Prevette]

Re: Should spreadsheets be controlled? If so, how?

I am working for a project, where we do indeed need to "control" the excel files I make for them. Each time the file is changed, it is saved as a new file, with a date stamp in the file name, such as:

Excel_File_06182014.xlsx

Or if multiple files, we put the file in a folder which includes the date stamp in the folder name.

 

[Big Jim]

Re: Should spreadsheets be controlled? If so, how?

This is one of those examples where people will confuse documents and records.

If there is a spreadsheet tool that is often used, as an example, for developing a quote, the spreadsheet template would be controlled as a document. It could be controlled by assigning a form number and listing it on the list of documents or list of forms, depending on how your company does such things. The form number with revision level could be in a header or footer.

When you use the template to create a quote, you don't alter the template (document). Instead, you are creating a record. records don't usually fall into a requirement to revise the form when you make record entries. You get to determine where the record is kept (perhaps in a customer file or in a project file). Unless there is a customer or regulatory requirement otherwise, you get to determine how long you need to keep the record (quote).

So it should be easy to keep track of spreadsheet templates you use in this manner. The concept of needing to update a document (form in this case) just because you have recorded something on it is just not required and is overkill.

 

[Mark Meer]

Re: Should spreadsheets be controlled? If so, how?

Steve:
Is there some means of recording approval? In addition to a timestamp, is there any traceability to the author or the changes?

Big Jim:
The complication is the continual update of the spreadsheet, and the maintenance of data history.

For example, if I maintain a spreadsheet that lists the statuses of all purchase orders. There may be a "Status" column that might read, for example, "Backordered", "Received", "Cancelled"...

In a given day, I might change the status of several line items to "Received" for all the shipments received that day. In terms of standard record control, this would be considered bad practice as I'm essentially erasing all traces (overwriting) the previous statuses.

In this case, there is no document template, and (Steve's system notwithstanding), no data integrity/history maintained. It's just a continually updated log.

 

[Marc]

Re: Should spreadsheets be controlled? If so, how?

This is one of those examples where people will confuse documents and records. <snip>

And templates/forms.

 

[insect warfare]

Re: Should spreadsheets be controlled? If so, how?

I usually see spreadsheets (like Excel) controlled as records, since they can have the potential to be continuously updated. While they can be uploaded into your documented management system as a "controlled" document, this is not always value-added. I have spreadsheets that I have been updating for years, because a need was determined to gather long-term data for trend analysis, and these spreadsheets were not under "document control" as you would think. Everything technically was "controlled", just at different levels, without the introduction of overkill.

Typical methods of spreadsheet control I've used before are:
1) Saving my work every time it is updated
2) Maintaining the electronic file on my local machine
3) Performing weekly backups to a specified location
4) Purging or deleting the old file in the event a better version of it is designed

Depending on where you work, more stringent controls may be necessary (not every industry is judged by the same standards). Nevertheless, your document control procedure can state exactly which levels of control your company deems are appropriate. Hopefully this helps spark your light bulb....

Brian

 

[outdoorsNW]

Re: Should spreadsheets be controlled? If so, how?

It sound like Mark needs a database and not a spreadsheet.

While a database is likely more expensive to set up, a database can save prior entries, time stamp changes, log who made the changes, etc. A database can restrict who can do what, reducing errors in the information.

When you need to analyze trends, you run a report and analyze the data in Excel, Minitab, etc. Most off the shelf databases have built in reports and instant calculations (purchase order total, for instance) for common functions.

Otherwise you are creating a new version several times per day, and how does someone know which version to look up?

How much more expensive initially depends on if you can buy it off the shelf, how much customization is needed, how much your IT people can do, and how much training is needed. Many specialty computer programs (for accounting, inventory management, etc) are special purpose databases.

In the long term, a database will likely save time/money.

Mark mentioned purchasing functions. I believe Quickbooks (look at the more expensive versions) and several other brands (whose names I can't remember) have software that may meet your needs for not much money.

You may want to talk to sellers of more expensive programs in case a more expensive package automating more functions is more cost effective in the long run.

Take your time evaulating software. Sales people often gloss over the areas where their software does not really meet your needs. The one downside of the lower cost programs is that customization is normally more limited. If you do a websearch, I reccomend looking at (not necessarily clicking on) at least the top 50 entries, and trying several sets of search terms.

Unless your needs are very specialized, developing your own database is probably more trouble than buying a package. You need to think about support, security, etc.

Tom

 

[Mark Meer]

I guess the concern is more generally regarding electronic logs (ongoing, perpetual, continually updated), for which spreadsheets are particularly convenient.

If these are considered records then, at least according to FDA regulations, there needs to be controls to ensure that "record changes shall not obscure previously recorded information." (see FDA 21 CFR Part 11.10(e)).

I guess the only way to do this is to "save as" a new file everytime the log is updated (e.g. see post #2 by Steve Prevette), and maintain a gigantic archive of files.
...good thing file-sizes are typically small and space cheap!

Edit: Or setup a database system like Tom suggests above...

 

[Raffy]

Hi
In my previous company I work with, what we do is that we document the format and how it will be saved in our hard disk.
Example:
Excel123_0614001.xls
Where:
Excel123 is the file name
06 is the current month
14 is the current year
001 is the series number
Hope this helps.
Best regards,
Raffy