Deck 8: Using Data Tables and Excel Scenarios for What-If Analysis

Question 1

To compare scenarios, you can create a summary report that lists the scenarios side by side or in a PivotScenario.

Answer 1

False

Question 2

You can use Custom number formats to display a label in a worksheet even when the cell contains a value or a formula.

Answer 2

True

Question 3

To set up a scenario, you create or use a worksheet that contains sets of input cells and no output cells.

Answer 3

False

Question 4

In a one-variable data table, you enter possible values for the single input variable in the last row or column of the data table.

Question 5

In a two-variable data table, you use two sets of values for two input variables, and display only the value of a single result cell.

Question 6

What-if analysis allows you to see what output results if the values of various input assumptions change.

Question 7

A one-variable data table allows only one input assumption to vary.

Question 8

When you have many input assumptions that will vary in each scenario, it is sometimes easier to identify the cells in the input section that will not change in the scenarios, thereby determining the scenarios' changing cells through a process of elimination.

Question 9

Cells that contain formulas should be specified as changing cells in scenarios.

Question 10

There is no right answer to the question of whether the effect of income taxes should be considered when performing what-if analysis.

Question 11

A data table depends on values and formulas used in a worksheet, and must appear on the same worksheet that contains this data.

Question 12

Technically, the number of scenarios you can create is limited only by your computer's memory, but scenario reports can include data from only the first 15 scenarios.

Question 13

Because a PivotTable is interactive, you can change the view of the data to see more or fewer details.

Question 14

You can add a title and borders to make the worksheet more visually appealing and easy to understand.

Question 15

Because what-if analysis usually involves many estimates and other projections, range analysis helps managers learn how tolerant the projected results are of changes in those estimates and assumptions.

Question 16

You can omit taxes from your estimates, or use your marginal tax rate.

Question 17

To create a two-variable data table, you must first set up a data source file.

Question 18

Data tables allow you to organize and present the results of only one what-if analysis.

Question 19

A scenario can span worksheets.

Question 20

Although data tables and scenarios are ideal for showing the results of the interaction of a number of variables, analyzing which results are more likely than others depends on your knowledge of your business and markets.

Question 21

You use a(n) one-variable data table to determine how two values affect a single result. _________________________

Question 22

Rough analysis is a type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions. _________________________

Question 23

To create a(n) scenario, you plan the input data you want to use and the results, or output, you want to achieve. _________________________

Question 24

You use a ____ when you want to ask a what-if question involving a range of values.

A) data table
B) source table
C) data chart
D) source chart

Question 25

The contribution margin represents the amount of revenue that can contribute to covering the fixed expenses of a company. _________________________

Question 26

A ____ is running a what-if analysis on realistic but not actual data.

A) simulation
B) situation
C) template
D) scenario

Question 27

A(n) source table is a range of cells containing values and formulas. _________________________

Question 28

A(n) PivotChart report is an interactive table that groups and summarizes information in a concise format so you can easily analyze the information. _________________________

Question 29

You use data tables when you want to conduct a what-if analysis involving one or two input cells. _________________________

Question 30

You can use two-variable data tables to base a what-if analysis on realistic, but not actual, data. Doing so is called running a(n) situation. _________________________

Question 31

A(n) one-variable data table has only one input cell and many result cells. _________________________

Question 32

The breaking point is the point at which revenue equals expenses. _________________________

Question 33

You use the Simulation Manager to define and save sets of values as scenarios, and then you view and change them as necessary to produce and compare different results. _________________________

Question 34

Break-even analysis is a type of what-if analysis that concentrates on an activity at or around the point at which a product breaks even-or where the ____ is $0.

A) profit
B) cost
C) income
D) COGS

Question 35

A ____ analysis asks, "How much will projected profits change if the cost of goods sold per unit changes by $1? By $2?"

A) sensitivity
B) random
C) systematic
D) simulated

Question 36

Creating the structure for a data table involves creating the set of input values you want to use in a what-if analysis and indicating the results you want to see in the target table. _________________________

Question 37

Because you can compare the results of many calculations in data tables, they can be very useful when performing a ____ analysis.

A) break-even
B) sensitivity
C) both a and b
D) neither a nor b

Question 38

At its most basic level, what-now analysis involves asking a question about a worksheet, such as: "What if the revenue of the swimwear product line increased by $10,000 per month?" _________________________

Question 39

When you change the values, the data table shows you how those changes affect the results of the formulas. _________________________

Question 40

Source cells contain values, and result cells show the results of formulas. _________________________

Question 41

As with one-variable data tables, you must specify how a two-variable data table is structured and which cells in the worksheet's input section relate to the table's ____.

A) values
B) formula
C) formatting
D) file size

Question 42

You must structure a two-variable data table so that the input values are perpendicular to each other, and so that the table's output formula is located ____ the two sets of input variables.

A) on top of
B) at the intersection of
C) under
D) next to

Question 43

A data table depends on values and formulas used in a worksheet, and must appear ____ that contains this data.

A) on the same worksheet
B) in the same file
C) in the same table
D) in the same row

Question 44

Use a(n) ____ data table to see how two factors-for example, different interest rates and loan terms-affect a loan payment.

A) one-variable
B) two-variable
C) multi-variable
D) output

Question 45

In a two-variable data table, you can vary the values of two input variables, and show the results for ____ output value(s).

A) one
B) two
C) one or two
D) two or more

Question 46

To conduct a ____ analysis, you can ask, "How would the break-even point and profitability estimates change if an estimate about one or more other assumptions changes?"

A) sensitivity
B) variable
C) systematic
D) routine

Question 47

Use a(n) ____ data table to see how different interest rates affect a loan payment.

A) one-variable
B) two-variable
C) multi-variable
D) input

Question 48

