Financial Data Using Advanced Excel

Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.

Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario manager). It has a programming aspect, Visual Basic for Applications, allowing the user to employ a wide variety of numerical methods, for example, for solving differential equations of mathematical physics, and then reporting the results back to the spreadsheet.

It also has a variety of interactive features allowing user interfaces that can completely hide the spreadsheet from the user, so the spreadsheet presents itself as a so-called application, or decision support system (DSS), via a custom-designed user interface, for example, a stock analyzer, or in general, as a design tool that asks the user questions and provides answers and reports. In a more elaborate realization, an Excel application can automatically poll external databases and measuring instruments using an update schedule, analyze the results, make a Word report or PowerPoint slide show, and e-mail these presentations on a regular basis to a list of participants.

Key Learning Points:

MODULE 1: SECURITY AND PROOFING

SECURITY FEATURES

• Working with Security Features

PROTECTING EXCEL FILES

• Specifying a password for opening a workbook
• Specifying a password for saving a workbook
• Removing a Workbook Password
• Using the Read-only option
• Creating backup files automatically

WORKSHEET PROTECTION

• Protecting Worksheets
• Un-Protecting Worksheets
• Protecting Cells

WORKING WITH DIGITAL SIGNATURES

• Using Digital Signatures
• Signing a File
• Removing a digital signature from a file

CHECKING SPELLING

• Using the Spell Checker

• USING DATA VALIDATION

  • Setting data validation
  • Creating the Input Message
  • Displaying an Input Message
  • Creating the Error Alert
  • Displaying an Error Alert

  USING TEXT TO SPEECH

  • Reading back a group of cells
  • Using Speak On Enter
  • Turning off Speak On Enter

  DOCUMENTATION

  • Working with Comments
  • Adding a Comment
  • Displaying a Comment
  • Editing a Comment
  • Deleting a Comment

  MODULE 2: ANALYZING DATA

  WHAT-IF ANALYSIS

  • Using What-If Analysis

  GOAL SEEK

  • Using Goal Seek
  • Applying Goal Seek

  DATA TABLES

  • Using Data Tables
  • Creating a one-variable Data Table
  • Creating a two-variable Data Table
  • Speeding up calculations with Data Tables

  SCENARIO MANAGER

  • Using Scenario Manager
  • Adding a Scenario
  • Showing a Scenario
  • Deleting a Scenario
  • Editing an existing Scenario
  • Summarizing Scenarios

  • SOLVER

    • Using Solver
    • Installing Solver
    • Applying Solver
    • Changing  a Constraint
    • Deleting a Constraint

    MODULE 3: TEMPLATES AND STYLES

    WORKING WITH TEMPLATES

    • Using Templates
    • Creating a Template
    • Applying Templates

    WORKING WITH STYLES

    • Using Styles
    • Creating a Style
    • Applying a Style
    • Editing a Style
    • Deleting a Style
    • Copying Styles from another Workbook

    MODULE 4: COLLABORATION AND CONSOLIDATION

    SHARING WORKBOOKS

    • Using Shared Workbooks
    • Allowing Workbook Sharing
    • Customizing Workbook Sharing Options
    • Comparing and Merging Shared Workbooks

    SENDING WORKBOOKS

    • Sending Workbooks for Review
    • Sending a Worksheet as Attachment
    • Routing a Workbook
    • Routing a Workbook to the next recipient

    CONSOLIDATION

    • Consolidating data over several Worksheets
    • Consolidating selected rows and columns from several Worksheets

    • MODULE 5: IMPORTING DATA

      IMPORTING DATA FROM EXTERNAL SOURCES

      • Using Database Terminology
      • Importing Data into Excel

       IMPORTING TEXT FILES

      • Importing text files into Excel
      • Importing text using the Text Import Wizard
      • Refreshing data from imported text files

      USING MICROSOFT QUERY

      • Adding a Data Source
      • Creating a Query

      QUERYING DATA FROM THE WEB

      • Using Web Queries
      • Accessing the New Web Query dialog box
      • Creating a new Web Query
      • Saving a Web Query
      • Setting Web Query options
      • Running a saved Web Query
      • Refreshing a Web Query

      REFRESHING DATA

      • Refreshing external data without losing the formatting
      • Refreshing external data automatically

      MODULE 6: MACROS AND CUSTOM CONTROLS

      WORKING WITH MACROS

      • Using Macros
      • Recording a Macro
      • Assigning a shortcut key to your Macro

      • RUNNING MACROS

        • Running a Macro using the main menu
        • Running a Macro using a shortcut key
        • Running a Macro using a button

        ADDING CUSTOM CONTROLS

        • Working with Buttons
        • Creating a new Button
        • Renaming a Button
        • Formatting Buttons
        • Linking an existing Macro to a Button
        • Deleting a Button

        MODULE 7: CUSTOMIZING EXCEL 2010

        CONFIGURING EXCEL 2010 OPTIONS

        • Customizing Excel Options
        • Customizing View Options
        • Customizing Calculation Options
        • Customizing Edit Options
        • Customizing General Options
        • Customizing Transition Options
        • Customizing Custom Lists Options
        • Customizing Chart Options
        • Customizing Color Options
        • Customizing International Options
        • Customizing Save Options
        • Customizing Error Checking Options
        • Customizing Spelling Options
        • Customizing Security Options

        CUSTOMIZING THE EXCEL ENVIRONMENT

        • Displaying Toolbars using the Customize dialog box
        • Creating a Custom Toolbar
        • Adding a Menu to a Toolbar
        • Attaching a Toolbar
        • Deleting a Custom Toolbar
        • Resetting a Menu
        • Resetting a Toolbar

        • WORKING WITH WORKSPACES

          • Using Workspace files
          • Saving your Workspace