Question 1

Arrows pointing from the selected cell to cells that depend on the selected cell are generated by using the ___________ button of the Formula Auditing group.

Accepted Answer

A) Error Checking 
B) Trace Precedents 
C) Trace Dependents 
D) Watch Window 
A) Error Checking 
B) Trace Precedents 
C) Trace Dependents 
D) Watch Window

Question 2

The __________ function is used for the conditional computation of expressions in Excel.

Accepted Answer

A) EFFECT 
B) IF 
C) FALSE 
D) NOT 
A) EFFECT 
B) IF 
C) FALSE 
D) NOT

Question 3

The _____________ button provides an automatic means of checking for mathematical errors within formulas of a worksheet.

Accepted Answer

A) Error Checking 
B) Trace Precedents 
C) Watch Window 
D) Math & Trig 
A) Error Checking 
B) Trace Precedents 
C) Watch Window 
D) Math & Trig

Question 4

The modeling process begins with the framing of a ______________ that shows the relationships between the various parts of the problem being modeled.

Accepted Answer

A) mathematical model 
B) conceptual model 
C) circular model 
D) correlation model 
A) mathematical model 
B) conceptual model 
C) circular model 
D) correlation model

Question 5

A(n) ____________ refers to a model input that can be controlled in a spreadsheet model.

Accepted Answer

A) decision variable 
B) outlier 
C) parameter 
D) dummy variable 
A) decision variable 
B) outlier 
C) parameter 
D) dummy variable

Question 6

The calculations of a cell can be investigated in great detail by using the ___________ button.

Accepted Answer

A) Calculation Options 
B) Evaluate Formula 
C) Error Checking 
D) Show Formulas 
A) Calculation Options 
B) Evaluate Formula 
C) Error Checking 
D) Show Formulas

Question 7

A(n) _____________ is a visual representation that shows which entities affect others in a model.

Accepted Answer

A) decision tree diagram 
B) influence diagram 
C) entity chart 
D) time series plot 
A) decision tree diagram 
B) influence diagram 
C) entity chart 
D) time series plot

Question 8

Given below is a sample list of 20 products in a grocery store with the product code, the price, and the associated discount rates. $$\begin{array} { | c | c | c | } 
\hline \text { Product code } & \text { Price (\$) } & \text { Discount (\%) } \
\hline \text { A003 } & 4.00 & 5 \
\hline \text { A345 } & 2.70 & 0 \
\hline \text { B985 } & 4.50 & 5 \
\hline \text { C765 } & 1.50 & 0 \
\hline \text { F302 } & 3.00 & 5 \
\hline \text { B453 } & 6.80 & 10 \
\hline \text { A109 } & 9.50 & 10 \
\hline \text { F432 } & 4.80 & 5 \
\hline \text { D234 } & 5.40 & 10 \
\hline \text { B432 } & 2.60 & 0 \
\hline \text { D765 } & 6.90 & 10 \
\hline \text { A406 } & 2.60 & 5 \
\hline \text { D203 } & 5.40 & 10 \
\hline \text { F405 } & 3.60 & 0 \
\hline \text { C432 } & 5.20 & 5 \
\hline \text { C } 106 & 3.20 & 5 \
\hline \text { D324 } & 1.30 & 0 \
\hline \text { F456 } & 5.20 & 10 \
\hline \text { A156 } & 2.50 & 5 \
\hline \text { B } 654 & 1.10 & 0 \
\hline
\end{array}$$ ​ 
a. Display your use of the VLOOKUP function and find the price of the products A109, F432, B985, D203, C432, B654, and A345.
b. Display your use of the COUNTIF function and determine the number of products associated with each discount rate - 0%, 5%, and 10%, from the provided list.​

Accepted Answer

The answer of Given below is a sample list of...

Question 9

Which of the following would be a likely mathematical expression for Total Variable Cost? ​   ​

Accepted Answer

A) Total Variable Cost = Production Volume × Revenue per Unit 
B) Total Variable Cost = Material Cost per Unit × Labor Cost per Unit 
C) Total Variable Cost = Total Cost - (Material Cost per Unit + Labor Cost per Unit) 
D) Total Variable Cost = (Material Cost per Unit + Labor Cost per Unit) × Production Volume 
A) Total Variable Cost = Production Volume × Revenue per Unit 
B) Total Variable Cost = Material Cost per Unit × Labor Cost per Unit 
C) Total Variable Cost = Total Cost - (Material Cost per Unit + Labor Cost per Unit) 
D) Total Variable Cost = (Material Cost per Unit + Labor Cost per Unit) × Production Volume

Question 10

The conceptual model

Accepted Answer

A) helps in organizing the data requirements. 
B) controls the model inputs. 
C) has tools defined to identify the optimal solution. 
D) explores the effects of changing model parameters. 
A) helps in organizing the data requirements. 
B) controls the model inputs. 
C) has tools defined to identify the optimal solution. 
D) explores the effects of changing model parameters.

Question 11

