MIS 205 (Chapter 8)

Two major steps for creating a data table

1. Set up the structure for the data table. 2. Instruct Excel about how the data table's structure relates to the input section of the worksheet

Two variable data table instructions

1. set up the structure for the data table 2. indicate how the data table's structure relates to the input section of the worksheet.

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 251 scenarios.

251 scenarios (517)

How to create a one-data variable data

498

Hide formulas by applying custom number formats to cells

500

How to create a two-variable data table

506

How to Edit a Scenario

526

Delete a Scenario

527

Enhancing Scenario Summary Reports

529

It doesn't matter which set of input values you place in a column and which set you place in a row, as long as the table results can be understood by worksheet users.

:)

Level 3

:)

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

Income taxes are a real expense to companies FASB GAAP Many companies use their marginal tax rate in their what-if analysis

You can change values, but not the defined names for cells and ranges in the Scenario Manager dialog box

LL When you change the values, the scenario results are updated to reflect the new information

Level 2

Let's Go (Using scenarios to perform a what if analysis)

syntax for SUMPRODUCT

SUMPRODUCT(array1, array2, array3) You can have 2 to 30 arrays whose components you want to multiply and then add in this function

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

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

what-if-analysis

a process of changing values to see how those changes affect the outcome of formulas in an Excel worksheet allows you to see what output results if the values of various inputs assumptions change

data table

a range of cells containing values and formulas when you change the values, the data table shows you how those changes affect the results from the formulas. data tables allow you to organize and present the results of multiple what-if analyses

scenario

a set of values stored in a worksheet that describes different situations, such as worst-case, likely case, and best-case scenarios

Break-even analysis

a type of what-if analysis that concentrates on an activity at or around the point at which a product breaks even the point at which the expenses of creating and selling the product are equal to the revenue it produces or where the profit is 0

Scenario Manager

allows you to consolidate multiple what-if models in one worksheet.

range name

also called a defined name can refer to a cell, range of cells, formula, or constant value in Excel

You must structure a two-variable data table so that the input values are perpendicular to each other

and the table's output formula is located at the top left cell of the dta table. This cell is also the intersection of the two sets of input variables,

Sensitivity analysis

another type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions sensitivity analysis helps managers learn how tolerant the projected results are of changes in those estimates and assumptions

cumulative probability distribution

assigns a probability to every interval of numbers

Each defined name must be unique within its scopoe

but a defined name can be used outside its scope if it is qualified, which means if its original scope is identified in its reference

Names can be 255 characters long

but cannot include spaces or punctuation marks other than periods or underscores

To compare scenarios

create a summary report that lists the scenarios side by side or in a PivotTable

cntrl + ` (accent)

displays formulas

A PivotTable

extracts, organizes, and summarizes data so you can analyze it by making comparisons, detecting patterns, and relationships, and recognizing trends

A defined name can have a scope for the entire workbook Or for one level

global level local level

two-variable data table

has two input cells, but only one result cell see how tow-factors different interest rates and loan terms affect a loan payment

IN a tw-variable data table, you use two sets of values for two input variables, but unlike one-variable, you display only the values of a single result cell

input cells contain values and result cells show the results of formulas

changing cells

input cells in the Scenario Manager because these are the cells that you want to change as you switch from one scenario to another.

two types of data tables

one-variable and two-variable both have input cells (cells containing values that can vary

one-variable data table

only one input cell and can have many result tables

Arrays

ranges or groups of related data values The arrays used in the SUMPRODUCT function must be on the same dimensions and parallel to each other. If they are not, a #VALUE! error is returned

Output cells

result cells a scenario cannot span worksheets

contribution margin

subtracting variable expenses from sales represents the amount of revenue that contributes to covering the fixed expenses of a company

The more times you repeat the experiment

the closer your results are to the expected values

step-variable relationship

the cost doesn't vary directly with the number of units, but varies as the units reach steps of quantities

To correctly perform the simulation

the iteration values are directed to any empty cell in the worksheet The iteration forces Excel to enter the nine values in the top row of the table for each values in the column

Simulated Results

those that are based on realistic, but not actual data RAND and VLOOKUP

SUMPRODUCT function

to calculate the total sales revenue and each variable expense is very useful when you want to sum a series of products, as long as the ranges involved are parallel to each other in the worksheet

To indicate the start and end of words in defined names, you can use a mix of uppercase and lowercase letters without spaces, as in MenTrunksUnitsSold, or you can include

underscore (_) or period ( . )

All defined names have a scope

which specifies the location in which the name is valid

In a two-variable data table

you can vary the values of two input variables, but show the results for only one output value. The two input variables that richard will vary are selling price and the number of units sold. The output or result will be profit before taxes.

A data table depends on values and formulas used in a worksheet and must appear on the same worksheet containing this data

you must structure the worksheet so it uses input cells that contain values you want to modify in a what-if analysis.

To create a scenario

you plan the input data you want to use and the type of results, or output, you want to achieve Next, you prepare a worksheet by adjusting its layout, if necessary, and naming the input and output cells or ranges