Final Exam Study Guide Answers Chapters 6 9 Chapter 5 Spring 2014 CGS 2518 Answers to all Conceptual Review 1 False The lookup value of a Vlookup function can be contiguous cell range 2 True 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 3 False The result vector of a lookup function must be sorted in ascending order 4 False Reference and lookup functions may not contain nested functions as arguments 5 False The default range lookup type for the Vlookup and Hlookup functions is false 6 True Excel matches the lookup value tom with the entry TOM in a lookup table 7 True The rows and column arguments in the INDEX functions can be numeric values Boolean values or text The formula INDEX B2 D7 B12 D17 B22 D17 2 3 2 returns the value in cell D13 8 True 9 False The formula Average Choose 1 B12 D17 B22 D17 average the value 1 with the values in cells B12 to D17 and B22 D17 The formula MATCH 40 10 40 50 90 0 returns the value 2 11 What happens when excel is solving a VLOOKUP formula with a FALSE range lookup type and does not find an exact match in the lookup table 10 True N A 12 What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function The LOOKUP function looks up avlues anywhere in a table whereas the VLOOKUP and HLOOKUP can only retrieve data from tables with vertical and horizontal orientation respectively 13 Which excell function should you use when you want to look up a value from a two dimensional table where both columns and rows can be varied INDEX Tuesday Saturday 14 Write a formula to choose the name of the fifth day of the week from the list starting with Sunday Monday CHOOSE 5 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 15 What is the difference between the LOOKUP function and the MATCH function A LOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range whereas a MATCH function returns the relative position of a matched value in a list Page 1 Base your answers for Questions 16 18 on the Pricing and Delivery worksheets shown here Cells Pricing A1 B7 list the costs per copy based on the total number of copies being made The price for less than 10 copies corresponds to 0 07 per copy the price for at least 10 copies but less than 100 copies corresponds to 0 06 per copy etc 16 Write a formula in cell B13 in the Pricing worksheet to determine the total cost of making copies for this order 3 copies Write the formula so that it works when copied into cells B14 B16 VLOOKUP A13 A2 B7 2 True A13 17 The delivery charges used in the Delivery worksheet are as follows For orders under 20 there is a 5 delivery fee For orders at least 20 but less than 50 there is an 8 delivery fee For orders over 50 delivery is free of charge Create a lookup table for the Delivery worksheet so that you can use a lookup function to calculate the delivery cost for each order Organize the table in a horizontal format as shown here Fill the chart with the rows equaling 20 50 and 5 00 8 00 and 0 for the respective prices 18 Write a formula in cell C13 in the pricing worksheet to lookup the correct delivery cost using the lookup table you created in question 17 Write the formula so that it can be copied down the column HLOOKUP B13 A 2 D 3 2 TRUE Page 2 Base your answers for Questions 19 20 on the Scores and Grades worksheets shown here 19 As shown in the Grades worksheet final grades are determined using the following grading scheme Students earning over 900 points receive an A Students earning less than 900 points but at least 800 points receive a B Students earning less than 800 points but at least 700 points receive a C Students earning less than 700 points but at least 600 points receive a D Students earning less than 600 points receive an F Write a formula in cell C2 in the Scores worksheet that determines the final grade forthe first student based on the grading scheme Use the appropriate Reference andLookup function and write the formula so that it can be copied down the column 20 Explain the difference between the lookup table in cells A1 E2 of the Grades worksheet and the lookup table The lookup table in cells A5 E6 is not in ascending order and therefore cannot work correctly HLOOKUP B2 A 5 E 6 2 TRUE in cells A5 E6 in the same worksheet Page 3 Chapter 6 Calculates the value at the beginning of a financial transaction Calculates the value at the end of a financial transaction 1 Rate Calculates the interest percentage per period of a financial transaction 2 PV 3 FV 4 PMT Calculates periodic payments into or out of a financial transaction 5 NPER Calculates the number of compounding periods in a financial transaction 6 Type Use a 1 for this argument to indicate that interest will be paid at the beginning 7 Compound Interest This type of interest is calculated based on principal and previous interest earned 8 Simple Interest This type of interest is calculated based on original principal regardless of the previous interest 9 SLN Calculates straight line depreciation based on the initial capital investment number of years to be earned depreciated and salvage value 10 PPMT Calculates the amount of a specific periodic payment that is principal in a given period 11 IPMT Calculates the amount of a periodic payment that is interest in a given period 12 CUMPRINCE Calculates the cumulative principal paid between two years 13 NPV Determines the value of a variable set of cash flows discounted to its present value 14 IRR Determines the rate of return where the net present value of the cash flows is 0 15 Assume that you are investing 6 000 in a savings plan today and will make additional contributions of 200 per quarter The plan pays 5 interest per year compounded quarterly Formula for savings in 5 years FV 05 4 5 4 200 6000 0 RATE 30 12 3000 375000 0 0 NPER 06 12 370 20500 0 16 Excel formula to determine the yearly interest rate being charged by the bank on a 375 000 30 year mortgage You make a monthly mortgage payment of 3 000 and the value of the loan at the end of 30 years is 0 Interest is compounded monthly 17 You are buying a car for 23 500 with a 3 000 down payment and you are borrowing the rest from a bank at 6 0 annual interest compounded monthly Your monthly payments are 370 Write an Excel formula to determine the number of years it will take you to …
View Full Document
Unlocking...