Question 1

Which of the following is true about constructing PivotTables?

Accepted Answer

A)  It is not possible to construct the PivotTable in the same worksheet. 
B)  Dragging a field into the Report Filter area allows addition of a third dimension to the analysis. 
C)  Placing a field each in the row and column labels will automatically sum the variable values in the table. 
D)  PivotTables cannot be duplicated by copying and pasting an existing table. 
A)  It is not possible to construct the PivotTable in the same worksheet. 
B)  Dragging a field into the Report Filter area allows addition of a third dimension to the analysis. 
C)  Placing a field each in the row and column labels will automatically sum the variable values in the table. 
D)  PivotTables cannot be duplicated by copying and pasting an existing table. B

Question 2

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)  =IFB7=12,ANDB10=10, "")YES)),7) 
B)  =IFB10=10,ORB7=12,"")"YES")7) 
C)  =IFB7=12,IFB10=10,"YES", "")),7) 
D)  =IFB7=12,ANDB10=10,"YES","")B10="NO"),7) 
A)  =IFB7=12,ANDB10=10, "")YES)),7) 
B)  =IFB10=10,ORB7=12,"")"YES")7) 
C)  =IFB7=12,IFB10=10,"YES", "")),7) 
D)  =IFB7=12,ANDB10=10,"YES","")B10="NO"),7) C

Question 3

Explain the different Lookup functions in Excel.

Accepted Answer

Excel provides some useful functions for finding specific data in a spreadsheet. These functions are useful in many applications:
VLOOKUPlookup_value, table_array, col_index_num) looks up a value in the leftmost
column of a table and returns a value in the same row from a column you specify. The table must be sorted in an ascending order.
HLOOKUPlookup_value, table_array, row_index_num) looks up a value in the top row of a table and returns a value in the same column from a row you specify. The table must be sorted in an ascending order from left to right.
INDEXarray, row_num, col_num) Returns a value or reference of the cell at the intersection of a particular row and column in a given range.
MATCHlookup_value, lookup_array, match_type) Returns the relative position of an item in an array that matches a specified value in a specified order.

Question 4

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each order's size?

Accepted Answer

A)  =IFD4=AND=OR=25,"Large","Small") 
B)  =IFD425,"Small")=ANDD4=25,"Large") 
C)  =IFD4=25,"Large")=OR"Small") 
D)  =IFD4>=25,"Large","Small") 
A)  =IFD4=AND=OR=25,"Large","Small") 
B)  =IFD425,"Small")=ANDD4=25,"Large") 
C)  =IFD4=25,"Large")=OR"Small") 
D)  =IFD4>=25,"Large","Small")

Question 5

To use the VLOOKUPlookup_value, table_array, col_index_num), the table must be sorted in descending order.

Accepted Answer

A) True 
 B)False

Question 6

ANDcondition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not.

Accepted Answer

A) True 
 B)False

Question 7

In a database file, which is organized in a two-dimensional table, the rows represent records of related data elements.

Accepted Answer

A) True 
 B)False

Question 8

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 9

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 10

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

Accepted Answer

A)  ORcondition 1, condition 2…) 
B)  ANDcondition 1, condition 2…) 
C)  TOvalue if true, value if false) 
D)  IFcondition, value if true, value if false) 
A)  ORcondition 1, condition 2…) 
B)  ANDcondition 1, condition 2…) 
C)  TOvalue if true, value if false) 
D)  IFcondition, value if true, value if false)

Question 11

