Question 1

In a MATCH function, the default value for match_type = 0.

Accepted Answer

A) True 
 B)False

Question 2

A positive NPV means that the investment will provide added value because the projected return exceeds the .

Accepted Answer

A)  modified internal rate of return 
B)  discount rate 
C)  accounting rate of return 
D)  adjusted present value 
A)  modified internal rate of return 
B)  discount rate 
C)  accounting rate of return 
D)  adjusted present value

Question 3

Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order?

Accepted Answer

A)  HLOOKUP(lookup_value, table_array, row_index_num) 
B)  MATCH(lookup_value, lookup_array, match_type) 
C)  INDEX(array, row_num, col_num) 
D)  VLOOKUP(lookup_value, table_array, col_index_num) 
A)  HLOOKUP(lookup_value, table_array, row_index_num) 
B)  MATCH(lookup_value, lookup_array, match_type) 
C)  INDEX(array, row_num, col_num) 
D)  VLOOKUP(lookup_value, table_array, col_index_num)

Question 4

In a MATCH function, if match_type = 1, then the function finds the smallest value that is greater than or equal to lookup_value.

Accepted Answer

A) True 
 B)False

Question 5

Use the data given below to answer the following question(s)
Below is the spreadsheet for demand prediction of a company that sells chocolates. $$\begin{array} { | l | l | l | l | } 
\hline & \text { A } & \text { B } & \text { C } \
\hline 1 & \text { Demand Prediction } & & \
& \text { Models } & & \
\hline 2 & & & \
\hline 3 & \text { Linear Model } & & \
\hline 4 & \text { A } & 10,000 & \
\hline 5 & \text { B } & 10 & \
\hline 6 & & & \
\hline 7 & \text { Price } & \text { Demand } & \
\hline 8 & \$ 50 & 9,500 & \
\hline 9 & \$ 55 & 9,450 & \
\hline 10 & \$ 45 & 9,550 & \
\hline 11 & & & \
\hline
\end{array}$$
-If a dollar sign is used before the column label B4 ($B4), how will the formula at B10 be represented in C11 using absolute addressing?

Accepted Answer

A)  $B5-C6*B11 
B)  $C5-C6*A10 
C)  $B4-C5*B11 
D)  $A5-C6*B11 
A)  $B5-C6*B11 
B)  $C5-C6*A10 
C)  $B4-C5*B11 
D)  $A5-C6*B11

Question 6

The Excel function of is used to find the largest value in a range of cells.

Accepted Answer

A)  SUM(range) 
B)  COUNT(range) 
C)  MAX(range) 
D)  COUNTIF(range, criteria) 
A)  SUM(range) 
B)  COUNT(range) 
C)  MAX(range) 
D)  COUNTIF(range, criteria)

Question 7

To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12?

Accepted Answer

A)  =COUNTIF(F4:F10,"<25") 
B)  =COUNT(F4:F10,25) 
C)  =AVERAGE(F4:F10,"<25") 
D)  =COUNTIF(F4:F10,F5) 
A)  =COUNTIF(F4:F10,"<25") 
B)  =COUNT(F4:F10,25) 
C)  =AVERAGE(F4:F10,"<25") 
D)  =COUNTIF(F4:F10,F5)

Question 8

Using a $ sign before a column label .

Accepted Answer

A)  keeps the reference to both the row and column fixed 
B)  keeps the reference to the row fixed, but allows the column reference to change 
C)  keeps the reference to column fixed, but allows the row reference to change 
D)  allows both the row and column references to change 
A)  keeps the reference to both the row and column fixed 
B)  keeps the reference to the row fixed, but allows the column reference to change 
C)  keeps the reference to column fixed, but allows the row reference to change 
D)  allows both the row and column references to change

Question 9

In a MATCH function, if the match_type = 0, then .

Accepted Answer

A)  the function finds the largest value that is less than or equal to lookup_value 
B)  the function finds the smallest value that is greater than or equal to lookup_value 
C)  MATCH finds the first value that is exactly equal to lookup_value 
D)  the values in the lookup_array must be in a particular order 
A)  the function finds the largest value that is less than or equal to lookup_value 
B)  the function finds the smallest value that is greater than or equal to lookup_value 
C)  MATCH finds the first value that is exactly equal to lookup_value 
D)  the values in the lookup_array must be in a particular order

