List of Cma Accounting Formulas by zdt20561

VIEWS: 112 PAGES: 39

More Info
									                       Update on Excel Spreadsheets: Bells, Whistles, and Controls
                       Virginia Society of Certified Public Accountants

Introduction
This workshop is designed to improve your skills using Microsoft Excel. This workshop will cover:

           • Spreadsheet Design
           • Managing the Data                These three topics are covered in this workbook
           • Analyzing the Data
           • Financial Functions              These two topics are covered in the Financial Model workbook
           • Financial Analysis




Workbook Contents
   Tab                    Title

     1     Spreadsheet Design - Some Basic Rules to Follow
     2     Spreadsheet Design - File Properties
     3     Spreadsheet Design - Data Validation with Drop Down Menus
     4     Spreadsheet Design - Password Protection
     5     Managing the Data - Fill Handle
     6     Managing the Data - Paste Special
     7     Managing the Data - Error Checking
     8     Managing the Data - Tracking Changes
     9     Managing the Data - Lookup Tables
    10     Analyzing the Data - Audit Tool
    11     Analyzing the Data - Filtering
    12     Analyzing the Data - Pivot Tables
    13     Analyzing the Data - OLAP
    14     Some Additional Tips
    15     Listing of Financial Functions
    16     Listing of Keyboard Short Cuts



                                                                                                             Main Menu
   17      Excel Add-Ins


Point of Contact:                Matt H. Evans, CPA, CMA, CFM
Excel Spreadsheet Page:          www.exinfm.com/free_spreadsheets.html
Download this spreadsheet and other files used in this presentation from:   www.exinfm.com/vscpa
Each worksheet can be printed by hitting Ctrl-P




                                                                                                   Main Menu
                     Update on Excel Spreadsheets: Bells, Whistles, and Controls
                     Virginia Society of Certified Public Accountants

Spreadsheet Design:
       Some basic rules to start with include:

    (1) Organize your data so that you:
                ■ Have a different panel or worksheet for constants vs. variable inputs.
                ■ Enter contants only once and reference downstream in other worksheets.
                ■ Use error checking formulas next to your cells. You can flag with conditional formatting.
                ■ Minimize embedding values and amounts into your formulas. This cuts down on errors.
                ■ Try to make your formulas point up and to the left, logical flow of how people see things.
                ■ Try to place your formulas in close proximity to the inputs for easy review and validation.
                ■ Add light grey to cells that are constants and formulas to minimize inappropriate changes.

    (2) Try to minimize the use of merging cells since this makes it difficult to navigate, print or change your spreadsheets.

                                    Merged Cells                              Non Merged Cells

    (3) Place the larger range of data down by rows and the lesser range of data across by columns

    (4) Use comments and navigation links to assist the end-user.

           TIP ► Format your comments by clicking on the border edge:


                     The example below brings up an image file as the comment:

                 1   Select the edge of the comment box and right click
                 2   From the Format Comment dialog box, select Colors and Lines
                 3   In the Fill section, open the Color box and choose Fill Effects
                 4   Click the Picture tab and select the Picture you want to use

           TIP ► Insert comments into formulas
                1 At the end of the formual, insert a plus sign "+" followed by the letter N


                                                                                                                    Tab 1 - Spreadsheet Design
             2 Add your comment within parentheses and within quotation marks.

                    5,609      6.70%         376      $5.60 $2,104.50

       TIP ► Print out your comments:
            1 From the main tool bar, select File > Page Setup > Sheet
            2 Select from the Comments drop down list. (None) is the default. Select At end of sheet if you want to
              print a separate page. Select As displayed on sheet if you want to print only displayed comments.

       TIP ►    Hyperlink an Image or Button within your spreadsheet:
            1   Paste the image file into your spreadsheet
            2   Right click on the Image File
            3   Select hyperlink and Link to: Place in This Document




       TIP ►    Insert an audio file into your spreadsheet
            1   Select a cell where you want to place the audio comment (you can move it later if you want);
            2   From the Insert menu select Object;
            3   On the Create New tab scroll down and select Wave Sound (the Windows Sound recorder will appear);
            4   When you are ready, click the record button and record your message. When you are finished, click the stop button;
            5   Close the Sound recorder window. A sound object (speaker icon) will appear on your worksheet;
            6   To listen to the message, double click the speaker icon.



(5) Include instructional clips of other worksheets or cell ranges
             1 Make sure the camera button appears on the toolbar:
                           From the main menu, select View > Toolbars > Customize
                           Click the Commands tab
                           Select the Tools category
                           Drag the Camera button onto the toolbar
             2 Select the range you want to make into an image
             3 Click on the Camera button
             4 Now go to where you want the image to appear - the cross hair is the is the upper left corner for placement of the image.




                                                                                                                       Tab 1 - Spreadsheet Design
(6) Include a Purpose Statement, Point of Contact, and Main Menu if you are developing a workbook.
             ■ Use a modular design - different worksheets for inputs, analysis, and outputs.




                                                                                                     Tab 1 - Spreadsheet Design
                      Update on Excel Spreadsheets: Bells, Whistles, and Controls
                      Virginia Society of Certified Public Accountants

