Deck 10: Data Tables, scenario Manager, and Solver

Question 1

If you want to apply a conditional format that would format different cells with different colors,where one color is assigned to the lowest group of values,another color is assigned to the highest group of values,and gradient colors are assigned to other values,you would use ________.

A)Top/Bottom Rules
B)Data Bars
C)Color Scales
D)Icon Sets

Answer 1

C

Question 2

The Goal Seek command is located in the ________ command on the Data tab.

A)Consolidate
B)Data Validation
C)What-If Analysis
D)Advanced

Answer 2

C

Question 3

Which What-If Analysis tool would be best at determining how much you can borrow for a car loan while paying only $275 a month?

A)Scenario Manager
B)Solver
C)Data Table
D)Goal Seek

Answer 3

D

Question 4

Which What-If Analysis tool would be best at comparing how interest rates affect a monthly car payment?

A)Goal Seek
B)Scenario Manager
C)One-variable data table
D)Two-variable data table

Question 5

The Scenario Manager dialog box enables you to do all of the following EXCEPT ________.

A)add scenarios
B)delete scenarios
C)format scenarios
D)edit scenarios

Question 6

A two-variable data table returns ________ result(s)for each combination of two variables.

A)one
B)two
C)three
D)four

Question 7

All of the following statements are TRUE about one-variable data tables EXCEPT:

A)A one-variable data table can help you analyze how different values of one variable in one or more formulas will change the results of those formulas.
B)If you change the formula used in your data table, the data table will need to be manually updated by pressing F5.
C)The formulas that are used in a one-variable data table must refer to only one input cell.
D)A one-variable data table has input values that are listed either as column-oriented or row-oriented.

Question 8

All of the following statements are TRUE about data tables EXCEPT:

A)A data table takes sets of input values, determines possible results, and displays all the results in one table on one worksheet.
B)Although a data table is limited to only one or two variables, a data table can include as many different variable values as needed.
C)Because data tables only focus on one or two variables, the results are easy to read and share in tabular form.
D)Data tables are one of the four types of what-if analyses tools in Excel.

Question 9

In the Goal Seek dialog box,the ________ box must contain a formula.

A)Goal
B)To value
C)By changing cell
D)Set cell

Question 10

Which of the following is NOT one of the three cost types to consider when calculating the break-even point?

A)Variable costs
B)Mixed costs
C)Fixed costs
D)Alternative costs

Question 11

A ________ analysis studies the relationship between fixed and variable costs,sales volume,and profits.

A)Variable cost
B)Cost-volume-prices
C)Cost-volume-profit
D)Cost-variable-profit

Question 12

A ________ is a type of scenario tool that allows you to change a number in a target cell location in single-unit increments.

A)variable
B)data bar
C)break-even point
D)scroll bar

Question 13

________ makes the data easier to read and understand because it adds a visual or graphical element to the cells or values.

A)Break-even analysis
B)Conditional formatting
C)A data table
D)A scroll bar

Question 14

What is the main difference between Goal Seek and variable data tables?

A)Goal Seek produces a list of result values.
B)Goal Seek uses the original worksheet data to change an input.
C)Goal Seek manipulates multiple variables to produce multiple results.
D)Goal Seek shows results in a separate table.

Question 15

Through a process called ________,Goal Seek repeatedly enters new values in the variable cell to find a solution to the problem.

A)iteration
B)repetition
C)valuation
D)seeking

Question 16

The To value box in the Goal Seek dialog box contains ________.

A)a cell reference from the formula that can be adjusted to achieve the goal
B)a formula
C)the result desired for the formula
D)a function

Question 17

All of the following statements are TRUE about two-variable data tables EXCEPT:

A)The data table can calculate elasticity.
B)You must specify both the row input cell and the column input cell from the original data set.
C)The top left corner must contain a formula or reference the original formula.
D)A custom format can be applied to the formula cell.

Question 18

In a data table,the ________ number format is used to disguise the formula references in the first row as labels.

