If you click on the summary option, you can create a whole summary as well.Īfter clicking on the summary, you will find a new dialog box. Now that you have added all the values, you have three different scenarios, namely
To update values, follow the same procedure as before click on add and edit the values in the new dialog box and press OK as shown below. Now, update the values for Band B and select OK as shown below.Īnd the last step is to create a scenario for Band A. You can select the add option, and you will have a new dialog box with editable data options. Similarly, you can add two more scenarios: Band B and Band A in this example. The next screen will show a small dialog box with all the values ready for Band C, as shown below. Here, we have chosen Band C as the name for this Scenario. Type a name for the Scenario and press OK. Then, a new dialog box will appear on the screen with cell addresses. To add a scenario, select add option as shown below. Go to the Data option from the toolbar and select the Excel What-if Analysis option from the Forecast Ribbon, as shown below.Ī dialog box will appear on the screen with empty scenarios. Select the cells consisting of numerical data and ignore the cells with mathematical formulas as shown below. Now, let us use this table and the Scenario manager from Excel What-if Analysis to recreate the same table for Band B and Band A employees. Cells B8, B16, and B18 include a mathematical formula that calculates the summation of the other cells above them and states them as "Compensation from Company", “Maintenance Bill Per Employee", and "Total Expense to Company", respectively. The following image depicts the details of Band C Employees. You can also generate the summary of all the three tables in one sheet in the form of a detailed visualization. The whole idea of using Scenario manager is to avoid duplication of similar data. This time-consuming process can be eliminated by using Scenario manager. The brute force method of excel would recommend creating three different tables with similar data and input the values. The Scenario Manager in What-if Analysis Excel is used to consume the original data and the mathematical formulas implemented on the data to recreate another scenario that inherits similarities from the previous table and generates a new table.įor better learning, let us consider that you are running an IT company and you have employees from three different bands, namely:Įmployees of different bands have different compensations, Basic Pay, and HRAs. We have three types of What-if Analysis as shown below. it applies to Excel 2013 and Excel 2016.Excel What-if Analysis is a procedure employed to the Excel sheets with formulas to see the tabular data results when any variations are applied to the original values without having to recreate a new sheet. See Microsoft documentation for more information. Quick Analysis is a similar set of tools available in Excel 2013. The button will open the Data Analysis dialog, which offers access to a variety of analysis tools.
Go back to the first screenshot in the instructions to see how it will look. Once the add in has been successfully installed you will see data analysis when you click on the data tab (usually to the far right of the toolbar).
If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it. Tip: If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.In the Manage box, select Excel Add-ins and then click Go.Click the File tab, click Options, and then click the Add-Ins category.
These instructions apply to Excel 2010, Excel 2013 and Excel 2016. If the Data Analysis command is not available in your version of Excel, you need to load the Analysis ToolPak add-in program.