Exploring Microsoft Office Excel 2010 INSERT BOOK COVER

Exploring Microsoft Office Excel 2010 INSERT BOOK COVER by Robert Grauer, Keith Mulbery, and Mary Anne Poatsy Chapter 6 What-If Analysis Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 1

Objectives • • Create a one-variable data table Create a two-variable data table Identify an input value with Goal Seek Use Scenario Manager Generate scenario summary reports Load the Solver Add-in Optimize results with Solver Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 2

Create a One-Variable Data Table • What-if analysis ─ allows you to see how changing variables impacts calculated results • A variable ─ a value that can be changed to see how it affects other values • A one-variable data table ─ a data analysis tool that provides various results based on changing one variable • A substitution value ─ replaces the original value of a variable in a data table Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 3

Create a One-Variable Data Table 1. List substitution values in the left column or first row 2. Enter formulas in the first row or left column (whichever was not used above) 3. Create the one-variable data table 4. Format the results of the data table 5. Create custom number formats to disguise the formulas as headings Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 4

Create a One-Variable Data Table To complete a one-variable data table: 1. Select entire table starting in the blank cell in the top-left corner 2. Click What-If Analysis in the Data Tools group on the Data tab and select Data Table 3. Enter address of the cell to be changed in the Data Table dialog box 4. Click OK Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 5

Create a One-Variable Data Table Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 6

Create a Two-Variable Data Table • A two-variable data table ─ a data analysis tool that provides results based on changing two variables • Creating a two-variable data table ─ similar to creating a one variable data table; however, you are limited to comparing one result. • Recommendations include: – Use the top row for one variable’s substitution values – Use the first column for the other variable’s values – Apply a custom number format to the formula cell in the top-left cell Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 7

Create a Two-Variable Data Table Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 8

Identify an Input Value with Goal Seek • Goal Seek ─ a tool when you know the desired end result but not the value needed to meet the goal • Enables you to work backwards to solve a problem • Excel can enter the input value in the variable cell Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 9

Identify an Input Value with Goal Seek To use Goal Seek: 1. Click What-If Analysis in the Data Tools group on the Data tab 2. Select Goal Seek to open the Goal Seek dialog box 3. Enter the cell reference for the cell to be optimized in the Set cell box 4. Enter the result you want to achieve (such as $300) in the To value box 5. Enter the cell reference that contains the value of the variable to adjust (such as cost of car) in the By changing cell box 6. Click OK 7. When an answer appears, click OK to accept the change or Cancel to return to the original data Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 10

Identify an Input Value with Goal Seek Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 11

Use Scenario Manager • Scenarios take detailed sets of input values and determine possible results • Scenario Manager enables you to define and manage up to 32 scenarios • Scenarios are maintained separately for each worksheet in a workbook Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 12

Use Scenario Manager To create a scenario: 1. Click What-If Analysis in the Data Tools group on the Data tab 2. Select Scenario Manager to open the Scenario Manager dialog box 3. Click Add to open the Add Scenario dialog box 4. Enter a meaningful name in the Scenario name box 5. Enter the input cells for the scenarios in the Changing cells box 6. Click in the Comment box to display you name and the date the scenario is created 7. Click OK to open the Scenarios Values dialog box 8. Click Add to add another scenario and specify its values OR click OK to return to the Scenario Manager dialog box Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 13

Use Scenario Manager Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 14

Use Scenario Manager Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 15

Use Scenario Manager Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 16

Use Scenario Manager To view your scenarios: 1. Click What-If analysis in the Data Tools group on the Data tab 2. Select Scenario Manager 3. Select the name of the Scenario you want to view in the Scenarios list 4. Click Show Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 17

Generate Scenario Summary Reports • Scenario summary report ─ a worksheet that compares the scenario results • Do not automatically update Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 18

Generate Scenario Summary Reports To create a scenario summary report: 1. Open the Scenario Manager dialog box 2. Click Summary to open the Scenario Summary dialog box 3. Click Scenario Summary or Scenario Pivot. Table report and enter the reference for the cell(s) whose values change in the scenarios in the Result cells box 4. Click OK Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 19

Generate Scenario Summary Reports Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 20

Generate Scenario Summary Reports Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 21

Load the Solver Add-in • Solver ─ a separate program that must be installed or added in to Excel • Once loaded ─ appears in the Analysis group on the Data tab • Solver ─ manipulates variables based on constraints to find the optimal solution to a problem Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 22

Load the Solver Add-in To load Solver: 1. Click the File tab, and then select Options 2. Click Add-Ins 3. Click the Manage arrow, select Excel Add-ins, and then click Go to open the Add-Ins dialog box 4. Click the Solver Add-in check box in the Add-Ins available list and click OK Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 23

Load the Solver Add-in Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 24

Optimize Results With Solver • Solver requires three parameters: – Objective cell contains a formula-based value that you want to optimize that relates directly or indirectly to the changing cells and constraints – Changing cells are the cells whose values are adjusted until the constraints are satisfied – Constraints specify the restrictions Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 25

Optimize Results With Solver To specify the objective and changing cells: 1. Click Solver in the Analysis group on the Data tab to open the Solver Parameters dialog box 2. Enter the cell containing the formula for which you want to optimize its value in the Set Objective box 3. Click an option in the To section to specify what type of value you need to find for the target cell (such as Max, Min, or Value Of with a specified value) 4. Enter the cell references that contain variables in the By Changing Variable Cells box Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 26

Optimize Results With Solver Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 27

Optimize Results With Solver To specify the constraints: 1. Click Add to the right of the Subject to the Constraints list to open the Add Constraint dialog box 2. Enter the cell reference, the operator to test the cell references, and the constraint the cell needs to match (such as the down payment must be less than or equal to $7, 000) 3. Click OK to add the constraint and return to the Solver Parameters dialog box, or click Add to add the constraint and create another constraint Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 28

Optimize Results With Solver Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 29

Optimize Results With Solver • Solver uses an iterative process to find an optimal solution • A summary report can be generated if a solution is found • The report specifies: – Binding Constraints which are rules that Solver enforced to reach the target value – Nonbinding Constraints do not restrict the target value found Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 30

Summary • In this chapter, you learned that what-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. You can: – Create a one- or two- variable data table. – Use Goal Seek. – Use Scenario Manager. – Generate scenario summary reports. – Optimize results with Solver. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 31

Questions Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 32

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 33