How to Use Scenario Manager in Microsoft Excel
Using the Scenario Manager in Excel
In this example, we need to choose between two jobs. Job 1 has a lower salary but is closer to home, which means lower fuel costs. Job 2 offers a higher salary but is farther away, leading to higher fuel expenses. We want to compare which job leaves us with more money at the end of the month.
Start by entering the data for your first scenario into the sheet. For Job 1, enter the salary in cell B2, the fuel cost in cell B3, and monthly bills in cell B4. In cell B5, create a simple formula that calculates the remaining money after subtracting fuel and bills from your salary.
Go to the Data tab, click the What-If Analysis drop-down arrow, and pick “Scenario Manager.”
In the Scenario Manager window, click “Add” to include this first scenario.
Give your scenario a name; in this case, we’ll call it “Job 1.”
Next, go to the “Changing Cells” input box. Here, enter or select the cell references for all the cells you want to adjust in this scenario. You can either type the cell references separated by commas, or hold Ctrl (Windows) or Command (Mac) while clicking each cell.
In the pop-up box, enter the values for the scenario. Since this is the first scenario and you’ve already added the amounts to your sheet, the values should automatically appear in the corresponding boxes. Review them, then click “OK” to confirm.
You’ll now see your first scenario listed in the Scenario Manager window. To create the second scenario, click “Add.”
Repeat the same steps to name the scenario and specify the Changing Cells. Since you’re comparing similar situations, the cell references will likely be the same. However, you can choose different or additional cells if needed. When you’re done, click “OK.”
Notes on the Scenario Manager
You can create as many scenarios as you need and switch between them easily. This is useful for comparing multiple numeric or financial situations, whether it’s 3, 5, or even 10 scenarios. The Scenario Manager allows you to use up to 32 changing cells for each scenario. In our example, we only used two, but you can compare many more variables.
To edit or delete a scenario, open the Scenario Manager, select the scenario, then click “Edit” to make changes or “Delete” to remove it.
To view a comparison in one place, open the Scenario Manager, click “Summary,” and choose “Scenario Summary.” A new tab will open with a visual comparison that you can save or share.
Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!