Exploring Microsoft Excel Chapter 3 Spreadsheets in Decision

Exploring Microsoft Excel Chapter 3 Spreadsheets in Decision Making: What If? By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 3

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 2

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 3

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 4

Analysis of a Car Loan ¡ ¡ Can I afford it? How do I calculate for: l l rebates down payments interest rates the number of payments Exploring Microsoft Excel 2002 Chapter 3 5

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 l l 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 6

How Much Money Will I Have at Retirement? ¡ The Future Value (FV) function to return the future value of a series of payments l ¡ ¡ A common example would be contributions you make to your IRA or 401 K 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 7

Inserting a Function ¡ ¡ Use the Insert Function command from the Insert menu Use the list box to select the name of the function l ¡ functions categorized by function Let the Wizard help you enter the arguments Exploring Microsoft Excel 2002 Chapter 3 8

Isolate Your Assumptions ¡ Enter your assumptions (the arguments needed for the function) into cells and use those cells for your arguments l ¡ 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 l Easier than editing the formula when you want to change on or more of your variables Exploring Microsoft Excel 2002 Chapter 3 9

Setting up Goal Seek ¡ Establish PMT l l l ¡ interest rate term down payment Use Goal Seek l l change result by changing one variable you can only change one variable Exploring Microsoft Excel 2002 Chapter 3 10

Hands-On Exercise 1 ¡ Objective: to illustrate the PMT and FV functions; to illustrate the Goal Seek command l l l 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 11

Getting the most from Excel ¡ Relative versus absolute addressing in a worksheet l ¡ Mixed references l ¡ Knowing the difference and when to use each when copying makes setting up your worksheet more efficient and more accurate 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 12

Hands-On Exercise 2 ¡ Objective: To use relative, absolute, and mixed references with the PMT and FV functions l l l l l 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 13

Using Functions in Excel ¡ Statistical Functions: MAX, MIN, AVERAGE, COUNT and COUNTA l ¡ IF function enhances decision making l ¡ Use functions instead of arithmetic expressions allows for different results based on different conditions VLOOKUP(vertical lookup) Function l assigns a value to a cell based on a numeric value in another cell Exploring Microsoft Excel 2002 Chapter 3 14

The IF function ¡ Allows for different results, based on a condition l ¡ Requires three arguments: l l l ¡ for example, if you work over forty hours in a week, you will receive overtime pay 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 15

The VLOOKUP function ¡ Allows Excel to look up a value in a table and return a related value l ¡ for example, Excel can look up your average in this class and return your grade Requires three arguments: l l l 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 16

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 Auto. Fill 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 17

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 l l l ¡ Change from portrait (8 ½ x 11) to landscape (11 x 8 ½) Change margins Scale the worksheet to print on one sheet Auto. Filter command lets you display only rows that meet certain criteria Exploring Microsoft Excel 2002 Chapter 3 18

Hands-On Exercise 3 ¡ Objective: to develop the expanded grade book; to use statistical and logical functions. l l l 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 19

Hands-On Exercise 3 l l cont’d Create the summary statistics The Page Break Preview command Hiding rows and columns The Auto. Filter command Exploring Microsoft Excel 2002 Chapter 3 20

Finished Grade Book Exploring Microsoft Excel 2002 Chapter 3 21

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 Exploring Microsoft Excel 2002 Chapter 3 22

Summary ¡ ¡ ¡ (2 of 2) Scroll & Freeze Panes to work with large worksheets Use Page Setup to control how the worksheet prints Use Auto. Filter command to display only rows that meet certain criteria Exploring Microsoft Excel 2002 Chapter 3 23