- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
CHAPTER
Performing
Spreadsheet What-If
Analysis
One of the most appealing aspects of Excel is its ability to create dynamic models. A dynamic model uses formulas that instantly recalculate when you change values in cells that are used by the
formulas. When you change values in cells in a systematic manner and observe the effects on specific formula cells, you’re performing a type of what-if analysis.
What-if analysis is the process of asking such questions as “What if the interest rate on the loan changes to 7.5 percent rather than 7.0 percent?” or “What if we raise our product prices by 5 percent?”
If you set up your worksheet properly, answering such questions is simply a matter of plugging in new values and observing the results of the recalculation. Excel provides useful tools to assist you in your what-if endeavors.
IN THIS CHAPTER
A what-if example
Types of what-if analyses
Manual what-if analyses
Creating one-input and two-input data tables
Using Scenario Manager
A What-If Example
Figure 36.1 shows a simple worksheet model that calculates information pertaining to a mortgage loan. The worksheet is divided into two sections: the input cells and the result cells (which contain formulas).
On the CD
This workbook is available on the companion CD-ROM. The filename is mortgage loan.xlsx.
745
Part V: Analyzing Data with Excel
With this worksheet, you can easily answer the following what-if questions:
•What if I can negotiate a lower purchase price on the property?
•What if the lender requires a 20-percent down payment?
•What if I can get a 40-year mortgage?
•What if the interest rate increases to 7.0 percent?
FIGURE 36.1
This simple worksheet model uses four input cells to produce the results.
You can answer these questions by simply changing the values in the cells in range C4:C7 and observing the effects in the dependent cells (C10:C13). You can, of course, vary any number of input cells simultaneously.
Avoid Hard-Coding Values in a Formula
The mortgage calculation example, simple as it is, demonstrates an important point about spreadsheet design: You should always set up your worksheet so that you have maximum flexibility to make changes. Perhaps the most fundamental rule of spreadsheet design is the following:
Do not hard-code values in a formula. Rather, store the values in separate cells and use cell references in the formula.
The term hard-code refers to the use of actual values, or constants, in a formula. In the mortgage loan example, all the formulas use references to cells, not actual values.
You could use the value 360, for example, for the loan term argument of the pmt function in cell C11 of Figure 36.1. Using a cell reference has two advantages. First, you have no doubt about the values that the formula uses (they aren’t buried in the formula). Second, you can easily change the value — which is easier than editing the formula.
746
Chapter 36: Performing Spreadsheet What-If Analysis
Using values in formulas may not seem like much of an issue when only one formula is involved, but just imagine what would happen if this value were hard-coded into several hundred formulas that were scattered throughout a worksheet.
Types of What-If Analyses
Not surprisingly, Excel can handle much more sophisticated models than the preceding example. To perform a what-if analysis using Excel, you have three basic options:
•Manual what-if analysis: Plug in new values and observe the effects on formula cells.
•Data tables: Create a special type of table that displays the results of selected formula cells as you systematically change one or two input cells.
•Scenario Manager: Create named scenarios and generate reports that use outlines or pivot tables.
I discuss each of these types of what-if analysis in the rest of this chapter.
Manual What-If Analysis
A manual what-if analysis doesn’t require too much explanation. In fact, the example that opens this chapter demonstrates how it’s done. Manual what-if analysis is based on the idea that you have one or more input cells that affect one or more key formula cells. You change the value in the input cells and see what happens to the formula cells. You may want to print the results or save each scenario to a new workbook. The term scenario refers to a specific set of values in one or more input cells.
Manual what-if analysis is very common, and people often use this technique without even realizing that they’re doing a type of what-if analysis. This method of performing what-if analysis certainly has nothing wrong with it, but you should be aware of some other techniques.
Tip
If your input cells are not located near the formula cells, consider using a Watch Window to monitor the formula results in a movable window. I discuss this feature in Chapter 3. n
Creating Data Tables
This section describes one of Excel’s most underutilized features: data tables. A data table is a dynamic range that summarizes formula cells for varying input cells. You can create a data table
747
Part V: Analyzing Data with Excel
fairly easily, but data tables have some limitations. In particular, a data table can deal with only one or two input cells at a time. This limitation becomes clear as you view the examples.
Note
Scenario Manager, discussed later in this chapter (see “Using Scenario Manager”), can produce a report that summarizes any number of input cells and result cells. n
Don’t confuse a data table with a standard table (created by choosing Insert Tables Table). These two features are completely independent.
Creating a one-input data table
A one-input data table displays the results of one or more formulas for various values of a single input cell. Figure 36.2 shows the general layout for a one-input data table. You need to set up the table yourself, manually. This is not something that Excel will do for you.
FIGURE 36.2
How a one-input data table is set up.
You can place the data table anywhere in a worksheet. The left column contains various values for the single input cell. The top row contains references to formulas located elsewhere in the worksheet. You can use a single formula reference or any number of formula references. The upper-left cell of the table remains empty. Excel calculates the values that result from each value of the input cell and places them under each formula reference.
This example uses the mortgage loan worksheet from earlier in the chapter (see “A What-If Example”). The goal of this exercise is to create a data table that shows the values of the four formula cells (loan amount, monthly payment, total payments, and total interest) for various interest rates ranging from 6 to 8 percent, in 0.25-percent increments.
748
Chapter 36: Performing Spreadsheet What-If Analysis
On the CD
This workbook is available on the companion CD-ROM. The file is named mortgage loan data table.xlsx.
Figure 36.3 shows the setup for the data table area. Row 3 consists of references to the formulas in the worksheet. For example, cell F3 contains the formula =C10, and cell G3 contains the formula =C11. Row 2 contains optional descriptive labels, and these are not actually part of the data table. Column E contains the values of the single input cell (interest rate) that Excel will use in the table.
To create the table, select the data table range (in this case, E3:I12) and then choose Data Data Tools What-If Analysis Data Table. Excel displays the Data Table dialog box, shown in Figure 36.4.
FIGURE 36.3
Preparing to create a one-input data table.
FIGURE 36.4
The Data Table dialog box.
You must specify the worksheet cell that contains the input value. Because variables for the input cell appear in the left column in the data table, you place this cell reference in the Column Input Cell field. Enter C7 or point to the cell in the worksheet. Leave the Row Input Cell field blank.
Click OK, and Excel fills in the table with the calculated results (see Figure 36.5).
749
Part V: Analyzing Data with Excel
FIGURE 36.5
The result of the one-input data table.
Using this table, you can now see the calculated loan values for varying interest rates. If you examine the contents of the cells that Excel entered as a result of this command, you’ll see that the data is generated with a multicell array formula:
{=TABLE(,C7)}
As I discuss in Chapter 16, an array formula is a single formula that can produce results in multiple cells. Because the table uses formulas, Excel updates the table that you produce if you change the cell references in the first row or plug in different interest rates in the first column.
Note
You can arrange a one-input table vertically (as in this example) or horizontally. If you place the values of the input cell in a row, you enter the input cell reference in the Row Input Cell field of the Data Table dialog box. n
Creating a two-input data table
As the name implies, a two-input data table lets you vary two input cells. You can see the setup for this type of table in Figure 36.6. Although it looks similar to a one-input table, the two-input table has one critical difference: It can show the results of only one formula at a time. With a one-input table, you can place any number of formulas, or references to formulas, across the top row of the table. In a two-input table, this top row holds the values for the second input cell. The upper-left cell of the table contains a reference to the single result formula.
Using the mortgage loan worksheet, you could create a two-input data table that shows the results of a formula (say, monthly payment) for various combinations of two input cells (such as interest rate and down-payment percent). To see the effects on other formulas, you simply create multiple data tables — one for each formula cell that you want to summarize.
The example in this section uses the worksheet shown in Figure 36.7 to demonstrate a two-input data table. In this example, a company wants to conduct a direct-mail promotion to sell its product. The worksheet calculates the net profit from the promotion.
750
Chapter 36: Performing Spreadsheet What-If Analysis
FIGURE 36.6
The setup for a two-input data table.
FIGURE 36.7
This worksheet calculates the net profit from a direct-mail promotion.
On the CD
This workbook, named direct mail.xlsx, is available on the companion CD-ROM. n
This model uses two input cells: the number of promotional pieces mailed and the anticipated response rate. The following items appear in the Parameters area:
•Printing costs per unit: The cost to print a single mailer. The unit cost varies with the quantity: $0.20 each for quantities less than 200,000; $0.15 each for quantities of 200,001 through 300,000; and $0.10 each for quantities of more than 300,000. The following formula is used:
=IF(B4<200000,0.2,IF(B4<300000,0.15,0.1))
751
Part V: Analyzing Data with Excel
•Mailing costs per unit: A fixed cost, $0.28 per unit mailed.
•Responses: The number of responses, calculated from the response rate and the number mailed. The formula in this cell is the following:
=B4*B5
•Profit per response: A fixed value. The company knows that it will realize an average profit of $18.50 per order.
•Gross profit: This is a simple formula that multiplies the profit-per-response by the number of responses:
=B10*B11
•Print + mailing costs: This formula calculates the total cost of the promotion:
=B4*(B8+B9)
•Net Profit: This formula calculates the bottom line — the gross profit minus the printing and mailing costs.
If you enter values for the two input cells, you see that the net profit varies quite a bit, often going negative to produce a net loss.
Figure 36.8 shows the setup of a two-input data table that summarizes the net profit at various combinations of quantity and response rate; the table appears in the range E4:M14. Cell E4 contains a formula that references the Net Profit cell:
=B14
FIGURE 36.8
Preparing to create a two-input data table.
To create the data table
1.Enter the response rate values in F4:M4.
2.Enter the number mailed values in E5:E14.
752