Add Even More Muscle to "What-If" Analyses: Team Scenario Manager with Scenario PivotTable for a More Powerful Tool
James A. Weisel · Journal of accountancy online/Journal of accountancy · 2005
This is the second of two articles on how to use Excel to conduct powerful business analyses. Excel 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. 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; www.aicpa.org/pubs/jofa/sep2004/weisel.htm), 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, page 77) and created a series of five scenarios with varying sales growth rates, cost-of-sales growth rates and advertising expenditures. [ILLUSTRATION OMITTED] 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_weiselpqr-budget.xls. The worksheet shown in exhibit 2, page 77, 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, page 79. [ILLUSTRATION OMITTED] 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. [ILLUSTRATION OMITTED] 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. [ILLUSTRATION OMITTED] 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. …