Use the data given below to answer the following question.
Following is the purchase order database of 'The Chef Says So', a restaurant in New York, over the last quarter April-June). \(\begin{array}{|c|c|c|c|r|r|}
\hline \begin{array}{c}
\text { Order } \
\text { Date }
\end{array} & \text { Item } & \text { Region } & \text { Supplier } & \text { Unit Cost } & {\text { Units }} \
\hline 5 / 6 / 2012 & \text { Steel Fork } & \text { Antasia } & \text { Peter } & 5.44 & 98 \
\hline 4 / 7 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Kane } & 23.44 & 53 \
\hline 5 / 13 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Jones } & 8.44 & 39 \
\hline 6 / 10 / 2012 & \text { Silver Spoon } & \text { Puitoria } & \text { Gerry } & 23.44 & 30 \
\hline 6 / 22 / 2012 & \text { Steel Fork } & \text { Almeco } & \text { Sarah } & 6.44 & 59 \
\hline 5 / 17 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Peter } & 8.44 & 63 \
\hline 4 / 25 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Audrey } & 5.44 & 78 \
\hline 6 / 1 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Jones } & 8.44 & 93 \
\hline 4 / 2 / 2012 & \text { Steel Fork } & \text { Almeco } & \text { Thomas } & 5.44 & 35 \
\hline 5 / 27 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Peter } & 12.44 & 63 \
\hline 6 / 13 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Mary } & 8.44 & 93 \
\hline 4 / 30 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Henry } & 5.44 & 32 \
\hline 5 / 11 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Philip } & 23.44 & 84 \
\hline 6 / 18 / 2012 & \text { Steel Fork } & \text { Antasia } & \text { Peter } & 8.44 & 38 \
\hline 5 / 9 / 2012 & \text { Glass Bottle } & \text { Puitoria } & \text { Simson } & 128.45 & 5 \
\hline 5 / 30 / 2012 & \text { Ceramic Bowl } & \text { Antasia } & \text { Peter } & 19.44 & 19 \
\hline 6 / 6 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Mary } & 12.44 & 31 \
\hline 4 / 3 / 2012 & \text { Silver Spoon } & \text { Antasia } & \text { Peter } & 12.44 & 67 \
\hline 6 / 26 / 2012 & \text { Silver Spoon } & \text { Antasia } & \text { Philip } & 23.44 & 18\
\hline 4 / 23 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Kane } & 8.44 & 99 \
\hline 4 / 29 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Simson } & 4.74 & 70 \
\hline 4 / 4 / 2012 & \text { Ceramic Bowl } & \text { Antasia } & \text { Philip } & 19.44 & 77 \
\hline 6 / 15 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Gerry } & 12.44 & 49 \
\hline 6 / 25 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Simson } & 18.45 & 90 \
\hline 5 / 23 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Peter } & 8.44 & 7 \
\hline 6 / 28 / 2012 & \text { Ceramic Plate } & \text { Almeco } & \text { Sarah } & 23.44 & 10 \
\hline 5 / 25 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Jones } & 8.44 & 53 \
\hline 5 / 1 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Audrey } & 5.44 & 69\
\hline
\end{array}\)
$\quad$
$\quad$ \(\begin{array}{|c|c|c|c|r|r|}
\hline\begin{array}{c}
\text { Order } \
\text { Date }
\end{array} & \text { Item } & \text { Region } & \text { Supplier } & \text { Unit Cost } & {\text { Units }} \
\hline 4 / 12 / 2012 & \text { Silver Spoon } & \text { Antasia } & \text { Henry } & 8.44 & 99 \
\hline 4 / 18 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Gerry } & 4.74 & 56 \
\hline 6 / 30 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Gerry } & 12.44 & 83 \
\hline 5 / 19 / 2012 & \text { Glass Bottle } & \text { Puitoria } & \text { Kane } & 128.45 & 8\
\hline 4 / 16 / 2012 & \text { Ceramic Bowl } & \text { Antasia } & \text { Peter } & 8.44 & 65 \
\hline 6 / 4 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Mary } & 15.94 & 58 \
\hline 5 / 2 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Kane } & 27.4 & 45 \
\hline 4 / 19 / 2012 & \text { Glass Bottle } & \text { Almeco } & \text { Sarah } & 278.45 & 6 \
\hline 6 / 11 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Gerry } & 4.74 & 10 \
\hline 5 / 31 / 2012 & \text { Silver Spoon } & \text { Almeco } & \text { Sarah } & 5.44 & 79 \
\hline 6 / 2 / 2012 & \text { Ceramic Plate } & \text { Almeco } & \text { Thomas } & 23.44 & 60 \
\hline 4 / 13 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Audrey } & 4.74 & 17 \
\hline 5 / 3 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Jones } & 8.44 & 14 \
\hline 6 / 3 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Jones } & 23.44 & 97 \
\hline 4 / 17 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Audrey } & 8.44 & 31\\hline
\end{array}\)
-Which of the following relies on sorting data and calculating the cumulative percentage of the characteristic of interest?

