Chapter 19
Using Excel’s business modeling tools
In this chapter, you will
Understand and build formulas for performing what-if analysis
Learn the details of Excel’s powerful Goal Seek tool
Understand, create, and work with scenarios
At times, it’s not enough to simply enter data into a worksheet, build a few formulas, and add a little formatting to make things presentable. In the business world, you’re often called on to divine some inner meaning from the jumble of numbers and formula results that litter your workbooks. In other words, you need to analyze your data to see what nuggets of understanding you can unearth. In Excel, analyzing business data means using the program’s business modeling tools. This chapter looks at a few of those tools and some analytic techniques that have many uses. You’ll find out how to use Excel’s numerous methods for what-if analysis, how to wield Excel’s useful Goal Seek tool, and how to create scenarios.
Using what-if analysis
What-if analysis is perhaps the most basic method for interrogating your worksheet data. With what-if analysis, you first calculate a formula, D, based on the input from variables A, B, and C. You then say, “What if I change variable A? Or B or C? What happens to the result?”
For example, Figure 19-1 shows a worksheet that calculates the future value of an investment based on five variables: the interest rate, period, annual deposit, initial deposit, and deposit type. Cell C9 shows the result of the FV() function (which I covered in Chapter 15, “Working with investment formulas”; see the section “Calculating the future value”). Now the questions begin:
What if the interest rate is 7%?
What if you deposit $8,000 per year? Or $12,000?
What if you reduce the initial deposit?
Answering these questions is a straightforward matter of changing the appropriate variables and watching the effect on the result.
Setting up a one-input data table
The problem with modifying formula variables is that you see only a single result at one time. If you’re interested in studying the effect a range of values has on a formula, you need to set up a data table. In the investment analysis worksheet, for example, suppose you want to see the future value of the investment with the annual deposit varying between $7,000 and $13,000. You could enter these values in a row or column and then create the appropriate formulas. Setting up a data table, however, is much easier, as the following procedure shows:
Add to the worksheet the values you want to input into the formula. You have two choices for the placement of these values:
If you want to enter the values in a row, start the row one cell up and one cell to the right of the formula.
If you want to enter the values in a column, start the column one cell down and one cell to the left of the cell containing the formula, as shown in Figure 19-2.
FIGURE 19-2 Enter the values you want to input into the formula.
Select the range that includes the input values and the formula. (In Figure 19-2, this is B9:C16.)
Select Data > What-If Analysis > Data Table. Excel displays the Data Table dialog box.
Fill in this dialog box based on how you set up your data table:
If you entered the input values in a row, use the Row Input Cell text box to enter the cell address of the input cell.
If the input values are in a column, enter the input cell’s address in the Column Input Cell text box. In the investment analysis example, you select cell C4, and then $C$4 appears in the Column Input Cell text box, as shown in Figure 19-3.
FIGURE 19-3 In the Data Table dialog box, enter the input cell where you want Excel to substitute the input values. Select OK. Excel places each of the input values in the input cell; Excel then displays the results in the data table. Format the cells as necessary (see Figure 19-4).
Adding more formulas to the input table
You’re not restricted to using just a single formula in a data table. If you want to see the effect of the various input values on different formulas, you can easily add them to the data table. For example, in the future value worksheet, it would be interesting to factor inflation into the calculations to see how the investment appears in today’s dollars. Figure 19-5 shows the revised worksheet with a new Inflation variable (cell C7) and a formula that converts the calculated future value into today’s dollars (cell D9).
Note
This is the formula for converting a future value into today’s dollars:
Future Value / (1 + Inflation Rate) ^ Period
Here, Period is the number of years from now that the future value exists.
To create the new data table, follow the steps outlined previously. However, make sure that the range you select in step 2 includes the input values and both formulas (that is, the range B9:D16 in Figure 19-5). Figure 19-6 shows the results.
Note
After you have a data table set up, you can do regular what-if analysis by adjusting the other worksheet variables. Each time you make a change, Excel recalculates every formula in the table.
Setting up a two-input data table
You also can set up data tables that take two input variables. This option enables you to see the effect on an investment’s future value when you enter different values—for example, the annual deposit and the interest rate. The following steps show you how to set up a two-input data table:
Enter one set of values in a column below the formula and the second set of values to the right of the formula in the same row, as shown in Figure 19-7.
FIGURE 19-7 Enter the two sets of values that you want to input into the formula. Select the range that includes the input values and the formula (B8:G15 in Figure 19-7).
Select Data > What-If Analysis > Data Table to display the Data Table dialog box.
In the Row Input Cell text box, enter the cell address of the input cell that corresponds to the row values you entered (C2 in Figure 19-7—the Interest Rate variable).
In the Column Input Cell text box, enter the cell address of the input cell you want to use for the column values (C4 in Figure 19-7—the Annual Deposit variable).
Select OK. Excel runs through the various input combinations and then displays the results in the data table. Format the cells as necessary (see Figure 19-8).
Tip
As mentioned earlier, if you make changes to any of the variables in a table formula, Excel recalculates the entire table. This isn’t a problem in small tables, but large ones can take a very long time to calculate. If you prefer to control the table recalculation, select Formulas > Calculation > Calculation Options > Automatic Except for Data Tables. This tells Excel not to include data tables when it recalculates a worksheet. To recalculate your data tables, select Formulas > Calculation > Calculate Now (or select F9); to recalculate just the data tables in the current worksheet, select Formulas > Calculation > Calculate Sheet (or select Shift+F9).
Editing a data table
If you want to make changes to a data table, you can edit the formula (or formulas) as well as the input value. However, the data table results are a different matter. When you run the Data Table command, Excel enters an array formula in the interior of the data table. This formula is a TABLE() function (a special function available only by using the Data Table command) with the following syntax:
{=TABLE(row_input_ref, column_input_ref)}
Here, row_input_ref and column_input_ref are the cell references you entered in the Data Table dialog box. The braces ({ }) indicate that this is an array, which means that you can’t change or delete individual elements of the array. If you want to change the results, you need to select the entire data table and then run the Data Table command again. If you just want to delete the results, you must first select the entire array and then delete it. (To learn more about arrays, see “Working with arrays,” in Chapter 2, “Creating advanced formulas.”)
Working with Goal Seek
Here’s a what-if question for you: What if you already know the result you want? For example, you might know that you want to have $50,000 saved to purchase new equipment five years from now or that you have to achieve a 30% gross margin in your next budget. If you need to manipulate only a single variable to achieve these results, you can use Excel’s Goal Seek feature. You tell Goal Seek the final value you need and which variable to change, and it finds a solution for you (if one exists). (For more complicated scenarios with multiple variables and constraints, you need to use Excel’s Solver feature. See Chapter 20, “Solving complex problems with Solver.”)
How does Goal Seek work?
When you set up a worksheet to use Goal Seek, you usually have a formula in one cell and the formula’s variable—with an initial value—in another. (Your formula can have multiple variables, but Goal Seek enables you to manipulate only one variable at a time.) Goal Seek operates by using an iterative method to find a solution. That is, Goal Seek first tries the variable’s initial value to see whether that produces the result you want. If it doesn’t, Goal Seek tries different values until it converges on a solution. (To learn more about iterative methods, see “Using iteration and circular references” in Chapter 2.)
Running Goal Seek
Before you run Goal Seek, you need to set up your worksheet in a particular way. This means doing three things:
Set up one cell as the changing cell. This is the value that Goal Seek will iteratively manipulate to attempt to reach the goal. Enter an initial value (such as 0) into the cell.
Set up the other input values for the formula and make them proper initial values.
Create a formula for Goal Seek to use to try to reach the goal.
For example, suppose you’re a small-business owner looking to purchase new equipment worth $50,000 five years from now. Assuming your investments earn 5% annual interest, how much do you need to set aside every year to reach this goal? Figure 19-9 shows a worksheet set up to use Goal Seek:
Cell C5 is the changing cell: the annual deposit into the fund (with an initial value of 0).
The other cells (C3 and C4) are used as constants for the FV() function.
Cell C7 contains the FV() function that calculates the future value of the equipment fund. When Goal Seek is done, this cell’s value should be $50,000.
With your worksheet ready to go, follow these steps to use Goal Seek:
Select Data, What-If Analysis, Goal Seek. Excel displays the Goal Seek dialog box.
Use the Set Cell text box to enter a reference to the cell that contains the formula you want Goal Seek to manipulate (cell C7 in Figure 19-9).
Use the To Value text box to enter the final value you want for the goal cell (such as 50000).
Use the By Changing Cell text box to enter a reference to the changing cell. (This is cell C5 in Figure 19-9.) Figure 19-10 shows the completed Goal Seek dialog box.
FIGURE 19-10 This is the completed Goal Seek dialog box. Select OK. Excel begins the iteration and displays the Goal Seek Status dialog box. When you are finished, the dialog box tells you whether Goal Seek found a solution (see Figure 19-11).
FIGURE 19-11 The Goal Seek Status dialog box shows you the solution (if one was found). NoteMost of the time, Goal Seek finds a solution relatively quickly, and the Goal Seek Status dialog box displays the solution within a second or two. For longer operations, you can select Pause in the Goal Seek Status dialog box to stop Goal Seek. To walk through the process one iteration at a time, select Step. To resume Goal Seek, select Continue.
If Goal Seek found a solution, you can accept the solution by selecting OK. To ignore the solution, select Cancel.
Optimizing product margin
Many businesses use product margin as a measure of fiscal health. A strong margin usually means that expenses are under control and that the market is satisfied with your price points. Product margin depends on many factors, of course, but you can use Goal Seek to find the optimum margin based on a single variable.
For example, suppose you want to introduce a new product line, and you want the product to return a margin of 30% during the first year. Suppose, too, that you’re operating under the following assumptions:
The sales during the year will be 100,000 units.
The average discount to your customers will be 40%.
The total fixed costs will be $750,000.
The cost per unit will be $12.63.
Given all this information, you want to know what price point will produce the 30% margin.
Figure 19-12 shows a worksheet set up to handle this situation. An initial value of $1.00 is entered into the Price per Unit cell (C4), and Goal Seek is set up in the following way:
The Set Cell reference is C14, the Margin calculation.
A value of 0.3 (the 30% Margin goal) is entered in the To Value text box.
A reference to the Price per Unit cell (C4) is entered into the By Changing Cell text box.
When you run Goal Seek, it produces a solution of $47.87 for the price, as shown in Figure 19-13. This solution can be rounded up to a more standard price point of $47.95.
A note about Goal Seek’s approximations
Notice that the solution in Figure 19-13 is an approximate figure. That is, the margin value is 29.92%, not the 30% you were looking for. It’s pretty close (it’s off by only 0.0008), but it’s not exact. Why didn’t Goal Seek find the exact solution?
The answer lies in one of the options Excel uses to control iterative calculations. Some iterations can take an extremely long time to find an exact solution, so Excel compromises by setting certain limits on iterative processes. To see these limits, select File > Options and then select Formulas in the Excel Options dialog box that appears (see Figure 19-14). Two options control iterative processes:
Maximum Iterations: The value in this text box controls the maximum number of iterations. In Goal Seek, this represents the maximum number of values that Excel plugs into the changing cell.
Maximum Change: The value in this text box is the threshold that Excel uses to determine whether it has converged on a solution. If the difference between the current solution and the desired goal is less than or equal to this value, Excel stops iterating.
The Maximum Change value prevented me from getting an exact solution for the profit margin calculation. On a particular iteration, Goal Seek found the solution .2992, which put me within 0.0008 of the goal of 0.3. However, 0.0008 is less than the default value of 0.001 in the Maximum Change text box, so Excel called a halt to the procedure.
To get an exact solution, you would need to adjust the Maximum Change value to 0.0001.
Performing a break-even analysis
In a break-even analysis, you determine the number of units you have to sell of a product so that your total profits are 0 (that is, the product revenue equals the product costs). Setting up a profit equation with a goal of 0 and varying the units sold is perfect for Goal Seek.
To try this, extend the example used in the “Optimizing product margin” section. In this case, assume a unit price of $47.95 (the solution found to optimize product margin, rounded up to the nearest 95¢). Figure 19-15 shows the Goal Seek dialog box filled out as detailed here:
The Set Cell reference is set to C12, the profit calculation.
A value of 0 (the profit goal) is entered in the To Value text box.
A reference to the Units Sold cell (C4) is entered into the By Changing Cell text box.
Figure 19-16 shows the solution: A total of 46,468 units must be sold to break even.
Solving algebraic equations
Algebraic equations don’t come up all that often in a business context, but they do appear occasionally in complex models. Fortunately, Goal Seek also is useful for solving complex algebraic equations of one variable. For example, suppose you need to find the value of x to solve the rather nasty equation displayed in Figure 19-17. Although this equation is too complex for the quadratic formula, it can be easily rendered in Excel. The left side of the equation can be represented with the following formula:
=(((3 * A2 - 8) ^ 2) * (A2 - 1)) / (4 * A2 ^ 2 - 5)
Cell A2 represents the variable x. You can solve this equation in Goal Seek by setting the goal for this equation to 1 (the right side of the equation) and by varying cell A2. Figure 19-17 shows a worksheet and the Goal Seek dialog box.
Figure 19-18 shows the result. The value in cell A2 is the solution (x) that satisfies the equation. Notice that the equation result (cell B2) is not quite 1. As mentioned earlier in this chapter, if you need higher accuracy, you must change Excel’s convergence threshold. In this example, select File, Options, select Formulas, and type 0.000001 in the Maximum Change text box.
Working with scenarios
By definition, what-if analysis is not an exact science. All what-if models make guesses and assumptions based on history, expected events, or some sort of magic. A particular set of guesses and assumptions that you plug into a model is called a scenario. Because most what-if worksheets can take a wide range of input values, you usually end up with a large number of scenarios to examine. Instead of going through the tedious chore of inserting all these values into the appropriate cells, Excel has a Scenario Manager feature that can handle the process for you. This section shows you how to wield this useful tool.
Understanding scenarios
As you’ve seen in this chapter, Excel has powerful features that enable you to build sophisticated models that can answer complex questions. The problem, though, isn’t in answering questions, but in asking them. For example, Figure 19-19 shows a worksheet model that analyzes a mortgage. You use this model to decide how much of a down payment to make, how long the term should be, and whether to include an extra principal paydown every month. The Results section compares the monthly payment and total paid for the regular mortgage and for the mortgage with a paydown. It also shows the savings and reduced term that result from the paydown.
The formula shown in Figure 19-19 uses the PMT() function, which is covered earlier in the book; see “Calculating a loan payment,” in Chapter 14, “Building loan formulas.”
Here are some possible questions to ask this model:
How much will I save over the term of the mortgage if I use a shorter term, make a larger down payment, and include a monthly paydown?
How much more will I end up paying if I extend the term, reduce the down payment, and forgo the paydown?
These are examples of scenarios that you would plug into the appropriate cells in the model. Excel’s Scenario Manager helps by letting you define a scenario separately from the worksheet. You can save specific values for any or all of the model’s input cells, give the scenario a name, and then recall the name (and all the input values it contains) from a list.
Setting up your worksheet for scenarios
Before creating a scenario, you need to decide which cells in your model will be the input cells. These will be the worksheet variables—the cells that, when you change them, change the results of the model. (Not surprisingly, Excel calls these the changing cells.) You can have as many as 32 changing cells in a scenario. For best results, follow these guidelines when setting up your worksheet for scenarios:
The changing cells should be constants. Formulas can be affected by other cells, and that can throw off the entire scenario.
To make it easier to set up each scenario and to make your worksheet easier to understand, group the changing cells and label them (refer to Figure 19-19).
For even greater clarity, assign a range name to each changing cell.
Adding a scenario
To work with scenarios, you use Excel’s Scenario Manager tool. This feature enables you to add, edit, display, and delete scenarios as well as create summary scenario reports.
When your worksheet is set up the way you want it, you can add a scenario to the sheet by following these steps:
Select Data > What-If Analysis > Scenario Manager. Excel displays the Scenario Manager dialog box.
Select Add. The Add Scenario dialog box appears. Figure 19-20 shows a completed version of this dialog box.
FIGURE 19-20 Use the Add Scenario dialog box to define a scenario. Use the Scenario Name text box to enter a name for the scenario.
Use the Changing Cells box to enter references to your worksheet’s changing cells. You can type in the references (be sure to separate noncontiguous cells with commas) or select the cells directly on the worksheet.
Use the Comment box to enter a description for the scenario. This description appears in the Comment section of the Scenario Manager dialog box.
Select OK. Excel displays the Scenario Values dialog box, shown in Figure 19-21.
FIGURE 19-21 Use the Scenario Values dialog box to enter the values you want to use for the scenario’s changing cells. Use the text boxes to enter values for the changing cells.
NoteNotice in Figure 19-21 that Excel displays the range name for each changing cell, which makes it easier to enter your numbers correctly. If your changing cells aren’t named, Excel just displays the cell addresses instead.
To add more scenarios, select Add to return to the Add Scenario dialog box and repeat steps 3 through 7. Otherwise, select OK to return to the Scenario Manager dialog box.
Select Close to return to the worksheet.
Displaying a scenario
After you define a scenario, you can enter its values into the changing cells by displaying the scenario from the Scenario Manager dialog box. The following steps give you the details:
Select Data > What-If Analysis > Scenario Manager.
In the Scenarios list, select the scenario you want to display.
Select Show. Excel enters the scenario values into the changing cells. Figure 19-22 shows an example.
FIGURE 19-22 When you select Show, Excel enters the values for the highlighted scenario into the changing cells. Repeat steps 2 and 3 to display other scenarios.
Select Close to return to the worksheet.
Tip
Displaying a scenario isn’t hard, but it does require having the Scenario Manager onscreen. You can bypass the Scenario Manager by adding the Scenario list to the Quick Access Toolbar. Pull down the Customize Quick Access Toolbar menu and then select More Commands. In the Choose Commands From list, select All Commands. In the list of commands, select Scenario, select Add, and then select OK. (One caveat, though: If you select the same scenario twice in succession, Excel asks whether you want to redefine the scenario. Be sure to select No to keep the current scenario definition.)
Editing a scenario
If you need to make changes to a scenario—whether to change the scenario’s name, select different changing cells, or enter new values—follow these steps:
Select Data > What-If Analysis > Scenario Manager.
In the Scenarios list, select the scenario you want to edit.
Select Edit. Excel displays the Edit Scenario dialog box (which is identical to the Add Scenario dialog box, shown in Figure 19-20).
Make your changes, if necessary, and select OK. The Scenario Values dialog box appears (refer to Figure 19-21).
Enter the new values, if necessary, and then select OK to return to the Scenario Manager dialog box.
Repeat steps 2 through 5 to edit other scenarios.
Select Close to return to the worksheet.
Merging scenarios
The scenarios you create are stored with each worksheet in a workbook. If you have similar models in different sheets (for example, budget models for different divisions), you can create separate scenarios for each sheet and then merge them later. Here are the steps to follow:
Select the worksheet in which you want to store the merged scenarios.
Select Data > What-If Analysis > Scenario Manager.
Select Merge. Excel displays the Merge Scenarios dialog box.
Use the Book drop-down menu to select the workbook that contains the scenario sheet.
Use the Sheet list to select the worksheet that contains the scenario.
Select OK to return to the Scenario Manager.
Select Close to return to the worksheet.
Generating a summary report
You can create a summary report that shows the changing cells in each of your scenarios along with selected result cells. This is a handy way to compare different scenarios. You can try it by following these steps:
Note
When Excel sets up the scenario summary, it uses either the cell addresses or defined names of the individual changing cells and results cells, as well as the entire range of changing cells. Your reports will be more readable if you name the cells you’ll be using before generating the summary.
Select Data > What-If Analysis > Scenario Manager.
Select Summary. Excel displays the Scenario Summary dialog box.
In the Report Type group, select either Scenario Summary or Scenario PivotTable Report.
In the Result Cells box, enter references to the result cells that you want to appear in the report (see Figure 19-23). You can select the cells directly on the sheet or type in the references. (Remember to separate noncontiguous cells with commas.)
FIGURE 19-23 Use the Scenario Summary dialog box to select the report type and result cells. Select OK. Excel displays the report.
Figure 19-24 shows the Scenario Summary report for the Mortgage Analysis worksheet. The names shown in column C (Down_Payment, Term, and so on) are the names I assigned to each of the changing cells and result cells.
Figure 19-25 shows the Scenario PivotTable report for the Mortgage Analysis worksheet.
Note
The PivotTable’s page field—labeled Changing Cells By—enables you to switch between scenarios created by different users. If no other users have access to this workbook, you’ll see only your name in this field’s list.
Deleting a scenario
If you have scenarios you no longer need, you can delete them by following these steps:
Select Data > What-If Analysis > Scenario Manager.
Use the Scenarios list to select the scenario you want to delete.
CautionExcel doesn’t ask you to confirm the deletion, and there’s no way to retrieve a scenario that you’ve deleted accidentally (other than closing the workbook without saving changes or opening a previous version of the workbook), so be sure the scenario you highlighted is one you can live without.
Select Delete. Excel deletes the scenario.
Select Close to return to the worksheet.