Exploring Microsoft Office 2000 Exploring Excel Chapter 3

1
Exploring Excel
Chapter 3 Spreadsheets in Decision Making What
If? By Robert T. Grauer Maryann Barber
2
Objectives (1 of 2)

• Use spreadsheets in decision making use Goal
  Seek and Scenario Manager
• Use PMT function
• Use Paste Function
• Use fill handle and AutoFill capability
• Use pointing to create a formula

3
Objectives ( 2 of 2)

• Use AVERAGE, MAX, MIN, and COUNT functions in a
  worksheet
• Use the IF function to implement a decision and
  explain how the VLOOKUP function is used
• Print and view large spreadsheets

4
Overview

• Learn to use spreadsheets as a tool in decision
  making
• Use financial and statistical functions
• Calculate different results with the Goal Seek
  command
• Choose between multiple sets of assumptions with
  Scenario Manager
• Use relative and absolute cell references

5
Analysis of a Car Loan

• Can I afford it?
• How do I calculate for
• rebates
• down payments
• interest rates
• years of loan

6
Analysis of a Car Loan

• Set up a worksheet template with initial
  conditions
• Use a PMT function which requires interest
  rate/period, number of periods, and amount of
  loan
• Use Goal Seek to set an end result

7
Hands-On Exercise 1

• Enter Descriptive Labels
• Enter PMT function
• What If?
• The Goal Seek Command
• The Goal Seek Command continued

8
Setting up Goal Seek

• Establish PMT
• interest rate
• term
• down payment
• Use Goal Seek
• change result changing one variable

9
Getting the most from Excel

• Relative versus Absolute addressing in a
  worksheet
• Using the fill handle to copy
• Pointing to cell address for formulas or
  functions is more accurate
• Using the Paste Function and the Formula Palette

10
Hands-On Exercise 2

• Enter the Descriptive Labels and Initial
  Conditions
• The Spell Check
• The Fill Handle
• Determine the 30-Year Payments
• The Formula Palette
• Copy the 15-Year Payments
• Compute the Monthly Difference (Pointing)
• The Finishing Touches
• Print the Worksheet

11
Variable Mortgage Rates
12
Using Functions in Excel

• Statistical Functions--MAX,MIN, AVERAGE, COUNT
  and COUNTA
• Use functions over arithmetic expressions
• IF function enhances decision making
• VLOOKUP(vertical lookup) Function and its use

13
Managing Large Worksheets

• Scrolling shows specific rows and columns
• Freezing Panes allows headings to be seen while
  scrolling
• AutoFill capability enter series into adjacent
  cells
• Scenario Manager enables evaluation of multiple
  conditions

14
Hands-On Exercise 3

• Open the Expanded Grade Book
• The AutoFill Capability
• Format the Social Security Numbers
• Scrolling and Freezing Panes
• The IF Function
• The VLOOKUP Function
• Copy the IF and VLOOKUP Functions

15
Hands-On Exercise 3 contd

• Statistical Functions
• Shortcut Menu
• Create the No Curve Scenario
• Add the Curve Scenario
• View the Scenarios
• Print the Worksheet

16
Finished Grade Book
17
Summary (1 of 2)

• Financial functions (PMT)
• Statistical functions (MAX, MIN, AVERAGE, and
  COUNT)
• Decision making functions (IF and VLOOKUP)
• Copy using fill handle
• Point to build a formula

18
Summary (2 of 2)

• Scroll Freeze Panes to view large spreadsheet
• Tool for Decision Making using the Goal Seek and
  Scenario Manager
• Isolate initial assumptions
• Print worksheet with values and formulas

19
Practice With Excel

• Startup Airlines
• Hot Spot Software
• Distributors
• Object Linking and
• Embedding
• Grouping and Outlines
• Amortization Table
• Compound Document
• Mortgage Calculator
• Professors Template

20
Case Studies

• The Financial Consultant
• Compensation Analysis
• The Automobile Dealership
• The Lottery
• A Penny a Day
• Data Tables
• The Power of Compound Interest