Exploring Microsoft Excel 97

1
ExploringMicrosoftExcel 97

• Chapter 2
• Gaining Proficiency Copying, Formatting, and
  Isolating Assumptions

CSU, COB, MMIS INSY137 Fall-99.
2
Objectives

• Explain importance of isolating assumptions
• Define, select and deselect cell ranges
• Copy and move cells differentiate between
  relative, absolute and mixed addresses
  
• Format a worksheet
• Change column widths
• Describe steps to create a financial forecast
  worksheet

3
Overview

• Basic commands to create a worksheet
• Use cell ranges and commands to build a
  worksheet
  
• Isolate assumptions to easily evaluate
  alternatives
  
• Improve appearance of worksheets
• Accuracy is critical for spreadsheets

4
Getting around the Worksheet

• A rectangular group of cells is a range
• Copy duplicates contents of a cell from a source
  range to a destination range
  
• Three types of addresses absolute, relative and
  mixed
  
• Move transfer contents of a cell

5
Creating a Workbook

• Hands-On Exercise 1
• Create a New Workbook
• Save the Workbook
• Enter Student Data and Literal Information
• Enter Exam Weights
• Compute the Semester Average
• Copy the Semester Average
• Compute Class Averages
• What If? Change Exam Weights

6
Formatting

• Column widths
• Row Heights
• Numeric Format
• Alignment
• Fonts
• Borders, Patterns, and Shading

7
Numeric Formats

• General
• Number
• Currency
• Accounting
• Date
• Time
• Percentage
• Fraction
• Scientific
• Text
• Special
• Custom

8
Excel 97 Screen

9
Formatting a Worksheet

• Hands-On Exercise 2
• Center Across Columns
• Increase the Width of Column A
• Format the Exam Weights
• Noncontiguous Ranges
• Borders
• Color
• Enter Your Name and Social Security Number
• The Page Setup Command
• Print the Cell Formulas

10
A Financial Forecast

• Always isolate assumptions and initial
  conditions
  
• Creating a financial forecast is a common task
• Be aware that by isolating that is separating
  initial conditions you spreadsheet will be more
  valuable when changes occur

11
A Financial Forecast

• Hands-On Exercise 3
• Enter the Formulas for Year One
• Enter the Formulas for Year Two
• Copy the Formulas to the Remaining Years
• Format the Spreadsheet
• Print the Completed Spreadsheet

12
Chapter 2 Summary

• All commands operate on a cell or cell range
• Formulas may be copied or moved
• Absolute reference remains constant
• Relative address is adjusted for new locations
• Format by select-then-do
• Tool for Decision Making

13
Practice with Excel

• 1. Coaches Realty
• 2. Sales Invoice
• 3. Birthday Probability
• Problem
• 4. Multiplication Table
• 5. Payroll for Employees

14
Case Studies

• Establishing a Budget
• The Entrepreneur
• Break-even Analysis
• The Corporate Balance Sheet
• The Office Assistant
• The Spreadsheet Audit