TEST BANK for Succeeding in Business with Microsoft Excel 2013 A Problem-Solving 1Edition. Gross Aka

Succeeding in Business with Microsoft Excel 2013 A Problem-Solving Approach 1e Gross Akaiwa Nordquist (Test Bank All Chapters, 100% Original Verified, A+ Grade) Chapter 1: Applying Fundamental Excel Skills and Tools in Problem Solving TRUE/FALSE 1. Excel provides a variety of tools for designing a spreadsheet, which is referred to as a worksheet in the Excel application.

ANS: T

PTS: 1

REF: 23

2. The labels and values upon which Excel calculations are based are outputs.

ANS: F

PTS: 1

REF: 25

3. The calculations and their results are inputs.

ANS: F

PTS: 1

REF: 25

4. The ### signs indicate that the column width is insufficient to display the complete value in the cell.

ANS: T

PTS: 1

REF: 26

5. If there is insufficient room to display a label in a cell, the text may be displayed in a truncated format.

ANS: T

PTS: 1

REF: 26

6. When entering a value in a cell, you should enter the value with commas.

ANS: F

PTS: 1

REF: 27

7. In the Format Cells dialog box, the Category box lists the available number formats.

ANS: T

PTS: 1

REF: 28

8. The Comma Style button formats numbers with commas and no decimal places.

ANS: F

PTS: 1

REF: 27

9. Some of the most common formatting options available in the Format Cells dialog box are also available on the HOME tab and on the Mini toolbar.

ANS: T

PTS: 1

REF: 27

10. A formula is an equation that performs calculations, the result of which can be either a value or a label.

ANS: T

PTS: 1

REF: 33

11. Typing a value into a cell instead of entering the formula that produced the value is a good practice when building a spreadsheet.

ANS: F

PTS: 1

REF: 35

12. When Excel rounds the display, it does so by rounding any number half or lower down to the next value.

ANS: F

PTS: 1

REF: 38

13. When working with multiple worksheets in a workbook, it’s a good idea to rename each worksheet tab based on the default labels (Sheet1, Sheet2, and so on).

ANS: T

PTS: 1

REF: 43

14. The rules programmed into a function are referred to as the function’s algorithm.

ANS: T

PTS: 1

REF: 45

15. The COUNT function ignores blank cells and cells with text.

ANS: T

PTS: 1

REF: 50

16. To use a “general” formula over and over again but with a different set of numbers, use a feature called Rotating Values.

ANS: F

PTS: 1

REF: 57

17. Sheet names are only needed in formulas when the cell being referenced is on another worksheet.

ANS: T

PTS: 1

REF: 58

18. The fill handle is a small square in the lower-right corner of a selected cell that you use to delete the contents of the selected cell.

ANS: F

PTS: 1

REF: 58

