To find the total order cost, what excel formula should be used in a12?

Business Analytics, 2e 19 Business Analytics (Evans) Chapter 2 Analytics on Spreadsheets 1) Which of the following symbols is used to represent exponents in Excel? A) ^ B) * C) # D)! Answer: A LO1: Find buttons and menus in the Excel 2010 ribbon. 2) Which of the following ways would 10 2 5 3 / 100-73 be represented in an Excel spreadsheet? A) 10(2) * 5(3) / 100 ^ 73 B) 10(2) ^ 5(3) / 100-73 C) 10^2 * 5^3 / 100-73 D) 10*2 ^ 5*3 / 100-73 Blooms: Understand LO1: Write correct formulas in an Excel worksheet. 3) Which of the following is a difference between relative addressing and absolute addressing when using cell formulas in Excel? A) A relative address uses a dollar sign before either the row or column label; an absolute address uses the ampersand symbol before either the row or column label. B) A relative address uses a dollar sign before either the row or column label; an absolute address uses just the row and column label in the cell reference. C) A relative address uses just the row and column label in the cell reference; an absolute address uses a dollar sign before either the row or column label. D) A relative address uses only the column label in the cell reference; an absolute address uses the row. LO1: Apply relative and absolute addressing in Excel formulas.

20 Chapter 2: Analytics on Spreadsheets Use the data given below to answer the following question(s) Below is the spreadsheet for demand prediction of a company that sells chocolates. A B C Demand Prediction 1 Models 2 3 Linear Model 4 A 10,000 5 B 10 6 7 Price Demand 8 $50 9,500 9 $55 9,450 10 $45 9,550 11 4) Given that D = a-bp, where D, is demand, "a" and "b," are linear constants, and P, is price, from the below spreadsheet, how will the formula in B9 be represented in Excel using relative addressing? A) B4-B5*A9 B) C5-C6*A10 C) B4-B5*A10 D) B5-B6*A10 Answer: A LO1: Copy formulas from one cell to another or to a range of cells. 5) If a dollar sign is used after the column in B5 (B$5), how will the formula at B8 be represented in C9 using absolute addressing? A) C3-B$5*C9 B) C5-C$6*B9 C) C5-C$6*C9 D) C5-C$5*B9 Answer: D LO1: Apply relative and absolute addressing in Excel formulas.

Business Analytics, 2e 21 6) If a dollar sign is used before the column label B4 ($B4), how will the formula at B10 be represented in C11 using absolute addressing? A) $B5-C6*B11 B) $C5-C6*A10 C) $B4-C5*B11 D) $A5-C6*B11 Answer: A LO1: Apply relative and absolute addressing in Excel formulas. 7) If, in the spreadsheet, cells B9 and B10 were empty, which of the following formulas should be entered in B8 so that the formula can be dragged to B9 and B10 to obtain their correct values? A) B4-B5*A8 B) B4-B5*$A8 C) $B4-B5*$A8 D) $B$4-$B$5*$A8 Answer: D LO1: Copy formulas from one cell to another or to a range of cells. 8) Using a $ sign before a column label. A) keeps the reference to both the row and column fixed B) keeps the reference to the row fixed, but allows the column reference to change C) keeps the reference to column fixed, but allows the row reference to change D) allows both the row and column references to change LO1: Copy formulas from one cell to another or to a range of cells.

22 Chapter 2: Analytics on Spreadsheets 9) To copy a formula from a single cell or range of cells down a column or across a row, first, click and hold the mouse on the small square in the lower right-hand corner of the cell, and drag the formula to the "target" cells which you wish to copy. A) press Ctrl-C B) select the cell or range C) press Ctrl-Enter D) select the whole spreadsheet Answer: B LO1: Copy formulas from one cell to another or to a range of cells. 10) Trace the process of copying and pasting a cell, which has a formula in it, such that the formula is not retained in the pasted cell. A) Home - Paste - Paste Special - Paste Values B) Home - Paste - Paste Special - Paste Validation C) Home - Paste - Paste Special - Paste Formats D) Home - Paste - Paste Special - Paste Formulas Answer: A LO1: Copy formulas from one cell to another or to a range of cells. 11) Which of the following is a differentiation between calculating using the functions COUNT and COUNTIF? A) COUNT does not require a range of cells; COUNTIF requires a range of cells. B) COUNT only requires a range of cell and can be obtained without a special criteria, COUNTIF requires range and special criteria to be calculated. C) COUNT requires a range of cells; COUNTIF does not require a range of cells, only special criteria. D) COUNT calculates the sum of values for a range of cells; COUNTIF finds the largest value in a range of cells. Answer: B

