Microsoft Office 2003

EX 266: Introduction

LECTURE NOTES

Explain that this chapter builds on previous chapters with regard to what-if analysis and printing methods
Introduce other highlights to be covered such as cell protections, hiding rows, columns, sheets, and workbooks; and formula checking
Point out the importance of Excel’s repertoire of functions and its capability to organize answers to what-if questions
Introduce the PMT function and the PV function

CLASSROOM ACTIVITIES

1. Group Activity: Ask students to list, describe, and give examples of functions with which they already are familiar.

2. Assign a Project: Ask students to read through the Introduction and make a list of items they are familiar with in the world of finance, such as amortization or loans.

EX 266: Project — Loan Payment Calculator with Data Table and Amortization Schedule

LECTURE NOTES

Review the worksheet to be created in the chapter using Figures 4-1 and 4-2

· Review the requirements document and the sketch and emphasize their importance using Figures 4-2 and 4-3

· Point out that the figures in this book require a resolution of 1024 x 768, and refer students to Appendix E for more information about how to change the resolution on their computers

Review the steps to start and customize Excel
Review the steps to bold the entire worksheet
Review the steps to work with titles, dates, and properties using Figure 4-4

FIGURES and TABLES: Figures — 4-1, 4-2, 4-3, 4-4

BOXES:

1. BTW: Good Worksheet Design. Review the ways to ensure good design for the life a worksheet.

2. BTW: Multiple Worksheets. Explain that the taskbar indicates when multiple instances of Excel are running and how to change the taskbar behavior.

3. BTW: Global Formatting. Review how to assign formats.

4. BTW: Concatenation. Review objects that can be concatenated and the ampersand operator character.

5. BTW: Shortcut Menus. Explain how the shortcut menu applies to a specific screen object.

TEACHER TIPS

Loan applicants today are rejected or accepted within minutes. To stay competitive, all lending institutions must take advantage of every tool and technology available to them. This includes worksheet designs that contain powerful financial calculations.

CLASSROOM ACTIVITIES

1. Critical Thinking: Numerous online lending institutions are available today. Anybody who spends much time online has been bombarded with offers of quick loans. What experiences have you had with online lending institutions, from reading a pop-up advertisement to actually applying for (or maybe even receiving) a loan? Do you find them useful and if so, in what ways? Could their function be improved? What do you find annoying about them, if anything?

2. Group Activity: What are the three basic sections of the workbook to be created? (Answer: The Loan Payment Calculator, the Interest Rate Schedule, the Amortization Schedule)

EX 269: Plan Ahead Box (Critical Thinking): General Project Decisions

LECTURE NOTES

Review the guidelines and decisions to make before creating the worksheet
Explain that the worksheet uses complex financial calculations to solve loan problems that arise between the lender and customer
Emphasize the importance of protecting cells in the worksheet from manipulation by anyone but the worksheet designer

· Use Figure 4-3 to review the planned worksheet design

CLASSROOM ACTIVITIES

1. Group Activity: Divide the class into small groups. Have students review the Plan Ahead Box while referring to the requirements document in Figure 4-2. Then, have them close their books and create a sketch for the worksheets.

2. Quick Quiz:

1) What are the two pieces of information necessary or useful before creating a workbook? (Answer: Requirements document (or request for work), sketch)

EX 272: Adding Custom Borders and a Background Color to a Range

LECTURE NOTES

· Use Figure 4-5 to describe the outlines applied to sections of the worksheet

· Use Figure 4-6 to show the Format Cells dialog box for controlling color and thickness

Review adding custom borders and a background color to a range using Figures 4-5 through 4-8
Review how to format cells before entering values
Use Figure 4-2 to identify the loan data to be entered and then review the steps for entering loan data
Use Figure 4-9 to describe how to align text within a worksheet

FIGURES and TABLES: Figures — 4-5, 4-6, 4-7, 4-8, 4-9

BOXES:

1. BTW: When to Format. Review the four opportunities for formatting and when specialists do this.

2. BTW: Entering Percents. Explain how Excel translates values entered in a cell into percents.

3. BTW: Managing Range Names. Explain how to work with ranges that have names.

4. Other Ways: Encourage other ways to add borders.

TEACHER TIPS

Remind students that making a font style change to a worksheet that does not yet contain text makes no visible change to the worksheet. Not to worry — as text and numbers are entered into the cells of the worksheet, the applied font style will be used.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) True or False: If the border type is chosen before the line style or color is chosen, the border type will not reflect the selected line style and color. (Answer: True. To prevent this, select the color first, then the border line style, and then the border type.)

