IT SIG Newsletter - November 2007 Edition

Articles

1. Shelf-Ware - Can We Put It To Use?
2. Access Databases (Out Of) Control?
3. Much Ado About Everything - Excel 2007!
4. Further Reading and References

1. Shelf-Ware - Can We Put It To Use?

Many companies find out about new software packages available off the shelf and think that they will solve some of their problems. However it is very easy to buy something but harder to install and integrate the software within current practices. David Smith provides some thoughts about the usefulness of shelf-ware and provides some steps for successful adoption within your practice.

Have a look on your shelf or in your computer room. Is there software there that is still in its shrink wrap? Have you downloaded software and then never used it? Why has this happened?

You have met a salesman who has convinced you that the product can improve efficiency or provide improved services to your clients and you buy it but it never gets used. In accounting practices, products that are bought and remain on the shelf are typically those that are not mission critical to the immediate operations of the practice. In many instances, they are products which would allow the firm to expand its services. It's a frightening statistic that for most organisations shelf-ware can make up 20% of software expenditure!

Products focused on business improvement rather than accounting and tax preparation are often in this category. For example, many firms have acquired scenario planning tools like MYOB's Profit Optimiser, budgeting tools like WinForecast or best practice procedures and precedents from companies like Business Fitness and struggle to successfully implement them in the practice. Yet some firms succeed in implementing such tools and reap the rewards from expanding their range of client services and from improving their process efficiency.

So why do some firms succeed where others fail? In our clients, we see some firms capitalising on the software they have acquired. Generally this is for the following reasons:

1. The firm has a clearly define technology plan and budget which is aligned to the firm's overall strategy. This means that when software is being evaluated, the firm is aware of their objectives and can evaluate whether the product will assist the practice in meeting those goals. If the product passes that test there is a much greater likelihood that the firm will commit to the product's implementation to ensure it is successfully used to achieve the firm's strategic objectives. Unfortunately, few firms have a cohesive strategy and even fewer have a technology plan and budget which means that decisions are haphazard and commitment to implementation weak.
2. Users have been involved in the software selection process. Users need to embrace the technology rather than having it forced upon them. When reviewing software it is best to have a selection committee comprised of a cross section of people who will have to implement and use the product. Their concerns can be discussed so that when a decision to buy is made those concerns have been addressed and everyone is then committed to a successful implementation.
3. Communication with all team members has occurred. Often, in many firms, team members don't know what software the firm has acquired or how to access it or use it. Regular update sessions should be held for all team members where different products can be highlighted and the benefits to the firm and its clients can be explained.
4. Dedicating sufficient resources to ensure implementation has happened. In most firms failure often relates to inadequate resources dedicated to the implementation. In a time where there is a shortage of quality team members and high pressure to complete compilation work for clients, it is difficult to dedicate the resources required to implement new software. Yet without those resources, implementations will fail. Implementation requires the appointment of a team member to champion the implementation. Ideally, this person should be someone who will use the software so that they understand the benefits it will deliver. This champion would be responsible for:

   • Liaising with the supplier to ensure all issues are sorted out quickly
   • Training users to ensure that they are trained to use the product the way the firm requires rather than relying on the supplier's generic training
   • Acting as the first point of contact for team members who are having problems using the product to ensure rapid response to issues that will maintain efficiency and reduce the frustration of team members
   • Incorporating the use of the software into the firm's processes (see below)
   • Developing of marketing plans where the software will result in new services being offered to clients
5. Product champions have developed an implementation plan with clearly defined tasks, responsibilities and deadlines. They are accountable for the success of the implementation and their performance in managing the project is part of their performance review. Regular review meetings with the management team have occurred during the implementation.
6. The software has been incorporated into the firm's processes. Firms should redesign their processes to incorporate any new software so that it is used regularly. For example some firms have redesigned their processes so that benchmarks are produced or scenario planning is undertaken for all of their larger clients. Where the use of the software is not part of the process, it is often forgotten as deadline pressure mounts. Firms that have included the extra services in their process allow for the extra time in their planning and have created an expectation in their clients for these additional services. Accordingly, the software becomes an everyday tool used by the firm.
7. Specialist users are appointed when the software is not used regularly by some team members. Rather than wasting time trying to remember how to use the software, some firms have found that centralising the work to some expert users will lead to the greatest efficiency. This can often apply to scenario planning tools such as MYOB's Profit Optimiser which many firms find an effective tool to use in meetings with clients to assist them in understanding their business drivers. Some firms use the specialist users in these meetings to drive the software, leaving the partner or manager free to discuss the issues with the client without worrying about the software.

