Spreadsheets in Decision Making: What If? - PowerPoint PPT Presentation

About This Presentation
Title:

Spreadsheets in Decision Making: What If?

Description:

Exploring Microsoft Excel Chapter 3 Spreadsheets in Decision Making: What If? By Robert T. Grauer Maryann Barber Describe the PMT and FV functions Use the PMT and FV ... – PowerPoint PPT presentation

Number of Views:126
Avg rating:3.0/5.0
Slides: 24
Provided by: Rober1302
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com