Fischer Advanced Accounting Test Bank - PowerPoint by ser19529

VIEWS: 155 PAGES: 30

More Info
									Spreadsheet Professional
   & Sarbanes Oxley

           The world‟s leading
           development and
           testing tool

            Web site:
The Sarbanes Oxley act mandates that a report is produced
each year that testifies that adequate internal controls are in
place for financial reporting…

Section 404

   (a) Rules Required. The Commission shall prescribe rules requiring each
    annual report required by section 13(a) or 15(d) of the Securities Exchange Act
    of 1934 to contain an internal control report, which shall--
      (1) state the responsibility of management for establishing and maintaining an
    adequate internal control structure and procedures for financial reporting; and
      (2) contain an assessment, as of the end of the most recent fiscal year of the
    issuer, of the effectiveness of the internal control structure and procedures of
    the issuer for financial reporting.
   (b) Internal Control Evaluation and Reporting. With respect to the internal
    control assessment required by subsection (a), each registered public
    accounting firm that prepares or issues the audit report for the issuer shall
    attest to, and report on, the assessment made by the management of the
    issuer. An attestation made under this subsection shall be made in accordance
    with standards for attestation engagements issued or adopted by the Board.
    Any such attestation shall not be the subject of a separate engagement.
Virtually all (all?) companies use spreadsheets in some part of
the creation of their published accounts, and these
spreadsheets can contain significant errors…

“   A single wrong figure on a spreadsheet forced Credit Suisse to
     markdown its profits by Swfr 200m (£86m).
    The error came in the German subsidiary of the bank‟s
     Winterthur arm, marking an embarrassing first year in charge
     for the insurer‟s Lenny Fischer. It means … fourth-quarter
     income was lowered 16.7% to Swfr 1 billion…”
London Evening Standard 26th March 2004
We believe that spreadsheet errors are significantly
underestimated by the business and IT

   “The European Spreadsheet Risks Group lists 72 publicly reported errors of
    upto $1billion due to inadequate spreadsheets and/or spreadsheet controls. “

   Both KPMG and Coopers & Lybrand have reported finding error rates of more
    than 90% in live spreadsheets.
    "KPMG Management Consulting audited spreadsheets from over 21 major UK banking and financial
    organisations. 92% of the spreadsheets dealing with tax issues had significant errors, 75% had significant
    accounting errors, and 59% were judged to have poor design".

   Academic studies (including ones using Spreadsheet Professional) regularly
    report 50% or more of spreadsheets containg „code errors‟.

   Our own experience mirrors the above. We are aware of several other
    significant spreadsheet errors that have not been reported in the press.
To meet the requirements of Sarbanes Oxley,
therefore, we believe that there are three key

1.   You must know the location of, and be able to control, your
     business critical spreadsheets and in particular those used for
     the creation of the companies financial results – both at the
     centre and in your company‟s operating units.

2.   These business critical spreadsheets must be
     comprehensively tested for errors, and both the spreadsheets
     and the tests on the spreadsheets documented to meet the
     requirements of SoX.

3.   Training must be provided to those people creating and using
     business critical spreadsheets.
To meet these requirements therefore
we believe organisations need…

1.   A method of control. There are now a variety of vendors
     offering control packages for spreadsheets. – we work with a
     number of them to ensure compatibility of our products.

2.   A method of testing and documenting the critical
     spreadsheets. Spreadsheet Professional, created in 1994, is
     the most established tool for performing such tests with,
     currently, 7000 clients worldwide.

3.   Training of end users. We have teamed with AuditExcel to
     produce video training that can accompany the product to
     provide an introduction to spreadsheet best practice.
Spreadsheet Professional
  –   How it helps meet the Sarbanes Oxley
Spreadsheet Professional has been developed as a
powerful testing and documentation tool, following
some key design principles…

   Design principles
     –   To save the end user time and ef f ort. SP has been designed so that its use saves the end user time, thereby
         encouraging its use.

     –   To be easy to use – It is designed as an addin f or Excel driven by standard menus, so that it is f amiliar to end users.

     –   To not impact on the Spreadsheets tested. SP‟s testing and documenting tools leave the original spreadsheet

     –   To provide evidence of the tests and documentation perf ormed. All tests and documentation are stored in separate

     –   To be compatible with all versions of Excel and to work over a network, thereby easing issues of implementation and

     –   Its tools are divided into f our sections:
               Build tools that encourage the use of spreadsheet design best practice, thereby reducing the probability of an error occurrin g.
               Testing tools to aid the identification of errors.
               Documentation tools to ensure that the organisation can understand the key assumptions and design of the spreadsheet
               Usage tools – to encourage proper use of the spreadsheet models developed.
The Build tools
- Setting up the spreadsheet correctly…

                        The Build tools are
                           designed to
                         encourage best
 SP is driven by an         practice.
 easy to use menu
The Build tools
- Setting up the spreadsheet correctly…
                            The Build bar
                         enables formatting
                         and copying to be
  The standard
                          performed with a
   format tool
                          single key press.
ensures sources
  and units are
The Build tools
- Setting up the spreadsheet correctly…
                         The colour coding
                          and formatting

                                             The translation bar
                                               provides instant
                                                translation into
                                                   English of
                                              formulas entered.
The Build tools
It is possible to apply formatting retrospectively…

                                  The spreadsheet
                                   painter enables
                                 you to colour code
                                    a spreadsheet
                                  after completion.
Testing tools
The test tools apply tests against common

                                     The automated
                                   tests capture many
Testing tools
… and produce a comprehensive test report
indicating where errors may have occurred.

                                        As well as
                                   providing the list of
                                    cells, SP explains
                                     what to look for.
