Microsoft Excel: How to use Scenario Manager

Q. I often save multiple versions of the same Excel file (usually with differing assumptions) and later have difficulty telling which file is which when I want to revert to a previous version. Any suggestions?

A. Like you, I too sometimes save my files by incrementing the file name using numbers (Budget 1.xlsx, Budget 2.xlsx, etc.). However, that’s not the most efficient way to manage multiple versions of your Excel workbook. Excel has a built-in tool for achieving the solution you seek called Scenario Manager. You can access this tool from the Data tab by selecting What-If Analysis, Scenario Manager, as pictured below.

Next, click the Add button and provide a name for the scenario you want to save (such as Higher Occupancy in the example at the top of the next column), indicate which cells contain the key assumptions that can change in the Changing cells box, and then click OK. (This tool requires you to indicate at least one cell that changes from one scenario to the next.)

Excel’s Scenario Manager allows you to save hundreds of scenarios in the same workbook, and you can quickly view any one of them (the number of scenarios is limited by your computer’s available memory). For example, in the workbook pictured below, I have created a simple Excel-based projection with six scenarios. To switch between the scenarios, I select a scenario (such as Steve’s Assumptions in the example) and click the Show button at the bottom to display that scenario (revenue is projected to be $361,238 in this example).

To view a different scenario, I can then select The Board’s Approved Assumptions and then click Show to display projected revenue of $357,000, as pictured in the example below.

This approach eliminates the need for creating multiple redundant Excel files for the same project, resulting in a neat and tidy way to manage multiple scenarios of the same workbook based on differing assumptions. The result is that there is less need to manage multiple files for the same project, and the resulting single workbook is easier to manage and share. In addition, you can create a summary comparison report from the Scenario Manager dialog box by selecting the Summary button, the result of which is pictured below.

(Note: There is no visual indicator that shows an Excel file contains multiple scenarios, so if you share an Excel file containing multiple scenarios, you may want to insert a note in the file or advise those recipients that multiple scenarios exist, or else they might completely miss the alternative scenarios.) You can download this Scenario Manager Excel file example at ­carltoncollins.com/scenario.xlsx.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.