File Properties
           A good internal control for managing files on your computer

  TIP ► Have the Properties Dialog Box pop up when you save files for the first time
  Steps
       1 From the main tool bar, select Tools > Options and choose the General tab
       2 Click on the Prompt for Workbook Properties

  TIP ► Insert keywords in the Summary tab to help locate files on your computer.
  Steps
       1 From the main tool bar, select File > Properties > Properties tab
       2 Enter Keywords that will help locate this file on your computer

  TIP ►    Make Your Files Read-Only to Protect Files from Accidental Deletion
  Steps
       1   From Windows Explorer, locate the file and right click on the file name
       2   Select Properties at the bottom of the pop up box:
       3   Check the Read-only attribute box and click Apply




                                                                                       Tab 2 - File Properties
                     Update on Excel Spreadsheets: Bells, Whistles, and Controls
                     Virginia Society of Certified Public Accountants

Data Validation:

          Useful when you have to enter the same data over and over again into a spreadsheet. This "error proofs" the spreadsheet
          based on the parameters you set for a specific cell range or table.
                                                                                                                            Product
  Steps                                                                                              Input Range            Codes
      1   Create a list of valid entries for the cell range                                                                 XG-004
      2   From the main menu, select Data > Validation                                                                      HG-009
      3   Click on the Settings tab and select the type of validation you want and enter the values                         LW-010
      4   Click on the Input Message tab to show an instruction when you cursor over the range                              BN-003
      5   Click on the Error Alert tab to show an error message                                                             FP-005
                                                                                                                            VS-009
                                                                                                                            QA-011

          You can also validate numberic values:
  Steps
      1   Select the range of cells you want to validate
      2   From the main menu, select Data > Validation
      3   From the Allow drop down list, select Whole or Decimal
      4   From the Data drop down list, select Between
      5   In the Minimum box, enter the low range of the validation
      6   In the Maximum box, enter the high range of the validation
      7   Click OK




                                                                                                                              Tab 3 - Data Validation
                      Update on Excel Spreadsheets: Bells, Whistles, and Controls
                      Virginia Society of Certified Public Accountants

Password Protection:
          Allows you to restrict access to your file, workbook, cells, graphics, charts, or other attributes.

  TIP ► Password Protection of Files is better than Password Protection of Work Sheets, Ranges and Cells. Several Utilities are available
        for cracking passwords once the file is open.

  TIP ► Excel 97 has very weak features when it comes to Password Protection. Recommend users upgrade to Excel 2003.


        A. Password Protect Your File:
  Steps
      1 From the main menu, select Tools > Options > Security > Password to open:

        B. Password Protect Work Sheets:
  Steps
      1 From the main menu, select Tools > Protection > Protect Sheet > Password to unprotect sheet:

         C. Allow editing of selected Cells:
  Steps
       1 Select the Cells you want to remain unprotected (the worksheet should already be protected - see B above)
       2 From the main menu, select Format > Cells > Protection tab and uncheck the Locked box
          Hidden - Prevents any formulas in the cell(s) from being displayed in the Formula bar after the worksheet has been protected.
  TIP ► It's also a good idea to highlight the unprotected cells

          D. Restrict Cursor Movement:
  Steps
         Follow Steps 1 and 2 in C above
       4 Change the worksheet properties: EnableSelection by selecting View > Toolbars > Control Toolbox > Properties
       5 Click on the cell labeled: xlNoRestrictions (to the right of EnableSelection)
       6 From the drop down list, change this to: xlUnlockedCells
                     xlNoRestrictions: In a protected worksheet, selection is allowed for both locked and unlocked cells.
                     xlUnLockedCells: In a protected worksheet, selection is allowed for unlocked cells only.


                                                                                                                            Tab 4 - Password Protection
                    xlNoSelection: In a protected worksheet, selection is not allowed for either locked or unlocked cells.

      E. Hide Work Sheets:
Steps
    1 From the main menu, select Format > Sheet > Hide

        F. Password Protect VBA Macro's:
Steps
    1   Press Alt and F11 to activate the VBA Editor
    2   Select your project from the Project Window
    3   Select Tools > VBA Project Properties > Protection tab
    4   Check the box: Lock project for viewing and enter a password twice
    5   Click OK to save the workbook


        Excel 2003 Feature
        Information Rights Management
        You can designate who can read, revise, or forward confidential information using the Information Rights Management (IRM) feature.
        This can be easier than password protection, but you will have to install the IRM component on your network server.

        Authenticode - Digital Signatures
        Microsoft Office 2003 uses Microsoft Authenticode to create a digital signature. A digital signature is used to confirm that the document
        originated from the signer and no changes have been made. For example, when you run macro's, you can enable only those macro's
        that are on your list of trusted sources with digital signature certificates.
        H. Protect Worksheet with a Digital Signature:
