Deck 10: Spreadsheet Models

Question 1

The impact of two inputs on the output of interest is summarized by a

A)Goal Seek.
B)Watch Window.
C)multiple-way data table.
D)two-way data table.

Answer 1

two-way data table.

Question 2

A one-way data table summarizes

A)a single input's impact on the output of interest.
B)multiple inputs' impact on a single output of interest.
C)values of the input cells that will cause the single output value to equal zero.
D)values of cells when not all of the model is observable on the screen.

Answer 2

a single input's impact on the output of interest.

Question 3

Which of the following is true of spreadsheet packages used in business analytics?

A)They are more expensive than specialized packages.
B)They require substantial user training.
C)They come preloaded on computers.
D)They do not have specialized functions to perform detailed analyses.

Answer 3

They come preloaded on computers.

Question 4

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

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

Question 5

Which of the following approaches is a good way to proceed with the influence diagram building for a problem?

A)The influence diagram for the entire problem is built first and then separate portions are clustered to form separate models.
B)The influence diagram for all the model parts at the same level are built in parallel to reduce the likelihood of error.
C)The influence diagram is reverse engineered-the diagram is developed in the opposite direction starting with the model output.
D)The influence diagram for a portion of the problem is built first and then expanded until the total problem is conceptually modeled.

Question 6

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

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

Question 7

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

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

Question 8

Excel's __________ tool allows the user to determine the value of an input cell that will cause the value of a related output cell to equal some specified value.

A)Goal Seek
B)Watch Window
C)Data Validation
D)XLMiner

Question 9

Using the diagram below, which of the following would be a likely mathematical expression for Total Cost? ​ ​

A)Total Cost = Total Variable Cost × Fixed Cost
B)Total Cost = Fixed Cost + Total Variable Cost
C)Total Cost = Total Variable Cost + Total Revenue × Production Volume
D)Total Cost = Fixed Cost + Total Variable Cost + Production Volume

Question 10

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

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

Question 11

What do nodes in an influence diagram represent?

A)Parts of the model
B)Influence levels
C)Road maps
D)Environmental factors

Question 12

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

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

Question 13

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

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 14

The conceptual model

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 15

A __________ decision is one in which companies have to decide whether they should manufacture a product or outsource production to another firm.

A)goal seek
B)two-way
C)voting-based
D)make-versus-buy

Question 16

The influence in an influence diagram is visually depicted by

A)a circular symbol.
B)an arrow.
C)a straight line.
D)the height of the influence diagram.

Question 17

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

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 18

Navigation in a spreadsheet model can be facilitated by

A)using different spreadsheets for each formula in the model.
B)using long calculations in the cells.
C)using clear labels and proper formatting and alignment.
D)referencing data by using hyperlinks to the problem statement.

Question 19

Which of the following design guidelines, if followed, enables the user to update the model parameters without the risk of mistakenly creating an error in a formula?

A)Separating the parameters from the spreadsheet model
B)Documenting the spreadsheet model
C)Using numbers in the spreadsheet formula
D)Using simple formulas

Question 20

An Excel __________ quantifies the impact of changing the value of a specific input on an output of interest.

A)Watch Window
B)Data Table
C)Goal Seek
D)Chart

Question 21

The Watch Window is observable

A)only when the complete model is observable on the screen.
B)only in the same worksheet of a workbook.
C)across different worksheets of a workbook.
D)across different workbooks in the same folder.

Question 22

The ___________ button, located in the Formula Auditing group, creates arrows pointing to the selected cell from cells that are part of the formula in that cell.

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

Question 23

The __________ function pairs each element of the first array with its counterpart in the second array, multiplies the elements of the pairs together, and adds the results.

A)SUM
B)SUMPRODUCT
C)SUMIF
D)VLOOKUP

Question 24

The SUM function in Excel

A)adds up all the numbers in the cells diagonally.
B)adds up only positive numbers in a range of cells.
C)adds up all the numbers in a range of cells.
D)adds up the cells specified by a given condition or criteria.

Question 25

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.

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

Question 26

The ___________ button in the Formula Auditing group allows the user to inspect each formula in detail in its cell location.

A)Evaluate Formula
B)Error Checking
C)Watch Window
D)Show Formulas

Question 27

The following table is used to lookup information on a specific product. The Product ID is entered into cell B2 and the information is returned in the shaded box. There are several formulas in the table. How could I look at the cells that use the value in cell B2? ​

A)Select "Trace Dependents" in the Formula Auditing Group on the Formulas tab.
B)Select "Trace Precedents" in the Formula Auditing Group on the Formulas tab.
C)Select "Show Formulas" in the Formula Auditing Group on the Formulas tab.
D)Select "Evaluate Formulas" in the Formula Auditing Group on the Formulas tab.