Question 10

Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not?

Accepted Answer

A)  TO(value if true, value if false) 
B)  AND(condition 1, condition 2…) 
C)  OR(condition 1, condition 2…) 
D)  IF(condition, value if true, value if false) 
A)  TO(value if true, value if false) 
B)  AND(condition 1, condition 2…) 
C)  OR(condition 1, condition 2…) 
D)  IF(condition, value if true, value if false)

Question 11

Which of the following is a difference between relative addressing and absolute addressing when using cell formulas in Excel?

Accepted Answer

A)  A relative address uses a dollar sign before either the row or column label; an absolute address uses the ampersand symbol before either the row or column label. 
B)  A relative address uses a dollar sign before either the row or column label; an absolute address uses just the row and column label in the cell reference. 
C)  A relative address uses just the row and column label in the cell reference; an absolute address uses a dollar sign before either the row or column label. 
D)  A relative address uses only the column label in the cell reference; an absolute address uses the row. 
A)  A relative address uses a dollar sign before either the row or column label; an absolute address uses the ampersand symbol before either the row or column label. 
B)  A relative address uses a dollar sign before either the row or column label; an absolute address uses just the row and column label in the cell reference. 
C)  A relative address uses just the row and column label in the cell reference; an absolute address uses a dollar sign before either the row or column label. 
D)  A relative address uses only the column label in the cell reference; an absolute address uses the row.

Question 12

Give the logical function for the following: If cell B7 equals 12, check contents of cell B10. If cell B10 is 10, then the value of the function in the string is YES; if not, it is a blank space. If cell B7 does not equal 12, then the value of the function is 7.

Accepted Answer

A)  =IF(B7=12,(AND(B10=10, "")(YES)),7) 
B)  =IF(B10=10,(OR(B7=12,"")"YES")7) 
C)  =IF(B7=12,(IF(B10=10,"YES", "")),7) 
D)  =IF(B7=12,(AND(B10=10,"YES","")(B10="NO"),7) 
A)  =IF(B7=12,(AND(B10=10, "")(YES)),7) 
B)  =IF(B10=10,(OR(B7=12,"")"YES")7) 
C)  =IF(B7=12,(IF(B10=10,"YES", "")),7) 
D)  =IF(B7=12,(AND(B10=10,"YES","")(B10="NO"),7)

Question 13

is a logical function that returns one value if the condition is true and another if the condition is false.

Accepted Answer

A)  OR(condition 1, condition 2…) 
B)  AND(condition 1, condition 2…) 
C)  TO(value if true, value if false) 
D)  IF(condition, value if true, value if false) 
A)  OR(condition 1, condition 2…) 
B)  AND(condition 1, condition 2…) 
C)  TO(value if true, value if false) 
D)  IF(condition, value if true, value if false)

Question 14

The reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time.

Accepted Answer

A)  modified internal rate of return 
B)  payback period 
C)  accounting rate of return 
D)  discount rate 
A)  modified internal rate of return 
B)  payback period 
C)  accounting rate of return 
D)  discount rate

Question 15

Using a $ sign before both the row and column labels keeps the reference to that cell fixed no matter where the formula is copied.

Accepted Answer

A) True 
 B)False

Question 16

measures the worth of a stream of cash flows, taking into account the time value of money.

Accepted Answer

A)  Accounting rate of return 
B)  Net present value 
C)  Internal rate of return 
D)  Adjusted present value 
A)  Accounting rate of return 
B)  Net present value 
C)  Internal rate of return 
D)  Adjusted present value

Question 17

The easiest way to locate a particular function is to select a cell and click on the Insert function button represented by on the Excel ribbon.

Accepted Answer

A)  fx 
B)  Σ 
C)  $ 
D)  % 
A)  fx 
B)  Σ 
C)  $ 
D)  %

Question 18

