Dan Parish Program Manager Microsoft Session Code: OFC 304

Regulatory compliance

Affects almost all public companies Local, state, and federal requirements

The spreadsheet challenge

Spreadsheets are easy to develop, flexible and powerful Spreadsheets support many critical business functions Often not thought of like a database or software program

It's all about the process

Spreadsheet compliance cannot be achieved through technology alone Critical spreadsheets require sound development and usage practices

Getting started

Before even getting to the plan, you need: Executive-level commitment IT and business users to be on the same page Appropriate resources ---------- -----------

Evaluate your situation

Inventory relevant spreadsheets Identify business-critical spreadsheets

Implement appropriate controls

Identify at what level your controls should be Two main types of controls: Preventative Detective Potential Risk

Examples

Unauthorized modification of historical data may damage the audit trail.

Control Activity Convert spreadsheets from previous reporting periods to a read-only format and security archive them for later retrieval.

Entered data is incomplete or disagrees with the source, which results in output and reporting errors.

Use “check cells” to validate data accuracy and the completeness of an entry.

Develop a long-term spreadsheet development and maintenance methodology

Spreadsheet development shares many characteristics with software development Error rates are similar Benefits of a sound development lifecycle are similar Define Requirements Design Implement Test and Verify Deploy Maintain and Document

Define requirements

Create detailed description of spreadsheet’s business purpose Scope and define boundaries Validate with users that spreadsheet will meet business needs

Design

Maps a detailed plan for implementing business requirements End result is a spreadsheet ‘blueprint’ Well designed spreadsheets include: Separation of input, output, and calculation cells Lockable and/or protected cells that should not be modified A standard organizational method Standard naming conventions throughout Named ranges to reduce errors and increase readability Simple formulas Extensive documentation

Implement

Based on the requirements and design already created Should simply be assembling the pieces described in the blueprint Testing and verification should occur throughout the implementation process

Test and verify

Like all software, spreadsheets will contain errors Ways to test spreadsheets include: Targeted audits Test case verification Scenario testing Code inspection Should be done by people other than creator

Deploy

When deploying, control activities must be determined and applied Other activities may include: A formal transition to a production environment Back up of source files Storage in a secure location with file access management Sign-off from development, test, and business users A formal approach to versioning and documented release criteria and management Creation of a detailed user manual Training courses

Maintain and document

Critical to ensure long term usefulness of a spreadsheet All changes after deployment must be tested, verified, and documented Documentation of spreadsheets should include: A detailed description of the spreadsheet’s purpose Change log including who and what Embedded comments to explain input, output, and calculation cells Description of the naming conventions used Legend to explain formatting in the spreadsheet User manual complete with examples Contact information for person responsible

A compliance solution using the 2007 Microsoft Office System

Developing robust spreadsheet models

Cell styles Lock important cells Using Excel Tables to reduce errors Defined Names Formula auditing tools

Preventing unauthorized access

Office SharePoint Server 2007 permissions Sharing spreadsheets using Excel Services Controlling what users can see The View Item right Information Rights Management (IRM) In Office Excel 2007 In Office SharePoint Server 2007 Workbook encryption

Managing and monitoring changes

Enterprise Content Management (ECM) in Office SharePoint Server 2007 Content types Versioning Auditing Workflow

Retaining and archiving spreadsheets

Office SharePoint Server 2007 Record Repository Vault capabilities Information management policies Hold Record collection interface Record routing Extensibility

Building a compliance solution using the 2007 Microsoft Office System

Wrap up

Spreadsheets are commonly a critical resource in companies, yet aren’t treated as such It is important for companies to develop a spreadsheet compliance framework with rigorous process controls The 2007 Microsoft Office system can help companies have greater success implementing and enforcing spreadsheet policies

Resources

www.microsoft.com/teched Sessions On-Demand & Community www.microsoft.com/learning Microsoft Certification & Training Resources http://microsoft.com/technet Resources for IT Professionals http://microsoft.com/msdn Resources for Developers www.microsoft.com/learning Microsoft Certification and Training Resources

Track Resources

Excel Blog http://blogs.msdn.com/excel Compliance Whitepaper http://office.microsoft.com/en-us/excel/HA102132911033.aspx

Complete an evaluation on CommNet and enter to win!

© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.