Business Analytics, 2e 23 12) The Excel function of is used to find the largest value in a range of cells. A) SUM(range) B) COUNT(range) C) MAX(range) D) COUNTIF(range, criteria)

24 Chapter 2: Analytics on Spreadsheets Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer. A B C D E F G H Purchase 1 Orders 2 Item Item Cost per A/P Terms Order Order 3 Supplier Description Cost Quantity Order (Months) No. Size 4 Rex Technologies Graphics Card $ 89 35 $3115 20 AL123 Large Rex 5 Technologies Monitor $150 15 $2250 25 AL234 Small 6 Rex Technologies Keyboard $ 15 40 $600 15 AL345 Large 7 Rex Technologies Speakers $ 15 20 $300 25 AL456 Small Max's 8 Wavetech HD Cables $ 5 10 $50 25 KO876 Small 9 Max's Wavetech Processor $278 27 $6950 30 KO765 Large 10 Max's Wavetech Hard disk $120 18 $2160 20 KO654 Small 11 12 13) To find the largest quantity of items ordered from Rex Technologies, what Excel formula should be used in A12? A) =COUNTIF(D4:D10) B) =SUM(D4:D7) C) =MAX(D4:D7) D) =COUNT(D4:D7)

Business Analytics, 2e 25 14) To find the total order cost, what Excel formula should be used in A12? A) =COUNT(C4:C10) B) =COUNT(C4:C7) C) =MAX(C4:C10) D) =SUM(E4:E10) Answer: D 15) For which of the following columns can the COUNT function be performed? A) column G B) column E C) column B D) column A Answer: B 16) To find the average of the total cost of orders from Rex Technologies, what Excel formula should be used in A12? A) =AVERAGE(C4:C10) B) =AVERAGE(C4:C7) C) =AVERAGE(E4:E7) D) =AVERAGE(E4:E10)

26 Chapter 2: Analytics on Spreadsheets 17) To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12? A) =COUNTIF(F4:F10,"<25") B) =COUNT(F4:F10,25) C) =AVERAGE(F4:F10,"<25") D) =COUNTIF(F4:F10,F5) Answer: A 18) If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each order's size? A) =IF(D4=AND=OR=25,"Large","Small") B) =IF(D4<>25,"Small")=AND(D4=25,"Large") C) =IF(D4=25,"Large")=OR("Small") D) =IF(D4>=25,"Large","Small") Answer: D 19) measures the worth of a stream of cash flows, taking into account the time value of money. A) Accounting rate of return B) Net present value C) Internal rate of return D) Adjusted present value Answer: B

Business Analytics, 2e 27 20) The reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time. A) modified internal rate of return B) payback period C) accounting rate of return D) discount rate Answer: D 21) Identify the equation for calculating the net present value for a stated period of time, where F t = cash flow in period t, and i is the discount rate. A) NPV = B) NPV = C) NPV = D) NPV = 22) A positive NPV means that the investment will provide added value because the projected return exceeds the. A) modified internal rate of return B) discount rate C) accounting rate of return D) adjusted present value Answer: B