The following table is used to lookup information on a specific product. The Product ID is entered into the box and the information is returned in the green box. What would the lookup formula need to be in C5 to return the information for the Product named in B2.
​   ​

Accepted Answer

=VLOOKUP($

Question 12

Anna operates a consignment shop where she sells cloths for women and children. The average consignments sold per month is 1000. The average material cost and the selling price of each consignment is $8 and $20, respectively.
The monthly fixed cost to run this business is given below:
Rental cost: $750
Utilities: $150
Advertising: $35
Insurance: $100
Labor cost: $4000
Use a two-way data table to show profit changes as a function of different number of consignments sold per month and different material costs. Vary the number of consignments from 400 to 1200 in increments of 100. The eight different material costs are $5.45, $6.23, $6.95, $7.54, $8.23, $8.88, $9, and $9.45.

Accepted Answer

The answer of Anna operates a consignment shop where she...

Question 13

A company asked one of their analysis team to analyze and create models that help decide whether they should manufacture a particular product or outsource its production. The different components are given below.
Fixed Cost, FC = $25,000
Material Cost per Unit, MC = $2.15
Labor Cost per Unit, LC = $2.00
Outsourcing Cost per Unit, O = $4.50
a. Build a spreadsheet model and then construct a one-way data table with production volume as the column input and savings due to outsourcing as the output. Breakeven occurs when savings equal zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur?
b. Using the appropriate Excel tool, find the exact breakeven point.

Accepted Answer

a. @#IMG-DLM& Breakeven appears in the p

Question 14

The Gatson manufacturing company has estimated the following components for a new product.
Fixed cost = $50,000
Material cost per unit = $2.15
Labor cost per unit = $2.00
Revenue per unit = $7.50
a. Construct a spreadsheet model and then construct a one-way data table with production volume as the column input and profit as the output. Breakeven occurs when profit is zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur?
b. Using the appropriate Excel tool, find the exact breakeven point.

Accepted Answer

a. @#IMG-DLM& Breakeven appears in the p

Question 15

The VLOOKUP with range set to _________ takes the first argument and searches the first column of the table for the last row that is strictly less than the first argument.

Accepted Answer

A) FALSE 
B) TRUE 
C) LESS 
D) NULL 
A) FALSE 
B) TRUE 
C) LESS 
D) NULL

Question 16

The selling price of each product sold in a furnishing showroom, and the number of units of each of these product sold during a period of one month are given below. The rental cost of the showroom is $225 and the other costs incurred are included in the cost/unit. $$\begin{array} { | c | c | c | c | } 
\hline \text { Product code } & \text { Price/Unit(\$) } & \text { Cost/Unit(\$) } & \text { Units } \
\hline \text { AD12 } & 232 & 162.4 & 12 \
\hline \text { FD23 } & 334 & 233.8 & 24 \
\hline \text { BD34 } & 342 & 239.4 & 5 \
\hline \text { AG56 } & 267 & 186.9 & 11 \
\hline \text { ET76 } & 345 & 241.5 & 15 \
\hline \text { FA56 } & 235 & 164.5 & 23 \
\hline \text { DE78 } & 546 & 382.2 & 34 \
\hline \text { BF32 } & 245 & 171.5 & 22 \
\hline
\end{array}$$ Display your use of the SUMPRODUCT function and find the profit earned by the showroom in a month.

Accepted Answer

​
​
​ @#IMG-DLM& ​ @#IMG-DLM&

Question 17

Using only "SUMIF", complete the "Team Sales".
​ $$\begin{array} { | l | l | l | l | } 
\hline& { \text { Individual Sales } } \
\hline \text { Rank } & \text { Name } & \text { Sales } & \text { Team } \
\hline 6 & \text { Susan } & 5 & \text { red } \
\hline 8 & \text { Thomas } & 7 & \text { red } \
\hline 9 & \text { Lynn } & 2 & \text { blue } \
\hline 5 & \text { Sean } & 6 & \text { blue } \
\hline 1 & \text { Jerry } & 20 & \text { yellow } \
\hline 2 & \text { George } & 12 & \text { red } \
\hline 10 & \text { Elaine } & 1 & \text { red } \
\hline 7 & \text { Delores } & 4 & \text { blue } \
\hline 4 & \text { Tony } & 9 & \text { yellow } \
\hline 3 & \text { Art } & 12 & \text { blue } \
\hline
\end{array}$$ ​ $$\begin{array} { | l | l | l | l | } 
\hline & { \text { Individual Sales } } \
\hline \text { Rank } & \text { Name } & \text { Sales } & \text { Team } \
\hline 1 & & & \
\hline 2 & & & \
\hline 3 & & & \
\hline 4 & & & \
\hline 5 & & & \
\hline 6 & & & \
\hline 7 & & & \
\hline 8 & & & \
\hline 9 & & & \
\hline 10 & & & \
\hline
\end{array}$$

Accepted Answer

The answer of Using only "SUMIF", complete the "Team Sales".
​...

Question 18

With reference to a spreadsheet model, an uncontrollable model input is known as a(n)

Accepted Answer

