excel

ghtyui52000

yo2016_e10_ppt.pptx

Home >Computer Science homework help >excel

Your Office: Microsoft Excel 2016

First Edition

Chapter 10

Data Tables, Scenario Manager, and Solver

If this PowerPoint presentation contains mathematical equations, you may need to check that your computer has the following installed:

1) MathType Plugin

2) Math Player (free versions available)

3) NVDA Reader (free versions available)

Learning Objectives (1 of 2)

10.1 Perform break-even analysis

10.2 Analyze variables in formulas through the use of data tables

10.3 Use Goal Seek to determine values needed to achieve an objective

10.4 Use the Scenario Manager to create scenarios

The objectives for this chapter are:

Perform break-even analysis

Analyze variables in formulas through the use of data tables

Use Goal Seek to determine values needed to achieve an objective

Use the Scenario Manager to create scenarios

Additional objectives are listed on the next slide.

Learning Objectives (2 of 2)

10.5 Create scenario reports

10.6 Understand the use of the Solver add-in

10.7 Solve complex problems using Solver

10.8 Generate and interpret Solver answer reports

The objectives for this chapter are:

Create scenario reports

Understand the use of the Solver add-in

Solve complex problems using Solver

Generate and interpret Solver answer reports

Perform Break-Even Analysis (1 of 5)

Cost-volume-profit—studies of how cost and sales volume are related

Break-even analysis—calculates the break-even point in sales volume or dollars

Break-even point—sales level at which revenue equals total costs

Cost-volume-profit (CVP) analysis studies of how cost and sales volume are related and the effect their relationship has on profit.

Breakeven analysis calculates the break-even point in sales volume or dollars, estimate profit or loss at any level of sales volume and helps in setting prices.

The break-even point is the sales level at which revenue equals total costs; in other words, there is neither a profit nor loss.

Perform Break-Even Analysis (2 of 5)

Calculating the break-even point requires:

Fixed costs—are expenses that never change

Variable costs—change based on how many products are sold or services are rendered

Mixed costs—variable and fixed components

Calculating the break-even point requires the consideration of the fixed, variable, and mixed costs.

Fixed costs are expenses that never change regardless of how much product is sold or how many services are rendered.

Variable costs do change according to how many products are sold or services are rendered.

Mixed costs are costs that contain a variable component and a fixed component.

Perform Break-Even Analysis (3 of 5)

This slide shows a break-event analysis chart based on profit and sales volume. This means that sales of over 550 items results in a profit. The chart also shows that greater sales results in greater profit.

Perform Break-Even Analysis (4 of 5)

Cell	Contents
D6	=D4*D5
D13	=SUM(D9:D12)
D15	=D6*C15
D16	=C16*D4
D17	=D15+D16
D18	=D13+D17
D19	=D6-D18

One way to calculate the break-even point is to set up the formulas and then perform trial-and-error by trying various amounts until the value of the cell containing the break-even formula displays zero. Look at the table to see the formulas in the indicted cells. By trying various values in cell D4 and watching cell D19, the break-even point can be determined. The example in the text tries the following values with the following results: 50 yielding -$1,18.50, 60 yielding $-393.00, and finally 64 yielding $17.20. This makes the break-even point 64 units.

Perform Break-Even Analysis (5 of 5)

A more efficient way to vary the values of cell D4 is to insert a Scroll Bar (Form Control) and set its properties to a minimum values, a maximum values, an incremental value, and assign the values of the scroll bar to cell D4. The details for doing this are explained in the textbook.

Analyze Variables in Formulas Through the Use of Data Tables (1 of 3)

Excel what-if analysis tools

Data tables

One-variable

Two-variables

Goal Seek

Scenario Manager

Excel contains three types of what-if analysis tools:

Data Tables

One-variable

Two-variables

Goal Seek

Scenario Manager

A data table takes sets of input values, determines possible results, and displays all the results in a table on a worksheet. Because data tables focus on only one or two variables, the results are easy to read and share in tabular form.

Analyze Variables in Formulas Through the Use of Data Tables (2 of 3)

