Exploring MS Office Excel 2003 Comprehensive Revised Edition and Student Resource CD Package

Dr. Robert T. Grauer        Dr. Robert T. Grauer is an Associate Professor in the Department of Computer Information Systems at the University of Mniami where he has been honored with the Outstanding Teacher Award in the School of Business. Dr. Grauer has written more than 20 books on programming and information systems. His work has been translated into three foreign languages and is in use at all levels of higher education at both the national and international level. Dr. Grauer has also been a consultant to several major corporations including IBM and American Express. He received his Ph.D. in Operations Research in 1972 from the Polytechnic Institute of Brooklyn. Maryann M. Barber Maryann M. Barber is a lecturer in the Department of Computer Information Systems at the University of Miami. She has been honored with several teaching awards, including the Excellence in Teaching Awards for the School of Business, the Freshman Teaching Award, the Apple Polishing Award, the Student Government Professor of the Year Award, the Interfraternity Council Professor of the Year Award, and the University of Miami Excellence in Teaching Award. Maryann is also the Faculty Master at Stanford Residential College. She is a member of numerous leadership honor societies at the University of Miami, including Iron Arrow, Mortar Board, and Omicron Delta Kappa.

Chapter 1: Introduction to Excel: What Is a Spreadsheet?

Objectives

Case Study: Weddings by Molly

Introduction to Spreadsheets

The Professor’s Grade Book

Row and Column Headings

Formulas and Constants

Introduction to Microsoft Excel

Toolbars

The File Menu

HANDS-ON EXERCISE 1: INTRODUCTION TO MICROSOFT EXCEL

Modifying the Worksheet

The Page Setup Command

HANDS-ON EXERCISE 2: MODIFYING A WORKSHEET

A Better Grade Book

Cell Ranges

Copy Command

Move Operation

Learning by Doing

HANDS-ON EXERCISE 3: CREATING A WORKBOOK

Formatting

Format Cells Command

Alignment

Fonts

Borders, Patterns, and Shading

HANDS-ON EXERCISE 4: FORMATTING A WORKSHEET

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 2: Gaining Proficiency: The Web and Business Applications

Objectives

Case Study: The Reluctant Entrepreneur

Employee Payroll

Pointing

The Fill Handle

Comments

HANDS-ON EXERCISE 1: PAYROLL

Excel and the Internet

HANDS-ON EXERCISE 2: CREATING A WEB PAGE

Web Queries

The Time Format

HANDS-ON EXERCISE 3: WEB QUERIES

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 3: Graphs and Charts: Delivering a Message



Objectives

Case Study: The Changing Student Population

Chart Types

Pie Charts

Column and Bar Charts

Creating a Chart

The Chart Wizard

Modifying a Chart

HANDS-ON EXERCISE 1: THE CHART WIZARD

Multiple Data Series

Stacked Column Charts

HANDS-ON EXERCISE 2: MULTIPLE DATA SERIES

Object Linking and Embedding

HANDS-ON EXERCISE 3: OBJECT LINKING AND EMBEDDING

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 4: Using Spreadsheets in Decision Making: What If?

Objectives

Case Study: Bill's Autos

Spreadsheets in Decision Making

Analysis of a Car Loan

PMT Function

FV Function

Inserting a Function

The Goal Seeking Command

HANDS-ON EXERCISE 1: BASIC FINANCIAL FUNCTIONS

Home Mortgages

Relative versus Absolute Addresses

Mixed References

HANDS-ON EXERCISE 2: ADVANCED FINANCIAL FUNCTIONS

The Grade Book Revisited

Statistical Functions

Arithmetic Expressions versus Functions

IF Function

VLOOKUP Function

Working with Large Spreadsheets

Freezing Panes

Hiding Rows and Columns

Printing a Large Worksheet

AutoFilter Command

HANDS-ON EXERCISE 3: THE EXPANDED GRADE BOOK

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 5: Consolidating Data: Worksheet References and File Linking

Objectives

Case Study: Tasty Treats

Consolidating Data

The Three-Dimensional Workbook

Copying Worksheets

Multiple Workbooks

HANDS-ON EXERCISE 1: COPYING WORKSHEETS

Worksheet References

3-D Reference

Grouping Worksheets

The AutoFormat Command

HANDS-ON EXERCISE 2: 3-D REFERENCES

The Documentation Worksheet

HANDS-ON EXERCISE 3: THE DOCUMENTATION WORKSHEET

Linking Workbooks

HANDS-ON EXERCISE 4: LINKING WORKBOOKS

Summary

Key Terms

Multiple Choice

Practice with Excel

Mini Cases

 

Chapter 6: Financial Analysis: Forecasting, Rate of Return, and Amortization

Objectives

Case Study: Whispering Woods Golf Club

A Financial Forecast

Advanced Formatting

Scenario Manager

HANDS-ON EXERCISE 1: A FINANCIAL FORECAST

Workgroups and Auditing

Data Validation

HANDS-ON EXERCISE 2: AUDITING AND WORKGROUPS

The Internal Rate of Return

Creating a Template

HANDS-ON EXERCISE 3: CREATING A TEMPLATE

The Amortization Schedule

The Year, Month, and Day Functions

The MATCH and INDEX functions

HANDS-ON EXERCISE 4: CREATING AN AMORTIZATION SCHEDULE

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 7: List and Data Management: Converting Data to Information

Objectives

Case Study: Alice Barr Realty

List and Data Management

Implementation in Excel

Data Form Command

Sort Command

The Text Import Wizard

Excel and XML

HANDS-ON EXERCISE 1: IMPORTING, CREATING, AND MAINTAINING A LIST