Accepted Answer

A)  Randolph diagram 
B)  Anscombe's quartet 
C)  Bland-Altman plot 
D)  Pareto analysis 
A)  Randolph diagram 
B)  Anscombe's quartet 
C)  Bland-Altman plot 
D)  Pareto analysis

Question 12

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

Accepted Answer

A) True 
 B)False

Question 13

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-Which of the following Database functions calculates the count of purchase orders made by Rex Technologies for large size orders?

Accepted Answer

A)  =DCOUNTA3:H10,C3,A12:B13) 
B)  =DCOUNTA3:H10,"Rex Technologies",B12:B13) 
C)  =DCOUNTA3:H10,C3,B12:B13) 
D)  =DCOUNTA4:H10,D3,A12:B13) 
A)  =DCOUNTA3:H10,C3,A12:B13) 
B)  =DCOUNTA3:H10,"Rex Technologies",B12:B13) 
C)  =DCOUNTA3:H10,C3,B12:B13) 
D)  =DCOUNTA4:H10,D3,A12:B13)

Question 14

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-Which of the following Database functions calculates the total quantity ordered in large size?

Accepted Answer

A)  =DSUMA3:H10,"Quantity",B12:B13) 
B)  =DSUMA3:H10,D3,B12:B13) 
C)  =DSUMA3:H10,D3,A12:B13) 
D)  =DSUMA4:H10,D3,B12:B13) 
A)  =DSUMA3:H10,"Quantity",B12:B13) 
B)  =DSUMA3:H10,D3,B12:B13) 
C)  =DSUMA3:H10,D3,A12:B13) 
D)  =DSUMA4:H10,D3,B12:B13)

Question 15

The function returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Accepted Answer

A)  VLOOKUPlookup_value, table_array, col_index_num) 
B)  INDEXarray, row_num, col_num) 
C)  MATCHlookup_value, lookup_array, match_type) 
D)  HLOOKUPlookup_value, table_array, row_index_num) 
A)  VLOOKUPlookup_value, table_array, col_index_num) 
B)  INDEXarray, row_num, col_num) 
C)  MATCHlookup_value, lookup_array, match_type) 
D)  HLOOKUPlookup_value, table_array, row_index_num)

Question 16