Steps
    1 From the main menu, select Tools > Options > Security
    2 Click Digital Signatures and add the signer's Digital Signature
      NOTE: To create a digital signature, you first must create a digital certificate from a commercial source such as verisign

        Quick Link to purchase a digital certificate:
        http://www.thawte.com/ssl-digital-certificates/buy-ssl-certificates/




                                                                                                                             Tab 4 - Password Protection
Tab 4 - Password Protection
                           Update on Excel Spreadsheets: Bells, Whistles, and Controls
                           Virginia Society of Certified Public Accountants

Fill Handle and Smart Tag - Auto Fill Options
           An easy way to copy and paste with or without auto fill or just paste a format (same as the Paint Brush)

           Fill Handle: The "+" sign that appears in the lower right corner of the cell you have selected.




           Smart Tag: A small icon that appears in the lower right corner of the range that you copied to. This is the Auto Fill Options Smart Tag:
                      Copy Cells - Copy the cell content as is to the other cell(s)
                      Fill Series - Copy the cell content and auto fill to the other cells in a series
                      Fill Formatting Only - Copy the cell format only to the other cell(s)
                      Fill Without Formatting - Copy the cell content as is, but do not include the format


  TIP ► Moving the Fill Handle Up will erase the cell contents

           Examples:

              Copy            Fill           Fill    Fill W/O
              Cells          Series        Format    Format
                      10              10          10        10




  TIP ►    Custom Fills - You can setup your own custom fills for a spreadsheet:
       1   From the main menu, select Tools > Options > Custom Lists > NEW LIST
       2   In the box labeled "List entries" enter all of your entries for your fill list OR select the cells for your list
       3   Click Add and Click OK



                                                                                                                                          Tab 5 - Fill Handle
       Evans




TIP ► Turn off Smart Tags:
     1 From the main menu, Select Tools > AutoCorrect Options > Smart Tags
     2 From the list Show smart tags, make sure None is selected




                                                                             Tab 5 - Fill Handle
                          Update on Excel Spreadsheets: Bells, Whistles, and Controls
                          Virginia Society of Certified Public Accountants

Paste Special
          Can be very useful for editing and altering data in a spreadsheet

        A. Convert Formulas to Values:
  Steps
      1 Highlight the cells you want to convert to values and select Copy
      2 Move to the home position where you want to paste
      3 Select Paste Special and check Values

               Store A     Store B    Store C     Total
               1,050.00      950.00     680.00   2,680.00
                 755.00      250.00     552.00   1,557.00
                 890.00      195.00     265.00   1,350.00
               1,175.00      480.00     805.00   2,460.00


          B. Convert Text Values into Real Values or positive values to negative values
  Steps
      1   Select an empty cell and enter 1 or -1
      2   From the main menu, select Edit Copy for the cell with 1 in it
      3   Highlight the text values you need to convert
      4   Select Edit Paste Special and select the Multiply option
      5   Under Paste, click Values and then click OK

          -1              118
                          122
                          135
                          144
                          174


          C. Use Paste Special to Copy a Format: (Very useful for tables and chart formatting differences)


                                                                                                             Tab 6 - Paste Special
Steps
    1   Select the cell range / chart / table you want to use as your format
    2   Control-C to Copy or from the main menu, select Edit > Copy
    3   Select the cell range / chart / table you want to change in terms of formatting
    4   From the main menu, select Edit > Paste Special > Formats

        Chart 1 has this format:                                                                              Chart 2 has a different format:


                                     Comparison of Store Sales

                                                                                                  1200
          1,400.00
          1,200.00                                                                                1000
          1,000.00
            800.00                                                                                800
                                                                                          Sales
            600.00
                                                                                                  600
            400.00
            200.00                                                                                400
              0.00
                                                                                                  200
                               1                   2                  3
                                                                                                     0
                                                Store                                                    1




                                                                                                             Tab 6 - Paste Special
Tab 6 - Paste Special
Chart 2 has a different format:


                     Sales




                                      Sales




                 2                3




                                              Tab 6 - Paste Special
                      Update on Excel Spreadsheets: Bells, Whistles, and Controls
                      Virginia Society of Certified Public Accountants

Error Checking
         Used to check formulas for errors.

  Steps
      1 From the main menu, select Tools > Error Checking
      2 Error Checking dialog box will appear to show potential errors. If you are re-checking the worksheet again, click Options, click
                     Reset Ignored Errors and click OK
      3 The potential error appears in the formula bar. The text describes the potential error
      4 Click on the button on the right side of the dialog box. Depending upon the error, you may elect to Ignore.
      5 Continue until errors have been reviewed.
      6 Select Tools > Options > Error Checking tab to control what gets checked.

                               78          99          35   #DIV/0!

  TIP ► Quickly trace the formula by inserting the I Bar into the open toolbar view of the formula.


         Types of Errors:

         ##### — Column is not wide enough, or a negative date or time is used
                   Possible Solutions
                   Widen the column to fit the contents Double-click the boundary on the right side of the column heading.
                   Shrink the contents to fit the column Select the column, and on the Format menu, click Cells.
                   Change the formatting The contents may fit with a different formatting.
                   If dates or times are negative, check your data and formulas.


         #VALUE! — The wrong argument or operand is used
                   How to Fix
                   Use the Trace Error tool to check your arguments, such as cell references, numbers and operands.
                               Click on the cell that contains the #VALUE error
                               On the Forumula Auditing toolbar, click Trace Error


                                                                                                                                   Tab 7 - Error Checking
