Excel Tutorial 10 Performing What-If Analyses

1
Excel Tutorial 10Performing What-If Analyses
2
Objectives

• Explore the principles of cost-volume-profit
  relationships
• Perform a basic what-if analysis
• Use Goal Seek to calculate a solution
• Create a one-variable data table
• Create a two-variable data table
• Create and apply different Excel scenarios

3
Objectives

• Generate a scenario summary report
• Generate a scenario PivotTable report
• Explore the principles of price elasticity
• Run Solver to calculate optimal solutions
• Create and apply constraints to a Solver model
• Save and load a Solver model

4
Understanding Cost-Volume-Profit Relationships

• Cost-volume-profit (CVP) analysis expresses the
  relationship between a companys expenses, its
  volume of business, and the resulting profit or
  net income
• Variable expenses change in proportion to the
  amount of business a company does
• A fixed expense is an expense that must be paid
  regardless of sales volume
• A variable expense is part variable and part
  fixed
• The point where revenue equals expenses is called
  the break-even point
• Break-even analysis

5
Understanding Cost-Volume-Profit Relationships
6
Working with What-If Analysis and Goal Seek

• To perform what-if analysis
• Change the value of a worksheet cell (the input
  cell)
• Observe its impact on one or more calculated
  cells (the result cells)
• To perform Goal Seek
• In the Data Tools group on the Data tab, click
  the What-If Analysis button, and then click Goal
  Seek
• In the Set cell box, select the result cell, and
  then, in the To value box, specify its value
  (goal)
• In the By changing cell box, specify the input
  cell
• Click the OK button. The value of the input cell
  changes to set the value of the result cell

7
Working with What-If Analysis and Goal Seek
8
Working with What-If Analysis and Goal Seek
9
Working with One-Variable Data Tables

• A data table organizes the results of several
  what-if analyses within a single table
• Insert a formula that references the input cell
  in the upper-left cell of the table
• Insert input values in either the first row or
  first column of the table
• For input values in the first row, insert
  formulas referencing result cells in the tables
  first column for input values in the first
  column, insert formulas referencing result cells
  in the tables first row

10
Working with One-Variable Data Tables

• Select the table (excluding any row or column
  headings). In the Data Tools group on the Data
  tab, click the What-If Analysis button, and then
  click Data Table
• If the input values are in the first row, enter
  the cell reference to the input cell in the Row
  input cell box if the input values are in the
  first column, enter the cell reference to the
  input cell in the Column input cell box
• Click the OK button

11
Working with One-Variable Data Tables
12
Working with Two-Variable Data Tables

• A two-variable data table uses two input cells,
  but unlike a one variable data table, only the
  value of a single result cell can be displayed
• Insert a formula that references the result cell
  in the upper-left cell of the table
• Insert input values in the first row and first
  column of the table
• Select the table (excluding any row or column
  headings)
• In the Data Tools group on the Data tab, click
  the What-If Analysis button, and then click Data
  Table

13
Working with Two-Variable Data Tables

• Enter the cell reference corresponding to the
  input values in the first row in the Row input
  cell box enter the cell reference to the input
  values in the first column in the Column input
  cell box
• Click the OK button

14
Working with Two-Variable Data Tables
15
Using Scenario Manager

• Scenario Manager enables you to create as many
  scenarios as you want, easily switching between
  the different scenarios to display the results of
  several what-if analyses
• Enter the data values in the worksheet for the
  scenario
• In the Data Tools group on the Data tab, click
  the What-If Analysis button, and then click
  Scenario Manager

16
Using Scenario Manager

• Click the Add button in the Scenario Manager
  dialog box
• In the Scenario name box, type a name for the
  scenario
• In the Changing cells box, specify the input or
  changing cells
• Click the OK button
• In the Scenario Values dialog box, specify values
  for each of the input cells, clicking the Add
  button after each
• Click the OK button

17
Using Scenario Manager
18
Creating a Scenario Summary Report

• A scenario summary report lists the values for
  the changing cells and result cells under each
  scenario
• In the Data Tools group on the Data tab, click
  the What-If Analysis button, and then click
  Scenario Manager
• Click the Summary button
• Click the Scenario summary option button to
  create a scenario summary report (or click the
  Scenario PivotTable report option to create a
  PivotTable describing the scenarios)
• Select the results cells to display in the report
• Click the OK button

19
Creating a Scenario Summary Report
20
Creating a Scenario PivotTable Report

• A Scenario PivotTable report displays the results
  from each scenario as a pivot field in a
  PivotTable
• In the Data Tools group on the Data tab, click
  the What-If Analysis button, and then click
  Scenario Manager
• Click the Summary button, and then click the
  Scenario PivotTable report option button
• Click the OK button

21
Creating a Scenario PivotTable Report
22
Understanding Price Elasticity of Demand
23
Understanding Price Elasticity of Demand

• The effect that price has on demand is called the
  price elasticity of demand
• Relatively inelastic
• Perfectly inelastic
• Relatively elastic
• Perfectly elastic
• Unit elastic

24
Finding an Optimal Solution Using Solver

• Solver is a program that searches for the optimal
  solution of a problem involving several variables
• Check whether Solver is already installed and
  activated. If it is, Solver will appear in
  Analysis group on the Data tab
• Click the Office Button, and then click the Excel
  Options button
• Click Add-Ins in the left pane, and then click
  the arrow next to the Manage box and click Excel
  Add-ins
• Click the Go button to open the Add-Ins dialog
  box
• Click the Solver Add-in check box, and then click
  the OK button. Follow the remaining prompts to
  install Solver, if it is not already installed

25
Setting Solver Parameters

• In the Analysis group on the Data tab, click the
  Solver button
• In the Set Target Cell box, specify the target
  cell
• Click the Max, Min, or Value of option buttons to
  maximize, minimize, or set the target cell to a
  specified value
• In the By Changing Cells input box, specify the
  changing cells

26
Setting Constraints on the Solver Solution

• In the Solver Parameters dialog box, click the
  Add button
• Enter the cell reference of the cell or cells
  containing the constraint
• Specify the nature of the constraint (lt, , gt,
  int, or bin)
• Enter the constraint value in the Constraint box
• Click the OK button to add the constraint and
  return to the Solver dialog box
• Repeat for each constraint you want to add

27
Setting Constraints on the Solver Solution
28
Saving and Loading a Solver Model

• To save a Solver model
• Open the Solver dialog box
• Click the Options button and then click the Save
  Model button
• Select the range to contain the parameters of the
  Solver model, and then click the OK button
• To load a Solver model
• Open the Solver dialog box
• Click the Options button and then click the Load
  Model button
• Select the range within the current worksheet
  containing the Solver parameters, and then click
  the OK button

29
Saving and Loading a Solver Model
30
Saving and Loading a Solver Model