Deck 6: What-If Analysis

Question 1

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

Answer 1

D

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

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

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

Answer 3

D

Question 4

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 5

________ 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 6

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 7

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

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

Question 8

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

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

Question 9

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 10

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

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

Question 11

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 12

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 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 are the row and column input cells in this table?

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

Question 15

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 16

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 17

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 18

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 19

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

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

Question 20

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 21

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 22

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 23

What is this an example of?

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

Question 24

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 25

Scenario Manager can manage up to ________ scenarios.

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

Question 26

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

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

Question 27

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 28

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

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

Question 29

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 30

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

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

Question 31

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 32

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 33

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 34

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 35

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 36

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 37

________ 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 38

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 39

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 40

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 41

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

Question 42

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

Question 43

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

Question 44

________ can define 32 different scenarios for comparison.

Question 45

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

Question 46

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

Question 47

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

Question 48

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

Question 49

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

Question 50

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

Question 51

How many scenarios can Scenario Manager manage?

Question 52

Scenario manager is on the ________ tab.

Question 53

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

Question 54

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

Question 55

________ has an optional summary output of a PivotTable.

Question 56

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

Question 57

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

Question 58

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 59

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 60

Business managers often create ________ /worst-case scenarios.

Question 61

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

Question 62

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 63

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 64

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

Question 65

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

Question 66

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

Question 67

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 68

Goal Seek is a what-if analysis tool.

Question 69

________ 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 70

Goal Seek is an Excel Add-in.

Question 71

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

Question 72

Discount Rate is a valid range name.

Question 73

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

Question 74

Goal Seek changes multiple variables to reach the optimal goal

Question 75

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

Question 76

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

Question 77

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

Question 78

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

Question 79

A one-variable table may only be created vertically.

Question 80

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