LAB ACTIVITIES

1. Have students experiment with entering numbers into cells that are formatted as percentages; have them report (in a one-page paper) their findings and list the steps they took to succeed in having the numbers format correctly.

EX 276: Creating Cell Names Based on Row Titles

LECTURE NOTES

· Review the usefulness of the Name Manager to help add clarity to formulas by allowing names to be assigned to cells

· Review the use of row titles and column titles within formulas in order to refer to the related data by name

· Review the additional guidelines for naming cells

Use Figures 4-10 through 4-12 to illustrate how to create and use cell names
Use Figures 4-13 and 4-14 to use names for the loan amount formula
Describe the PMT function, its three arguments, its form, and how to enter it in the worksheet using Figures 4-15 and 4-16
Use Table 4-1 to summarize other financial functions and their descriptions
Use Figures 4-17 and 4-18 to review entering data, formulas, and how Excel calculates the data

FIGURES and TABLES: Figures — 4-10, 4-11, 4-12a, 4-12b, 4-13, 4-14, 4-15, 4-16, 4-17, 4-18; Table — 4-1

BOXES:

1. BTW: Cell References in Formulas. Review the Name Manager for managing cell references.

2. BTW: Entering Interest Rates. Explain the alternate way to enter interest rate without an appended percent.

3. BTW: Range Finder. Review how to use Range Finder to check formulas.

4. BTW: Testing a Worksheet. Explain that good practice dictates testing worksheet formulas.

5. Other Ways: Emphasize other ways to create names.

6. Other Ways: Encourage students to explore other ways of inserting a PMT function.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) A cell name can be a maximum of how many characters? (Answer: 255 characters)

2) True or False: A name assigned to a cell or cell range on one worksheet in a workbook can be used on other sheets in the same workbook to reference the named cell or range. (Answer: True. Names are global to the workbook.)

2. Group Activity: By giving a cell a name, it can be referenced by that name in formulas. Ask students to brainstorm when it might be good to name a cell. Be sure that the following situations are included: the cell is used in a formula, especially when the cell is used in several formulas; the cell frequently must be selected and updated.

EX 284: Using a Data Table to Analyze Worksheet Data

LECTURE NOTES

Use Figures 4-19 and 4-20 to explain the types of data tables and what an input value is

· Use Figures 4-20 and 4-21 to describe how to enter titles in the data table section and column

Use Figures 4-22 and 4-23 to review using the fill handle to create a series that will serve as input data for the data table
Use Figures 4-24 through 4-27 to describe the steps for entering formulas in the data table and defining a range for the data table
Emphasize the important aspects about data tables
Describe how to format the data table by referring to Figure 4-28

FIGURES and TABLES: Figures — 4-19,4-20, 4-21, 4-22, 4-23, 4-24, 4-25, 4-26, 4-27, 4-28

BOXES:

1. BTW: Data Tables. Explain the purpose is to organize answers to what-if questions.

2. BTW: Selecting Cells. Describe the ways to make a cell active.

3. BTW: Expanding Tables. Describe the ways to create a large data table.

4. BTW: Formulas in Data Tables. Describe how experienced users enter formulas and why.

5. BTW: Undoing Formats. Review how to undo a format if it is a mistake.

6. Other Ways: Encourage other ways to create a series.

TEACHER TIPS

If students delete a name that was used in a formula, the cell(s) containing the formula will display the #NAME? error message. To remedy the problem, click the cell containing the error message and replace the name in the formula with the cell reference of the cell whose name was deleted.

In a one-input data table, the cell immediately to the left of the formulas should not include an input value.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) How many active data tables can you have in a worksheet? (Answer: As many as you want)

2) When you assign a formula to a cell, Excel applies the format of which cell reference in the formula to the cell? (Answer: The format of the first cell reference in the formula is applied to the cell.)

2. Assign a Project: Use the Web to research sites that describe analyzing worksheet data. Report your findings in a 2–3 page report about the various ways in which the topic is covered. Which did you find most useful and why? Which did you find most interesting and why? Provide references to your sources of information.

EX 291: Adding a Pointer to the Data Table Using Conditional Formatting

LECTURE NOTES

Describe how to add a pointer to the data table by referring to Figures 4-29 through 4-32
Explain how to add conditional formatting to the table by using the Conditional Formatting menu

FIGURES and TABLES: Figures — 4-29, 4-30, 4-31, 4-32

BOXES:

1. BTW: Conditional Formatting. Describe the guidelines and pointers for using formatting conditions.

2. Other Ways: Describe alternate way to add a pointer.

CLASSROOM ACTIVITIES