Question 28

With reference to the SUMPRODUCT function, which of the following statements is true?

A)The range of cells for each array must contain only nonzero values.
B)Any cell that does not satisfy the specified given condition or criteria will not be considered.
C)The array appearing as the first argument must be sorted in ascending order.
D)The arrays that appear as arguments must be of the same dimension.

Question 29

The __________ function allows the user to pull a subset of data from a larger table of data based on some criterion.

A)VLOOKUP
B)IF
C)SUMIF
D)COUNTIF

Question 30

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

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

Question 31

The user can monitor how listed cells change with a change in the model without searching through the worksheet or changing from one worksheet to another by using the __________ functionality.

A)Goal Seek
B)Evaluate Formula
C)Watch Window
D)Trial-and-Error

Question 32

The function of Trace Precedents and Trace Dependents is to

A)highlight errors in copying and formula construction.
B)ascertain how model parts are segregated.
C)trace the range of cells included in the Watch Window box list.
D)investigate the cell calculations in great detail.

Question 33

The condition that VLOOKUP assumes is that

A)there are no nonzero values in the range.
B)all the arguments are of the same dimension.
C)the first column of the table is sorted in ascending order.
D)the columns with empty cells are to be neglected.

Question 34

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

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

Question 35

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.

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

Question 36

The arguments supplied to the IF function, in order, are the condition for execution, ​

A)the result if condition is true, and the result if condition is false.
B)and the range of cells to test.
C)the array1 of data cells to test, and the array2 of data cells to output.
D)the result if condition is false, and the result if condition is true.

Question 37

Excel searches for an exact match of the first argument in the first column of the data when the range in the VLOOKUP function is

A)TRUE.
B)NULL.
C)FALSE.
D)EXACT.

Question 38

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

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

Question 39

Which of the following tools provides an excellent means of identifying the exact location of an error in a formula?

A)Error Checking
B)Function Library
C)Show Formulas
D)Evaluate Formula

Question 40

Within a given range of cells, the number of times a particular condition is satisfied is computed by using the __________ function.

A)SUMIF
B)IF
C)VLOOKUP
D)COUNTIF

Question 41

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
Construct a spreadsheet model and then use a two-way data table to show how the profit changes as a function of different production volumes and different values of material cost per unit. Vary the production volume from 0 to 100,000 in increments of 10,000. The five different material costs are $1.50, $1.95, $2.15, $2.85, and $3.25.

Question 42

When should you use a one-way data table in Excel instead of a two-way data table?
​

Question 43

Suppose a company supplies four of its products A, B, C, and D, to five different regions. The management wants to know the total number of all products supplied to each region as well as the total number of units of each product supplied. The data collected over a period of month are given below. Display your use of the SUMIF function and find the total volume by each region and total volume by each product.
​
​

Question 44

Using only the "VLOOKUP" function, transfer all of the data from the table below into the "Individual Sales" table.
​ ​

Question 45

The average cost/unit for the production of a particular component at a manufacturing plant varies with the number of units produced in each batch. The data are given below.
​ ​
Suppose the selling price of each unit is $35.
Use a two-way data table to show how the profit changes as a function of demand and the selling price of the product. Vary the demand from 20 units to 80 units in increments of 10 units and selling price from $30 to $40 in increments of $2.

Question 46

Using only the "SUMIF" function, complete the "Team Sales."
​
​

Question 47

John would like to establish a retirement plan that returns an amount of $100,000 in 20 years. Build a spreadsheet model to calculate the amount John must contribute at the end of each year towards his retirement fund, assuming an annual interest rate of 6%.
Use the Excel function
=PMT(rate, nper, pv, fv, type)
The arguments of this function are
rate = the interest rate for the loan
nper = the total number of payments
pv = present value (the amount borrowed which is 0 in this case)
fv = future value (in the formula, indicate this value as negative as the future value command assumes a stream of payments not deposits)
type = payment type (0 = end of period, 1 = beginning of the period)
Also, construct a one-way table with interest rate as the column variable and the amount contributed at the end of each year as the output. Vary the interest rate from 4% to 7% in increments of 0.5%.

Question 48

Suppose you have $1,100 and decide to purchase a new model of television that costs you $1,100. 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 the zero-percent financing option.
Hint: Use Goal Seek to find the discount rate that makes the net present value of the payments = $1,050.
​

Question 49

A clothing retail store offers a discount at the rate of 10% on the customer bill if the purchase exceeds $100. The owner of the store wishes to know the total amount that has been discounted on the customers' purchases on a particular day. The purchase amount for each of the 12 customers who visited the store on that day is given below. Display your use of the IF and SUM functions and calculate the total amount discounted on this single day purchases.