BUS 311 Excel Chapter 6

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

Duplex

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

!

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

1

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?

18

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?

3.0%, 3.25%, 3.5%, 3.75%

Scenario Manager can manage up to _________ scenarios.

32

How many scenarios can Scenario Manager manage?

32 and thirty two

What is an Excel Add-in?

A program that you can add to Excel to enhance functionality

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

Add-ins dialog box

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

Checking scenarios with variable down payments

How do you start Goal Seek?

Click What-If Analysis on the Data tab and then select Goal Seek

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

Constraints

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

Constraints

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

Data

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

Data

Scenario manager is on the __________ tab.

Data

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

Data

What is a scenario in Excel What-if analysis?

Detailed set of values that represent different possible situations

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

Create from Selection

T/F. A Scenario Manager Summary report displays the single best input to achieve the desired outcome.

False

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

False

T/F. A one-variable table may only be created vertically.

False

T/F. 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.

False

T/F. Discount Rate is a valid range name.

False

T/F. Goal Seek changes multiple variables to reach the optimal goal.

False

T/F. Goal Seek has the ability to restrict variables to integers and greater than zero numbers.

False

T/F. 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.

False

T/F. Goals Seek is an Excel Add-in.

False

T/F. Solver is on the Formulas tab.

False

T/F. The Solver Add-in is loaded by default.

False

T/F. The number of scenarios that Scenario Manager can manage is unlimited.

False

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

False

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

False

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

Function:

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?

Goal Seek

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

Goal Seek

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

In the first row and first column in the table

Which is not true about Scenario Manager?

Is rarely used in business models

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

Name Manager

What tool would you use to rename a named range?

Name Manager

_________ 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.

Negative

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

Scenario Manager

Where does Excel create a Scenario Summary report?

On a new worksheet

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

Options

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

Options and then Add-ins

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

Range

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

Range names are easier for people to remember than cell references.

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 used

Scenario Manager

__________ has an optional summary output of a PivotTable.

Scenario Manager

_____________ can define 32 different scenarios for comparison.

Scenario Manager

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

Solver

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

Solver

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

Substitution

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

True

T/F. An objective cell is required to complete a Solver model.

True

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

True

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

True

T/F. Goal Seek is a what-if analysis tool.

True

T/F. 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.

True

T/F. Optimization models find the highest, lowest, or exact value for one particular result.

True

T/F. Scenario Manager is not an Excel Add-in.

True

T/F. Solver has the ability to generate an answer report.

True

T/F. Solver is an Excel Add-in.

True

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

True

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

Two-variable data table

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

What-if analysis

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

What-if and what if

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

add-in and add in

Business managers often create __________/worst-case scenarios.

best-case and best case

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

binding constraint

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

binding constraint

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

changing variable cells

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.

constraint

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

constraints

A one-variable data table can be build ___________.

either horizontally or vertically

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

non-binding, nonbinding and non binding

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

objective cell

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

objective cell

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

providing a single best answer rather than a table of possible answers

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

range name

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

scenario summary report

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 ____________.

select Custom Number format and type "Payment" in the type box

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

substitution

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

two-variable, two variable and 2 variable

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

underscore and __

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

variable