- 148 Views
- Uploaded on
- Presentation posted in: General

Exploring Office 2003 - Grauer and Barber

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

**1. **Exploring Office 2003 - Grauer and Barber 1 Exploring Microsoft Excel 2003

**2. **Exploring Office 2003 - Grauer and Barber 2 Objectives Use the PMT function to calculate the payment of a car loan or mortgage.
Use the FV function to determine the future value of a retirement account
Explain how the Goal Seek command facilitates the decision-making process
Use mixed references to vary two parameters in a table
Use the AVERAGE, MAX, MIN, and COUNT functions

**3. **Exploring Office 2003 - Grauer and Barber 3 Objectives (continued) Use the IF and VLOOKUP functions to implement decision making
Freeze, unfreeze, hide, and unhide, rows and columns in a worksheet
Use the AutoFilter command to display selected records in a list
Describe the options in the Page Setup command used with large worksheets

**4. **Exploring Office 2003 - Grauer and Barber 4 Case Study: Vacation Time In this case study, students are asked to complete a worksheet that was started by someone else. The worksheet contains information about employees. They need to use date arithmetic to calculate how long an employee has been working here, use the VLOOKUP and IF functions to make determinations on how much vacation time each employee is entitled to and if he or she has any time left. After using these functions, students create some simple statistics, using built-in statistical functions.

**5. **Exploring Office 2003 - Grauer and Barber 5 Using Functions Function – a predefined computational task
Requires arguments
Values the function uses to calculate answers
Returns a value

**6. **Exploring Office 2003 - Grauer and Barber 6 The PMT Function Calculates a periodic payment, such as a car or mortgage payment
Based on:
Amount financed
Interest rate
Number of periods

**7. **Exploring Office 2003 - Grauer and Barber 7 Using the PMT function With the payment function, we have to make sure all arguments are expressed in the same time interval. We normally express interest rates and the length of the loan in years, while we calculate a monthly payment. Since the interest rate is expressed in years and we want the interest rate per period, divide the interest rate by 12. Since the repayment period is expressed in years and we make 12 payments per year, we need to multiply the number of years by 12.
In order for the payment amount to be positive, the amount of the loan must be expressed as a negative. Since we don’t want cell B4 to read -$9,999, we place the negative operator before the cell reference to B4 in the formula. You could also put the negative operator right after the equal sign.
If you have students who are business majors, this is no different than any time value of money calculation (present value, NPV, IRR, etc.)With the payment function, we have to make sure all arguments are expressed in the same time interval. We normally express interest rates and the length of the loan in years, while we calculate a monthly payment. Since the interest rate is expressed in years and we want the interest rate per period, divide the interest rate by 12. Since the repayment period is expressed in years and we make 12 payments per year, we need to multiply the number of years by 12.
In order for the payment amount to be positive, the amount of the loan must be expressed as a negative. Since we don’t want cell B4 to read -$9,999, we place the negative operator before the cell reference to B4 in the formula. You could also put the negative operator right after the equal sign.
If you have students who are business majors, this is no different than any time value of money calculation (present value, NPV, IRR, etc.)

**8. **Exploring Office 2003 - Grauer and Barber 8 The FV function Returns the future value of a series of payments
For example, contributions to your 401K or IRA
Based on:
Number of periods
Expected rate of return
Amount invested each period

**9. **Exploring Office 2003 - Grauer and Barber 9 Using the FV Function Unlike the PMT function, in this case, all three assumptions were measured in years, so there was no need to express all the terms in months. If you wanted to illustrate the power of compounding, show the value of making a $250 contribution each month rather than a $3,000 contribution each year. Over the same 40 years, with the same 7% return, the future value would be $656,203.35. While that may not have anything to do with Excel, it is a valuable lesson for the students to learn now.Unlike the PMT function, in this case, all three assumptions were measured in years, so there was no need to express all the terms in months. If you wanted to illustrate the power of compounding, show the value of making a $250 contribution each month rather than a $3,000 contribution each year. Over the same 40 years, with the same 7% return, the future value would be $656,203.35. While that may not have anything to do with Excel, it is a valuable lesson for the students to learn now.

**10. **Exploring Office 2003 - Grauer and Barber 10 Inserting a Function Use the Insert Function command from the Insert menu
Use the list box to select the name of the function
Functions are categorized
Let the Wizard help you enter the arguments
Point to enter cell references
Use the Collapse button to collapse the dialog box

**11. **Exploring Office 2003 - Grauer and Barber 11 The Function Wizard Notice the formula bar as each argument is entered. As arguments are changed, the value that will be returned by the function changes. Have students examine that value and perform a reasonableness check before clicking the OK button.Notice the formula bar as each argument is entered. As arguments are changed, the value that will be returned by the function changes. Have students examine that value and perform a reasonableness check before clicking the OK button.

