CHAPTER 9

1. CHAPTER 9 Decision Making Using Excel

2. 9.1 Performing What-If Analysis • A what-if analysis is a worksheet model that lets you calculate possible outcomes for a given set of assumptions. • Assumptions are also known as input cells or variables • The values stored in a worksheet’s input cells at any given time provide the set of assumptions for a single scenario

3. 9.1.1 Using Goal Seeking Figure 9.1 Opening the EX0910 workbook

4. 9.1.1 Using Goal Seeking Figure 9.2 Entering arguments in the Goal Seek dialog box The cell containing the outcome formula The target value for the outcome cell The input cell containing the value that Excel may change to achieve the outcome or target value

5. 9.1.1 Using Goal Seeking Figure 9.3 Goal Seek Status dialog box

6. 9.1.1 Using Goal Seeking Figure 9.4 Displaying the GoalSeek Chart worksheet

7. Position the mouse pointer over the column marker The Range Finder selects the cell ranges referenced by the embedded chart. Markers appear when you correctly select a single column 9.1.1 Using Goal Seeking Figure 9.5 Preparing to size a data column in an embedded chart

8. 9.1.1 Using Goal Seeking Figure 9.6 Finding an answer using graphical goal seeking Input value required to achieve the outcome or target value. Outcome or target value

9. 9.1.2 Using Solver Figure 9.7 Add-ins dialog box with the Solver Add-in selected

10. 9.1.2 Using Solver Figure 9.8 Solver Parameters dialog box Outcome cell containing the formula to optimize Input cells containing values that can be changed by Solver Constraints that must be adhered to by Solver when changing values

11. 9.1.2 Using Solver Figure 9.9 Add Constraint dialog box Enter a constant value or a cell reference containing the constraint. Select a comparison operator (>=, =, <=), int (integer), or bin (binary). Select a direct or indirect reference to one of the input cells.

12. 9.1.2 Using Solver Figure 9.10 Adding constraints to Solver

13. 9.1.2 Using Solver Figure 9.11 Solver Parameters dialog box after adding constraints Constraints are added using absolute cell references.

14. 9.1.2 Using Solver Figure 9.12 Solver Results dialog box

15. 9.1.2 Using Solver Figure 9.13 Requiring integer values for results

16. 9.1.2 Using Solver Figure 9.14 Viewing an Answer report created by Solver

17. 9.1.3 Using Scenario Manager Figure 9.15 Displaying the Scenarios worksheet Create a range name by selecting a cell and then typing in the Name box.

18. 9.1.3 Using Scenario Manager Figure 9.16 Add Scenario dialog box Enter a descriptive name for the scenario. Enter the input cells that you want to modify to achieve the desired outcome. Enter an optional comment to describe the scenario.

19. 9.1.3 Using Scenario Manager Figure 9.17 Scenario Values dialog box

20. 9.1.3 Using Scenario Manager Figure 9.18 Showing the “Union Proposal” results

21. 9.1.3 Using Scenario Manager Figure 9.19 Creating a scenario summary report

22. Using a linear trendline to predict future values Independent variable or “x-set” Dependent variable or “y-set” Y-axis X-axis 9.2 Finding Trends and Forecasting Results Figure 9.20 Predicting future values based on historical results

23. 9.2.1 Statistical Forecasting Functions Figure 9.21 Opening the EX0920 workbook

24. Male growth appears linear. Female growth appears exponential. 9.2.1 Statistical Forecasting Functions Figure 9.22 Viewing the Enr-Line chart

25. 9.2.1 Statistical Forecasting Functions Figure 9.23 Function Arguments dialog box: FORECAST function

26. 9.2.1 Statistical Forecasting Functions Figure 9.24 ScreenTip for the TREND function

27. 9.2.1 Statistical Forecasting Functions Figure 9.25 Function Arguments dialog box: GROWTH function

28. 9.2.1 Statistical Forecasting Functions Figure 9.26 Entering forecasting functions in the Shuster workbook

29. 9.2.2 Calculating Trendlines Figure 9.27 Add Trendline dialog box: Type tab Click the type of trendline to fit to the selected data series Currently selected data series

30. 9.2.2 Calculating Trendlines Figure 9.28 Add Trendline dialog box: Options tab Select “3” to project the trendline three years into the future.

31. 9.2.2 Calculating Trendlines Figure 9.29 Adding a trendline to a data series

32. Exponential trendline Linear trendline 9.2.2 Calculating Trendlines Figure 9.30 Adding trendlines to the Enr-Line chart

33. 9.2.2 Calculating Trendlines Figure 9.31 Adding a moving average trendline to the Enr-Total chart Moving Average trendline

34. Formula expression Input cells Contains the formula “=E2” For a one-input table, use either a column or row arrangement; a typical column arrangement is shown here. 9.3 Working with Data Tables Figure 9.32 One-input and two-input data tables

35. Worksheet used for creating a two-input data table in the next lesson. Worksheet used for creating a one-input data table in this lesson. 9.3.1 Creating a One-Input Data Table Figure 9.33 Opening the EX0930 workbook

36. 9.3.1 Creating a One-Input Data Table Figure 9.34 Preparing a one-input data table Using the fill handle to create columns labels

37. Input cells Outcome formulas For a one-input data table, enter either a row input cell or a column input cell; but not both. 9.3.1 Creating a One-Input Data Table Figure 9.35 Creating a one-input data table for performing two calculations

38. 9.3.1 Creating a One-Input Data Table Figure 9.36 Formatting the one-input data table Array formula Outcome formulas Data table calculations

39. 9.3.2 Creating a Two-Input Data Table Figure 9.37 Preparing a two-input data table

40. 9.3.2 Creating a Two-Input Data Table Figure 9.38 A two-input data table Notice that the array formula contains arguments for both input cells. If desired, you can hide this value by applying the same font color as the background fill color.

41. Page field item Data area (cell range from B5 to G11) Page field Column field Column field item Data field Row field Row field item 9.4 Working With PivotTables and PivotCharts Figure 9.39 Example of a PivotTable report

42. 9.4.1 Creating a PivotTable Report Figure 9.40 Opening the EX0940 workbook

43. 9.4.1 Creating a PivotTable Report Figure 9.41 PivotTable and PivotChart Wizard: Step 1 of 2

44. 9.4.1 Creating a PivotTable Report Figure 9.42 Building a PivotTable report PivotTable toolbar The PivotTable Field List window displays the field columns from a worksheet list.

45. Data field button Row field button Field items already added to the PivotTable area appear in boldface. Column field button 9.4.1 Creating a PivotTable Report Figure 9.43 Adding field items to the PivotTable area

46. 9.4.2 Customizing a PivotTable Report Figure 9.44 Customizing a PivotTable report The Gender field button was dragged from the column field area to the page field area in the PivotTable report. Education field button

47. 9.4.2 Customizing a PivotTable Report Figure 9.45 Pivoting a PivotTable report Filtering the PivotTable report so that only data for the male gender is included. Education is now a row field button as opposed to a column field button.

48. 9.4.2 Customizing a PivotTable Report Figure 9.46 Selecting results to display in the PivotTable report Each field button contains a list of unique values that you may use to limit the data displayed in a PivotTable report.

49. 9.4.2 Customizing a PivotTable Report Figure 9.47 PivotTable Field settings dialog box Select the type of calculation to perform in the data area.

50. 9.4.2 Customizing a PivotTable Report Figure 9.48 Pivoting the PivotTable report Calculating averages for the salary data field. The number formatting remains intact even after pivoting the PivotTable report.