A)Custom
B)Scientific
C)Text
D)Special

Question 19

A phone bill,which contains a base plan fee and then per minute used,is an example of ________.

A)Variable costs
B)Mixed costs
C)Fixed costs
D)Alternative costs

Question 20

A manager may want to highlight cells for employees who exceeded monthly sales quotas or products that are selling below cost.This is an example of ________.

A)break-even analysis
B)conditional formatting
C)a data bar
D)a data table

Question 21

Which of the following is NOT in the What-If Analysis command in the Data Tools group on the Data tab?

A)Scenario Manager
B)Solver
C)Data Table
D)Goal Seek

Question 22

________ are rules you establish when formulating your Solver model.

A)Constraints
B)By changing variable cells
C)Input values
D)Objective cells

Question 23

The ________ is the default method that Excel's Solver uses.

A)Simplex LP method
B)Evolutionary method
C)GRG Nonlinear method
D)Linear programming method

Question 24

All of the following statements are TRUE regarding Solver EXCEPT:

A)Business managers can use Solver to minimize or maximize the output based on the constraints.
B)The purpose of using Solver is to perform what-if analysis to solve more complex problems and to optimize the outcome.
C)Prior to configuring the Solver variables, you need to ensure that you create a spreadsheet model that can be used to manipulate the values for your constraints.
D)For the Solver to work properly, the formula in the result cell must reference and depend on the variable cells for part of its calculation.

Question 25

Where do you go to begin loading the Solver add-inn?

A)Install on the Add-Ins tab
B)Options on the File tab
C)Add-In options on the File tab
D)What-If Analysis on the Data tab

Question 26

A constraint that affects the optimal solution is considered a ________ constraint.

A)Binding
B)Nonbinding
C)Linear
D)Model

Question 27

All of the following are types of Solver reports EXCEPT ________.

A)Solver limits report
B)Solver statistical report
C)Solver population report
D)Solver sensitivity report

Question 28

What happens when you add a new scenario and generate a new scenario summary report?

A)The new report will be created on a new worksheet.
B)The new scenario will be included into the old report.
C)The new report will be added directly below the original report.
D)The new report will replace the original report.

Question 29

Solver can be used for ________.

A)complex equation solving
B)conditional formatting
C)consolidating data
D)linear programming

Question 30

To view scenarios,click the ________ button in the Scenario Manager dialog box.

A)Summary
B)Add
C)Merge
D)Show

Question 31

Which Solver report type displays various statistical characteristics about the model?

A)Solver limits report
B)Solver statistical report
C)Solver population report
D)Solver sensitivity report

Question 32

Which of the following is NOT one of the three parameters in Solver?

A)Constraints
B)By changing variable cells
C)Input value
D)Objective cell

Question 33

The ________ solving method is the most complex because it is both nonlinear and nonsmooth.

A)Simplex LP method
B)Evolutionary method
C)GRG Nonlinear method
D)Linear programming method

Question 34

To create scenario summary reports,click the ________ button in the Scenario Manager dialog box.

A)Summary
B)Add
C)Merge
D)Show

Question 35

All of the following solving methods are available within the Solver Parameters dialog box EXCEPT ________.

A)Simplex LP method
B)Evolutionary method
C)GRG Nonlinear method
D)Linear programming method

Question 36

Which of the following is TRUE about scenario reports?

A)New reports appear on separate worksheets.
B)Reports cannot be formatted.
C)Click the Show button to create a report.
D)Headings cannot be added once the report is generated.

Question 37

In the Scenario Manager dialog box,click ________ to create a new scenario.

A)Summary
B)Add
C)Merge
D)Show

Question 38

A(n)________ is an application with specific functionality geared toward accomplishing a specific goal.

A)Scenario Manager
B)Data Table
C)downloadable app
D)add-in

Question 39

Solver models can be saved and restored by clicking the Load/Save button on the ________ dialog box.

