EXCELlent Ones Consulting Skip to Content Skip to Search

Print | | Download PDF

4 Reasons your Excel reports may be wrong

 

The business landscape is changing and it’s changing fast.  Ultimately, senior leadership relies upon timely reports from staff to make the critical decisions needed for growth and survival. The reports whether tabular or graphical in nature are the basis for many of these business decisions.   Missteps in the compilation or representation of these reports can lead to shrinking revenue, lost market share or worst outright annihilation in this competitive business landscape.  It behooves any organization leaning on Microsoft Excel to take note of the following points detailing common root causes of erroneous reports and how to error-proof its processes.

The compromised integrity of reports has a few root causes, such as: lost in translation, exclusion of stakeholders, data format and data entry. 

 

Lost In Translation

Many times reports are incorrect due to improper interpretation.  In every report you are encoding information and delivering a message.  In some cases interpretation varies depending on the individual.   For example, if a law firm tracks the types of cases it handles on a monthly basis.  There are a number of ways we can measure the performance of the firm.  If we want to measure volume by month it’s important to note it in the axis whether it is by dollars or quantity.  Clarify the unit of measurement in the axis is a simple but sometimes overlooked practice.  In a situation like this the number of cases may be a respectable number but what is our frame of reference?  It is ideal to form a trend line to see the path of cases or revenue across the years.  Is the organization increasing or decreasing?  Proper chart or graph choice is very important.  Depending on the graph chosen additional information can unintentionally be hidden or misrepresented.  Going back to our law firm example, perhaps the majority of cases or revenue is driven by certain types of cases; injury, business formation etc.  Selecting the right chart can bring this information to light in a clear succinct manner.

The best course of action to take with issues of translation is to document.  Clear up any ambiguities not captured by the graph in a section called notes below the graph, chart or table.   If the item includes complex calculations, add a legend detailing that.  Pull the curtain back and be forthcoming with information, it only leads to a better end product.  In addition always credit the source of data.  The purpose for this is twofold.  The first reason is that you want to properly attribute another individual or department’s work.  It’s never a good idea to pass on someone else’s work as your own.  The second reason is to properly direct the source of scorn in case any errors arise in the data.  This point may be somewhat self-serving, but it’s important to take responsibility for your own mistake(s), not that of others. In some instances the source of data within an organization determines the level of significance or reliability.  I was given the task of reviewing the operations of a certain department and this required the gathering of some data regarding costs and various revenue streams.  Initially, my focus was to gather the data directly from the department until it came to light that the data may be compromised by the department.  The review was one of performance and these situations typically create an conflict of interest environment.   Data manipulation or forgery can occur when conflicts of interest arise.  These types of situations arise often in the workplace, be keenly aware of them.  For example, asking the admission department for student trends for the past few years may lead to overestimates if taken from the department.   The office may supply the requestor with number of student inquiries rather than actual admitted students to gross up the perception of productivity or value.   Making the data source and underlying details of the report transparent helps in the proper digestion.

The best practice is to leave nothing to the notion that it “seems clear.”  Documenting puts the onus on the user of the report rather than the generator.   Add the run date for any report that’s created to bring the time period reviewed to light.

 

Exclusion of Key Stakeholders

 

A stakeholder is defined as a person who affects or can be affected by certain actions.   In the business world the implementation of any new process requires the involvement of key stakeholders.  The same holds true for reports.  Often this step is overlooked when the report designer assumes that he\she “knows” what the stakeholder\recipient wants.  However, executing things in this manner can lead to bad reports.  One way to combat that is to bring the senior leadership to the table.  Explore potential report types, underlying assumptions, constraints, limitations and the like.   Walking through this crucial step saves time and rework later. Make sure the reporting question being asked by the senior leadership is in fact being answered by the generated report.  Many times this is where the breakdown in the value and purpose of report occurs.

 

Another critical point to mention is test, test, and test.  I was once told that computers don’t make mistakes.  I affirmed this statement with the caveat “but people do and people built computers”.

 

Data Entry

A major reason for the errors represented in reports deals with data entry.  As the saying goes “Garbage In Garbage Out” a pun derived from the accounting inventory method called “First In First Out”.  Many organizations deal with some level of manual data entry.   For example, If you have a clinical practice the patients being observed on a daily basis is recorded by the receptionist.  If you use Excel as the interface insert a few simple tools to control consistent input:

·       Misspelled text or invalid numbers: Use data validation, sometimes referred to as a “drop box” or “toggle” to restrict the type of inputs. 

 

·      Omissions: A big problem in many organizations is incomplete data.  Build in mechanisms into your spreadsheet to alert the user that data entry has not been completed.  This can be done programmatically using Visual Basic Application (VBA) or with a combination of simple formula and conditional formatting.  A VBA program could be run to check certain areas of the spreadsheet prior to the closure of the workbook.  When omissions are noticed, a prompt would appear telling the user to continue input.  Another approach mentioned by a colleague would be to have the page change certain characters white if all the information is not entered.

For date exclusions consider adding formulas that pull this in.  Formulas such as “Today()” to add the date and “Now()” for date and time.

Mechanize your data entry process as much as possible.  For example the internet houses exponential quantities of data; a VBA script can be created to periodically retrieve data from web sites of your choosing.  That method minimizes the errors associated with manual data entry and increases productivity.   Some may think that extracting data from another system and importing into Excel is error proof.  This is a false claim; glitches are apparent at times and the data must be inspected for accuracy.  A general manual review may suffice at times, but for extensive data sets a VBA script can be created to alert the user to any errors. 

 

Data Format

The final area leading to erroneous reports is “data format”.  What are the characteristics of the data?  When determining these characteristics we are referring to several things: types of characters (alpha and\or numeric), granularity (transactional vs. summative), cleanliness (consistent entry no garbage), normalization (reducing redundant components) .  The answers to these areas and countless others greatly impact the type, quality, integrity and manipulation method of the report generated.  In addition the data characteristics predicate the ability to generate a report to answer a specific business question.  For example, if a grocery store only records total sales by month an in-depth analysis cannot be done.  It’s important to be as granular as possible with data.  Recording daily transactions is usually suffices. However in an environment like the stock market minute by minute recording is needed.  Proper data format in order to devise trends, correlation analysis etc.

To echo a previous statement, document heavily.  In the technical world documentation is seen as a pesky task to be rushed through or brushed under the rug.   However, this is perhaps one of the most critical steps in creating any ad-hoc report in Excel or otherwise.  Document the characteristics of the data, note any anomalies, periodic omissions and issues in transferring data from one system to another.  Do not leave the knowledge of the data to those savants in your staff, but capture that institutional knowledge and pass it on. Also, correct errors whenever possible.  For more information on formatting an Excel report for easy digestion read the white paper “17 Spreadsheet Mistakes” available in the free resources section at www.excellentones.com.

 

Excel reports can be littered with errors based on a variety of reasons: be it translation issues, exclusion of key stakeholders, data entry or data format.  The implications of those errors can be substantial.  Take the necessary precautions to ensure that each report represents the most concrete material with which to make business decisions.

 

 

Footer

Email: info@excellentones.com | P.O. Box 160493 | Brooklyn, NY 11216 | Phone: (347) 763-1035 | Fax: (718) 228-7859

All rights reserved. Copyright 2014 Excellent Ones Consulting LLC.