Deck 6: What-If Analysis

Question 1

The What-If Analysis tool is on the ________ tab.

A)Data
B)Home
C)View
D)Insert

Answer 1

A

Question 2

Given this worksheet,which cell would you use in the set cell:
Box in Goal Seek to see how expensive of a house you can afford and keep your Mortgage Payment at $650?

A)B3
B)E3
C)B2
D)E5

Answer 2

C

Question 3

What are the row and column input cells in this table?

A)B5 and B4
B)B9
C)B6 and B2
D)E5 and E4

Answer 3

A

Question 4

Where would you setup the two substitution values in a two-variable data table?

A)In the first row and first column in the table
B)In the Series dialog box
C)In the bottom row and last column
D)Excel does this for you.

Question 5

What tool would you use to rename a named range?

A)A range cannot be renamed.
B)Define Names
C)Name Manager
D)Range Tool

Question 6

How many variables does Goal Seek change with each time it is run?

A)2
B)1
C)up to 3
D)unlimited

Question 7

If you set up a two-variable data table to calculate monthly payments on different interest rates and loan terms,what values would be a likely set of entries in the left column of your data table?

A)$150,000,$160,000,$170,000
B)APR
C)3)0%,3.25%,3.5%,3.75%
D)$583.73,$630.02,$659.97

Question 8

What is a benefit of using a range name in Excel?

A)Range names are easier for people to remember than cell references.
B)Range names inherently make calculations more accurate.
C)Range names make the data more portable to other applications.
D)Range names are required when using financial functions.

Question 9

Goal Seek differs from one and two-variable data table analysis by ________.

A)building a data table automatically
B)providing a single best answer rather than a table of possible answers
C)providing a row of possibilities
D)providing an average of the possibilities

Question 10

Which of these would be a good candidate for a one-variable data table and payment function?

A)Finding how much you can spend on a car with a specific goal of $450 per month payments
B)Checking scenarios with variable interest rates and down payments
C)Solving for a constraint of 3 years of payments
D)Checking scenarios with variable down payments

Question 11

________ values replace the original value of a variable in a data table.

A)Primary
B)Substitution
C)Table
D)Range

Question 12

To create range names for multiple cells at once,use the ________ tool.

A)Namer
B)Range Creator
C)Defined Names
D)Create from Selection

Question 13

A one-variable data table can be built ________.

A)vertically
B)horizontally
C)in column A
D)either horizontally or vertically

Question 14

What tool would be most effective when comparing the impact of changes in interest rate and purchase price on a new car loan?

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

Question 15

To set up this two-variable data table,what is in B9?

A)Variable APR
B)=$E$5
C)=PMT($B$4,$B$5,$B$2)
D)Monthly PMT

Question 16

To add a column header of Payment for the calculation column in a one-variable data table,Select the cell with the calculation formula and then ________.

A)type Payment in the cell and format as desired
B)select Custom Number format and type Payment in the type box
C)select Custom Number format and type "Payment" in the type box
D)open the one-variable analysis dialog and type Payment in the header field

Question 17

Which of these is not part of the Goal Seek dialog box inputs?

A)Set cell:
B)To value:
C)By changing cell:
D)Function:

Question 18

________ enables you to experiment with different variables or assumptions so you can compare the related outcomes.

A)PivotTables
B)What-if analysis
C)Goal seek
D)Solver

Question 19

Which of these characters is not an acceptable character to use in a range name?

A)_
B)z
C)!
D)1

Question 20

If cell B5 contains the value 6 and is named Quantity and cell C6 contains the value 3 and is named Unit_Price,what would the formula =Quantity*Unit_Price display in D6?

A)Quantity*Unit_Price
B)18
C)9
D)#ERROR

Question 21

Which is not true about Scenario Manager?

A)Is rarely used in business models
B)Defines and manages up to 32 scenarios
C)Provides more variability and details than a data table
D)Used to provide best-case,worst-case,and most-likely scenarios.

Question 22

The ________ are the cells containing variables whose values change within the constraints until the objective cell reaches its optimum value.

A)goal seek cells
B)changing variable cells
C)objective cells
D)constraints

Question 23

Solver does not support which of the following calculations on the target cell?

A)Maximize
B)Range
C)Minimize
D)Exact Value

Question 24

Which of the following is not a solving method supported by Solver?

A)GRG nonlinear
B)Simplex LP
C)Duplex
D)Evolutionary

Question 25

A list of active and inactive Add-ins is in the ________.

A)Options dialog box
B)Properties Pane
C)Add-ins dialog box
D)View tab

Question 26

What is an Excel Add-in?

A)All what-if analysis tools
B)Printer drivers
C)A program that you can add to Excel to enhance functionality
D)A feature installed by default in Excel

Question 27

Scenario Manager can manage up to ________ scenarios.

A)8
B)32
C)16
D)64

Question 28

A(n)________ specifies the cell that contains a formula that produces the value to be optimized with Solver.

A)objective cell
B)set value
C)target cell
D)variable cell

Question 29

Which is the best what-if analysis tool to determine the optimal combination of products to produce in order to maximize profit?

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

Question 30

________ specify the restrictions or limitations imposed on a spreadsheet model as Solver finds an optimum value.

