Excel Chapter - 16

PM2 - Management Reporting – IT Office Tools (MS Excel)
Chapter - 16
Forecasting Data:
Determine Potential Outcomes Using Data Tables:
Another way to get answers to what-if questions is by using a data table. A data table is a
cell range that displays the results of a formula using different values.
For example, create a data table to calculate loan payments for several interest rates and
term lengths.
There are two types of data tables:
• One-input Data Table: Displays the results of a formula for multiple values of a single
input cell. For example, if there is a formula that calculates a loan payment, create a one-
input data table that shows payment amounts for different interest rates.
Two-input Data Table: Displays the results of a formula for multiple values of two input
cells. For example, if there is a formula that calculates a loan payment, create a two-input
data table that shows payment amounts for different interest rates and different term
lengths.
Create a one-input data table:
1. Set up the table area. Make sure to include the formula in the top row and the input values
in the left column.
Make sure the formula refers to the input cell.
2. Select the table range that contains the formula and substitution values.
This should include blank cells below the formula and to the right of the values—this is
where the data table will go.
3. Click the Data tab on the Ribbon, click the What-If Analysis button in the Data Tools
group, and select Data Table.
The Data Table dialog box appears.
4. Type the cell reference for the input cell in the Column input cell box and click OK.
Excel displays the results of the formula using each of the substituted values.
Tip: If the table is set up with the data in a row instead of a column, enter the cell
reference for the input cell in the Row input cell box instead.
Create a two-input data table
1. Set up the table area. Make sure to include the formula in the upper-left cell and the
values for the first input cell in the left column and the values for the second input cell in
the top row.
Make sure the formula refers to the two input cells.
2. Select the table range that contains the formula and substitution values (both the row and
column values).
This should include blank cells below the formula and to the right of the values—this is
where the data table will go.
group, and select Data Table.
The Data Table dialog box appears. Since this is a two input table, two input cells need to
be entered.
4. Enter the cells to use for the Row input cell and the Column input cell and click OK.
Determine Potential Outcomes Using Scenarios:

A scenario is a set of input values that can be substituted in a worksheet to perform what-
if analysis. For example, create scenarios to show various interest rates, loan amounts, and
terms for a mortgage. Excel’s scenario manager allows creation and storage of different
scenarios in the same worksheet.
1. Create or open a worksheet that contains one or more formulas.
group, and select Scenario Manager from the list.
The Scenario Manager Dialogue box appears with the message “No Scenarios defined.
Choose Add to add scenarios.” Add a new scenario.
3. Click the Add button.
The Add Scenario dialog box appears.
4. Type a name for the scenario and press <Tab>.
The cursor moves to the Changing cells box. Select the cells that contain the values to
change.
Tip: To select multiple nonadjacent cells, hold down the <Ctrl> key as they are clicked.
5. Select the cells in the worksheet that contain the values to change, then click OK.
The Scenario Values dialog box appears. Enter desired values for the changing cells.
Tip: To make sure the original values aren’t lost for the changing cells, use the original cell
values in the first scenario created.
6. Enter values in each of the boxes. Click OK, or click Add to add another scenario.
The scenario is added. If OK is clicked, the scenario is listed in the Scenario Manager. If Add
is clicked, the Add Scenario dialog box appears to add another scenario to.
7. Repeat steps 4 – 6 to add a new scenario. Click OK. The Scenario Manager dialog box lists
each scenario created.
8. Click the Close button. The Scenario Manager closes.
To edit a scenario, select the scenario in the Scenario Manager dialog box and click the Edit
button.
Display a scenario:
Once scenarios are created in a worksheet, display the worksheet using the values from
those scenarios.
group, and select Scenario Manager from the menu.
The Scenario Manager dialog box appears.

2. Select the scenario that to display and click the Show button.
The worksheet’s values are changed to the values specified in the scenario.
Creating a Scenario Report:

A scenario summary report is a single compiled report that summarizes the results from
several scenarios. It’s easier to read than switching between different scenarios.
Create cell names:

The first step in creating a scenario summary report is to create names for the cells that
change.
1. Select the cells involved in the scenario and the labels to use to name them.
2. Click the Formulas tab on the Ribbon and click the Create from Selection button in
the Defined Names group.
The Create Names from Selection dialog box appears.

3. Select the option that describes where the labels are located in the selected cell range.
The labels that are in the selected cell range will be used as names.
4. Click OK.
The cells are named using the labels.
Create a Scenario Summary report:
Once at least two scenarios are created and have named cells, create a summary report.
group, and select Scenario Manager from the menu.
The Scenario Manager dialog box appears.
2. Click the Summary button.
The Scenario Summary dialog box appears.
3. Make sure the Scenario summary option is selected.

Next, specify the result cells. These are the cells that are affected by the changing cells.
Tip: Alternatively, select the “Scenario PivotTable report” option to create a report that
gives an instant what-if analysis of the scenarios.
4. Select the result cell range and click OK.
A new Scenario Summary worksheet is added to the workbook that contains the summary
report.
Use the Goal Seek Feature:

When the desired result of a single formula is attained, but not the value the formula
needs for the result, use the Goal Seek feature. For example, if a $1,200 monthly payment
is affordable, so how much of a loan can be taken out? When goal seeking, Excel plugs
different values into a cell until it finds one that works.
1. Open or create a workbook that contains the formulas to work with.
group, and select Goal Seek.
The Goal Seek dialog box appears.
3. Click the Set cell box, and click the cell in the worksheet that contains the formula to use.
4. Click the To value box and enter the value to change it to.
5. Click the By changing cell box, and click the cell to change to achieve the formula result.
This cell must be a cell that is referenced by the formula.
6. Click OK.
Excel calculates and displays the value needed to achieve the formula result desired.
7. Click OK to replace the original values or click Cancel to keep the original values.

Excel Chapter - 16

Uploaded by

Copyright:

Available Formats

Excel Chapter - 16

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Excel Chapter - 16

Uploaded by

Copyright:

Available Formats

PM2 - Management Reporting – IT Office Tools (MS Excel)

There are two types of data tables:

Create a one-input data table:

Make sure the formula refers to the input cell.

group, and select Data Table.

The Data Table dialog box appears.

Create a two-input data table

Make sure the formula refers to the two input cells.

group, and select Data Table.

Determine Potential Outcomes Using Scenarios:

1. Create or open a worksheet that contains one or more formulas.

group, and select Scenario Manager from the list.

3. Click the Add button.

The Add Scenario dialog box appears.

4. Type a name for the scenario and press <Tab>.

8. Click the Close button. The Scenario Manager closes.

group, and select Scenario Manager from the menu.

The Scenario Manager dialog box appears.

Creating a Scenario Report:

Create cell names:

the Defined Names group.

The Create Names from Selection dialog box appears.

The cells are named using the labels.

Create a Scenario Summary report:

group, and select Scenario Manager from the menu.

The Scenario Manager dialog box appears.

2. Click the Summary button.

The Scenario Summary dialog box appears.

3. Make sure the Scenario summary option is selected.

4. Select the result cell range and click OK.

Use the Goal Seek Feature:

1. Open or create a workbook that contains the formulas to work with.

group, and select Goal Seek.

The Goal Seek dialog box appears.

This cell must be a cell that is referenced by the formula.

You might also like