Microsoft Excel: How to use Scenario Manager

By J. Carlton Collins, CPA

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.

techqa1


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.)

techqa2


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).

techqa3


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.

techqa4


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.

techqa5


(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.

SPONSORED REPORT

2018 financial reporting survey: Challenges and trends

Learn the top reporting challenges that emerged in a survey of more than 800 finance, accounting, and compliance professionals across the world, and compare them with your organization's obstacles.

PODCAST

How the skill set for today’s CFO is changing

Scott Simmons, a search expert for large-company CFOs, gives advice for the next generation of finance leaders and more, including which universities are regularly producing future CEOs and CFOs.