Finally, a critical eye is a must during evaluation. Does the product really deliver the benefits that are outlined by the sales representative? Do our clients really want any new service offering the product may deliver? Is the product easy enough for team members to implement and use? Can we dedicate sufficient resources at this time to implement the product successfully?

So grab that software off the shelf, appoint a champion and develop your implementation plan!

David Smith is a former president of the Institute and is a director of FMRC Smithink Pty Limited which assists accounting practices in practice strategy, management, technology and process improvement. A free monthly newsletter is available by subscribing at www.fmrcsmithink.com.

Top

2. Access Databases (Out Of) Control?

This article was first published in the August 2007 edition of Chartech, the magazine for members of the IT Faculty of the Institute of Chartered Accountants in England and Wales and is reprinted with permission. The issues raised are equally relevant across the world.

"If 30% of the figures in your company's annual report were dependent on software applications that had been developed by users and are not supported by the IT department, would you say your company has an issue?" Roger Cooper and Fred Wilson believe so!

There is evidence to suggest that upwards of 30% by value of the figures in the balance sheets and profit & loss statements of many major companies, may at some point pass through or be manipulated within Microsoft Access databases.

The use of MS Access in the Finance area has grown exponentially in the last few years. There are a number of reasons for this, for example:

• Major changes in reporting requirements e.g. IFRS, Basel2
• Legacy ledger systems unable to provide all the data in the format required to comply with new requirements
• Lack of investment in the back office and finance areas
• Increasing demand for management information often requiring rapid response
• The difficulty of managing increasingly large amounts of data within spreadsheets; Access databases are often linked to or generated from spreadsheets

MS Access provides an excellent solution. It enables the Finance department to respond quickly and flexibly to new demands for management information, business analysis and external reporting.

It allows users to avoid laborious project processes and lengthy waits while the IT department develops the desired application. In fact it is often the preferred option where the alternative would be to divert budget and scarce IT resources away from investment in new products and customer services.

If Access is the solution, what is the problem?

MS Access databases are generally developed and operated by users themselves, in an environment where there are few formal controls over development, testing, access and documentation.

The users and their managers are often self-taught and while they may be able to construct an application using basic MS Access functionality and in-built wizards, they generally have little training or experience in the use of development life cycles, structured programming, testing, access and change control, let alone the concepts of relational databases, joins and normalisation.

Consequently there are significant risks that the databases will not perform as required leading to financial loss, compliance failure and damage to the company's reputation. Not to mention the potential consequences for companies subject to Sarbanes-Oxley.

Obviously not all databases are critical to the functioning of the business. The first challenge is to identify those that are. Typically they would include databases:

• Where the output is used for financial accounting, statutory, regulatory or fiscal reporting and where any potential error could be material.
• That support key controls or are applications in Sarbanes-Oxley terms
• Where failure to perform consistently could expose the company to a significant operational loss
• That hold data of a confidential nature about customers or staff, or data of potential value to competitors

In our experience, Access database applications often suffer from one or more of the following problems:

The work undertaken to comply with Sarbanes-Oxley exposed end user computing as a serious issue and, in particular, it became clear that the extent of reliance on Access databases was little understood.

Even relatively small finance departments can and do rely on dozens if not hundreds of Access databases often built up over the years by different users of varying skill and ability. Just unravelling the interdependencies between these databases, spreadsheets and other data processing systems can be a daunting task.

Sarbanes-Oxley has thus been the catalyst which has led many large organisations to recognise their dependence on Access databases for financial reporting and business critical activities, and the risks that uncontrolled proliferation poses.

For those in the financial service industry, the Financial Services Authority (FSA) has also been issuing wake up calls, noting that there is:

• "Increasing use of ACCESS in bad ways" and a
• "Lack of spreadsheet and ACCESS audit skills".

