Exploring Microsoft Excel

1. Exploring Microsoft Excel Chapter 3 Spreadsheets in Decision Making: What If? By Robert T. Grauer Maryann Barber 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

21. Finished Grade Book 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 Exploring Microsoft Excel 2002 Chapter 3

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