#REF! — The formula contains an invalid cell reference
          Possible Solutions
          Change the formulas or restore the cells that reference the formula.
          Make sure formula links are still valid
          Verify that macros are referring to valid cells


#NAME? — The formula contains an invalid cell reference
         Possible Solutions
         Misspelled function name or text in formula
         Make sure formula links are still valid
         Edit errors in formulas, such as double quote marks, parenthesis, etc.


#DIV/0! — A number is divided by zero (0)
           Possible Solutions
           Check the division in your formula - should not divide by 0
           Make sure your formula does not reference a blank cell or a cell that contains 0
           Enter a value other than 0 as your divisor




                                                                                              Tab 7 - Error Checking
                         Update on Excel Spreadsheets: Bells, Whistles, and Controls
                         Virginia Society of Certified Public Accountants

Track Changes
           Useful when you need to understand who is making changes to a spreadsheet and why.


           Tracking Changes
  Steps
       1   From the main menu, select Tools > Track Changes > Highlight Changes
       2   In the Highlight Changes dialog box, select Track changes while editing
       3   Select Highlight changes on screen
       4   Open the When menu and select All
       5   Open the Who menu and select Everyone. This makes your file shared.
       6   Click OK
  TIP ►    You can track changes with only certain cells by using the Where option. If you leave this option blank, all changes are tracked in the workbook.

  TIP ► Changed cells have a triangle in the left corner, surrounded by a blue line around the cell.

           Accepting or Rejecting Changes
  Steps
      1    From the main menu, select Tools > Track Changes > and select Accept or Reject Changes option.
      2    In the Dialog Box, select "Not yet reviewed" to see all changes or "Since date" to see changes after a certain day.
      3    Click OK
      4    In the Accept or Reject Changes dialog box, review the edits to the cell.
      5    Select the Reject or Accept button for each edit.
           NOTE: Rejecting your changes will erase the change and restore the cell back to the original entry.

  TIP ► If you want to make changes but not see them on screen (useful if you're making a lot of changes),
        don't select the "Highlight changes on screen" option. Select the option later to see the changes you made.

  TIP ► To review your changes later, select Close in the Accept or Reject Changes dialog box.

           $ 2,050.00


           Note: When you track changes, the workbook is now [Shared] and some functions may not work. Remove sharing by
           unchecking the box from Tools > Share Workbook




                                                                                                                                                    Tab 8 - Track Changes
                      Update on Excel Spreadsheets: Bells, Whistles, and Controls
                      Virginia Society of Certified Public Accountants

Lookup Functions:
       Very useful for looking up information from tables and working with worksheets that are split between a master file (permanent
       type records) and data files (regularly updated records). You can lookup values or text.


     1 Start with your Data Table - this will be used as the Array in your Lookup formulas
       <- - - - - - - - - - - - - - - - - - - - - - - - - - - - array - - - - - - - - - - - - - - - - - - - - - - - >
            ID            Last Name             First Name          Age               Function              Points     IF Statement
              2306 Evans                     Matt                            50 Consultant                      1,050 Bonus
              1788 Frazier                   John                            43 Manager                           980
              1568 Johnson                   William                         56 Executive                       1,175 Bonus
              1477 Frazier                   Charlotte                       35 Administrator                     750
              1896 McDonald                  Shelley                         39 Administrator                     610
              1962 Bissell                   Carmen                          32 Staff                             885
              2077 Alexander                 Stewart                         37 Manager                         1,305 Bonus

     2 Use basic lookup functions in conjunction with your array

                      =LOOKUP("value or text you want to lookup", range name of the table, column or row index number,
                                  FALSE if you want an exact match, TRUE is the default for closet match)

       Example - Vertical Lookup (most data tables are organized by columns with column headings)
       Typical Accounting Files are full of codes         Setup and use a Lookup Table to make codes meaningful:
                                             1568         Johnson
                                             1962         Bissell
                                             1788         Frazier
                                             2306         Evans

       Example - Match (locate the position of a record)
                 How far down is the record Bissell?                  6 Note: Use the vector and not the full array name to avoid getting the #N/A
       Example - Index (what value resides in this postion)
                 What value shows up in row 5, column 4? Administrator Note: Use the vector and not the full array name to avoid getting the #N/A




                                                                                                                                        Tab 9 - Lookup Tables
Tab 9 - Lookup Tables
                    Update on Excel Spreadsheets: Bells, Whistles, and Controls
                    Virginia Society of Certified Public Accountants

Audit Tool Bar
        Useful for understanding the sources that feed a dependent cell in your spreadsheet.

  Steps
      1 From the main menu, select Tools > Formula Auditing > Show Formula Audting Toolbar




                                                                                                                             Precedent
                                                                                                                                  1.15
        Precedent: A cell which feeds or drives the formula in question.
        Dependent: A cell which depends on the formula for its value.

                        Both       Precedent Precedent Dependent
                        1050.00      1,150.00 ####### 3,979.00

                  1 Trace Precedents - if the current cell contains a formula, arrows will be drawn leading back to the source cells.

                  2 Remove precedents arrows

                  3 Trace Dependents - arrows will be drawn from the current cell to any other cells that incorporate it into their formulas.

                  4 Remove dependents arrows

                  5 Remove all arrows

                  6 Trace errors - if the current cell is displaying an error (e.g. #DIV/0!), an arrow will be drawn leading back to the cause of the error.

                  7 Trace errors - if the current cell is displaying an error (e.g. #DIV/0!), an arrow will be drawn leading back to the cause of the error.

                  8 Circle invalid data - highlights any cells which fail Data Validation rules that have been defined for the current range

                  9 Remove circles around data




                                                                                                                                                     Tab 10 - Audit Tool Bar
TIP ► Use the Evaluate Formula dialog box to step through a nexted formula:
           Steps
               1 Place your cursor over the cell where the formula is located at
               2 From the main menu, select Tools > Formula Auditing > Evaluate Formula
               3 The Evaluate Formula dialog box will pop up. Click on the Step In button to walk through formula arguments.




                                                                                                                               Tab 10 - Audit Tool Bar
                              Update on Excel Spreadsheets: Bells, Whistles, and Controls
                              Virginia Society of Certified Public Accountants

Data Filtering
           The Data Filter option is very useful for sorting through data with simple drop down menus.

  Steps
      1    Move your cursor over the column heading row.
      2    Turn on the Data Filter - From the main menu, select Data > Filter > Auto Filter. Down Arrow Boxes should appear for each column heading.
      3    Single column filtering - Click the drop down menu for the column heading
      4    Turn Off Filter - Select Data > Filter > Auto Filter to turn off the option and the Drop Arrow Boxes will disappear.


Empl No.       Last Name           First Name           Location       Date of Birth Pay Grade            Department
  00019    Melton             Zachery              Morgantown               5/1/1943          7     Engineering
  00030    Phillips           William              Morgantown               1/5/1949          8     Sales
  00035    Bingers            Alice                Charlotte               6/28/1947          6     Production
  00039    Anderson           Carol                Pittsburgh               4/6/1950          8     Corporate
  00046    Yuller             Joseph               Columbia                3/27/1948          7     Distribution
  00048    Daad               Mostaf               Charlotte               8/11/1949          7     Production
  00055    Landry             Martha               Columbia                3/17/1951          7     Production
  00059    Willis             Paul                 Pittsburgh              1/19/1951          6     Corporate
  00063    Morrison           Donald               Richmond                12/2/1953          6     Engineering
  00079    Mackle             Jerome               Pittsburgh              7/17/1946          9     Corporate
  00081    Walters            Bill                 Columbia                 2/3/1949          6     Accounting
  00088    Jenkins            Paula                Pittsburgh              11/8/1951          6     Accounting
  00092    Bissel             Harold               Charlotte                9/3/1941          7     Engineering
  00101    Jackson            Sherry               Pittsburgh               8/2/1958          8     Administration
  00106    Keyes              Louis                Richmond               12/15/1954          8     Engineering
  00108    Alden              Pam                  Richmond                8/11/1956          6     Administrative
  00110    Zurich             Allen                Morgantown              11/7/1954          6     Distribution
  00111    Bartolla           Frank                Columbia                10/4/1967          7     Logistics
  00115    Sutton             Ralph                Charlotte               1/15/1955          8     Accounting
  00117    Easton             Russell              Charlotte              11/29/1957          8     Engineering
  00119    Pierce             John                 Morgantown              7/22/1952          5     Administrative
  00128    Gardner            Jeff                 Charlotte               4/19/1960          8     Engineering
  00129    Smith              James                Morgantown              6/16/1953          7     Distribution


                                                                                                                                        Tab 11 - Data Filtering
00144   Terrell          Carey                  Morgantown              10/26/1961               7   Distribution
00149   Matz             Julie                  Morgantown               12/4/1960               6   Logistics
00155   Yen              Sei                    Columbia                10/29/1955               5   Administrative
00158   Ruston           Sam                    Richmond                 4/17/1962               5   Distribution
00160   Hollinford       Bradley                Richmond                  4/3/1964               7   Sales
00162   King             Susan                  Richmond                10/11/1956               8   Logistics
00177   McDonald         Gerald                 Columbia                11/13/1960               6   Logistics
00188   Lattimer         Mark                   Richmond                 6/29/1959               6   Sales
00206   Leopold          Karen                  Charlotte                2/27/1962               6   Sales
00218   Hills            Terry                  Charlotte                 9/4/1963               6   Logistics
00226   Nutterfield      Leslie                 Morgantown               6/11/1960               6   Distribution
00233   Studdlemeyer     Peter                  Richmond                 8/16/1977               4   Distribution
00236   Jones            Tammy                  Charlotte                 7/7/1963               6   Production
00239   Sellers          Jim                    Pittsburgh               5/29/1958               7   Marketing
00241   Ingle            Motley                 Richmond                 2/20/1969               6   Marketing
00262   Kelley           Janet                  Columbia                 3/22/1966               9   Sales

      Filtering using Custom Criteria
    1 Click on the drop down menu for the column that you want to customize for filtering and select (Custom . . .)
    2 From the Custom AutoFilter dialog box, select your first criteria for filtering and click Or or And
    3 From the second operator drop down list, select the comparison operator.


                         Filtering Operators:
                                   =            Equal to
                                   >            Greater than
                                   <            Less than
                                   >=           Greater than or equal to
                                   <=           Less than or equal to
                                   <>           Not equal to

                         Wild Cards
                                  *             Any string of characters: S* would find Smith, Sculley, Sull, Sal, Steinberger, . .
                                  ?             Any character in this position: B?t would find Bat, But, Bet, . .




                                                                                                                                      Tab 11 - Data Filtering
Tab 11 - Data Filtering
                              Update on Excel Spreadsheets: Bells, Whistles, and Controls
                              Virginia Society of Certified Public Accountants

Pivot Tables
         One of the most popular features of Excel for data analysis. Allows you to easily view and understand data in
         different ways by moving data attributes between sections of the table - simply drag and drop.


  Steps
      1 List out your data, including the column headings
      2 From the main tool bar, select Data > PivotTable and PivotChart Report. Follow the Wizard

         EXAMPLE
                                                     Product              Sales           Sales         Units    < - Note: Column Headings
            Sales Month           Sales Year           Type               Lead           Region         Sold                  are in single cells
         September                        2005   Soap               Smith              East               1,255
         September                        2005   Paper              Jones              East                  755
         October                          2005   Mix                Keane              Central            2,105
         October                          2005   Paper              Stiles             North              1,005
         November                         2005   Mix                Carlson            Central               950
         November                         2005   Paper              Jones              East                  890
         November                         2005   Mix                Stiles             North              1,860
         November                         2005   Mix                Keane              Central            1,330
         December                         2005   Soap               Keane              Central            2,550
         December                         2005   Paper              Stiles             North              1,240
         December                         2005   Mix                Smith              East               2,250
         January                          2006   Mix                Carlson            Central            3,275
         January                          2006   Mix                Jones              East               1,470
         January                          2006   Soap               Keane              Central            3,025
                                                                                                         23,960

         To show a table of product type sales by Sales Leads:
       3 Drag / Drop the Sales Lead into the left column portion of the Pivot Table Panel
       4 Drag / Drop the Product Type into the horizontal top panel section.
       5 Drag / Drop the Units Sold into the Data center panel


                                                                                                                                   Tab 12 - Pivot Tables
          NOTE: The Pivot Table is showing a "Count" as our data; change to "Sum" by selecting the Field Setting

Table >




 TIP ► Pivot tables may insert a blank column and row for future changes. You can hide these blanks by placing your cursor in
       the table and right click your mouse, select Hide.

 TIP ► When you make changes to your source data, don't forget to refresh             !


          30 Minute On-Line Introduction to Pivot Tables:
          http://office.microsoft.com/training/training.aspx?AssetID=RC010136191033




                                                                                                                                Tab 12 - Pivot Tables
Tab 12 - Pivot Tables
                           Update on Excel Spreadsheets: Bells, Whistles, and Controls
                           Virginia Society of Certified Public Accountants

Importing Data
          Excel includes certain features, allowing you to import data into the spreadsheet from other applications.

          EXAMPLE: You have sales data from another system that you produces a text file. You need to analyze the sales data
          in Microsoft Excel. You can use Excel to Query the data and place it in a set of Cubes, allowing you to analyze the data.

  Steps
          A. Select the Data Source
      1   From the main menu, select Data > Import External Data > New Database Query
      2   In the Choose Data Source box, click the Database tab, select New Data Source > OK
      3   In the Create New Data Source dialog box, type a name for the data source file and select Microsoft Text Driver, click Connect.
      4   In the ODBC Text Setup dialog box, clear the Use Current Directory box and click Select Directory.
      5   In the Select Database dialog box, locate the folder that contains the data source file. Do NOT select the file, just the FOLDER.
      6   Click OK twice and return to Choose Data Source dialog box.
          B. Create the Query (pulls in the data)
      7   From the Choose Data Source dialog box, select the data source file as created per the previous steps.
                            Make sure Use the Query Wizard to create / edit queries is selected. Click OK
      8   In the Query Wizard - Choose Columns dialog box, select the data source file you want to query.
                            Move the columns from the Available tables and columns pane to the columns in your query.
                            Click Next in the next two dialog boxes
      9   In the Query Wizard - Finish dialog box, select Create an OLAP Cube from this query and click Finish.
          C. Create the Cube (allows you to analyze the data in different ways)
     10   Click Next in the Welcome to the OLAP Cube Wizard dialog box.
     11   Select the source fields you want to use, check the box in the Source field column. Also make sure the
                            "Sum" is selected for those fields you want summarized - Summarize by column. Click Next.
     12   Move the fields that you want to use in your analysis from the Source fields pane to the Dimensions pane. Click Next.
     13   Select Save a cube file containing all data for the cube. Enter the path name and file name. Click Finish.
     14   In the Save As dialog box, type a file name that corresponds to the query you have created. Click Save.
          D. Analyze the Query Definition File - "oqy" file
     15   Once the OLAP Cube has been completed, the PivotTable and PivotChart Wizard will appear.
          You can now create a pivot table report or chart.
          You can also use Data Analyzer to analyze the Cube file


                                                                                                                                   Tab 13 - OLAP Analysis
TIP ► Look for this icon to find the "oqy" file:

        SAMPLE SOURCE FILE:

        Download the "Sales" file from:       www.exinfm.com/vscpa
        Use this as your source file to practice on how to do what has been described above.


        MICROSOFT INSTRUCTIONS:
        http://office.microsoft.com/en-us/assistance/HA010127121033.aspx




                                                                                               Tab 13 - OLAP Analysis
Tab 13 - OLAP Analysis
                         Update on Excel Spreadsheets: Bells, Whistles, and Controls
                         Virginia Society of Certified Public Accountants

Other Tips

  TIP ►    Zooming Only Selected Cells rather than the entire worksheet
  Steps
       1   Select the range of cells you want to appear in the screen
       2   From the main menu, select View > Zoom > Fit Selection
       3   Select 100% or the appropriate % from the Custom option.


  TIP ► Displaying Cell Formulas Rather Than Results
  Steps
       1 From the main menu, select Tools > Options > View tab > Window options section and check Formulas

                 5,609       6.70%           376        $5.60 $2,104.50


  TIP ► Line Break Within a Cell - Useful when you have text wrapping within a cell
  Steps
       1 Move your cursor over the cell and select Format > Cells > Alignment > check the Wrap Text
       2 Enter your text and hit Alt - Enter where you want the break to occur
                                        North
                      North Park        Park

           A more complicated version of this is to split the contents of the cell and insert an angle border within the cell:

                            Month

                         Town


  TIP ► Conditional formats applied to color different amounts
  Steps


                                                                                                                                 Tab 14 - Other Tips
     1   Determine and enter the values you want to use for triggering the different formats
     2   Highlight the range of cells you want to format
     3   From the main menu, select Format > Conditional Formatting
     4   For Condition 1, select Cell Value Is and greater than and the value per Step 1
     5   Click Format and select Font and from Color, select the color you want to use, click OK
     6   Click Add to apply the next condition in formatting, repeat steps 4 and 5.


             Bill     Jeff             Chris     Lisa       Barbara               Monthly Bonus
  Jan $         650 $ 1,350        $     1,940 $ 1,290     $ 1,305                % + Trip      $ 2,500
  Feb $       1,100 $ 1,780        $       860 $    720    $ 2,050                % only        $ 1,500   $ 2,499
  Mar $         960 $    880       $       750 $ 1,190     $ 2,550                Gift          $ 1,000   $ 1,499


TIP ► Change the default worksheets that loads when you create a new workbook - Default is 3
Steps
     1 From the main menu, select Tools > Options > General tab
     2 Change the number that appears in the Sheets in new workbook box


TIP ► Change the default path where you open and save files
Steps
     1 From the main menu, select Tools > Options > General tab
     2 Type in the desired path in the Default file location box.




                                                                                                                    Tab 14 - Other Tips
                      Update on Excel Spreadsheets: Bells, Whistles, and Controls
                      Virginia Society of Certified Public Accountants

Financial Functions

       ACCRINT        Returns the accrued interest for a security that pays periodic interest
       ACCRINTM       Returns the accrued interest for a security that pays interest at maturity
       AMORDEGRC      Returns the depreciation for each accounting period by using a depreciation coefficient
       AMORLINC       Returns the depreciation for each accounting period
       COUPDAYBS      Returns the number of days from the beginning of the coupon period to the settlement date
       COUPDAYS       Returns the number of days in the coupon period that contains the settlement date
       COUPDAYSNC     Returns the number of days from the settlement date to the next coupon date
       COUPNCD        Returns the next coupon date after the settlement date
       COUPNUM        Returns the number of coupons payable between the settlement date and maturity date
       COUPPCD        Returns the previous coupon date before the settlement date
       CUMIPMT        Returns the cumulative interest paid between two periods
       CUMPRINC       Returns the cumulative principal paid on a loan between two periods
       DB             Returns the depreciation of an asset for a specified period using the fixed-declining balance method
       DDB            Returns the depreciation of an asset for a specified period using the double-declining balance method
       DISC           Returns the discount rate for a security
       DOLLARDE       Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
       DOLLARFR       Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
       DURATION       Returns the annual duration of a security with periodic interest payments
       EFFECT         Returns the effective annual interest rate
       FV             Returns the future value of an investment
       FVSCHEDULE     Returns the future value of an initial principal after applying a series of compound interest rates
       INTRATE        Returns the interest rate for a fully invested security
       IPMT           Returns the interest payment for an investment for a given period
       IRR            Returns the internal rate of return for a series of cash flows
       ISPMT          Returns the interest paid during a specific period of an investment
       MDURATION      Returns the Macauley modified duration for a security with an assumed par value of $100
       MIRR           Returns the internal rate of return where positive and negative cash flows are financed at different rates
       NOMINAL        Returns the annual nominal interest rate
       NPER           Returns the number of periods for an investment
       NPV            Returns the net present value of an investment based on a series of periodic cash flows and a discount rate


                                                                                                                  Tab 15 - Financial Functions
ODDFPRICE    Returns the price per $100 face value of a security with an odd first period
ODDFYIELD    Returns the yield of a security with an odd first period
ODDLPRICE    Returns the price per $100 face value of a security with an odd last period
ODDLYIELD    Returns the yield of a security with an odd last period
PMT          Returns the periodic payment for an annuity
PPMT         Returns the payment on the principal for an investment for a given period
PRICE        Returns the price per $100 face value of a security that pays periodic interest
PRICEDISC    Returns the price per $100 face value of a discounted security
PRICEMAT     Returns the price per $100 face value of a security that pays interest at maturity
PV           Returns the present value of an investment
RATE         Returns the interest rate per period of an annuity
RECEIVED     Returns the amount received at maturity for a fully invested security
SLN          Returns the straight-line depreciation of an asset for one period
SYD          Returns the sum-of-years' digits depreciation of an asset for a specified period
TBILLEQ      Returns the bond-equivalent yield for a Treasury bill
TBILLPRICE   Returns the price per $100 face value for a Treasury bill
TBILLYIELD   Returns the yield for a Treasury bill
VDB          Returns the depreciation of an asset for a specified or partial period using a declining balance method
XIRR         Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV         Returns the net present value for a schedule of cash flows that is not necessarily periodic
YIELD        Returns the yield on a security that pays periodic interest
YIELDDISC    Returns the annual yield for a discounted security; for example, a Treasury bill
YIELDMAT     Returns the annual yield of a security that pays interest at maturity




                                                                                                           Tab 15 - Financial Functions
Tab 15 - Financial Functions
                          Update on Excel Spreadsheets: Bells, Whistles, and Controls
                          Virginia Society of Certified Public Accountants

Key Board Short Cuts
       Key Strokes        Action Invoked
       F2                 Edit the Selected Cell
       F5                 Go to the Selected Cell
       F7                 Spell Check the Selected Text
       F11                Create a Chart
       Shift + F5         Bring up the Search Box
       Ctrl + A           Select all worksheet contents
       Ctrl + K           Insert a Link
       Ctrl + F6          Switch between workbooks or windows
       Ctrl + Page Up     Move between worksheets in the same Excel File
       Ctrl + Page Down   Move between worksheets in the same Excel File
       Ctrl + '           Insert the value above into the currently selected cell
       Ctrl + Shift + !   Format numbers with comma's and two decimal places
       Ctrl + Shift + $   Format numbers in currency format and two decimal places
       Ctrl + Shift + %   Format number in percent format
       Ctrl + Space       Selects the entire column
       Shift + Space      Selects the entire row




                                                                                        Tab 16 - Keyboard Short Cuts
                     Update on Excel Spreadsheets: Bells, Whistles, and Controls
                     Virginia Society of Certified Public Accountants

Excel Add-Ins
        Excel Add-Ins (xla files) provide increased functionality and features. From the main menu, select Tools > Add-Ins
        Listed below are a few free add-ins:

     (1) Navigation Tool Bar
         Places a navigation tool bar for your workbook into the spreadsheet, allowing you to navigate the workbook with a drop-down tool bar.
         Download this Add-In from:
         http://www.contextures.com/xlToolbar01.html

     (2) Popular Add-Ins from Microsoft:
          Report Manager - Allows you to save reports with your workbook and print the report later.
          Access Links - Allows updating of the Access Database that is linked to your spreadsheet.
         Download these Add-Ins from:
         http://office.microsoft.com/en-us/officeupdate/cd010225441033.aspx

     (3) Some economic add-ins from University of Texas:
          Estimate - Worksheet for capital budget estimates and cost estimates during a project life cycle
          Investment Economics - Evaluates investment alternatives using time value concepts
          Forecasting - Provides several forecasting tools such as moving average, exponential smoothing and regression analysis.
          Inventory - Calculates the economic lot size of inventories under different scenarios.
         Download these Add-Ins from:
         http://www.me.utexas.edu/~jensen/ORMM/frontpage/jensen.lib/index_omie.html

     (4) Data Filtering:
         http://www.rondebruin.nl/easyfilter.htm




                                                                                                                                 Tab 17 - Excel Add Ins

								
To top