Docstoc

Contract Precedents

Document Sample
Contract Precedents Powered By Docstoc
					               Excel Tutorial

               Auditing
              Scenarios
        Professor Stephen R. Lawrence
    College of Business and Administration
Graduate School of Business and Administration
             University of Colorado
               Boulder, CO 80303

                 303/492-4351
       Stephen.Lawrence@Colorado.Edu
                                             XLS Corporation
                                       Proforma Income Statement
                                                    (in 000's)

               Base Case                           2000              2001            2002            2003
Revenues
    Product Sales                                   1,000             1,200           1,400           1,600
    Contract Revenues                  30%            300               360             420             480
Total Revenues                                 $    1,300        $    1,560      $    1,820      $    2,080

Cost of Goods Sold
    Product Cost                       50%           500                600             700             800
    Subcontractor Cost                 18%           180                216             252             288
Total Cost of Goods Sold                       $     680         $      816      $      952      $      800

Selling, General, and Administrative
     Payroll & Benefits                25%           250                300             350             400
     Rent, Telephone, & Insurance      25%           250                300             350             400
     Equipment & Software               9%            90                108             126             144
     Supplies                           2%            20                 24              28              32
Total SG&A                                     $     610         $      732      $      854      $      976

Net Profit (Loss) Before Taxes                 $       10        $          12   $          14   $      304

Percent Profit (Loss) on Sales                       1.0%              1.0%            1.0%           19.0%
 2004

  1,800
    540
$ 2,340


    900
    324
$ 1,224


#VALUE!
    450
    162
     36
#VALUE!

#VALUE!

#VALUE!
                                    Assignments

                   Auditing Assignment
                1) Find the source of the #VALUE errors in the P&L sheet using
                   the auditing tool
                2) The net profit in year 2003 appears large. Is there a problem?

                   Scenario Assignment
                   Create base case, best case, and worst case scenarios for the
                   P&L sheet using the data shown below (or data you generate).


                                 Base        Best       Worst

        Revenues      2000         1,000      1,200        1,000
                      2001         1,200      1,400        1,200
                      2002         1,400      1,600        1,400
                      2003         1,600      1,800        1,600
                      2004         1,800      2,000        1,800

          Contract Revenues      30%         35%         25%
               Product Cost      50%         45%         55%
          Subcontractor Cost     18%         15%         21%
           Payroll & Benefits    25%         22%         28%
Rent, Telephone, & Insurance     25%         22%         28%
       Equipment & Software       9%          8%         10%
                    Supplies      2%          2%          2%
                                 Auditing Tool
The Excel Auditing tool is an excellent way to find errors and trace the logic of
complex spreadsheets.

          To use the audit tool:
       1) Select the cell to which you wish to audit.
       2) Select menu item Tools/Auditing
                     Trace Precedents (show what goes into a cell)
                     Trace Dependents (show where a cell is used)
                     Trace Errors (where does an error statement arise)
                     Remove all arrows (when you are done)
                     Show Auditing Toolbar (if you have a lot of auditing to do)
                               Cell Comments
The Scenario tool is useful to track and maintain multiple versions of your spreadsheet.

          To use the Scenario tool:
       1) Select menu item Tools/Scenarios
       2) A "Scenario Manager" window will pop up -- click on the "Add" button
       3) An "Add Scenarios" window will appear -- fill in
                      Scenario Name -- descriptive name identifying the scenario
                      Change Cells -- cells that are modified with this scenario
          Click the "OK" button when finished.
       4) Add as many scenarios as desired in the same fashion
       5) To invoke a scenario, select a scenario and click the Show button in the
          Scenario Manager window.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:8/16/2011
language:English
pages:6
Description: Contract Precedents document sample