28 Chapter 2: Analytics on Spreadsheets 23) The easiest way to locate a particular function is to select a cell and click on the Insert function button represented by on the Excel ribbon. A) fx B) Σ C) $ D) % Answer: A LO1: Find buttons and menus in the Excel 2010 ribbon. 24) is a logical function that returns one value if the condition is true and another if the condition is false. A) OR(condition 1, condition 2 ) B) AND(condition 1, condition 2 ) C) TO(value if true, value if false) D) IF(condition, value if true, value if false) Answer: D 25) Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not? A) TO(value if true, value if false) B) AND(condition 1, condition 2 ) C) OR(condition 1, condition 2 ) D) IF(condition, value if true, value if false)

Business Analytics, 2e 29 26) Give the logical function for the following: If cell B7 equals 12, check contents of cell B10. If cell B10 is 10, then the value of the function in the string is YES; if not, it is a blank space. If cell B7 does not equal 12, then the value of the function is 7. A) =IF(B7=12,(AND(B10=10, "")(YES)),7) B) =IF(B10=10,(OR(B7=12,"")"YES")7) C) =IF(B7=12,(IF(B10=10,"YES", "")),7) D) =IF(B7=12,(AND(B10=10,"YES","")(B10="NO"),7) Diff: 3 Blooms: Understand 27) If cell G7 contains the function, it states that if the value in cell C3 is 9, the number 7 will be assigned to cell G7; if the value in cell C3 is not 9, the number 4 will be assigned to cell G7. A) =IF(G7=9)(G7=7)=OR(G7=4) B) =IF(G7=7)=THEN(C3=9)=OR(C3=4) C) =IF(C3=9)(C3=7)=OR(C3=4) D) =IF(C3=9,7,4) Answer: D Blooms: Understand 28) The function returns a value or reference of the cell at the intersection of a particular row and column in a given range. A) VLOOKUP(lookup_value, table_array, col_index_num) B) INDEX(array, row_num, col_num) C) MATCH(lookup_value, lookup_array, match_type) D) HLOOKUP(lookup_value, table_array, row_index_num) Answer: B

30 Chapter 2: Analytics on Spreadsheets 29) Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order? A) HLOOKUP(lookup_value, table_array, row_index_num) B) MATCH(lookup_value, lookup_array, match_type) C) INDEX(array, row_num, col_num) D) VLOOKUP(lookup_value, table_array, col_index_num) Answer: B 30) In a MATCH function, if the match_type = 0, then. A) the function finds the largest value that is less than or equal to lookup_value B) the function finds the smallest value that is greater than or equal to lookup_value C) MATCH finds the first value that is exactly equal to lookup_value D) the values in the lookup_array must be in a particular order 31) For which of the following MATCH functions must the values in the lookup_array be ordered in a descending order? A) When match_type = -1 B) When match_type >1 C) When match_type = 0 D) When match_type = 1 Answer: A

Business Analytics, 2e 31 Use the following scenario to answer the following question(s) The Blue Sunset Band is planning to record a new album. A major decision to be made is if the band can record the album on their own, or if they should hire a studio to record it with. The fixed cost for recording at the studio is $100,000 plus the manufacturing cost per CD, which is at $250. If they record the album in-house, the cost per CD is $350. They plan to produce 3000 copies of the album regardless of the place of recording. The band plans to record with the cheaper option. Below is the spreadsheet of the Recording Decision. A B The Blue Sunset Band Album Recording 1 Decision 2 3 Data 4 5 Recording at Studio 6 Fixed Cost $ 100,000 7 Unit CD Cost $ 250 8 9 Recording In-house 10 Unit CD Cost $ 350 11 12 Production Volume 3000 13 14 Model 15 Total studio 16 manufacturing cost $ 850,000 Total in-house manufacturing cost $1,050,000 17 18 19 Cost Difference -$ 200,000 20 Recording Decision Studio