[Editor's Note: The Financial Services Authority (FSA) is an independent non-governmental body in the United Kingdom given statutory powers by the Financial Services and Markets Act 2000.]

So what is the answer?

Eliminating the use of Access is unrealistic. There are many valid business needs that Access databases satisfy. This is especially true in the Finance area where rapid change and complex reporting requirements are the order of the day.

In a nutshell, the issue is not with the tool itself but with its uncontrolled proliferation and misuse. The FSA puts it very succinctly:

Many people are put off by the size of the task of controlling Access databases across their organisations. As one major bank's IT department put it "the Bank has no knowledge of the number of Access applications it owns - a conservative guess on the number of (Microsoft Office) applications that will need to be checked for compliance is over 1.5 million".

Bringing MS Access databases under control requires a systematic approach which can be broken down into these steps:

1. Define the Rules: Develop a policy and a definition of what constitutes a business critical Access database. Set standards and a timetable for compliance.


2. Discover and Analyse: Find the location and dependencies of the Access database population. There are tools available to assist in this process. Pick one that can provide a detailed analysis of the complexity of each database as well as mapping its dependencies. That will help with the next two steps.


3. Assess the Risk: Decide which Access applications are actually business critical and require controlling in line with the defined policy and standards.


4. Validate and Base-line: Test that each Access database falling within the control policy is performing as required. Decide which databases need to be repaired, re-developed or migrated. All business critical Access databases must be base-lined before change control is implemented.


5. Control and Log: Ensure that only authorised employees can access and change data in a controlled manner, and that all relevant actions are audit logged. This is where a good software tool can make all the difference. It is not feasible to do this manually.


6. Development Life Cycle: Introduce a life cycle to ensure that new Access databases are developed sensibly and documented, and that subsequent changes are controlled. Again, a good software tool can assist in this process and provide a secure environment for the development of the most business critical Access databases.

Sounds over the top? Not if you want to be sure that your key Access applications function as expected and will continue to do so in the long term.

The good news is that there are now a number of UK and US organisations that have developed software tools to assist in the above process. They remove much of the manual effort that would otherwise be required especially in the discovery, validation and control phases.

The bottom line

Although the task may appear daunting, in our experience it is perfectly doable with the software tools now available. It is certainly more cost-effective than trying to replace all business critical Access databases with IT systems, even if this was an option.

It is therefore possible to continue benefiting from the flexibility of Microsoft Access, while at the same time ensuring that the necessary controls are in place to satisfy strict audit and regulatory requirements including Sarbanes-Oxley.

In addition to achieving regulatory compliance, there are potential business benefits in bringing Access databases under control. As well as reducing the risk of error and fraud, significant time and costs are typically saved in data gathering and reconciliation.

The bottom line can be significant cost savings and renewed confidence in the outputs from your business critical Access databases.

Roger Cooper is a professional Internal Auditor and has worked within both the Public and Private Financial sectors specialising in IT audit. He headed up the Sarbanes-Oxley project for End User Computing in a major UK bank where he devised and implemented the approach to bring end user developed applications under control. He is now an independent consultant specialising in IT and End User Computing control. (www.rjclimited.co.uk)

Fred Wilson worked for leading US and UK financial institutions where he headed the development, marketing and delivery of electronic banking and e-commerce services. As head of a Sarbanes-Oxley programme he directed the implementation of an innovative solution involving Access databases used in financial reporting. He is now an independent business consultant and leadership coach. (www.thijs.co.uk)

Top

3. Much Ado About Everything - Excel 2007!

With each new release of Microsoft Excel there are an increased number of new and improved features. This is particularly true with the recent release of Microsft Excel 2007. In this article, Roger Pegler and Dickla Yaretzky provide some insight into the new version and offer some tips for getting the most from Excel 2007.

You have a large amount of data. You want to juggle, rearrange, recalculate and perhaps enhance it. Most importantly though you need to accurately display and report it in a format that makes sense to everyone. Easier said than done!

Using Excel 2007 can make that job a whole lot easier and more enjoyable for you. So we wanted to introduce you to some of the new features offered by Microsoft Excel 2007 to help you manage your data better, faster and more efficiently.

The new interface

The first simple yet probably the most useful enhancement is the new interface. Icons are laid out in the toolbar area which makes it easier to find the right tools as and when you need them.

This new 'results - oriented' interface makes the most commonly used features of Excel more accessible and visible for power users and novice users.

Expanded spreadsheets

One of the most common problems with Excel have always been far too much data and not enough space, this however is not the case anymore.

Excel 2007 makes it easier to import, organize, and explore massive data sets within significantly expanded spreadsheets. Excel 2007 supports spreadsheets up to one million rows by sixteen thousand columns in size. Now you can have a spreadsheet with a column for every day of the year! This new feature reduces the need to work with multiple linked spreadsheets and other applications when analysing large amounts of data.

Excel's speed and performance has been enhanced along with this expanded capacity. The old memory limits have gone so now Excel uses whatever RAM your workstation has. Formulas that can be calculated independently are multi-threaded to recalculate concurrently across multiple processors.

Improved Tables

Once you have all your data in a spreadsheet ready to go, there are several options available to you when it comes to design and structure of your data.

By simply pressing CTRL+T within a worksheet Excel quickly inserts your data range into a table providing you with a lot more advantages than just an average data range. When analysing data within a large table the table headings remain in view allowing you to scroll through your data without losing your place. You can easily add a new column to the table and Excel will automatically copy your formula for all records in the table.

Applying a different style to the table such as horizontal branding (formatting by alternate rows) makes it easier for the user to follow each row of data across the table. Custom styles can be developed to allow corporate branding of your data.

Your table expands appropriately as and when you enter more data adopting all formatting and formulas with it. Charts built from table columns expand automatically without any manual interference.

Filter options

The table filter option has also always helped you flexibly analyse your data

Excel's filters now recognise the type of data contained within a column and provides you with relevant options for the data type (a column of numbers will include options such as top n / bottom n, a column of words will provide options such as words beginning with a specific letter). The filter options allow you to filter a column on distinct values therefore displaying only the values you request.

Pivot tables

The pivot table which has allowed you to rearrange data to answer numerous business questions has also been enhanced.

Excel 2007 includes field heading check boxes as well as drag and drop functionality making reporting far easier and more flexible. Updates can be deferred till you have restructured your pivot table, avoiding all those unnecessary refreshes. Drill downs can expand within a column instead of adding new columns with each level, making it much easier to see all the data on the screen. Rows and columns can be individually formatted, including being hidden and reordered. All the formatting is retained, even after refreshing, making pivot tables a viable reporting tool.

Of particular interest is the ability to "break" the pivot tables which converts the pivot tables into individual cells using the new cube formulas. This allows you to reorganise the data into whatever arrangement you want. For example, you may want to insert a new column with your own calculation, such as a % variance, and the arrangement will stay fixed, even when the data changes.

Cell styles and Conditional formatting.

After organising your data you may want to analyse specific trends and present your findings in a professional manner. Excel 2007 has enhanced these features to make it easier for the end user to use.

With cell styles (shown below), you can mark bad or good values statically. With conditional formatting, you can do it dynamically, so that data is highlighted according to its value. The conditional format rules have been expanded from 3 levels to 64 levels and can be based on value, cell contents, rankings, average values and uniqueness.

Conditional formatting is more than just background colour. It now includes a range of visualisation schemes such as gradients, thresholds, and performance indicator icons.

Some of the options are highlighted in the screen below.

It is a great deal easier and more versatile as you can do so much more without needing to write any formulas. You can use the features to create heat diagrams (red for high numbers and green for low ones); data bars (background colour graduated according to the relative value of the cell) and icon sets which enable you to flag values with traffic lights, arrows and any other easily recognisable symbols.

Charting tools

Making charts look professional and presentable is probably the last thing you get to, yet the presentation of your information and findings can be your most powerful selling tool. You can easily build professional-looking charts by using the charting tools option in the new user interface. Rich visual enhancements can be applied using features such as 3-D, soft shadowing, and transparency.

The charting engine is consistent in Microsoft Excel 2007, Microsoft Office Word 2007 and Microsoft Office PowerPoint 2007 allowing you to interact with the charts in the same way regardless of the application.

What else?

So far we have discussed what's been enhanced i