To open the Data Table dialog box, click the DATA tab on the ribbon, click the ____ Analysis button in the Data Tools group, and then click the Data Table command.

A) What-If
B) Sensitivity
C) Data
D) Data Table

Question 49

A one-variable data table allows you to compare results calculated from changes to ____ input value(s).

A) only one
B) one or more
C) one or two
D) two or more

Question 50

Income tax expense on income statements for ____ companies and the amount of income tax actually paid are two different numbers.

A) publicly traded
B) private
C) large
D) small

Question 51

Excel can be used to create two types of data tables: one-variable data tables and ____ data tables.

A) multi-variable
B) two-variable
C) input
D) output

Question 52

Creating the structure for a data table involves creating the set of ____ you want to use in your what-if analysis, and then indicating the results you want to see in the data table.

A) input values
B) data values
C) combination values
D) lookup values

Question 53

In preparation for executing the command to complete the data table, first select the ____ that will constitute the completed data table.

A) range
B) output area
C) input area
D) chart area

Question 54

____ margins are widely used in analysis, especially when evaluating the profitability of sales alternatives and performing break-even analysis.

A) Lookup
B) Contribution
C) Data
D) Source

Question 55

Many companies decide to use the company's ____ tax rate in their what-if analysis.

A) marginal
B) projected
C) relative
D) absolute

Question 56

A two-variable data table has two input cells and ____ result cell(s).

A) one
B) two
C) variable
D) random

Question 57

A contribution margin is calculated by subtracting variable expenses from sales, and represents the amount of revenue that contributes to covering the ____ expenses of a company.

A) variable
B) fixed
C) tax
D) accounts payable

Question 58

To specify which input cell in the worksheet relates to the values in the data table, open the ____ dialog box.

A) Data Table
B) Input Cell
C) Worksheet
D) What-if

Question 59

Creating a data table in Excel involves the very important step(s) of ____.

A) setting up the structure for the data table
B) instructing Excel on how the data table's structure relates to the input section of the worksheet
C) both a and b
D) neither a nor b

Question 60

If a company decides to use income tax expense as a factor in analyses, a method it could use to compute the effects is ____.

A) rules based on Generally Accepted Accounting Principles (GAAP)
B) tax-basis rules
C) either a or b
D) neither a nor b

Question 61

Scenario reports can include data from only the first ____ scenarios.

A) 32
B) 151
C) 251
D) 351

Question 62

A defined name can be used outside its scope if it is ____, which means if its original scope is identified in its reference.

A) qualified
B) saved
C) archived
D) linked

Question 63

Defined names with a global scope can be created for cells and ranges using the Name box in the Formula Bar, and using the ____ command in the Defined Names group on the FORMULAS tab.

A) Create from Formula
B) Create from Selection
C) Create from Cell
D) Create from Range

Question 64

When applying names to cells and ranges, to indicate the start and end of words, the textbook recommends ____.

A) using a mix of uppercase and lowercase letters
B) including underscore ( _ ) characters
C) including a period ( . ) character
D) all of the above

Question 65

Before you start creating scenarios, you can name the ____ cells you plan to use in the scenario.

A) input
B) result
C) both a and b
D) neither a nor b

Question 66

Each ____ in a scenario must be unique within its scope.

A) defined name
B) refined name
C) comment
D) data label

Question 67

The textbook recommends using defined names that are fairly ____ and meaningful.

A) short
B) long
C) unusual
D) common

Question 68

Using ____ can make the process of creating scenarios and using them easier.

A) range names
B) cell names
C) row names
D) column names

Question 69

To set up a scenario, you create or use a worksheet that contains sets of input and output cells, as you do with data tables. For example, in a worksheet listing sales assumptions and projections, the projections are ____.

A) input cells
B) output cells
C) guiding cells
D) lead cells

Question 70

Although Excel does not consider case when evaluating cell names, the use of case differences in cell names can make them ____.

A) easier to read
B) easier to interpret
C) both a and b
D) neither a nor b

Question 71

You can use the Scenario Manager to perform a what-if analysis with ____ input cell(s).

A) at most one
B) at most two
C) only one or two
D) more than two

Question 72

Excel allows up to ____ changing cells in each scenario.

A) 32
B) 51
C) 255
D) 399

Question 73

To determine the ____ in a worksheet, ask which input assumptions you want to vary from one option to another.

A) changing cells
B) result cells
C) variance cells
D) formula cells

Question 74

Data tables can be used to give you ____.

A) break-even information
B) information about the interaction of various selling prices
C) information about how sensitive profits are to price changes
D) all of the above

Question 75

You use the ____ to define and save sets of values as scenarios, and then you view and change them as necessary to produce and compare different results.

A) Scenario Manager
B) Scenario Wizard
C) Values Manager
D) Values Wizard

Question 76

As with data tables, Excel allows you to run scenarios only on worksheets that have well-structured input and output sections, with the output section depending on the input section through the use of ____.

A) formulas
B) formats
C) hyperlinks
D) range names

Question 77

As one of the final steps of creating a scenario, you create the scenario by naming it and then entering ____ into the Scenario Manager.

A) data
B) file names
C) worksheet names
D) formulas

Question 78

Defined names with a local or global scope can be created using the Define Name command in the Defined Names group on the ____ tab on the ribbon.

A) FORMULAS
B) DATA
C) DATA TABLES
D) WORKSHEET

Question 79

Data tables help you answer questions such as: ____.

A) If I change the unit price, how does that affect gross profit?
B) If I vary the unit price and the cost of goods sold, how does that affect income before taxes?
C) both a and b
D) neither a nor b

Question 80

If a business problem involved three break-even analyses, you could create ____ data table(s) by varying the unit price and sales projection values, but that would involve generating and printing each data table to compare the analyses.

A) one
B) two different
C) three different
D) three identical