Add muscle to "what-if" analysis: make Scenario Manager even more productive.

When you prepare budgets or other financial analyses built on future assumptions, you usually have to try out various estimates before you can come up with the scenario that meets your needs. In a typical situation, you even may need to print the numerous analyses so you don't lose track of which versions are based on which assumptions. In short, it can be a tedious task. Fortunately, there is a way, applying a few adjustments in Excel's powerful "what-if" tool Scenario Manager, to make the task more effective and less onerous. Let's walk through the process so you can see how to use the tool.

So you can work along with me as I set up the analyses, I suggest you download the model budget I prepared (see exhibit 1, page 39) for PQR Co. from www.aicpa.org/ download/pubs/jofa/2004_09_weisel-budget.xls. All numbers have been rounded.

[ILLUSTRATION OMITTED]

As you'll see I prepared the budgeted quarterly income statement with several initial assumptions.

PQR's management wants to see how various assumptions will affect results. For example, what will happen if sales growth slows to 2.5% or 2% while cost of sales continues at a 3% rate? And will 3.5% sales growth be sufficient to offset a targeted increase in advertising?

To begin the exercise, assume gross sales and cost of sales will rise 3% each quarter. The spreadsheet has the appropriate data and formulas to use that information to calculate net operating income, gross profit ratios and return on sales.

Now let's put Scenario Manager to work. With the spreadsheet open, click on Tools and Scenarios to evoke the Scenario Manager dialog box (exhibit 2, page 40).

[ILLUSTRATION OMITTED]

Click on the Add button, which brings up the Add Scenario box (exhibit 3, page 40).

[ILLUSTRATION OMITTED]

To add a scenario you must input its name and identify the cells you will allow to be changed by new information. Once we get under way, I'll show you how to specify the scenario values.

In addition to the original assumption of a 3% sales growth rate, we also will be examining the impact of 2.5%, 2% and 1.5% growth rates.

In the Add Scenario screen, create a name for the first scenario, such as 2.5% growth rate, and type it in the Scenario name box. Now click in the Changing cells box and backspace to delete any existing references. Click on cell 15 (the sales growth-rate assumption) and note that Excel automatically populates it with all the appropriate references. Hold down the Ctrl key and click on cell 111 (a cost-of-sales growth rate assumption). Continue holding down the Ctrl key and click on cells B16, C16, D16 and E16 so we can see the quarterly impact on advertising expenses. In order to provide some additional documentation about what I'm doing, I've added a description of this scenario in the Continent box. You can add your own details here; just click in the Comment box and type away. The Add Scenario box now should resemble exhibit 4, above.

[ILLUSTRATION OMITTED]

Click on OK to move on to the third task: specifying scenario values in the new screen that opens (see exhibit 5, below). The only value we will alter initially in the Scenario Values box is the first item--sales growth rate. Click in the first box and type in .025 and click on OK.

[ILLUSTRATION OMITTED]

The Scenario Manager screen now should resemble exhibit 6, below.

[ILLUSTRATION OMITTED]

To create additional scenarios, simply repeat the above process and specify the appropriate value for sales growth rate. We have already specified the cells allowed to change so we do not need to repeat that step. After you add the scenarios for 2% and 1.5% growth rates, the completed Scenario Manager should resemble exhibit 7, below.

[ILLUSTRATION OMITTED]

GETTING IT ALL TOGETHER

Now we'll generate a report that will summarize the three scenarios we've just created. From the Scenario Manager dialog box, click on Summary. Leave the Report type as Scenario summary and crick in the Result cells box. You may need to backspace to delete any existing cell references. While holding down the Ctrl key, click on cells F7, F23, F25 and F26. The Scenario Summary dialog box now will look as illustrated in exhibit 8, below.

[ILLUSTRATION OMITTED]

Now the payoff for your efforts: Click on OK in the Scenario Summary dialog box and Excel will generate a new worksheet tab, as shown in exhibit 9, page 41, with a report summarizing our initial budget as well as the three scenarios.

Across the top of the report we find a listing of the current values as well as each of the three scenarios we specified. The report is divided into two main sections. The first identifies each of the cells we permitted to change as well as their values. The second section contains the result cells we asked for: net sales, net operating income, gross profit ratio and return on sales. We can see, for example, that a sales growth rate of 1.5% would reduce net operating income from its current projection of $214,000 to $124,000. The single report provides an excellent comparison of multiple assumptions and documents those assumptions--eliminating the need to sort through stacks of paper.

You can add and subtract scenarios from Scenario Manager at will. With the cells previously identified as allowed to change, we easily can create a virtually unlimited number of scenarios with various combinations of assumptions. For example, following the steps identified previously, I've created two additional scenarios assuming the company increased advertising to $110,000 quarterly and the resulting sales growth rate and cost-of-sales growth rates are 3.5% and 4%, respectively.

For convenience, I've temporarily hidden columns E through G (the 2.5%, 2%, and 1.5% growth rate assumptions) so the two new scenarios fit in the screen. The ability to hide or unhide columns gives the user more flexibility in managing and presenting the reports. The resulting report is shown in exhibit 10, below. Here we can see a 4% growth rate is necessary to effectively offset the cost of advertising, yielding a small increase in net operating income from $214,000 to $219,000.

With a little work up front, Scenario Manager can significantly improve your ability to create concise reports comparing multiple "what-if" analyses. Users should note that Excel limits the number of cells allowed to change to 32. The number of scenarios that can be created, however, is virtually unlimited. This should permit analysts sufficient flexibility to create as many "what-ifs" as needed.

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces:

* Boldface type is used to identify the names of icons, agendas and URLs.

* Sans serif type indicates the names of files and the names of commands and instructions users should type into the computer.

JAMES A. WEISEL, CPA, DBA, CMA, is a professor at the Stetson School of Business & Economics, Mercer University, Atlanta. His e-mail address is [e-mail].