Use the data given below to answer the following question.
Following is the purchase order database of 'The Chef Says So', a restaurant in New York, over the last quarter April-June). \(\begin{array}{|c|c|c|c|r|r|}
\hline \begin{array}{c}
\text { Order } \
\text { Date }
\end{array} & \text { Item } & \text { Region } & \text { Supplier } & \text { Unit Cost } & {\text { Units }} \
\hline 5 / 6 / 2012 & \text { Steel Fork } & \text { Antasia } & \text { Peter } & 5.44 & 98 \
\hline 4 / 7 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Kane } & 23.44 & 53 \
\hline 5 / 13 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Jones } & 8.44 & 39 \
\hline 6 / 10 / 2012 & \text { Silver Spoon } & \text { Puitoria } & \text { Gerry } & 23.44 & 30 \
\hline 6 / 22 / 2012 & \text { Steel Fork } & \text { Almeco } & \text { Sarah } & 6.44 & 59 \
\hline 5 / 17 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Peter } & 8.44 & 63 \
\hline 4 / 25 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Audrey } & 5.44 & 78 \
\hline 6 / 1 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Jones } & 8.44 & 93 \
\hline 4 / 2 / 2012 & \text { Steel Fork } & \text { Almeco } & \text { Thomas } & 5.44 & 35 \
\hline 5 / 27 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Peter } & 12.44 & 63 \
\hline 6 / 13 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Mary } & 8.44 & 93 \
\hline 4 / 30 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Henry } & 5.44 & 32 \
\hline 5 / 11 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Philip } & 23.44 & 84 \
\hline 6 / 18 / 2012 & \text { Steel Fork } & \text { Antasia } & \text { Peter } & 8.44 & 38 \
\hline 5 / 9 / 2012 & \text { Glass Bottle } & \text { Puitoria } & \text { Simson } & 128.45 & 5 \
\hline 5 / 30 / 2012 & \text { Ceramic Bowl } & \text { Antasia } & \text { Peter } & 19.44 & 19 \
\hline 6 / 6 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Mary } & 12.44 & 31 \
\hline 4 / 3 / 2012 & \text { Silver Spoon } & \text { Antasia } & \text { Peter } & 12.44 & 67 \
\hline 6 / 26 / 2012 & \text { Silver Spoon } & \text { Antasia } & \text { Philip } & 23.44 & 18\
\hline 4 / 23 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Kane } & 8.44 & 99 \
\hline 4 / 29 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Simson } & 4.74 & 70 \
\hline 4 / 4 / 2012 & \text { Ceramic Bowl } & \text { Antasia } & \text { Philip } & 19.44 & 77 \
\hline 6 / 15 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Gerry } & 12.44 & 49 \
\hline 6 / 25 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Simson } & 18.45 & 90 \
\hline 5 / 23 / 2012 & \text { Ceramic Plate } & \text { Antasia } & \text { Peter } & 8.44 & 7 \
\hline 6 / 28 / 2012 & \text { Ceramic Plate } & \text { Almeco } & \text { Sarah } & 23.44 & 10 \
\hline 5 / 25 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Jones } & 8.44 & 53 \
\hline 5 / 1 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Audrey } & 5.44 & 69\
\hline
\end{array}\)
$\quad$
$\quad$ \(\begin{array}{|c|c|c|c|r|r|}
\hline\begin{array}{c}
\text { Order } \
\text { Date }
\end{array} & \text { Item } & \text { Region } & \text { Supplier } & \text { Unit Cost } & {\text { Units }} \
\hline 4 / 12 / 2012 & \text { Silver Spoon } & \text { Antasia } & \text { Henry } & 8.44 & 99 \
\hline 4 / 18 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Gerry } & 4.74 & 56 \
\hline 6 / 30 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Gerry } & 12.44 & 83 \
\hline 5 / 19 / 2012 & \text { Glass Bottle } & \text { Puitoria } & \text { Kane } & 128.45 & 8\
\hline 4 / 16 / 2012 & \text { Ceramic Bowl } & \text { Antasia } & \text { Peter } & 8.44 & 65 \
\hline 6 / 4 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Mary } & 15.94 & 58 \
\hline 5 / 2 / 2012 & \text { Ceramic Bowl } & \text { Puitoria } & \text { Kane } & 27.4 & 45 \
\hline 4 / 19 / 2012 & \text { Glass Bottle } & \text { Almeco } & \text { Sarah } & 278.45 & 6 \
\hline 6 / 11 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Gerry } & 4.74 & 10 \
\hline 5 / 31 / 2012 & \text { Silver Spoon } & \text { Almeco } & \text { Sarah } & 5.44 & 79 \
\hline 6 / 2 / 2012 & \text { Ceramic Plate } & \text { Almeco } & \text { Thomas } & 23.44 & 60 \
\hline 4 / 13 / 2012 & \text { Steel Fork } & \text { Puitoria } & \text { Audrey } & 4.74 & 17 \
\hline 5 / 3 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Jones } & 8.44 & 14 \
\hline 6 / 3 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Jones } & 23.44 & 97 \
\hline 4 / 17 / 2012 & \text { Ceramic Plate } & \text { Puitoria } & \text { Audrey } & 8.44 & 31\\hline
\end{array}\)
-Describe how to sort the data by inventory value to compute cumulative percentage of total inventory value to help the restaurateur conduct a Pareto analysis. Assume that no damages were caused to the inventory purchased over the three months)