A)Solver Parameters
B)Scenario Data
C)Scenario Manager
D)Solver Manager

Question 40

What information is displayed in the first section of a Solver report?

A)Objective cell information
B)Variable cell information
C)Report details
D)Constraints information

Question 41

If a product or service is not responsive to change,it is inelastic.

Question 42

Goal seek is a form control that allows you to change a number in a target cell location in single-unit increments.

Question 43

Scenarios can be created without a formula or function on your worksheet.

Question 44

Excel contains three types of what-if analysis tools on the Data tab: Data table,Scenario Manager,and Goal Seek.

Question 45

If a manager wanted to highlight a cell once its value reached the break-even point,the manager will apply the icon sets conditional formatting option.

Question 46

The number of scenarios you can create is limitless.

Question 47

A two-variable data table can be used to calculate elasticity.

Question 48

The scroll bar is a type of scenario tool because of the ability to calculate numerous outputs in other cells by referencing the target cell in formulas and functions.

Question 49

If you delete a scenario and change your mind,you can use the Undo button to undo your deletion.

Question 50

When the golf course is open for business,they have to pay management salaries,insurance,depreciation of building and equipment,and so on,regardless of how many customers they have during the day.These are examples of variable costs.

Question 51

What-if analysis tools provide definitive solutions to problems.

Question 52

For a one-variable data table,you must specify both the row input cell and column input cell from the original data set.

Question 53

Managers can use break-even analysis to calculate the break-even point in sales volume or dollars,estimate profit or loss at any level of sales volume,and help in setting prices.

Question 54

By selecting the Scenario Summary option,you can create a worksheet that includes subtotals and the results of the scenarios.

Question 55

The iteration settings cannot be changed.

Question 56

The Scenario Manager dialog box enables you to add,edit,and delete scenarios.

Question 57

The initial results of Goal Seek are automatically entered into a new worksheet.

Question 58

Formulas that are used in a one-variable data table must refer to several input cells.

Question 59

The value you want to achieve in Goal Seek needs to be entered in the To value box in the Goal Seek dialog box.

Question 60

Utilities such as electricity and water are examples of mixed costs.

Question 61

Changing a binding constraint also changes the optimal solution.

Question 62

In the Goal Seek dialog box,the ________ box contains a formula.

Question 63

The Simplex LP,Generalized Reduced Gradient Nonlinear,and Evolutionary are the three solving methods within the Solver Parameters dialog box.

Question 64

The Goal Seek command is located in the What-If Analysis tool of the ________ tab.

Question 65

Goal Seek repeatedly enters new values in the variable cell through a process called ________.

Question 66

To add a scroll bar,you first need to add the ________ tab.

Question 67

If a large change in price is accompanied by a small amount of change in demand,a product or service is considered ________.

Question 68

Solver can be used to find values that optimize an objective.

Question 69

Managers use ________ to determine the minimum volume the business needs to make and sell if it is a manufacturer,or buy and sell if it is a retail business,to be sustainable.

Question 70

A(n)________ allows you to build a what-if analysis model that includes variable cells linked by one or more formulas or functions.

Question 71

To open a saved Solver model you must first click Load/Save button on the Solver Parameters dialog box.

Question 72

The last section of the Solver report displays information about the report.

Question 73

What-if analysis tools can be found on the ________ tab.

Question 74

PivotTables allow even more manipulation of data because of the three-dimensional view they provide.

Question 75

The scroll bar is a type of ________ tool because of the ability to calculate numerous outputs in other cells by referencing the target cell in formulas and functions.

Question 76

The Reports list in the Solver Results dialog box displays the available report types.

Question 77

If a change in price is accompanied by a large change in quantity demanded,a product or service is considered ________.

Question 78

If a manager wanted to analyze how interest rates affect a monthly loan payment,the best data table to use will be the ________ data table.

Question 79

Input values are listed in both columns and rows in a(n)________ data table.

Question 80

When using Solver,the constraint cell contains a formula that creates a value that you want to optimize.