Lesson 16 Using WhatIf Analysis

1

• Lesson 16 Using What-If Analysis

2
Lesson Objectives

• Create a scenario.
• Set highlight cells rules conditional formatting.
• Manage scenarios.
• Forecast with a trendline.
• Use Goal Seek.
• Use Solver.

3
Enabling a Macro

• A macro is a Visual Basic routine saved in a
  macro-enabled workbook.
• A macro performs a task in the workbook such as
  inserting labels or values or formatting
  row/columns.
• A macro-enabled workbook has an xlsm filename
  extension.

4
Enabling a Macro
The Trust Center in the Excel Options dialog box
has options for how macro-enabled workbooks are
opened.
5
Enabling a Macro
You must enable macros in order to use them.
6
Creating a Scenario

• A scenario is a what-if analysis tool.
• A scenario is a set of values saved with the
  workbook.
• You can save many scenarios with a workbook.
• You can show one scenario at a time.

7
The Add Scenario Dialog Box
The changing cells range can use cell addresses
or a range name.
8
Changing Scenario Values
The values use General format in this dialog box.
9
Setting Highlight Cells Rules

• Highlight cells rules are conditional formatting.
• They apply formatting when cell contents meet
  conditions set with relational or conditional
  operators.
• The rules can be used with values or labels.
• You can apply more than one rule to the same
  range.

10
Setting Highlight Cells Rules
Select the cell range for the conditional
formatting. Then choose the rule.
11
Setting Highlight Cells Rules
Choose More Rules when you want to edit the rule
or its formatting.
12
Managing Scenarios
Scenarios can be

• Edited
• Displayed
• Included in a summary report

13
Showing a Scenario
Choose the scenario name and click Show to see
the related values in the worksheet.
14
Printing a Scenario Summary Report
A scenario summary report shows the results and
changing cells for every scenario in a workbook.
15
Printing a Scenario Summary Report
A scenario summary report is formatted as an
outline. It can be further formatted like most
worksheets.
16
Saving a Macro-Free Workbook
You can save a workbook that has a macro as a
macro-free workbook.
17
Forecasting with a Trendline

• A trendline is a line in a chart that points out
  or predicts general tendencies.
• Trendlines are well-suited to column charts.
• Trendlines can project forward or backward.

18
Forecasting with a Trendline
19
Formatting a Trendline
20
Forecasting with a Trendline
21
Using Goal Seek
Goal Seek backsolves a problem. It adjusts one
cell with a value to reach a target value in
another cell.
22
Using Goal Seek
You can save the Goal Seek solution as a scenario.
23
Using Solver

• Solver is an Excel add-in.
• An add-in is a feature or command that supplies
  some type of enhanced capability.
• Solver backsolves the value for a cell with a
  formula.

24
Using Solver
A Solver problem has these components

• A target cell with a formula that you want to
  result in a particular value.
• Adjustable cells that relate to the target cell
  and that Solver changes to produce the desired
  result.
• Limitations, or constraints, placed on the target
  cell, adjustable cells, or other cells related to
  the target cell.

25
Using Solver
The value in cell F14 will be changed to
1,500,000 by adjusting the values in cells B6E6.
26
Using Solver with Constraints
27
Using a Thumbnail Preview
Click the Properties View and Options button in
the Document Information Panel to open the
Properties dialog box.
28
Summary

• What-if analysis tests values in a worksheet to
  predict future results.
• A scenario is a saved set of values for specific
  cells, saved with a name in the workbook.

29
Summary

• You can add multiple scenarios to a workbook.
• Scenarios can be edited, displayed, or deleted
  when necessary.
• A scenario summary report is an outline about
  each scenario in the workbook.

30
Summary

• Highlight cells rules are conditional formatting
  for cells to display a chosen format when a
  requirement is met.
• Highlight cells rules use comparison and
  relational operators.
• Two rules can be true at the same time.

31
Summary

• Business forecasting can be accomplished with a
  trendline added to a chart.
• A trendline illustrates and predicts general
  tendencies of values.
• In backsolving, you specify the desired results
  of a formula and adjust values to reach those
  results.

32
Summary

• Goal Seek adjusts a single cell value to reach a
  desired outcome in a formula.
• Solver is a what-if analysis tool. Its parameters
  are a target cell, adjustable cells, and
  constraints or limitations on the target or
  adjustable cells.
• Document properties include an optional thumbnail
  preview that is visible in the Open dialog box.