1. Critical Thinking: Although a range can have an unlimited number of conditional formats, ask the students to think about the consequences, if any, that might result if too many conditions are present. Would the worksheet creator have difficulty using an unlimited number? In what situations would you find a need for many conditions?

2. Group Activity: Brainstorm with the class for different uses of conditional formatting. For example, using it to distinguish between blanks and zeroes, and sorting or filtering.

EX 294: Creating an Amortization Schedule

LECTURE NOTES

Use Figure 4-33 to describe an amortization schedule

· Summarize the steps for creating the amortization schedule: Adjust column widths and enter titles; create a number series to represent the loan’s lifetime; enter formulas; copy the formulas to other rows; determine totals; format; and generate new loan information

· Use Figure 4-34 to explain adjusting column widths and adding titles to the section and columns

· Use Figure 4-35 to describe using the fill handle to create the number series to represent the life of the loan

· Use Table 4-2 to review the formulas for the amortization schedule

· Describe Excel’s PV function, its arguments, its form, and how it will be used to calculate the annuity, as shown in Figure 4-36

· Use Figures 4-36 through 4-39 to demonstrate adding the formulas from Table 4-2 to the first row of the schedule

· Use Figures 4-40 through 4-42 to describe copying the formulas to the remaining rows

· Use Figure 4-43 to explain how to enter the beginning balances

· Describe how to enter the total formulas

· Use Figures 4-44 and 4-45 to explain formatting the numbers and adding borders and background

· Explain how to enter new and original loan data using Figure 4-46

FIGURES and TABLES: Figure — 4-33, 4-34, 4-35, 4-36, 4-37, 4-38, 4-39, 4-40, 4-41, 4-42, 4-43, 4-44, 4-45, 4-46; Table — 4-2

BOXES:

1. BTW: Amortization Schedules. Discuss where to find amortization schedules.

2. BTW: Column Borders. Describe the purpose of the borders in the worksheet.

3. BTW: The Magical Fill Handle. Describe the usefulness and efficiency of the fill handle feature.

4. BTW: Round-off Errors. Explain how the error occurs and how to prevent it with the ROUND function.

TEACHER TIPS

Liquidating a debt by installment payments is referred to as amortizing. A portion of every loan payment is applied to both the interest and the principal balance of the loan. The amount applied to each changes over time. Early in the loan, a higher amount of the payment goes toward interest. As the loan matures, larger portions go towards paying down the principal (paying off the loan). An amortization schedule shows the specific dollar amount applied to interest and applied to principal balance with each payment.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) What Excel function can prevent round-off errors? (Answer: The ROUND function)

2. Project to Assign: The Web offers many online calculators that can solve many types of interest and payment situations through an amortization schedule. Research two or three of these online amortization schedule calculators and compare them to the spreadsheet in this lesson. Write a one or two page paper describing the similarities and differences among the calculators and the similarities and differences between them and the Excel worksheet. If you find features you like about the online calculators, mention those here.

EX 307: Printing Sections of the Worksheet

LECTURE NOTES

· Remind students how they printed a section of a worksheet in a previous lesson but how a specific range can be printed with the Set Print Area command

· Use Figures 4-47 and 4-48 and Table 4-4 to describe the print options on the Page Setup dialog box

Use Figures 4-49 and 4-50 to explain how to set the print area to print a section of the worksheet
Use Figures 4-51 through 4-54 to explain how to set up names for sections of the worksheet so the names can be used to identify what sections to print rather than defining the range each time it has to be printed

FIGURES and TABLES: Figures — 4-47, 4-48, 4-49, 4-50, 4-51, 4-52, 4-53, 4-54; Table — 4-3

BOXES:

1. Other Ways: Mention another way to set up the print area.

2. Other Ways: Emphasize another way to name and print a section of the worksheet.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) After a print area is set, what command from the Print Area menu can be used to remove the setting so that the entire worksheet prints? (Answer: Clear Print Area on the Print Area menu.)

2) True or False: The gridlines of the worksheet cannot be printed. (Answer: False. A check mark in the Gridlines check box instructs Excel to print gridlines.)

LAB ACTIVITIES

1. Have students use the current worksheet to practice with the various Page Setup options and then print the worksheet based on the selected options. First, ask them to use the options on the Page tab, start with Portrait and then make other adjustments on the Page tab. Second, ask them select Landscape and adjust the other settings. Third, ask them to experiment with the settings on the Sheet tab. Ask them to bring the results to class to compare output with the class. They should bring as few as 3 and no more than 6 printed sheets to class. Students may refer to Table 4-4 to help them with the print option choices.

