Exploring Microsoft Excel 2002 Chapter 3
Chapter 3Spreadsheets in Decision Making: What If?
ByRobert T. GrauerMaryann Barber
Exploring Microsoft Excel
Exploring Microsoft Excel 2002 Chapter 3
2
Objectives (1 of 2)
Describe the PMT and FV functions Use the PMT and FV functions Explain how the Goal Seek command can
facilitate decision making Define and use a mixed reference Use the AVERAGE, MAX, MIN, and COUNT
functions
Exploring Microsoft Excel 2002 Chapter 3
3
Objectives ( 2 of 2)
Use the IF and VLOOKUP functions to help in the decision-making process
Freeze and hide rows and columns to work with large worksheets
Print and view large spreadsheets
Exploring Microsoft Excel 2002 Chapter 3
4
Overview
Learn to use spreadsheets as a tool in decision making
Use financial functions Use statistical functions Calculate different results with the Goal
Seek command Use absolute, relative, and mixed cell
references Review the importance of isolating
assumptions and initial conditions in a worksheet
Exploring Microsoft Excel 2002 Chapter 3
5
Analysis of a Car Loan
Can I afford it? How do I calculate
for: rebates down payments interest rates the number of
payments
Exploring Microsoft Excel 2002 Chapter 3
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 Payment amounts and the number of payments
are usually expressed in months, while interest rates are annual rates. Divide the interest rate by 12 to come up with a monthly rate
The amount of the loan (present value) should be expressed as a negative number.
Exploring Microsoft Excel 2002 Chapter 3
7
How Much Money Will I Have at Retirement?
The Future Value (FV) function to return the future value of a series of payments A common example would be contributions you
make to your IRA or 401K plan Use the FV function which requires the
expected rate of return, the number of periods, and the investment each period.
The Moral of the Story: Start planning for your retirement now!! The sports car can wait!!!!
Exploring Microsoft Excel 2002 Chapter 3
8
Inserting a Function
Use the Insert Function command from the Insert menu
Use the list box to select the name of the function functions categorized by function
Let the Wizard help you enter the arguments
Exploring Microsoft Excel 2002 Chapter 3
9
Isolate Your Assumptions
Enter your assumptions (the arguments needed for the function) into cells and use those cells for your arguments For example, in the PMT function, enter the
loan amount, number of payments, and interest rate into cells, then use those cells in the PMT function
Change the values in those cells to test different scenarios Easier than editing the formula when you want
to change on or more of your variables
Exploring Microsoft Excel 2002 Chapter 3
10
Setting up Goal Seek
Establish PMT interest rate term down payment
Use Goal Seek change result by
changing one variable
you can only change one variable
Exploring Microsoft Excel 2002 Chapter 3
11
Hands-On Exercise 1
Objective: to illustrate the PMT and FV functions; to illustrate the Goal Seek command Enter Descriptive Labels Enter PMT function What If? Use the Goal Seek command The FV function Format the worksheet Print the cell formulas
Exploring Microsoft Excel 2002 Chapter 3
12
Getting the most from Excel
Relative versus absolute addressing in a worksheet Knowing the difference and when to use each
when copying makes setting up your worksheet more efficient and more accurate
Mixed references Either the row or the column is absolute; the
other is relative Don’t forget to isolate your assumptions!!
Exploring Microsoft Excel 2002 Chapter 3
13
Hands-On Exercise 2
Objective: To use relative, absolute, and mixed references with the PMT and FV functions The Spell Check The Fill Handle Determine the 30-year payments Determine the 15-year payments Format the worksheet Merge and Center text Enter the row and column headings Create the mixed reference Copy the formula The finishing touches
Exploring Microsoft Excel 2002 Chapter 3
14
Using Functions in Excel
Statistical Functions: MAX,MIN, AVERAGE, COUNT and COUNTA Use functions instead of arithmetic expressions
IF function enhances decision making allows for different results based on different
conditions VLOOKUP(vertical lookup) Function
assigns a value to a cell based on a numeric value in another cell
Exploring Microsoft Excel 2002 Chapter 3
15
The IF function
Allows for different results, based on a condition for example, if you work over forty hours in a
week, you will receive overtime pay Requires three arguments:
a condition, which Excel must be able to evaluate as true or false
a value if true a value if false
The value if true and value if false may contain additional (nested) IF functions for more complex decisions.
Exploring Microsoft Excel 2002 Chapter 3
16
The VLOOKUP function
Allows Excel to look up a value in a table and return a related value for example, Excel can look up your
average in this class and return your grade
Requires three arguments: the numeric value (or cell) to look up the range of the table the column number containing the
value you want to return
Exploring Microsoft Excel 2002 Chapter 3
17
Managing Large Worksheets (1 of 2)
Scrolling causes the screen to move horizontally or vertically as you change the active cell
Freezing Panes allows row and column headings to be seen while scrolling
AutoFill capability enter series into adjacent cells
Hiding rows and columns makes rows and columns invisible on the monitor or when printed
Exploring Microsoft Excel 2002 Chapter 3
18
Managing Large Worksheets (2 of 2)
Page Preview command (View menu) lets you see where the page breaks are
Page Setup command (File menu) lets you change how the sheet prints Change from portrait (8 ½ x 11) to landscape
(11 x 8 ½) Change margins Scale the worksheet to print on one sheet
AutoFilter command lets you display only rows that meet certain criteria
Exploring Microsoft Excel 2002 Chapter 3
19
Hands-On Exercise 3 Objective: to develop the expanded
grade book; to use statistical and logical functions. The Fill Handle Format the Social Security Numbers The Freeze Panes command The IF Function The VLOOKUP function Copy the IF and VLOOKUP functions
Exploring Microsoft Excel 2002 Chapter 3
20
Hands-On Exercise 3 cont’d
Create the summary statistics The Page Break Preview command Hiding rows and columns The AutoFilter command
Exploring Microsoft Excel 2002 Chapter 3
22
Summary (1 of 2)
Financial functions (PMT and FV) Statistical functions (MAX, MIN, AVERAGE,
and COUNT) Decision making functions (IF and VLOOKUP) Use Goal Seek to enhance decision making Isolate and clearly label initial assumptions Copy using fill handle