Data versus Information

AutoFilter Command

Advanced Filter Command

Criteria Range

Database Functions

Insert Name Command

Subtotals

HANDS-ON EXERCISE 2: DATA VERSUS INFORMATION

Pivot Tables and Pivot Charts

HANDS-ON EXERCISE 3: PIVOT TABLES AND PIVOT CHARTS

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 8: Data Analysis: A Capstone Chapter

Objectives

Case Study: The Personal Computer Store

Drowning in Data

The Admissions Office

Implementation in Excel

HANDS-ON EXERCISE 1: THE ADMISSIONS OFFICE

The Graduating Class

Implementation in Excel

HANDS-ON EXERCISE 2: THE GRADUATING CLASS

The Men's Store

HANDS-ON EXERCISE 3: THE MEN'S STORE

The Restaurant

Implementation in Excel

HANDS-ON EXERCISE 4: THE RESTAURANT

Summary

Key Words and Concepts

Multiple Choice

Practice Exercises

Additional Mini Cases

Medical Specialties

The Corporate Take Over

Drowning in Data

 

Chapter 9: Automating Repetitive Tasks: Macros and Visual Basic for Applications

Objectives

Case Study: Universal Health Services

Introduction to Macros

HANDS-ON EXERCISE 1: INTRODUCTION TO MACROS

Relative versus Absolute References

The Personal Macro Workbook

HANDS-ON EXERCISE 2: THE PERSONAL MACRO WORKBOOK

Data Management Macros

HANDS-ON EXERCISE 3: DATA MANAGEMENT MACROS

Visual Basic for Applications

HANDS-ON EXERCISE 4: CREATING ADDITIONAL MACROS

Loops and Decision Making

If Statement

Do Statement

HANDS-ON EXERCISE 5: LOOPS AND DECISION MAKING

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 10: A Professional Application: An Enhanced Schedule

Objectives

Case Study: Retirement Planning

Application Development

The Enhanced Amortization Schedule

Date Functions

HANDS-ON EXERCISE 1: THE ENHANCED AMORTIZATION SCHEDULE WORKBOOK

Exploring VBA Syntax

Three Simple Procedures

HANDS-ON EXERCISE 2: EXPLORING VBA SYNTAX

Event Procedures

User Forms

HANDS-ON EXERCISE 3: EVENT PROCEDURES

More Complex Procedures

HANDS-ON EXERCISE 4: PERIODIC OPTIONAL PAYMENTS

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Chapter 11: Extending VBA: Processing Worksheets and Workbooks

Objectives

Case Study: Bon Voyage Travel

The Expense Summary Application

A Quick Review

The Dir Function

HANDS-ON EXERCISE 1: CREATE THE SUMMARY WORKBOOK

Displaying a Specific Worksheet

Error Trapping

HANDS-ON EXERCISE 2: ERROR TRAPPING

Processing Worksheets in a Workbook

Adding Employees to the Summary Worksheet

HANDS-ON EXERCISE 3: CREATE THE SUMMARY WORKSHEET

A Better Summary Workbook

HANDS-ON EXERCISE 4: A BETTER SUMMARY WORKBOOK

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases

 

Appendix A: Toolbars for Microsoft Office Excel 2003

 

Appendix B: Solver: A Tool for Optimization

 

Appendix C: Using XML with Microsoft Excel 2003

Introduction to XML

HANDS-ON EXERCISE 1: INTRODUCTION TO XML

Microsoft Excel and XML

HANDS-ON EXERCISE 2: EXCEL AND XML

Summary

Key Terms

 

Getting Started with VBA: Extending Microsoft Office 2003

Objectives

Case Study: On-the-Job Training

Introduction to VBA

The MsgBox Statement

The InputBox Function

Declaring Variables

The VBA Editor

HANDS-ON EXERCISE 1: INTRODUCTION TO VBA

If . . . Then . . . Else Statement

Case Statement

Custom Toolbars

HANDS-ON EXERCISE 2: DECISION MAKING

For . . . Next Statement

Do Loops

Debugging

HANDS-ON EXERCISE 3: LOOPS AND DEBUGGING

Putting VBA to Work (Microsoft Excel)

HANDS-ON EXERCISE 4: EVENT-DRIVEN PROGRAMMING (MICROSOFT EXCEL)

Putting VBA to Work (Microsoft Access)

HANDS-ON EXERCISE 5: EVENT-DRIVEN PROGRAMMING (MICROSOFT ACCESS)

Summary

Key Terms

Multiple Choice

 

Getting Started with Microsoft Windows XP

Objectives

Case Study: Unforeseen Circumstances

Welcome to Windows XP

The Desktop

Moving and Sizing a Window

Anatomy of a Window

Pull-down Menus

Dialog Boxes

Help and Support Center

HANDS-ON EXERCISE 1: WELCOME TO WINDOWS XP

Files and Folders

The Exploring Office Practice Files

Connecting to the Internet

HANDS-ON EXERCISE 2: DOWNLOAD THE PRACTICE FILES

Windows Explorer

Personal Folders

Moving and Copying a File

Deleting a File

Backup

HANDS-ON EXERCISE 3: WINDOWS EXPLORER

Increasing Productivity

The Control Panel

Shortcuts

The Search Companion

HANDS-ON EXERCISE 4: INCREASING PRODUCTIVITY

Fun with Windows XP

Windows Media Player

Digital Photography

Windows Messenger

HANDS-ON EXERCISE 5: FUN WITH WINDOWS XP

Summary

Key Terms

Multiple Choice

Practice Exercises

Mini Cases