Scenario Manager
Scenario Manager allows you to set up several different scenarios. You can use scenarios to predict the outcome of different situations in your spreadsheet. Suppose a company trying to establish a budget for the upcoming year does not know what the sales for the year will be.
The sales figures obviously affect the rest of the budget. With the Scenario Manager, you can create a two Scenario one is “Best Case” and another one is “Worst Case” scenario. The best-case scenario would show what the budget figures would look like if sales were especially poor.
Below is a data set where I have made a trading account of the company.
To generate I have use formulas in the particular cell. For your reference, I have added the formulas below
To make the above table, use these formulas in the particular cell and where is no formula you have to put the values: –
| Cell | Formula Applied |
| A7 | =IF(C8=“”,””,”Gross Profit”) |
| C8 | IF(SUM(F3:F7)>SUM(C3:C7),SUM(F3:F7)-SUM(C3:C7) |
| D8 | IF(D8=“”,””,”Gross Loss”) |
| F8 | IF(SUM(C3:C7)>SUM(F3:F7),SUM(C3:C7)-SUM(F3:F7) |
| C10 | =SUM(C3:C8) |
| F10 | =SUM(F3:F8) |
To create a Scenario, select the corresponding cells on the worksheet and click on
Data Tab ->Data group -> What-If Analysis -> Scenario Manager,
The Scenario Manager dialog box as Figure below will appear:
Click on Add button.
The Add Scenario dialog box is displayed as shown in the figure. Give a Name to the scenario and define the cell references where you wish to make changes.
Enter additional details about the scenario in the Comment field. Click OK.
The Scenario Values dialog box is displayed to change the current data of the selected cells to your target. Change the values as required. Click OK to return to the Scenario Manager otherwise clicks Add to define another scenario after saving the previous one.
To display a scenario effect, open the Scenario Manager again using Data Tab -> Data group -> What-If Analysis -> Scenario Manager. Choose a scenario from the list and click the Show button to display the scenario results in your worksheet.
If you want to compare all scenarios in a view, click the Summary button on the Scenario Manager Dialog box. Another worksheet will open having a name Scenario Summary that will give you a details comparison chart between all existing scenarios with original values. To remove any scenario, select the corresponding scenario from the Scenario Manager Dialog box and click on the Delete button.