Documentation tools
The tools are designed to provide easy
understanding of a spreadsheet…

                                           The summary
                                         report details the
                                         sheets used and
                                         basic data about
                                         the spreadsheet.
Documentation tools
The tools are designed to provide easy
understanding of a spreadsheet…
                                            The Map report
                                          identifies potential
                                          inconsistencies in
                                           the development
                                                  of the

                          Something odd
                          has happened
Documentation tools
The tools are designed to provide easy
understanding of a spreadsheet…

                                          The translation
                                         report translates
                                         each formula into
                                         English for easy
Documentation tools
The inputs report enables you to check all the
inputs to a spreadsheet against the source data…
          Documentation tools
          The formula tracer rapidly aids understanding of a
          spreadsheet…                                This has proved
                                                           the easiest way to
                                                            understand how
     Easy to
                                                              formulas are
understand printed
documentation can
    be quickly
Usage tools
The quick reports tool enables reports to be created
very quickly…

                                            The quick reports
                                             tool provides an
                                            easy mechanism
                                              for printing out
                                            several reports at
Usage tools
The comparison tool enables changes in
spreadsheets to be spotted quickly…

                                           The comparison
                                         tool enables you to
                                         check whether two
                                          spreadsheets are
                                          the same, and/or
                                           the differences
                                          between two runs
                                             of the same
Usage tools
The sensitivity analyser enables you to identify
what drives your results…

                                             The sensitivity tool
                                              provides a range
                                               of outputs both
                                                graphical and
                                             numerical showing
                                              how a target cell
                                                 varies when
                                               another cell is
Usage tools
The sensitivity analyser and produce written

                                          SP is designed to
                                         provide hard copy
                                           reports of all its
                                           outputs. These
                                            are created as
                                          Excel workbooks
                                         so that they can be
                                          added to reports,
                                          electronically, or
                                              printed out.

   The world‟s leading tool for the production of
    professional quality spreadsheets.
   Ideal for the implementation of appropriate controls
    to meet the needs of Sarbanes Oxley.
   Designed to radically reduce the time taken to build
    and use spreadsheets – and at the same time
    radically improve their quality.
   Impacts all aspects of the Spreadsheet design, build,
    document, test and use.
   Pays for itself the first time it is used.

   Whatever controls are implemented, we believe that
    the success of the implementation will be enhanced
    through the provision of end user training.
   We have teamed with Audit Excel to provide a
    multimedia training package that can accompany
    Spreadsheet Professional.
 The course consists of an introduction to using

Introduction (2 min)
Brief introduction to the topic and using this training course

Auditing Toolbar (5 min)
Loading and using the auditing toolbar which is a fundamental tool in Excel

1- Auditing Toolbar exercise
Load the auditing toolbar and see how it helps to find errors in the spreadsheet.

Solution included in exercise

How Errors Happen
It is extremely easy to make errors in a spreadsheet. Below are some of the typical errors made. Look through the items you are familiar with:

Basic Functions

Copy and Paste (2 min)
Inserting Rows (2 min)
Inserting cells (1 min)
Deleting cells (1 min)
Hiding rows or columns of information (2min)
Linking spreadsheets together (3 min)

Intermediate and advanced functions

IF functions (e.g. SUMIF) (3 min)
VLOOKUP function (5 min)
MIN and MAX functions (2 min)
Referencing (absolute and relative) (2 min)

2- How errors happen
Work through each example of the functions you use. When you get to the external links example open the following workbook.

3- More than one workbook

Work through the example to see what goes wrong when you work across workbooks.
… a comprehensive guide to how to use Spreadsheet
Professional to review spreadsheets (this is just some of the

 Getting Started with Spreadsheet Professional (3min)
 An introduction to Spreadsheet Professional ("SP") and the objectives of this part of the course
 Change the options as indicated in the introduction video. You can stop/ start the video to follow the precise steps.

 4- Feedback template
 A template to use when documenting a review. Use it for the upcoming exercises.

 The Spreadsheet Model review process (3 min)
 A high level overview of the spreadsheet review process and how spreadsheet professional assists in the task.
 A print out of the spreadsheet review process

 Maps (4 min)
 How to use the Maps feature in SP.

 5- Maps exercise

 Run the maps on the 2 sheets in this workbook. Review the maps and look at the inconsistent areas. Document your findings on the feedback template. Tick
 off the formula that you have looked at on the map.

 6-What your map should look like and where you should look
 Highlighted in red are the areas you should have reviewed and documented on the Feedback Template.

 Prioritising the cells you look at (7 min)

 Which cells to look at first to find the errors

 7-Priority Exercise
 Run the test reports on the 2 sheets in this workbook. Review the report and look at the inconsistent areas. Document your findings on the feedback
 template. Tick off the formulae you look at on the maps.

 8- What your report should look like
 What your report should look like.

 9- Suggested findings after exercises
 After reviewing the maps and reports you should have found most of these.
… and a section on Spreadsheet design and
advanced features of Excel

 Good Spreadsheet Design (15 min)
 Basic concepts in designing spreadsheet models.

 External Links (5 min)
 Handle links between spreadsheets using Excels inbuilt features.

 21- External links exercise

 22- Input Data v1

 23- Input Data v2

 Open these 3 files, view the external links and make the changes.

 Find/ Replace (9 min)

 Use this common Excel feature to help in reviews and find cells containing external links.

 Use the files for the external links and find the stray link.

 The GoTo Special Command (7 min)
 Learn how to use this brilliant tool to navigate your spreadsheet.

 24- GoTo Special Exercise

 Follow the instructions in the file.

 The Function Wizard (3 min)

 See what is already built into Excel.

 25- Function Wizard Exercise

 Follow the instructions in the exercise
… each section consists of video clips,
exercises and worked examples…

   Click on the picture to
    see an example video
Next steps and any questions…

To top