A)Accesses
B)Objectives
C)Variables
D)Constraints

Question 31

What is a scenario in Excel What-if analysis?

A)A two-variable goal seek tool
B)Any what-if analysis
C)A tool that changes one variable through several iterations
D)Detailed set of values that represent different possible situations

Question 32

The limit of no more than a $50,000 down payment is an example of a(n)________ when calculating the optimal combination of down payment and interest rate to purchase a new printing press.

A)variable
B)set value
C)constraint
D)objective

Question 33

If Solver is loaded,it will appear on this tab.

A)Data
B)Formulas
C)Insert
D)View

Question 34

A(n)________ is a rule that Solver must enforce to reach the objective value.

A)binding constraint
B)non-binding constraint
C)objective
D)solving method

Question 35

What selections in the File tab do you make to load an add-in?

A)Properties and then Solutions
B)Properties and then Add-ins
C)Options and then Solutions
D)Options and then Add-ins

Question 36

You have a worksheet with a calculation to estimate production costs based on cost of raw materials,labor,fuel and delivery costs.You would like to see what would happen if the price of fuel and raw materials spiked.Which what-if analysis tool would be best?

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

Question 37

How do you start Goal Seek?

A)Install the Goal Seek add-in.
B)Click Goal Seek on the Formulas tab.
C)Click What-If Analysison the Data tab and then select Goal Seek
D)Click Scenario Manager on the Data tab and then select Goal Seek.

Question 38

Where does Excel create a Scenario Summary report?

A)On a new worksheet
B)On the same worksheet
C)In a new workbook
D)In a dialog box

Question 39

The biggest difference between Scenario Manager and Solver is Solver uses ________.

A)labels
B)constraints
C)solver scenarios
D)the rank in the sort order dialog box

Question 40

What is this an example of?

A)Solver Report
B)Scenario Summary Report
C)Goal Seek Report
D)Multi-value Data Table

Question 41

A valid character to separate words in a range name is ________.

Question 42

This what-if analysis tool is best suited to determine the input value needed to achieve a specific output.

Question 43

________ is an add-in application that searches for the optimum solution by manipulating variables using constraints you set.

Question 44

Business managers often create ________ /worst-case scenarios.

Question 45

________ has an optional summary output of a PivotTable.

Question 46

How many scenarios can Scenario Manager manage?

Question 47

A(n)________ is a program in Excel which provides enhanced capabilities over the base installation.

Question 48

________ analysis enables you to compare how changes affect a related outcome.

Question 49

If you are analyzing how much a 30 year mortgage would cost with different interest rates,the interest rate would be the ________ value in the what-if scenario.

Question 50

If Solver is installed on your computer,it will display on the ________ tab.

Question 51

Scenario manager is on the ________ tab.

Question 52

If you would like to determine how many bags of popcorn to sell at a profit margin of $5 to raise $100 and have no constraints,which what-if analysis tool should you choose?

Question 53

A(n)________ is a label for a cell or range of cells which can be used in formulas.

Question 54

________ can define 32 different scenarios for comparison.

Question 55

Select ________ from the File menu to install the Solver Add-in.

Question 56

The what-if analysis tool which uses two substitution values is a(n)________ data table.

Question 57

The what-if analysis tool best suited to evaluating multiple scenarios within a formula is ________.

Question 58

A(n)________ is the changeable input value used in a what-if analysis.

Question 59

A(n)________ is a structured report organized as a worksheet to summarize multiple scenarios.

Question 60

Use the ________ to edit,delete and create range names.

Question 61

A one-variable table may only be created vertically.

Question 62

A(n)________ is a rule that Solver has to enforce to reach the objective value.

Question 63

Excel uses two substitution values laid out in a grid in two-variable data table.

Question 64

A two-variable data table is the most effective tool to find the appropriate interest rate and purchase price to make a monthly payment exactly $500 in a car loan.

Question 65

Goal Seek changes multiple variables to reach the optimal goal

Question 66

A(n)________ constraint does not restrict the target that Solver finds.

Question 67

A variable is an input value that changes the outcome of the situation.

Question 68

________ are the limits or restrictions placed on a Solver model.

Question 69

It is a good idea to use a custom format to label the top left corner of a two-variable data table with a readable label.

Question 70

Use Paste Names to document a list of all named ranges in a workbook.

Question 71

Discount Rate is a valid range name.

Question 72

Goal Seek is an Excel Add-in.

Question 73

________ results are mathematically feasible but physically impossible solutions such as producing a less than zero products.These should be constrained to be greater than zero in some situations.

Question 74

Goal Seek would be the best what-if analysis tool to use to discover the necessary interest rate to keep your car payment below $500 per month.

Question 75

What-if analysis always provides a definitive solution to a problem.

Question 76

A one-variable data table would be the appropriate what-if analysis tool to compare loans with variable time period and interest rates.

Question 77

The ________ specifies the cell containing the formula that produces a value for Solver to optimize.

Question 78

Goal Seek is a what-if analysis tool.

Question 79

Excel must have at least one formula in one-variable data table.

Question 80

You do need to use an absolute cell reference when copying formulas with named ranges.