Chapter 5: Retrieving Data for Computation, Analysis, and ReferenceSales: Creating Product Order Forms for Equipment PurchasesConceptual Review:1. The lookup_value of a VLOOKUP function can be a contiguous cell range.FALSE2. 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. TRUE3. The result_vector of a LOOKUP function must be sorted in ascending order. FALSE4. Reference and Lookup functions may not contain nested functions as arguments. FALSE5. The default range_lookup type for the VLOOKUP and HLOOKUP functions is FALSE. FALSE6. Excel matches the lookup_value “tom” with the entry “TOM” in a lookup table. TRUE7. The row and column arguments in the INDEX function can be numeric values, in cell D13. FALSE8. The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,2) returns the value incell D13. TRUE9. 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. FALSE10.The formula =MATCH(40,{10,40,50,90},0) returns the value 2. TRUE11.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?#N/A appears in the cell12.What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function? The LOOKUP function finds the greatest value that does not exceed a specified value anywhere in a range/table. The LOOKUP function does not have True/False and always gives an estimate, not an exact match. You can only use a LOOKUP function when you want to retrieve a value that is stored to the left of a key data column in a vertical lookup table or above a key data row in a horizontal lookup table. Use VLOOKUP or HLOOKUP when the data you are looking for is in the first column/row13.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? You should use the INDEX function to find a value in a two-dimensional table.14.Write a formula to choose the name of the fifth day of the week from the list starting with Sunday, Monday, Tuesday…Saturday.=CHOOSE(5,”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)15.What is the difference between the LOOKUP function and the MATCH function? The difference is, while the LOOKUP function returns a corresponding value in a list, the MATCH function returns the relative position number of the lookup_value in a list.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,$A$2:$B$7,2,TRUE)*A1317.The delivery charges used in the Delivery worksheet are as follows:a. For orders under $20, there is a $5 delivery fee.b. For orders at least $20 but less than $50, there is an $8 delivery fee.c. For orders over $50, delivery is free of charge.i. 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: I made a chart that has the total order row across withthe values x<20, x>=20, x>=50. Under each respective one I have $5, $8, Free.18.Write a formula in cell C13 in the Pricing worksheet to look up 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)19.As shown in the Grades worksheet, final grades are determined using the following grading scheme:a. Students earning over 900 points receive an Ab. Students earning less than 900 points but at least 800 points receive a Bc. Students earning less than 800 points but at least 700 points receive a Cd. Students earning less than 700 points but at least 600 points receive a De. Students earning less than 600 points receive an Fi. Write a formula in cell C2 in the Scores worksheet that determines the final grade for the first student based on thegrading scheme. Use the appropriate Reference and Lookupfunction and write the formula so that it can be copied down the column. =HLOOKUP(B2,$A$5:$E$6,2,TRUE)20.Explain the difference between the lookup table in cells A1:E2 of the Grades worksheet and the lookup table in cells A5:E6 in the same worksheet. The lookup table A1:E2 is sorted in descending order, whereas the lookup table A5:E6 is sorted in ascending order, which allows you to use the HLOOKUP function with a TRUE lookup type.Chapter 6: Evaluating the Financial Impact of Loans and InvestmentsFinance: Forecasting Cash Flows for a Capital Project Analysis1. Function to calculate the interest percentage per period of a financial transaction. RATE2. Function to calculate the value at the beginning of a financial transaction.PV3. Function to calculate the value at the end of a financial transaction. FV4. Function to calculate periodic payments into or out of a financial transaction. PMT5. Function to calculate the number of compounding periods in a financial transaction. NPER6. Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period. Type7. This type of interest is calculated based on original principal regardless of the previous interest earned. Compound interest8. This type of interest is calculated based on principal and previous interest earned. Simple interest9. Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value. SLN10.Function to calculate the amount of a specific periodic payment that is principal in a given period. PPMT11.Function to calculate the amount of a periodic payment that is interest in a given period. IPMT12.Function to calculate the cumulative principal paid between two periods.CUMPRINC13.Function to determine the value of a variable set of cash flows discounted to its present value. NPV14.Function to determine the rate of return, where the net present value of the cash flows is 0. IRR15.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. Write an Excel formula to determine how much your savings will be worth in five years. =FV(5.0%/4,5*4,-200,-6000,0)16.Write an Excel
View Full Document
Unlocking...