Spreadsheets in Decision Making: What If?

1
Exploring Microsoft Excel

• Chapter 3
• Spreadsheets in Decision Making What If?
• By
• Robert T. Grauer
• Maryann Barber

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

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

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

5
Analysis of a Car Loan

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

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.

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!!!!

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

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

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

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

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
• Dont forget to isolate your assumptions!!

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

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

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.

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

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

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

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

20
Hands-On Exercise 3 contd

• Create the summary statistics
• The Page Break Preview command
• Hiding rows and columns
• The AutoFilter command

21
Finished Grade Book
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

23
Summary (2 of 2)

• Scroll Freeze Panes to work with large
  worksheets
• Use Page Setup to control how the worksheet
  prints
• Use AutoFilter command to display only rows that
  meet certain criteria