Exit Excel Hell - Part 2

In my previous 'Exit Excel Hell - Part 1' blog I started talking about how sending all those Excel attachments is a misuse of the email system. Other problems also exist. When you ask staff to update a spreadsheet and email it back to you, how do you incorporate multiple staff input changes? Doing that manually just creates an opening for errors. You might be surprised at the number of copies of a given spreadsheet in an organization. Which is the most recent and correct version? And, what happens to a spreadsheet that is used annually and is dusted off each year: does the creator and/or user relearn the contents and how it is used? Old spreadsheets are breeding grounds for errors because people will automatically assume it was correct. Or for that matter, if a user builds onto a spreadsheet that someone else developed, they often have to reverse engineer it to truly understand it. Furthermore, as Excel users become more sophisticated, their spreadsheets get more complicated and they start using external links and hidden references creating even more risk.

 

Well, how can this proliferation of spreadsheets be controlled and what other controls can be used for spreadsheets?

My first preference is, obviously, not to use a spreadsheet. But if you must, and if it is to be shared by multiple staff, it should be in a repository (or an Electronic Document Management System) where only one person can check the spreadsheet in or out to modify it at a given time.

Another control feature you can use is to put a password on a spreadsheet or part of a spreadsheet so someone can change or access only certain cells. In addition, important and complex spreadsheets should be documented. How would you document a spreadsheet?

There are several ways. The first is to use named cells wherever practical. For example, when calculating revenue, the most popular way is to multiply two cells — for example, F12=N12*P12. But why not define columns N & P and name them Cost and Quantity so column F becomes Revenue=Cost*Quantity. The formula itself becomes more descriptive and understandable to anyone looking at the spreadsheet. You can also insert comments in the cells to help explain the reason for your calculation or process. You can embed instructions right into the cells themselves, or create an external documentation manual, or even create a spreadsheet tab call 'Instructions'. You could maintain a revision history in which each person who makes a change to the spreadsheet logs what they did and when.

Some companies, when they have important spreadsheets, will follow a structured methodology similar to developing software. Some call it the Software Development Life Cycle (SDLC) which includes user requirements, design, development, testing, training, and so on. But unfortunately not many Excel users have formal SDLC training and they usually develop a spreadsheet in more of an ad-hoc fashion.

When you have an important memo or communication to send to a lot of people do you have someone else look at it before it is sent out? I do. I want someone else to give it a once-over. I want to make sure the message I’m trying to communicate is free of grammar and spelling errors, but also I want see how someone else interprets my message.

That same concept is exactly what should happen with Excel spreadsheets. Someone, other than the creator, should be auditing and verifying the spreadsheet. It’s too easy for those of us who create the spreadsheet to become so close to give it a truly objective look. You’re unlikely to find all your own mistakes. This is even more important for monthly, quarterly or year-end financial statements which are so often rushed.

Ideally, if it is a financial report, have the report come directly from the financial system. With the flexibility and options in report writing these days, you can even get a nicely formatted report. If the report, which can be run anytime, comes from a single official corporate source, there is far less chance of error than re-entering the summary data into some spreadsheet report.  If you must use Excel for financial reporting or some other corporate reporting function, consider having your IT department develop scripts to automatically query the corporate database and deposit the data in the spreadsheet (Excel can do SQL queries to a database). This will at least avoid transcription data entry errors. 

Spreadsheets provide a great analyzing tool. Personally I think spreadsheets are great for individuals to do their job, but I try to categorize spreadsheets two ways. It’s either for personal job working purposes or for corporate use (i.e. many staff use it). By that I mean when that person leaves the company, their files, and I refer to Excel files in this instance, should be thrown out, deleted along with all the other files in his/her data directory. If it is a corporate Excel file that should be kept, it should be documented and kept in a location other than the employee’s data directory.

Any file that is used by more than one staff member should be in a department or corporate directory or an Enterprise Document Management System (EDMS) or an Enterprise Content Management System. When a person leaves, the supervisor or manager can quickly look at files in that employee’s data directory to see if any might be useful to the next incumbent in that role, but for the most part, good file management procedures should include deleting the personal directory of staff members when they leave the organization.