**12. **Exploring Office 2003 - Grauer and Barber 12 The Goal Seek Command Allows you to set an end result and vary the inputs (assumptions) to produce that result
Only one input can be varied at a time
All other assumptions remain constant
For example, set a desired monthly car payment
Vary the amount financed
Interest rate and number of months remain the same Goal Seek can be a useful tool when one of our input parameters is inflexible. In the example given, we can’t afford more than a $200 monthly payment, so this command can be used to determine the most we can finance. Since the amount financed is contingent upon either the price of the car, the down payment, or the amount of the rebate, we will use Goal Seek to set one of those variables.Goal Seek can be a useful tool when one of our input parameters is inflexible. In the example given, we can’t afford more than a $200 monthly payment, so this command can be used to determine the most we can finance. Since the amount financed is contingent upon either the price of the car, the down payment, or the amount of the rebate, we will use Goal Seek to set one of those variables.

**13. **Exploring Office 2003 - Grauer and Barber 13 Using the Goal Seek Command In this case, we have a maximum monthly payment of $200. We can change any one of the assumptions. For the Goal Seek command to be usable, focus on the assumptions you can realistically change. Students aren’t likely to get one of those 0% financing deals or a seven or eight year repayment period and they probably can’t negotiate the amount of the manufacturer’s rebate, so they need to be willing and able to change the purchase price of the car or the down payment.In this case, we have a maximum monthly payment of $200. We can change any one of the assumptions. For the Goal Seek command to be usable, focus on the assumptions you can realistically change. Students aren’t likely to get one of those 0% financing deals or a seven or eight year repayment period and they probably can’t negotiate the amount of the manufacturer’s rebate, so they need to be willing and able to change the purchase price of the car or the down payment.

**14. **Exploring Office 2003 - Grauer and Barber 14 Hands-on Exercise 1 Title of Exercise: Basic Financial Functions
Objective: To illustrate the PMT and FV functions; to illustrate the Goal Seek command.
Input file: None
Output file: Basic Financial Functions

**15. **Exploring Office 2003 - Grauer and Barber 15 Developing Proficiency Use relative and absolute references correctly
Use relative cell references if the value will change when a cell is copied
Use absolute references if the value remains constant (typically assumptions)
Mixed references
Use when either the row or the column will change
Isolate your assumptions
Formulas in cells refer to the assumptions area, not to the actual values This is a review of material covered in chapters 1 and 2.This is a review of material covered in chapters 1 and 2.

**16. **Exploring Office 2003 - Grauer and Barber 16 Using Mixed References Mixed references are most often used to create a table, where one value in a formula remains the same as the formula is copied over a column, but changes as the formula is copied down a row, and the other value in a formula does just the opposite.
In the example above, when the formula in cell C6 is copied to other columns, the number of payments will always come from column B, so that column reference needs to be absolute. When it is copied to other rows, the number of payments will come from the row the formula is being copied to, so the row reference needs to be relative.
The reference for the expected rate of return is just the opposite. As the formula in cell C6 is copied to other columns, the expected rate of return will reflect the column the cell is being copied to, so the column reference needs to be relative. As the cell is copied to other rows, the rate of return will always come from row 5, so the row reference needs to be absolute.Mixed references are most often used to create a table, where one value in a formula remains the same as the formula is copied over a column, but changes as the formula is copied down a row, and the other value in a formula does just the opposite.
In the example above, when the formula in cell C6 is copied to other columns, the number of payments will always come from column B, so that column reference needs to be absolute. When it is copied to other rows, the number of payments will come from the row the formula is being copied to, so the row reference needs to be relative.
The reference for the expected rate of return is just the opposite. As the formula in cell C6 is copied to other columns, the expected rate of return will reflect the column the cell is being copied to, so the column reference needs to be relative. As the cell is copied to other rows, the rate of return will always come from row 5, so the row reference needs to be absolute.

**17. **Exploring Office 2003 - Grauer and Barber 17 Hands-on Exercise 2 Title of Exercise: Advanced Financial Functions
Objective: To use relative, absolute, and mixed references in conjunction with the PMT and FV functions; to practice various formatting commands.
Input file: None
Output file: Advanced Financial Functions