32 Chapter 2: Analytics on Spreadsheets 32) Using the spreadsheet below, provide the steps in using Excel formulas in finding the cost of the first order for Item number 1345, and the total cost of all Item numbers 1345, using the Match and Index functions in Excel. Column B is sorted by item number in ascending order. A B C D E 1 Purchase Orders 2 3 Supplier Item No. Item Cost Quantity Cost per Order 4 Rex Technologies 1123 $ 89 35 $3115 5 Rex Technologies 1234 $150 15 $2250 6 Rex Technologies 1345 $ 15 40 $ 600 7 Rex Technologies 1345 $ 15 20 $ 300 8 Max's Wavetech 1345 $ 5 10 $ 50 9 Max's Wavetech 1765 $278 27 $6950 10 Max's Wavetech 1654 $120 18 $2160 11 Rex Technologies 1765 $ 54 56 $3024 12 Rex Technologies 1100 $ 71 33 $2343 13 Rex Technologies 1100 $ 10 14 $ 140 14 Rex Technologies 1683 $ 7 25 $ 175 15 Max's Wavetech 1683 $100 31 $3100 Answer: To find the order cost associated with the first order for 1345, which is in column E, we have to first use the Match function, =MATCH(1345,$B$4:$B$15,0). Accordingly, the result will be, =MATCH(1345,$B$4:$B$15,0) = 3. In order to find the cost associated with this result, we add this function in an Index function. Therefore, the formula for finding cost is, =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,0),5).. (1) The result for this formula is, =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,5) = $600. To find the total cost associated with all items under Item number: 1345, we find the last cost associated with 1345 in column E, which is given by the formula =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,1),5).. (2) The result for this formula is, =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,1),5) = $50. We then substitute both (1) and (2) into Excel's SUM function. Therefore we get, =SUM(INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,0),5):INDEX($A$4:$E$15,MATCH(1 345,$B$4:$B$15,1),5)) Therefore the total cost for all items under 1345, =SUM(INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,0),5):INDEX($A$4:$E$15,MATCH(1 345,$B$4:$B$15,1),5)) = $950.

Business Analytics, 2e 33 Diff: 3 33) Using a $ sign before both the row and column labels keeps the reference to that cell fixed no matter where the formula is copied. Answer: TRUE LO1: Write correct formulas in an Excel worksheet. 34) AND(condition 1, condition 2 ) is a logical function that returns TRUE if all conditions are true and FALSE if not. Answer: TRUE 35) To use the VLOOKUP(lookup_value, table_array, col_index_num), the table must be sorted in descending order. Answer: FALSE 36) In a MATCH function, the default value for match_type = 0. Answer: FALSE

34 Chapter 2: Analytics on Spreadsheets 37) In a MATCH function, if match_type = 1, then the function finds the smallest value that is greater than or equal to lookup_value. Answer: FALSE 38) Describe the method of calculating the net present value (NPV) in Excel. Answer: The Excel function NPV (rate, value1, value2, ) calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Rate is the rate of discount over the length of one period (i), and value1, value2, are 1 to 29 arguments representing the payments and income. The values must be equally spaced in time and are assumed to occur at the end of each period. The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If the first cash flow (such as an initial investment or fixed cost) occurs at the beginning of the first period, then it must be added to the NPV result and not included in the function arguments.

Business Analytics, 2e 35 39) What is the Insert function in Excel? Answer: The easiest way to locate a particular function is to select a cell and click on the Insert function button fx, which can be found under the ribbon next to the formula bar and also in the Function Library group in the Formulas tab. You may either type in a description in the search field, such as "net present value," or select a category, such as "Financial," from the drop-down box. This feature is particularly useful if you know what function to use but are not sure of what arguments to enter because it will guide you in entering the appropriate data for the function arguments. 40) Explain the different Lookup functions in Excel. Answer: Excel provides some useful functions for finding specific data in a spreadsheet. These functions are useful in many applications: VLOOKUP(lookup_value, table_array, col_index_num) looks up a value in the leftmost column of a table and returns a value in the same row from a column you specify. The table must be sorted in an ascending order. HLOOKUP(lookup_value, table_array, row_index_num) looks up a value in the top row of a table and returns a value in the same column from a row you specify. The table must be sorted in an ascending order from left to right. INDEX(array, row_num, col_num) Returns a value or reference of the cell at the intersection of a particular row and column in a given range. MATCH(lookup_value, lookup_array, match_type) Returns the relative position of an item in an array that matches a specified value in a specified order.