A one-variable data table has input values that are listed either down a column or across a row. A one-variable data table helps analyze how different values of one variable in one or more formulas will change the results of those formulas. The formulas that are used in a one-variable data table must refer to only a single input cell. In the worksheet shown in the slide, column D contains interest rates and cell E2 contains =B6, but by using Format Cells, the cell displays Monthly Payment. Now you:

Select the range D2:E9 as the data for the data table.

Click What-If Analysis and click Data Table, in the Forecast group the Data tab, to open the Data Table dialog box.

Click inside the Column input cell box, click cell B4.

Analyze Variables in Formulas Through the Use of Data Tables (3 of 3)

A two-variable data table uses input values that are listed down a column and across a row. A two-variable data table helps to analyze how changing the value of two variables affects the results of a formula. The slide shows a two-variable loan table. As we can see the rows indicate the various interest rates, which the columns indicate the various loan amounts. The interior cells of the table show the monthly payments based on these two variables. Refer to your textbook for the discussion on how to construct a two-variable data table.

Use Goal Seek to Determine Values Needed to Achieve an Objective (1 of 3)

Goal Seek—finds input values needed to achieve a goal or objective

Goal Seek uses:

Variable cell

Target value

Location of the changing input value

Goal Seek is another scenario tool that maximizes Excel’s cell-referencing capabilities to find the input values needed to achieve a goal or objective.

To use Goal Seek, you:

Select the variable cell, which contains the formula that will return the result that is sought.

Indicate the target value you want the formula to return.

Select the location of the input value that Excel changes to reach the target.

Use Goal Seek to Determine Values Needed to Achieve an Objective (2 of 3)

In this example, we are going to use Goal Seek to determine the number of boxes of golf balls Red Bluff needs to sell to meet its sales goal.

After selecting cell E5, click What-If Analysis and click Goal Seek to open the Goal Seek dialog box. As shown in the slide, the three Goal Seek arguments are entered, where a goal of $25,000 is sought.

Use Goal Seek to Determine Values Needed to Achieve an Objective (3 of 3)

As shown in the slide, 781 golf ball boxes sold at $32 will reach the goal of $25,000.

Use Scenario Manager to Create Scenarios (1 of 2)

Cell	Contents
D6	=SUM(D4:D5)
D13	=SUM(D9:D12)
D15	=D6*C15
D16	=C13+D15
D17	=D6-D16

Scenario Manager enables you to build a what-if analysis model that includes variable cells linked by one or more formulas or functions. By running scenarios, you have the ability to compare multiple variables and their combined effects on the various calculated outcomes. Scenario Manager can be used to determine the common best-case, worst-case, and most likely scenarios. The key to creating the various scenarios is identifying the various data cells whose values can differ in each scenario. You can then select these cells—known as changing cells—in the worksheet before you open the Scenario Manager dialog box. In our example, we will find the Most Likely scenario with the changing cells being D4:D5, which represent the Retail Sales and Golf Lessons and Fees.

Look at the table to see the formulas in the indicted cells. To use the Scenario Manager, click What-If Analysis and click Scenario Manager to open the Scenario Manager dialog box. To add a scenario, click Add.

Use Scenario Manager to Create Scenarios (2 of 2)

In the Add Scenario dialog box, we name the scenario Most Likely Scenario, enter the changing cells as D4:D5, and click OK. In the Scenario Values dialog box, enter the indicated values, click OK, and click Add.

Create Scenario Reports

After a scenario has been created, you can view the results by using the Show button at the bottom of the Scenario Manager dialog box. To view multiple scenarios, you create a Scenario Summary report, which lists the results of scenarios side by side, allowing the outcomes to be easily compared. Clicking the Summary button opens the Scenario Summary dialog box where you can choose the type of report you would like to create.

This slide show a Scenario Summary report for Best-case and Worst-case scenarios.

Understand the Use of the Solver Add-In (1 of 2)

Solver—optimizes a problem:

By manipulating the values of selected variables

But bound by constraints

Solver can be used to find:

Highest value

Lowest value

Exact value

Solver is used to optimize a problem by manipulating the values for selected variables, which are bound by constraints. A constraint is a rule that is established when formulating a Solver model. Solver can be used to find the highest, lowest, or exact value for a specific outcome by adjusting values for selected variables.