Accepted Answer

In order to calculate inventory value of

Question 17

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-Which of the following Database functions calculates the total cost charged to Rex Technologies for large size orders?

Accepted Answer

A)  =DSUMA3:H10,C3,A12:B14) 
B)  =DSUMA4:H10,"Cost per Order",A12:B13) 
C)  =DSUMA3:H10,C3,A12:B13) 
D)  =DSUMA3:H10,E3,A12:B13) 
A)  =DSUMA3:H10,C3,A12:B14) 
B)  =DSUMA4:H10,"Cost per Order",A12:B13) 
C)  =DSUMA3:H10,C3,A12:B13) 
D)  =DSUMA3:H10,E3,A12:B13)

Question 18

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-Which of the following Database functions calculates the average item cost ordered by Rex Technologies?

Accepted Answer

A)  =DAVERAGEA3:H10,C3,A12:A13) 
B)  =DAVERAGEA3:H10,C3,A12:B13) 
C)  =DAVERAGEA3:H10,"Item Cost",A12:A14) 
D)  =DSUMA3:H10,C3,A12:A13)/COUNTAA4:A10) 
A)  =DAVERAGEA3:H10,C3,A12:A13) 
B)  =DAVERAGEA3:H10,C3,A12:B13) 
C)  =DAVERAGEA3:H10,"Item Cost",A12:A14) 
D)  =DSUMA3:H10,C3,A12:A13)/COUNTAA4:A10)

Question 19

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-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 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 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.

Question 20

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-Which of the following Database functions calculates the count of purchase orders made by Rex Technologies?

Accepted Answer

A)  =DCOUNTA3:H10,C3,A12:A13) 
B)  =DCOUNTA4:H10,C3,A12:A13) 
C)  =DCOUNTA3:H10,C3,A13) 
D)  =DCOUNTA3:H10,A3,A12:A13) 
A)  =DCOUNTA3:H10,C3,A12:A13) 
B)  =DCOUNTA4:H10,C3,A12:A13) 
C)  =DCOUNTA3:H10,C3,A13) 
D)  =DCOUNTA3:H10,A3,A12:A13)

Which of the following is true about constructing PivotTables?

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.

Explain the different Lookup functions in Excel.

To use the VLOOKUPlookup_value, table_array, col_index_num), the table must be sorted in descending order.

ANDcondition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not.

In a database file, which is organized in a two-dimensional table, the rows represent records of related data elements.

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

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

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

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

The function returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Introduction to Labour Market Economics

Data Visualization

Descriptive Statistics

Probability Distributions and Data Modeling

Sampling and Estimation

Statistical Inference

Trendlines and Regression Analysis

Forecasting Techniques

Introduction to Data Mining

Spreadsheet Modeling and Analysis

Simulation and Risk Analysis

Linear Optimization

Integer and Nonlinear Optimization Models

Optimization Analytics

Decision Analysis

Filters

Exam 2: Database Analytics

Which of the following is true about constructing PivotTables?

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.

Explain the different Lookup functions in Excel.

To use the VLOOKUPlookup_value, table_array, col_index_num), the table must be sorted in descending order.

ANDcondition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not.

In a database file, which is organized in a two-dimensional table, the rows represent records of related data elements.

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

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

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

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

The function returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Introduction to Labour Market Economics

Data Visualization

Descriptive Statistics

Probability Distributions and Data Modeling

Sampling and Estimation

Statistical Inference

Trendlines and Regression Analysis

Forecasting Techniques

Introduction to Data Mining

Spreadsheet Modeling and Analysis

Simulation and Risk Analysis

Linear Optimization

Integer and Nonlinear Optimization Models

Optimization Analytics

Decision Analysis

Filters