**18. **Exploring Office 2003 - Grauer and Barber 18 Statistical Functions MAX, MIN, and AVERAGE functions
Return highest, lowest, and average values from an argument list
Argument list may include cell references, cell ranges, values, functions, or formulas
Cells that are empty or contain text are not included
COUNT and COUNTA functions
COUNT returns number of cells containing numeric entries or formulas that return a number
COUNTA also includes cells with text It is important to note that as far as Excel is concerned, a 0 is not a blank cell, so the 0 will be included in the MAX, MIN, and AVERAGE functions. If you had two ranges of four cells, one of which contained 100, 100, 100, and a blank space and the other of which contained 100, 100, 100, and 0, the AVERAGE function would return 100 for the first range and 75 for the second. This sometimes causes havoc with students and with professors who use Excel for their grade books!It is important to note that as far as Excel is concerned, a 0 is not a blank cell, so the 0 will be included in the MAX, MIN, and AVERAGE functions. If you had two ranges of four cells, one of which contained 100, 100, 100, and a blank space and the other of which contained 100, 100, 100, and 0, the AVERAGE function would return 100 for the first range and 75 for the second. This sometimes causes havoc with students and with professors who use Excel for their grade books!

**19. **Exploring Office 2003 - Grauer and Barber 19 Using Functions versus Formulas In general, use functions instead of formulas
Functions are adjusted as rows or columns are deleted or added within the range referenced by the function
With formulas
Adding a row adjusts the cell references in the formula, but does not include the new row in the formula
Deleting a row causes a #REF error message Study Figure 4.11 in the text, which compares the effects of inserting and deleting rows with functions as opposed to formulas. When students see the #REF error message, they typically think something is wrong with the computer.Study Figure 4.11 in the text, which compares the effects of inserting and deleting rows with functions as opposed to formulas. When students see the #REF error message, they typically think something is wrong with the computer.

**20. **Exploring Office 2003 - Grauer and Barber 20 The IF Function Enables decision making in a worksheet
Requires three arguments:
A condition
A value if the condition is true
A value if the condition is false
Condition must be able to be evaluated as true or false
Uses relational operators (=, <, etc.)
Students usually do pretty well with the conditional test. The most common mistake they make is entering another conditional test in the value-if-true or value-if-false argument, which returns either true or false rather than the value and often causes a circular reference.Students usually do pretty well with the conditional test. The most common mistake they make is entering another conditional test in the value-if-true or value-if-false argument, which returns either true or false rather than the value and often causes a circular reference.

**21. **Exploring Office 2003 - Grauer and Barber 21 Using the IF Function Incorrectly In this example, I have entered the value_if_true and value_if_false as conditional tests. This seems perfectly logical because, in my mind, I am saying that if the condition is true, then cell F4 will be equal to 40 and if it is false, it will be equal to the value in cell B11. The presence of the relational operator (the equal sign) causes Excel to return either True or False, rather than the value.In this example, I have entered the value_if_true and value_if_false as conditional tests. This seems perfectly logical because, in my mind, I am saying that if the condition is true, then cell F4 will be equal to 40 and if it is false, it will be equal to the value in cell B11. The presence of the relational operator (the equal sign) causes Excel to return either True or False, rather than the value.

**22. **Exploring Office 2003 - Grauer and Barber 22 Using the IF Function Correctly As illustrated here, the value can be either a numeric value or a cell reference. If you were in a union environment or thought that federal employment law regarding overtime might change, you could include the overtime threshold as an assumption and refer to that cell in the formula.
For values that are extremely static, such as an overtime threshold, it is more efficient, in terms of using computer resources, to enter the value as a value rather than a reference. As illustrated here, the value can be either a numeric value or a cell reference. If you were in a union environment or thought that federal employment law regarding overtime might change, you could include the overtime threshold as an assumption and refer to that cell in the formula.
For values that are extremely static, such as an overtime threshold, it is more efficient, in terms of using computer resources, to enter the value as a value rather than a reference.

**23. **Exploring Office 2003 - Grauer and Barber 23 The VLOOKUP function Allows Excel to look up a value in a table and return a related value
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
The function will look for the value in the first column of the table array, so when students are defining the table, they need to make sure the column containing the breakpoints is the first column in the table.
If the cell containing the VLOOKUP function is going to be copied, the table array needs to be an absolute reference. It might be helpful to teach the students how to define a name for the table. If they name the table and use that name in the function, it will automatically be an absolute reference.
The function will look for the value in the first column of the table array, so when students are defining the table, they need to make sure the column containing the breakpoints is the first column in the table.
If the cell containing the VLOOKUP function is going to be copied, the table array needs to be an absolute reference. It might be helpful to teach the students how to define a name for the table. If they name the table and use that name in the function, it will automatically be an absolute reference.

**24. **Exploring Office 2003 - Grauer and Barber 24 Using the VLOOKUP Function A common mistake is to enter the actual column containing the result as the col_index_num, rather than the index number. For example, they would enter J, since the grades are found in column J. The function looks at the column’s position within the table array, rather than the absolute column reference.
If any of your students have had a programming class, they are accustomed to arrays beginning with 0. In this case, the array begins with 1; that is, the column containing the lookup values is 1, not 0.A common mistake is to enter the actual column containing the result as the col_index_num, rather than the index number. For example, they would enter J, since the grades are found in column J. The function looks at the column’s position within the table array, rather than the absolute column reference.
If any of your students have had a programming class, they are accustomed to arrays beginning with 0. In this case, the array begins with 1; that is, the column containing the lookup values is 1, not 0.

