Exploring Microsoft Excel 2016 Series Editor Mary Anne

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 6 What-If Analysis Using Decision-Making Tools Copyright © 2017 Pearson Education, Inc.

Objectives • Create and Maintain Range Names • Create a One-Variable Data Table • Create a Two-Variable Data Table • Determine Optimal Input Values Using Goal Seek • Use Scenario Manager • Load the Solver Add-In • Optimize Results with Solver Copyright © 2017 Pearson Education, Inc.

Objective 1: Create and Maintain Range Names Skills: Create a Range Name Edit or Delete a Range Name Use Range Names in Formulas Insert a List of Range Names Copyright © 2017 Pearson Education, Inc.

Create and Maintain Range Names Type range name Select cell Copyright © 2017 Pearson Education, Inc.

Create and Maintain Range Names Select range name Edit or delete Copyright © 2017 Pearson Education, Inc.

Create and Maintain Range Names Double-click Tax_Rate Type = P Copyright © 2017 Pearson Education, Inc.

Objective 2: Create a One-Variable Data Table Skills: Set Up One-Variable Substitution Values Add Formulas to a One-Variable Data Table Calculate Results Format a One-Variable Data Table Copyright © 2017 Pearson Education, Inc.

Create a One-Variable Data Table Click Fill First substitution value 5% in D 4 Increment value Select columns Stop value Copyright © 2017 Pearson Education, Inc.

Create a One-Variable Data Table Click What-If Analysis Drag down and right from starting cell Select column input cell Copyright © 2017 Pearson Education, Inc.

Create a One-Variable Data Table Cryptic column headings Copyright © 2017 Pearson Education, Inc.

Create a One-Variable Data Table Meaningful column headings Type column text Copyright © 2017 Pearson Education, Inc.

Objective 3: Create a Two-Variable Data Table Skills: Set Up Two-Variable Substitution Values Add a Formula to the Data Table Calculate Results for a Two-Variable Data Table Copyright © 2017 Pearson Education, Inc.

Create a Two-Variable Data Table Cell reference =B 12 Varying monthly payments Varying interest rates Copyright © 2017 Pearson Education, Inc.

Create a Two-Variable Data Table Copyright © 2017 Pearson Education, Inc.

Objective 4: Determine Optimal Input Values Using Goal Seek Skills: Determine Optimal Input Values Using Goal Seek Copyright © 2017 Pearson Education, Inc.

Determine Optimal Input Values Using Goal Seek Optimize Monthly Payment in B 12 Optimize to 800 By changing Monthly Payment in B 3 Copyright © 2017 Pearson Education, Inc.

Objective 5: Use Scenario Manager Skills: Create Scenarios Add and Edit Scenarios View Scenarios Generate a Scenario Summary Report Copyright © 2017 Pearson Education, Inc.

Use Scenario Manager Click Add Click Edit Enter Changing cells Enter values Click Show Copyright © 2017 Pearson Education, Inc.

Use Scenario Manager Result cells New worksheet Copyright © 2017 Pearson Education, Inc.

Objective 6: Load the Solver Add-In Skills: Load the Solver Add-in Copyright © 2017 Pearson Education, Inc.

Load the Solver Add-In Solver Add-in added to Ribbon Select Solver Add-in Copyright © 2017 Pearson Education, Inc.

Objective 7: Optimize Results with Solver Skills: Identify the Objective Cell and Changing Cells Define Constraints Create a Solver Report Configure Solver Save and Restore a Solver Model Copyright © 2017 Pearson Education, Inc.

Optimize Results with Solver • Solver’s three parameters: §Objective cell—specifies the cell that contains the formula to be optimized §Changing variable cells—contain variables whose values change within the constraints §Constraints—specify the restrictions or limitations imposed on the model Copyright © 2017 Pearson Education, Inc.

Optimize Results with Solver Click Add Minimize Changing cells Copyright © 2017 Pearson Education, Inc. Enter constraint

Optimize Results with Solver Keep or Restore? Select report Copyright © 2017 Pearson Education, Inc.

Optimize Results with Solver First section Second section Third section New worksheet Copyright © 2017 Pearson Education, Inc.

Summary Excel What-If analysis tools: • Variable Data Tables • One-Variable—contains different values for one variable • Two-Variable—contains different values for two variables • Goal Seek—specify a desired result without knowing what input value achieves that goal • Scenario Manager—define and manage scenarios to compare effects on calculated results • Solver—searches for the best or optimum solution to a problem by manipulating values for several variables within imposed constraints. Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

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 © 2017 Pearson Education, Inc.