DAY 7 MICROSOFT EXCEL Navya Thum navya thummail

DAY 7: MICROSOFT EXCEL Navya Thum navya. thum@mail. wvu. edu February 13, 2013

ATTENDANCE • Go to https: //cs 101. wvu. edu/tools/recordattendance/ to record attendance. • Enter your My. ID username and password. 1/22/2022 2

ANNOUNCEMENTS • Homework 2 due on February 2 • Homework 3 due on February 22 • Exam 1 Section 12 - 02/27/2012 Section 14 - 02/25/2012 1/22/2022

TODAY • To Create a one-variable data table and a two-variable data table • Identify an input value with Goal Seek • Use Scenario Manager • Optimize results with Solver 1/22/2022

WHAT-IF ANALYSIS • Three important tools which enable to perform “what-if” analysis • What-if analysis enables you to see how changes in variables affect calculated results • A variable is a value that can be changed or to see how those changes impact other values • An independent variable is one that can be controlled directly, such as the price of an item • A dependent variable is one that may vary such as the number of items sold • A substitution value is the one which replaces the original value of a variable in a data table 1/22/2022

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 1/22/2022

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 1/22/2022

CREATE A ONE-VARIABLE DATA TABLE 1/22/2022

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 1/22/2022

CREATE A TWO-VARIABLE DATA TABLE 1/22/2022

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 1/22/2022

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 1/22/2022

IDENTIFY AN INPUT VALUE WITH GOAL SEEK 1/22/2022

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 1/22/2022

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 1/22/2022

USE SCENARIO MANAGER 1/22/2022

USE SCENARIO MANAGER 1/22/2022

USE SCENARIO MANAGER 1/22/2022

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 1/22/2022

GENERATE SCENARIO SUMMARY REPORTS • Scenario summary report ─ a worksheet that compares the scenario results • Do not automatically update 1/22/2022

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 1/22/2022

GENERATE SCENARIO SUMMARY REPORTS 1/22/2022

GENERATE SCENARIO SUMMARY REPORTS 1/22/2022

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 1/22/2022

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 1/22/2022

LOAD THE SOLVER ADD-IN 1/22/2022

OPTIMIZE RESULTS WITH SOLVER • Solver requires three parameters: – Objective cell contains a formulabased 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 1/22/2022

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 1/22/2022

OPTIMIZE RESULTS WITH SOLVER 1/22/2022

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 1/22/2022

OPTIMIZE RESULTS WITH SOLVER 1/22/2022

ANSWER REPORT • • Solver report information Objective cell section Variable cells section Constraints section 1/22/2022

Questions? 33 1/22/2022