Solver is an add-in, which means that is has to be installed before in can be used.

Understand the Use of the Solver Add-In (2 of 2)

Solver parameters:

Objective cell—contains the formula that creates a value that is to be optimized

Variable cells—changed by Solver as it finds a solution

Constraints—the rules or restrictions that your variable cells must follow

Solver requires thee parameters:

The objective cell that contains the formula that creates a value that is to be optimized—maximized, minimized, or set to a specific value.

Variable cells that are changed by Solver as it finds a solution.

Constraints that are the rules or restrictions that your variable cells must follow when the Solver performs its analysis.

Solve Complex Problems Using Solver

Click Solver in the Analyze group on the Data tab top open the Solver Parameters dialog box. Enter the objective cell, select Max, Min, the Value of, and enter the changing cells. To add constraints, click Add to display the Add Constraint dialog box. After entering a constraint, click Add. Continue this process until all constraints are added. In the slide, we see that five constraints have been added. The final step is selecting a solving method.

There are three methods from which to choose:

Simplex LP

Evolutionary

GRG Nonlinear

See the textbook for a discussion of the these methods. For our example, we will select GRG Nonlinear, check some settings, and then click Solve.

Generate and Interpret Solver Answer Reports (1 of 3)

In the Solver Results dialog box, verify that the Keep Solver Solution is selected, select Answer, and click OK. When running Solver, an answer report is created in a new worksheet and named “Answer Report.”

Generate and Interpret Solver Answer Reports (2 of 3)

The Solver answer report is divided into four sections: report details, objective cell information, variable cell information, and constraints information. The first three sections of the Answer Report you just created are shown on this slide.

The first section displays information about the Solver report.

The second section reports information about the objective cells: cell references; cell names; whether you searched for the minimum, maximum, or a specific value; and the original and final objective cell values.

The third section displays information about the variable cells: cell references, variable cell names, original cell values, and final cell values.

Generate and Interpret Solver Answer Reports (3 of 3)

The fourth section displays information about the constraints you entered— ell references, descriptions, new cell values, formulas, status, and slack—for each constraint.

Summary (1 of 2)

Analysis concepts:

Cost-volume-profit—studies how cost and sales volume are related

Break-even—calculates the break-even point in sales volume or dollars

In this chapter, we were introduced to several analysis concepts and tools.

Analysis concepts:

Cost-volume-profit (CVP) analysis is the study of how cost and sales volume are related and the effect their relationship has on profit.

Break-even analysis to calculate the break-even point in sales volume or dollars, estimate profit or loss at any level of sales volume.

Summary (2 of 2)

Analysis tools:

One- and two-variable data tables—take sets of input values and determine possible results

Goal Seek—maximizes Excel’s cell-referencing capabilities to find the input values needed to achieve a goal or objective

Scenario manager—builds a what-if analysis model using variable cells linked by formulas or functions

Solver—optimizes a problem by manipulating the values for several variables but limited by constraints

Analysis tools:

One- and two-variable data tables which take sets of input values, determine possible results, and display all the results in one table on one worksheet.

Goal Seek is scenario tool that maximizes Excel’s cell-referencing capabilities and enables you to find the input values needed to achieve a goal or objective.

Scenario manager allows you to build a what-if analysis model that includes variable cells linked by one or more formulas or functions.

Solver helps to optimize a problem by manipulating the values for several variables but limited by constraints.

Questions?

As you complete Chapter 10, be sure you ask questions.

ISBN-13: 978-0-13-447956-9 ISBN-10: 0-13-447956-4

9 780 13 4 4 79569

9 0 0 0 0

PROVEN RESULTS For over 10 years, instructors and students have

reported better grades through increased engagement and real-time insights into progress.

ENGAGING EXPERIENCES MyLab is designed to reach students in a personal way. Engaging learning and practice opportunities lead to assessments that create a personalized study plan.

A TRUSTED PARTNERSHIP With millions of students registered annually, MyLab is the most e!ective and reliable learning solution available today.

Pearson’s MyLab™

www.pearsonhighered.com

Microsoft®

Comprehensive

JACOBSON | KINSER | MORIARITY

Series Editor AMY KINSER

Excel 2016