EX 313: Protecting the Worksheet

LECTURE NOTES

· Define unprotected cells (sometimes called unlocked cells) and protected cells (sometimes called locked cells)

· Explain the two-step process for protecting a worksheet and why the process must be performed in the order indicated

· Use Figures 4-55 through 4-58 to describe why some cells must remain protected from users and how to protect the worksheet

· Use Figure 4-58 to explain the Protect Sheet dialog box for creating a password

· Review passwords, protecting the worksheet and workbook for production

· Use Figure 4-59 to show what happens when a user tries to access a protected area

· Use Figures 4-60 through 4-63 to explain hiding or unhiding a worksheet and workbook and the purpose for doing so

FIGURES and TABLES: Figures — 4-55, 4-56, 4-57, 4-58, 4-59, 4-60, 4-61, 4-62, 4-63

BOXES:

1. BTW: Naming Ranges. Describe how to name two or more ranges.

2. BTW: Using Protected Worksheets. Describe how to move from one unprotected cell to another and why it is useful.

3. Other Ways: Encourage students to use another command to protect the worksheet.

TEACHER TIPS

Although good passwords are obscure, it is important that they be memorable. If the password is forgotten, no one (including the worksheet author) can remove the protection. Some experts believe that forgotten passwords cause more problems than unauthorized changes.

If students try to hide, say, a row, and find that after pointing to Row on the Format menu the Hide command is unavailable, it probably is because the worksheet is protected. Tell students first to unprotect the worksheet, and then they will be able to hide the row.

CLASSROOM ACTIVITIES

1. Critical Thinking: It is relatively simple to change something in a cell on a worksheet, on purpose or inadvertently. Considering the worksheet created in this chapter, what cells in the worksheet should not be changed (generally, these are cells containing text or formulas)? What might be some “worst case scenarios” — disasters that could occur if the wrong cells inadvertently are altered by someone unfamiliar with computers or worksheets?

2. Quick Quiz:

1) Why must you first unlock the cells you do not want to protect before protecting the entire worksheet? (Answer: Because all cells have a locked status when they are created; the lock is just not engaged)

2) What happens when a user attempts to change a protected cell? (Answer: Excel displays a message indicating the area is protected.)

LAB ACTIVITIES

1. Have students practice using the protection and password features of an Excel cell, worksheet, and workbook. Ask them to use the Excel Help system to gather more information about protection.

EX 318: Formula Checking

LECTURE NOTES

· Explain that the Error Checking command checks worksheet formulas for rule violations

· Refer to Table 4-4 to summarize the rules that are checked for errors

· Use Figure 4-64 to discuss how to select specific error checking rules

· Describe background formula checking, the error indicator that appears, and what happens when a formula does not pass the rule check

· Use Figure 4-65 to describe how to analyze the error by viewing the Trace Error menu

· Review the steps for quitting Excel

FIGURES and TABLES: Figures — 4-64, 4-65; Table — 4-4

BOXES:

1. BTW: Excel Help. Refer the students to Appendix C for using Excel Help.

2. BTW: Quick Reference. Point out the location for the Quick Reference Summary and the Excel 2007 Quick Reference Web page.

3. BTW: Certification. For more information on the MCAS program see Appendix G or visit the Excel 2007 Certification Web page.

CLASSROOM ACTIVITIES

1. Critical Thinking: When the “Enable background error checking” option is selected, Excel checks all formulas against a list of rules found in Table 4-5. Which rules do you think are most important to invoke, and which ones could be ignored? Why? Would your answer change depending on what kind of worksheet you are working with? Why or why not?

2. Quick Quiz:

1) If a formula in a cell does pass the rule check, what does Excel display in the cell? (Answer: A green triangle is displayed.)

Microsoft Office Excel 2007

Chapter Four: Financial Functions, Data Tables, and Amortization Schedules

Chapter Objectives

Chapter Objectives	2
EX 266: Introduction	2
EX 266: Project — Loan Payment Calculator with Data Table and Amortization Schedule	2
EX 269: Plan Ahead Box (Critical Thinking): General Project Decisions	3
EX 272: Adding Custom Borders and a Background Color to a Range	3
EX 276: Creating Cell Names Based on Row Titles	4
EX 284: Using a Data Table to Analyze Worksheet Data	5
EX 291: Adding a Pointer to the Data Table Using Conditional Formatting	6
EX 294: Creating an Amortization Schedule	6
EX 307: Printing Sections of the Worksheet	7
EX 313: Protecting the Worksheet	8
EX 318: Formula Checking	9
End of Chapter Material	10
Glossary of Key Terms	11