Use the following scenario to answer the following question(s)
The Blue Sunset Band is planning to record a new album. A major decision to be made is if the band can record the album on their own, or if they should hire a studio to record it with. The
fixed cost for recording at the studio is $100,000 plus the manufacturing cost per CD, which is at
$250. If they record the album in-house, the cost per CD is $350. They plan to produce 3000 copies of the album regardless of the place of recording. The band plans to record with the cheaper option. Below is the spreadsheet of the Recording Decision. $\begin{array}{|l|l|l|}
\hline & \text { A } & \text { B } \
\hline & \begin{array}{l}
\text { The Blue Sunset Band } \
\text { Album Recording }
\end{array} & \
1 & \begin{array}{l}
\text { Decision } \
\text { Den }
\end{array} & \\hline 2 & & \
\hline 3 & \text { Data } & \
\hline 4 & & \\hline 5 & \text { Recording at Studio } & \
\hline 6 & \text { Fixed Cost } & \$ 100,000 \
\hline 7&\text { Unit CD Cost }&\$250\
\hline\\hline 9 & \text { Recording In-house } & \
\hline 10 & \text { Unit CD Cost } & \$350 \\hline 11 & & \
\hline 12 & \text { Production Volume } & 3000 \
\hline 13 & & \
\hline 14 & \text { Model } & \\hline 15 & & \
\hline 16 & \begin{array}{l}
\text { Total studio } \
\text { manufacturing cost }
\end{array} & \$ 850,000 \\hline 17 & \begin{array}{l}
\text { Total in-house } \
\text { manufacturing cost }
\end{array} & \$ 1,050,000 \
\hline 18 & & \\hline 19 & \text { Cost Difference } & -\$ 200,000 \
\hline 20 & \text { Recording Decision } & \text { Studio } \
\hline
\end{array}$
-Using the spreadsheet below, provide the steps in using Excel formulas in finding the cost of the first order for Item number 1345, and the total cost of all Item numbers 1345, using the
Match and Index functions in Excel. Column B is sorted by item number in ascending order. \(\begin{array}{|l|l|l|l|l|l|}
\hline & \text { A } & \text { B } & \text { C } & \text { D } & \text { E } \
\hline 1 & \text { Purchase Orders } & & & & \
\hline 2 & & & & & \\hline 3 & \text { Supplier } & \text { Item No. } & \text { Item Cost } & \text { Quantity } & \text { Cost per Order } \
\hline 4 & \text { Rex Technologies } & 1123 & \$ 89 & 35 & \$ 3115 \\hline 5 & \text { Rex Technologies } & 1234 & \$ 150 & 15 & \$ 2250 \
\hline 6 & \text { Rex Technologies } & 1345 & \$ 15 & 40 & \$ 600 \
\hline 7 & \text { Rex Technologies } & 1345 & \$ 15 & 20 & \$ 300 \
\hline 8 & \text { Max's Wavetech } & 1345 & \$ 5 & 10 & \$ 50 \\hline 9 & \text { Max's Wavetech } & 1765 & \$ 278 & 27 & \$ 6950 \
\hline 10 & \text { Max's Wavetech } & 1654 & \$ 120 & 18 & \$ 2160 \
\hline 11 & \text { Rex Technologies } & 1765 & \$ 54 & 56 & \$ 3024 \
\hline 12 & \text { Rex Technologies } & 1100 & \$ 71 & 33 & \$ 2343 \
\hline 13 & \text { Rex Technologies } & 1100 & \$ 10 & 14 & \$ 140 \
\hline 14 & \text { Rex Technologies } & 1683 & \$ 7 & 25 & \$ 175 \
\hline 15 & \text { Max's Wavetech } & 1683 & \$ 100 & 31 & \$ 3100 \
\hline
\end{array}\)

Accepted Answer

To find the order cost associated with t

Question 19

To use the VLOOKUP(lookup_value, table_array, col_index_num), the table must be sorted in descending order.

Accepted Answer

A) True 
 B)False

Question 20

Which of the following is a differentiation between calculating using the functions COUNT and COUNTIF?

Accepted Answer

