Instructor Reference Card [CF_NUM]
Excel Chapter 6 | Data Tables and Amortization Tables
Concepts–At a Glance Summary
§ KEY CONCEPTS (blue)–Most important concepts in this chapter
§ TIPS (red)–Useful shortcuts and information for more productive use of Word
§ STICKY POINTS (green)–Areas that might cause difficulty for students
Separating and Combining Text
Use the Convert Text to Columns Wizard
Functions to manipulate text:
CONCATENATE–Used to combine text entries
= CONCATENATE(text1,text2)
TIP=CONCATENATE(good, bye) would create the word “goodbye”
STICKY POINT–You must include spaces as arguments in order to properly concatenate; for example, first name and last name, might be helpful.
PROPER-Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter.
=PROPER(text)
TIP =PROPER(good) changes the text to Good.
UPPER-Converts text to uppercase letters
=UPPER(text)
TIP =UPPER(good) changes the text to GOOD
LOWER-Converts all uppercase letters to lowercase.
=LOWER(text)
TIP =LOWER(GOOD) changes the text to good
SUBSTITUTE-Substitutes new text for old text in a text string.
=SUBSTITUTE(text,old_text,new_text,instance_num)
TIP =SUBSTITUTE(A1,good,bad) replaces the word “good” in cell A1 with the word “bad” and places that text in a resultant cell
STICKY POINT–This function cannot be placed in the cell that has the text you want to replace; otherwise, you will get a circular reference.
Nested functions are functions within another function.
Use conditional formatting to identify and remove duplicate values and avoid confusion.
TIP-Excel automatically removes new duplicate rows as they are entered
Grouping enables you to consolidate related rows or columns into single units.
Collapse the group for an easier view
Can also ungroup data and expand the group
STICKY POINT–Excel will not create an outline or group data if no formulas exist in the worksheet.
The Subtotal command uses a summary function such as SUM, AVERAGE, or COUNT to compute subtotals within a sorted data table.
TIP-A grand total is displayed after the last record.
STICKY POINT–The records must be in sequence by the field on which the subtotals will be grouped prior to executing the Subtotal command.
Work with multiple workbooks visible in multiple windows on your screen by clicking Arrange All in the Window group on the View tab, and then select one of the view options.
Splitting a window is the process of dividing a worksheet window.
A splitter control is the two-headed arrow in the scroll bar used to divide a window into panes.
STICKY POINT–When you split the worksheet vertically, synchronized scrolling is on. Use the Synchronous Scrolling command in the Window group on the View tab to turn off.
STICKY POINT–Click Page Break Preview in the status bar to see the worksheet with the intended page breaks. Click Normal to return to the familiar view of a spreadsheet.
Conditional and logical functions are used to determine whether data meets specified criteria and, if so, instruct Excel how to act upon the data.
Conditional functions include:
SUMIF-Adds the cells specified by a given criterion.
=SUMIF(range,criteria,sum_range)
COUNTIF-Counts the number of cells within a range that meets the given criterion.
=COUNTIF(range,criteria)
AVERAGEIF-Returns the average of all the cells in a range that meets a given criterion.
=AVERAGEIF(range,criteria,average_range)
SUMIFS-Adds the cells in a range that meet multiple criteria.
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2[el])
COUNTIFS-Counts the number of cells within a range that meet multiple criteria.
=COUNTIFS(range1,criteria1,range2,criteria2[el])
AVERAGEIFS-Returns the average of all the cells that meet multiple criteria.
=AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2[el])
Logical functions include:
AN-Returns true when all arguments are true and returns false when one or more arguments are false.
=AND(logical1,logical2,[el])
OR-Returns true if any argument is true and returns false if all arguments are false.
=OR(logical1,logical2,[el])
NOT-Reverses the value of its argument.
=NOT(logical)
IFERROR-Returns a value you specify if a formula evaluates to an error.
=IFERROR(value,value_if_error)
Amortization is the payment schedule for a loan and shows the date of each loan payment, the amount of each payment that goes to principal and interest, and the remaining balance, which eventually reaches zero.
Requires a loan amount, an interest rate, and the number of periods for which the loan will be borrowed.
Use the YEAR, MONTH, and DAY functions to determine information such as how many days are there between July 1, 2007 and August 15, 2007.
Date Functions
Function / DescriptionDay / Returns the day of a date as a serial number. The day is shown as an integer from 1 to 31.
Month / Returns the month a date as a serial number. The month is shown as an integer from 1 (January) to 12 (December).
Year / Returns the year of a date. The year is shown as an integer from 1900 to 9999.
MATCH-Returns the relative position of an item in an array that matches a specified value in a specified order.
=MATCH(lookup_value,lookup_array,match_type)
INDEX-Returns a value or the reference to a value within a table or range.
=INDEX(array,row_num,column_num)
ROUND-Rounds a value to a specified number of digits
=ROUND(number, num_digits)
CONNECTIONS -- Practical Projects for the Student Excel User
· Create a User ID (Separate and combine text)
· Create and change an e-mail address (Manipulate text with functions)
· Remove duplicate last names (Identify and remove duplicate rows)
· Group data by class and subtotal scholarship amounts (Group and subtotal data)
· Split the window into two panes to view the header information while scrolling to the bottom of the worksheet (Work with windows)
· Determine the value of all convertibles in a collection (Use conditional functions)
· Identify which people meet the conditions and have a job classification of 3 (Create a nested IF function)
· Identify which employees meet neither criterion, and which meet one OR the other criteria (Use AND, OR, NOT, and IFERROR functions)
· Use a formula to calculate the number of scheduled payments for a loan. (Define the amortization table)
· Determine whether a second payment in a loan is necessary based on specified criteria. (Use functions in amortization tables)
Case Study Lecture Demonstration Document
Refinance Your Home
CONNECTIONS -- Practical Applications to the Real World
In the following Case Study Demonstration Document, students learn to take control of their financial information by learning to work with loans. In the future, your students may need to:
Secure a loan for a car, truck or motorcycle
Refinance their home
Obtain a line of credit for a small business (i.e. a card shop or bakery)
Understand how to manage donations for a tax exempt foundation
Balance portfolios for their investment clients
NOTE: This demonstration walk though may take more than one class period because many complex functions need to be typed in carefully.
1. Open the Student File: chap6_case_house.
2. Save the file as chap6_case_house_solution.
3. Convert Text to Columns
STEPS:
To convert text into column labels:
· Select cell A12, click the Data tab, and click Text to Columns in the Data Tools group to open the Text to Column Wizard dialog box. Click Delimited in Step 1 of 3, and click Next.
· In Step 2 of 3, click Comma in the Delimiters section to select it, and then click Tab to deselect it. Click Finish, and then click OK.
· Click in cell C12, place the insertion point before the word Balance in the Formula bar, and press Alt+Enter to place the label on two lines of the cell. Click in cell D12, place the insertion point before the word Payment in the Formula bar, and press Alt+Enter. Click in cell G12, place the insertion point before the word Payment in the Formula bar, and press Alt+Enter. Click in cell H12, place the insertion point before the word Balance in the Formula bar, and press Alt+Enter.
· Select the cells A12:H12, click the Home tab, and then click Center in the Alignment group.
4. Remove Duplicate Data
STEPS:
To remove duplicate records from a list:
· Click the Rate Comparison worksheet tab, and select the cells A1:D25.
· Click the Data tab, click Advanced in the Sort and Filter group, click Unique Records Only, and then click OK.
5. Enter Loan Data into the Amortization Table
STEPS:
To enter data:
· Click the Amortization Schedule worksheet tab, click in the cell C4, and then type 300000 and press Enter.
· Type 5 and press Enter, type 1/31/2008 and press Enter, type 15 and press Enter, type 200 and press Enter, type your Last Name, and press Enter.
6. Enter Functions into the Amortization Table
STEPS:
To create functions:
· Click in cell H4 and type =IF(DataEntered,PMT(C5/12,C7*12,-C4),0) to calculate the monthly payment.
NOTE: This IF function checks to see if data is entered, and if the result is true, the payment is calculated; otherwise, a zero is placed in the cell.
· Click in cell H5 and type =IF(DataEntered,C7*12,0) to calculate the scheduled payments.
NOTE: This IF function checks to see if data is entered, and if the result is true, the number of years located in cell C7 is multiplied by 12 months; otherwise, a zero is placed in the cell.
· Click in cell H6 and type =IF(DataEntered,DATE(YEAR(C6),MONTH(C6)+(C7-1)*12+11,DAY(C6)), 0) to calculate the normal payoff date.
NOTE: This IF function checks to see if data is entered, and if the result is true, the payoff date is calculated using the beginning date and adding the length of the loan using date math; otherwise, a zero is placed in the cell.
· Click in cell H7 and type =IF(DataEntered, MATCH(0,EndingBalance,-1)+1,0) to calculate the actual number of payments.
NOTE: This IF function checks to see if data is entered, and if the result is true, then the MATCH function uses the ending balance to find the number of payments; otherwise, a zero is placed in the cell.
· Click in cell H8 and type =IF(DataEntered,INDEX(AmortizationTable,H7,2),0) to calculate the actual payoff date.
NOTE: This IF function checks to see if data is entered, and if the result is true, then the INDEX function finds the payoff date in the table; otherwise, a zero is placed in the cell.
· Click in cell H9 and type =SUM(ExtraPayment) to calculate the total of extra payments.
NOTE: This function totals the range that contains the extra payments.
· Click in cell H10 and type =SUM(Interest) to calculate the total of all interest payments.
NOTE: This function totals the range that contains the interest.
· Click in cell B13 and type =IF(A13>0,C6,0) to place the date in C6 in the cell.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then the contents of cell C6 are copied to the current location; otherwise, a zero is placed in the cell.
· Click in cell B14 and type =IF(A14>0,DATE(YEAR(B13),MONTH(B13)+1,DAY(B13)),0) to calculate the date for each payment. Copy the function in B14 down the column stopping at B173.
NOTE: This IF function checks to see if the contents of cell A14 are greater than zero, and if the result is true, then the DATE function uses the date in B13 and adds one to the MONTH; otherwise, a zero is placed in the cell.
· Click in C13 and type =IF(A13>0,C4,0) to calculate the beginning balance.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then the contents of cell C4 are copied to the current location; otherwise, a zero is placed in the cell.
· Click in cell C14 and type =IF(A14>0,H13,0) to calculate the beginning balance for each payment. Copy the function in C14 down the column stopping at C173.
NOTE: This IF function checks to see if the contents of cell A14 are greater than zero, and if the result is true, then the contents of cell H13 are copied to the current location; otherwise, a zero is placed in the cell.
· Click in cell D13 and type =IF(A13>0,$H$4,0) to calculate the amount of a regular payment. Copy the function in D13 down the column stopping at D173.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then the contents of cell H4 are copied to the current location with an absolute reference; otherwise, a zero is placed in the cell.
· Click in E13 and type =IF(A13>0,($C$5/12)*C13,0) to calculate the interest. Copy the function in E13 down the column stopping at E173.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then the absolute contents of cell C5 are divided by 12 and then multiplied by the contents of cell C13; otherwise, a zero is placed in the cell.
· Click in F13 and type =IF(A13>0,D13-E13,0) to calculate the principal. Copy the function in F13 down the column stopping at F173.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then E13 is subtracted from D13; otherwise, a zero is placed in the cell.
· Click in G13 and type =IF(A13>0,$C$8,0)to calculate the amount of the extra payment. Copy the function in G13 down the column stopping at G173.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then the contents of cell C8 are copied to the current cell as an absolute reference; otherwise, a zero is placed in the cell.
· Click in H13 and type =IF(A13>0,(C13-F13-G13),0) to calculate the amount of the ending balance. Copy the function in H13 down the column stopping at H173.
NOTE: This IF function checks to see if the contents of cell A13 are greater than zero, and if the result is true, then G13 is subtracted from F13 and F13 is subtracted from C13; otherwise, a zero is placed in the cell.
7. Split the Window
STEPS:
To split the window to see different parts of the same worksheet:
· Move to cell A21 and click the View tab and then click Split in the Window group. Click and drag the scroll box on the bottom window down to display through row 173 so that you can view the first several and last several payments. Click Split to return the display to normal.
NOTE: To create footers, see Case Study Lecture Demonstration Chapter 5. To print formulas see Case Study Lecture Demonstration Chapter 2.
NOTE: Change the loan data at the top of the worksheet to try different scenarios.
CAUTION: Instructing all students to print the same large file at the same time can cause chaos in a lab, so you might want them simply to view the results in Print Preview and skip printing or print at a later time.