19. The pound sign (#) is the syntax used by Excel to indicate that a cell reference is absolute.

ANS: F

PTS: 1

REF: 60

20. Range names, like text labels, are enclosed in quotation marks when used in formulas.

ANS: F

PTS: 1

REF: 63

MODIFIED TRUE/FALSE 1. A paper spreadsheet is simply a sheet organized into columns and headings. _________________________ ANS: F, rows PTS: 1

REF: 23

2. The Accounting Number Style displays values with commas and two decimal places, but no dollar signs. _________________________ ANS: F

Comma Style Comma style comma style PTS: 1

REF: 27

3. The No Break button automatically wraps text within the cell, and the cell height adjusts accordingly. _________________________ ANS: F Wrap Text Wrap text wrap text PTS: 1

REF: 29 | 30

4. A(n) value is an equation that performs calculations. _________________________ ANS: F formula Formula PTS: 1

REF: 33

5. A cell formatted with the Proportion Style displays the % symbol following the value. _________________________ ANS: F Percent percent PTS: 1

REF: 38

6. Excel stores times as decimal fractions. _________________________ ANS: T

PTS: 1

REF: 39

7. Functions are predefined formulas that perform calculations. _________________________ ANS: T

PTS: 1

REF: 44

8. A function is always structured beginning with the function address and an opening parenthesis mark. _________________________ ANS: F Name name PTS: 1 9.

REF: 44

The function inputs are referred to as arguments. _________________________ ANS: T

PTS: 1

REF: 44

10. Each function has its own output, which specifies the function name and order of the arguments. _________________________ ANS: F Syntax syntax PTS: 1

REF: 44 | 45

11. SUM calculates the minimum value in a list of values. _________________________ ANS: F MIN Min PTS: 1

REF: 46

12. MAX determines the number of values in a list. _________________________ ANS: F COUNT Count PTS: 1

REF: 46

13. The COUNT function recognizes both text and number cells. _________________________ ANS: F COUNTA Counta PTS: 1

REF: 51

14. To use a “general” formula over and over again but with a different set of numbers, use a feature called absolute cell referencing. _________________________ ANS: F, relative PTS: 1

REF: 57

15. The dollar sign syntax is used by Excel to indicate that a cell reference is absolute. _________________________ ANS: T

PTS: 1

REF: 60

MULTIPLE CHOICE 1. Excel provides a variety of tools for designing and working with a spreadsheet, which is referred to as a ____ in the Excel application. a. chart c. table b. spreadsheet d. worksheet ANS: D

PTS: 1

REF: 23

2. In a spreadsheet, a number such as 12 entered into a cell represents a ____. a. calculated value c. numeric value b. submenu d. text label ANS: C

PTS: 1

REF: 24 | 25

3. The word Component entered into a cell represents a ____. a. calculated value c. numeric value b. subhead d. text label ANS: D

PTS: 1

REF: 24 | 25

4. If a column shows the numbers 1, 2, and 3 as well as the SUM equaling 6, the number 6 is a ____. a. calculated value c. text label b. numeric value d. submenu ANS: A

PTS: 1

REF: 24 | 25

5. The labels and values upon which the calculations are based are called ____. a. inputs c. calculated values b. outputs d. numeric values ANS: A

PTS: 1

REF: 25

6. Calculations and their results are called ____. a. inputs c. calculated values b. outputs d. numeric values ANS: B

PTS: 1

REF: 25

7. The Excel error message for “no answer” is ____. a. #N/A c. !NA b. #NA d. ### ANS: A

PTS: 1

REF: 25

8. The Excel error type for “wrong argument type or operand” is ____. a. #VALUE c. #VALUE! b. #NAME d. #NAME? ANS: C

PTS: 1

REF: 25

9. The error message ###### means that there is ____. a. insufficient width in the cell to display data, or negative date/time b. an invalid cell reference c. an invalid numeric value in a formula or function d. no answer ANS: A

PTS: 1

REF: 25

10. The error message #NUM! means that there is ____. a. insufficient width in the cell to display data b. an invalid cell reference c. an invalid numeric value in a formula or function d. no answer

ANS: C

PTS: 1

REF: 25

11. The error message #REF! means that there is ____. a. insufficient width in the cell to display data, or negative date/time b. an invalid cell reference c. an invalid numeric value in a formula or function d. no answer ANS: B

PTS: 1

REF: 25

12. The error message #DIV/0! means that there is ____. a. division by zero b. an invalid cell reference c. an invalid numeric value in a formula or function d. a wrong argument type or operand ANS: A

PTS: 1

REF: 25

13. To correct the problem of the error message ######, ____. a. double-click the column dividing line to make the column as wide as the longest entry in the column b. drag the column dividing line to the desired width c. choose the Column option on the Format menu and then specify column width d. any of the above ANS: D

PTS: 1

REF: 26

14. The Accounting Number Format displays ____. a. the dollar sign at the left edge of the cell b. commas c. two decimal places for the numeric value, with a column of values aligned on the decimal point d. all of the above ANS: D

PTS: 1

REF: 27

15. The ____ button formats numbers in the Accounting Number Format. a. $ c. % b. , d. all of the above ANS: A

PTS: 1

REF: 27

16. The ____ Style button formats numbers with commas, two decimal places, and values aligned on the decimal point. a. Currency c. Percent b. Comma d. all of the above ANS: B

PTS: 1

REF: 29

17. The ____ Style button formats numbers as percentages, displayed to the nearest percent, with no decimal places. a. Currency c. Percent b. Comma d. all of the above ANS: C

PTS: 1

REF: 29

18. Changing the cell background, bolding the text, and applying a border can be accomplished using the buttons in the ____ group on the HOME tab. a. Cells c. Font b. Text d. Color ANS: C

PTS: 1

REF: 30

19. The result of a formula can be a ____. a. value b. label ANS: C

PTS: 1

c. either a or b d. neither a nor b REF: 33

20. A formula in Excel always begins with a(n) ____. a. question mark (?) c. pound sign (#) b. equal sign (=) d. asterisk (*) ANS: B

PTS: 1

REF: 33

21. Excel formulas can contain ____. a. constants b. cell references to previously entered values c. cell references to other calculated values d. all of the above ANS: D

PTS: 1

REF: 33

22. One way to determine where an error might exist in a formula is to print the worksheet in a format that shows ____. a. values c. both a and b b. formulas d. neither a nor b ANS: C

PTS: 1

REF: 33

23. When the formulas are displayed, the worksheet column widths and formats ____. a. do not change b. are modified to accommodate the display c. default to a standard format d. all of the above ANS: B

PTS: 1

REF: 33

24. To display formulas in a worksheet, click the FORMULAS tab on the ribbon and then click Show Formulas in the ____. a. Formula Editing group c. Formula Auditing group b. Formula Show/Hide group d. Formula Now group ANS: C

PTS: 1

REF: 34

25. To toggle between the display of formulas and the display of values, press and hold the Ctrl key while pressing ____. a. ` (grave accent) c. ? (question mark) b. % (percent) d. + (plus) ANS: A

PTS: 1

REF: 34

26. The Excel operator for addition is ____. a. + b. * ANS: A

PTS: 1

27. The Excel operator for subtraction is ____. a. + b. * ANS: C

PTS: 1

c. / d. = REF: 35

c. d. = REF: 35

28. The Excel operator for multiplication is ____. a. + c. * b. d. = ANS: C

PTS: 1

29. The Excel operator for division is ____. a. / b. * ANS: A

PTS: 1

30. The Excel operator for exponent is ____. a. * b. / ANS: C

PTS: 1

REF: 35

c. ^ d. = REF: 35

c. ^ d. & REF: 35

31. ____ is/are first in the order of precedence rules. a. Addition and subtraction from left to right b. Exponentiation c. Multiplication and division from left to right d. Operations in parentheses ANS: D

PTS: 1

REF: 36

32. ____ is/are second in the order of precedence rules. a. Addition and subtraction from left to right b. Exponentiation c. Multiplication and division from left to right d. Operations in parentheses ANS: B

PTS: 1

REF: 36

33. If A1=2, then the resulting value of =A1*(3+5) is ____. a. 10 c. 16 b. 11 d. 30 ANS: C

PTS: 1

REF: 36

34. If A1=2, the resulting value of =3*A1^3 is ____. a. 9 c. 18 b. 12 d. 24 ANS: D

PTS: 1

REF: 36

35. ____ is/are third in the order of precedence rules. a. Addition and subtraction from left to right b. Exponentiation c. Multiplication and division from left to right d. Operations in parentheses ANS: C

PTS: 1

REF: 36

36. ____ is/are last in the order of precedence rules. a. Addition and subtraction from left to right b. Exponentiation c. Multiplication and division from left to right d. Operations in parentheses ANS: A

PTS: 1

REF: 36

37. If A1=2, B2=3, and C3=10, the resulting value of =A1-B2+C3/10 is ____. a. 0 c. 11/10 b. 1 d. 4 ANS: A

PTS: 1

REF: 36

38. When Excel rounds the display, it does so by rounding any number ____ down to the next value. a. greater than half c. equal to or less than half b. less than half d. equal to or greater than half ANS: B

PTS: 1

REF: 38

39. When Excel rounds the display, it does so by rounding any number ____ up to the next value. a. greater than half c. equal to or less than half b. less than half d. equal to or greater than half ANS: D

PTS: 1

REF: 38

40. If 5% is displayed in a cell, the actual value that is stored is ____. a. .005 c. .5 b. .05 d. 5 ANS: B

PTS: 1

REF: 38

41. Excel stores dates as sequential numbers starting with January 1st of the year ____. a. 1000 c. 2000 b. 1900 d. 2007 ANS: B

PTS: 1

42. Excel stores times as ____. a. decimal fractions b. whole numbers ANS: A

PTS: 1

REF: 39

c. mini clocks d. all of the above REF: 39

43. If a formula reads =1/10/2014+10, Excel will treat the 1/10/2014 as a(n) ____. a. calculation c. error b. date d. none of the above

ANS: A

PTS: 1

REF: 39

44. When working with multiple worksheets in a workbook, it’s a good idea to ____ each worksheet tab. a. rename c. reorder b. delete d. make a duplicate of ANS: A

PTS: 1

REF: 43

45. ____ are predefined formulas that perform calculations. a. Functions c. Rules b. Arguments d. Codes ANS: A

PTS: 1

REF: 44

46. The ____ function adds a list of values and/or cell ranges. a. SUM c. NAME b. RANGE d. VALUE ANS: A

PTS: 1

REF: 44

47. A function is always structured beginning with the function name and an opening ____. a. parenthesis mark c. bracket b. quotation mark d. none of the above ANS: A

PTS: 1

REF: 44

48. A function contains a list of inputs in a specific order, separated by ____. a. commas (,) c. backward slashes (\) b. forward slashes (/) d. pound signs (#) ANS: A

PTS: 1

49. The function inputs are referred to as ____. a. lists b. data ANS: C

PTS: 1

REF: 44

c. arguments d. syntax REF: 44

50. Each function has its own ____, which specifies the function name and order of the arguments. a. arguments c. list b. syntax d. code ANS: B

PTS: 1

REF: 44 | 45

51. The rules programmed into the function are referred to as a function’s ____. a. argument c. syntax b. algorithm d. code ANS: B

PTS: 1

REF: 45

52. Two cell references separated by a colon are referred to as a ____. a. cell format c. cell range b. cell group d. cell block ANS: C

PTS: 1

REF: 45

53. Excel’s ____ feature gives you quick access to the SUM function and other similar functions.

a. Summation b. AddNow ANS: C

c. AutoSum d. AutoAdd PTS: 1

REF: 46

54. The ____ function determines the number of values in a list. a. AVERAGE c. MIN b. COUNT d. MAX ANS: B

PTS: 1

REF: 46

55. After clicking the AutoSum button and choosing the function you want, Excel presents a suggested range to include in the calculation based on ____. a. rows or columns of numbers next to the current cell b. your past history with the function c. column width d. all of the above ANS: A

PTS: 1

REF: 46

56. The ____ function calculates the maximum in a list of values. a. MAX c. MOST b. MAXIMUM d. MORE ANS: A

PTS: 1

REF: 46

57. The ____ function calculates a minimum in a list of values. a. MIN c. LESS b. MINIMUM d. LOWEST ANS: A

PTS: 1

REF: 46

58. The ____ function calculates the average of a list of values. a. AVE c. SUM/DIVIDE b. AVG d. AVERAGE ANS: D

PTS: 1

REF: 46

59. A range is considered to be ____ if it contains multiple rows and columns. a. one-dimensional c. multi-dimensional b. two-dimensional d. mega-dimensional ANS: B

PTS: 1

REF: 47

60. B3:B7 represents ____. a. noncontiguous cells and/or constants b. a range along a column c. a range along a row d. a two-dimensional range or a block of cells ANS: B

PTS: 1

61. B3:D3 represents ____. a. noncontiguous cells and/or constants b. a range along a column c. a range along a row

REF: 47

d. a two-dimensional range or a block of cells ANS: C

PTS: 1

REF: 47

62. B3:D7 represents ____. a. noncontiguous cells and or constants b. a range along a column c. a range along a row d. a two-dimensional range or a block of cells ANS: D

PTS: 1

REF: 47

63. B3,C4, D5:D6, 6 represents ____. a. nonadjacent cells and/or constants b. a range along a column c. a range along a row d. a two-dimensional range or a block of cells ANS: A

PTS: 1

REF: 47

64. The ____ function ignores blank cells and text. a. AVERAGE c. COUNTA b. COUNT d. both a and b ANS: D

PTS: 1

REF: 50

65. The syntax used for referencing cells with their worksheet names is the sheet name, followed by ____, then the usual column letter and, finally, the row number. a. a question mark c. a pound sign b. an exclamation point d. a dollar sign ANS: B

PTS: 1

REF: 66

66. A cell reference that has only one $ is referred to as a(n) ____ cell reference. a. mixed c. absolute b. alternative d. relative ANS: A

PTS: 1

67. $B$1 is an example of a(n) ____ reference. a. absolute b. mixed ANS: A

PTS: 1

68. $B1 is an example of a(n) ____ reference. a. absolute b. mixed ANS: B

PTS: 1

69. B1 is an example of a(n) ____ reference. a. absolute b. mixed ANS: D

PTS: 1

REF: 60

c. noncontiguous d. relative REF: 60

c. noncontiguous d. relative REF: 60

c. noncontiguous d. relative REF: 60

70. To copy a formula from one cell to another, use the Copy button in the ____ group on the HOME tab to copy the cell contents to the Clipboard. a. Copy and Paste c. Content b. Paste Options d. Clipboard ANS: D

PTS: 1

REF: 64

Case-Based Critical Thinking Questions Case 1-1

Jane has just started a job at a local shoe retailer. One of her first tasks was to compile the above worksheet, showing the first quarter. Her boss has reviewed the worksheet and asked her to add some functions and formulas to the worksheet.

71. Which of the rows on the spreadsheet could easily make use of the SUM function? a. Selling Expense c. COGS Subtotal b. Revenue d. Sales Volume ANS: C

PTS: 1

REF: 63

TOP: Critical Thinking

72. Which cell would have the formula =C5*C6? a. C8 c. E8 b. D8 d. C16 ANS: A

PTS: 1

REF: 64

73. What formula would yield the correct results for cell C18? a. =C8-(C14+C16) c. either a or b b. =C8-C14-C16 d. neither a nor b

TOP: Critical Thinking

ANS: C

PTS: 1

REF: 64

TOP: Critical Thinking

74. Cell D11 can be calculated with what formula? a. =B11*D5*B1 c. =D8-D14 b. =B11*D6 d. =D8*D15 ANS: A

PTS: 1

REF: 62

TOP: Critical Thinking

75. What formula would you write to find the largest value in cells E11:E14? a. =MAXIMUM(E11:E14) c. MAX(E11:E14) b. =MAX(E11:E14) d. MIN(E11:E14) ANS: B

PTS: 1

REF: 47

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 1-2

Peter is creating a spreadsheet to summarize labor costs, which is shown above. He has entered information into the Description column, Quantity/Shoe column, and Total Cost column. However, there are several errors in the Total Cost column.

76. What does the ###### error in cell D13 indicate? a. insufficient column width to display data c. invalid numeric values in formula or function b. division by zero d. no answer ANS: A

PTS: 1

REF: 25

TOP: Critical Thinking

77. What does the #DIV/0 error in cell D15 indicate? a. insufficient column width to display data c. invalid numeric values in formula or function b. division by zero d. no answer

ANS: B

PTS: 1

REF: 25

TOP: Critical Thinking

78. What should Peter do to correct the ###### error? a. Increase the column width. b. Change the formula. c. Delete the cell. d. Change the column heading.

ANS: A

PTS: 1

REF: 26

TOP: Critical Thinking

79. Peter has updated the production and cost figures and wants to enter them in the input area, which is ____. a. cells A1:B15 c. cells A9:D15 b. cells A1:D15 d. cells A1:D7 ANS: D

PTS: 1

REF: 24

TOP: Critical Thinking

80. The chart shows that 30 appliques can be attached for a cost of $10/hour. If there are 6 appliques per shoe (cell B12), how much is the cost per shoe for applying the appliques? a. $0.02 c. $2.00 b. $0.20 d. $0.33 ANS: C

PTS: 1

REF: 36

TOP: Critical Thinking

COMPLETION 1. In Excel, a spreadsheet is referred to as a(n) ____________________.

ANS: worksheet PTS: 1

REF: 23

2. A word such as component, when used in a cell of the worksheet, is an example of a(n) ____________________.

ANS: text label PTS: 1

REF: 24

3. The ____________________ format displays the dollar sign immediately in front of the value and does not align values on the decimal point.

ANS: Currency currency $ PTS: 1

REF: 28

4. The ____________________ Format button formats numbers with commas, two decimal places, values aligned on the decimal point, and the dollar sign displayed at the left edge of the cell.

ANS: Accounting Number

PTS: 1

REF: 29

5. The ____________________ Style button formats numbers with commas, two decimal places, and values aligned on the decimal point.

ANS: Comma comma , PTS: 1

REF: 29

6. The ____________________ Style button formats numbers as percentages, displayed to the nearest percent, with no decimal places.

ANS: Percent percent % PTS: 1

REF: 29

7. A(n) ____________________ is an equation that performs calculations.

ANS: formula PTS: 1

REF: 33

8. A(n) ____________________ in Excel always begins with an equal sign (=).

ANS: formula PTS: 1

REF: 33

9. The default print format has ____________________ rather than formulas printed in the cells.

ANS: values PTS: 1

REF: 33

10. The Excel operator used for multiplication is ____________________.

ANS: * asterisk PTS: 1

REF: 35

11. The Excel operator used for division is ____________________.

ANS: / forward slash

PTS: 1

REF: 35

12. The Excel operator used for an exponent is ____________________.

ANS: ^ caret PTS: 1

REF: 35

13. ____________________ are predefined formulas that perform calculations.

ANS: Functions PTS: 1

REF: 44

14. The function inputs are referred to as ____________________.

ANS: arguments PTS: 1

REF: 44

15. The rules programmed into the function are referred to as the function’s ____________________.

ANS: algorithm PTS: 1

REF: 45

16. In the SUM function, two cell references separated by a colon are referred to as a(n) ____________________.

ANS: cell range PTS: 1

REF: 45

17. Excel’s ____________________ feature gives you quick access to the SUM function and other similar functions.

ANS: AutoSum Autosum autosum PTS: 1

REF: 46

18. In the formula =$B$1*B11*C5, the cell reference B11 is a(n) ____________________ reference.

ANS: relative PTS: 1

REF: 60

19. In the formula =$B$1*$B11*C5, the cell reference $B11 is a(n) ____________________ reference.

ANS: mixed PTS: 1

REF: 59 | 60

20. In the formula =$B$1*B11*C5, the cell reference $B$1 is a(n) ____________________ reference.

ANS: absolute PTS: 1

REF: 59 | 60

MATCHING

Using the above figure, identify the letter of the choice that best matches the figure. a. b. c. d. e.

Cell D7 Cell D16 Cells C12:D16 Cell A7 Cells D5:D8

1.

2.

3.

4.

5.

Numeric values Text labels Calculated values Input Output

1.

2.

3.

4.

5.

ANS: A ANS: D ANS: B ANS: E ANS: C

PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1

REF: REF: REF: REF: REF:

24 24 24 25 25

Identify the letter of the choice that best matches the phrase or definition. a. ###### e. #NAME? b. #N/A f. #REF! c. #VALUE! g. #NUM! d. #DIV/0!

6.

7.

8.

9.

10.

11.

12.

Insufficient width in cell to display data, or negative date/time Unrecognized text in a formula No answer Invalid cell reference Wrong argument type or operand Invalid numeric values in a formula or function Division by zero

6.

7.

8.

9.

10.

11.

12.

ANS: A ANS: E ANS: B ANS: F ANS: C ANS: G ANS: D

PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF:

25 25 25 25 25 25 25

ESSAY 1. Describe three ways to correct the formatting problem of modifying column width as shown by the error message ###### in a calculated cell.

ANS: • Double-click the column dividing line to make the column as wide as the longest entry in the column.

• Drag the column dividing line to the desired width.

• Click the Format button in the Cells group on the HOME tab, click Column Width on the menu, and then type the width in the Column width box. (You can also open this dialog box by right-clicking a column heading, and then clicking Column Width on the shortcut menu.)

• Click the Format button in the Cells group on the HOME tab, and then click AutoFit Column Width.

PTS: 1

REF: 25 | 26

TOP: Critical Thinking

2. Describe the difference between the three predefined styles in the Number group on the HOME tab or the Mini toolbar.

ANS: The Accounting Number Format button formats numbers in an accounting format, with commas, two decimal places, values aligned on the decimal points, and the dollar sign displayed at the left edge of the cell.

The Percent Style button formats numbers as percentages, displayed to the nearest percent, with no decimal places.

The Comma Style button formats numbers with commas, two decimal places, and values aligned on the decimal point.

PTS: 1

REF: 29

TOP: Critical Thinking

3. When you insert rows in a list of values that are being calculated, such as with a total, Excel most often includes the newly inserted values and recalculates the formulas affected. Depending on what values are in the list and how they are formatted, however, this might not always work as anticipated.

If you think additional rows will be needed, describe two ways to avoid potential problems.

ANS: 1. Insert a new row or rows within the list of values instead of at the end of the list. This ensures that the newly inserted values are incorporated in any formulas. This method is acceptable if the values can be listed in any order. If you want to maintain a specific order, you would have to copy and paste and/or retype some of the data to obtain the order you want.

2. Design your worksheet to include a blank row between the end of the list of values and the total formulas. The cell ranges you use in the formulas can include the blank row; and then whenever you insert rows, you can do so just above the blank row. This preserves the order of the values listed and ensures that all formulas are automatically updated. Because most of the commonly used functions ignore blank cells, the values generated should not be affected. When using this method, however, it is important to keep in mind that Excel might display an alert informing you that the formula refers to a blank cell. You can simply ignore this alert in this instance.

PTS: 1

REF: 49

TOP: Critical Thinking

Chapter 2: Solving Problems with Statistical Analysis Tools TRUE/FALSE 1. Microsoft Excel provides a variety of predefined functions, including statistical functions, that you can use to determine such values as the arithmetic mean, median, mode, and standard deviation of a set of data.

ANS: T

PTS: 1

REF: 79

2. The median is the arithmetic average of a set of numbers.

ANS: F

PTS: 1

REF: 80

3. The standard deviation tells you how closely together values are distributed.

ANS: T

PTS: 1

REF: 82

4. The ROUND argument num_digits is a single value that can be a constant, a cell reference where the cell contains a numerical value, or another formula that results in a single number value.

ANS: F

PTS: 1

REF: 85

5. The formula =ROUNDUP(3.432,1) rounds the value 3.432 up to the next highest tenth, or 3.5.

ANS: T

PTS: 1

REF: 86

6. The Format Painter can be used to copy a format into multiple noncontiguous cells.

ANS: T

PTS: 1

REF: 88

7. Selecting the Set precision as displayed workbook option permanently changes the values in all workbook cells from full precision, which is six digits, to whatever format is displayed in that cell.

ANS: F

PTS: 1

REF: 88

8. If you select the Paste option button called Values, you will paste only the values; the formulas and any formatting from the original cell(s) are not pasted.

ANS: T

PTS: 1

REF: 90

9. The Paste Special dialog box offers Operation options, which allow you to paste values using only three arithmetic operations: Add, Subtract, and Multiply.

ANS: F

PTS: 1

REF: 92

10. The statistical function MODE returns the most frequently occurring value in a range of data.

ANS: T

PTS: 1

REF: 93

11. The technique used to fix certain rows while you scroll to other rows in a worksheet is called freezing panes.

ANS: T

PTS: 1

REF: 95

12. A way to analyze the differences between two sets of data is to look at the percent difference of a value in one data set compared with that value in the second data set.

ANS: T

PTS: 1

REF: 99

13. The syntax of the RANK.EQ function is as follows: RANK(number,sort,order).

ANS: F

PTS: 1

REF: 105 | 106

14. With the LARGE function, the argument called analysis describes the range of cells being evaluated.

ANS: F

PTS: 1

REF: 107

15. The SMALL function determines the nth smallest value in a range.

ANS: T

PTS: 1

REF: 109

16. The COUNTONLY function counts the number of items in a range that meet specified criteria.

ANS: F

PTS: 1

REF: 111

17. The values TRUE and FALSE are referred to as Boolean values.

ANS: T

PTS: 1

REF: 112

18. Relational operators are used to compare data.

ANS: T

PTS: 1

REF: 113

19. Result Seek uses an iterative approach to finding the right input that achieves the desired result, or goal, in the dependent cell.

ANS: F

PTS: 1

REF: 134

20. Simulation is an analytical method that creates artificially generated data to imitate real data.

ANS: T

PTS: 1

REF: 139

MODIFIED TRUE/FALSE 1. The median is the arithmetic value that occurs in the middle of a data set when organized from lowest to highest, where half the values are less than and half the values are greater than the median value. _________________________ ANS: T

PTS: 1

REF: 80

2. The Paste option called Paste Special pastes a connection to the original cells, including the applied formatting. _________________________ ANS: F

Paste Link Paste link paste link PTS: 1

REF: 91

3. In the function RANK.EQ(number,ref,order), the number argument refers to the value to be ranked. _________________________ ANS: T

PTS: 1

REF: 105

4. The BIG function determines the nth largest value in a range. _________________________ ANS: F, LARGE PTS: 1

REF: 107

5. To obtain the value for the lowest or highest Friction Coefficient values, the MIN and MAX functions would suffice. _________________________ ANS: T

PTS: 1

REF: 107

6. The critical argument is essentially a test that the data must meet in order for it to be counted in the grouping. _________________________ ANS: F, criteria argument PTS: 1

REF: 111

7. To determine if a value is greater than or equal to another value, you can use syntax operators. _________________________ ANS: F, relational PTS: 1

REF: 113

8. Wingdings are symbols that you can use as part of the criteria to search for text strings; each symbol can be substituted for a character or set of characters. _________________________ ANS: F, Wildcards PTS: 1

REF: 113

9. The COUNTIF function accommodates a(n) single contiguous range argument. _________________________ ANS: T

PTS: 1

REF: 116

10. In the SUMIF function, the criteria argument identifies the cell range where the criteria are located. _________________________ ANS: F, range PTS: 1

REF: 118

11. The ADDIF function adds all the values in a range that meet specified criteria. _________________________ ANS: F, SUMIF PTS: 1

REF: 118

12. The Format Cells dialog box, which can be opened from the Number group Dialog Box Launcher on the HOME tab, provides many options for changing the display of cell values. _________________________ ANS: T

PTS: 1

REF: 125

13. Excel uses the Goal Find tool to try various input values in order to calculate the required input to achieve your desired outcome. _________________________ ANS: F, Goal Seek PTS: 1

REF: 131

14. The RANDOM function randomly assigns a number between two specified values. _________________________ ANS: F, RANDBETWEEN PTS: 1

REF: 140

15. You can recalculate a worksheet at any time by pressing the F9 function key or by selecting the Refresh button found in the Calculation group on the FORMULAS tab on the ribbon. _________________________ ANS: F, Calculate Now PTS: 1

REF: 142

MULTIPLE CHOICE 1. Microsoft Excel allows you to use ____, such as LARGE, SMALL, and RANK.EQ, that help you to structure and analyze data in meaningful ways. a. functions c. rules b. charts d. arguments ANS: A

PTS: 1

REF: 79

2. The ____ is the arithmetic average of a set of numbers. a. mean c. mode b. median d. standard deviation ANS: A

PTS: 1

REF: 80

3. The ____ is the arithmetic value that occurs in the middle of a data set when organized from lowest to highest, where half the values are less than and half the values are greater than the median value. a. mean c. mode

b. median ANS: B

d. standard deviation PTS: 1

REF: 80

4. The ____ is the arithmetic value that occurs most frequently in a data set. a. mean c. mode b. median d. standard deviation ANS: C

PTS: 1

REF: 80

5. The ____ is a measure of how widely the data values are dispersed from the arithmetic mean. a. mean c. mode b. median d. standard deviation ANS: D

PTS: 1

REF: 80

6. Consider the following five values: 1, 1, 6, 7, and 10. The arithmetic mean of these values is ____. a. 1 c. 5 b. 3.94 d. 6 ANS: C

PTS: 1

REF: 80

7. A(n) ____ distribution exhibits an equal number of occurrences of data values both below and above the arithmetic mean. a. normal c. simulated b. skewed d. angular ANS: A

PTS: 1

REF: 80

8. The ____ of a normal distribution are the same value. a. mean, median, and mode b. mean and median c. mean, median, mode, and standard deviation d. median and mode ANS: A

PTS: 1

REF: 80

9. The ____ function algorithm rounds down all values of less than half the range, and rounds up values from half the range and above. a. AVERAGE c. DOWN b. NORMAL d. ROUND ANS: D

PTS: 1

REF: 86

10. The ROUND argument num_digits is the specified number of ____. a. decimal places c. integers b. digits d. operators ANS: A

PTS: 1

REF: 85

11. If you wrote the formula =ROUNDDOWN (25.83%,2), the resulting value would be ____. a. 26% c. 25.8% b. 25% d. 25.83% ANS: B

PTS: 1

REF: 87

12. If you wrote the formula =ODD(1.23), the resulting value would be ____.

a. 1 b. 2 ANS: C

c. 3 d. 4 PTS: 1

REF: 87

13. If you wrote the formula =TRUNC(-4.382,1), the resulting value would be ____. a. -4 c. -4.38 b. -4.3 d. -4.382 ANS: B

PTS: 1

REF: 87

14. Use ____ to copy a format from one cell to another cell or group of contiguous cells. a. the Format Painter c. Paint b. the Format Copier d. Special Format ANS: A

PTS: 1

REF: 88

15. Selecting the Set precision as displayed workbook option permanently changes the values in all workbook cells from full precision (____ digits) to whatever format is displayed in that cell, including the number of decimal places. a. 5 c. 15 b. 9 d. 21 ANS: C

PTS: 1

REF: 88

16. The simplest method to copy information is to first select the information you want to copy, and then use the Copy button and the Paste button in the Clipboard group on the ____ tab. a. FORMAT c. INSERT b. HOME d. DATA ANS: B

PTS: 1

REF: 90

17. The Paste option called ____ pastes the contents of the copied cells(s) as a picture. a. Picture c. Paste Picture b. As Picture d. Paste Graphic ANS: A

PTS: 1

REF: 91

18. The Paste option called ____ pastes the formulas and formatting from the original range of cells, but reverses the orientation so that the rows of the original cell range become the columns in the pasted range, and the original columns become rows. a. Transpose c. Wildcard b. Switch d. Turn ANS: A

PTS: 1

REF: 90

19. The Paste option button option called ____ pastes the formulas and formatting from the original cell(s), but not the format of the cell borders. a. Keep Source Formatting c. Document Theme b. No Borders d. Destination Formatting ANS: B

PTS: 1

REF: 90

20. The Paste option button called ____ pastes the data and formulas from the original cell(s), and maintains the column width of the original cell(s). a. Column Stay c. Keep Source Column Widths

b. Width Only ANS: C

d. Keep Column Size PTS: 1

REF: 90

21. The Paste Special dialog box offers the ____ option, which enables you to copy and paste a cell range that contains one or more blank cells where the blank cells are not pasted over any existing values in the range into which they are pasted. a. Copy Blanks c. Comments b. Blank Over d. Skip Blanks ANS: D

PTS: 1

REF: 92

22. The MODE.SNGL, MEDIAN, and STDEV.S functions work in a similar way, containing only one type of argument, which is ____. a. sort order c. a range of values for comparison b. a list of values d. ranking parameters ANS: B

PTS: 1

23. A list of values can contain ____. a. constants and cell references b. a range of cells along a column or row ANS: D

PTS: 1

REF: 93

c. a two-dimensional block of cells d. all of the above REF: 93

24. With a ____ function, you include that function inside another formula or function as one of its arguments. a. nested c. child b. parent d. linked ANS: A

PTS: 1

REF: 94

25. The technique used to fix certain rows while you scroll to other rows in a worksheet is called ____ panes. a. freezing c. keeping b. sticking d. locking ANS: A

PTS: 1

REF: 95

26. A technique you can use to see different parts of the screen at the same time is to ____ the window by dragging either the horizontal split box or the vertical split box to create separate, scrollable panes. a. split c. crack b. delete d. separate ANS: A

PTS: 1

REF: 96 | 97

27. To calculate a(n) ____ between two data sets, you subtract the old value from the new value and then divide the difference by the old value. a. average difference c. percent difference b. standard deviation difference d. none of the above ANS: C

PTS: 1

REF: 99

28. The ____ function allows you to sort a list and then count the number of entries either above or below the value in question. a. RANK.EQ c. FIND

b. POSITION ANS: A

d. COUNT PTS: 1

REF: 105

29. In the LARGE function, the second argument, k, is the desired ranking, where 1 is ____. a. the largest value c. required b. the smallest value d. not allowed ANS: A

PTS: 1

REF: 107

30. In the SMALL function, the first argument, array, is ____. a. a formula c. a range of cells b. the desired ranking d. a time period ANS: C

PTS: 1

REF: 109

31. The syntax of the COUNTIF function is ____. a. =COUNTIF(range,array) c. =COUNTIF(array,k) b. =COUNTIF(ref,range) d. =COUNTIF(range,criteria) ANS: D

PTS: 1

REF: 111

32. The values TRUE and FALSE are referred to as ____. a. operational imperatives c. base values b. Boolean values d. syntax neutral ANS: B

PTS: 1

REF: 112

33. The symbols > and >= are examples of ____. a. relational operators b. relational values ANS: A

PTS: 1

c. arrays d. reference operators

REF: 113

34. ____ are symbols that you can use as part of the criteria to search for text strings in which the symbol can be substituted for another character or set of characters. a. Wingdings c. Open Text symbols b. Wildwheels d. Wildcards ANS: D

PTS: 1

REF: 113

35. The ____ wildcard specifies that any number of characters can be substituted. a. asterisk (*) c. forward slash (/) b. question mark (?) d. backward slash (\) ANS: A

PTS: 1

REF: 113

36. The ____ wildcard specifies that a single character can be substituted. a. asterisk (*) c. forward slash (/) b. question mark (?) d. backward slash (\) ANS: B

PTS: 1

37. Wildcards work with ____. a. numbers b. dates

REF: 113 | 114

c. text d. all of the above

ANS: C

PTS: 1

REF: 114

38. With the COUNTIF function, the first time it encounters the comma delimiter, it assumes that what follows is ____. a. a date c. additional ranges b. a number d. the criteria ANS: D

PTS: 1

REF: 116

39. The Format Cells dialog box, which can be opened from the ____ group Dialog Box Launcher on the HOME tab, provides many options for changing the display of cell values. a. Cells c. Number b. Data d. Data ANS: C

PTS: 1

REF: 125

40. A format code can include up to four parts, each separated by a semicolon, and does NOT include ____. a. negative number format c. zero value format b. positive number format d. placeholder format ANS: D

PTS: 1

REF: 127

41. The ____ symbol acts as a digit placeholder that displays significant digits. a. # c. ? b. 0 d. % ANS: A

PTS: 1

REF: 128

42. The ____ symbol acts as a digit placeholder that displays both significant and insignificant zeros. a. # c. ? b. 0 d. % ANS: B

PTS: 1

REF: 128

43. The ____ symbol acts as a digit placeholder that does not display insignificant digits, but does hold a place so that decimal points will align. a. # c. ? b. 0 d. % ANS: C

PTS: 1

REF: 128

44. The ____ symbol inserts a percentage sign and automatically multiplies the value inserted by 100 for display. a. # c. ? b. 0 d. % ANS: D

PTS: 1

REF: 128

45. The ____ symbol(s) insert(s) a comma as a thousands separator or as a scaling operator. a. , c. “” b. * d. @ ANS: A

PTS: 1

REF: 128

46. The ____ symbol(s) indicate(s) repetition of the following character enough times to fill the column to its complete width. a. , c. “” b. * d. @ ANS: B

PTS: 1

REF: 128

47. The ____ symbol(s) specify/specifies that text enclosed in between these marks should be inserted as shown. a. , c. “” b. * d. @ ANS: C

PTS: 1

REF: 128

48. The ____ symbol(s) indicate(s) the location where text should be inserted in cells formatted with a custom format. a. , c. “” b. * d. @ ANS: D

PTS: 1

REF: 128

49. The ____ symbol indicates to skip the width of the next character. It’s frequently used with ( ) to make sure positive numbers align with negative numbers displayed with ( ). a. _ (underscore) c. @ b. - (dash) d. + ANS: A

PTS: 1

REF: 128

50. Performing a ____ analysis means, simply, to determine the outcome of changing one or more input values and to evaluate the recalculated results. a. maybe c. factor b. what-if d. research ANS: B

PTS: 1

REF: 131

51. When using Goal Seek, you can specify the outcome you want and which input value you want to vary, and Excel ____. a. gives you a set of code to use in a database program b. automatically calculates the solution c. prompts you with a dialog box d. none of the above ANS: B

PTS: 1

REF: 131

52. In the Goal Seek dialog box, the cell containing the data to vary in order to reach the desired output is labeled ____. a. By changing cell c. What if b. What to change d. Vary ANS: A

PTS: 1

REF: 132

53. In the Goal Seek dialog box, you use the Set cell box to specify the cell ____. a. in which the output value will appear c. with the output label b. that contains the formula to use d. none of the above ANS: A

PTS: 1

REF: 132

54. The Step button in Goal Seek ____. a. allows you to step through each iteration one step at a time b. returns the data in separate spreadsheets c. walks you through the steps similar to a wizard d. none of the above ANS: A

PTS: 1

REF: 134

55. Once the Goal Seek Status dialog box gives the target value, you can click ____ to update your worksheet with the new values based on Goal Seek. a. OK c. New b. Update d. Cancel ANS: A

PTS: 1

REF: 132

56. In Goal Seek, if the target value cannot be reached exactly, the ____. a. Goal Seek dialog box asks for your input b. value of zero is listed as the current value c. closest value found is listed as the current value d. none of the above ANS: C

PTS: 1

REF: 132

57. Goal Seek uses ____ approach to finding the right input that achieves the desired result, or goal, in the dependent cell. a. a database c. a scientific b. an iterative d. a random ANS: B

PTS: 1

REF: 134

58. Goal Seek continues to enter values until it reaches ____. a. 0.001 of the goal c. either a or b b. 100 iterations d. neither a nor b ANS: C

PTS: 1

REF: 134

59. The ____ function provided by Excel averages a series of values if they meet specific criteria. a. COUNTIF c. AVERAGE b. SUMIF d. none of the above ANS: D

PTS: 1

60. Goal Seek allows you to vary ____ input(s). a. a single b. up to 3 ANS: A

PTS: 1

61. The input for Goal Seek can be ____. a. a constant value b. derived from a formula ANS: A

PTS: 1

REF: 136

c. up to 5 d. up to 10 REF: 134

c. either a or b d. neither a nor b REF: 135

62. The syntax of the AVERAGEIF function ____ is very similar to the syntax of the SUMIF function.

a. (range,criteria,average_range) b. (criteria,average,range) ANS: A

PTS: 1

c. (ref,range,criteria) d. (array,average_range,ref) REF: 136

63. ____ is an analytical method that creates artificially generated data to imitate real data. a. Simulation c. Role playing b. Play acting d. Regression ANS: A

PTS: 1

REF: 139

64. A simulation that is based on randomly generating specific values that have an equal chance of appearing, such as numbers on a set of dice, is often referred to as a ____ simulation. a. Las Vegas c. Lucky 7 b. Blackjack d. Monte Carlo ANS: D

PTS: 1

REF: 139

65. The ____ function randomly assigns a number between two specified values. a. RANGERANDOM c. INBETWEEN b. RANDBETWEEN d. RANDOM ANS: B

PTS: 1

REF: 140

66. The ____ function returns a random value between 0 and 1. a. RANDUNDER c. RANDZERO b. RAND d. RANDONE ANS: B

PTS: 1

REF: 140

67. The formula =RANDBETWEEN(1,3) randomly returns a(n) ____. a. integer with three numbers c. number with three decimal places b. 1, 2, or 3 d. none of the above ANS: B

PTS: 1

REF: 140

68. Automatic calculation can be turned off from the ribbon or from the ____ dialog box accessed via the FILE tab. a. Excel Options c. Automatic Options b. Worksheet Options d. Ribbon Options ANS: A

PTS: 1

REF: 142

69. When working with the RAND and RANDBETWEEN functions, every time you enter another value in a cell anywhere on the worksheet, the random values ____. a. automatically change c. prompt you with an error message b. prompt you with a dialog box d. stay the same ANS: A

PTS: 1

REF: 141

70. You can recalculate a worksheet at any time by pressing the ____ function key. a. F5 c. F8 b. F7 d. F9 ANS: D

PTS: 1

Case-Based Critical Thinking Questions

REF: 142

Case 2-1

Julia is learning how to use relational operators with the COUNTIF function. Her boss handed her the chart in the above figure and asked her to solve some everyday business problems.

71. Julia wants to take a count of all employees who are participating in more than one committee. The data is listed in column F of a worksheet. The correct formula would be ____. a. =COUNTIF(F3:F13,“<1”) c. =COUNTIF(F3:F13,“>=1”) b. =COUNTIF(F3:F13,“>1”) d. =COUNTIF(F3:F13,“=1”) ANS: B

PTS: 1

REF: 112

TOP: Critical Thinking

72. Julia wants to take a count of all employees who are participating in exactly one committee. The correct formula would be ____. a. =COUNTIF(F3:F13,“<1”) c. =COUNTIF(F3:F13,“>=1”) b. =COUNTIF(F3:F13,“>1”) d. =COUNTIF(F3:F13,“=1”) ANS: D

PTS: 1

REF: 112

TOP: Critical Thinking

73. After showing her boss the data, he asked her to run one more COUNTIF to determine who is on one or more committees. The correct formula would be ____. a. =COUNTIF(F3:F13,“<1”) c. =COUNTIF(F3:F13,“>=1”) b. =COUNTIF(F3:F13,“>1”) d. =COUNTIF(F3:F13,“=1”) ANS: C

PTS: 1

REF: 112

TOP: Critical Thinking

74. On a separate project, the head of Human Resources is looking for a list of people who do not have 100% attendance. In the database, the number 1 means 100% attendance; all other numbers indicate that some work was missed (for example, .75 is 75% attendance). The data is listed in column E of a worksheet. The correct COUNTIF formula would be ____. a. =COUNTIF(E3:E13,“<>1”) c. =COUNTIF(E3:E13,“>=1”) b. =COUNTIF(E3:E13,“=1”) d. =COUNTIF(E3:E13,“<1”) ANS: A

PTS: 1

REF: 112

TOP: Critical Thinking

75. If the Human Resources director wanted a list of people who do have 100% attendance, the correct formula would be ____. a. =COUNTIF(E3:E13,“<>1”) c. =COUNTIF(E3:E13,“>=1”) b. =COUNTIF(E3:E13,“=1”) d. =COUNTIF(E3:E13,“<1”) ANS: B

PTS: 1

REF: 112

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 2-2

Nevia is using Goal Seek for the first time. She is determining what to put in each text box inside the Goal Seek dialog box shown in the above figure.

76. If you were to help Nevia, you would tell her that the space labeled #1 in the above figure ____. a. indicates the cell containing the output value b. indicates the desired output value c. indicates the cell containing the data to vary in order to reach the desired output d. none of the above ANS: A

PTS: 1

REF: 132

TOP: Critical Thinking

77. Nevia has told you that the desired value is 325. In which text box should she insert the desired value? a. #1 c. #3 b. #2 d. none of the above ANS: B

PTS: 1

REF: 132

TOP: Critical Thinking

78. Nevia’s boss stops by her desk and asks which of the three numbers in the above figure is considered to be the dependent data, and she correctly answers ____. a. #1 c. #3 b. #2 d. none of the above ANS: C

PTS: 1

REF: 132

TOP: Critical Thinking

79. At the end of the project, Nevia is very pleased with the results and is giving a demo of Goal Seek to a few of her co-workers. To open Goal Seek, her first step is to click the ____ tab on the ribbon. a. FORMULAS c. REVIEW b. PAGE LAYOUT d. DATA ANS: D

PTS: 1

REF: 132

TOP: Critical Thinking

80. Once Nevia clicks the correct tab, to open Goal Seek, she clicks the ____ button in the Data Tools group, then selects Goal Seek. a. What-If Analysis c. Goal Minder b. Simulation d. Data Dialog ANS: A COMPLETION

PTS: 1

REF: 132

TOP: Critical Thinking

1. ____________________ is a measure of how widely the data values are dispersed from the arithmetic mean.

ANS: Standard deviation PTS: 1

REF: 80

2. To specify that a value should be precisely stored to the nearest hundredth, use the ____________________ function.

ANS: ROUND Round round PTS: 1

REF: 84 | 85

3. The formula =____________________(3.432,1) rounds the value 3.432 up to the next highest tenth, or 3.5.

ANS: ROUNDUP Roundup roundup PTS: 1

REF: 86

4. The ____________________ Paste option button pastes the formulas and formatting from the original cell(s), but not the format of the cell borders.

ANS: No Borders PTS: 1

REF: 90

5. The ____________________ Paste option button pastes only the formulas from the original (copied) cell(s).

ANS: Formulas PTS: 1

REF: 90

6. The ______________________________ Paste option button pastes values from the original cell(s) and formatting.

ANS: Values & Source Formatting Values and Source Formatting values & source formatting values and source formatting PTS: 1

REF: 90

7. The ____________________ Paste option button pastes a connection to the original cell, including the applied formatting.

ANS: Paste Link paste link PTS: 1

REF: 91

8. The MODE.SNGL, MEDIAN, and STDEV.S functions work in a similar way, containing only one type of ____________________, which is a list of values.

ANS: argument PTS: 1

REF: 93

9. When you ____________________ a function, you include that function inside another formula or function as one of its arguments.

ANS: nest PTS: 1

REF: 94

10. Excel provides several tools for displaying and scrolling columns and/or rows so that certain areas can be fixed, or ____________________, and the remainder of the worksheet can be scrolled easily.

ANS: frozen PTS: 1

REF: 95

11. To split an Excel window vertically, click the ____________________ after clicking to the right and below the location where you want to divide the window.

ANS: split button PTS: 1

REF: 96

12. To split the screen both vertically and horizontally so there are five rows at the top and three columns on the left, place the cursor in the ____________________ column displayed on the screen in the sixth row of the worksheet.

ANS: fourth 4th PTS: 1

REF: 96

13. To calculate a(n) ____________________ between two data sets, you subtract the old value from the new value and then divide the difference by the old value.

ANS: percent difference PTS: 1

REF: 99

14. In the function RANK.EQ(number,ref,[order]), the ____________________ argument is the range of values the number is being compared with.

ANS: REF Ref ref PTS: 1

REF: 105

15. The ____________________ function counts the number of items in a range that meet specified criteria.

ANS: COUNTIF PTS: 1

REF: 111

16. Wildcards do not work with values that are numbers or dates, only ____________________.

ANS: text PTS: 1

REF: 114

17. The relational operator >= stands for ____________________ than or equal to.

ANS: greater PTS: 1

REF: 113

18. When the # symbol acts as a placeholder for a digit, it is considered to be a(n) ____________________ code.

ANS: number formatting PTS: 1

REF: 128

19. Up to four different format codes can be applied to a cell: one for positive numbers, one for negative numbers, one for zero values, and one for ____________________.

ANS: text PTS: 1

REF: 127

20. Performing a(n) ____________________ analysis means, simply, to determine the outcome of changing one or more input values and to evaluate the recalculated results.

ANS: what-if what if PTS: 1 MATCHING

REF: 131

Using the above figure, identify the letter of the choice that best matches the figure. a. Counts all Inspector IDs that contain the text value “1” anywhere in the value (A313, C321, B313, and so on) b. Counts all Inspector IDs that end with the text value “3” c. Counts all Inspector IDs that have the value “3” in the third-to-last position in the text value, regardless of the number of preceding characters d. Counts all Inspector IDs with a single character followed by the characters “313”; notice that cell H7 is not counted because it contains two characters preceding the characters “313” 1.

2.

3.

4.

#1 #2 #3 #4

1.

2.

3.

4.

ANS: B ANS: A ANS: D ANS: C

PTS: PTS: PTS: PTS:

1 1 1 1

REF: REF: REF: REF:

114 114 114 114

Identify the letter of the choice that best matches the function. a. =ROUND(25.449,0) b. =ROUND(SUM(10.33,10.44),0) c. =ROUND(25.33%,2) d. =INT(-4.3) e. =ROUND(103234,-2) f. =ROUND(23.75%,2) g. =ROUNDDOWN(9.99,0) h. =EVEN(2.23) 5.

6.

7.

8.

9.

10.

11.

24% 21 25% -5 25 9 4

12. 103,200 5.

6.

7.

8.

9.

10.

11.

12.

ANS: F ANS: B ANS: C ANS: D ANS: A ANS: G ANS: H ANS: E

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF:

86 | 87 86 | 87 86 | 87 86 | 87 86 | 87 86 | 87 86 | 87 86 | 87

ESSAY 1. List and explain briefly mean, median, mode, and standard deviation.

ANS: • Mean is the arithmetic average of a set of numbers.

• Median is the arithmetic value that occurs in the middle of a data set when organized from lowest to highest, where half the values are less than and half the values are greater than the median value.

• Mode is the arithmetic value that occurs most frequently in a data set.

• Standard deviation is a measure of how widely the data values are dispersed from the arithmetic mean.

PTS: 1

REF: 80

TOP: Critical Thinking

2. Describe what the arguments are for this function: =SUMIF(range,criteria,sum_range).

ANS: • The range argument identifies the cell range where the criteria are located.

• The criteria argument specifies which values should be selected.

• The sum_range argument identifies the corresponding cell range to sum if the specified criteria have been met in the range established by the range argument. If the sum_range argument is omitted, the function adds the values in the range indicated by the first argument.

PTS: 1

REF: 118

TOP: Critical Thinking

3. Explain what the following four number formatting codes do when used in Excel: #, 0, ?, and %. Write one sentence about each symbol and give an example of how it can be used.

ANS: The # symbol acts as a digit placeholder that displays significant digits (for example, ####.#).

The 0 symbol acts as a digit placeholder that displays both significant and insignificant zeros (for example, 0.00).

The ? symbol acts as a digit placeholder that does not display insignificant digits, but does hold a place so that decimal points will align (for example, 0.00?).

The % symbol inserts a percentage sign and automatically multiplies the value inserted by 100 for display (for example, #%).

PTS: 1

REF: 128

TOP: Critical Thinking

Chapter 3: Determining Effective Data Display with Charts TRUE/FALSE 1. Microsoft Excel charts provide a visual representation of quantitative information, giving the viewer an overall picture of a set of data.

ANS: T

PTS: 1

REF: 155

2. Charts can successfully organize a large collection of numbers, make comparisons between different parts of the data, and tell a story.

ANS: T

PTS: 1

REF: 156

3. A data series is composed of related data points that are plotted on a chart.

ANS: T

PTS: 1

REF: 156

4. Data series are points in a data set at which the x-axis and y-axis values intersect.

ANS: F

PTS: 1

REF: 156

5. The design of statistical graphics is the combined use of points, lines, numbers, words, shading, and color to present quantitative information.

ANS: T

PTS: 1

REF: 157

6. Tufte’s first principle, “Above all else show the data,” is a reminder to show as large a range of data as possible.

ANS: F

PTS: 1

REF: 158

7. In business, charts are typically used to summarize information so that it can be used by one person making a decision.

ANS: F

PTS: 1

REF: 158

8. As a general rule, you should place charts that are developed for presentation and printing on a chart sheet.

ANS: T

PTS: 1

REF: 163

9. The values in a bubble chart type are indicated by the distance from a center point.

ANS: F

PTS: 1

REF: 199

10. In a line chart, values are indicated by the filled areas below the lines.

ANS: F

PTS: 1

REF: 163

11. An X Y (Scatter) chart plots numeric values on both the x- and y-axes based on the value of the data.

ANS: T

PTS: 1

REF: 164

12. A line chart plots numeric values on one axis and category labels equidistantly on the other axis.

ANS: T

PTS: 1

REF: 165

13. The Alternative Data task pane, which is available from the shortcut menu for a selected chart, provides options for modifying the values used to generate a chart.

ANS: F

PTS: 1

REF: 168

14. The Switch Row/Column button provides settings for swapping a data series between rows or columns.

ANS: T

PTS: 1

REF: 168

15. The Description options on the Chart Elements menu control the display and placement of the chart legend.

ANS: F

PTS: 1

REF: 169

16. Use the Trendline options on the Chart Elements menu to specify whether the x- and y-axes are displayed.

ANS: F

PTS: 1

REF: 169

17. Use a pie chart when it’s most important to show relative percentages rather than values.

ANS: T

PTS: 1

REF: 170

18. You must make changes to the content of data labels using buttons in the Format Data Labels task pane.

ANS: T

PTS: 1

REF: 184

19. Bar and column charts are commonly used to compare values across categories.

ANS: T

PTS: 1

REF: 163 | 177

20. To compare the contribution each value in a single data series makes to the whole, or 100%, use a pie chart.

ANS: T

PTS: 1

REF: 163 | 177

MODIFIED TRUE/FALSE 1. The chart title is the descriptive text that identifies the chart’s contents. _________________________ ANS: T

PTS: 1

REF: 156

2. Excel datalines are small, word-sized graphics placed within the images or words they represent. _________________________

ANS: F, sparklines PTS: 1

REF: 159

3. Excel provides 10 standard chart types, with 53 sub-types that can be used to present information graphically. _________________________ ANS: T

PTS: 1

REF: 163

4. Use the Label options on the Chart Elements menu to display a table of values for each data series in a grid below the chart. _________________________ ANS: F Data Table data table Data table PTS: 1

REF: 169

5. The Data Labels category found on the Chart Elements menu provides options for changing the information the label displays as well as the label position. _________________________ ANS: T

PTS: 1

REF: 169

6. Layered charts are known for doing a good job of illustrating the cumulative effects of data in categories. _________________________ ANS: F, Stacked PTS: 1

REF: 178

7. Excel offers two pie chart sub-types, Pie of Pie and Bar of Bar, that can be used to combine many smaller segments of a pie chart into a separate smaller chart. ________________________ ANS: F Bar of Pie Bar of pie bar of pie PTS: 1

REF: 182

8. Any changes to the data label content must be made using the Format Data Labels task pane. _________________________ ANS: T

PTS: 1

REF: 184

9. Regresslines graphically illustrate trends in the data using a statistical technique known as regression. _________________________ ANS: F, Trendlines PTS: 1

REF: 191

10. When tracking stock prices, the term crawling average is used because the average is calculated each day for the last 30 days—moving the average along the chart. _________________________ ANS: F, moving average PTS: 1

REF: 191

11. In a worksheet, the MAX and MIN functions can be used to take care of the highest and lowest stock price, respectively, for each month. _________________________ ANS: T

PTS: 1

REF: 193

12. In a worksheet, you can add up the stock traded during the month by using the ADD function. _________________________ ANS: F SUM Sum sum PTS: 1

REF: 193

13. A management console shows a visual summary of various performance data that a company’s managers use on a regular basis. _________________________ ANS: F, dashboard PTS: 1

REF: 196

14. Radar charts are named for their resemblance to the plots on radar screens as they scan a 360-degree circle. _________________________ ANS: T

PTS: 1

REF: 196

15. Bubble charts allow three-dimensional data to be plotted in three dimensions on two axes. _________________________ ANS: F two 2 PTS: 1

REF: 199

MULTIPLE CHOICE 1. Microsoft Excel ____ provide a visual representation of quantitative information, giving the viewer an overall picture of a set of data. a. charts c. tables b. spreadsheets d. worksheets ANS: A

PTS: 1

REF: 155

2. The ____ is the descriptive text that identifies the chart’s contents. a. chart title c. x-axis label b. chart legend d. y-axis label ANS: A

PTS: 1

REF: 156

3. The ____ is the vertical axis where data values are plotted. a. chart title c. y-axis b. chart legend d. x-axis ANS: C

PTS: 1

REF: 156

4. ____ identify the data values plotted on the y-axis. a. Chart titles c. Y-axis labels b. Chart legends d. X-axis labels ANS: C

PTS: 1

REF: 156

5. The ____ is the horizontal axis where categories are plotted. a. chart title c. y-axis b. chart legend d. x-axis ANS: D

PTS: 1

REF: 157

6. ____ identify the categories plotted on the x-axis. a. Chart titles c. Y-axis labels b. Chart legends d. X-axis labels ANS: D

PTS: 1

REF: 157

7. ____ are the related data points that are plotted on the chart. a. Data series c. Legends b. Data points d. Chart titles ANS: A

PTS: 1

REF: 156

8. ____ are the points in a data series at which the x-axis and y-axis values intersect. a. Data series c. Legends b. Data points d. Chart titles ANS: B

PTS: 1

REF: 156

9. A ____ is a box that identifies the patterns or colors assigned to the data series in a chart. a. data series c. legend b. data point d. chart title ANS: C

PTS: 1

REF: 157

10. According to Tufte, ____ can make charts difficult to read, use, and interpret. a. data ink c. chart junk b. chart legends d. chart labels ANS: C

PTS: 1

REF: 158

11. ____ is a part of the chart that decorates more than informs. a. Non-data-ink c. Legend-ink b. Data-ink d. none of the above

ANS: A

PTS: 1

REF: 158

12. The purpose of formatting is to make a chart ____. a. easier for the viewer to understand c. colorful b. compact d. contain as much data as possible ANS: A

PTS: 1

REF: 158

13. A ____ is a word-sized chart embedded in context with the data it represents. a. chart sub-type c. chart b. line chart d. sparkline ANS: D

PTS: 1

REF: 159

14. Charts can be embedded as a(n) ____ on the same worksheet as the source data for the chart. a. function c. comment b. icon d. object ANS: D

PTS: 1

REF: 163

15. Charts can be placed on a separate worksheet that is referred to as a ____. a. chart sheet c. worksheet b. cheat sheet d. chart page ANS: A

PTS: 1

REF: 163

16. You can modify the location of a chart at any time using the _____ dialog box. a. Modify Chart c. Chart Elements b. Move Chart d. Edit Chart ANS: B

PTS: 1

REF: 163

17. To create a chart in Excel, you first select the data you want to display in the chart, and then click the ____ tab on the ribbon. a. VIEW c. DATA b. FORMULAS d. INSERT ANS: D

PTS: 1

REF: 161

18. If the source data is dynamic, seeing the effect of the changes in a chart can be very handy. In this case, the chart should be placed ____. a. on the worksheet with its data c. in a new file b. on a worksheet separate from its data d. in separate workbooks ANS: A

PTS: 1

REF: 163

19. An Excel chart requires ____ numeric data series in one chart. a. two or more b. at least one c. x and y d. none of the above; the number of data series and data points that Excel requires in a chart is only limited by the available computer memory and capacity ANS: B

PTS: 1

REF: 163

20. In a(n) ____ chart, values are indicated by the height of the columns.

a. column b. area ANS: A

c. pie d. radar PTS: 1

REF: 163

21. In a ____ chart, values are indicated by the height of the lines. a. column c. pie b. line d. radar ANS: B

PTS: 1

REF: 163

22. In a ____ chart, values are indicated by the size of the slices. a. column c. pie b. line d. radar ANS: C

PTS: 1

REF: 163

23. In a ____ chart, values are indicated by the distance from a center point. a. column c. pie b. line d. radar ANS: D

PTS: 1

REF: 164

24. The ____ chart type displays stock price and volume trends over time. a. finance c. Wall Street b. stock d. trader ANS: B

PTS: 1

REF: 164

25. In the ____ chart type, values are indicated by areas with colors or patterns on the surface of the chart. a. pattern c. scatter b. surface d. doughnut ANS: B

PTS: 1

REF: 164

26. The column chart’s default column chart sub-type is the _____ sub-type. a. stacked c. high-low b. clustered d. 3-D ANS: B

PTS: 1

27. Clicking ____ a chart selects the chart. a. anywhere in b. outside of ANS: A

PTS: 1

REF: 164

c. on the chart title only in d. on the data series only in REF: 165

28. A(n) ____ chart plots numeric values on both the x- and y-axes based on the value of the data. a. X Y (Scatter) c. column b. line d. bar ANS: A

PTS: 1

REF: 164

29. When a chart is selected, two contextual chart tools appear on the ribbon, and they do NOT include ____. a. DESIGN c. EDIT b. FORMAT d. neither b nor c

ANS: C

PTS: 1

REF: 169

30. To change the source data, click the Switch Row/Column button in the Data group on the CHART TOOLS ____ tab to open the Select Data Source dialog box. a. LAYOUT c. EDIT b. FORMAT d. DESIGN ANS: D

PTS: 1

REF: 168

31. Excel offers many formatting and display options for changing the layout of chart elements that can be found on the ____ when a chart is selected. a. Chart Elements menu c. TOOLS LAYOUT tab b. CHART LAYOUT tab d. FORMATTING tab ANS: A

PTS: 1

REF: 168

32. The Chart Elements menu allows you to control the appearance of the ____. a. legend c. chart title and axis titles b. data labels d. all of the above ANS: D

PTS: 1

REF: 168

33. The Format Axis task pane allows you to control ____. a. axes c. both a and b b. gridlines d. neither a nor b ANS: C

PTS: 1

REF: 169

34. To create a forward forecast in Excel, adding a ____ to a chart may reveal trends in the data. a. linear trendline c. stock volume b. sparkline d. stock price ANS: A

PTS: 1

REF: 191

35. A(n) ____ chart combines the features of a line chart with a bar or column chart by filling in the area below the line. a. area c. contrast b. stacked d. radar ANS: A

PTS: 1

REF: 170

36. A ____ chart displays trends in three dimensions, with values in colored or patterned areas on the surface of the chart. a. column c. bubble b. radar d. surface ANS: D

PTS: 1

REF: 164

37. The sum of the value of the categories must total ____ to use a pie chart. a. 50% c. 95% b. 75% d. 100% ANS: D

PTS: 1

REF: 170

38. In the Format Data Labels task pane, use the options in the ____ category to specify the alignment (horizontal or vertical), orientation, position, and direction of the data labels.

a. Alignment b. Size & Properties ANS: B

c. Label Options d. Effects PTS: 1

REF: 174

39. ____ of the chart types have 3-D options, but these should be used with caution. a. Few c. Only one b. Most d. All ANS: B

PTS: 1

REF: 175

40. A 100% stacked chart is similar to a ____ chart except that the pieces are in a column. a. pie c. bar b. line d. radar ANS: A

PTS: 1

REF: 177

41. ____ charts all have a stacked chart sub-type. a. Line b. Column ANS: D

PTS: 1

c. Area d. all of the above

REF: 178

42. Stacked charts do a good job of illustrating the ____ effects of data in categories. a. cumulative c. long-term b. individual d. short-term ANS: A

PTS: 1

REF: 178

43. To change the chart sub-type, click the Change Chart Type button in the ____ group on the CHART TOOLS DESIGN tab. a. Type c. Design b. Chart d. Format ANS: A

PTS: 1

REF: 179

44. The pie chart has two chart sub-types, ____ and Bar of Pie, which are smaller charts that are displayed next to the original chart. a. Pie of Pie c. Line of Pie b. Column of Pie d. Table of Pie ANS: A

PTS: 1

REF: 182

45. The ____ option of splitting a pie data series into a second, smaller pie chart assigns a specific number of values to the second plot. a. Position c. Percent Value b. Value d. Custom ANS: A

PTS: 1

REF: 183

46. The ____ option of splitting a pie data series into a second, smaller pie chart allows you to select a cutoff point that assigns all the values below that point to the second plot. a. Position c. Percent Value b. Value d. Custom ANS: B

PTS: 1

REF: 183

47. The ____ option of splitting a pie data series into a second, smaller pie chart allows you to select a cutoff point by percentage, rather than value, and assign all the percentages below that point to the second plot. a. Position c. Percentage Value b. Value d. Custom ANS: C

PTS: 1

REF: 183

48. The ____ option of splitting a pie data series into a second, smaller pie chart allows you to drag individual pie segments between the two charts so you can include exactly the segments you want in the main pie chart and the second plot. a. Position c. Percent Value b. Value d. Custom ANS: D

PTS: 1

REF: 183

49. Although doughnut charts are not a chart sub-type, they are related to ____ because of the type of data displayed. a. 100% stacked charts c. both a and b b. pie charts d. neither a nor b ANS: C

PTS: 1

REF: 184

50. One of the features of 100% stacked charts is that they show ____ percentages. a. cumulative c. average b. individual d. linked ANS: A

PTS: 1

REF: 184

51. Doughnut charts are a way to show the information contained in a pie chart for ____. a. one series only c. totals more than 100% b. more than one series d. none of the above ANS: B

PTS: 1

REF: 184

52. With the ____ sub-type, the data to be plotted must be placed by column in this order: high stock value for the day, low stock value, and, finally, closing stock value. a. High-Low-Close c. Low-High-Close b. Low-Close-High d. High-Close-Low ANS: A

PTS: 1

REF: 186

53. The ____ chart sub-type adds information on the opening price of the stock to the High-Low-Close information for the time period. a. Open-High-Low-Close c. either a or b b. High-Low-Close-Open d. neither a nor b ANS: A

PTS: 1

REF: 187

54. In the Open-High-Low-Close chart sub-type, ____ is used to show if the stock closed at a higher or lower price than its opening price. a. color c. icons b. arrows d. none of the above ANS: A

PTS: 1

REF: 187

55. In the Open-High-Low-Close chart sub-type, if the box is white, the stock increased in value for the time period. The white box is referred to as the ____. a. up bar c. increase bar b. raise bar d. rich bar ANS: A

PTS: 1

REF: 187

56. Using the ____ in a chart is a common way to include widely differing sets of data from the same time period. a. secondary axis c. moving average b. primary axis d. scatter feature ANS: A

PTS: 1

REF: 188

57. ____ graphically illustrate trends in the data using a statistical technique known as regression. a. Graph lines c. Trendlines b. Average lines d. Datalines ANS: C

PTS: 1

REF: 191

58. The ____ trendline is used to smooth out the data, making it easier to spot trends. a. average c. smooth average b. moving average d. trial ANS: B

PTS: 1

REF: 191

59. The radar chart is similar in structure to a spider web, with each “web strand” that roughly forms a circle representing the ____. a. data values c. y-axis b. x-axis d. data labels ANS: A

PTS: 1

REF: 196

60. Generally, when a stock price moves above the moving average trendline, it is a good indicator of ____. a. a downward trend c. a mistake in the data b. an upward trend d. baseline regression ANS: B

PTS: 1

REF: 191

61. ____ charts are named for their resemblance to the plots on radar screens as they scan a 360-degree circle. a. Radar c. NASA b. Air traffic controller d. Doppler ANS: A

PTS: 1

REF: 196

62. Each segment in a radar chart is _____. a. always the same size as the other segments b. always a different size than the other segments c. always dependent on the other segments d. always stated as a percentage ANS: A

PTS: 1

REF: 196

63. Radar charts are like area charts—but whereas area charts emphasize the amount of the contribution, radar charts emphasize ____. a. the high and low points of the data c. the trend in each category over time b. moving averages d. the relative comparison of the categories ANS: D

PTS: 1

REF: 196

64. ____ charts allow three-dimensional data to be plotted in 2-D on two axes. a. Radar c. Doughnut b. Bubble d. Scatter ANS: B

PTS: 1

REF: 199

65. In a scatter chart and a bubble chart, the x- and y-axes ____. a. are numeric c. either a or b b. contain categories d. neither a nor b ANS: A

PTS: 1

REF: 199

66. In a bubble chart, the larger the bubble, the _____ the value. a. larger c. more complex b. smaller d. more simple ANS: A

PTS: 1

REF: 199

67. Bubble charts can be problematic to work with since the source data must be selected without including ____. a. labels c. either a or b b. numbers d. neither a nor b ANS: A

PTS: 1

REF: 201

68. ____ got their name because they display performance indicators in a fashion similar to the instrument panel in a car’s dashboard. a. Management dashboards c. Management speedways b. Management instrument panels d. Management control centers ANS: A

PTS: 1

REF: 202

69. The ____ gallery in the Insert Shapes group on the CHART TOOLS FORMAT tab contains a variety of shapes that can be added to a chart. a. Shapes c. Borders b. Icons d. Paint ANS: A

PTS: 1

Case-Based Critical Thinking Questions Case 3-1

REF: 205

Column Chart

Area Chart

Sarah has put together the same information in two different charts, shown in the figure above, to present to her boss. She is thinking through the pros and cons of each chart type.

70. Which chart does a better job of illustrating the collective sales trend in the Equipment, Footwear, and Apparel categories over the eight years? a. the column chart c. both a and b equally b. the area chart d. neither a nor b does a good job ANS: B

PTS: 1

REF: 170

TOP: Critical Thinking

71. What is one of the weaknesses of the column chart shown above? a. too many columns c. inaccurate data b. too few columns d. choice of x- and y-axis ANS: A

PTS: 1

REF: 170

TOP: Critical Thinking

72. An area chart combines the features of a ____ by filling in the area below the line, and displaying the trend of values over time or categories. a. line chart with a bar or column chart c. scatter chart with a radar chart b. bar chart with a column chart d. bubble chart with a surface chart ANS: A

PTS: 1

REF: 170

TOP: Critical Thinking

73. If Sarah wanted to focus on and display the percentage contribution that each category makes to the whole, which chart type would work best? a. surface c. scatter b. pie d. radar ANS: B

PTS: 1

Case-Based Critical Thinking Questions Case 3-2

REF: 170

TOP: Critical Thinking

By Columns

By Rows

Ross always looks at data in more than one way. He discovered a feature in Excel that allows him to display the chart By Column and By Rows, as shown in the above figure.

74. To create the comparison shown in the above figure, Ross used the Switch Row/Column button in the ____ group. a. Data c. Location b. Type d. Chart Layouts ANS: A

PTS: 1

REF: 168

TOP: Critical Thinking

75. When the chart is plotted by columns, what information does it emphasize? a. comparing the amount of each year’s sales for each of the three categories b. the contribution that each category made to each year’s performance c. the percentage of total sales that each of the three categories make up d. none of the above ANS: A

PTS: 1

REF: 168

TOP: Critical Thinking

76. When the chart is plotted by rows, what information does it emphasize? a. comparing the amount of each year’s sales for each of the three categories b. the contribution that each category made to each year’s performance c. the percentage of total sales that each of the three categories make up d. none of the above ANS: B

PTS: 1

REF: 168

TOP: Critical Thinking

77. Ross discovers a second way to change the rows and columns without opening a dialog box: He simply clicks the Switch Row/Column button in the Data group on the ____ tab. a. CHART EDIT c. CHART FORMAT b. CHART INSERT d. CHART TOOLS FORMAT ANS: D

PTS: 1

REF: 178

TOP: Critical Thinking

78. Looking at the column chart, which category/categories is/are decreasing in sales each year? a. Equipment only c. Footwear only b. Apparel only d. Equipment, Footwear, and Apparel ANS: A COMPLETION

PTS: 1

REF: 168

TOP: Critical Thinking

1. Microsoft Excel can be used to provide a visual representation of ____________________ information, giving the viewer an overall picture of a set of data.

ANS: quantitative PTS: 1

REF: 155

2. The groundbreaking book by Edward R. ____________________, The Visual Display of Quantitative Information, gave advice such as “Above all else show the data.”

ANS: Tufte PTS: 1

REF: 156

3. The ____________________ is the descriptive text that identifies a chart’s contents.

ANS: chart title PTS: 1

REF: 156

4. ____________________ are small word-sized charts or graphics embedded within the words, numbers, and images they represent.

ANS: Sparklines PTS: 1

REF: 159

5. Charts can be either embedded as an object on the same worksheet as the data, or placed on a separate worksheet referred to as a(n) ____________________.

ANS: chart sheet PTS: 1

REF: 163

6. The ____________________ chart type displays value trends in three dimensions. Values are indicated by areas with colors or patterns on the surface of the chart.

ANS: surface PTS: 1

REF: 164

7. The ____________________ chart type displays stock price and volume trends over time. Plotted values can include volume, opening price, highest price, lowest price, and closing price.

ANS: stock PTS: 1

REF: 164

8. The ____________________ chart type compares sets of three values. Values are indicated by the size of the filled circles.

ANS: bubble PTS: 1

REF: 164

9. An X Y (____________________) chart plots numeric values on both the x- and y-axes based on the value of the data.

ANS: Scatter scatter PTS: 1

REF: 164

10. The ____________________ dialog box, which is available from the shortcut menu for a selected chart, provides options for modifying the values used to generate a chart.

ANS: Select Data Source PTS: 1

REF: 171

11. Many element-related Excel chart options are found on the Chart ____________________ menu.

ANS: Elements PTS: 1

REF: 169

12. To control the display of individual labels in a chart, use the ____________________ options in Chart Elements.

ANS: Data Labels Data labels data labels PTS: 1

REF: 169

13. To modify how gridlines display in a chart’s plot area, use the ____________________ options in Chart Elements.

ANS: Gridlines gridlines PTS: 1

REF: 169

14. To change the thickness of a pie slice, change the value of the degree in the ____________________ box in the Format Chart Area task pane.

ANS: Perspective perspective PTS: 1

REF: 173

15. To display a moving average along with your chart data, use the ____________________ option in Chart Elements.

ANS: Trendline trendline PTS: 1

REF: 169

16. In the Format Data Labels task pane, use the ____________________ option to change the appearance of a data label’s 3-D format.

ANS: Effects effects PTS: 1

REF: 174

17. A(n) ____________________ combines the features of a line chart with a bar or column chart by filling in the area below the line, and displaying the trend of values over time or categories.

ANS: area chart PTS: 1

REF: 170

18. One available chart option is to create a 100% ____________________ chart, in which the plotted values are converted into percentages of the total amount within each category.

ANS: stacked PTS: 1

REF: 177

19. To change the chart sub-type, click the chart to display the CHART TOOLS contextual tabs on the ribbon, click the CHART TOOLS ___________ tab, and then click the Change Chart Type button in the Type group.

ANS: Design DESIGN PTS: 1

REF: 165

20. To emphasize categories of data and each category’s contribution to the total, create a(n) ____________________ pie chart by dragging a pie slice away from the other slices in the pie.

ANS: exploded PTS: 1 MATCHING

REF: 172 | 174

Using the above figure, identify the letter of the choice that best matches the figure. a. Data series f. Legend b. Chart title g. Y-axis c. Data table h. Y-axis labels d. X-axis labels i. Gridlines e. X-axis j. Data point with data label 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

#1 #2 #3 #4 #5 #6 #7 #8 #9 #10

1.

2.

3.

4.

5.

6.

7.

8.

9.

ANS: H ANS: G ANS: I ANS: B ANS: J ANS: A ANS: E ANS: C ANS: F

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF: REF:

157 157 157 157 157 157 157 157 157

10. ANS: D

PTS: 1

REF: 157

Identify the letter of the choice that best matches the phrase or definition. a. Compares values across categories in a c. Compares values across categories in a vertical orientation horizontal orientation b. Compares the contribution each value in a d. Displays trends over time or by category multiple numeric data series makes to the whole, or 100% 11.

12.

13.

14.

Column chart type Line chart type Bar chart type Doughnut chart type

11.

12.

13.

14.

ANS: A ANS: D ANS: C ANS: B

PTS: PTS: PTS: PTS:

1 1 1 1

REF: REF: REF: REF:

163 163 163 184

ESSAY 1. Briefly describe the eight common areas of a chart: chart title, y-axis, y-axis labels, x-axis, x-axis labels, data series, data points, and legend.

ANS: • Chart title: The descriptive text that identifies the chart’s contents • Y-axis: The vertical axis where data values are plotted • Y-axis labels: The labels that identify the data values plotted on the y-axis • X-axis: The horizontal axis where categories are plotted • X-axis labels: The labels that identify the categories plotted on the x-axis • Data series: The related data points that are plotted on the chart; each data series on a chart has a unique color or pattern and is identified in the chart legend • Data points: The points in a data series at which the x-axis and y-axis values intersect • Legend: A box that identifies the patterns or colors assigned to the data series in a chart PTS: 1

REF: 156 | 157

TOP: Critical Thinking

2. List and explain briefly the five guidelines given by Edward R. Tufte.

ANS: 1) Above all else show the data.

This is a reminder not to clutter a chart by adding unnecessary illustration or decoration.

2) Maximize the data-ink ratio, within reason.

This refers to the portion of the ink that is devoted to displaying the data versus the portion of a graphic that can be removed without losing the data.

3) Erase non-data-ink, within reason.

4) Erase redundant data ink, within reason.

The third and fourth data principles—“Erase non-data-ink” and “Erase redundant data ink”—are somewhat related to maximizing the data-ink ratio. Non-data-ink is a part of the chart that decorates more than informs. Redundant data ink is ink that repeats information.

5) Revise and edit.

Charts can be improved in much the same way that writing is improved: by revision and editing.

PTS: 1

REF: 158

TOP: Critical Thinking

3. Name and briefly describe five of the eleven chart types listed in the chapter.

ANS: • Column: Compares values across categories in a vertical orientation. Values are indicated by the height of the columns.

• Bar: Compares values across categories in a horizontal orientation. Values are indicated by the length of the bars.

• Line: Displays trends over time or by category. Values are indicated by the height of the lines.

• Area: Displays trends over time or by category. Values are indicated by the filled areas below the lines.

• Pie: Compares the contribution each value in a single numeric data series makes to the whole, or 100%. Values are indicated by the size of the pie slices.

• Doughnut: Compares the contribution each value in multiple numeric data series makes to the whole, or 100%. Values are indicated by the size of the doughnut segments.

• X Y (Scatter): Compares pairs of numeric values on the x- and y-axes, with the data points plotted proportionally to the values on the x-axis; can also be used to display a functional relationship, such as y=mx+b. Values are indicated by the position of the data points.

• Stock: Displays stock price and volume trends over time. Plotted values can include volume, opening price, highest price, lowest price, and closing price.

• Radar: Compares values across categories in a circular orientation. Values are indicated by the distance from a center point.

• Bubble: Compares sets of three values. Values are indicated by the size of the bubbles (filled circles).

• Surface: Displays value trends in three dimensions. Values are indicated by areas with colors or patterns on the surface of the chart.

PTS: 1

REF: 163 | 164

TOP: Critical Thinking

Chapter 4: Applying Logic in Decision Making TRUE/FALSE 1. TRUE and FALSE are referred to as Boolean logical values.

ANS: T

PTS: 1

REF: 213

2. Dun & Street (D&S) is one of the most widely used financial reporting services that provides, among other products, financial information about corporations and institutions.

ANS: F

PTS: 1

REF: 214

3. If you want to compare the value 3 to the value 5 to see if 3 is greater than 5, you would enter the following formula: =3>5.

ANS: T

PTS: 1

REF: 218

4. Relational operators can be used to evaluate dates.

ANS: T

PTS: 1

REF: 219

5. The AND function evaluates a list of logical arguments to determine if at least one of the arguments is TRUE. ANS: F

PTS: 1

REF: 221

6. The formula =OR(K3:K21) returns a TRUE value if any of the values in K3 through K21 contain the value TRUE. ANS: T

PTS: 1

REF: 221

7. For an OR function to be TRUE, at least one of its arguments must be TRUE. ANS: T

PTS: 1

REF: 222

8. The NOT function takes only one argument and essentially changes a single TRUE value to FALSE, or a single FALSE value to TRUE. ANS: T

PTS: 1

REF: 229

9. Excel provides unconditional formatting tools, which are tools you can use to identify a set of conditions and specify the formatting if those conditions are met.

ANS: F

PTS: 1

REF: 230

10. An IF function is a Boolean logical function that returns one value if a specified condition evaluates to TRUE, and the same value if the specified condition evaluates to FALSE. ANS: F

PTS: 1

REF: 243

11. The term parent-child means that a function contains additional formulas and/or functions as one or more of its arguments.

ANS: F

PTS: 1

REF: 243

12. The syntax of the IF function is =IF(logical_test,value_if_true,value_if_false).

ANS: T

PTS: 1

REF: 244

13. An IF function can be used to evaluate a logical test, such as determining if the 90-days past due balance is greater than 0, with only two resulting values: a value_if_true and a value_if_false.

ANS: T

PTS: 1

REF: 244

14. In a nested IF function, the second argument (value_if_true) and/or the third argument (value_if_false) cannot contain another IF function.

ANS: F

PTS: 1

REF: 253

15. In cases where you want to test criteria between a range of values, placing the logical tests in a specific order—either from highest to lowest or lowest to highest—can save a considerable amount of work.

ANS: T

PTS: 1

REF: 255

16. When laying out a complex IF function, a good way to proceed is to take the information and represent this process schematically in a bar chart.

ANS: F

PTS: 1

REF: 261

17. One approach to determining if none of the credit rules is TRUE for a specific customer is to figure out if each rule results in a FALSE value.

ANS: T

PTS: 1

REF: 261

18. The syntax of the NOT function evaluates only a single TRUE or FALSE value, not a range of values.

ANS: T

PTS: 1

REF: 229

19. To test if neither L3 nor M3 is TRUE, you can use the none of construct.

ANS: T

PTS: 1

REF: 262

20. Use the logical construct of OR(A1) to change a single TRUE to FALSE or a single FALSE to TRUE. ANS: F

PTS: 1

REF: 273

MODIFIED TRUE/FALSE 1. Some examples of relational logical functions are AND, OR, and NOT. ____________________ ANS: F Boolean

boolean PTS: 1

REF: 213

2. PAYDEX® provides a score from 1 to 100, with 100 representing the worst payment record. ____________________ ANS: F 1 one PTS: 1

REF: 215

3. The result of the formula =100>MAX(5,10,20) is FALSE. ____________________ ANS: F TRUE true True PTS: 1

REF: 218

4. In the formula =(1+2)/3<>10, the numbers 1 and 2 are added after they are divided by 3. ____________________ ANS: F, before PTS: 1

REF: 218

5. The function =C2="Hello" uses relational operators to evaluate text labels. ____________________ ANS: T

PTS: 1

REF: 218

6. The NOT function evaluates only one logical argument to determine if it is FALSE. ____________________ ANS: T

PTS: 1

REF: 221

7. =OR(I3>90,I4>90,I5>90,I6>90) returns a TRUE value if the values in cells I3, I4, I5, and I6 all contain values of 90 or less. ____________________ ANS: F FALSE False false PTS: 1

REF: 222 | 223

8. Each value in the AND function can consist of cell references or a range of cells and functions that reduce to a single TRUE or FALSE value. ____________________ ANS: F, argument

PTS: 1

REF: 225

9. For a(n) AND function to be TRUE, all of its arguments must be TRUE. ____________________ ANS: T

PTS: 1

REF: 225

10. Use the NOT function to change a TRUE value to FALSE or a FALSE value to TRUE. ____________________ ANS: T

PTS: 1

REF: 273

11. NOT(H2=2) returns the value FALSE assuming that cell H2 contains any value other than 2. ____________________ ANS: F TRUE True true PTS: 1

REF: 229

12. The Conditional Formatting tools allow you to insert colored bars (called info bars) in addition to standard formatting. ____________________ ANS: F, data bars PTS: 1

REF: 230

13. To apply an icon set and modify criteria rules, highlight the cell(s) to be formatted and then click the Conditional Formatting button to open the Conditional Formatting drop-down menu. ____________________ ANS: T

PTS: 1

REF: 231

14. The Highlight Cells Rules submenu options called Equal To identify either duplicate values with a specific format or unique values. ____________________ ANS: F Duplicate Values Duplicate values duplicate values PTS: 1

REF: 234

15. With conditional formatting, if both types of formatting cannot be applied to the cell simultaneously, the rule that appears first in the rules list overrides other rules for that specific cell. ____________________ ANS: T MULTIPLE CHOICE

PTS: 1

REF: 239

1. You can use the ____ logical functions AND, OR, and NOT. a. Boolean c. relational b. Bradstreet d. simple ANS: A

PTS: 1

REF: 221

2. Dun & Bradstreet’s ® credit rating includes a classification ranging from ____ indicating the net worth category of the company. a. 5A to HH c. 2B to 2Z b. 1A to ZZ d. 1 to 100 ANS: A

PTS: 1

REF: 214

3. Dun & Bradstreet’s ® credit rating includes a composite credit appraisal score that ranges from ____. a. 1 to 4 c. 1 to 100 b. 1 to 10 d. A to Z ANS: A

PTS: 1

REF: 214

4. The financial stress risk class is a rating from ____, indicating the risk of a company in financial distress, where 1 represents businesses with the lowest probability of risk. a. 1 to 5 c. 1 to 9 b. 1 to 4 d. 1 to 99 ANS: A

PTS: 1

REF: 215

5. The result of the formula =B3>=C3 where cell B3 contains the date 1/1/2010 and cell C3 contains the date 12/31/2009 is ____. a. TRUE c. ERROR b. FALSE d. EQUAL ANS: A

PTS: 1

REF: 218

6. The result of the formula =B3<C3 where cell B3=5 and cell C3=4 is ____. a. TRUE c. ERROR b. FALSE d. EQUAL ANS: B

PTS: 1

REF: 218

7. The result of the formula =C1<=D1 where cell C1 contains the label “AA” and cell D1 contains the label “BB” is ____. a. TRUE c. ERROR b. FALSE d. EQUAL ANS: A

PTS: 1

REF: 218

8. In the formula =(1+2)/3<>10, Excel evaluates ____ first. a. 1+2 c. 3<>10 b. 2/3 d. 3/3 ANS: A

PTS: 1

REF: 218

9. Relational expressions begin with a(n) ____. a. equal sign b. quotation mark ANS: A

PTS: 1

c. question mark d. parenthesis

REF: 218

10. The result of the formula =100>MAX(5,10,20) is ____. a. TRUE c. TRUE or FALSE b. FALSE d. ERROR ANS: A

PTS: 1

REF: 218

11. In the formula =B2=C2, the second equal sign is interpreted as ____. a. the beginning of a formula c. an absolute operator b. a relational operator d. a relative operator ANS: B

PTS: 1

REF: 218

12. The >, >=, <, and <= operators also work with text, where a greater value is one that appears later in the ____. a. alphabet c. worksheet b. formula d. logic ANS: A

PTS: 1

13. Relational expressions ____. a. are not case sensitive b. are case sensitive ANS: A

PTS: 1

14. Dates are stored as ____. a. text and numbers b. sequential numbers ANS: B

PTS: 1

REF: 218

c. only read uppercase letters d. only read lowercase letters REF: 219

c. text only d. a series of slashes and numbers REF: 219

15. If you entered the date 1/1/2014 directly into a formula, Excel interprets the entry as a ____. a. numeric expression c. both a and b b. date d. neither a nor b ANS: A

PTS: 1

REF: 219

16. Assuming that cell D4 contains the date 12/31/2013 and cell D1 contains the date 1/1/2014, you could write the formula =D4>=D1, and the result would be ____. a. TRUE c. EQUAL b. FALSE d. ERROR ANS: B

PTS: 1

REF: 219

17. Excel interprets the formula =Goodbye>Hello, with no quotation marks, by ____. a. looking for a range named Goodbye and a range named Hello b. looking for a range named Hello and a range named Goodbye c. returning the value TRUE d. returning the value FALSE ANS: A

PTS: 1

18. You can use dates in ____. a. relational expressions b. arithmetic expressions

REF: 219

c. both a and b d. neither a nor b

ANS: C

PTS: 1

REF: 219

19. When all of the operands copy relatively, no ____ references are required. a. absolute c. both a and b b. relative d. neither a nor b ANS: A

PTS: 1

REF: 220

20. An AND function returns a value of TRUE if ____. a. all arguments in the function are TRUE b. one or more of the arguments in the function are TRUE c. all arguments in the function are FALSE d. one or more of the arguments in the function are TRUE ANS: A

PTS: 1

REF: 221

21. The OR function returns a value of FALSE if ____. a. all arguments in the function are TRUE b. one or more of the arguments in the function are TRUE c. all arguments in the function are FALSE d. one or more of the arguments in the function are TRUE ANS: C

PTS: 1

REF: 221

22. The NOT function essentially changes the value ____. a. TRUE to FALSE c. either a or b b. FALSE to TRUE d. neither a nor b ANS: C

PTS: 1

REF: 221

23. When you encounter the keywords either and or (meaning if either condition is met) followed by a specified outcome, the situation indicates the need for a(n) ____ function. a. AND c. NOT b. OR d. IF ANS: B

PTS: 1

REF: 222

24. The formula =OR(25<24,MIN(1,10)<2,3<=2+1) returns a ____ value. a. TRUE c. numeric b. FALSE d. error ANS: A

PTS: 1

REF: 222

25. =OR(FALSE,TRUE,TRUE) results in the final value of ____. a. TRUE c. EQUAL b. FALSE d. ERROR ANS: A

PTS: 1

REF: 222

26. The result of the formula =OR(I3:I21>90), where cell I5 is 92, is ____. a. FALSE c. ERROR b. TRUE d. both a and b ANS: B

PTS: 1

REF: 222

27. With the AND function, arguments can consist of ____.

a. b. c. d.

cell references a range of cells containing Boolean logical values relational expressions all of the above

ANS: D

PTS: 1

REF: 225

28. The syntax of the AND function is as follows: ____. a. AND(logical1,logical2,...) c. AND(ref1,ref2,ref3,...) b. AND(value1,value2,...) d. AND(if then,false then) ANS: A

PTS: 1

REF: 225

29. A range can be used in a Boolean logical AND or OR function if the range refers to cells containing ____. a. text values c. numeric values b. TRUE or FALSE values d. absolute values ANS: B

PTS: 1

REF: 228

30. Excel ____ empty cells in a range of cells in an AND or OR function. a. ignores c. fills b. deletes d. highlights ANS: A

PTS: 1

REF: 228

31. The formula =NOT(K3:K21) will result in an answer of ____ if all cells in the range are TRUE. a. TRUE c. SUM b. FALSE d. none of the above ANS: D

PTS: 1

REF: 229

32. The syntax of the NOT function is as follows: ____. a. NOT(logical1) c. NOT(reference1) b. NOT(logical1,logical2,...) d. NOT(value1,value2,...)

ANS: A

PTS: 1

33. NOT(FALSE) returns the value ____. a. TRUE b. FALSE ANS: A

PTS: 1

REF: 229

c. EQUAL d. ERROR REF: 229

34. You can change the formatting of a cell quite easily using the formatting buttons on the ____ tab. a. HOME c. EDIT b. CREATE d. PROOF ANS: A

PTS: 1

REF: 230

35. Excel provides ____ tools, which are tools you can use to identify a set of conditions and specify the formatting if those conditions are met. a. Conditional Formatting c. Conditional Editing b. Unconditional Formatting d. Unconditional Editing ANS: A

PTS: 1

REF: 230

36. You can apply conditional formatting to cells based on ____. a. the value in a cell b. results of a specified formula that returns a Boolean value c. the name of the function used in the cell d. both a and b ANS: D

PTS: 1

REF: 230

37. If the conditions are not mutually exclusive, resulting in more than one specified condition being TRUE, the formatting ____. a. of the last specified rule overrides those of earlier rules b. of the first specified rule overrides those of later rules c. defaults back to match the rest of the spreadsheet d. is applied by merging the formatting of all of the conditions ANS: A

PTS: 1

REF: 230

38. One of the easiest ways to use conditional formatting is to apply preset formats such as ____. a. Data Bars c. Icon Sets b. Color Scales d. all of the above ANS: D

PTS: 1

REF: 230

39. To remove all of the conditional formatting on a worksheet, select the Conditional Formatting button, select ____ in the drop-down menu, and then click Clear Rules from Entire Sheet. a. Clear Rules c. Clear Editing b. Clear Formatting d. Clear ANS: A

PTS: 1

REF: 233

40. After establishing both the relational operator and the condition, you need to specify the formatting to be applied if the condition is met, including ____. a. font style and borders c. both a and b b. font type and font size d. neither a nor b ANS: A

PTS: 1

REF: 234

41. Use the ____ button to launch the New Formatting Rule dialog box. a. New Rule c. New Launch b. New Formatting d. New ANS: A

PTS: 1

REF: 237 | 238

42. Unlike cell referencing within a worksheet, the default for conditional formatting is a(n) ____ cell reference. a. relative c. complex b. absolute d. simple ANS: B

PTS: 1

REF: 238

43. After establishing the conditional formatting in one row, you can use the ____ tool to copy the formatting down the column to the remaining rows. a. Format Painter c. Format b. Paint d. Copy ANS: A

PTS: 1

REF: 238

44. In conditional formatting, the last rule created will appear ____ the list, unless you change the order of the rules. a. last in c. in the middle of b. first in d. below ANS: B

PTS: 1

REF: 239

45. The Conditional Formatting Rules Manager dialog box can be used to ____. a. create new rules c. change the order of these rules b. edit or delete existing rules d. all of the above ANS: D

PTS: 1

REF: 239

46. A(n) ____ function is a Boolean logical function that returns one value if a specified condition evaluates to TRUE, and another value if the specified condition evaluates to FALSE. a. IF c. COUNTIF b. AND d. NOT ANS: A

PTS: 1

REF: 243

47. The term ____ means that a function contains additional formulas and/or functions as one or more of its arguments. a. parent-child c. connected b. nested d. linked ANS: B

PTS: 1

REF: 243

48. In the formula =ROUND(AVERAGE(B1:B10),0), the function ____ is considered to be nested. a. ROUND c. ROUND(AVERAGE(B1:B10) b. AVERAGE d. both a and b ANS: B

PTS: 1

REF: 249 | 250

49. The first argument of the IF function is the logical_test, and can consist of ____. a. the value TRUE or FALSE b. a reference to a cell containing a TRUE or FALSE value c. a relational expression resulting in a single TRUE or FALSE value d. all of the above ANS: D

PTS: 1

REF: 244

50. If you want the argument to display a text label, you must enclose the text within quotation marks; otherwise, Excel interprets the text as a(n) ____. a. relational operator c. value b. named range d. error ANS: B

PTS: 1

REF: 244

51. To return a blank cell in an IF function, you can include ____. a. “ ” c. < > b. ? ? d. / / ANS: A

PTS: 1

REF: 245

52. To perform the task of displaying “credit approved” or “credit denied” based on the corresponding Boolean value in column H, you can write a formula in cell I3 containing an IF function, as follows: ____. a. =IF(H3,"credit approved","credit denied") b. =IF(H3,"credit denied","credit denied") c. =IF("credit denied","credit denied",H3) d. =IF H3 ("credit denied","credit denied") ANS: A

PTS: 1

REF: 245

53. In a decision tree, a logical test is represented by a(n) ____ shape. a. diamond c. rectangular b. circular d. oval ANS: A

PTS: 1

REF: 246

54. In a decision tree, if there is one action to take if the test is TRUE and another action to take if it is FALSE, these actions are presented in ____. a. rectangles c. ovals b. circles d. squares ANS: A

PTS: 1

REF: 246

55. When you are only comparing two values with only a TRUE or FALSE result, an IF function ____. a. is not necessary c. will result in an incorrect answer b. will make your formula simpler d. must be used ANS: A

PTS: 1

REF: 248

56. Choose a(n) ____ function if the problem requires you to make a decision and then generate different values depending on whether the logical test results in a TRUE or FALSE value. a. IF c. OR b. AND d. NOT ANS: A

PTS: 1

REF: 249

57. ____ is a kind of shorthand formula showing the structure of the formula without the syntactical details. a. Pseudocode c. Shorthand code b. Trial code d. Label code ANS: A

PTS: 1

REF: 252

58. In the formula =IF(F9>0,G$2,IF(E9>0,G$3,IF(D9>0,G$4,0))), Excel will evaluate the first IF function ____. a. first c. third b. second d. fourth ANS: A

PTS: 1

REF: 252 | 253

59. When using an IF function, if customers with overdue payments fall into only one category (90 days, 60 days, or 30 days), ____. a. you should test the 90-day condition first c. you should test the 30-day condition first b. you should test the 60-day condition first d. it doesn’t matter which condition is tested first

ANS: D

PTS: 1

REF: 254

60. In cases where you want to test criteria between a range of values, placing the logical tests in a specific order—either from highest to lowest or lowest to highest—____. a. can save a considerable amount of work b. will generate more work c. will have no bearing on the amount of work d. is not recommended ANS: A

PTS: 1

REF: 255

61. Up to the maximum of ____ levels of nesting can be used in a formula. a. 24 c. 60 b. 48 d. 64 ANS: D

PTS: 1

REF: 256

62. By nesting levels of ____ functions, multiple sets of criteria can be sequentially analyzed until a final value is returned. a. IF c. OR b. AND d. NOT ANS: A

PTS: 1

REF: 260

63. To help view certain areas of a worksheet, you can ____ panes in the worksheet so that you can focus more easily. a. freeze c. immobilize b. stick d. delete ANS: A

PTS: 1

REF: 261 | 262

64. You can flip the value of a rule from TRUE to FALSE by using the ____ function. a. NO c. FLIP b. NOT d. OR ANS: B

PTS: 1

REF: 263

65. In the construct =AND(NOT(K3),NOT(L3),NOT(M3)), you are listing ____. a. each cell separately c. cells as absolute references b. cell ranges d. cells as mixed references ANS: A

PTS: 1

REF: 263

66. Note that a(n) ____ construct always has a positive condition to be evaluated AND a negative condition to be evaluated. a. only c. or b. not d. and ANS: A

PTS: 1

REF: 264

67. To prove that both of these statements are TRUE—1) Either Rule #2 or Rule #3 is TRUE or both are TRUE, and 2) Rule #1 is FALSE—the correct formula is ____. a. AND(OR(Rule #2 is TRUE, Rule #3 is TRUE), Rule #1 is FALSE) b. AND(Rule #2 is TRUE, Rule #3 is TRUE), Rule #1 is FALSE) c. (OR(Rule #2 is TRUE, Rule #3 is TRUE), Rule #1 is FALSE) d. AND(AND(Rule #2 is TRUE, Rule #3 is TRUE), Rule #1 is FALSE)

ANS: A

PTS: 1

REF: 264 | 265

68. If you are using a none of construct and if one customer meets the criteria, the statement “none of the customers meet the criteria” is ____. a. TRUE c. EQUAL b. FALSE d. ERROR ANS: B

PTS: 1

REF: 265 | 266

69. When using the none of construct, if Rule #1, Rule #2, and Rule #3 all result in the value FALSE for a customer, then the statement “None of the rules are true” is ____. a. TRUE c. EQUAL b. FALSE d. ERROR ANS: A

PTS: 1

REF: 263

70. The syntax of the NOT function evaluates ____. a. a single TRUE or FALSE value c. two TRUE or FALSE values b. a range of values d. logical operators ANS: A

PTS: 1

REF: 263

Case-Based Critical Thinking Questions Case 4-1

Chloe is creating an Excel spreadsheet using a number of formulas to help make some business decisions regarding her company’s customers. Please refer to the figure above as you answer the questions below.

71. If any one value in column C, D, or E is FALSE, then that customer is going to be put on the Accounts Receivable Watch List. Chloe wants to return a value of TRUE for customers to be put on the watch list. She will write the formula for Aster Construction first, and then copy it to the other rows. The logical construct Chloe would use is ____. a. OR(NOT(C5),NOT(D5),NOT(E5)) c. AND(C5:E5) b. OR(C5:E5) d. AND(NOT(C5),NOT(D5),NOT(E5)) ANS: A

PTS: 1

REF: 273

TOP: Critical Thinking

72. Chloe also intends to send out a letter congratulating those customers who have the value TRUE in all three columns of C, D, and E. She now wants to write a formula to generate a TRUE value if a customer matches this criteria. The logical construct would be ____. a. AND(C5:E5) c. OR(C5:E5) b. AND(C5:C9) d. NOT(OR(C5:E5) ANS: A

PTS: 1

REF: 273

TOP: Critical Thinking

73. The CEO of Chloe’s company reviews the chart and hands her this formula: AND(C5,NOT(OR(C5:E5))). The formula will be entered for Aster Construction, and then copied to the remaining rows. Which companies, if any, will return a TRUE value as a result of this formula? a. Aster Construction c. Belami Homes b. Ember Trading Company d. Davis & Sons ANS: A

PTS: 1

REF: 273

TOP: Critical Thinking

74. Chloe also wants to know if total purchases ever fall below $150,000. The current value is $162,000.

She writes the formula ____. a. =SUM(B5:B9)<150 c. =SUM(B5:B9)<>150 b. =AND(B5:B9)<150 d. =AND(B5:B9)<=150 ANS: A

PTS: 1

REF: 218

TOP: Critical Thinking

75. Another way that Chloe could write the formula that would show if total purchases ever fell below $150,000 would be ____. The current value is $162,000. a. =B10<150 c. =AND(B10)<150 b. =SUM(B10)<150 d. =B10>150 ANS: A

PTS: 1

REF: 218

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 4-2

Nathan is in charge of collections, and is putting together a list of customers to call. Please refer to the figure above as you answer the questions below.

76. Nathan wants to develop a list of customers with past due balances. He will write the formula for one customer and copy it to the remaining rows. A correct formula would be ____. a. =B5<$B$11 c. =B5<>$B$11 b. =B5>$B$11 d. =B5=$B$11 ANS: A

PTS: 1

REF: 218

TOP: Critical Thinking

77. Nathan wants to fill in the words “Call” and “Don’t Call” on the spreadsheet based on the value in column C. He will list “Call” if the value is FALSE and “Don’t Call” if the value is TRUE. What is the formula Nathan could write?

a. =IF(C5,"Don’t Call","Call") b. =IF(C5,"Call","Don’t Call") ANS: A

PTS: 1

c. =AND(C5,"Don’t Call","Call") d. =AND(C5,"Call","Don’t Call") REF: 245

TOP: Critical Thinking

78. Instead of “Call,” Nathan has decided to list the due date for those customers who have a FALSE value in column C. Customers with a TRUE value in column C should still generate a “Don’t Call” value.

His formula now looks like ____. a. =IF(C5,"Don’t Call",B5) c. =AND(C5,B5,"Don’t Call") b. =IF(C5,B5,"Don’t Call") d. =AND(C5,"Don’t Call",B5) ANS: A

PTS: 1

REF: 245

TOP: Critical Thinking

79. One of the daily reports that is delivered to management every day lists any customer who has a balance due of $100,000 or more. If the customer has a high balance, the formula should return due date. Otherwise, the cell is left blank. Assuming that the balance due is in column D in the spreadsheet above, what would the formula look like? a. =IF(D5=100000,B5, " ") c. =IF(D5>=100000,B5," ") b. =IF(D5<99999,B5," ") d. =IF(D5," ",B5) ANS: C

PTS: 1

REF: 245

TOP: Critical Thinking

80. If Nathan entered the dates shown in column B directly into a formula, Excel would interpret the entry as a(n) ____. a. date c. error b. numerical expression d. text entry ANS: B

PTS: 1

REF: 219

TOP: Critical Thinking

COMPLETION 1. The ____________________ of a company is based on the assets and liabilities listed on its balance sheet.

ANS: net worth PTS: 1

REF: 214

2. The Dun & Bradstreet ® financial ____________________ risk class is a rating from 1 to 5 indicating the risk of a company in financial distress.

ANS: stress PTS: 1

REF: 215

3. The > symbol represents the ____________________ relational operator.

ANS: greater than PTS: 1

REF: 218

4. The <> symbol is the relational operator that means ____________________.

ANS: not equal to

PTS: 1

REF: 218

5. The result of the formula =(1+2)/3<>10 is ____________________.

ANS: TRUE True true PTS: 1

REF: 218

6. The formula ="Goodbye">"Hello" returns the value ____________________ because Goodbye comes alphabetically before Hello.

ANS: False false FALSE PTS: 1

REF: 218 | 219

7. The ____________________ function evaluates a list of logical arguments to determine if all of the arguments are TRUE. ANS: AND And and PTS: 1

REF: 221

8. The ____________________ function evaluates a list of logical arguments to determine if at least one argument is TRUE. ANS: OR Or or PTS: 1

REF: 221

9. The ____________________ function evaluates only one logical argument to determine if it is FALSE. ANS: NOT Not not PTS: 1

REF: 221

10. The ____________________ of the NOT function is as follows: NOT(logical1).

ANS: syntax PTS: 1

REF: 229

11. The formula NOT(7<10) returns the value ____________________.

ANS: FALSE false False PTS: 1

REF: 229

12. The Excel Conditional Formatting tools allow you to insert multicolor gradients called ____________________.

ANS: color scales PTS: 1

REF: 230

13. The Excel Conditional Formatting tools allow you to insert small graphics called ____________________.

ANS: icons PTS: 1

REF: 230

14. You can use relational operators and ____________________ functions to evaluate sets of conditions resulting in TRUE or FALSE values.

ANS: Boolean PTS: 1

REF: 242 | 243

15. A(n) ____________________ function is a Boolean logical function that returns one value if a specified condition evaluates to TRUE, and another value if the specified condition evaluates to FALSE. ANS: IF PTS: 1

REF: 243

16. The term ____________________ means that a function contains additional formulas and/or functions as one or more of its arguments.

ANS: nested PTS: 1

REF: 243

17. logical_test is the ____________________ argument of the IF function.

ANS: first 1st

1 PTS: 1

REF: 244

18. If logical_test results in a(n) ____________________ value, Excel evaluates the value_if_true argument.

ANS: TRUE true True PTS: 1

REF: 244

19. You can “flip” the value of a rule with a(n) ____________________ function to obtain a TRUE value if the rule had a FALSE value.

ANS: NOT not Not PTS: 1

REF: 262

20. Note that an only construct always has a positive condition to be evaluated and a(n) ____________________ condition to be evaluated.

ANS: negative PTS: 1

REF: 264

MATCHING

Identify the letter of the choice that best matches the common logical construct. a. #1 e. #5

b. #2 c. #3 d. #4

f. #6 g. #7 h. #8

1.

2.

3.

4.

5.

6.

7.

8.

NOT(OR(A1:A4)) AND(NOT(A1),NOT(A2),NOT(A3),NOT(A4)) NOT(AND(A1:A4)) AND(A1:A4) NOT(A1) OR(NOT(A1),NOT(A2),NOT(A3),NOT(A4)) AND(A1,NOT(OR(A1:A4))) OR(A1:A4)

1.

2.

3.

4.

5.

6.

7.

8.

ANS: D ANS: E ANS: F ANS: A ANS: C ANS: G ANS: H ANS: B

PTS: PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF: REF:

273 273 273 273 273 273 273 273

Identify the letter of the choice that best matches the relational operator used in the formula. a. =3+5=8 c. =B3<C3 where cell B3=5 and cell C3=4 b. =100>MAX(5,10,20) d. =SUM(3,7)<>10 9.

10.

11.

12.

Not equal to Equal to Less than Greater than

9.

10.

11.

12.

ANS: D ANS: A ANS: C ANS: B

PTS: PTS: PTS: PTS:

1 1 1 1

REF: REF: REF: REF:

218 218 218 218

ESSAY 1. List three types of conditional formatting and the steps you would take to apply them to your worksheet.

ANS: To apply Data Bars, Color Scales, or Icon Sets: 1. Highlight the cell or cells to be formatted.

2. Click the Conditional Formatting button in the Styles group on the HOME tab to open the drop-down menu.

3. Select Data Bars, Color Scales, or Icon Sets from the drop-down menu to open a gallery of options.

4. Point to a format to preview it in the selected cells, and click the desired format to apply it.

PTS: 1

REF: 230 | 231

TOP: Critical Thinking

2. Briefly describe five of the eight Highlight Cells Rules submenu options that are found on the Conditional Formatting drop-down menu. The options are Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values, and More Rules.

ANS: • Greater Than: Identify values greater than the specified value.

• Less Than: Identify values less than the specified value.

• Between: Set lower and upper limits inclusive.

• Equal To: Identify values equal to the specified value.

• Text that Contains: Identify text strings matching a specific string.

• A Date Occurring: Identify dates such as Today, Tomorrow, Last Week, Last Month, etc.

• Duplicate Values: Identify either duplicate values with a specific format or unique values.

• More Rules: Open the New Formatting Rule dialog box, which contains additional relational operators, including not equal to, not between, greater than or equal to, and less than or equal to. Also available are options to highlight blank or nonblank cells and cells with or without errors.

PTS: 1

REF: 233 | 234

TOP: Critical Thinking

3. Briefly describe each of the arguments in an IF function: IF(logical_test,value_if_true,value_if_false).

ANS: • The first argument of the IF function is the logical_test. This is the hypothesis to be tested, which results in a TRUE or FALSE value.

• The second argument of the IF function is the value_if_true. This value is applied only if the condition in the first argument is evaluated as TRUE. • The third argument of the IF function is the value_if_false. This value is applied only if the condition in the first argument is evaluated as FALSE. PTS: 1

REF: 244 | 245

TOP: Critical Thinking

Chapter 5: Retrieving Data for Computation, Analysis, and Reference TRUE/FALSE 1. A data list that categorizes values you want to retrieve is called a linkup table.

ANS: F

PTS: 1

REF: 283

2. A vertical lookup table is a lookup table in which the data to be searched is organized in columns.

ANS: T

PTS: 1

REF: 285

3. When you write a VLOOKUP formula, you indicate the value you want to look up in a table.

ANS: T

PTS: 1

REF: 286

4. If a lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP opens up the Lookup Wizard.

ANS: F

PTS: 1

REF: 288

5. When using a VLOOKUP function, it’s not always obvious when a returned value is incorrect.

ANS: T

PTS: 1

REF: 290

6. When you use a lookup type of TRUE, the VLOOKUP function looks only for an exact match of the lookup value.

ANS: F

PTS: 1

REF: 290

7. Headings at the top of each column should be included as part of the lookup_range in the VLOOKUP formula.

ANS: F

PTS: 1

REF: 293

8. When creating a vertical lookup table with the TRUE type, be certain to start with the lowest possible value, such as 0, so that the table covers all the possible data.

ANS: T

PTS: 1

REF: 293

9. The syntax of the HLOOKUP function is as follows: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

ANS: T

PTS: 1

REF: 295

10. The rules for an HLOOKUP function with a TRUE lookup type test the values down the first column.

ANS: F

PTS: 1

REF: 295 | 296

11. A table_array argument must contain relative cell references so that the row values will not change when the formula is copied down the column.

ANS: F

PTS: 1

REF: 297

12. You can use a VLOOKUP formula and an HLOOKUP formula to retrieve data stored in lookup tables on the same worksheet.

ANS: T

PTS: 1

REF: 299

13. Range names copy absolutely.

ANS: T

PTS: 1

REF: 302

14. Unlike VLOOKUP, the RLOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range.

ANS: F

PTS: 1

REF: 304

15. You can often use a LOOKUP function instead of a VLOOKUP or HLOOKUP function with a TRUE lookup type.

ANS: T

PTS: 1

REF: 304

16. LOOKUP has both a TRUE and a FALSE lookup type.

ANS: F

PTS: 1

REF: 304

17. The INDEX function allows you to retrieve data from multidimensional tables.

ANS: T

PTS: 1

REF: 307

18. In a one-dimensional table, Excel searches one dimension, such as the columns, and then searches another dimension, such as the rows, to find the value at the intersection of a single row and column.

ANS: F

PTS: 1

REF: 307

19. You cannot use VLOOKUP, HLOOKUP, or LOOKUP functions with a two-dimensional table.

ANS: T

PTS: 1

REF: 307

20. The INDEX function allows you to specify a list of nonadjacent ranges as the reference argument, and then specify which of these ranges to use in the area_num argument.

ANS: T

PTS: 1

REF: 309

MODIFIED TRUE/FALSE 1. The V in VLOOKUP stands for velocity. _________________________ ANS: F, vertical PTS: 1

REF: 285

2. In VLOOKUP, if a type FALSE is specified, an exact or approximate match is returned. _________________________

ANS: F, TRUE PTS: 1

REF: 288

3. Internal values are those pieces of data that you know, such as customer names, quantities, or product descriptions. _________________________ ANS: F, Key PTS: 1

REF: 285

4. When you use the HLOOKUP function, you specify the row_index_num. _________________________ ANS: T

PTS: 1

REF: 295

5. The Lookup function argument called lookup_vector is the location of the data you want to look up. _________________________ ANS: T

PTS: 1

REF: 304

6. In a(n) two-dimensional table, Excel can search a row or column to find key data, and then use that data to locate the correct value. _________________________ ANS: F, one PTS: 1

REF: 307

7. If you want to use the INDEX function to retrieve data stored in more than one range, be certain to store all the lookup ranges on the same computer. _________________________ ANS: F, worksheet PTS: 1

REF: 313

8. Excel provides nine functions, called the OF functions, that test a value or cell reference, and then return a TRUE or FALSE value depending on the results. _________________________ ANS: F, IS PTS: 1

REF: 323

9. You can use the ISEMPTY function to test a cell reference. _________________________ ANS: F ISBLANK isblank(value) PTS: 1

REF: 323

10. The IS function called ISLOGICAL is for values that refer to a logical value. _________________________

ANS: T

PTS: 1

REF: 323

11. A function named IFWRONG can be used in combination with the VLOOKUP function to simplify error checking. _________________________ ANS: F, IFERROR PTS: 1

REF: 325

12. The syntax of the CHOOSE function is as follows: =CHOOSE(index_num,value1,value2,…). _________________________ ANS: T

PTS: 1

REF: 328

13. The final argument of the VLOOKUP function is the range_lookup type, which can be TRUE or FALSE. _________________________ ANS: T

PTS: 1

REF: 330

14. The syntax of the INDEX function is as follows: INDEX(value,row_num,column_num,area_num). _________________________ ANS: F, reference PTS: 1

REF: 337

15. The MATCH function is designed to return the relative position (such as 1, 2, or 3) of an item in a list. _________________________ ANS: T

PTS: 1

REF: 339

MULTIPLE CHOICE 1. A data list that categorizes values you want to retrieve is called a ____ table. a. lookup c. query b. list d. help ANS: A

PTS: 1

REF: 283

2. You use VLOOKUP when ____ of the lookup table contains the data you are looking up. a. the first column c. any column b. the last column d. all columns ANS: A

PTS: 1

REF: 285

3. The data you are looking up is also called the ____ data. a. key c. important b. look d. clean ANS: A

PTS: 1

REF: 285

4. When you write a VLOOKUP formula, you indicate the value you want to look up in a ____. a. table c. graph b. chart d. list

ANS: A

PTS: 1

REF: 286

5. VLOOKUP searches for a matching value in the leftmost column of the table, and then retrieves the value ____. a. in the same row but in another column that you specify b. in the same column but in another row that you specify c. anywhere in the table d. in the same row, in the very next column ANS: A

PTS: 1

REF: 286

6. The syntax of the VLOOKUP function is as follows: =VLOOKUP(lookup_value,table_array,col_index_num,____). a. lookup_range c. row_index_num b. range_lookup d. table_value ANS: B

PTS: 1

REF: 286

7. In a VLOOKUP function, the argument ____ refers to the data you want to look up. a. lookup_value c. col_index_num b. table_array d. range_lookup ANS: A

PTS: 1

REF: 286

8. In a VLOOKUP function, the argument ____ refers to the range containing the data that you want to search to find the lookup value. a. lookup_value c. col_index_num b. table_array d. range_lookup ANS: B

PTS: 1

REF: 286

9. In a VLOOKUP function, the argument ____ refers to the number of the column containing the data you want to retrieve. a. lookup_value c. col_index_num b. table_array d. range_lookup ANS: C

PTS: 1

REF: 286

10. In a VLOOKUP function, the argument ____ refers to the type of lookup you want to perform—TRUE or FALSE. a. lookup_value c. col_index_num b. table_array d. range_lookup ANS: D

PTS: 1

REF: 286

11. With a ____ type of VLOOKUP, the function would look only for the exact match. a. FALSE c. complex b. TRUE d. simple ANS: A

PTS: 1

REF: 286

12. With a ____ type of VLOOKUP, the function returns the greatest value that does not exceed the lookup value. a. FALSE c. complex b. TRUE d. simple

ANS: B

PTS: 1

REF: 286

13. If a lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the ____ error value. a. #N/A c. #ERROR# b. ## d. @#@# ANS: A

PTS: 1

REF: 288

14. When creating a range for use with a type TRUE VLOOKUP table, make sure to sort the values in the first column of table_array in ____ order. a. descending c. alphabetical b. ascending d. numerical ANS: B

PTS: 1

REF: 289

15. When creating a range for use with a type FALSE VLOOKUP table, make sure to sort the values in the first column of table_array in ____ order. a. descending c. alphabetical b. ascending d. any (values do not need to be sorted) ANS: D

PTS: 1

REF: 289

16. In a FALSE VLOOKUP, if an exact match is not found, ____ is returned. a. an error value c. the first value in the column b. an approximate match d. the last value in the column ANS: A

PTS: 1

REF: 289

17. To find and retrieve data from a vertical lookup table in the most efficient way, create a simple worksheet that contains related data, and organize this data in ____ to form the vertical lookup table. a. columns c. either a or b b. rows d. neither a nor b ANS: A

PTS: 1

REF: 293

18. For a VLOOKUP table, list the key values in ____. a. the last column c. the first column b. any column d. the first row ANS: C

PTS: 1

REF: 293

19. A VLOOKUP table can have ____ key value(s) in each cell of its first column. a. one c. three b. two d. four or more ANS: A

PTS: 1

REF: 293

20. If you have a valid range that is not in ascending order, you can sort it using the ____ commands in the Editing group on the HOME tab on the ribbon. a. Sort c. Range b. Order d. Data ANS: A

PTS: 1

21. The H in HLOOKUP stands for ____.

REF: 294

a. help b. horizontal ANS: B

c. historic d. hit PTS: 1

REF: 295

22. When solving an HLOOKUP formula, Excel looks up a value by testing for a criterion across a ____. a. row c. table b. column d. worksheet ANS: A

PTS: 1

REF: 295

23. The syntax of the HLOOKUP function is as follows: =HLOOKUP(lookup_value,____,row_index_num,range_lookup). a. table_index c. table_range b. table_array d. table_row ANS: B

PTS: 1

REF: 295

24. The first row (key values) of the lookup table must be sorted in ____ order to use an HLOOKUP function with a TRUE lookup type. a. ascending c. alphabetical b. descending d. numerical ANS: A

PTS: 1

REF: 296

25. To return a negative value, ____ is a correct formula. a. =–HLOOKUP(F14,I$7:K$9,3,TRUE)*H14 b. =(NEGATIVE)HLOOKUP(F14,I$7:K$9,3,TRUE)*H14 c. =(0)HLOOKUP(F14,I$7:K$9,3,TRUE)*H14 d. none of the above ANS: A

PTS: 1

REF: 297

26. The table_array argument must contain ____ cell references so that the row values will not change when the formula is copied down the column. a. absolute c. simple b. relative d. complex ANS: A

PTS: 1

REF: 287

27. You can use VLOOKUP and HLOOKUP to retrieve data stored in lookup tables on ____. a. the same worksheet c. either a or b b. other worksheets d. neither a nor b ANS: C

PTS: 1

REF: 299

28. If the values you want to look up are in column B and the values you want to retrieve are in column A, you can use the ____ function. a. VLOOKUP c. either a or b b. LOOKUP d. neither a nor b ANS: B

PTS: 1

REF: 304

29. The ____ function looks up the greatest value that does not exceed a specified value anywhere in a table or range. a. HLOOKUP c. LOOKUP

b. VLOOKUP ANS: C

d. all of the above PTS: 1

REF: 304

30. The LOOKUP function uses a ____ lookup type, so the column or row containing the lookup values must be in ascending order. a. TRUE c. both a and b b. FALSE d. neither a nor b ANS: A

PTS: 1

REF: 304

31. The syntax of the LOOKUP function is as follows: =LOOKUP(____,lookup_vector,result_vector). a. lookup_value c. lookup_range b. lookup_reference d. lookup_index ANS: A

PTS: 1

REF: 304

32. In a LOOKUP function, the argument ____ refers to the data you want to look up. a. lookup_value c. result_vector b. lookup_vector d. lookup_range ANS: A

PTS: 1

REF: 304

33. In a LOOKUP function, the argument ____ refers to the location of the data you want to look up. a. lookup_value c. result_vector b. lookup_vector d. lookup_range ANS: B

PTS: 1

REF: 304

34. In a LOOKUP function, the argument ____ refers to the location of the data you want to retrieve. a. lookup_value c. result_vector b. lookup_vector d. lookup_range ANS: C

PTS: 1

REF: 304

35. You can only use a LOOKUP function when you want to retrieve a value that is stored to the ____ of a key data column in a vertical lookup table. a. left c. either a or b b. right d. neither a nor b ANS: A

PTS: 1

REF: 304

36. You can only use a LOOKUP function when you want to retrieve a value that is stored ____ a key data row in a horizontal lookup table. a. above c. either a or b b. below d. neither a nor b ANS: A

PTS: 1

REF: 304

37. As with the VLOOKUP and HLOOKUP functions with the TRUE lookup type, the ____ function looks for a value that matches the criterion by comparing values based on their position in the lookup table. a. POSITION c. LINK b. LOOKUP d. LIST ANS: B

PTS: 1

REF: 305

38. If the lookup_value is less than the smallest value in the lookup_vector, LOOKUP displays ____. a. an error symbol c. the number 0 b. the LOOKUP wizard d. the number 100 ANS: A

PTS: 1

REF: 305

39. The LOOKUP function has the ____ lookup type. a. TRUE c. either a or b b. FALSE d. neither a nor b ANS: A

PTS: 1

REF: 305

40. A ____ at the beginning of the formula indicates that Excel should display the results as a negative value. a. negative sign c. (0) b. double negative sign d. none of the above ANS: A

PTS: 1

REF: 306

41. The ____ function allows you to retrieve data from multidimensional tables. a. INDEX c. VLOOKUP b. LOOKUP d. HLOOKUP ANS: A

PTS: 1

REF: 307

42. In a(n) ____ table, Excel can search a row or column to find key data, and then use that data to locate the correct value. a. one-dimensional c. three-dimensional b. two-dimensional d. none of the above ANS: A

PTS: 1

REF: 307

43. In a(n) ____ table, Excel searches one dimension, such as the columns, and then searches another dimension, such as the rows, to find the value at the intersection of a single row and column. a. one-dimensional c. three-dimensional b. two-dimensional d. none of the above ANS: B

PTS: 1

REF: 307

44. In an INDEX function, the argument ____ refers to the range containing the data you want to find. a. reference c. column_num b. row_num d. area_num ANS: A

PTS: 1

REF: 309

45. In an INDEX function, the argument ____ refers to the number of the row in the range referenced in the first argument. a. reference c. column_num b. row_num d. area_num ANS: B

PTS: 1

REF: 309

46. In an INDEX function, the argument ____ refers to the column in the range referenced in the first argument. a. reference c. column_num b. row_num d. area_num

ANS: C

PTS: 1

REF: 309

47. In an INDEX function, the argument ____ refers to the part of a nonadjacent range referenced in the first argument. a. reference c. column_num b. row_num d. area_num ANS: D

PTS: 1

REF: 309

48. The INDEX function allows you to specify a list of nonadjacent ranges as the reference argument, and then specify which of these ranges to use in the ____ argument. a. reference c. column_num b. row_num d. area_num ANS: D

PTS: 1

49. The INDEX function can solve for ____. a. no variables b. one variable only ANS: D

PTS: 1

REF: 309

c. one or two variables only d. two or more variables REF: 311

50. With the INDEX function, if you are using a noncontiguous range, you must enclose the entire reference in ____. a. quotation marks c. parentheses b. brackets d. none of the above ANS: C

PTS: 1

REF: 313

51. With the INDEX function, if you specify ____ contiguous range(s), you can omit the area_num argument. a. only one c. two or more b. one or two d. three or more ANS: A

PTS: 1

REF: 313

52. You can use the INDEX function to look up data stored in a single row, such as B3:E3, or a single column, such as B3:B7. In this case, you enter the range as the ____ argument. a. first c. third b. second d. fourth ANS: A

PTS: 1

REF: 313

53. When using an INDEX function, if you enter a row_num, column_num, or area_num outside of the range you referenced in the first argument, Excel displays ____. a. an error message c. the next nearest column or row b. the INDEX wizard d. none of the above ANS: A

PTS: 1

REF: 313

54. When using an INDEX function, you can use a row_num of ____ to retrieve all the values in a specified column. a. 0 c. 10 b. .01 d. 100 ANS: A

PTS: 1

REF: 313

55. If you use an INDEX formula that refers to ranges on separate worksheets, Excel displays the ____ error message. a. #VALUE c. #ERROR b. #REF! d. none of the above ANS: A

PTS: 1

REF: 314

56. Excel provides nine functions, called the ____ functions, that test a value or cell reference and then return a TRUE or FALSE value depending on the results. a. IS c. OR b. IF d. OF ANS: A

PTS: 1

REF: 323

57. The ISBLANK function checks whether a specified value refers to an empty cell. The syntax of this function is ____. a. =ISBLANK(value) c. =ISBLANK(reference) b. =ISBLANK(value,reference) d. =ISBLANK(reference,value) ANS: A

PTS: 1

REF: 323

58. In the formula =IF(ISBLANK(A9)," ",VLOOKUP(A9,Products,2,FALSE)), if cell A9 is blank, Excel displays ____. a. a string of empty characters c. the word “FALSE” b. the word “products” d. the error “#VALUE” ANS: A

PTS: 1

REF: 323

59. The function named IFERROR combines aspects of the IF function and the ____ function. a. OFERROR c. ISBLANK b. ISERROR d. VLOOKUP ANS: B

PTS: 1

REF: 325

60. The ISBLANK function tests for a(n) ____. a. blank cell b. error message resulting from the use of a blank cell in your formula c. both a and b d. neither a nor b ANS: A

PTS: 1

REF: 323

61. The syntax of the IFERROR function is ____. a. =IFERROR(value_if_error,value) c. =IFERROR(value,range) b. =IFERROR(value,value_if_error) d. =IFERROR(value,vector_lookup) ANS: B

PTS: 1

REF: 325

62. The CHOOSE function can return a value or a range for up to ____ different values. a. 12 c. 202 b. 157 d. 254 ANS: D

PTS: 1

REF: 328

63. The syntax of the CHOOSE function is ____.

a. =CHOOSE(index_num,value1,value2,…) c. =CHOOSE(value1,value2,...) b. =CHOOSE(num_index,value1,value2,...) d. =CHOOSE(range_num,value1,value2,...)

ANS: A

PTS: 1

REF: 328

64. When using the CHOOSE function, if the index_num argument is 2, the function returns ____. a. the first value from the list c. an error message b. the second value from the list d. the first value that equals “2” ANS: B

PTS: 1

REF: 329

65. When using the CHOOSE function, if the index_num argument is less than 1 or greater than the last value in the list, the function displays the error ____. a. #VALUE! c. #REF b. #NAME d. #ERROR ANS: A

PTS: 1

REF: 329

66. When using the CHOOSE function, if the index_num is a fraction, it is truncated to ____ before being used. a. an integer c. the first decimal place b. zero d. the second decimal place ANS: A

PTS: 1

REF: 329

67. The ____ function is designed to return the relative position (such as 1, 2, or 3) of an item in a list. a. MATCH c. POSITION b. RELATIVE d. LIST ANS: A

PTS: 1

REF: 339

68. If you use a match_type of 0 (exact match), you can use the ____ to match any sequence of characters. a. asterisk (*) c. exclamation point (!) b. question mark (?) d. dollar sign ($) ANS: A

PTS: 1

REF: 340

69. When testing a worksheet, it’s best to use numbers like ____. a. 10, 100, 1000 c. 13, 27, 88 b. 12, 124, 248 d. 1, 77, 129 ANS: A

PTS: 1

REF: 344

70. One effective way to verify accuracy in a worksheet is to create an order with ____ set(s) of entries, and then systematically vary each entry to test each formula and cell that contains a value. a. one c. three b. two d. four or more ANS: A

PTS: 1

Case-Based Critical Thinking Questions Case 5-1

REF: 344

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount “0” in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.

71. Violet wants to send any orders for 30 toys or fewer to Warehouse 1 to ship out. To do this, she locates the position in the chart where the greatest value does not exceed 30. The formula is ____. a. =MATCH(30,A1:E1,0) c. =MATCH(30>,A1:E1,1) b. =MATCH(30,A1:E1,1) d. =MATCH(30<A1:E1,0) ANS: B

PTS: 1

REF: 341

TOP: Critical Thinking

72. Violet now wants to send any orders for more than 30 toys to Warehouse 2. She is looking for the position where the smallest value is greater than 30. She will send the orders starting from that position on to Warehouse 2. The correct formula would be ____. a. =MATCH(31,A1:E1,-1) c. =MATCH(31>=A1:E1,-1) b. =MATCH(31,A1:E1,0) d. =MATCH(31<=A1:E1,0) ANS: A

PTS: 1

REF: 341

TOP: Critical Thinking

73. The shipping manager asks Violet to send any orders for 100 toys over immediately because they are testing a new box that will hold exactly 100 toys. In order to determine the position in the chart for the order with 100 toys, she writes the formula ____. a. =MATCH(4,A1:E1,0) c. =MATCH(100,A1:E1,-1) b. =MATCH(100,A1:E1,0) d. =MATCH(100{A1,E1},-1) ANS: B

PTS: 1

REF: 341

TOP: Critical Thinking

74. The Customer Service department tells Violet there is a 25% discount on orders of 500 toys or more.

Violet knows there is only one order that large, for 1000 toys, so which of the following formulas does she write to find the position of this order? a. =MATCH(1000,25%,A1:E1,1) c. =MATCH(1000,{1000,100,50,25,0},0) b. =MATCH(1000,A1:E1,0) d. =MATCH(A1:E1,0,1000) ANS: B

PTS: 1

REF: 341

TOP: Critical Thinking

75. The Sales department has been mistakenly writing the word “return” in the quantity field for orders that are returned. Violet looks at the chart above and can see that the word “return” is not listed this time. However, to check for this word in future orders, Violet writes the formula ____. a. =MATCH("return",A1:E1,0) c. =MATCH(A1:E1,0,"return") b. =MATCH(return,A1:E1,0) d. =MATCH("return",0:1000,0) ANS: A

PTS: 1

REF: 341

TOP: Critical Thinking

76. Violet is not sure if the Sales department is filling in the word “return” in uppercase or lowercase letters. What is the correct way to write “return” in a MATCH formula? a. return b. RETURN c. Return d. It doesn’t matter; the MATCH formula does not distinguish between upper- and lowercase letters.

ANS: D

PTS: 1

Case-Based Critical Thinking Questions Case 5-2

REF: 340

TOP: Critical Thinking

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below.

77. Mark writes a formula to look up the unit pricing for each order. He starts by writing one for Order #1 and then plans to copy it from E12 to E13:E16. The correct formula would be ____. a. =VLOOKUP(D12,A$7:B$8,3,TRUE) c. =VLOOKUP(D12,A$7:B$8,0,TRUE) b. =VLOOKUP(D12,A$7:B$8,2,TRUE) d. =VLOOKUP(D12,A$7:B$8,1,TRUE) ANS: B

PTS: 1

REF: 288

TOP: Critical Thinking

78. One of Mark’s co-workers asks what would happen if he changed the formula to one having a FALSE type. As an example for discussion, Mark writes: =VLOOKUP(Z10,H$1:I10,1,FALSE). How does the FALSE type affect this formula? a. The VLOOKUP function looks only for an exact match of the lookup_value. b. The VLOOKUP function looks only for negative values in the lookup_value. c. The VLOOKUP function looks for the first value that is greater than the lookup_value. d. The VLOOKUP function looks for the first value that is less than the lookup_value.

ANS: A

PTS: 1

REF: 288

TOP: Critical Thinking

79. In the formula Mark is discussing with his co-worker, =VLOOKUP(Z10,H$1:I10,1,FALSE), the lookup_value is ____. a. Z10 c. 1 b. H$1:I$10 d. FALSE ANS: A

PTS: 1

REF: 286

TOP: Critical Thinking

80. Mark tests the following formula with a FALSE type in the worksheet shown above: =VLOOKUP(D14,A$3:B$8,2,FALSE). The result is ____. a. a price of $4.75 c. the #N/A error message b. a price of $5.00 d. a price of $4.50 ANS: A COMPLETION

PTS: 1

REF: 292

TOP: Critical Thinking

1. A data list that categorizes values you want to retrieve is called a(n) ____________________ table.

ANS: lookup PTS: 1

REF: 283

2. Use ____________________ when the first column of the lookup table contains the key data, and the information you want to retrieve is in one of the columns to the right of the key data column.

ANS: VLOOKUP PTS: 1

REF: 285

3. In the VLOOKUP function, if a type ____________________ is specified, an exact or approximate match is returned.

ANS: TRUE PTS: 1

REF: 288

4. A lookup table can have only one ____________________ in each cell of its first column.

ANS: key value PTS: 1

REF: 293

5. When solving a(n) ____________________ formula, Excel looks up a value by testing for a criterion across a row, instead of down a column.

ANS: HLOOKUP PTS: 1

REF: 295

6. The LOOKUP function uses only a TRUE lookup type, so the column or row containing the lookup values must be in ____________________ order.

ANS: ascending PTS: 1

REF: 304

7. The INDEX function allows you to retrieve data from ____________________ tables.

ANS: multidimensional PTS: 1

REF: 307

8. The INDEX function argument ____________________ refers to the number of the row in the range referenced in the first argument.

ANS: row_num PTS: 1

REF: 309

9. With the INDEX function, you can use a column_num of ____________________ to retrieve all the values in a specified row.

ANS: 0 zero PTS: 1

REF: 313

10. If you use an INDEX formula that refers to ranges on separate worksheets, Excel displays the ____________________ error.

ANS: #VALUE PTS: 1

REF: 314

11. You can use the ____________________ function to test a cell reference. If the cell is blank, or empty, the function returns the value TRUE. ANS: ISBLANK PTS: 1

REF: 323

12. You could write a formula using the ____________________ function to check whether cell A5 contains text.

ANS: ISTEXT PTS: 1

REF: 323

13. The ISBLANK function checks whether a specified value refers to an empty cell. The syntax of this function is as follows: =ISBLANK(____________________).

ANS: value PTS: 1

REF: 323

14. The IS function where the value refers to a reference is ____________________.

ANS: ISREF PTS: 1

REF: 323

15. With ____________________, you can specify your own text (or even a blank cell) when an error is encountered rather than the standard Excel message.

ANS: IFERROR PTS: 1

REF: 325

16. The function =____________________(index_num,value1,value2,…) can return up to 254 values.

ANS: CHOOSE

PTS: 1

REF: 328

17. The final argument of the VLOOKUP function is the ____________________ type, which can be TRUE or FALSE. ANS: range_lookup PTS: 1

REF: 330

18. If a product or company has more than three or four pricing structures, a relational database, such as Microsoft ____________________, might be a better choice than using a spreadsheet program.

ANS: Access PTS: 1

REF: 333

19. The ____________________ function returns the relative position of an item in a list, and can be used instead of LOOKUP, VLOOKUP, or HLOOKUP when you need the position of an item in a range instead of the item itself.

ANS: MATCH PTS: 1

REF: 340

20. When using the MATCH function, the lookup_array must be enclosed in ____________________ if the values are not contiguous.

ANS: curly braces braces curly brackets {} PTS: 1

REF: 342

MATCHING Identify the letter of the choice that best matches the definition of the word or phrase. a. vertical lookup table e. MATCH function b. horizontal lookup table f. CHOOSE function c. LOOKUP function g. IFERROR d. INDEX function 1.

2.

3.

4.

5.

6.

7.

Looks up the greatest value that does not exceed a specified value anywhere in a table or range Allows you to retrieve data from multidimensional tables Can return a value or a range for up to 254 different values Allows you to specify your own text when an error is encountered A lookup table in which the data to be searched is organized in columns Designed to return the relative position (such as 1, 2, or 3) of an item in a list A lookup table in which the data to be searched is organized in rows

1. ANS: C 2. ANS: D

PTS: 1 PTS: 1

REF: 304 REF: 307

3.

4.

5.

6.

7.

ANS: F ANS: G ANS: A ANS: E ANS: B

PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1

REF: REF: REF: REF: REF:

328 325 285 339 295

1. This function allows you to retrieve data from multidimensional tables.

2. The range containing the data you want to find. The range can be a contiguous range or a set of non adjacent ranges.

3. The number of the row in the range referenced in the first argument. You number the rows within the range, not the worksheet, so the first row of the range is row 1, even if it is stored in a different row in the worksheet.

4. The number of the column in the range referenced in the first argument. You number the columns within the range, not the worksheet, so the first column of the range is column 1, even if it is stored in a different column in the worksheet.

5. The part of nonadjacent range referenced in the first argument. You use this argument only if you specified a nonadjacent range in the first argument. Use an area_num of 1 to indicate the first part of a nonadjacent range, 2 to indicate the second part, and so on.

Identify the letter of the choice that best matches the function definition and arguments. a. #1 d. #4 b. #2 e. #5 c. #3 8.

9.

10.

11.

12.

reference column_num row_num area_num INDEX

8.

9.

10.

11.

12.

ANS: B ANS: D ANS: C ANS: E ANS: A

PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1

REF: REF: REF: REF: REF:

309 309 309 309 308

ESSAY 1. Write the correct syntax for the VLOOKUP function. Give a brief explanation of each of the four arguments that are included in VLOOKUP. ANS: The correct syntax: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) • lookup_value: The data you want to look up. This value can be a number, text, a logical value, or a name or cell reference that refers to a value.

• table_array: The range containing the data that you want to search to find the lookup value. This range must start in the column with the lookup values and extend at least as far as the column containing the data to be returned.

• col_index_num: The number of the column containing the data you want to retrieve. The number 1 indicates the first column of the lookup table, 2 indicates the second column, and so on.

• range_lookup: The type of lookup you want to perform—TRUE or FALSE. With a TRUE type (the default), the VLOOKUP function finds the greatest value that does not exceed the lookup_value.

When the lookup type is TRUE, the values in the first column of the lookup table must be sorted in ascending sort order; otherwise, VLOOKUP might not retrieve the correct value. With a FALSE type, the VLOOKUP function looks only for an exact match of the lookup_value. If it does not find an exact match, the text #N/A is displayed in the cell.

PTS: 1

REF: 286

TOP: Critical Thinking

2. Write the correct syntax for the LOOKUP function. Give a brief explanation of each of the three arguments that are included in LOOKUP. ANS: Correct syntax: =LOOKUP(lookup_value,lookup_vector,result_vector) • lookup_value: The data you want to look up. This value can be a number, text, a logical value, or a cell reference or a name that refers to a value.

• lookup_vector: The location of the data you want to look up. This location is a range of only one row or column that contains the value you want to look up.

• result_vector: The location of the data you want to retrieve. This location is a range of only one row or column that contains the data you want to retrieve. This range must be the same size as the lookup_vector; for example, if there are 10 values in the lookup_vector, there must be 10 corresponding values in the result_vector.

PTS: 1

REF: 304

TOP: Critical Thinking

3. Briefly describe five of the nine IS functions. The options are: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, and ISTEXT. ANS: • ISBLANK: The value refers to an empty cell.

• ISERR: The value refers to any error value except #N/A. • ISERROR: The value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

• ISLOGICAL: The value refers to a logical value.

• ISNA: The value refers to the #N/A (value not available) error value.

• ISNONTEXT: The value refers to any item that is not text (this function returns TRUE if the value refers to a blank cell).

• ISNUMBER: The value refers to a number.

• ISREF: The value refers to a reference.

• ISTEXT: The value refers to text.

PTS: 1

REF: 323

TOP: Critical Thinking

Chapter 6: Evaluating the Financial Impact of Loans and Investments TRUE/FALSE 1. Money is usually not the only component in a business decision.

ANS: T

PTS: 1

REF: 359

2. When you borrow money from a bank to make a purchase, you must pay back the amount borrowed plus an additional amount known as principal.

ANS: F

PTS: 1

REF: 360

3. Most financial instruments, such as loans and CDs, use compound interest.

ANS: T

PTS: 1

REF: 361

4. Financial institutions advertise their loan interest rates in what is referred to as an annual loan plan, or ALP. ANS: F

PTS: 1

REF: 362

5. The PMT function finds the value of the payment per period, assuming that there are constant payments and a constant interest rate for the duration of the loan.

ANS: T

PTS: 1

REF: 364

6. When cash is received, it is considered negative cash flow.

ANS: F

PTS: 1

REF: 365

7. $10,000 compounded 1 time at 4% per period is different from $10,000 compounded 4 times at 1% per period; the latter results in a higher value.

ANS: T

PTS: 1

REF: 361

8. =NPER(rate,pmt,pv,fv,type) solves for the interest rate per period.

ANS: F

PTS: 1

REF: 368

9. The syntax of the FV function is =FV(rate,nper,pmt,[pv],[type]).

ANS: T

PTS: 1

REF: 368

10. The present value is the amount, or initial outlay, at the beginning of the transaction.

ANS: T

PTS: 1

REF: 371

11. A down payment is money required from the lender toward the purchase of an asset.

ANS: F

PTS: 1

REF: 375

12. Corporate taxes allow companies to allocate portions of the capital investment as an expense, thereby reducing the amount of taxes owed.

ANS: T

PTS: 1

REF: 379

13. Depreciation must be added to the cash flow in each year to calculate taxes for that year, but then must be subtracted because it is not actually a cash flow in that year.

ANS: F

PTS: 1

REF: 381

14. The fv argument is the value at the end of the financial transaction.

ANS: T

PTS: 1

REF: 385

15. Principal payments represent negative cash flows.

ANS: T

PTS: 1

REF: 389

16. In Excel 2013, using the Define Name button and then typing a name in the Name field allows the user to specify a named range scope.

ANS: T

PTS: 1

REF: 393

17. The net present value is an interest rate chosen to reflect not only the time value of money, but the desired returns the company expects for the level of risk being taken.

ANS: F

PTS: 1

REF: 403

18. The IRR method calculates the rate at which discounted cash flows in and out are equal, essentially where the NPV is $0.

ANS: T

PTS: 1

REF: 407

19. ROI is the sum of the cash flows, including the initial investment, divided by the investment value.

ANS: F

PTS: 1

REF: 410

20. Chart data series ranges include both sheet names and absolute cell references.

ANS: T

PTS: 1

REF: 414

MODIFIED TRUE/FALSE 1. The payback period is the value of the loan or investment. _________________________ ANS: F, principal PTS: 1

REF: 360

2. The rate per period is the monthly rate divided by the number of periods per year. _________________________ ANS: F

annual yearly PTS: 1

REF: 363

3. PV solves for the final value, which is the amount in or out at the end of the financial transaction. _________________________ ANS: F FV fv PTS: 1

REF: 368

4. The syntax of the FV function is =FV(rate,nper,pmt,[fv],[type]). _________________________ ANS: F pv [pv] PTS: 1

REF: 368

5. To calculate an interest rate, you can use the INTEREST function. _________________________ ANS: F RATE rate PTS: 1

REF: 368

6. A(n) balloon payment is additional money owed at the end of a loan. _________________________ ANS: T

PTS: 1

REF: 375

7. When calculating taxes in the United States, a company can subtract from income only the principal portion of a loan payment. _________________________ ANS: F, interest PTS: 1

REF: 379

8. The IPMT function calculates the value of the interest payment for a specified period. _________________________ ANS: T

PTS: 1

REF: 385

9. =CIPMT(rate,nper,pv,start_period,end_period,type) returns the cumulative interest paid on a loan between start_period and end_period. _________________________ ANS: F CUMIPMT cumipmt

PTS: 1

REF: 387

10. Bottom line depreciation basically allocates the value of an asset evenly throughout the life of the asset. _________________________ ANS: F Straight line Straight-line PTS: 1

REF: 390

11. Range names must start with a letter, an underscore, or a(n) backslash. _________________________ ANS: T

PTS: 1

REF: 394

12. The variable-declining balance method returns the depreciation of an asset for a specified period. _________________________ ANS: F, fixed PTS: 1

REF: 395

13. NPV enables you to see the current worth of the projected cash flows, which helps you to determine the profitability of the venture. _________________________ ANS: T

PTS: 1

REF: 403

14. The NPV function in Excel takes the cash flow from year 0, which is referred to as value0, and discounts the value by one year. _________________________ ANS: F 1 one PTS: 1

REF: 405

15. The values argument in the IRR function is a list of positive and negative cash flows. _________________________ ANS: T

PTS: 1

REF: 408

MULTIPLE CHOICE 1. ____ is like a user fee because you are paying to “use” the bank’s money. a. Interest c. Cash flow b. Principal d. Depreciation ANS: A

PTS: 1

REF: 360

2. The ____ is the value of the loan or investment. a. principal c. cash flow b. interest d. depreciation ANS: A

PTS: 1

REF: 360

3. Interest that is paid solely on the amount of the original principal value is called ____ interest. a. simple c. compound b. complex d. principal ANS: A

PTS: 1

REF: 360

4. Adding interest earned each period to the principal for purposes of computing interest for the next period is known as ____ interest. a. compound c. complex b. simple d. principal ANS: A

PTS: 1

REF: 361

5. ____ is the equivalent of a yearly simple interest rate, taking into account compounding. a. Annual percentage yield c. Annual compound interest b. Annual cash flow d. Annual simple interest ANS: A

PTS: 1

REF: 362

6. The APR reflects the ____ being paid on the actual amount borrowed. a. principal c. cash flow b. interest d. depreciation ANS: B

PTS: 1

REF: 362

7. The ____ function finds the value of the payment per period, assuming that there are constant payments and a constant interest rate for the duration of the loan. a. PAY c. PAYMENT b. CASH d. PMT ANS: D

PTS: 1

REF: 368

8. In a PMT function, the ____ argument is the interest rate per compounding period. a. rate c. pv b. nper d. type ANS: A

PTS: 1

REF: 364

9. In a PMT function, the ____ argument is the number of compounding periods. a. rate c. pv b. nper d. type ANS: B

PTS: 1

REF: 364

10. In a PMT function, the ____ argument is the original principal value at the beginning of the financial transaction. a. rate c. pv b. nper d. type ANS: C

PTS: 1

REF: 364

11. In a PMT function, the ____ argument designates when payments are made. a. rate c. fv b. nper d. type ANS: D

PTS: 1

REF: 364

12. In a PMT function, the ____ argument is the value at the end of the financial transaction. a. rate c. fv b. nper d. type ANS: C

PTS: 1

REF: 364

13. Cash amounts that are either received or paid out during the course of the financial transaction are often referred to as ____. a. cash flow c. in and out money b. cash movement d. business money ANS: A

PTS: 1

REF: 365

14. When cash is received, it is considered to be ____. a. positive cash flow c. in money b. negative cash flow d. positive cash source ANS: A

PTS: 1

REF: 365

15. When cash is paid out, it is considered to be ____. a. positive cash flow c. out money b. negative cash flow d. bad cash ANS: B

PTS: 1

REF: 365

16. When used in the PMT function, the comma in 1,000 is interpreted as ____. a. the beginning of a new argument c. part of the number b. the end of a new argument d. none of the above ANS: A

PTS: 1

REF: 367

17. In financial functions, the default transaction (type ____) indicates payments are made at the end of each period. a. 0 c. 1 b. d. end ANS: A

PTS: 1

REF: 368

18. In financial functions, type ____ indicates payments are made at the beginning of each period. a. 0 c. 1 b. d. start ANS: C

PTS: 1

REF: 369

19. A loan that is completely paid off would have an fv of ____. a. 0 c. the monthly payment b. the original amount of the loan d. none of the above ANS: A

PTS: 1

REF: 369

20. A bank account would have an fv equal to the ____ plus any accrued interest, plus or minus any payments into or out of the account. a. pv c. pmt b. rate d. fv ANS: A

PTS: 1

REF: 369

21. If you are calculating payments to a bank (a negative cash flow), your formula requires a positive ____. a. present value (pv) c. present value (pv) and future value (fv) b. future value (fv) d. any of the above ANS: D

PTS: 1

REF: 370

22. If you are calculating a positive future value (money being withdrawn from a bank, which is a positive cash flow), your formula requires a negative ____. a. present value (pv) c. present value (pv) and payment (pmt) b. payment (pmt) d. any of the above ANS: D

PTS: 1

REF: 370

23. A house that costs $200,000 and requires a 10% down payment would have an original loan value (pv) of ____. a. $180,000 c. $210,000 b. $190,000 d. $220,000 ANS: A

PTS: 1

REF: 375

24. The formula =PMT(0.03/12,12*3,15000,–2000) includes a ____. a. balloon payment c. down payment b. depreciation factor d. none of the above ANS: A

PTS: 1

REF: 375

25. ____ include(s) points, loan application fees, or any other fee that the borrower must pay up front. a. Mortgage loans c. Upfront interest b. Mortgage interest d. none of the above ANS: D

PTS: 1

REF: 375

26. To account for a down payment, adjust the ____ of the loan by subtracting it from the loan amount. a. present value (pv) c. rate b. future value (fv) d. type ANS: A

PTS: 1

REF: 375

27. Taxes are calculated based on _____ income, which excludes certain elements that are cash flows and includes other non-cash flow items. a. regular c. standard b. taxable d. itemized ANS: B

PTS: 1

REF: 380

28. The ____ of a loan is NOT directly tax deductible. a. principal payment c. both a and b b. interest d. neither a nor b ANS: A

PTS: 1

REF: 381

29. Depreciation is the process by which a company spreads the ____ of an asset over its useful life. a. value c. both a and b b. expense d. neither a nor b

ANS: B

PTS: 1

REF: 381

30. Depreciation needs to be _____ the cash flow in each year to calculate taxes for that year. a. added to c. either a or b b. subtracted from d. neither a nor b ANS: B

PTS: 1

REF: 381

31. After the ____ and depreciation have been deducted, taxes can be calculated. a. interest expense c. liabilities b. assets d. all of the above ANS: A

PTS: 1

REF: 381

32. A standard method of detailing a loan transaction is to set up what is known as a(n) ____. a. amortization table c. depreciation table b. mortgage table d. cash flow table ANS: A

PTS: 1

REF: 383

33. The PPMT function calculates the value of the ____ for a specified period. a. principal payment c. either a or b b. interest payment d. neither a nor b ANS: A

PTS: 1

REF: 385

34. The ____ function calculates the value of the interest payment for a specified period. a. PPMT c. INMT b. IPMT d. RATE ANS: B

PTS: 1

REF: 385

35. In the PPMT function, the per-argument periods begin at 1 and end at the last payment period, which equals ____. a. nper c. pv b. fv d. rate ANS: A

PTS: 1

REF: 385

36. In the function =IPMT(B$3/B$5,A11,B$4*B$5,B$2,B$6,0), the entry “B$6” represents the ____. a. rate c. pv b. per d. fv ANS: D

PTS: 1

REF: 385

37. In the function =IPMT(B$3/B$5,A11,B$4*B$5,B$2,B$6,0), the only argument that will vary when copied down a column will be the ____. a. nper c. pv b. per d. fv ANS: B

PTS: 1

REF: 385

38. ____ returns the cumulative interest paid on a loan between start_period and end_period. a. CUMIPMT c. IPMTCUM b. CUMPMT d. CUMINT ANS: A

PTS: 1

REF: 387

39. ____ returns the cumulative principal paid on a loan between start_period and end_period. a. CUMPRINC c. PRINCUM b. ICUMPR d. CUMPRI ANS: A

PTS: 1

REF: 387

40. ____ depreciation basically allocates the value of an asset evenly throughout the life of the asset. a. Complex c. Straight line b. Compound d. Even ANS: C

PTS: 1

REF: 390

41. In the SLN function, the _____ argument is the initial cost of the asset. a. cost c. value b. price d. pv ANS: A

PTS: 1

REF: 391

42. In the SLN function, the _____ argument is the value at the end of depreciation. a. end c. salvage b. life d. sell ANS: C

PTS: 1

REF: 391

43. In the SLN function, the _____ argument is the number of periods over which the asset is depreciated. a. periods c. number b. life d. sell ANS: B

PTS: 1

REF: 391

44. Named ranges can be named and then specified as either a workbook scope or to a specific ____. a. worksheet c. chart b. table d. file ANS: A

PTS: 1

REF: 393

45. The depreciation method that computes depreciation at an accelerated rate with depreciation being highest in the first period and decreasing in successive periods is ____. a. variable-declining balance c. sum of the years digits b. fixed-declining balance d. double-declining balance ANS: D

PTS: 1

REF: 394

46. Which of the following is true of range names? a. Range names are case sensitive. c. Range names can contain blank spaces. b. Range names can exceed 255 characters. d. none of the above ANS: D

PTS: 1

REF: 394

47. The depreciation method where depreciation is apportioned based on a declining fractional amount of the asset’s life is the ____ method. a. variable-declining balance c. sum of the years digits b. fixed-declining balance d. double-declining balance ANS: C

PTS: 1

REF: 395

48. The depreciation method with the formula =DB(cost,salvage,life,period,[month]) is the ____ method. a. variable-declining balance c. sum of the years digits b. fixed-declining balance d. double-declining balance ANS: B

PTS: 1

REF: 395

49. The ____ depreciation method calculates interest as highest in the first period and decreases in amount each period thereafter. a. double-declining balance c. sum of the years digits b. fixed-declining balance d. variable-declining balance ANS: A

PTS: 1

REF: 394

50. To apply a range name globally, make sure that Workbook is selected as the ____ box. a. Name c. Range b. Breadth d. Scope ANS: D

PTS: 1

REF: 396

51. The function =-ROUND(Taxrate*C16,0) will round taxes to the nearest ____. a. dollar c. penny b. dime d. none of the above ANS: A

PTS: 1

REF: 396

52. ____ is preferred by most financial theorists because it uses the expected cash flows and applies a minimum rate of return to discount these cash flows into current (present) value dollars. a. NPV c. EPP b. EPS d. IRR ANS: A

PTS: 1

REF: 404

53. The rate of return is often referred to as the ____. a. hurdle rate c. both a and b b. discount rate d. neither a nor b ANS: C

PTS: 1

54. When using NPV, ____ are ignored. a. text values b. error messages ANS: C

PTS: 1

REF: 404

c. both a and b d. neither a nor b REF: 404

55. NPV requires that value1, value2, value3, and so on must be equally spaced in time and occur at ____. a. the end of each period c. the beginning of each period b. the end of the year d. the beginning of the year ANS: A

PTS: 1

REF: 404

56. When using the NPV function in Excel, the first cash flow into or out of the financial transaction is assumed to be ____. a. one year from now c. today b. one month from now d. tomorrow ANS: A

PTS: 1

REF: 405

57. Using the NPV function, you can write a formula to determine the net present value of the income flows for a project, including the initial investment, as follows: ____. a. –Initial Investment+NPV c. NPV+Initial investment b. –NPV+Initial investment d. none of the above ANS: A

PTS: 1

REF: 405

58. In projects where cash flows are negative at both the beginning and the end of the project life, the results of the NPV and IRR are often ____. a. similar c. error-generating b. contradictory d. none of the above ANS: B

PTS: 1

59. IRR stands for the ____. a. internal rate of return b. initial rate of return ANS: A

PTS: 1

REF: 407

c. interest rate of return d. none of the above REF: 407

60. The ____ method calculates the rate at which discounted cash flows in and out are equal. a. IRR c. both a and b b. NPV d. neither a nor b ANS: A

PTS: 1

REF: 407

61. The syntax of the IRR function is as follows: ____. a. =IRR(values,[guess]) c. =IRR(fv,pv,[values]) b. =IRR(rate,[guess]) d. =IRR(per,[guess]) ANS: A

PTS: 1

REF: 407

62. In the IRR function, the guess argument is optional and should not be needed, but can be used if the ____ iterations that are automatically performed by Excel do not result in an accurate value. a. 10 c. 20 b. 15 d. 25 ANS: C

PTS: 1

REF: 408

63. In the IRR function, the values argument is a list of ____. a. negative cash flows c. both a and b b. positive cash flows d. neither a nor b ANS: C

PTS: 1

REF: 408

64. The best chart to show a functional relationship, such as hurdle rate versus NPV, is ____. a. an X plot c. a Y plot b. an XY Scatter d. a pie chart ANS: B

PTS: 1

REF: 409

65. Stockholders often look at the ____ as a quick measure of how attractive a company’s stock is versus other stocks within the industry. a. ROI c. FV b. NPV d. ROR ANS: A

PTS: 1

REF: 410

66. ROI is the ____, excluding the initial investment, divided by the investment value. a. sum of cash flows c. sum of principal payments b. market value d. asset value minus depreciation ANS: A

PTS: 1

REF: 410

67. The payback year is the year in which the cumulative total cash flow is greater than or equal to ____. a. $0 c. the amount of the last payment b. the value of the business d. the original loan amount ANS: A

PTS: 1

REF: 411

68. The payback period is the time it will take to ____. a. pay the loan back b. earn sufficient profits so that the loan can be paid back c. depreciate an item d. none of the above ANS: B

PTS: 1

REF: 411

69. Excel provides a method to automatically calculate the payback period, but it is a somewhat complex process that involves using the ____ reference function and calculating a cumulative total. a. MATCH c. CALCULATE b. LINK d. ISNUMBER ANS: A

PTS: 1

REF: 412

70. Which formula from the list below can be copied across a row to create a row of cumulative totals? a. =SUM($C24:C24)>0 c. MATCH($C24:C24)>0) b. IF($C24:C24)>0 d. =SUM($C$24:$C$24)>0 ANS: A

PTS: 1

Case-Based Critical Thinking Questions Case 6-1

REF: 412

Phil has put together the worksheet above with a 5-year cash flow estimate for his shoe company. He needs to explain the chart to his investors. Please answer the questions below using this figure as a reference.

71. Phil enters the tax rate of 35% in the workbook as a range named Taxrate without first entering the value itself in the workbook. In Excel, you can give a value a range name without actually entering the value in a specific cell. To do so, you use the ____ dialog box. a. New Name c. Data Name b. Range Name d. Cell Name ANS: A

PTS: 1

REF: 393

TOP: Critical Thinking

72. Phil also assumes that the actual taxes owed will be rounded to the nearest dollar. Using this information, he enters the formula ____ in cell C18 to calculate the taxes. This is the area indicated by #1 in the figure above. a. =ROUND(Taxrate*C16,0) c. =-ROUND(Taxrate*C16,1) b. =-ROUND(Taxrate*C16,0) d. =-ROUND(Taxrate*C16,2) ANS: B

PTS: 1

REF: 396

TOP: Critical Thinking

73. Phil also calculates the projected income after taxes in cell C19 using the formula ____. a. =C18+C19 c. =C18+C16 b. =C18-C12 d. =C18-C16 ANS: C

PTS: 1

REF: 396

TOP: Critical Thinking

74. Phil now must add back the depreciation because this is not actually a cash flow. Cell C15 contains the depreciable amount represented as a negative value. In this case, Phil enters the formula ____ in cell C21 and copies it across the row. a. =-C15 c. =C15-C16 b. =C15 d. =C15+C16 ANS: A

PTS: 1

REF: 397

TOP: Critical Thinking

75. To determine the projected cash flow, Phil must now total the following: taxable income, depreciation added back to the cash flow, and principal payments deducted from the cash flow (already a negative value). Phil enters the following formula in cell C24 and copies it across the row: ____. a. =SUM(C19,C21,C22) c. =C19+C21-C22 b. =C19-C21+C22 d. =C19-C21-C22 ANS: A

PTS: 1

REF: 397

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 6-2

Natalie is working on calculating the IRR of a project. Please refer to the figure above as you answer the questions below.

76. In college, Natalie had to calculate IRR manually so that she would have a good understanding of the way it worked. To do that, she needed to guess an IRR value and substitute it as the ____ in the NPV formula. a. discount rate c. time period b. amount borrowed d. none of the above ANS: A

PTS: 1

REF: 407

TOP: Critical Thinking

77. Natalie knows the syntax of the IRR function is as follows: IRR(values,[guess]). The values argument is a list of positive and negative cash flows. For this function to work, ____. a. all cash flows must be positive b. all cash flows must be negative c. there must be at least one positive and one negative cash flow d. it doesn’t matter at all ANS: C

PTS: 1

REF: 370

TOP: Critical Thinking

78. Natalie’s company has been going through a rocky time. Her boss asks her what would be an acceptable cash flow interval for calculating IRR. What should be her answer? a. every month c. every time a bill is paid b. every time a payment is received d. all of the above ANS: A

PTS: 1

REF: 407 | 408

TOP: Critical Thinking

79. Natalie has used the IRR function in the past and received a #NUM error. What does this error mean? a. 20 iterations did not result in an accurate value. b. 10 iterations did not result in an accurate value. c. There is an error in Natalie’s data. d. There is an error in Natalie’s formulas.

ANS: A

PTS: 1

REF: 408

TOP: Critical Thinking

80. Applying the IRR function to the worksheet in cell B39, Natalie writes the formula ____. a. =IRR(B24:G24) c. =IRR(C30:C38) b. =IRR(B27:G27) d. =IRR(C39) ANS: B

PTS: 1

REF: 408

TOP: Critical Thinking

COMPLETION 1. Interest that is paid solely on the amount of the original principal value is called ____________________ interest.

ANS: simple PTS: 1

REF: 360

2. The PMT function finds the value of the payment per period, assuming that there are constant payments and a constant ____________________ for the duration of the loan.

ANS: interest rate PTS: 1

REF: 364

3. When cash is paid out, it is considered ____________________ cash flow.

ANS: negative PTS: 1

REF: 365

4. Payments that are inflows should be ____________________, and payments that are outflows should be negative.

ANS: positive PTS: 1

REF: 368

5. Before constructing a formula with a financial function, it is a good idea to determine the timeline of the financial ____________________.

ANS: transaction

PTS: 1

REF: 371

6. The RATE function syntax is as follows: =RATE(nper,pmt,pv,fv,____________________,[guess]).

ANS: type PTS: 1

REF: 372

7. A(n) ____________________ payment is money required from the borrower toward the purchase of an asset.

ANS: down PTS: 1

REF: 375

8. In the SLN function, the ____________________ argument is the value at the end of the depreciation, sometimes referred to as the salvage value.

ANS: salvage PTS: 1

REF: 391

9. Sometimes a value is needed in multiple worksheets within a workbook. In this case, a(n) ____________________ range name is best.

ANS: global PTS: 1

REF: 394

10. Range ____________________ must start with a letter, an underscore, or a backslash.

ANS: names PTS: 1

REF: 394

11. The correct function for the fixed-declining balance method is =DB(cost,salvage,life,period,____________________).

ANS: month [month] PTS: 1

REF: 395

12. This function rounds the taxes owed: =-____________________(Taxrate*C16,0).

ANS: ROUND PTS: 1

REF: 396

13. When using the NPV function, the hurdle rate must match the ____________________ duration, so that a yearly cash flow would apply to a yearly discount rate, for example.

ANS: period time period PTS: 1

REF: 404

14. The NPV function in Excel takes the cash flow from year 0, which is referred to as value 1, and discounts the value by ____________________.

ANS: one year 1 year 12 months PTS: 1

REF: 405

15. The ____________________ method calculates the rate at which discounted cash flows in and out are equal, essentially where the NPV is $0.

ANS: IRR irr Internal Rate of Return internal rate of return PTS: 1

REF: 407

16. The syntax of the IRR function is as follows: =IRR(values,____________________).

ANS: guess [guess] PTS: 1

REF: 407

17. The ____________________ argument is a list of positive and negative cash flows.

ANS: values PTS: 1

REF: 408

18. The ____________________ is the sum of the cash flows, excluding the initial investment, divided by the investment value.

ANS: ROI roi return on investment ROI (return on investment) return on investment (ROI) PTS: 1

REF: 410

19. Business analysts must be aware that the ROI does not take into account the time value of ____________________.

ANS: money PTS: 1

REF: 410

20. Excel provides a method to automatically calculate the payback period, but it is a somewhat complex process that involves using the ____________________ reference function and calculating a cumulative total.

ANS: MATCH PTS: 1

REF: 412

MATCHING Identify the letter of the choice that best matches the word or phrase. a. simple interest d. compound interest b. principal e. balloon payment c. interest f. cash flow 1.

2.

3.

4.

The value of the loan or investment Interest that is paid solely on the amount of the original principal value A user fee you are paying to “use” the bank’s money Adding interest earned each period to the principal for purposes of computing interest for the next period 5. Additional money owed at the end of a loan 6. Cash amounts paid or received 1.

2.

3.

4.

5.

6.

ANS: B ANS: A ANS: C ANS: D ANS: E ANS: F

PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1

REF: REF: REF: REF: REF: REF:

360 360 360 361 375 365

Identify the letter of the choice that best matches the arguments in the CUMIPMT function. a. #1 d. #4 b. #2 e. #5 c. #3 f. #6 7. Type 8. Rate per period

9.

10.

11.

12.

Initial loan amount (pv) Number of periods (nper) Starting period Ending period

7.

8.

9.

10.

11.

12.

ANS: F ANS: A ANS: C ANS: B ANS: D ANS: E

PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1

REF: REF: REF: REF: REF: REF:

389 389 389 389 389 389

ESSAY 1. What is the correct syntax of the PMT function? Briefly describe each of the arguments associated with this function.

ANS: Correct syntax: =PMT(rate,nper,pv,[fv],[type]) • The rate argument is the interest rate per compounding period.

• The nper argument is the number of compounding periods.

• The pv argument is the present value, also referred to as the original principal value at the beginning of the financial transaction.

• The fv argument is the future value (compounded amount), also referred to as the value at the end of the financial transaction.

• The type argument designates when payments are made. Type 0, the default type, indicates that payments are made at the end of the period. Type 1 indicates that payments are made at the beginning of the period.

PTS: 1

REF: 364

TOP: Critical Thinking

2. Correctly set up an NPER function, assuming you are solving for how many years it will take to pay off a loan. You do not have to do the calculations—just set up the formula based on the facts below: • NPER(rate,pmt,pv,fv,type) • The rate per year is 3.5% compounded quarterly.

• The payment (pmt) is –$25,000 per quarter.

• The present value (pv) is $400,000 because the bank has offered to fund all of the capital required for the project.

• The future value (fv) is assumed to be $0 because no mention is made of any residual amounts owed at the end of the loan.

• The type argument is assumed to be the default 0.

ANS: =NPER(3.5%/4,-25000,400000)/4 NPER function result: 4.328051 (years) Notes: Since the payments are quarterly, the interest rate is divided by 4.

Since the type argument is 0, it can be omitted.

Remember to calculate NPER in terms of the number of compounding periods; in this case, the number of quarters. To calculate the loan duration in years, divide the number of periods (NPER) by the number of quarters per year.

PTS: 1

REF: 374

TOP: Critical Thinking

3. Briefly describe each of the following depreciation methods: straight line depreciation, double-declining balance, sum of the years digits, and fixed-declining balance.

ANS: • Straight line depreciation basically allocates the value of an asset evenly throughout the life of the asset.

• Double-declining balance computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods.

• In sum of the years digits, depreciation is apportioned based on a declining fractional amount of the asset’s life.

• Fixed-declining balance returns the depreciation of an asset for a specified period.

PTS: 1

REF: 390 | 394 | 395

TOP: Critical Thinking

Chapter 7: Organizing Data for Effective Analysis TRUE/FALSE 1. In a computer application, the term “string” refers to a meaningful sequence of characters.

ANS: T

PTS: 1

REF: 428

2. The CONCATENATE function combines the values in a range of cells into one numeric item in a new cell.

ANS: F

PTS: 1

REF: 430

3. The TRIM function removes all spaces in a text string except for the single spaces between words.

ANS: T

PTS: 1

REF: 432

4. The SEARCH function is case sensitive.

ANS: F

PTS: 1

REF: 434

5. Dividing text into columns is also referred to as parsing text into columns.

ANS: T

PTS: 1

REF: 438

6. The Subtotal command creates summary reports that quickly organize data into categories with subtotal calculations.

ANS: T

PTS: 1

REF: 441

7. Creating an Excel worksheet often bridges the gap between tables and simple databases.

ANS: F

PTS: 1

REF: 444

8. To remove a filter from a column and display all of the data in the Excel table again, click the column’s filter arrow, and then click Clear Filter From.

ANS: T

PTS: 1

REF: 448

9. A database is created by and defined in a database management warehouse (DBW), which is a software program that creates and accesses the data in a database.

ANS: F

PTS: 1

REF: 451

10. The Query Wizard lets you choose your data source and select the database table and fields you want to import into a workbook.

ANS: T

PTS: 1

REF: 454

11. When you create formulas referencing cells that contain dates and/or times, Excel will never format the result as a date instead of as a number.

ANS: F

PTS: 1

REF: 457

12. The syntax for the YEARFRAC function is as follows: =YEARFRAC(start_date,end_date,[basis]).

ANS: T

PTS: 1

REF: 458

13. Placing a field in the Row Labels area of a PivotTable displays data from that field in columns and rows.

ANS: F

PTS: 1

REF: 463

14. The easiest way to create a PivotChart report is to use an existing PivotTable report as the source data.

ANS: T

PTS: 1

REF: 470

15. Excel includes several external data sources saved as database files that you can use to import data into Excel.

ANS: F

PTS: 1

REF: 454

16. Hypertext Markup Language is the markup language that creates Web pages.

ANS: T

PTS: 1

REF: 474

17. HTML documents are user-defined documents in which the user develops a DTD that defines the elements contained in a document.

ANS: F

PTS: 1

REF: 474

18. Excel uses an XML map to relate the data to the elements when importing and exporting XML data.

ANS: T

PTS: 1

REF: 478

19. Any data you enter in the row below an XML table will be added to the table.

ANS: T

PTS: 1

REF: 481

20. XML is a free, nonproprietary format.

ANS: T

PTS: 1

REF: 482

MODIFIED TRUE/FALSE 1. In Excel, you can use a text function to link together or transpose the information from multiple cells into one cell. _________________________ ANS: F, concatenate PTS: 1

REF: 429

2. The LAST function returns the last character or characters in a text string, based on the number of characters specified. _________________________

ANS: F, RIGHT PTS: 1

REF: 431

3. The START function returns the starting position of one text value within another text value. _________________________ ANS: F, FIND PTS: 1

REF: 434

4. The FIXED function rounds a number to a specified number of decimals and returns the number as text with commas and a period. _________________________ ANS: T

PTS: 1

REF: 437

5. A database stored in one table is sometimes called a(n) flat-file database. _________________________ ANS: T

PTS: 1

REF: 444

6. When you create an Excel table, drop-down list arrows appear in the header row for each column when the ArrowFilter feature is enabled. _________________________ ANS: F, AutoFilter PTS: 1

REF: 446

7. The AutoFilter option called Text Filters removes any existing filters, displays all data in the column, and restores the default (Select All) option. _________________________ ANS: F, Clear Filter From PTS: 1

REF: 447

8. A(n) list is a set of related data that is stored in tables. _________________________ ANS: F, database PTS: 1

REF: 450

9. Many DBMSs are called absolute database management systems because the data is stored in tables that are related to each other through a common field. _________________________ ANS: F, relational PTS: 1

REF: 451

10. The TODAY function returns the current date’s serial number (based on the computer’s internal clock). _________________________ ANS: T

PTS: 1

REF: 457

11. A(n) PivotSheet report is an interactive report that lets you summarize and analyze a data set. _________________________ ANS: F, PivotTable PTS: 1

REF: 460

12. A PivotChart report represents source data as a(n) graphic. _________________________ ANS: T

PTS: 1

REF: 470

13. A(n) marked language uses a set of tags to distinguish different elements in a document and uses attributes to define those elements further. _________________________ ANS: F, markup PTS: 1

REF: 474

14. The term “well-formed” is commonly used in the XML community to describe an XML document that is properly structured and meets established rules and guidelines. _________________________ ANS: T

PTS: 1

REF: 476

15. Excel table data is organized in rows and columns, where each column defines a record. _________________________ ANS: F, row PTS: 1

REF: 483

MULTIPLE CHOICE 1. You can enter data into a worksheet by ____. a. typing data into cells b. importing data into a worksheet ANS: D

PTS: 1

c. linking data to another document d. all of the above

REF: 427

2. A common way of storing data so that it is usable in other programs is to save it in a ____ file. a. comma-delimited c. comma-delineated b. comma-limited d. comma-lineated ANS: A

PTS: 1

REF: 429

3. The data format that separates the values in each record with commas is often called ____. a. comma-separated values (CSV) c. comma data (CD) b. comma-separated data (CSD) d. comma-separated records (CSR) ANS: A

PTS: 1

REF: 429

4. A ____ identifies the end of each field in a comma-delimited file. a. paragraph mark c. comma b. pound sign d. question mark

ANS: C

PTS: 1

REF: 438

5. The first goal when working with unstructured data is to determine the ____ you need so you can find the best way to change the unstructured data into structured data. a. format c. color coding b. size of the file d. reports ANS: A

PTS: 1

REF: 429

6. You can use a text function to ____, or concatenate, the information from multiple cells into one cell. a. look up c. transform b. link together d. calculate ANS: B

PTS: 1

REF: 429

7. The CONCATENATE function combines the values in a range of cells into ____ in a new cell. a. one text item c. multiple text items b. one numeric item d. multiple numeric items ANS: A

PTS: 1

REF: 430

8. The syntax for the CONCATENATE function is as follows: ____. a. =CON(text1,text2,...) c. CONCATE(text1,text2,...) b. =CONCATENATE(text1,text2,...) d. LINK(text1,text2,...)

ANS: B

PTS: 1

REF: 430

9. The ____ function returns the last character or characters in a text string, based on the number of characters specified. a. LEFT c. RIGHT b. CHARACTER d. NUMBER ANS: C

PTS: 1

REF: 431

10. The syntax for the RIGHT function is as follows: ____. a. =RIGHT(text,[num_chars]) c. =RIGHT(range,[num_chars]) b. =RIGHT(num_chars,[text]) d. =RIGHT(num_chars,[range]) ANS: A

PTS: 1

REF: 431

11. In the RIGHT function, the argument called ____ is the text string or cell reference that contains the characters you want to extract. a. text c. word b. table d. chars ANS: A

PTS: 1

REF: 432

12. In the RIGHT function, the argument called ____ specifies the number of characters that you want the RIGHT function to extract. a. text c. table b. num_chars d. word ANS: B

PTS: 1

REF: 432

13. The LEFT function extracts characters from the ____ of a text string. a. left side c. end

b. specified area ANS: A

d. none of the above PTS: 1

REF: 432

14. The TRIM function removes all spaces in a text string except for the single spaces ____. a. between numbers c. between cells b. between words d. between worksheets ANS: B

PTS: 1

REF: 432

15. The syntax for the TRIM function is as follows: =TRIM(____). a. num c. cell b. text d. range ANS: B

PTS: 1

REF: 433

16. In the ____ function, text is the text string or cell reference that contains the space(s) that you want to remove. a. TRIM c. REMOVE b. SPACE d. NOSPACE ANS: A

PTS: 1

REF: 433

17. The FIND function returns the ____ one text value within another text value. a. starting position of c. middle position of b. ending position of d. comma between ANS: A

PTS: 1

REF: 434

18. The FIND function is case sensitive, so searching for ____ returns a different result than searching for D. a. d c. either a or b b. D d. neither a nor b ANS: A

PTS: 1

REF: 434

19. The SEARCH function is not case sensitive, so searching for ____ returns the same result as searching for D. a. D c. either a or b b. d d. neither a nor b ANS: C

PTS: 1

REF: 434

20. In the FIND function, ____ is the text that you want to find. a. find_text c. text_find b. find_words d. words_find ANS: A

PTS: 1

REF: 434

21. In the FIND function, ____ is the text containing the text that you want to find. a. text_within c. contain_text b. within_text d. text_contain ANS: B

PTS: 1

REF: 434

22. In the FIND function ____ is the character position in which to start the search. a. start_num c. char_pos

b. num_start ANS: A

d. pos_char PTS: 1

REF: 434

23. In the FIND function, if you omit start_num, Excel assumes a start number of ____. a. 0 c. whatever is in the first cell of the range b. 1 d. none of the above ANS: B

PTS: 1

REF: 434

24. When data is in a comma-delimited form, there is a single ____ of data with commas separating the data. a. cell c. column b. row d. worksheet ANS: A

PTS: 1

REF: 436

25. The DOLLAR function converts a number to ____ in currency format with a dollar sign and the specified number of decimal places. a. a formula c. a graphic b. text d. none of the above ANS: B

PTS: 1

REF: 437

26. The FIXED function rounds a number to a specified number of decimals and returns the number as text with ____. a. commas c. both a and b b. a period d. neither a nor b ANS: C

PTS: 1

REF: 437

27. The ____ function converts uppercase letters in a text string to lowercase. a. HIGHER c. UPPER b. LOWER d. STRING ANS: B

PTS: 1

REF: 437

28. The REPLACE function replaces part of a text string with a new text string based on the number of characters specified in ____. a. num_chars c. text b. range d. start_num ANS: A

PTS: 1

REF: 437

29. The SEARCH function finds one text string ____ another text string (not case sensitive). a. alphabetically in front of c. in the row next to b. alphabetically after d. within ANS: D

PTS: 1

REF: 437

30. The ____ function converts lowercase letters in a text string to uppercase. a. UPPER c. TEXT b. LOWER d. INCREASE ANS: A

PTS: 1

REF: 438

31. The ____ function converts a text string that represents a number (a number, date, or time) to a number. a. VALUE c. CONVERT b. NUMBER d. NEW ANS: A

PTS: 1

REF: 438

32. The ____ Wizard separates the values in a text string into columns or fields. a. Convert Text to Columns c. Separate Values b. Convert Text d. Convert Values ANS: A

PTS: 1

REF: 438

33. In comma-delimited files, a comma is the delimiter. Another common delimiter is the ____. a. slash c. paragraph mark b. tab character d. quotation mark ANS: B

PTS: 1

REF: 438

34. You can add a row at the top of a worksheet (called a ____ row) and type labels that identify the data contained in each column. a. title c. header b. neon d. footer ANS: C

PTS: 1

REF: 440

35. The Subtotal command creates summary reports that let you ____ the level of detail in a report. a. collapse c. both a and b b. expand d. neither a nor b ANS: C

PTS: 1

REF: 441

36. The Subtotal command works with ____ calculation(s) at a time. a. one category and one subtotal c. one category and multiple subtotal b. multiple categories and one subtotal d. multiple categories and multiple subtotal ANS: A

PTS: 1

REF: 443

37. An Excel table is a range of ____ that you formalize as a single unit of data. a. labels c. worksheets b. cells d. functions ANS: B

PTS: 1

REF: 444

38. When working with an Excel table, you should insert ____ around the list to segregate it from any other data in the worksheet. a. empty columns c. both a and b b. empty rows d. neither a nor b ANS: C

PTS: 1

REF: 444

39. The maximum number of rows and columns in a worksheet limits the number of records and fields you can have in your list to ____ rows and 16,384 columns. a. 16,384 c. 148,576 b. 65,535 d. 1,048,576 ANS: D

PTS: 1

REF: 444

40. Excel must load the entire workbook into memory when you open it, which can lead to performance problems ____. a. with very small lists c. with very large tables b. when adding column headings d. when using functions ANS: C

PTS: 1

REF: 444

41. Clicking within an Excel table opens the ____ tab on the ribbon. a. TABLE TOOLS DESIGN c. TABLE DESIGN b. TOOLS DESIGN d. WORKSHEET DESIGN ANS: A

PTS: 1

REF: 445

42. If you want to sort by more than one column, you can use the Sort dialog box to sort by up to ____ columns. a. 12 c. 64 b. 24 d. 128 ANS: C

PTS: 1

REF: 445

43. When you click a filter arrow in the header row, you can ____. a. sort the records in ascending or descending order b. display the top 10 records c. display records containing a specific value that appears in the column d. all of the above ANS: D

PTS: 1

REF: 446

44. To remove a filter from a column and display all of the data in the Excel table again, click the column’s drop-down list arrow, and then click ____. a. Clear Filter From c. Clear Filter Range b. Clear Filter Now d. Delete Filter ANS: A

PTS: 1

REF: 447

45. When data is formatted as an Excel table, you can insert a new row into the table by selecting the last cell in the last row of the existing data and pressing the ____ key to create a new row. a. + c. Ctrl b. Tab d. F2 ANS: B

PTS: 1

REF: 449

46. The ____ function calculates the number of years between two dates. a. YEARBET c. YEARFRAC b. YEARTWEEN d. YEARCALC ANS: C

PTS: 1

REF: 458

47. A ____ is an interactive report that lets you summarize and analyze a data set. a. PivotTable report c. Pivot report b. Table report d. Relational report ANS: A

PTS: 1

REF: 460

48. The PivotTable ____ task pane contains a list of fields in the data source you selected for the PivotTable report. a. List of Fields c. Field Source b. Field List d. Fields ANS: D

PTS: 1

REF: 462

49. The default field setting in the ____ area is to calculate a sum of the items in this drop area by the categories in the Row Labels area or Column Labels area. a. TABLE c. CHART b. CELLS d. VALUES ANS: D

PTS: 1

REF: 464

50. You can change the calculation used in the VALUES area by right-clicking a value in the VALUES area to open the shortcut menu, and then clicking Field Settings to open the ____ dialog box. a. Value Field c. Field Settings b. Value Settings d. Value Field Settings ANS: D

PTS: 1

REF: 464

51. Unlike a regular chart, you can change the layout and data displayed in a PivotChart report by ____. a. moving fields c. both a and b b. changing the chart type d. neither a nor b ANS: A

PTS: 1

REF: 471

52. A PivotChart report represents source data as a ____. a. graphic c. worksheet b. presentation d. table ANS: A

PTS: 1

REF: 470

53. Click the ____ option on the DATA tab on the ribbon to import Access data into Excel. a. From Access c. From Other Sources b. Query Wizard d. From SQL Server ANS: C

PTS: 1

REF: 454

54. A document type definition ____. a. identifies the tags used in the markup language b. defines how the tags are related to each other c. describes which tags can be nested in other tags d. all of the above ANS: D

PTS: 1

REF: 474

55. ____ provides structure for a document by dividing it into elements (pieces) such as title, paragraph, text, name, part number, and so on. a. SGML c. HTML b. WWW d. FTP ANS: A

PTS: 1

REF: 474

56. ____ is the acronym for Extensible Markup Language. a. XML c. EXML

b. EML ANS: A

d. EMARK PTS: 1

REF: 474

57. ____ adds meaning to content by providing a description of the data through user-defined elements, and a hierarchical structure that shows the relationship of one data element to another. a. XML c. HTML b. SGML d. A PivotTable ANS: A

PTS: 1

58. ____ is data that describes other data. a. Metadata b. Superdata ANS: A

PTS: 1

REF: 475

c. Description data d. A metatag REF: 475

59. An XML document can have ____ root element(s), and all other elements are nested within that/those root element(s). a. only one c. only three b. only two d. only four ANS: A

PTS: 1

REF: 477

60. When you add a schema to a workbook, Excel generates an ____ to create mapped ranges of data, and define the relationships between these ranges and the elements in the XML schema. a. XML map c. XML linked table b. XML diagram d. XML flow chart ANS: A

PTS: 1

REF: 478

61. An XML table is similar in appearance and functionality to an Excel table, but the data in an XML table is mapped to ____ elements. a. SGML c. HTML b. XML d. Excel ANS: B

PTS: 1

REF: 477

62. A schema is a set of ____ for an XML document. a. arguments c. maps b. hyperlinks d. validation rules ANS: D

PTS: 1

REF: 477

63. To add a new row to an XML table, you use the handle in the lower-right cell of the XML table to ____. You can then type or copy values into the cells. a. increase the size of the table c. rotate the bottom row b. open a menu d. none of the above ANS: A

PTS: 1

REF: 479

64. The XML data in an XML table functions like the data in an Excel table in that you can _____ the data in the same way. a. sort c. analyze b. filter d. all of the above ANS: D

PTS: 1

REF: 479

65. To import data using an XML map, open the ____ task pane, and then click the XML Maps button. a. XML Source c. XML Export b. XML Maps d. XML Import ANS: A

PTS: 1

REF: 477

66. When the data elements are being imported using an XML map, only the element names appear in the worksheet. To import the data from these elements into the worksheet, click the ____ button in the External Table Data group on the TABLE TOOLS DESIGN tab. a. Refresh c. XML b. Calculate d. Design ANS: A

PTS: 1

REF: 481

67. You can open the Export XML dialog box using the Export button in the XML group on the ____ tab. a. DEVELOPER c. PROGRAMMING b. WEB d. WEB QUERY ANS: A

PTS: 1

REF: 481

68. You need to be careful when you export an XML file because only the elements in the XML list are exported, and not the elements from the ____. a. XML graphic c. XML group b. XML map d. XML category ANS: B

PTS: 1

REF: 482

69. XML documents are organized in a running tabular form with each element occupying ____. a. one line c. one column b. one map d. one table ANS: A

PTS: 1

REF: 483

70. To convert data to an XML document that can be used in any application that supports XML data, take the following steps: Click the Export button in the XML group, enter a filename in the ____ dialog box, and then click the Export button. a. Export XML c. Save XML b. Import XML d. Convert XML ANS: A

PTS: 1

Case-Based Critical Thinking Questions Case 7-1

REF: 483

Julie is a sales manager, and she created the table above to organize her customer list. Please refer to the above figure when answering the questions below.

71. To create the table as shown in the figure, Julie can click any cell in the worksheet and then the Table button in the Tables group on the ____ tab. a. INSERT c. DATA b. FORMULAS d. REVIEW ANS: A

PTS: 1

REF: 445

TOP: Critical Thinking

72. When Julie first created the Excel table, the filter arrows did not appear in the header row for each column. To enable them, she clicked the Filter button in the ____ group on the DATA tab. a. Sort c. Sort & Filter b. Tables d. Data ANS: C

PTS: 1

REF: 446

TOP: Critical Thinking

73. Julie wants to find a company that starts with the letter Z, but she can’t remember its complete name.

The quickest way to find the company name is to choose the ____ option. a. Sort Z to A c. Text Filters b. Sort A to Z d. Date Filters ANS: A

PTS: 1

REF: 447

TOP: Critical Thinking

74. Julie plans to add some rows with sales information. She wants to perform some calculations including Average, Standard Deviation, and Variance. She would need to display the ____ to access these calculations. a. Total row c. Calculation Wizard b. Function row d. Formula row ANS: A

PTS: 1

REF: 448

TOP: Critical Thinking

75. Julie has just signed up a new customer. How does she add the customer’s information to the table? a. Select the last cell in the last row and press the Tab key. b. Select the first cell in the last row and press Enter. c. Select any cell and press Enter.

d. none of the above ANS: A

PTS: 1

REF: 448-449

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 7-2

Robert has imported some data from Access and is using the Query Wizard to filter the data. Please refer to the above figure when answering the questions below.

76. If Robert wanted to find all records with the value IL in the State field, he would click State in the Column to filter list box, click the State list arrow, and then click ____. Click the list arrow for the second list box, and then select IL. a. equals c. IL b. find d. search ANS: A

PTS: 1

REF: 455-456

TOP: Critical Thinking

77. Robert also wants to add an additional filter by phone number for those people who have a 312 area code. To include a second filter in his query, he would click the list arrow in the ____ and define the next filter. a. first row c. third row b. second row d. Column to filter list box ANS: B

PTS: 1

REF: 455-456

TOP: Critical Thinking

78. Robert is looking for all the customers who both are in the state of IL and have a 312 area code. He would create a(n) ____. a. And query c. either a or b b. Or query d. neither a nor b ANS: A

PTS: 1

REF: 455-456

TOP: Critical Thinking

79. Robert wants to send a special gift to anyone who has placed his or her first order in the past three months or who has total sales of over $1,000 per year. In this case, Robert would use a(n) ____. a. And query c. either a or b b. Or query d. neither a nor b ANS: B

PTS: 1

REF: 455-456

TOP: Critical Thinking

80. Robert has lost track of the columns with filters applied. He then remembers that when you specify a filter for a column, the column name changes to ____ in the Column to filter list box. a. bold c. bold and italic b. italic d. grayed out text ANS: A

PTS: 1

REF: 455-456

TOP: Critical Thinking

COMPLETION 1. The ____________________ function combines the values in a range of cells into one text item in a new cell.

ANS: CONCATENATE PTS: 1

REF: 430

2. The ____________________ function returns the last character or characters in a text string, based on the number of characters specified.

ANS: RIGHT PTS: 1

REF: 431

3. The ____________________ function removes all spaces in a text string except for the single spaces between words.

ANS: TRIM PTS: 1

REF: 432

4. The ____________________ function returns the starting position of one text value within another text value.

ANS: FIND PTS: 1

REF: 434

5. The ____________________ command creates summary reports that quickly organize data into categories with subtotal calculations, and lets you collapse and expand the level of detail in the report.

ANS: Subtotal PTS: 1

REF: 441

6. An Excel ____________________ is a range of cells that you formalize as a single unit of data.

ANS: table PTS: 1

REF: 444

7. A(n) ____________________ is a set of related data that is stored in tables.

ANS: database

PTS: 1

REF: 450

8. A(n) ____________________ is a single characteristic of the entity, such as a product ID number or an address.

ANS: field PTS: 1

REF: 451

9. Many DBMSs are called ____________________ database management systems because the data is stored in tables that are related to each other through a common field in a process called normalization.

ANS: relational PTS: 1

REF: 451

10. Excel includes a program called ____________________ that lets you create queries to select data from external sources, including Access, and import the query results into a worksheet.

ANS: Microsoft Query PTS: 1

REF: 454

11. The ____________________ function returns the current date’s serial number (based on the computer’s internal clock).

ANS: TODAY PTS: 1

REF: 457

12. The ____________________ function calculates the number of years between the two dates.

ANS: YEARFRAC PTS: 1

REF: 458

13. A(n) ____________________ is an interactive report that lets you summarize and analyze a data set.

ANS: PivotTable report PTS: 1

REF: 460

14. The PivotTable ____________________ task pane contains a list of fields in the data source you selected for the PivotTable report.

ANS: Fields PTS: 1

REF: 462

15. A(n) ____________________ is a question that you ask a database.

ANS: query

PTS: 1

REF: 453

16. A(n) ____________________ language uses a set of tags to distinguish different elements in a document, and uses attributes to define those elements further.

ANS: markup PTS: 1

REF: 474

17. XML stands for ____________________.

ANS: Extensible Markup Language PTS: 1

REF: 474

18. The term ____________________ is commonly used in the XML community to describe an XML document that is properly structured, and meets established rules and guidelines for how data is described and defined using XML. ANS: well-formed well formed PTS: 1

REF: 476

19. A(n) ____________________ is a set of validation rules for an XML document.

ANS: schema PTS: 1

REF: 477

20. A(n) ____________________ is similar in appearance and functionality to an Excel table, but the data is mapped to XML elements.

ANS: XML table PTS: 1 MATCHING

REF: 477

Identify the letter of the choice that best matches the PivotTable that has been added to the worksheet shown in the figure above. a. #1 e. #5 b. #2 f. #6 c. #3 g. #7 d. #4 1.

2.

3.

4.

5.

6.

7.

ROWS area COLUMNS area PivotTable report area PivotTable inserted on a new worksheet VALUES area Field headings FILTERS area

1.

2.

3.

4.

5.

6.

7.

ANS: D ANS: F ANS: A ANS: B ANS: G ANS: E ANS: C

PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF:

462 462 462 462 462 462 462

Identify the letter of the choice that best matches the date and time function. a. TIMEVALUE d. DATEVALUE b. EDATE e. WEEKDAY c. EOMONTH 8.

9.

10.

11.

12.

Returns the serial number of the date that is the indicated number of months before or after start_date Returns the serial number of the last day of the month before or after a specified number of months Converts a date in the form of text to a serial number Returns a decimal number for a specific time that is represented by a text string Converts a serial number to a day of the week

8. ANS: B 9. ANS: C

PTS: 1 PTS: 1

REF: 459 REF: 459

10. ANS: D 11. ANS: A 12. ANS: E

PTS: 1 PTS: 1 PTS: 1

REF: 459 REF: 460 REF: 460

ESSAY 1. Write the correct syntax for the FIND function, and briefly describe each of its three arguments.

ANS: The correct syntax: =FIND(find_text,within_text,[start_num]) In the FIND function, find_text is the text that you want to find, within_text is the text containing the text that you want to find, and start_num is the character position in which to start the search. If you omit start_num, Excel assumes a start number of 1.

PTS: 1

REF: 434

TOP: Critical Thinking

2. Creating an Excel table lets you add some simple database functionality to a worksheet, but an Excel table has two limitations that are important to understand. What are they?

ANS: 1) First, the maximum number of rows and columns in a worksheet limits the number of records and fields you can have in your list to 1,048,576 rows and 16,384 columns.

2) The second, more important limitation is the fact that Excel must load the entire workbook into memory when you open it, which can lead to performance problems with very large lists.

PTS: 1

REF: 444

TOP: Critical Thinking

3. What is XML? How does Microsoft Excel take advantage of the benefits of XML data in spreadsheets?

ANS: XML is the acronym for Extensible Markup Language. XML is a fairly recent innovation created through the efforts of the World Wide Web Consortium.

XML was originally developed to meet the needs of large-scale electronic publishing efforts, but it has been playing an increasingly important role in describing and exchanging data.

Many programs can read XML documents, which makes it possible to read the data using a variety of applications. Most programs store data in a proprietary binary format, which can result in an organization becoming locked into a particular solution. Translating data from one format to another can be a very expensive task.

XML documents are user-defined documents in which the user develops a DTD or schema that defines the elements contained in a document and descriptions of how those elements are related to each other.

Because you can define elements in XML, it makes sense to make them as meaningful as possible.

Instead of identifying a paragraph of text in a document as a paragraph, you can define individual types of paragraphs based on the content they will contain.

PTS: 1

REF: 474-475

TOP: Critical Thinking

Chapter 8: Using Data Tables and Excel Scenarios for What-If Analysis TRUE/FALSE 1. What-if analysis allows you to see what output results if the values of various input assumptions change.

ANS: T

PTS: 1

REF: 491

2. Although data tables and scenarios are ideal for showing the results of the interaction of a number of variables, analyzing which results are more likely than others depends on your knowledge of your business and markets.

ANS: T

PTS: 1

REF: 491

3. Because what-if analysis usually involves many estimates and other projections, range analysis helps managers learn how tolerant the projected results are of changes in those estimates and assumptions.

ANS: F

PTS: 1

REF: 493

4. Data tables allow you to organize and present the results of only one what-if analysis.

ANS: F

PTS: 1

REF: 494

5. A data table depends on values and formulas used in a worksheet, and must appear on the same worksheet that contains this data.

ANS: T

PTS: 1

REF: 495

6. There is no right answer to the question of whether the effect of income taxes should be considered when performing what-if analysis.

ANS: T

PTS: 1

REF: 495

7. You can omit taxes from your estimates, or use your marginal tax rate.

ANS: T

PTS: 1

REF: 496

8. In a one-variable data table, you enter possible values for the single input variable in the last row or column of the data table.

ANS: F

PTS: 1

REF: 498

9. You can use Custom number formats to display a label in a worksheet even when the cell contains a value or a formula.

ANS: T

PTS: 1

REF: 500

10. A one-variable data table allows only one input assumption to vary.

ANS: T

PTS: 1

REF: 502

11. To create a two-variable data table, you must first set up a data source file.

ANS: F

PTS: 1

REF: 505

12. In a two-variable data table, you use two sets of values for two input variables, and display only the value of a single result cell.

ANS: T

PTS: 1

REF: 505

13. You can add a title and borders to make the worksheet more visually appealing and easy to understand.

ANS: T

PTS: 1

REF: 508

14. To compare scenarios, you can create a summary report that lists the scenarios side by side or in a PivotScenario.

ANS: F

PTS: 1

REF: 512

15. To set up a scenario, you create or use a worksheet that contains sets of input cells and no output cells.

ANS: F

PTS: 1

REF: 513

16. A scenario can span worksheets.

ANS: F

PTS: 1

REF: 513

17. Technically, the number of scenarios you can create is limited only by your computer’s memory, but scenario reports can include data from only the first 15 scenarios.

ANS: F

PTS: 1

REF: 517

18. When you have many input assumptions that will vary in each scenario, it is sometimes easier to identify the cells in the input section that will not change in the scenarios, thereby determining the scenarios’ changing cells through a process of elimination.

ANS: T

PTS: 1

REF: 517

19. Cells that contain formulas should be specified as changing cells in scenarios.

ANS: F

PTS: 1

REF: 517

20. Because a PivotTable is interactive, you can change the view of the data to see more or fewer details.

ANS: T

PTS: 1

REF: 530

MODIFIED TRUE/FALSE 1. At its most basic level, what-now analysis involves asking a question about a worksheet, such as: “What if the revenue of the swimwear product line increased by $10,000 per month?” _________________________ ANS: F, What-If

PTS: 1

REF: 491

2. You can use two-variable data tables to base a what-if analysis on realistic, but not actual, data. Doing so is called running a(n) situation. _________________________ ANS: F, simulation PTS: 1

REF: 491

3. The breaking point is the point at which revenue equals expenses. _________________________ ANS: F break-even point break even point PTS: 1

REF: 492

4. Rough analysis is a type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions. _________________________ ANS: F, Sensitivity analysis PTS: 1

REF: 493

5. A(n) source table is a range of cells containing values and formulas. _________________________ ANS: F, data table PTS: 1

REF: 494

6. When you change the values, the data table shows you how those changes affect the results of the formulas. _________________________ ANS: T

PTS: 1

REF: 494

7. A(n) one-variable data table has only one input cell and many result cells. _________________________ ANS: T

PTS: 1

REF: 494

8. Creating the structure for a data table involves creating the set of input values you want to use in a what-if analysis and indicating the results you want to see in the target table. _________________________ ANS: F, data table PTS: 1

REF: 505

9. You use a(n) one-variable data table to determine how two values affect a single result. _________________________ ANS: F two-variable two variable

PTS: 1

REF: 505

10. Source cells contain values, and result cells show the results of formulas. _________________________ ANS: F, Input PTS: 1

REF: 505

11. You use data tables when you want to conduct a what-if analysis involving one or two input cells. _________________________ ANS: T

PTS: 1

REF: 512

12. You use the Simulation Manager to define and save sets of values as scenarios, and then you view and change them as necessary to produce and compare different results. _________________________ ANS: F, Scenario Manager PTS: 1

REF: 512

13. To create a(n) scenario, you plan the input data you want to use and the results, or output, you want to achieve. _________________________ ANS: T

PTS: 1

REF: 513

14. The contribution margin represents the amount of revenue that can contribute to covering the fixed expenses of a company. _________________________ ANS: T

PTS: 1

REF: 515

15. A(n) PivotChart report is an interactive table that groups and summarizes information in a concise format so you can easily analyze the information. _________________________ ANS: F, PivotTable PTS: 1

REF: 530

MULTIPLE CHOICE 1. You use a ____ when you want to ask a what-if question involving a range of values. a. data table c. data chart b. source table d. source chart ANS: A

PTS: 1

REF: 491

2. A ____ is running a what-if analysis on realistic but not actual data. a. simulation c. template b. situation d. scenario ANS: A

PTS: 1

REF: 491

3. Break-even analysis is a type of what-if analysis that concentrates on an activity at or around the point at which a product breaks even—or where the ____ is $0. a. profit c. income b. cost d. COGS ANS: A

PTS: 1

REF: 492

4. A ____ analysis asks, “How much will projected profits change if the cost of goods sold per unit changes by $1? By $2?” a. sensitivity c. systematic b. random d. simulated ANS: A

PTS: 1

REF: 493

5. Because you can compare the results of many calculations in data tables, they can be very useful when performing a ____ analysis. a. break-even c. both a and b b. sensitivity d. neither a nor b ANS: C

PTS: 1

REF: 494

6. Excel can be used to create two types of data tables: one-variable data tables and ____ data tables. a. multi-variable c. input b. two-variable d. output ANS: B

PTS: 1

REF: 494

7. Use a(n) ____ data table to see how different interest rates affect a loan payment. a. one-variable c. multi-variable b. two-variable d. input ANS: A

PTS: 1

REF: 494

8. A two-variable data table has two input cells and ____ result cell(s). a. one c. variable b. two d. random ANS: A

PTS: 1

REF: 494

9. Use a(n) ____ data table to see how two factors—for example, different interest rates and loan terms—affect a loan payment. a. one-variable c. multi-variable b. two-variable d. output ANS: B

PTS: 1

REF: 494

10. A data table depends on values and formulas used in a worksheet, and must appear ____ that contains this data. a. on the same worksheet c. in the same table b. in the same file d. in the same row ANS: A

PTS: 1

REF: 495

11. Income tax expense on income statements for ____ companies and the amount of income tax actually paid are two different numbers. a. publicly traded c. large

b. private ANS: A

d. small PTS: 1

REF: 495

12. If a company decides to use income tax expense as a factor in analyses, a method it could use to compute the effects is ____. a. rules based on Generally Accepted Accounting Principles (GAAP) b. tax-basis rules c. either a or b d. neither a nor b ANS: C

PTS: 1

REF: 495

13. Many companies decide to use the company’s ____ tax rate in their what-if analysis. a. marginal c. relative b. projected d. absolute ANS: A

PTS: 1

REF: 496

14. ____ margins are widely used in analysis, especially when evaluating the profitability of sales alternatives and performing break-even analysis. a. Lookup c. Data b. Contribution d. Source ANS: B

PTS: 1

REF: 496

15. A contribution margin is calculated by subtracting variable expenses from sales, and represents the amount of revenue that contributes to covering the ____ expenses of a company. a. variable c. tax b. fixed d. accounts payable ANS: B

PTS: 1

REF: 496

16. A one-variable data table allows you to compare results calculated from changes to ____ input value(s). a. only one c. one or two b. one or more d. two or more ANS: A

PTS: 1

REF: 498

17. Creating a data table in Excel involves the very important step(s) of ____. a. setting up the structure for the data table b. instructing Excel on how the data table’s structure relates to the input section of the worksheet c. both a and b d. neither a nor b ANS: C

PTS: 1

REF: 498

18. To specify which input cell in the worksheet relates to the values in the data table, open the ____ dialog box. a. Data Table c. Worksheet b. Input Cell d. What-if ANS: A

PTS: 1

REF: 500

19. To conduct a ____ analysis, you can ask, “How would the break-even point and profitability estimates change if an estimate about one or more other assumptions changes?” a. sensitivity c. systematic b. variable d. routine ANS: A

PTS: 1

REF: 503

20. In a two-variable data table, you can vary the values of two input variables, and show the results for ____ output value(s). a. one c. one or two b. two d. two or more ANS: A

PTS: 1

REF: 505

21. Creating the structure for a data table involves creating the set of ____ you want to use in your what-if analysis, and then indicating the results you want to see in the data table. a. input values c. combination values b. data values d. lookup values ANS: A

PTS: 1

REF: 505

22. You must structure a two-variable data table so that the input values are perpendicular to each other, and so that the table’s output formula is located ____ the two sets of input variables. a. on top of c. under b. at the intersection of d. next to ANS: B

PTS: 1

REF: 506

23. To open the Data Table dialog box, click the DATA tab on the ribbon, click the ____ Analysis button in the Data Tools group, and then click the Data Table command. a. What-If c. Data b. Sensitivity d. Data Table ANS: A

PTS: 1

REF: 506

24. In preparation for executing the command to complete the data table, first select the ____ that will constitute the completed data table. a. range c. input area b. output area d. chart area ANS: A

PTS: 1

REF: 507

25. As with one-variable data tables, you must specify how a two-variable data table is structured and which cells in the worksheet’s input section relate to the table’s ____. a. values c. formatting b. formula d. file size ANS: A

PTS: 1

REF: 507

26. Data tables can be used to give you ____. a. break-even information b. information about the interaction of various selling prices c. information about how sensitive profits are to price changes d. all of the above ANS: D

PTS: 1

REF: 510

27. You use the ____ to define and save sets of values as scenarios, and then you view and change them as necessary to produce and compare different results. a. Scenario Manager c. Values Manager b. Scenario Wizard d. Values Wizard ANS: A

PTS: 1

REF: 512

28. Data tables help you answer questions such as: ____. a. If I change the unit price, how does that affect gross profit? b. If I vary the unit price and the cost of goods sold, how does that affect income before taxes? c. both a and b d. neither a nor b ANS: C

PTS: 1

REF: 512

29. If a business problem involved three break-even analyses, you could create ____ data table(s) by varying the unit price and sales projection values, but that would involve generating and printing each data table to compare the analyses. a. one c. three different b. two different d. three identical ANS: C

PTS: 1

REF: 512

30. You can use the Scenario Manager to perform a what-if analysis with ____ input cell(s). a. at most one c. only one or two b. at most two d. more than two ANS: D

PTS: 1

REF: 512

31. As one of the final steps of creating a scenario, you create the scenario by naming it and then entering ____ into the Scenario Manager. a. data c. worksheet names b. file names d. formulas ANS: A

PTS: 1

REF: 513

32. To set up a scenario, you create or use a worksheet that contains sets of input and output cells, as you do with data tables. For example, in a worksheet listing sales assumptions and projections, the projections are ____. a. input cells c. guiding cells b. output cells d. lead cells ANS: B

PTS: 1

REF: 513

33. As with data tables, Excel allows you to run scenarios only on worksheets that have well-structured input and output sections, with the output section depending on the input section through the use of ____. a. formulas c. hyperlinks b. formats d. range names ANS: A

PTS: 1

REF: 513

34. Scenario reports can include data from only the first ____ scenarios. a. 32 c. 251

b. 151 ANS: C

d. 351 PTS: 1

REF: 517

35. To determine the ____ in a worksheet, ask which input assumptions you want to vary from one option to another. a. changing cells c. variance cells b. result cells d. formula cells ANS: A

PTS: 1

REF: 517

36. Excel allows up to ____ changing cells in each scenario. a. 32 c. 255 b. 51 d. 399 ANS: A

PTS: 1

REF: 517

37. Before you start creating scenarios, you can name the ____ cells you plan to use in the scenario. a. input c. both a and b b. result d. neither a nor b ANS: C

PTS: 1

REF: 518

38. Using ____ can make the process of creating scenarios and using them easier. a. range names c. row names b. cell names d. column names ANS: B

PTS: 1

REF: 518

39. Each ____ in a scenario must be unique within its scope. a. defined name c. comment b. refined name d. data label ANS: A

PTS: 1

REF: 518

40. A defined name can be used outside its scope if it is ____, which means if its original scope is identified in its reference. a. qualified c. archived b. saved d. linked ANS: A

PTS: 1

REF: 518

41. Defined names with a local or global scope can be created using the Define Name command in the Defined Names group on the ____ tab on the ribbon. a. FORMULAS c. DATA TABLES b. DATA d. WORKSHEET ANS: A

PTS: 1

REF: 518

42. Defined names with a global scope can be created for cells and ranges using the Name box in the Formula Bar, and using the ____ command in the Defined Names group on the FORMULAS tab. a. Create from Formula c. Create from Cell b. Create from Selection d. Create from Range ANS: B

PTS: 1

REF: 518

43. When applying names to cells and ranges, to indicate the start and end of words, the textbook recommends ____. a. using a mix of uppercase and lowercase letters b. including underscore ( _ ) characters c. including a period ( . ) character d. all of the above ANS: D

PTS: 1

REF: 519

44. Although Excel does not consider case when evaluating cell names, the use of case differences in cell names can make them ____. a. easier to read c. both a and b b. easier to interpret d. neither a nor b ANS: C

PTS: 1

REF: 521

45. The textbook recommends using defined names that are fairly ____ and meaningful. a. short c. unusual b. long d. common ANS: A

PTS: 1

REF: 521

46. Defined names can be deleted or edited in the ____ dialog box. a. Name Manager c. Manager b. Name d. Defined Names ANS: A

PTS: 1

47. You use the ____ to add a scenario. a. Scenario Wizard b. Scenario Manager ANS: B

PTS: 1

REF: 519

c. Case Manager d. Data Table Manager REF: 521

48. Open the Scenario Manager by clicking the DATA tab on the ribbon, clicking the ____ analysis button in the Data Tools group, and then clicking Scenario Manager. a. What-If c. Break-Even b. Sensitivity d. Contribution ANS: A

PTS: 1

REF: 521

49. Although you can use generic names for your scenarios, you should use more descriptive names because they appear on any scenario summary reports you ____. a. create c. both a and b b. print d. neither a nor b ANS: C

PTS: 1

REF: 521

50. Scenario names can contain up to 255 characters, including spaces. However, only the first ____ characters of the scenario name appear in the Scenario Manager list. a. 15 c. 35 b. 25 d. 45 ANS: C

PTS: 1

REF: 521

51. To view a scenario in the worksheet, click the scenario name in the Scenario Manager dialog box, and then click the ____ button. a. Open c. Display b. Show d. View ANS: B

PTS: 1

REF: 523

52. After you create a scenario, you can use the Scenario Manager ____ to change its values to extend your what-if analysis. a. dialog box c. status bar b. toolbar d. Wizard ANS: A

PTS: 1

REF: 526

53. You can use the Scenario Manager dialog box to change a scenario’s ____. a. values c. defined names for ranges b. defined names for cells d. all of the above ANS: A

PTS: 1

REF: 526

54. You can use the Scenario Manager to create two types of scenario reports: a scenario summary and a scenario ____. a. Flow Chart c. Workbook b. PivotTable d. Data Chart ANS: B

PTS: 1

REF: 528

55. You set up and print both types of scenario reports from the Scenario Manager dialog box by clicking the ____ button to open the Scenario Summary dialog box. a. Summary c. Print b. Scenario d. Scenario Summary ANS: A

PTS: 1

REF: 528

56. Before printing a scenario report, you indicate which ____ cells are of interest to you. a. result c. error b. formula d. defined ANS: A

PTS: 1

REF: 528

57. When you create a scenario summary report, Excel generates it as a ____ in the workbook. a. new worksheet c. new file b. new table d. new chart ANS: A

PTS: 1

REF: 528

58. Instead of listing all the cells, a scenario summary report shows the values for all ____ cells and for all indicated result cells for all scenarios. a. changing c. formula b. moving d. relative ANS: A

PTS: 1

REF: 528

59. Changing cells and result cells are listed by the names you gave them when you ____. a. created the scenarios c. saved the file b. ran a report d. none of the above

ANS: A

PTS: 1

REF: 529

60. To make the scenario summary easy to interpret and distribute, Excel ____. a. shades the changing cell values b. shades the cell names c. puts the report into a tabular format d. all of the above ANS: D

PTS: 1

REF: 529

61. Scenario summary reports are created with ____ as the worksheet title. a. Scenario Summary c. Summary b. Scenario d. Scenario Summary Report ANS: A

PTS: 1

REF: 529

62. A ____ extracts, organizes, and summarizes data so you can analyze it by making comparisons, detecting patterns and relationships, and recognizing trends. a. PivotTable c. PivotSummary b. PivotData d. PivotFile ANS: A

PTS: 1

REF: 530

63. Although a PivotTable usually summarizes information into different categories using functions such as ____, a scenario PivotTable lets you summarize the result cells from a scenario. a. COUNT c. MAX b. AVERAGE d. all of the above ANS: D

PTS: 1

REF: 530

64. After you create a PivotTable report, you can analyze the information in a different form by generating an associated ____, which contains the same elements as a regular Excel chart. a. ScenarioChart c. Data Chart b. PivotChart d. Data File ANS: B

PTS: 1

REF: 530

65. ____ results are those that are based on realistic but not actual data. a. Simulated c. Approximated b. Fictional d. Produced ANS: A

PTS: 1

REF: 535

66. In financial statistics, a probability distribution assigns a probability to every ____. a. interval of numbers c. column and row b. number d. formula ANS: A

PTS: 1

REF: 536

67. The VLOOKUP function compares a lookup value to the first column of a lookup table, and returns the corresponding value in ____ column of the lookup table. a. a designated c. the second b. the first d. the last ANS: A

PTS: 1

REF: 536

68. The ____ function generates a random number between 0 and 1. a. RANDOM c. RNDM b. RAND d. RDOM ANS: B

PTS: 1

REF: 537

69. When using a two-variable data table to run a simulation, one set of input values in the data table’s structure must be a sequence of numbers that represents the ____. a. number of iterations of the simulation c. number of input cells b. dates the file was saved d. number of output cells ANS: A

PTS: 1

REF: 539

70. When using data tables to run a simulation, it is usually best to base your analysis on ____. a. the raw data b. statistics about the data c. the number of iterations of the simulation d. the number of output cells ANS: B

PTS: 1

Case-Based Critical Thinking Questions Case 8-1

REF: 542

Beckham created the worksheets above to show the profitability and break-even points for his company. Please refer to the figure above as you answer the questions below.

71. Beckham’s boss has asked him to be more precise about how many units the company will need to sell to break even. What can Beckham do to get this information? a. Create a larger data table with smaller increments in specifying units sold. b. Create a larger data table with larger increments in specifying units sold. c. Change the input variable to sales revenue. d. Change the input variable to income.

ANS: A

PTS: 1

REF: 502

TOP: Critical Thinking

72. To conduct a sensitivity analysis, Beckham can ask: ____. a. How do the shareholders feel about these changes? b. How would the break-even point change when the assumptions remain the same? c. How many units must the company sell to break even? d. How would the break-even point change if an estimate about one or more other assumptions changes?

ANS: D

PTS: 1

REF: 503

TOP: Critical Thinking

73. Beckham has received new information regarding some of the assumptions. The estimate of the COGS per unit has changed from $75.75 to $95.00, and the estimated marketing expense has been cut from $75,000 to $60,000. What should Beckham do to show this change in the worksheet above? a. Calculate the changes manually to ensure the integrity of the data table. b. Change the worksheet to a two-variable data table. c. Change those items in the Assumptions section of the worksheet, and Excel will recalculate the data table. d. none of the above ANS: C

PTS: 1

REF: 503

TOP: Critical Thinking

74. Beckham has received word from the Purchasing Department that there could be some variance in the COGS. He wants to develop a worksheet that shows the effects of various interactions between the selling price and the cost of goods sold per unit on profitability. In the two-variable data table, how would he set it up? a. Place COGS and selling price next to each other in a column, and the table’s output formula for income in the third column. b. Place COGS and selling price perpendicular to each other, and the table’s output formula for income in the intersection of these two sets of input variables. c. Place income and selling price perpendicular to each other, and the table’s output formula for COGS in the intersection of these two sets of input variables. d. Place COGS and selling price in two subsequent rows, with the table’s output formula for income in the third row.

ANS: B

PTS: 1

Case-Based Critical Thinking Questions Case 8-2

REF: 506

TOP: Critical Thinking

Gabriel created the table above to help him organize financial information on the projected results of increased marketing campaigns for the swimsuit line. A change in marketing expense will change the number of units sold. Please refer to the table above when answering the questions below.

75. Gabriel needs to create five scenarios in the Swimwear worksheet. To do so, he needs to determine the changing cells in the worksheet. Which input values change under the various options, and what will the changing cells be in his scenarios? a. the marketing expense for each of the swimsuit styles b. the units sold for each of the swimsuit styles c. both a and b d. neither a nor b ANS: A

PTS: 1

REF: 517

TOP: Critical Thinking

76. Gabriel considers whether the average salary input item should be a changing cell. Although the average salary will change to $39,100 next year, it will not vary from that number for any of the five scenario options. Why would you recommend NOT making salary a changing cell? a. because Gabriel would have to update each of the five scenarios if the value changes again, causing inefficiency b. because the value is a “constant” across all five scenarios c. both a and b d. neither a nor b ANS: C

PTS: 1

REF: 517

TOP: Critical Thinking

77. Gabriel plans to name the input and result cells with range names. What would you recommend the name be for Option 3, “High End”? a. High End c. HE b. Option 3 d. High_End ANS: D

PTS: 1

REF: 519

TOP: Critical Thinking

78. Gabriel plans to use the Scenario Manager to add a scenario. He opens the Scenario Manager by clicking the DATA tab on the ribbon, and then clicking the ____. a. What-If analysis button in the Data Tools group b. Scenario Manager button in the Data Tools group c. What-If analysis button in the Scenario Manager group d. Scenario Manager button in the Scenario Manager group ANS: A

PTS: 1

REF: 520

TOP: Critical Thinking

79. Gabriel wants to edit his scenario. He can change everything below EXCEPT ____. a. scenario name c. comments b. changing cells d. defined names ANS: D

PTS: 1

REF: 526

TOP: Critical Thinking

80. Gabriel decides that he needs to delete one of his scenarios. How does he open the Scenario Manager in order to delete the desired scenario in the Scenarios box? a. Click the What-If Analysis button in the Data Tools group on the SCENARIOS tab on the ribbon. b. Click the Manage Scenarios button in the Data Tools group on the DATA tab on the ribbon. c. Click the What-If Analysis button in the Data Tools group on the DATA tab on the

ribbon. d. Click the Analysis button in the Manage Scenarios group on the DATA tab on the ribbon.

ANS: C

PTS: 1

REF: 527

TOP: Critical Thinking

COMPLETION 1. ____________________ is a process of changing values to see how those changes affect the outcome of formulas in an Excel worksheet.

ANS: What-if analysis What if analysis PTS: 1

REF: 491

2. The ____________________ Manager allows you to consolidate multiple what-if models in one worksheet.

ANS: Scenario PTS: 1

REF: 491

3. A(n) ____________________ table is a range of cells containing values and formulas that let you perform many calculations at once, and then compare the results of these variations on a single worksheet.

ANS: data PTS: 1

REF: 494

4. ____________________ analysis is a type of what-if analysis that concentrates on an activity at or around the point at which a product breaks even.

ANS: Break-even Break even PTS: 1

REF: 492

5. A(n) ____________________ data table has only one input cell and many result cells.

ANS: one-variable one variable PTS: 1

REF: 494

6. Both one-variable data tables and two-variable data tables have ____________________ cells, which are the cells containing values that can vary.

ANS: input

PTS: 1

REF: 494

7. Both one-variable data tables and two-variable data tables have ____________________ cells, which contain formulas that involve the input cells in their calculations in some way.

ANS: result PTS: 1

REF: 494

8. A(n) ____________________ margin is calculated by subtracting variable expenses from sales, and represents the amount of revenue that contributes to covering the fixed expenses of a company.

ANS: contribution PTS: 1

REF: 496

9. A(n) ____________________ data table has two lists of input values and one formula that refers to the two different input cells.

ANS: two-variable two variable PTS: 1

REF: 505

10. You can use the Scenario ____________________ to perform a what-if analysis with more than two input cells.

ANS: Manager PTS: 1

REF: 512

11. A(n) ____________________ is a set of values stored in a worksheet that describes different situations, such as worst-case, likely case, and best-case scenarios.

ANS: scenario PTS: 1

REF: 512

12. Scenarios are best created using sets of ____________________ value assumptions, such as best-case, likely case, and worst-case outcomes.

ANS: input PTS: 1

REF: 513

13. The Scenario Manager refers to input cells as ____________________ cells.

ANS: changing PTS: 1

REF: 513

14. The Scenario Manager refers to output cells as ____________________ cells.

ANS: result PTS: 1

REF: 513

15. A(n) ____________________ relationship means that the cost does not vary directly with the number of units, but varies as the units reach steps of quantities.

ANS: step-variable step variable PTS: 1

REF: 515

16. The ____________________ function is very useful when you want to sum a series of products, as long as the ranges involved are parallel to each other in the worksheet.

ANS: SUMPRODUCT PTS: 1

REF: 515

17. A range name, also called a(n) ____________________ name, can refer to a cell, range of cells, formula, or constant value in Excel.

ANS: defined PTS: 1

REF: 518

18. All defined names have a(n) ____________________, which specifies the location in which the name is valid.

ANS: scope PTS: 1

REF: 518

19. A defined name can have a scope for the entire workbook, which is referred to as the ____________________ level.

ANS: global PTS: 1

REF: 518

20. ____________________ results are those that are based on realistic but not actual data.

ANS: Simulated PTS: 1 MATCHING

REF: 535

Identify the letter of the choice that best matches the break-even point in the chart above. a. A d. D b. B e. E c. C 1.

2.

3.

4.

5.

Loss Fixed costs Profit Break-even point Total revenue line

1.

2.

3.

4.

5.

ANS: E ANS: D ANS: A ANS: B ANS: C

PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1

REF: REF: REF: REF: REF:

493 493 493 493 493

Identify the letter of the choice that best matches the word or phrase with its definition. a. changing cells e. input cells b. result cells f. two-variable data table c. one-variable data table g. range name d. data table 6.

7.

8.

9.

10.

11.

12.

A multiple-output formula that shows the results that come from one set of input values A one-output formula that shows the results of the interaction of two different sets of input values A range of cells containing values and formulas Data table cells containing values that can vary Contain formulas that involve the input cells in their calculations The Scenario Manager’s version of input cells Refers to a cell, range of cells, formula, or constant value

6.

7.

8.

9.

10.

11.

12.

ANS: C ANS: F ANS: D ANS: E ANS: B ANS: A ANS: G

PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF:

494 494 494 494 494 513 518

ESSAY 1. What is a scenario and how is it used? What are the correct terms for a scenario’s input and output cells?

ANS: • A scenario is a set of values stored in a worksheet that describes different situations, such as worst-case, likely case, and best-case scenarios. You use the Scenario Manager to define and save these sets of values as scenarios, and then you view and change them as necessary to produce and compare different results. To compare scenarios, you can create a summary report that lists the scenarios side by side or in a PivotTable.

• The Scenario Manager refers to the input cells (as they are called in data tables) as the changing cells because these are the cells that you want to change. The output cells are called the result cells.

PTS: 1

REF: 512-513

TOP: Critical Thinking

2. You are faced with 100 rows of simulated profit data. You know that when using data tables for a simulation, it is helpful to calculate statistics about the data. Do you base your decisions about the data on the profit data or the statistics? What type of statistics would you run?

ANS: • When using data tables to run a simulation, it is usually best to calculate some statistics about the data, and to base your analysis on those statistics, not the raw data itself.

• The most basic statistics are often the best because they are the easiest to understand and, thus, the most readily interpreted. The textbook suggested calculating the average, maximum, and minimum simulated profit, and the standard deviation of the simulated profits for each selling price option.

PTS: 1

REF: 542

TOP: Critical Thinking

3. What are what-if analysis, break-even analysis, and sensitivity analysis? Please include an example of a situation using each type of analysis (one example each).

ANS: • What-if analysis is a process of changing values to see how those changes affect the outcome of formulas in an Excel worksheet. What-if analysis allows you to see what output results if the value of various input assumptions changes. At its most basic level, what-if analysis involves asking a question about a worksheet, such as “What if the revenue of the swimwear product line increased by $10,000 per month?”

• Break-even analysis is a type of what-if analysis that concentrates on an activity at or around the point at which a product breaks even—in other words, the point at which the expenses of creating and selling the product are equal to the revenue it produces, or where the profit is $0. For example, suppose your product line has $60,000 of fixed costs. In addition, each product has $20.00 of variable costs.

Break-even analysis will calculate how many products your product line needs to sell to break even, or have a profit of $0.

• Sensitivity analysis is another type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions. Because what-if analysis usually involves many estimates and other projections, sensitivity analysis helps managers learn how tolerant the projected results are of changes in those estimates and assumptions. For example, a sensitivity analysis asks, “How much will projected profits change if the cost of goods sold per unit changes by $1? By $2?”

PTS: 1

REF: 491-493

TOP: Critical Thinking

Chapter 9: Enhancing Decision Making with Solver TRUE/FALSE 1. Goal Seek lets you change multiple cells.

ANS: F

PTS: 1

REF: 553

2. With Solver, you can change the values of up to 500 cells at one time to reach a goal in the objective cell.

ANS: F

PTS: 1

REF: 564

3. Any Solver solution requires a well-organized spreadsheet that links the variable cells with the objective cell and the known facts about the problem with the objective cell.

ANS: T

PTS: 1

REF: 557

4. Policy constraints are often based on company history and, to some extent, inertia.

ANS: T

PTS: 1

REF: 558

5. After you load the Solver Add-in, the Solver command is available in the Data group on the DATA tab.

ANS: F

PTS: 1

REF: 558

6. The default solving method in the Solver Parameters dialog box is GRG Nonlinear.

ANS: T

PTS: 1

REF: 559

7. The cells in a worksheet usually display combinations of text or numbers; the math that generates the answers is hidden.

ANS: T

PTS: 1

REF: 560

8. It is more difficult to use Solver with linear functions than nonlinear functions.

ANS: F

PTS: 1

REF: 560

9. When solving problems with nonlinear objective functions, you must enter a variety of starting points to ensure that the answer that Solver reports is actually the optimal solution.

ANS: T

PTS: 1

REF: 561

10. Using the integer constraint significantly adds to the computational complexity of a Solver model.

ANS: T

PTS: 1

REF: 566

11. Solver can produce four different reports: summary, answer, sensitivity, and limits.

ANS: F

PTS: 1

REF: 569

12. A binding status indicates that the constraint does not have a final value that is equal to the value of the constraint.

ANS: F

PTS: 1

REF: 570

13. When Solver cannot find a feasible solution, it still produces an answer report.

ANS: F

PTS: 1

REF: 574

14. Range names are usually the source of the problem when Solver cannot find a feasible solution.

ANS: F

PTS: 1

REF: 575

15. By default, Solver attempts to solve a problem 230 times before it stops working and determines that there is no feasible solution.

ANS: F

PTS: 1

REF: 576

16. If all of the constraints, which represent the availability of resources, have slack values, the solution isn’t the optimal solution that maximizes the available resources.

ANS: T

PTS: 1

REF: 577

17. When determining the feasibility of a solution, it is helpful to examine the model’s constraints visually—for example, in a two-dimensional chart—to understand them.

ANS: T

PTS: 1

REF: 577

18. Before changing the Solver parameters to troubleshoot a problem in a Solver model, you might want to save it as a baseline so you can return to it later if necessary.

ANS: T

PTS: 1

REF: 588

19. When troubleshooting, one of the best options is to save the worksheet and the Solver model in a blank section of the worksheet.

ANS: T

PTS: 1

REF: 588

20. When you copy a worksheet that contains a Solver model, the Solver model is not saved with the new worksheet unless you save it as a model in a blank area of the worksheet and load the Solver model into the new worksheet.

ANS: T

PTS: 1

REF: 593

MODIFIED TRUE/FALSE 1. You can set up complex business models in a worksheet and use Goal Seek to change more than one cell at a time. _________________________ ANS: F, Solver PTS: 1

REF: 553

2. The combination of the objective cell, variable cells, and constraints that are used to solve a problem is called a(n) Goal model. _________________________ ANS: F, Solver PTS: 1

REF: 556

3. Solver changes the values in the variable cells to maximize, minimize, or set the objective cell to a specific value. _________________________ ANS: T

PTS: 1

REF: 556

4. As Solver inserts values into the variable cells, it also examines the attributes to ensure that the solution satisfies them. _________________________ ANS: F, constraints PTS: 1

REF: 557

5. The size of a truck is an example of a(n) political limit. _________________________ ANS: F, physical PTS: 1

REF: 558

6. An example of a(n) nonlinear function is the relationship between the price of an item and the demand for that item. _________________________ ANS: T

PTS: 1

REF: 560

7. A(n) output function is a mathematical formula that relates the decision variables or variable cells to the desired outcome. _________________________ ANS: F, objective PTS: 1

REF: 560

8. In solving business problems, you usually need to maximize items such as revenue or profit, and minimize items such as expenses or losses. _________________________ ANS: T

PTS: 1

REF: 561

9. Because Solver is a(n) database model, there is nothing to keep it from producing negative results. _________________________ ANS: F, mathematical PTS: 1

REF: 563

10. After changing one or more values in your constraints table, you must click the Solve button in the Solver Parameters dialog box to “resolve” the problem because Solver is not included in the automatic recalculation that Excel performs when you change a cell in a worksheet. _________________________

ANS: T

PTS: 1

REF: 564

11. When a constraint is binding in a Solver answer report, the first column is set to 0 because the constraint equals the final value. _________________________ ANS: F, slack PTS: 1

REF: 570

12. Ranges are the boundaries that define the feasible solution area. _________________________ ANS: F Constraints constraints PTS: 1

REF: 575

13. If you try to solve a problem in which there are no constraints or too few constraints, the solution is bounded. _________________________ ANS: F, unbounded PTS: 1

REF: 576

14. When using the Solver model, a value of FALSE indicates a satisfied constraint. _________________________ ANS: F TRUE true True PTS: 1

REF: 589

15. In a Solver model, a value of TRUE indicates a constraint that could not be satisfied. _________________________ ANS: F FALSE false False PTS: 1

REF: 589

MULTIPLE CHOICE 1. Although Goal Seek only lets you change ____, it can be quite useful at times. a. one cell c. three cells b. two cells d. four cells ANS: A

PTS: 1

REF: 553

2. You can set up complex business models in a worksheet and use Solver to determine the optimal set of decision inputs to improve profitability, such as minimizing costs or ____ income. a. maximizing c. cutting b. reducing d. lowering ANS: A

PTS: 1

REF: 553

3. The method you use to narrow the available options for a problem so you can choose the best potential outcome is an analytical process known as ____ in the field of management science. a. optimization c. targeting b. simulation d. breaking even ANS: A

PTS: 1

REF: 554

4. You must answer four questions before using optimization to solve a problem. They do NOT include ____. a. How many resources are there and how many are needed? b. How many resources does each decision variable consume? c. How much does each decision variable contribute to the objective? d. How much does each decision variable cost?

ANS: D

PTS: 1

REF: 554

5. ____ is similar to Goal Seek in that it lets you specify what you want to occur in a particular cell by changing the values of one or more related cells. a. Solve-it c. Source b. Solver d. Source-it ANS: B

PTS: 1

REF: 556

6. When you use ____, you are limited to entering values in a single cell to reach a goal in another related cell. a. Goal Seek c. Source-It b. Solver d. Goal Solver ANS: A

PTS: 1

REF: 556

7. The combination of the objective cell, variable cells, and constraints that are used to solve a problem is called a ____. a. Solver model c. Solver formula b. Solver function d. Solver data table ANS: A

PTS: 1

REF: 556

8. Which of the following is NOT one of the three required parameters when running a Solver model? a. the objective cell that you want to maximize, minimize, or set to a specific value b. the variable cells that produce the desired results in the objective cell c. the constraints that limit how to solve the problem d. the formula you plan to use to produce the result in the objective cell ANS: D

PTS: 1

REF: 557

9. When you use Goal Seek, you are limited to entering values in a single cell to reach a goal in another related cell. This related cell is known as the ____ in Solver. a. goal cell c. end cell b. objective cell d. destination cell

ANS: B

PTS: 1

REF: 556

10. Solver lets you establish ____ that restrict the values that are entered into the variable cells. a. attributes c. scenarios b. events d. constraints ANS: D

PTS: 1

REF: 556

11. Solver changes the values in the variable cells (also called ____ variables) to maximize, minimize, or set the objective cell to a specific value. a. decision c. relative b. flexible d. random ANS: A

PTS: 1

REF: 556

12. Any Solver solution requires a well-organized spreadsheet that links the variable cells with the ____, and links the known facts about the problem with the objective cell. a. objective cell c. PivotTable b. range names d. summary reports ANS: A

PTS: 1

REF: 557

13. Solver inserts values in the ____ until the value of the objective cell has reached the specified goal. a. variable cells c. objective cells b. result cells d. formula cells ANS: A

PTS: 1

REF: 557

14. You can use Solver to determine how to allocate ____, such as people, budgets, or inventories, in such a way as to achieve the greatest benefits. a. resources c. data b. formulas d. research ANS: A

PTS: 1

REF: 557

15. By including the constraints in your worksheet, you will have ____. a. a record of them for later use b. a data-entry reference when you set up Solver c. both a and b d. neither a nor b ANS: C

PTS: 1

REF: 558

16. Solver lets you create a ____ model of a business scenario. a. mathematical c. random b. business d. qualitative ANS: A

PTS: 1

REF: 558

17. A constraint that only fully loaded trucks make deliveries is an example of a ____ limit. a. policy c. relative b. physical d. local ANS: A

PTS: 1

REF: 558

18. You can base the constraints that limit the value of a particular cell in the Solver model on ____.

a. company policies b. physical limits ANS: C

c. both a and b d. neither a nor b PTS: 1

REF: 558

19. After you load the Solver Add-in, the Solver command is available in the ____ group on the DATA tab. a. Analysis c. Formulas b. Data d. What-if ANS: A

PTS: 1

REF: 558

20. Restricting the number of gallons of milk that a customer can buy at a sale price is a ____. a. physical limit c. COGS limit b. policy limit d. maximizing limit ANS: B

PTS: 1

REF: 558

21. The default solving method when the Solver Parameters dialog box opens is ____. a. GRG Nonlinear c. Integral Calculus b. Baseline d. Integrated ANS: A

PTS: 1

REF: 559

22. The model you create when using Solver is ____. a. mathematical c. scientific b. qualitative d. physical ANS: A

PTS: 1

REF: 563

23. The objective function or formula that you use in the objective cell can be ____. a. linear c. either a or b b. nonlinear d. neither a nor b ANS: C

PTS: 1

REF: 560

24. When you plot a linear function in a chart, the result is a ____. a. straight line c. bar b. curved line d. circle ANS: A

PTS: 1

REF: 560

25. The cells in a worksheet usually display combinations of text or numbers; the math that generates the answers is ____ in the worksheet itself. a. visible c. written in bold text b. not visible d. none of the above ANS: B

PTS: 1

REF: 560

26. When you graph a nonlinear function on two dimensions, it results in a ____. a. straight line c. bar b. curved line d. circle ANS: B

PTS: 1

REF: 560

27. When working with Solver, you maximize, minimize, or set the objective cell to a certain value by selecting the Max (maximum), Min (minimum), or ____ option button, respectively.

a. Value Of b. Value ANS: A

c. Cell reference d. Number PTS: 1

REF: 561

28. A(n) ____ function is a mathematical formula that relates the decision variables or variable cells to the desired outcome. a. objective c. relative b. subjective d. absolute ANS: A

PTS: 1

REF: 561

29. In Solver, if you select the Value Of option button, you also must enter a(n) ____. a. specific value c. range name b. worksheet name d. option name ANS: A

PTS: 1

REF: 561

30. Each constraint contains a cell reference to a cell in a worksheet, a ____, and a cell reference or a constraint value. a. formula c. physical limit b. comparison operator d. function ANS: B

PTS: 1

REF: 561

31. The ____ operators you can use in Solver are less than or equal to (<=), equal to (=), greater than or equal to (>=), integer (int), binary (bin), and different (dif). a. comparison c. Scenario b. match d. simple ANS: A

PTS: 1

32. The Cell Reference input box accepts ____. a. cell references b. values ANS: A

PTS: 1

REF: 562

c. both a and b d. neither a nor b REF: 562

33. The Constraint input box can contain values, references to cells that contain those values, and ____. a. formulas c. hyperlinks b. row headers d. PivotTables ANS: A

PTS: 1

REF: 562

34. Although you can enter constraints directly into the ____ dialog box without creating a constraints table in your worksheet, creating a constraints table in the worksheet is a much better way of organizing your Solver models. a. Solver c. Solver Parameters b. Constraints d. Solver Constraints ANS: C

PTS: 1

REF: 564

35. After changing one or more values in your constraints table, you must click the Solve button in the Solver Parameters dialog box to “resolve” the problem because Solver is not included in the ____ that Excel performs when you change a cell in a worksheet.

a. automatic recalculation b. manual recalculation ANS: A

PTS: 1

c. randomly timed recalculation d. scheduled recalculation REF: 564

36. Constraints that are entered into the Add Constraint dialog box are stored in Solver and are only visible when you open the Solver Parameters dialog box; you can see only ____ constraint(s) at a time in this way. a. one c. ten b. six d. thirteen ANS: D

PTS: 1

REF: 564

37. Creating a(n) ____ table in a worksheet organizes the constraints and makes them visible in the worksheet at any time, and without the limitation of having to open the Solver Parameters dialog box and scroll the constraints to view them. a. constraints c. input b. data d. results ANS: A

PTS: 1

REF: 564

38. Having a cell in the worksheet that contains the constraint value makes it possible for you to refer to each constraint value as a cell reference in the ____ dialog box. a. Value c. Constraint b. Add Constraint d. Cell Value ANS: B

PTS: 1

REF: 564

39. You can use ____ in the Constraint input box of the Add Constraint dialog box instead of entering a specific value. a. cell references c. workbook references b. file references d. worksheet references ANS: A

PTS: 1

REF: 564

40. If you keep the Solver solution, the original values are lost and you cannot recover them unless you previously ____. a. highlighted them c. deleted them b. saved them d. named them ANS: B

PTS: 1

REF: 565

41. The constraints that you add to the Solver Parameters dialog box are ____. a. saved with the worksheet c. archived in a constraints folder b. deleted when the worksheet is saved d. none of the above ANS: A

PTS: 1

REF: 565

42. A ____ saves the result of a Solver model so that you can load it later. a. scenario c. situation b. scheme d. save set ANS: A

PTS: 1

REF: 568

43. Large Solver models run noticeably slower when they include integer constraints because Solver uses a different solution method, called ____, when a model includes integer constraints.

a. integer programming b. data programming ANS: A

PTS: 1

c. Web programming d. Excel programming REF: 566

44. Click the Save Scenario button in the ____ dialog box to open the Save Scenario dialog box. a. Solver Results c. Solver Summary b. Solver Save d. Solver Scenario ANS: A

PTS: 1

REF: 568

45. To view a previously saved scenario, select it in the Scenarios box, and then click the ____ button to update the worksheet with the data saved in the scenario. a. Show c. View b. Display d. Save ANS: A

PTS: 1

REF: 568

46. ____ reports are NOT one of the three reports Solver can produce. a. Answer c. Limit b. Sensitivity d. Break-even ANS: D

PTS: 1

REF: 569

47. When Solver produces an acceptable solution to the problem you identified, you can use the ____ option button in the Solver Results dialog box to permanently change the values in the worksheet to reflect the solution. a. Keep Solver Solution c. Solver Solution b. Keep Solution d. Permanent Solution ANS: A

PTS: 1

REF: 569

48. The ____ report includes detailed information about the objective cell, variable cells, and constraints used in the Solver model. a. answer c. limits b. sensitivity d. break-even ANS: A

PTS: 1

REF: 570

49. The ____ section of the answer report includes the objective cell reference and name, the original value in the cell, and the final value in the cell. a. Objective Cell (Max) c. Constraints b. Adjustable Cells d. Output ANS: A

PTS: 1

REF: 570

50. The ____ section of the answer report includes the cell reference, name, original value, and final value of the variable cells in a worksheet. a. Objective Cell (Max) c. Constraints b. Variable Cells d. Output ANS: B

PTS: 1

REF: 570

51. The ____ section of the answer report contains the cell reference, name, cell value, and formula for cells that have constraints. a. Objective Cell (Max) c. Constraints

b. Adjustable Cells ANS: C

d. Output PTS: 1

REF: 570

52. Of the three Solver reports, the ____ report is the most frequently used and most useful report for business users. a. answer c. limits b. sensitivity d. break-even ANS: A

PTS: 1

REF: 569

53. In the Solver answer report, when the status is ____, the slack column indicates the difference between the final value and the constraint. a. binding c. targeted b. not binding d. untargeted ANS: B

PTS: 1

REF: 570

54. A ____ status indicates that a constraint has a final value that is equal to the value of the constraint. a. binding c. slack b. not binding d. global ANS: A

PTS: 1

REF: 570

55. A(n) ____ results when Solver cannot determine the combination of decision variables that satisfies all of the constraints. a. infeasible solution c. error b. wrong solution d. dead end ANS: A

PTS: 1

REF: 574

56. There are three steps to troubleshoot an infeasible solution in Solver. Which of the following is NOT one of them? a. Correct any data-entry errors in the Solver Parameters dialog box. b. Review the constraints to see if a policy constraint is the cause of the infeasibility. c. Try loosening a constraint value for each suspected problem. d. Try getting a solution from Goal Seek instead.

ANS: D

PTS: 1

REF: 575 | 576

57. After you have identified the criteria that prevent a solution from being feasible, you have two choices: You can do nothing and declare that there is no solution to the problem, or you can ____ to create a feasible solution. a. add data c. add constraints b. adjust the constraints d. run a summary report ANS: B

PTS: 1

REF: 576

58. By default, Solver attempts to solve a problem ____ times before it stops working and determines that there is no feasible solution. a. 10 c. 500 b. 100 d. 750 ANS: B

PTS: 1

REF: 576

59. To change the number of times Solver attempts to solve a problem, click Options in the Solver Parameters dialog box, change the value in the ____ box, and then click the OK button. a. Iterations c. Count b. Solve d. Options ANS: A

PTS: 1

REF: 576

60. If Solver attempts the maximum number of iterations without the objective cell converging to an answer, the assumption is that the solution is ____. a. bounded c. targeted b. unbounded d. untargeted ANS: B

PTS: 1

REF: 576

61. If all of the constraints, which represent the availability of resources, have ____ values, the solution isn’t the optimal solution that maximizes the available resources. a. slack c. input b. constraint d. result ANS: A

PTS: 1

REF: 577

62. The _______________ function sums a series of products in ranges of identical sizes, called arrays, that are parallel to each other in a worksheet. a. SUMPRODUCT c. ADDPRODUCT b. SUMPROD d. ADDPROD ANS: A

PTS: 1

REF: 585

63. Before changing the Solver parameters to troubleshoot a problem in a Solver model, you might want to ____ so you can return to it later if necessary. a. save it as a baseline c. open it in a second window b. change the filename d. none of the above ANS: A

PTS: 1

REF: 588

64. With Solver, you can copy the worksheet and solution to a new worksheet for troubleshooting. A better option, however, is to save the worksheet and the Solver model in a ____. a. blank section of the worksheet c. Web file b. new file d. PivotTable ANS: A

PTS: 1

REF: 588

65. Saving a Solver model saves the ____ that were used in the Solver model so you can load them later if necessary. a. Solver parameters c. Solver PivotTables b. Solver attributes d. none of the above ANS: A

PTS: 1

REF: 588

66. The ____ dialog box lets you put limits on the amount of iterations Solver will attempt to solve a problem. a. Solver Results c. Scenario Manager b. Solver Parameters d. Options ANS: D

PTS: 1

REF: 576

67. When using the Solver model, a value of ____ indicates a satisfied constraint. a. TRUE c. NO b. YES d. FALSE ANS: A

PTS: 1

REF: 589

68. Sometimes, you might need to save several Solver models in a worksheet to track your progress in solving a specific problem. Without ____, it might be difficult to determine which saved Solver model appears in the different ranges. a. formulas c. hyperlinks b. labels d. cell references ANS: B

PTS: 1

REF: 593

69. A(n) ____ problem is an optimization problem in which there is a one-to-one relationship between a resource and an assignment or a job. a. assignment c. one-to-one b. job d. objective ANS: A

PTS: 1

REF: 594

70. If you add a(n) ____ constraint to the variable cells, Solver will use the value 1 or 0 in the solution. a. integer c. bounded b. binary d. unbounded ANS: B

PTS: 1

Case-Based Critical Thinking Questions Case 9-1

REF: 594

Ava is setting up a production model for her jewelry-making business. She decides to use Solver to find the optimal number of topaz and onyx pendants to produce with the given constraints. One of the most important constraints is that the minimum number to produce is 100 of each. Please answer the questions below, referencing the above figure as needed.

71. Ava is using Solver to determine her production schedule for pendants. Ava clicks the Set Objective box, and then she clicks cell G6 because it contains the ____. a. formula to calculate the total number of onyx and topaz pendants to produce b. formula listing the total cost constraint c. minimum number of onyx and topaz pendants to produce d. formula adding up the total costs to produce the pendants ANS: A

PTS: 1

REF: 561

TOP: Critical Thinking

72. Ava then needs to specify whether Solver should maximize, minimize, or set the objective cell to a certain value. She would choose the ____ option button. a. Max c. Value Of b. Min d. none of the above ANS: A

PTS: 1

REF: 561

TOP: Critical Thinking

73. Ava is ready to fill in the By Changing Variable Cells box. She would choose the cells containing the ____. a. minimum number of onyx and topaz pendants to produce b. range representing the number of onyx and topaz pendants produced c. unit cost for both the onyx and topaz pendants d. total cost to produce each pendant

ANS: B

PTS: 1

REF: 561

TOP: Critical Thinking

74. Ava’s production plan contains no nonlinear formulas or arrangement. What should she do to the solving method used by the Solver? a. Leave the default GRG Nonlinear solving method. b. Change the solving method to Goal Seek. c. Change the solving method to Binary LP. d. Change the solving method to Simplex LP. ANS: D

PTS: 1

REF: 559

TOP: Critical Thinking

75. Ava is ready to add the constraints and clicks the Add button. Each constraint contains a cell reference to a cell in the worksheet, a comparison operator, and a cell reference or a constraint value. Ava’s first constraint is that total cost (H6) cannot exceed $10,000 (C10). She would enter these parameters in the appropriate text box: ____. a. H6 <= C10 c. either a or b b. H6 <= 10,000 d. neither a nor b ANS: C

PTS: 1

REF: 561 | 562

TOP: Critical Thinking

76. To keep Solver from returning a negative number as a solution, Ava can ____. a. enter the constraint for each pendant that the quantity produced must be greater than or equal to 100 b. enter the constraint for each pendant that the quantity produced must be less than or equal to 100 c. enter a plus sign in the Value Of box d. none of the above ANS: A

PTS: 1

Case-Based Critical Thinking Questions Case 9-2

REF: 581

TOP: Critical Thinking

Rick has created the chart above outlining the information he has on producing toy trains. He will create a two-dimensional chart to visualize the information. Please refer to the chart above as you answer the questions below.

77. Looking at the chart above, what would the mathematical formula be for required molding time? a. Required molding time = (0.20 x (Model I quantity)) - (0.20 x (Model II quantity)) b. Required molding time = (70.0 x (Model I quantity)) + (70.0 x (Model II quantity)) c. Required molding time = (0.70 x (Model I quantity)) - (0.70 x (Model II quantity)) d. Required molding time = (0.20 x (Model I quantity)) + (0.20 x (Model II quantity)) ANS: D

PTS: 1

REF: 577 | 578

TOP: Critical Thinking

78. Rick now needs to determine the required finished time. The correct mathematical model is ____. a. Required finishing time = (0.20 x (Model I quantity)) + (0.50 x (Model II quantity)) b. Required finishing time = (0.20 x (Model I quantity)) x (0.50 x (Model II quantity)) c. Required finishing time = (0.20 x (Model I quantity)) - (0.50 x (Model II quantity)) d. Required finishing time = (0.20 + 0.50) x (Model I quantity + Model II quantity) ANS: A

PTS: 1

REF: 577 | 579

TOP: Critical Thinking

79. Rick runs Solver and receives an infeasible solution. To create a feasible solution, one idea he has is to loosen some of the constraints. He knows that loosening criteria with a less than or equal to operator will then ____. a. increase values c. either a or b b. decrease values d. neither a nor b ANS: A

PTS: 1

REF: 574

TOP: Critical Thinking

80. The slack for molding time for Sample 3 shows ____. a. 10 hours of molding time, with 5 hours borrowed from finishing time b. 10 hours too many used for molding time, with 5 hours of unused finishing time c. 10 hours of unused molding time, with 5 hours too many used for finishing time d. none of the above ANS: C

PTS: 1

REF: 577

TOP: Critical Thinking

COMPLETION 1. In the field of management science, the method you use to narrow the available options for a problem so you can choose the best potential outcome is an analytical process known as ____________________.

ANS: optimization Optimization PTS: 1

REF: 554

2. With Solver, you can change the values in many cells at one time to reach a goal in the ____________________ cell.

ANS: objective

PTS: 1

REF: 556

3. Solver also lets you establish ____________________ that restrict the values that are entered into the variable cells.

ANS: constraints PTS: 1

REF: 556

4. The combination of the objective cell, variable cells, and constraints that are used to solve a problem is called a(n) ____________________ model.

ANS: Solver PTS: 1

REF: 556

5. To start Solver, Beth clicks the Solver button in the ____________________ group on the DATA tab on the ribbon.

ANS: Analysis PTS: 1

REF: 558

6. A(n) ____________________ function results when a decision variable is multiplied by a constant, such as price.

ANS: linear PTS: 1

REF: 560

7. When you cannot describe a function in linear terms, it is called a(n) ____________________ function.

ANS: nonlinear PTS: 1

REF: 560

8. Maximize Z = Q1 + Q2 is an example of a(n) ____________________ function, a mathematical formula that relates the decision variables or variable cells to the desired outcome.

ANS: objective PTS: 1

REF: 561

9. One of the options when assigning constraints to a Solver model is to include the ____________________ operator, which you might be tempted to include automatically when a value in your Solver model is expressed as a whole number.

ANS: integer int PTS: 1

REF: 566

10. A(n) ____________________ saves the result of a Solver model so that you can load it later.

ANS: scenario PTS: 1

REF: 568

11. When Solver produces an acceptable solution to the problem you identified, you can use the Keep Solver Solution option button in the ____________________ dialog box to permanently change the values in the worksheet to reflect the solution.

ANS: Solver Results PTS: 1

REF: 568

12. A(n) ____________________ status indicates that the constraint has a final value that is equal to the value of the constraint.

ANS: binding PTS: 1

REF: 570

13. ____________________ are the boundaries that define the feasible solution area.

ANS: Constraints PTS: 1

REF: 575

14. A(n) ____________________ solution results when Solver cannot determine the combination of decision variables that satisfies all of the constraints.

ANS: infeasible PTS: 1

REF: 575

15. A(n) ____________________ solution occurs in situations in which the feasible solution is unrestrained or unlimited on some dimension.

ANS: unbounded PTS: 1

REF: 576

16. The ____________________ function sums a series of products in ranges of identical sizes, called arrays, which are parallel to each other in a worksheet.

ANS: SUMPRODUCT PTS: 1

REF: 585

17. Saving a(n) ____________________ saves the Solver parameters that were used in the Solver model so you can load them later, if necessary.

ANS: Solver model Solver Model

PTS: 1

REF: 588

18. When using the Solver model, a value of ____________________ indicates a satisfied constraint.

ANS: TRUE PTS: 1

REF: 589

19. It is a good idea to use ____________________ in the worksheet to identify which Solver model is stored in the different ranges of the worksheet.

ANS: labels PTS: 1

REF: 593

20. The ____________________ constraint assigns the value 0 or 1 to variable cells.

ANS: binary PTS: 1

REF: 594

MATCHING Identify the letter of the choice that best matches the definition of the word or phrase. a. linear function d. infeasible solution b. nonlinear function e. objective function c. unbounded solution f. Solver model 1. When you cannot describe a function in linear terms 2. Results when Solver cannot determine the combination of decision variables that satisfies all of the constraints 3. Results when a decision variable is multiplied by a constant, such as price 4. The combination of the objective cell, variable cells, and constraints that are used to solve a problem 5. Occurs in situations in which the feasible solution is unrestrained or unlimited on some dimension 6. A mathematical formula that relates the decision variables or variable cells to the desired outcome 1.

2.

3.

4.

5.

6.

ANS: B ANS: D ANS: A ANS: F ANS: C ANS: E

PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1

REF: REF: REF: REF: REF: REF:

560 574 560 556 576 561

Identify the letter of the choice that best matches the items in the Solver answer report above. a. #1 d. #4 b. #2 e. #5 c. #3 f. #6 7. Includes the objective cell reference and name (which is generated from the worksheet’s labels), the original value in the cell, and the final value in the cell 8. Includes the cell reference, name, original value, and final value of the variable cells in the worksheet 9. The constraint has a final value that is equal to the value of the constraint 10. Includes detailed information about the objective cell, variable cells, and constraints 11. Contains the cell reference, name, cell value, and formula for cells that have constraints 12. The constraint does not have a final value that is equal to the value of the constraint 7.

8.

9.

10.

11.

12.

ANS: B ANS: C ANS: F ANS: A ANS: D ANS: E

PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1

REF: REF: REF: REF: REF: REF:

570 570 570 570 570 570

ESSAY 1. Describe how to create a Solver model. Please include these key terms in your descriptions: Solver model, objective cell, variable cells, and constraints.

ANS: Solver is similar to Goal Seek in that it lets you specify what you want to occur in a particular cell by letting Excel change the values of one or more related cells. Goal Seek is limited to changing values in a single cell to reach a goal in another related cell. This related cell is known as the objective cell in Solver. With Solver, you can change the values in as many as 200 cells at one time to reach a goal in the objective cell (which in earlier versions of Excel was called the target cell).

Solver changes the values in the variable cells, also known as decision variables, to maximize, minimize, or set the objective cell to a specific value. Solver also lets you establish constraints that restrict the values that are entered into the variable, or changing, cells. The combination of a worksheet with the objective cell, variable cells, and constraints used to solve the problem is called the Solver model, or model.

There are three required parameters when running a Solver model: the objective cell that you want to maximize, minimize, or set to a specific value; the variable cell(s) that Solver uses to produce the desired results in the objective cell; and the constraint(s) that limit how to solve the problem. Any Solver solution requires a well-organized spreadsheet that links the variable cells with the objective cell, and links the known facts about the problem with the objective cell. Solver inserts values in the variable cells until the value of the objective cell has reached the specified goal. At the same time that Solver is inserting values into the variable cells, it examines the constraints to ensure that the solution satisfies them. You can use Solver to determine how to allocate resources, such as people, budgets, or inventories, in such a way as to achieve the greatest benefits.

PTS: 1

REF: 556 | 557

TOP: Critical Thinking

2. Solver can be used with either linear or nonlinear functions. Define linear and nonlinear functions, and give an example of a situation where you would find each of them.

ANS: • A linear function results when a decision variable is multiplied by a constant, such as price. When you plot a linear function in a chart, the result is a straight line, thus the term linear. An example of a linear function is a production schedule where you are determining the optimal quantities to make of two different items.

• When you cannot describe a function in linear terms, it is called a nonlinear function. When you graph a nonlinear function on two dimensions, it results in a curve, not a straight line. An example of a nonlinear function is the relationship between the price of an item and the demand for that item. As the price of the item increases, the demand for the item might decrease because demand varies as a function of price. Calculating the revenue (price multiplied by demand) for an item means that you are multiplying price by a function of price. The result is a nonlinear function or a curve.

PTS: 1

REF: 560

TOP: Critical Thinking

3. An infeasible solution results when Solver cannot determine the combination of decision variables that satisfies all of the constraints. What are three ways you can troubleshoot this situation?

ANS: 1. Correct any data-entry errors in the Solver Parameters dialog box. Compare the constraints listed in the Solver Parameters dialog box with the constraints table you created in your worksheet. Verify that your cell references are correct and that you selected the correct operators.

2. Review the Feasibility Report for constraints with a violated status. Try loosening these constraint values by changing the slack value for each suspected problem. Loosening a criterion with a greater than or equal to operator decreases values. Loosening a criterion with a less than or equal to operator increases values. Loosening the criteria values expands the solution area.

3. Review the constraints to see if a policy constraint is the cause of the infeasibility. Policy constraints with minimum or maximum limits are a good place to look for causes of infeasibility.

PTS: 1

REF: 575 | 576

TOP: Critical Thinking

Chapter 10: Troubleshooting Workbooks and Automating Excel Applications TRUE/FALSE 1. An Excel application can be a DSS, a tool that helps managers or other users make decisions or solve problems.

ANS: T

PTS: 1

REF: 605

2. To achieve a gross profit, an item’s COGS should always be less than the sales price.

ANS: T

PTS: 1

REF: 607

3. The Data Validation Rule called Text length restricts formulas to a specified length.

ANS: F

PTS: 1

REF: 612

4. Displaying an input message when a user clicks a cell is a helpful way to let the user know what type of data to enter in the cell.

ANS: T

PTS: 1

REF: 615

5. The data validation tool restricts data being entered and data already stored in the worksheet.

ANS: F

PTS: 1

REF: 617

6. You click the Circle Invalid Data command on the Data Validation menu to find and mark cells containing data that does not meet validation criteria.

ANS: T

PTS: 1

REF: 617

7. For optimal password security, Microsoft recommends that you always assign a password to open and view a file.

ANS: T

PTS: 1

REF: 620

8. By default, every cell in a worksheet is unlocked.

ANS: F

PTS: 1

REF: 621

9. Hiding a row or column affects the data and any formulas that reference a hidden cell.

ANS: F

PTS: 1

REF: 623

10. Documentation can be descriptions of the system, checklists, flowcharts, and other hard-copy documents, as well as notations built into the system itself.

ANS: T

PTS: 1

REF: 626

11. Cell formula printouts also serve as a backup for workbooks.

ANS: T

PTS: 1

REF: 626

12. If you double-click the cell containing a formula with an error, Excel displays color-coded borders around related cells.

ANS: F

PTS: 1

REF: 632

13. The formula auditing tool called Evaluate Formula displays the different parts of a nested formula, evaluated in the order the formula is written.

ANS: F

PTS: 1

REF: 633

14. Excel assumes that any text in a formula is either a command word, such as a function name, or a reference to a cell, such as a named range.

ANS: T

PTS: 1

REF: 634

15. EPS is calculated by dividing the number of shares outstanding by net income.

ANS: F

PTS: 1

REF: 638

16. When you trace dependent cells, you trace the components of a formula that references other cells.

ANS: T

PTS: 1

REF: 639

17. Most of Excel’s error-checking rules are disabled by default.

ANS: F

PTS: 1

REF: 643

18. You can create an Excel macro by using the macro recorder to record your keystrokes and mouse actions as you perform them.

ANS: T

PTS: 1

REF: 648

19. A virus can harm your computer by changing your program settings, for example, or deleting files on your hard disk.

ANS: T

PTS: 1

REF: 653

20. A trusted location is usually any computer that is used only by one person.

ANS: F

PTS: 1

REF: 653

MODIFIED TRUE/FALSE 1. A(n) scenario support system is a type of information system that helps managers model business situations, especially through what-if analysis. _________________________ ANS: F Decision Support System (DSS) DSS (Decision Support System) Decision Support System DSS

PTS: 1

REF: 605

2. Excel applications are workbooks that you design so that others can use them in a relatively error-free manner. _________________________ ANS: T

PTS: 1

REF: 606

3. To help prevent a user from entering data that doesn’t satisfy the validation rule for a cell, you can display a(n) output message that identifies the kind of data the user should enter. _________________________ ANS: F, input PTS: 1

REF: 610

4. An error alarm is a message box that displays a message of varying severity. _________________________ ANS: F, alert PTS: 1

REF: 610

5. By default, input messages appear as comments next to the cell when the cell is selected. _________________________ ANS: T

PTS: 1

REF: 615

6. You should protect your entire workbook file with a(n) passkey for optimal security. _________________________ ANS: F, password PTS: 1

REF: 620

7. If you do not want others to view data in a worksheet but you want to retain the information, you can freeze a row or a column. _________________________ ANS: F, hide PTS: 1

REF: 623

8. Cells with comments have blue indicator triangles in the upper-right corner. _________________________ ANS: F, red PTS: 1

REF: 628

9. When #DIV/0! appears in a cell, you know that a formula is trying to divide by the value 10, which cannot be done. _________________________ ANS: F 0 zero

PTS: 1

REF: 638

10. The formula auditing tool called Internal References allows you to step through a set of cells that directly or indirectly refer to themselves. _________________________ ANS: T, Circular PTS: 1

REF: 633

11. A(n) dependent cell uses the value in the active cell in its formula. _________________________ ANS: T

PTS: 1

REF: 634

12. An Error Alert button names the type of error, and lets you access the Excel Help tool or edit the formula in the Format Bar. _________________________ ANS: F, Formula PTS: 1

REF: 634

13. False positives are items that are incorrect but are not flagged as such. _________________________ ANS: F, negatives PTS: 1

REF: 642

14. Macro viruses are viruses that are written and stored as macros attached to documents created in Office programs such as Excel, and run when you open an infected document. _________________________ ANS: T

PTS: 1

REF: 653

15. A(n) digital certificate should contain a digital signature, which confirms that the signer created the macro and that the macro has not been changed since its digital certificate was created. _________________________ ANS: T

PTS: 1

REF: 653

MULTIPLE CHOICE 1. To help minimize the errors users make as they enter data into worksheets, Excel provides a ____ tool. a. data validation c. data message b. data d. data-entry ANS: A

PTS: 1

REF: 610

2. To help prevent a user from entering data that doesn’t satisfy the validation rule for a cell, you can display a(n) ____ message that identifies the kind of data the user should enter. a. input c. data b. error d. data table ANS: A

PTS: 1

REF: 610

3. A(n) ____ rule can specify a range of acceptable values, such as those between 1 and 100 or those in a particular list of values. a. validation c. data b. error d. input ANS: A

PTS: 1

REF: 610

4. To prevent users from storing inaccurate data in a worksheet, you can include a(n) ____. a. error alert message c. file protection message b. worksheet protection message d. error detection warning ANS: A

PTS: 1

REF: 610

5. You access the data validation tool by clicking the DATA tab on the ribbon, and then clicking Data Validation in the ____ group to open the Data Validation dialog box. a. Data Tools c. View b. Data d. Formulas ANS: A

PTS: 1

REF: 611

6. Which of the following is NOT one of the three tabs in the Data Validation dialog box? a. Settings c. Error Alert b. Input Message d. Audit ANS: D

PTS: 1

REF: 611

7. The data validation rule called ____ value is the default setting for cells. a. Any c. Decimal b. Whole number d. List ANS: A

PTS: 1

REF: 611

8. You can use ____ even for cells that allow any data values to be entered in them. a. input messages c. help messages b. data messages d. cell messages ANS: A

PTS: 1

REF: 614

9. By default, input messages appear as comments ____ when the cell is selected. a. next to the cell c. on top of the cell b. under the cell d. within the cell ANS: A

PTS: 1

REF: 615

10. In addition to a helpful message that appears before users enter data, you can also display a(n) ____ message in a message box if users enter data that violates the cell’s validation rule. a. error alert c. caution b. alarm d. stop and look ANS: A

PTS: 1

REF: 615

11. Which of the following is NOT one of the three available error alert styles for a cell? a. Stop c. Information b. Warning d. Help ANS: D

PTS: 1

REF: 616

12. You use the Error Alert tab in the ____ dialog box to specify error alert styles and messages. a. Data Validation c. Data b. Formulas d. View ANS: A

PTS: 1

REF: 616

13. To reinforce the ____ rule, you can set up an error alert message for a cell. a. validation c. data b. step-through d. input ANS: A

PTS: 1

REF: 615

14. If you set up the validation rule only, all future data entry in that cell is restricted to the values ____. a. you have specified c. new users enter b. Excel enters as a default d. none of the above ANS: A

PTS: 1

REF: 617

15. Using only a validation rule without ____ can frustrate users of the worksheet. a. an input message c. either a or b b. an error alert message d. neither a nor b ANS: C

PTS: 1

REF: 617

16. To prevent data errors, you can ____ a workbook, which lets you control the changes users can make to it. a. protect c. guard b. save d. secure ANS: A

PTS: 1

REF: 619

17. You can protect a workbook by ____. a. locking cells so that users cannot change their contents b. hiding cells so that users cannot see them c. preventing a worksheet from being deleted from a workbook d. all of the above ANS: D

PTS: 1

REF: 619

18. You can use up to three levels of security and protection to control who can access and change your Excel data. These levels do NOT include ____. a. worksheet protection c. file protection b. workbook protection d. data table protection ANS: D

PTS: 1

REF: 620

19. For optimal password security, Microsoft recommends that you always assign a password to open and view a file. Users who also have permission to modify data should then enter ____. a. a second password c. no password b. the same password d. a Microsoft-assigned password ANS: A

PTS: 1

REF: 620

20. To allow users to edit some cells while preventing them from changing others, you ____ the cells you want users to access and then protect the worksheet. a. unlock c. protect

b. lock ANS: A

d. unprotect PTS: 1

REF:

621

21. If you protect a worksheet, users cannot change ____. a. any data c. both a and b b. any formulas d. neither a nor b ANS: C

PTS: 1

REF: 620

22. If you want to prohibit workbook users from adding, renaming, or deleting worksheets in a workbook, you can enable ____ protection. a. workbook c. data table b. worksheet d. user data ANS: A

PTS: 1

REF: 620

23. When you protect a workbook, you can secure the ____. a. workbook structure c. both a and b b. workbook windows d. neither a nor b ANS: C

PTS: 1

REF: 620

24. When a workbook’s structure is protected, users cannot ____ worksheets. a. insert c. rename b. delete d. all of the above ANS: D

PTS: 1

REF: 620

25. Protect and uprotect a workbook using the ____ dialog box. a. Protect Structure and Windows c. Protect Sheet b. Data Validation d. Format Cells ANS: A

PTS: 1

REF: 625

26. To unprotect a workbook, on the REVIEW tab on the ribbon, click the Protect Workbook button in the ____ group, and then deselect the necessary check boxes. a. Changes c. What-if b. Format d. Data ANS: A

PTS: 1

REF: 625

27. Backups of important Excel files should be stored or maintained _____. a. on the computer’s desktop c. on a server that is backed up regularly b. anywhere on the computer’s hard disk d. only as a hard copy ANS: C

PTS: 1

REF: 626-627

28. A ____ worksheet usually contains the workbook title, creation date, name of the person who created the workbook, and any other information that describes the purpose and content of the workbook. a. documentation c. data table b. detailed d. protected ANS: A

PTS: 1

REF: 626

29. The following types of documentation are helpful in an Excel application: ____. a. step-by-step instructions for entering data and performing calculations

b. notes or comments that define terms, explain calculations, and describe assumptions c. notes or comments that identify the source of data, such as another workbook, a database, or a Web page d. all of the above ANS: D

PTS: 1

REF: 626

30. When printing worksheet formulas, especially for the purposes of documentation, it is very helpful to set the print options to ____. a. print the row and column headings c. both a and b b. portrait orientation d. neither a nor b ANS: A

PTS: 1

REF: 627

31. It is best to include a workbook’s filename, worksheet name, and date on cell formula printouts that are used for documentation. The worksheet’s ____ is a handy location for this kind of information. a. header or footer c. range name b. filename d. first column ANS: A

PTS: 1

REF: 627

32. To read a comment, you point to a cell with ____ to display its comment. a. an indicator c. a border b. an error message d. shading ANS: A

PTS: 1

REF: 628

33. The error message #NAME? indicates ____. a. unrecognized text in a formula b. not enough column space for text ANS: A

PTS: 1

c. a name is required d. an unrecognized name in a cell

REF: 632

34. You can immediately fix the #### error by ____. a. widening the column c. changing the formula b. increasing the height of a row d. running spellcheck ANS: A

PTS: 1

REF: 632

35. If a cell contains an error-indicator triangle in its upper-left corner, you can click the cell to display the ____ button. a. Error Alert c. Error Wizard b. Error Help d. Error Warning ANS: A

PTS: 1

REF: 632

36. You can press the ____ keyboard combination to display the formula in the cell. a. Ctrl+` c. Ctrl+Tab b. Ctrl+F8 d. Ctrl+& ANS: A

PTS: 1

37. Common formula errors include ____. a. using mixed cell references in a formula b. multiplying a cell’s value by 0 ANS: D

PTS: 1

REF: 627

c. using a defined name in a table d. problems with order of precedence REF: 633

38. The Excel formula auditing tools help you troubleshoot formula problems by letting you review the ____ of a formula. a. structure c. both a and b b. behavior d. neither a nor b ANS: C

PTS: 1

REF: 633

39. The formula auditing tool called ____ toggles between displaying formulas or results of formulas in the worksheet. a. Show Formulas c. Trace Dependents b. View Formulas d. Watch Window ANS: A

PTS: 1

REF: 633

40. When text is included inside quotation marks, it is treated as a ____. a. text string c. value b. formula d. function ANS: A

PTS: 1

REF: 634

41. A #VALUE! error in a worksheet cell indicates ____. a. the wrong argument type or operand c. a circular argument b. an incorrect formula d. the cell needs to be widened ANS: A

PTS: 1

REF: 635

42. ____ tracer arrows point to another cell that provides data for the active cell. a. Blue c. Green b. Yellow d. Orange ANS: A

PTS: 1

REF: 635

43. ____ tracer arrows indicate that a precedent cell contains an error. a. Blue c. Green b. Red d. Orange ANS: B

PTS: 1

REF: 636

44. The ____ on the blue tracer arrow indicate that certain cells are precedent cells for other cells. a. solid circles c. solid triangles b. open circles d. open triangles ANS: A

PTS: 1

REF: 636

45. An Error Alert button appears on tracer arrows and ____. a. names the type of error b. lets you access the Excel Help tool c. lets you edit the formula in the Formula Bar d. all of the above ANS: D

PTS: 1

REF: 636

46. You can use the ____ button in the Formula Auditing group to identify parts of a formula that reference other cells. a. Trace Precedents c. Error Checking

b. Trace Dependents ANS: A

d. Show Formulas PTS: 1

REF: 638

47. ____ data is input data for which you already know the results. a. Test c. Scenario b. Complex d. Break-even ANS: A

PTS: 1

REF: 644

48. By entering a variety of input data and comparing the results to output you have already calculated outside the worksheet, you can use ____ analysis to indirectly verify the validity of your worksheet. a. what-if c. audit b. COGS d. data linking ANS: A

PTS: 1

REF: 644

49. One way to make a workbook easier to use is to automate repetitive or difficult tasks. To do so, you can use ____. a. a macro c. a wizard b. animation d. a validation scheme ANS: A

PTS: 1

REF: 647

50. You can create a macro that can ____. a. display the formulas in a workbook b. print the workbook in landscape orientation c. show row and column headings on the printout d. all of the above ANS: D

PTS: 1

REF: 647-648

51. The steps you want to automate are stored as ____ commands in the macro, and are performed in sequence when you run the macro. a. VBA c. HTTP b. HTML d. JAVA ANS: A

PTS: 1

REF: 648

52. You can create an Excel macro by ____. a. using the macro recorder to record your keystrokes and mouse actions as you perform them b. writing macros from scratch by entering VBA commands in the Visual Basic Editor c. either a or b d. neither a nor b ANS: C

PTS: 1

REF: 648

53. To work with advanced macro features, the ____ tab must be displayed on the ribbon. a. DEVELOPER c. VBA b. FORMULAS d. PROGRAMMING ANS: A

PTS: 1

REF: 648

54. ____ is a program that is attached to a file and runs when you open the file. It can harm your computer by changing program settings or deleting hard disk files.

a. A virus b. Adware ANS: A

c. Spyware d. A Trojan horse PTS: 1

REF: 653

55. Macro ____ consist(s) of viruses that are written and stored as macros attached to documents created in Office programs such as Excel, and run when you open an infected document. a. viruses c. adware b. spyware d. shareware ANS: A

PTS: 1

REF: 653

56. A ____ for a macro should contain a digital signature, which confirms that the signer created the macro and that the macro has not been changed since its certificate was created. a. digital certificate c. high-performance certificate b. Web certificate d. security certificate ANS: A

PTS: 1

REF: 653

57. When you choose ____, Excel disables all macros and security alerts about macros in documents that are not stored in a trusted location. a. Disable all macros without notification b. Disable all macros with notification c. Disable all macros except digitally signed macros d. Enable all macros ANS: A

PTS: 1

REF: 653-654

58. When you choose ____, Excel disables all macros but retains security alerts about macros in documents that are not stored in a trusted location. a. Disable all macros without notification b. Disable all macros with notification c. Disable all macros except digitally signed macros d. Enable all macros ANS: B

PTS: 1

REF: 653-654

59. When you choose ____, Excel disables all macros except macros that are digitally signed and are from an already trusted publisher. a. Disable all macros without notification b. Disable all macros with notification c. Disable all macros except digitally signed macros d. Enable all macros ANS: C

PTS: 1

REF: 653-654

60. When you choose ____, Excel allows all macros to run. a. Disable all macros without notification b. Disable all macros with notification c. Disable all macros except digitally signed macros d. Enable all macros ANS: D

PTS: 1

REF: 653-654

61. You control how Excel handles macros that are not stored in trusted locations by choosing Macro Settings in the ____.

a. Trust Center b. Security Center ANS: A

c. Control Panel d. Help Center PTS: 1

REF: 653

62. New or casual Excel users usually prefer to create a macro using the ____, an excellent choice for automating tasks that you perform using the keyboard or mouse. a. macro recorder c. macro animator b. macro simulator d. macro duplicator ANS: A

PTS: 1

REF: 648

63. After you record a macro, you can run it in two ways: press the shortcut key assigned to the macro, or use the ____. a. Macro dialog box c. Macro recent file pane b. Macro Wizard d. Macro status bar ANS: A

PTS: 1

REF: 654

64. One way to correct a macro is to run the macro one step at a time by opening the Macro dialog box, clicking the macro you want to delete, and then clicking the ____ button. After you identify the step causing the problems, record the macro again. a. Step Into c. Step Through b. Step Out d. Step by Step ANS: A

PTS: 1

REF: 659

65. When Excel runs a macro, it performs the actions specified by the macro’s ____. a. VBA code c. Java code b. HTML code d. FTP code ANS: A

PTS: 1

REF: 660

66. To view the code of a macro, you open the ____, a separate program that works with Excel and other Office applications and lets you work with VBA code. a. Visual Basic Editor c. Visual Basic Documenter b. Visual Basic Word d. Visual Basic Programmer ANS: A

PTS: 1

REF: 660

67. A macro is set to display the formulas in the active window. To disable this setting, the code _____ can be used. a. ActiveWindow.DisplayFormulas = True c. ActiveWindow.DisplayFormulas = False b. ActiveWindow.DisplayFormulas = No d. ActiveWindow.DisplayFormulas = Yes ANS: C

PTS: 1

REF: 661

68. In VBA, comments appear in green and start with a(n) ____. a. apostrophe (') c. question mark (?) b. forward slash (/) d. exclamation point (!)

ANS: A

PTS: 1

REF: 661

69. VBA interprets ____ as “ignore these lines of text” because these lines do not contain commands that Excel should perform. a. apostrophes c. exclamation points

b. question marks ANS: A

d. forward slashes PTS: 1

REF: 661

70. The words End Sub indicate the end of the ____. a. macro c. analysis b. formula d. command ANS: A

PTS: 1

REF: 661

Case-Based Critical Thinking Questions Case 10-1

Elianna has put together the Projected Income worksheet above. She is finalizing the worksheet for a presentation to her company’s board of directors. When needed, please refer to the figure above as you answer the questions below.

71. Under Assumptions, Elianna accidentally clears the entry for COGS, 300%, and she wants to recreate it. She only wants data entered as a percentage between 0 and 1. She also knows that several top managers throughout the company will be entering the data. What could Elianna do to guide them? a. Set up a new macro. c. Set COGS to 50. b. Display formulas. d. Set up a validation rule.

ANS: D

PTS: 1

REF: 617

TOP: Critical Thinking

72. Elianna opens the Data Validation dialog box. What options will she be able to change using this dialog box? a. VBA settings c. cell ranges b. input messages d. all of the above ANS: B

PTS: 1

REF: 611

TOP: Critical Thinking

73. Elianna now wants to check for errors. A first step would be to look at those cells with a ____. a. green error indicator triangle in the upper-left corner b. red error indicator square in the lower-left corner c. red error indicator triangle in the upper-left corner

d. green error indicator triangle in the lower-left corner ANS: A

PTS: 1

REF: 632

TOP: Critical Thinking

74. Elianna clicks on the cell containing the #NAME? error. To find out more about this error, what type of dialog box should she open? a. Error Checking c. Data Entry b. Formulas d. Data Validation ANS: A

PTS: 1

REF: 633

TOP: Critical Thinking

75. What entry should Elianna have in the cell returning the #NAME? error? a. =E12-E13 c. =SUM(E13:E12) b. ="E12-E13" d. =E12*E13 ANS: A

PTS: 1

REF: 607

TOP: Critical Thinking

76. Elianna has included a Print Formulas button on the worksheet. This button links to ____. a. a macro c. a formula in a cell b. another worksheet d. another file ANS: A

PTS: 1

REF: 657

TOP: Critical Thinking

77. Elianna is ready to edit one of the macros in the worksheet. To do this, she opens the ____. a. Visual Basic Editor c. Macro Editor Dialog Box b. Macro Editor d. Text Editor ANS: A

PTS: 1

REF: 660

TOP: Critical Thinking

Case-Based Critical Thinking Questions Case 10-2 As part of his new job, Seth has created a Projected Income worksheet for his company. He wants to prohibit users from changing the formulas in the Projected Income worksheet, while allowing them to change values such as unit sales and unit price. Seth has also imported a worksheet named Sales History, which contains sales information for sunglasses for the last year.

78. Seth wants to understand Excel’s default settings. He realizes that the workbook’s default setting includes which of the following? a. all cells are locked, worksheet is protected b. all cells are locked, worksheet is unprotected c. cells are not locked, worksheet is protected d. cells are not locked, worksheet is not protected ANS: B

PTS: 1

REF: 621

TOP: Critical Thinking

79. In order for Seth to allow others to enter data into some of the cells in the worksheet but not others, he should ____. a. unlock the cells he wants users to access and then protect the worksheet b. protect the worksheet and then unlock the cells he wants users to access c. name ranges and click “allow data entry” in the Data Validation dialog box d. none of the above ANS: A

PTS: 1

REF: 621

TOP: Critical Thinking

80. Seth is thinking about hiding all cells with formulas in his Projected Income worksheet. He thinks this will definitely protect them from being changed. You would advise him not to hide all of these cells because ____. a. depending on whether the worksheet is protected, users will still be able to unhide and change the cell contents b. it is considered poor practice to hide cells with formulas in a worksheet c. both a and b d. neither a nor b ANS: A

PTS: 1

REF: 624

TOP: Critical Thinking

COMPLETION 1. A(n) ____________________ is a system that is used to collect, organize, and process data, and to communicate and distribute the results in a coordinated manner.

ANS: information system PTS: 1

REF: 605

2. In general, an Excel application supports your decisions by allowing you to perform what-if ____________________ and other types of data analysis to model business situations.

ANS: analysis PTS: 1

REF: 605

3. A(n) ____________________ can specify the type of data that is appropriate to be entered into a cell, such as whole numbers, decimal numbers, dates, time, or text.

ANS: validation rule PTS: 1

REF: 610

4. EPS is the amount of ____________________ that has been earned by each share on average, which measures the earning power of the product.

ANS: profit PTS: 1

REF: 607

5. The default setting for error alert messages is ____________________, which tells the user the entry is incorrect and must be canceled or corrected before continuing.

ANS: Stop PTS: 1

REF: 616

6. ____________________ protection protects the contents of a worksheet to prevent anyone from changing, moving, or deleting its data.

ANS: Worksheet

PTS: 1

REF: 620

7. ____________________ refers to all the records that describe how and why a system has been developed, and how it should be used.

ANS: Documentation PTS: 1

REF: 626

8. A(n) ____________________ is a note that you attach to a cell, separate from other cell content.

ANS: comment PTS: 1

REF: 628

9. The formula auditing tool called ____________________ displays tracer arrows that indicate which cells affect the value of the currently selected cell.

ANS: Trace Precedents PTS: 1

REF: 633

10. A(n) ____________________ cell contains a value that is used in a formula in the active cell.

ANS: precedent PTS: 1

REF: 634

11. A(n) ____________________ cell contains a formula that refers to other cells.

ANS: dependent PTS: 1

REF: 639

12. ____________________ are items that are flagged as incorrect but are, in fact, correct.

ANS: False positives PTS: 1

REF: 642

13. ____________________ is input data for which you already know the results.

ANS: Test data PTS: 1

REF: 644

14. A(n) ____________________ is a series of commands that you store and then run when you need to perform a task.

ANS: macro PTS: 1

REF: 647

15. If you know a document came from a trustworthy source and contains only safe macros, you can store it in a trusted location, allowing any macros in it to run without being checked by security settings in the ____________________.

ANS: Trust Center PTS: 1

REF: 653

16. You can obtain a(n) ____________________ from a commercial certification authority or, in some cases, from your system administrator.

ANS: digital certificate PTS: 1

REF: 653

17. To save and run a macro in an Excel workbook, it must be saved as a macro-enabled workbook, giving it a(n) ____________________ extension.

ANS: .xlsm xlsm PTS: 1

REF: 654

18. You can assign a macro to a(n) ____________________, which is a combination of keystrokes you press to run the macro.

ANS: shortcut key shortcut key sequence PTS: 1

REF: 649

19. All Excel ____________________, including the ones you record, consist of VBA code.

ANS: macros PTS: 1

REF: 660

20. VBA calls macros sub procedures, and starts each sub procedure name with Sub, followed by its descriptive name and a pair of ____________________.

ANS: parentheses parens PTS: 1 MATCHING

REF: 661

Identify the letter of the choice that best matches the tools listed above. a. #1 g. #7 b. #2 h. #8 c. #3 i. #9 d. #4 j. #10 e. #5 k. #11 f. #6 l. #12 1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

Watch Window Trace Precedents Remove Arrows menu Error Checking Trace Error Error Checking menu Trace Dependents Remove Arrows Circular References Evaluate Formula Show Formulas formula auditing tools

1.

2.

3.

4.

5.

ANS: K ANS: A ANS: D ANS: F ANS: H

PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1

REF: REF: REF: REF: REF:

633 633 633 633 633

6.

7.

8.

9.

10.

11.

12.

ANS: G ANS: B ANS: C ANS: I ANS: J ANS: E ANS: L

PTS: PTS: PTS: PTS: PTS: PTS: PTS:

1 1 1 1 1 1 1

REF: REF: REF: REF: REF: REF: REF:

633 633 633 633 633 633 633

ESSAY 1. You can use up to three levels of security and protection to control who can access and change your Excel data. Please describe two of the three levels and how you access each of them.

ANS: • Worksheet protection: Protect the contents of a worksheet to prevent anyone from changing, moving, or deleting the data. Be certain the cells you want to protect are locked, unlock the cells you want users to be able to access, and then use the Protect Sheet dialog box to select the actions users can still take after worksheet protection is enabled. Specifying a password that users must enter before unprotecting the worksheet is optional. This level of password protection is not designed to secure confidential information in Excel, but only to prevent others from viewing or changing your data. For workbooks that are shared on networks, you can set protection options for individual ranges of cells, users, or groups of users by using the Allow Users to Edit Ranges features.

• To access worksheet protection: On the REVIEW tab on the ribbon, click Protect Sheet in the Changes group to open the Protect Sheet dialog box.

• Workbook protection: Protect a workbook to prevent anyone from inserting, deleting, or renaming a worksheet. Use the Protect Workbook dialog box to protect the workbook structure. As with worksheet protection, specifying a password that users enter before they remove protection from a workbook is optional.

• To access workbook protection: On the REVIEW tab on the ribbon, click Protect Workbook in the Changes group to open the Protect Structure and Windows dialog box.

• File protection: Protect an entire workbook file with a password for optimal security. Securing a file means that only authorized users can open a workbook and use its data. While the Save As dialog box is open, you can protect a file by clicking the Tools button, clicking General Options, and then using the General Options dialog box to specify a password that users must enter to open and view a workbook file. This password helps you prevent unauthorized users from viewing your workbook.

You can also use this dialog box to specify another password users must enter to modify the file. This password allows you to prevent unauthorized users from editing your workbook.

• To access file protection: You can protect a file in the Save As dialog box by clicking the Tools button, clicking General Options, and then using the General Options dialog box to specify a password that users must enter to open and view a workbook file.

PTS: 1

REF: 620-625

TOP: Critical Thinking

2. The textbook suggested that before using the formula auditing tools, you take the following three steps: fix obvious errors; use the Error Alert button; and examine the formula. Please briefly describe each of these steps.

ANS: • Fix obvious errors—If an error message indicates an obvious error, such as ####, you can immediately fix the problem, such as by widening the column to display the complete value in the cell.

• Use the Error Alert button—If a cell contains an error-indicator triangle in its upper-left corner, you can click the cell to display the Error Alert button. Click this button to see a list of possible problems and solutions.

• Examine the formula—Click the cell containing an error message, and then examine the formula in the Formula Bar or select the Show Formulas button on the FORMULAS tab to display all cell formulas on the worksheet. (You can also press the keyboard combination Ctrl+` to display the formula in the cell.) If you double-click the cell, Excel displays color-coded borders around related cells. Common formula errors include using incorrect cell references, problems with order of precedence, and using a value as text.

PTS: 1

REF: 632 | 633

TOP: Critical Thinking

3. You control how Excel handles macros that are not stored in trusted locations by choosing one of four macro settings in its Trust Center. Please list and describe each of the four settings.

ANS: • Disable all macros without notification—Excel disables all macros and security alerts about macros in documents that are not stored in a trusted location.

• Disable all macros with notification—Excel disables all macros but retains security alerts about macros in documents that are not stored in a trusted location. This option allows you to enable macros on a case-by-case basis. This is the default setting for macros.

• Disable all macros except digitally signed macros—This setting is the same as the Disable all macros with notification setting except that macros that are digitally signed can run if you have already identified the source as a trusted publisher.

• Enable all macros—Excel allows all macros to run. Because this setting makes your computer vulnerable to potentially malicious code, it is not recommended.

PTS: 1

REF: 653 | 654

TOP: Critical Thinking