Category: ActiveData News Blog
Can you believe that it has been 100 years since US Supreme Court judge Louis Brandeis made his famous statement on transparency in business and government? Writing for Harper’s Weekly in December 1913, Brandeis asserted that “sunlight is said to be the best of disinfectants”.
As the workplace becomes more virtual, geographically dispersed, and decisions are more decentralized, often technology helps us bridge the gaps. Chat, video conferencing, simple status or file-sharing tools are on the rise in all types of businesses. Transparent work, open communication, collaboration across processes – not just good for employee morale but increasingly essential for fraud prevention.
There’s been a significant interest in adopting technology to support better collaboration, information-sharing and person-to-person communication over the last decade. Originally conceived as “Enterprise 2.0”, we now hear terms like “social business” or “enterprise collaboration”. What this often means is using more online collaboration tools to facilitate more communication, more sharing, more engagement with colleagues as people – not just as faceless boxes in the org chart.
But it is not just all warm and fuzzy kumbaya. Organizations that value team collaboration, shared responsibilities, knowledge/information transfer, and transparency into the decision-making process face measurably reduced risk of fraud. Let’s take a look at some of the key findings from the 2012 “Report to the Nations on Occupational Fraud and Abuse”, published by the Association for Certified Fraud Examiners.
- Mandatory vacation or job-rotation policies are in place as fraud prevention in only 16.7% of organizations (no real change from 2010 survey). This is particularly problematic in small companies (less than 100 employees). Only 8% of companies with less than 100 employees have mandatory holiday policies, compared to over 20% for bigger companies.
- Mandatory vacation/job-rotation policies, when in place, helped reduce the amount of fraud by one-third – a median loss of $100K rather than $150K in firms without such a policy.
- Frauds were detected twice as quickly in organizations that adopted job rotation/mandatory vacation policies, whistle-blower reward programs and surprise audits.
- Organizations took 9 months to detect fraud when job-rotation/mandatory vacation policies were in affect – compared to 24 months in firms who did not.
No individual should ever be allowed to become a single point of failure. Cross-training and collaborating with colleagues up and down the org chart ensures that no one feels obligated to skip vacation, off-site training or professional development events. Employees who resist time away from their job may be at higher risk of attempting to hide fraudulent activities or careless work habits. Multiple sets of eyes on figures, procedures and communication help find problems or discrepancies much earlier in the cycle. Again from the ACFE Report:
- Over 18% of fraud committers were individuals who demonstrated control issues in the workplace, and refused to share duties.
- This number jumps to 24% for individuals in management or executive positions
The era of the lone wolf control freak may be coming to a close as businesses are compelled to go digital and mobile, become more distributed with remote working, and seek to attract the tech-savvy, collaboratively-minded next generation workforce. This era may also be coming to an end, simply because it is bad for business and the company bottom line when this behavior acts as a cover for internal fraud.
Better collaboration, more eyes on work processes, more open access to non-confidential information: all ways to reduce the darkness that breeds corruption and fraud in the workplace.
To learn more about fraud prevention and International Fraud Awareness Week (November 11-17), follow the #FraudWeek hashtag on Twitter, and explore the resources provided by the ACFE at www.fraudweek.com
With 99.7% of organizations using spreadsheets for business processes or financial reporting – 70% of them “heavy” users – managing this tabular information needs to be an important element in a fraud prevention strategy. InformationActive will be exploring several aspects of fraud prevention as part of the ACFE’s annual “International Fraud Awareness Week” from November 11-17.
This post will highlight some of the content and data management practices that can help reduce the risk of fraud. Ensure that spreadsheets are addressed as part of your organization’s information management strategy.
A very useful process checklist was provided by Deloitte in its 2009 study “Spreadsheet Management: Not What You Figured”:
Let’s step through these stages and see where content management and data analytics can help with spreadsheet governance.
Identify, inventory, and organize spreadsheet content
Document management systems (including Microsoft SharePoint, IBM, EMC, OpenText, Alfresco and others) allow IT, compliance and audit managers to control and track how spreadsheets are used, revised, and shared. Key capabilities of these tools include:
- Version control: know who created changes to the spreadsheet file and allow old versions to be preserved for audit or recovery purposes.
- Check-in/check-out: allow changes to be made safely by locking or reserving a spreadsheet so that no one else can override changes in error. This is particularly valuable if the revisions or data updates are being done on a local PC or mobile laptop while on the road or at a client site.
- Metadata: is “data about data”, or descriptive properties that provide context to a spreadsheet. This may include the name of the author or current custodian, a meaningful title, status indicators (draft, final, approved, etc.), tags such as customer name, project code, type of spreadsheet (operational vs. financial, for example).
- Search: allows IT, legal, auditors or business users quickly and accurately locate spreadsheets based on date, subject, keyword or any of the metadata associated with the file. Search is important to the discovery process in the event of audits, threat of litigation or freedom of information requests in public sector.
- Access Controls: allow very granular security settings to be applied to a spreadsheet – perhaps letting an entire department view the file, but reserving edit permissions to only a select team of experienced users.
- Lifecycle Management: records management features are often included or available as optional modules to document management systems, helping ensure the entire lifespan of an electronic spreadsheet is handled. Retention rules may specify that obsolete content be deleted or destroyed after a period of time, in line with laws, industry regulations or corporate policies. Automated identification of this expired content can help reduce legal risk, relieve storage costs and improve accuracy of search requests.
Identify the risk level, magnitude and complexity of the spreadsheets and set baselines
Create categories to identify spreadsheets based on their risk types and complexity levels. These categories may be used as descriptive properties (metadata) if a document management system has been adopted. Organizations should look at:
- Complexity levels: design a scale that ranges from rudimentary (meaning no significant calculations are used to transform data) to advanced (a high degree of advanced functionality such as pivot tables, macros or links to other spreadsheets or electronic documents). Mark the spreadsheets accordingly with notes in the file properties or metadata in a document management system.
- Analytical tools: data analytic technologies – such as ActiveData for Excel – have been designed specifically for spreadsheet use. These tools can be a valuable resource when assessing spreadsheets to identify their complexity levels. Testing the data integrity, formulae, and using tested analytics rather than home-grown macros can significantly improve the quality and reliability of the worksheets.
- Magnitude: identify whether the spreadsheet contains data that is immaterial, material or critical based on the operational quantity or dollar value of the data being collected and calculated in the spreadsheet. Once again, tag or categorize the spreadsheet accordingly and analyze its integrity on a prioritized basis.
- Baselines: isolate a point in time where the spreadsheet is confirmed to be working correctly and in line with management needs. Validate the fields containing the inputted or imported data; compare it to the source data, ensuring that the information transfers correctly. Verify that the formulae are working correctly and understand the purpose of the calculations. Data analytic tools, like ActiveData for Excel, can assist with this task.
Set policies, procedures and controls for spreadsheet use
Once the inventory, assessments and baselines have been established, organizations should ensure that spreadsheet integrity is protected and monitored. Spreadsheet audits must also include a review and evaluation of policies and procedures, and document recommendations for improvements. Adoption of document management and data analytic technologies can provide organizations with enhanced capabilities to establish, maintain and audit access controls, version controls, improve search and retrieval and ultimately ensure obsolete spreadsheets can be safely and securely disposed of in accordance with approved retention schedules.
Want to learn more?
Check out the extensive data analytics and spreadsheet management tools offered in the ActiveData for Excel software package. Short, informative demo videos available here: http://www.informationactive.com/video
Download the full Deloitte research.
Find more tips on fraud prevention by visiting the ACFE site: www.fraudweek.com
According to the Association of Certified Fraud Examiners (ACFE), a typical organization loses 5% of its revenue to fraud each year. In its 2012 survey, “Report to the Nations on Occupational Fraud and Abuse” 1,388 incidents were reported by fraud examiners and auditors across 100 countries, revealing a compelling snapshot of the damage that fraud can do to business reputation and financial stability. Key findings include:
- Median loss of an occupational fraud case was $140,000
- More than 20% of fraud incidents were over $1 million in losses
- Experienced, long-time employees were the root of the most costly fraud incidents, with a median loss of $229,000. Brand new employees had less costly impact on revenue, with median loss of $25,000.
- Six departments were the source of the majority of fraud – 77%. These are accounting, operations, sales, executive/upper management, customer service and purchasing.
During International Fraud Awareness Week, the InformationActive team will focus on some of the more interesting aspects of fraud, fraud prevention, results of the ACFE survey, and explore new ways that internal auditors, business managers and accounting professionals can become more vigilant against deceptive practices in today’s increasingly digital workplace.
Learn what other organizations are doing to combat fraud by following the #fraudweek hashtag on Twitter, subscribing to the ACFE blog, and listening in to the ACFE podcast channels for interviews and helpful recommendations.
Gartner Inc. projects that “big data” will drive 232 billion in IT spending over the next 4 years. A TechCrunch article on this new research says that storage management, databases, application middleware and infrastructure technologies will drive the biggest chunk of this anticipated investment. On the short end of the stick? Analytics.
Yet again, as new technologies offer new ways of improving our businesses, we continue to repeat the mistakes of the past. We invest in storage, middleware, disk space but fail to deliver meaningful insights into the data in ways that average business managers can understand. Turning on the spigot of “big data” by harvesting social media information, tapping into open data sources or by getting better connections into in-house line of business applications, will do little to help enterprises when fundamental business decisions are still made in large, complex, formula-rich spreadsheets.
Big data gets all the hype today, but enterprises around the globe continue to be run by big spreadsheets – spreadsheets with few controls, analytics or simple ways of testing, validating or detecting patterns.
Spreadsheets are perhaps the original “user-generated content” to have had a material impact on business operations. Spreadsheet data is “enormous in size and impact”, according to research by R. Panko and D. Port presented in 2012. End-user computing applications, such as MS Excel, have had tremendous effects on organizational decision-making, yet it “seems to be invisible to the central corporate IT group, general corporate management, and information systems (IS) researchers”, according to Panko and Port.
In a 2009 survey, Deloitte found a miniscule 0.3% of organizations did not use spreadsheets. Over 70% of respondents described themselves as having “heavy” reliance on spreadsheet use to support critical portions of the business. Academic research at Carnegie Mellon University used US Department of Labor statistics to project that by 2012, there would be 90 million computer users in the American workplace. 60% – or 55 million – of these people would use spreadsheets or simple databases as part of their day-to-day work. European research reveals that financial analysts average three hours a day in Microsoft Excel, with spreadsheets having an average five year life span.
Despite this pervasive, universal reliance on spreadsheets, testing for errors, consistency of use, and QA of internally created custom macros are almost non-existent activities. Professor Ray Panko estimated that in a given spreadsheet, 2-5% of formulas are in fact wrong – regardless of the experience level of the spreadsheet user. Up to 5% of these errors can in fact be “material” to business operations. Incorrect forecasting, cost management, or revenue projections can cost a business millions of dollars, and even help disguise internal fraud.
So what can be done?
First, understand where critical business data is stored. Odds are good it will be in a spreadsheet. Understand how this information is being crunched, sorted and calculated.
Second, do not skimp on analytics that can be understood and used by a typical business worker. There’s 90 million of them in the American workforce and the majority of them are using spreadsheets in some way. Find inexpensive, intuitive tools that won’t require major corporate IT support.
Third. Don’t pin hopes of improved business productivity and operational insights on to “big data” when the foundational analysis of “big spreadsheet” hasn’t even begun. Take a look at the dark data stored in departmental spreadsheets, off the CIO’s radar, to see what quick wins can be made with the untapped data sources already in-house.
Cheryl McKinnon is the President of Candy Strategies Inc., and provides marketing advisory services to InformationActive Inc.
Recommendations for further reading:
Carnegie Mellon University. Christopher Scaffidi , Mary Shaw , Brad Myers. “The ‘55m End-User Programmers’ Estimate Revisited”. 2005.
DecisionLine. R. Panko. “Facing the Problem of Spreadsheet Errors”, Volume 37, #5. 2006.
Delft University of Technology. Felienne Hermans, Martin Pinzger, Arie van Deursen. “Supporting Professional Spreadsheet Users by Generating Leveled Dataflow Diagrams”. 2010.
Deloitte. “Spreadsheet Management: Not what you Figured”, 2009.
It was a busy summer for the ActiveData team, working on new features, translations and getting ready for a few upcoming events.
By John West – Founder of InformationActive Inc.
I hope you get a chance to come out and see us at the ACFE conference in Ottawa at the end of this month (Oct 28-31). We’ll be at the Canadian Government Technology show (also in Ottawa) the following week. We’ve just finished a French version of the software and hope to generate some interest in ActiveData from a few Canadian federal government departments. Next year we’re planning on doing the ACFE conference in Vegas and the IIA conference in Winnipeg.
This is going to be a busy fall for us. We’re releasing a new version of ActiveData (and a French version), Windows 8 is due out at the end of October and Excel / Office 2013 will ship later on in the year. Microsoft has also released a new version of Visual Studio and Dot Net, the tools we use to develop ActiveData so that’s 3 major software platform shifts happening at once. After testing with pre-release versions of Win8 and Excel 2013, I’m happy to report that ActiveData works with both of them.
We’re revamping our marketing materials and would love to hear from you about the successes you’ve had using our software. For example, do you use it with ACL or IDEA? Has its introduction into your mix of software tools increased the overall use of data analytics in your organization or practice? Do you find yourself using it for general Excel work or other tasks you didn’t envision when you purchased the software? Have you found ways to save yourself significant amounts of time on a regular basis like my sister the geologist? She uses ActiveData to merge dozens of assay worksheets that come in from the field every Friday. What used to take 6-8 hours every week and involve the occasional mistake now takes her less than an hour using ActiveData’s Merge and Compare Worksheets functions. I’m tempted to ask her to pay for a license. Anyway, if you’d like to share some of your success stories with us then please contact us at firstname.lastname@example.org
BETA VERSION OF ACTIVEDATA 5.0.504
The beta version of ActiveData 5.0.504 is available for testing. There are lots of new features in this free update including the ability to convert worksheets formatted as reports into tabular data. You can read about the new features here: http://informationactive.com/beta
INTRODUCING A NEW BUSINESS EDITION FOR ACTIVEDATA
We’ve just introduced ActiveData For Excel – Business Edition, a lower cost version (currently $129) of the software, containing a subset of the ActiveData feature set. We’ve seen growing interest from the general business community who need the powerful spreadsheet analytic and management features we offer, but not the full range of audit-specific tools. We’ve now renamed the existing product to ActiveData For Excel – Professional Edition.
A full comparison of the ActiveData professional and business editions is posted on our website here.
Events, Training and Activities
WE’RE INTERESTED IN YOUR FEEDBACK – NEW VIRTUAL USER GROUP SURVEY
We’d like to know if you’d be interested in participating in user group meetings, either in person or virtually. If you are interested, please provide feedback via the survey posted here: http://tinyurl.com/chnppkf
CONFERENCES AND TRADE SHOWS
We’ll be sponsoring and exhibiting at two upcoming conferences this fall. Please drop by the booth if you’re attending either event. We’d love to hear about how you’re using ActiveData for Excel and show you some of the new things we’re working on.
October 28-31: ACFE’s Canadian Fraud Conference in Ottawa, Ontario.
More info: http://www.acfe.com/canadian-about.aspx
November 5-8: GTEC 2012, Canada’s Government Technology Event in Ottawa, Ontario.
More info: http://www.gtec.ca
We’ve listed these conferences on our new events page:
NEW VIDEO TUTORIALS
#1 – How to use ActiveData for Excel’s Find features to locate data across worksheets. Also includes a section on advanced column querying and importing data using ODBC.
#2 – How to use ActiveData for Excel to risk-rank a universe of Excel spreadsheets using a mathematical scoring model of risky items such as cells containing IF statements and errors in formulas.
TRAINING NEWS – CPE CREDIT COURSE, WEBINARS AND ON-SITE CLASSES
We have now updated and re-issued our 8-hour CPE course, “Auditing, Fraud Detection, and Cash Recovery Using ActiveData for Excel”. It now includes sections on random sampling, stratified random sampling, monetary unit sampling and Digital Analysis using Benford’s Law. For September and October, we’ve reduced the price to $179 and are also offering it without the CPE credits for $99.
Did you miss the ActiveData webinar? For $59, you can view a taped version of it as many times as you like for the next year here: http://tinyurl.com/cdq6bzj
If you’re interested in on-site training for ActiveData for Excel, please contact email@example.com so that he can draft a specific learning plan for your particular situation and discuss on-site training options with you.