**25. **Exploring Office 2003 - Grauer and Barber 25 Working With Large Worksheets Scrolling causes the screen to move horizontally or vertically as you change the active cell
Drag the horizontal or vertical scroll bars
Click above or below vertical scroll bars
Click to the left or right of horizontal scroll bars
Freezing Panes allows row and column headings to remain visible while scrolling
Hiding rows and columns makes rows and columns invisible on the monitor or when printed There are several ways to scroll. In addition to the ways shown here, the mouse typically has some “intelligent” scrolling features.
Freezing panes is helpful with large worksheets because it allows you to keep column headings or row headings visible. There are several ways to scroll. In addition to the ways shown here, the mouse typically has some “intelligent” scrolling features.
Freezing panes is helpful with large worksheets because it allows you to keep column headings or row headings visible.

**26. **Exploring Office 2003 - Grauer and Barber 26 Freezing Panes Freezing panes keeps you from entering a score in the wrong column or giving the grade to the wrong student. Freezing panes has no effect when the worksheet is printed.
Hidden rows and columns, on the other hand, are hidden when the worksheet is printed, as well as when the worksheet is viewed on a monitor.Freezing panes keeps you from entering a score in the wrong column or giving the grade to the wrong student. Freezing panes has no effect when the worksheet is printed.
Hidden rows and columns, on the other hand, are hidden when the worksheet is printed, as well as when the worksheet is viewed on a monitor.

**27. **Exploring Office 2003 - Grauer and Barber 27 Printing Large Worksheets 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

**28. **Exploring Office 2003 - Grauer and Barber 28 The AutoFilter Command Allows you to display a selected set of rows within a worksheet
Displays rows that meet selected criteria
Other rows are hidden, not deleted
Select Filter then AutoFilter from the Data menu
Select criteria from the dropdown This is one of the database features found in Excel. It is extremely useful. In a business environment it is used on a regular basis by those who know the feature.This is one of the database features found in Excel. It is extremely useful. In a business environment it is used on a regular basis by those who know the feature.

**29. **Exploring Office 2003 - Grauer and Barber 29 Using the AutoFilter Command If the column headings are in row 1 or 2 of a spreadsheet, the AutoFilter command will find them automatically. You can click anywhere in the data, choose Data, Filter, AutoFilter – and the drop down boxes will fall into place.If the column headings are in row 1 or 2 of a spreadsheet, the AutoFilter command will find them automatically. You can click anywhere in the data, choose Data, Filter, AutoFilter – and the drop down boxes will fall into place.

**30. **Exploring Office 2003 - Grauer and Barber 30 Hands-on Exercise 3 Title of Exercise: The Expanded Grade Book
Objective: To develop the expanded grade book; to use statistical (AVERAGE, MAX, and MIN) and logical (IF and VLOOKUP functions); to demonstrate scrolling and the Freeze Panes command
Input file: Expanded Grade Book
Output file: Expanded Grade Book Solution

**31. **Exploring Office 2003 - Grauer and Barber 31 Summary Financial functions (PMT and FV)
Goal Seek enhances decision making
Statistical functions (MAX, MIN, AVERAGE, COUNT, and COUNTA)
Decision making functions (IF, VLOOKUP, and HLOOKUP)
Isolate and clearly label initial assumptions

**32. **Exploring Office 2003 - Grauer and Barber 32 Summary (continued) Copy using fill handle
Use scrolling & the Freeze Panes command to work with large worksheets
Page Setup controls how the worksheet prints
AutoFilter command displays only rows that meet certain criteria

**33. **Exploring Office 2003 - Grauer and Barber 33 End-of-chapter Exercises Multiple Choice
Practice Exercises
Exercise 1 – Calculating Your Retirement
Exercise 2 – Alternate Grade Book
Exercise 3 – Expanded Payroll
Exercise 4 – Fuel Estimates
Exercise 5 – The Roth IRA
Exercise 6 – Celebrity Birthdays
Exercise 7 – The Health Club
Exercise 8 – File Formats and Folders

**34. **Exploring Office 2003 - Grauer and Barber 34 End-of-Chapter Exercises (continued) Practice Exercises
Exercise 9 – Nested Ifs and Other Functions
Exercise 10 – Election 2000
Mini Cases
The Financial Consultant
Fun with the If Statement
The Lottery
A Penny a Day
The Rule of 72

**35. **Exploring Office 2003 - Grauer and Barber 35 Questions?