A)  COUNT does not require a range of cells; COUNTIF requires a range of cells. 
B)  COUNT only requires a range of cell and can be obtained without a special criteria, COUNTIF requires range and special criteria to be calculated. 
C)  COUNT requires a range of cells; COUNTIF does not require a range of cells, only special criteria. 
D)  COUNT calculates the sum of values for a range of cells; COUNTIF finds the largest value in a range of cells. 
A)  COUNT does not require a range of cells; COUNTIF requires a range of cells. 
B)  COUNT only requires a range of cell and can be obtained without a special criteria, COUNTIF requires range and special criteria to be calculated. 
C)  COUNT requires a range of cells; COUNTIF does not require a range of cells, only special criteria. 
D)  COUNT calculates the sum of values for a range of cells; COUNTIF finds the largest value in a range of cells.

In a MATCH function, the default value for match_type = 0.

A positive NPV means that the investment will provide added value because the projected return exceeds the .

Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order?

In a MATCH function, if match_type = 1, then the function finds the smallest value that is greater than or equal to lookup_value.

The Excel function of is used to find the largest value in a range of cells.

To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12?

Using a $ sign before a column label .

In a MATCH function, if the match_type = 0, then .

Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not?

Which of the following is a difference between relative addressing and absolute addressing when using cell formulas in Excel?

Give the logical function for the following: If cell B7 equals 12, check contents of cell B10. If cell B10 is 10, then the value of the function in the string is YES; if not, it is a blank space. If cell B7 does not equal 12, then the value of the function is 7.

is a logical function that returns one value if the condition is true and another if the condition is false.

The reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time.

Using a $ sign before both the row and column labels keeps the reference to that cell fixed no matter where the formula is copied.

measures the worth of a stream of cash flows, taking into account the time value of money.

The easiest way to locate a particular function is to select a cell and click on the Insert function button represented by on the Excel ribbon.

To use the VLOOKUP(lookup_value, table_array, col_index_num), the table must be sorted in descending order.

Which of the following is a differentiation between calculating using the functions COUNT and COUNTIF?

Business Analytics

Visualizing and Exploring Data

Descriptive Statistical Measures

Probability Distributions and Data Modeling

Sampling and Estimation

Statistical Inference

Trendlines and Regression Analysis

Forecasting Techniques

Introduction to Data Mining

Spreadsheet Modeling and Analysis

Monte Carlo Simulation and Risk Analysis

Linear Optimization

Applications of Linear Optimization

Integer Optimization

Decision Analysis

Filters

Exam 2: Analytics on Spreadsheets

In a MATCH function, the default value for match_type = 0.

A positive NPV means that the investment will provide added value because the projected return exceeds the .

Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order?

In a MATCH function, if match_type = 1, then the function finds the smallest value that is greater than or equal to lookup_value.

The Excel function of is used to find the largest value in a range of cells.

To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12?

Using a $ sign before a column label .

In a MATCH function, if the match_type = 0, then .

Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not?

Which of the following is a difference between relative addressing and absolute addressing when using cell formulas in Excel?

Give the logical function for the following: If cell B7 equals 12, check contents of cell B10. If cell B10 is 10, then the value of the function in the string is YES; if not, it is a blank space. If cell B7 does not equal 12, then the value of the function is 7.

is a logical function that returns one value if the condition is true and another if the condition is false.

The reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time.

Using a $ sign before both the row and column labels keeps the reference to that cell fixed no matter where the formula is copied.

measures the worth of a stream of cash flows, taking into account the time value of money.

The easiest way to locate a particular function is to select a cell and click on the Insert function button represented by on the Excel ribbon.

To use the VLOOKUP(lookup_value, table_array, col_index_num), the table must be sorted in descending order.

Which of the following is a differentiation between calculating using the functions COUNT and COUNTIF?

Business Analytics

Visualizing and Exploring Data

Descriptive Statistical Measures

Probability Distributions and Data Modeling

Sampling and Estimation

Statistical Inference

Trendlines and Regression Analysis

Forecasting Techniques

Introduction to Data Mining

Spreadsheet Modeling and Analysis

Monte Carlo Simulation and Risk Analysis

Linear Optimization

Applications of Linear Optimization

Integer Optimization

Decision Analysis

Filters