Chapter 22 Using Data Analysis Tools of Data Tables, Goal Seek, Solver, and Scenario

1 / 49

# Chapter 22 Using Data Analysis Tools of Data Tables, Goal Seek, Solver, and Scenario - PowerPoint PPT Presentation

Chapter 22 Using Data Analysis Tools of Data Tables, Goal Seek, Solver, and Scenario. Excel 2003, Volume 2 by Karen J. Jolly. Outcomes. Create one- and two-variable data tables. Use the analysis business tools. Goal Seek Solver Scenario. About Data Analysis Tools.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Chapter 22 Using Data Analysis Tools of Data Tables, Goal Seek, Solver, and Scenario' - tareq

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Chapter 22Using Data Analysis Tools of Data Tables, Goal Seek, Solver, and Scenario

Excel 2003, Volume 2

by Karen J. Jolly

Outcomes
• Create one- and two-variable data tables.
• Use the analysis business tools.
• Goal Seek
• Solver
• Scenario

• Data tables provide a quick comparison of business problems when one or two variables may change.
• Goal Seek, optimizes a goal and provides a solution when one variable changes.
• Solver provides solutions when more than variable may be changed.
• Scenarios create several different solutions for a complex business problem.

What-If Analysis
• What-If analysis allows businesses to analyze their financial picture to forecast and make other financial decisions.
• “What if the interest rate decreases?”
• “What if the markup rate increases by 1%?”
• “What if the travel budget is decreased by 10%?”
• “What if we increase production by 15%?”

Basics of What-If Analysis
• Analysis quickly completed with Excel.
• Performed by changing values in input cells.
• Dependent cell
• usually contains a formula.
• changes when input data changes.
• Model worksheet contains the what-if analysis.

Procedures for Planning Analysis
• Define the problem
• Input values
• Dependent cells
• Results
• Complete the analysis

Define the Problem
• The problem must be clearly defined to begin the what-if analysis.
• Scenario: a couple want to purchase a home and cannot have payments larger than \$1,200 per month.
• Problem definition: what is the maximum purchase price they can pay for a house?

Input Values
• Determine the data input values:
• Amount of the loan (principal)
• Interest Rate
• Length of loan

Guess

Dependent Cells
• Determine which cells contain formulas – dependent cells.

Input cells

Dependent cell

Results
• What are the results given the input data?
• Monthly payment =\$1,945.79
• Do the results match the requirements?
• No, payment must be max \$1,200
• If not, change the input data to obtain the needed results.
• What happens if interest rate changes?
• What happens if purchase price changes?

Complete the Analysis
• Enter variables and determine the result.
• Make changes in data until desired result is obtained.
• Analysis completion will differ depending on which what-if tool is used.

Data Tables

A data tableautomates data analysis and organizes the results when one or two variables change.

One-Variable Data Table
• A one-variable data table is used to evaluate financial information for decision making.
• Only one variable is changing:
• In a business, what happens when net sales change?
• How do interest rates affect the monthly payment on a car loan?

Use a One-Variable Data Table
• Define the Problem:
• What effect will interest rates have on loan payments?
• Create the worksheet.

Enter formula to determine amount of paymentUse a One-Variable Data Table

Enter input values to tested

Use a One-Variable Data Table
• From the Data menu select Table.
• Determine if input values have been entered as columns or as rows.

Use a One-Variable Data Table—Cont.
• Enter the input cell – the cell in the original worksheet that contains the variable (interest rate).
• Enter input cell as absolute value.

Completed Data Table

Two-Variable Data Table
• Makes financial comparisons when two variables change.
• Uses one formula to evaluate two sets of variables.
• Example: What happens if interest rate and length of loan change?

Use a Two-Variable Data Table
• Complete the worksheet.

Use a Two-Variable Data Table

Place the formula between the two variables

Create a data table with both variables

Use a Two-Variable Data Table
• In Row input cell, input cell that contains the original value of the principal.
• In Column input cell, input cell that contains the original value of the interest rate.

Completed Two-Variable Data Table

Goal Seek
• Only one variable will change.
• Maximizes the results within the other financial constraints.
• Example: maximize the cost of a remodeling project where payments do not exceed \$1200 per month.

Use Goal Seek
• Complete the worksheet.
• Select the cell containing the formula.

Use Goal Seek
• From the Tools menu select Goal Seek

Use Goal Seek

Set cell containsthe formula

To value is the valueyou want the set cellto be (max payment)

The cell to changeto obtain desiredresult (principal)

Completed Goal Seek

Maximum amount of loan

Solver
• What-if solutions often affect more than one factor.
• Example: how to change production quantity given multiple variables; i.e. multiple product lines, available resources.
• Solver determines optimum value of data by changing other data factors.
• Constraints can be used to limit how values change.

Prepare for Solver

Use Solver
• From the Tools menu select Solver.

Use Solver
• Determine target cell – the limiting conditions for the problem.
• Determine if the target cell should be maximized, minimized or equal to a value.

Solver Parameters Dialog Box

Use Solver
• Enter the changing (or adjusting) cells that contain variables that will change the results.

Use Solver
• Determine the constraintsthat will limit the changing values.
• Cells that change are called changing or adjustablecells.

Use Solver

Completed Solver Parameter

Use Solver
• Click Solve.

Solver has founda solution

Keep solution or restore original values

Solver Results Dialog Box

Solver Results

Changingcells

Scenario Manager
• Use Scenario Manager to solve complex business solutions when more than one factor can be used.
• A scenario is a set of values used to forecast results of a what-if model.
• A what-if model is a worksheet that contains different sets of values for the variables.

Scenario Manager
• Scenarios are useful when data is uncertain.
• A scenario may contain up to 32 variables.
• Use Scenario Manager to determine best case and worst case scenarios.
• Scenario Manager finds solutions to complex business problems.
• Use named cells to help analyze results.

Use Scenario Manager
• Create a worksheet with known information.

Use Scenario Manager

Create Scenarios

Use Scenario Manager
• From the Tools menu select Scenarios.

Scenario Manager Dialog Box

Use Scenario Manager
• Name the scenario.
• Identify changing cells on original worksheet.
• Click OK.

Use Scenario Manager
• Edit the amounts for that specific scenario.
• For each scenario enter the variables that apply.
• Continue to add scenarios until each has been set up.

Scenario Values Dialog Box

Use Scenario Manager
• Select a scenario.
• Click Show.
• Variables in the current worksheet are replaced by those in the scenario.

Scenario Summary Report
• A summary of the results of all scenarios can be displayed in a separate worksheet.
• Access Scenario Summary dialog box.