CGS 2518 Conceptual Final Exam Review Chapter 5 TRUE FALSE 1 The lookup value of a HLOOKUP function can be a contiguous cell range 2 In a VLOOKUP formula with a TRUE lookup type the first column of the lookup table referenced must be in ascending order to retrieve the correct value False True False False False True True True False True N A INDEX y 3 The result vector of a LOOKUP function must be sorted in ascending order 4 Reference and Lookup functions may not contain nested functions as arguments 5 The default range lookup type for the VLOOKUP and HLOOKUP functions is TRUE 6 Excel matches the lookup value tom with the entry TOM in a lookup table 7 The row and column arguments in the INDEX function can be numeric values Boolean values or text 8 The formula INDEX B2 D7 B12 D17 B22 D17 2 3 1 returns the value in cell D3 9 The formula AVERAGE CHOOSE 1 B12 D17 B22 D17 averages the value 1 with the values in cells B12 to D17 and B22 to D17 10 The formula MATCH 40 10 20 40 50 0 returns the value 3 11 What happens when Excel is solving a HLOOKUP formula with a FALSE range lookup type and does not find an exact match in the lookup table 12 What is the difference between the LOOKUP function and the VLOOKUP for HLOOKUP function LOOKUP function can retrieve data from a vertical or horizontal lookup table while VLOOKUP only retrieves data from tables with a vertical orientation and HLOOKUP only from tables with horizontal orientation 13 Which Excel function should you use when you want to look up a value from a two dimensional table where both the columns and rows can be varied 14 Write a formula to choose the name of the third day of the week from the list starting with Sunday Monday Tuesday Saturday CHOOSE 5 Sunday Monday Tuesday Wednesday Thursday Friday Saturda 15 What function returns the relative position of an item from a list The MATCH function Chapter 6 FV RATE PV NPER PMT period TYPE earned 1 Function to calculate the value at the end of a financial transaction 2 Function to calculate the interest percentage per period of a financial transaction 3 Function to calculate the value at the beginning of a financial transaction 4 Function to calculate the number of compounding periods in a financial transaction 5 Function to calculate periodic payments into or out of a financial transaction 6 Use a 0 for this argument to indicate that interest will be paid at the end of each compounding 7 This type of interest is calculated based on original principal regardless of the previous interest 8 This type of interest is calculated based on principal and previous interest earned Simple Interest Compound Interest 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 10 Function to calculate the cumulative interest paid between two periods 11 Function to calculate the amount of a periodic payment that is interest in a given period 12 Function to calculate the amount of a specific periodic payment that is principal in a given 13 Function to determine the value of a variable set of cash flows discounted to its present value 14 Function to determine the rate of return where the net present value of the cash flows is 0 15 Assume that you have been left an inheritance and want to save part of it toward the purchase of a car upon graduation which is three years from now Write an Excel formula to determine the amount of money you need to invest now to have 17 000 at the end of the three year period Assume that you will place this money in a CD that pays 2 interest compounded quarterly and that you will be making no additional deposits into this account PV 02 4 3 4 0 17000 16 Write an Excel formula to determine the yearly interest rate being charged by the bank on a 400 000 20 year mortgage You make a monthly mortgage payment of 3 400 and the value of the loan at the end of 20 years is 0 Interest is compounded monthly RATE 30 12 3400 400000 0 0 SLN CUMIPMT IPMT period PPMT NPV IRR 17 Assume that you are buying a car for 25 500 with a 4 000 down payment and you are borrowing the rest from a bank at 5 5 annual interest compounded monthly Your monthly payments are 400 Write an Excel formula to determine the number of years it will take you to pay off this loan NPER 055 12 400 25500 0 18 Consider a 150 000 mortgage at 5 annual interest compounded monthly to be paid back over the next 30 years The loan will have a 5 000 balloon payment that must be made each month on this loan Write an Excel formula to determine the payment that must be made each month on this loan PMT 05 12 30 12 150000 5000 19 Assume that you are investing 3 000 in a savings plan today and will make additional contributions of 300 per quarter The plan pays 3 interest per year compounded quarterly at the beginning of each period Write an Excel formula to determine how much your savings will be worth in five years FV 03 4 5 4 300 3000 0 20 Write an Excel formula to determine the amount of money that can be depreciated each year using straight line depreciation for a new packaging machine purchased by your company The machine originally cost 150 000 and has a useful life of 5 years and an estimated salvage value of 5 000 SLN 150000 5000 5 Chapter 7 1 List and describe the steps you would take to create a structured list of data from a text file that contains values stored on separate lines Use the CONCATENATE function to combine the data from each line into one cell Use the TRIM function to eliminate any unnecessary spaces Use the FIND function to indicate which row of data is correct and sort data Use text to columns to separate the cells 2 Why should you remove unnecessary spaces from data imported from another source How do you remove unnecessary spaces from a text string Unnecessary spaces can cause unwanted errors in the formulas You can remove these by using the TRIM function 3 How do the FIND and SEARCH functions work and how are they different The FIND and SEARCH functions return the starting position of one text value within another text value FIND is case sensitive SEARCH is not 4 What options are available for parsing data when you use the Convert Text to Columns Wizard The first way is to identify the character that delimits separates the data The second way is to set field widths to identify the breaks between data that appears in columns 5 What are the advantages and disadvantages of using the Subtotal tool to analyze data The Subtotal
View Full Document
Unlocking...