A) decision variable. 
B) dummy variable. 
C) parameter. 
D) statistic. 
A) decision variable. 
B) dummy variable. 
C) parameter. 
D) statistic.

Question 19

Spreadsheet models are referred to as what-if models because they

Accepted Answer

A) are mathematical and logic-based models. 
B) allow easy instantaneous recalculation for a change in model inputs. 
C) come preloaded on computers. 
D) have specialized functions to perform detailed analysis. 
A) are mathematical and logic-based models. 
B) allow easy instantaneous recalculation for a change in model inputs. 
C) come preloaded on computers. 
D) have specialized functions to perform detailed analysis.

Question 20

Suppose you have $1100 and decide to purchase a new model of television that costs you $1100. You find an electronics store where a gift voucher, worth $50, is offered for this TV model if payment is made in full at the time of purchase. Alternatively, it can be financed at zero-percent (0%) interest for 5 months with a monthly payment of $220. You now have two options: either opt for the zero-percent financing option for the full amount and invest your money at an annual interest rate of 10%; or choose the full payment option with the $50 discount. Develop a spreadsheet model to find the better option that results in the most savings. Also, find the discount rate for zero-percent financing option.
Hint: Use Goal Seek to find the discount rate that makes the net present value of the payments = $1050.
​

Accepted Answer

@#IMG-DLM& Key cell formulas: @#IMG-DLM& The 0 percent inter

Arrows pointing from the selected cell to cells that depend on the selected cell are generated by using the ___________ button of the Formula Auditing group.

The __________ function is used for the conditional computation of expressions in Excel.

The _____________ button provides an automatic means of checking for mathematical errors within formulas of a worksheet.

The modeling process begins with the framing of a ______________ that shows the relationships between the various parts of the problem being modeled.

A(n) ____________ refers to a model input that can be controlled in a spreadsheet model.

The calculations of a cell can be investigated in great detail by using the ___________ button.

A(n) _____________ is a visual representation that shows which entities affect others in a model.

Which of the following would be a likely mathematical expression for Total Variable Cost?

The conceptual model

The following table is used to lookup information on a specific product. The Product ID is entered into the box and the information is returned in the green box. What would the lookup formula need to be in C5 to return the information for the Product named in B2.

The VLOOKUP with range set to _________ takes the first argument and searches the first column of the table for the last row that is strictly less than the first argument.

Using only "SUMIF", complete the "Team Sales". Individual Sales Rank Name Sales Team 6 Susan 5 red 8 Thomas 7 red 9 Lynn 2 blue 5 Sean 6 blue 1 Jerry 20 yellow 2 George 12 red 10 Elaine 1 red 7 Delores 4 blue 4 Tony 9 yellow 3 Art 12 blue Individual Sales Rank Name Sales Team 1 2 3 4 5 6 7 8 9 10

With reference to a spreadsheet model, an uncontrollable model input is known as a(n)

Spreadsheet models are referred to as what-if models because they

Introduction

Descriptive Statistics

Data Visualization

Descriptive Data Mining

Probability: an Introduction to Modeling Uncertainty

Statistical Inference

Linear Regression

Time Series Analysis and Forecasting

Predictive Data Mining

Linear Optimization Models

Integer Linear Optimization Models

Nonlinear Optimization Models

Monte Carlo Simulation

Decision Analysis

Filters

Exam 10: Spreadsheet Models

Arrows pointing from the selected cell to cells that depend on the selected cell are generated by using the ___________ button of the Formula Auditing group.

The __________ function is used for the conditional computation of expressions in Excel.

The _____________ button provides an automatic means of checking for mathematical errors within formulas of a worksheet.

The modeling process begins with the framing of a ______________ that shows the relationships between the various parts of the problem being modeled.

A(n) ____________ refers to a model input that can be controlled in a spreadsheet model.

The calculations of a cell can be investigated in great detail by using the ___________ button.

A(n) _____________ is a visual representation that shows which entities affect others in a model.

Which of the following would be a likely mathematical expression for Total Variable Cost? ​ ​

The conceptual model

The following table is used to lookup information on a specific product. The Product ID is entered into the box and the information is returned in the green box. What would the lookup formula need to be in C5 to return the information for the Product named in B2. ​ ​

The VLOOKUP with range set to _________ takes the first argument and searches the first column of the table for the last row that is strictly less than the first argument.

With reference to a spreadsheet model, an uncontrollable model input is known as a(n)

Spreadsheet models are referred to as what-if models because they

Introduction

Descriptive Statistics

Data Visualization

Descriptive Data Mining

Probability: an Introduction to Modeling Uncertainty

Statistical Inference

Linear Regression

Time Series Analysis and Forecasting

Predictive Data Mining

Linear Optimization Models

Integer Linear Optimization Models

Nonlinear Optimization Models

Monte Carlo Simulation

Decision Analysis

Filters

Which of the following would be a likely mathematical expression for Total Variable Cost?

The following table is used to lookup information on a specific product. The Product ID is entered into the box and the information is returned in the green box. What would the lookup formula need to be in C5 to return the information for the Product named in B2.