Enterprise Content Management System, or ECM as it is commonly referred to, is a formal way of storing and organizing corporate documents or other content in a central repository. I will tackle ECM in another blog.

Exit Excel Hell - Part 1

Did you know, and this is based on studies by PWC, KPMG and others, that over 90 per cent of spreadsheets have errors? The larger the spreadsheet, the more errors found.

Many organizations I’ve worked for in the past relied on Excel too much. Recognizing the problems with Excel, I tried to promote a strategy that I called ‘Exit Excel Hell’.

Too many senior managers rely on the dubious quality of data in a spreadsheet.Taking this to the extreme, look at what happened to Worldcom or Nortel. At some of these companies, senior managers manipulated or unknowingly introduced errors into the spreadsheet financial reporting. Those looking to misrepresent the business can easily manipulate spreadsheets.

Think about it, anyone can change a cell, manual errors happen so easily, even accidentally keying in the wrong number e.g. 200 instead of 100. How do you know if you have keyed in the wrong number? If you're lucky, you visually catch it. Other things like simply cutting and pasting the wrong cells can easily happen. Even formula errors are so easy to make —  a row was omitted in the sum for a financial report is easy to do. Sometimes when adding rows, Excel will adjust the formula, but not in all cases. It’s so easy to make a mistake. Even knowing the pitfalls doesn’t prevent mistakes; many times people have found errors in my spreadsheets I’ve used in making business cases.

There are some simple controls you can do such as build in control totals. For example, if you had a spreadsheet that reported staff absences and the number this month was ten times what it was last month that might be a warning flag to check the numbers. The spreadsheet designer should devise tests to verify the results generated. Another control is to lockdown cells. By lockdown, I mean set a cell so it can’t be changed by a user (also known as protecting a cell). This applies to cells where data is not being changed, such as a formula cell. More sophisticated spreadsheets may actually extract data from a corporate database, such as the financial system. Getting the data directly from the source provides a better control than someone manually re-entering the data and you are more assured of the data integrity.

Since each spreadsheet is largely manual, reconciling numbers and the source can involve, and waste, a lot of company resources. That’s why I like to promote using a single source for certain data. For example, revenue recognition should be in the financial system, not in someone’s spreadsheet or spreadsheets. All departments should agree on the process entering data for sales and rules need to be clear. If Sales uses one system and Finance uses another, you are caught with two different sets of numbers — which is correct? Who do you believe?”

Sometimes staff try to create their own systems using Excel rather than relying on a corporate application system. As an example, suppose rather than using the purchasing module within the financial application, the purchasing department used a system of Excel spreadsheets to do their work. This creates a disparate system, often without appropriate controls, that does not tie into the existing systems. These Excel systems were never designed to be enterprise-level applications.

Perhaps the most popular Excel system is for budgeting. Many companies claim their budget process is unique so they have to use Excel because it is so flexible and accommodating. Ideally though, it would be much better to use either the budgeting solution in the finance system or have a system that is specifically designed for budgeting. The main problem that occurs with budgeting is that you often have many spreadsheets, hundreds or thousands for some companies, that your managers use to fill out their budget, with finance probably going through hoops trying to link or consolidate all the budget spreadsheets. In addition, in both the purchasing and budgeting spreadsheet examples, you end up creating a duplication of data that must be re-entered into the financial system at some point. In a corporate application system, you often have data entry validation. For example, in Purchasing you might enter a supplier number. The computer would look up the supplier name and address at the time of entry. Or, when entering the GL account number the application would validate the GL account number and it could look up to see if funds were available in that account for this purchase. Staff using Excel often do not build in data validation for cells that require data entry.

You know, Excel spreadsheets can proliferate like rabbits, and once that happens, version control becomes a real problem. As people create, modify and share spreadsheets, how do you keep control of the latest version? Is the latest version on a shared drive, on someone’s laptop, in someone’s email, where? During a budget process, sending hundreds of emails with budget attachments can choke your email system.

More in my next blog - 'Exit Excel Hell - Part 2'.
 Dilbert Copyright Scott Adams