Add Even More Muscle to What-If Analyses
Team Scenario Manager with Scenario PivotTable for a more powerful tool.
| This is the second of two articles on how to use Excel to conduct powerful business analyses. |
xcel experts know Scenario Manager conveniently calculates what-if analyses of multiple versions of budgets and other financial projections. But if more than 10 scenarios are being considered, experienced users know the project can become very cumbersome and the results hard to track. The solution: Team Scenario Manager with Scenario PivotTable . Working together they make it easier to examine and compare scores of scenarios by parsing down to the most relevant options to avoid getting lost in a blizzard of numbers.
| ||
Boldface type is used to identify the names of icons, agendas and URLs. Follow along as I demonstrate how Scenario PivotTable can make your analysis of even the most complex what-if projects more efficient and effective. In part 1, “ Add Muscle to What-If Analyses ” (see JofA , Sept.04, page 38), we demonstrated the basic techniques for managing multiple what-if versions using Excel’s Scenario Manager. To illustrate the process, we started with a model budget for a fictitious business, PQR Co. ( exhibit 1 ) and created a series of five scenarios with varying sales growth rates, cost-of-sales growth rates and advertising expenditures.
If you wish to follow along as we demonstrate the use of Scenario PivotTable , download the Excel budget file from www.aicpa.org/download/pubs/jofa/2005_03_weisel-pqr-budget.xls .
The worksheet shown in exhibit 2 contains the five scenarios we created in the original example plus five more. For a brief refresher on the process of adding alternative business plans, see the sidebar, “ Adding Scenarios .”
Once you’ve created the scenarios, we can begin to generate a pivot table to analyze how each option affects the business. With the Scenario Manager dialog box open, click on Summary . Change the report type to Scenario PivotTable and click 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 I5, I11, B16, C16, D16, E16, F7, F23, F25 and F26. The Scenario Summary dialog box will now resemble exhibit 3 , below.
The next step is to create a dynamic report enabling us to analyze all these scenarios. Click on OK in the Scenario Summary dialog box and Excel will generate a new worksheet with a report summarizing our 10 scenarios, as shown in exhibit 4 , below. For consistency in presentation, I’ve formatted the columns of data containing dollar values to currency with whole numbers and the columns containing percent values to percentages with two decimal places.
The table presents all 10 scenarios (rows 5 through 14). Column headers contain the variables that are allowed to change ( sales growth rate , cost-of-sales growth rate and advertising for each period) as well as the target cells ( net total sales , net total operating income , gross profit ratio and net return on sales ). The row headings are simply the 10 scenarios that we specified earlier.
For example, in row 9 the scenario is the 3% growth in sales without additional advertising. You can see the various target cell results for this scenario. Working with the PivotTable allows us to have complete control over the presentation of all input and output elements of our analysis.
The pull-down arrows in the Results Cells box (B3) and the column header in box A4 allow you to manage what you see on the screen, creating more or less detail as needed. Click on the drop-down arrow in cell B3 and you will see all of the variables and target elements that are included in the current table. Click on the drop-down arrow in cell A4 and you will see each of the 10 scenarios included in the table.
If we want to focus on only a subset of the data, we can easily alter what is presented in the table. For example, if you decide that the 1.0% and 1.5% sales growth rate scenarios are unlikely to occur and are cluttering your report, click on the pull-down arrow in cell A4, select those scenarios and click on OK to remove them. If you decide to bring them back into the analysis, all you have to do is click again on the pull-down arrow, then select those scenarios again and finally, click on OK .
You have similar control over the columns. Click on the pull-down arrow in cell B3 ( Results Cells ) and select the elements you wish to remove. To restore those elements, go to the Excel toolbar, click on Edit and on Undo Pivot .
As you can see, Scenario PivotTable gives you exceptional control over the reporting structure and can easily manage a vast number of scenarios. It’s an invaluable tool that helps you document and evaluate the impact of many different changes to financial conditions .
JAMES A. WEISEL, CPA, DBA, CMA, is a professor at the Stetson School of Business & Economics, Mercer University, Atlanta. His e-mail address is weisel_ja@mercer.edu .
Adding Scenarios
With the downloaded spreadsheet open, click on Tools , Scenarios to evoke the Scenario Manager dialog box. You will see the five previously created scenarios ( exhibit 5 , below).
Click on the Add button to display the Add Scenario box and type in a name for each new scenario in the Scenario name box. We’ve already identified the cells that will be allowed to change so we do not need to alter those now. Now add a detailed description in the Comment box to further document the scenario. Click on OK to move on to the final task, specifying scenario values. In the Scenario Values box, click in box 1, Sales_growth_rate and type in a value, as shown in exhibit 6 , below. Do the same for Cost_of_sales_growth and the quarterly Advertising . Click on OK and repeat the process until you have configured all the scenarios.
