Using What iF scenarios and What If tools spreadsheet
In this chapter, we will discuss Using “What If” scenarios and “What If” tools in Electronic Spreadsheet (advanced). The electronic spreadsheet has powerful advanced features to analyze data and summarise data. This data analysis helps us to make the right decisions. The contents are:
Using “What If” scenarios
The “What If” scenario feature allows us to create different target-based “What If” analysis. Suppose we are playing a clicket tournament where we need to analyze our team performance on the net run rate we can use “What If” scenarios. Moreover, it can help in every match to achieve the target in different conditions. These different conditions mean how many runs are required in how many overs to qualify on top of the point table? How many runs need to be saved to climb up in the point table? etc.
You can create scenarios with different names and targets. These scenarios you can view any time from the scenarios manager.
Using “What If” scenarios in MS Excel
MS Excel provides What-If-Analysis command in the Data tab to use “What IF” scenarios. Prepare your data in advance and open the worksheet then follow below given steps. I have used the following data for this purpose.
Step 1: Select data and click on Data → What – if – Analysis → Scenario Manager. Observe the following screenshot:
Step 2: Click on the scenario manager. The Scenario Manager dialog box appears as displayed in the following screenshot.
Scenario Manager dialog box in Excel
This dialog box has the following options:
Scenario Details Preview: It shows the details of the scenarios.
Add: You can add new scenario by clicking this option.
Delete: You can delete an existing scenario that is unwanted.
Edit: You can edit an existing scenario.
Merge: It is useful, If more than one scenario needs to be added
Summary: It shows a summary of the scenario.
Changing Cells: Select the cells in which values are going to be changed.
Comment: If any additional text is required then you can insert it.
Show: Display the information of the selected scenario.
Close: To close the scenario manager.
Step 3: Click on the Add button. Add scenario dialog box will appear as displayed in the below-given screen shot.
Add scenario dialog box
Type the scenario name as I have scenario1, you can change cells by selecting changing cells options. Type your comments if you want to type in the comment box. Click on the OK button. Scenario values dialog box appears as displayed in the following dialog box.
Scenario Values Dialog box
Step 4: Now enter the target values in the respective cells. My target which I want to use for this scenario is 120 runs in 10 overs and want to concede 90 runs in 10 overs to see the net run rate variations. So I have changed these values respectively.
Step 5: Click on the Add button. Repeat the same steps for more scenarios.
Check the result of the scenario
To check the scenario result follow these steps:
Step 1: Open the scenario manager.
Step 2: Select the scenario which results you want to check. Click on the scenario name.
Step 3: Click on show button.
Scenario Manager to check result
The result will be displayed in the excel worksheet.
Using “What If” scenarios in OO calc
Consider similar data as used in MS Excel example. Follow the below-given steps to use the “What-if” scenario in OO Calc.
Step 1: Type the required values in the excel sheets and click on Tools → Scenarios option.
Tools Scenario in OO Calc
Step 2: A create scenario dialog box appears as displayed in the following screenshot.
create a scenarios dialog box in OO calc
Create scenario dialog box options
Name of Scenario: Type the name of the scenario.
Comment: Type the remarks or comments related to the scenario.
Settings: It allows to change the display border color, copy the contents, copy the entire sheet as well as the prevention of changes.
OK: When set up is complete click OK button.
Step 3: Apply the settings you need in your current scenario and click on OK button. I have applied a blue border to a scenario and changed the value runs scored 80 and runs conceded 75 and the result is 0.5. Observe the following screenshot.
You can use a down arrow to view more scenario results.
Multiple Operations in OO Calc
The scenario allows us to perform a task on a single set of values at a time. But what if you want to see the multiple results at the same time? So OO calc provides a strong feature named Multiple operations to do the same thing. You can set input row values according to the row or columns.
Type the target values in the desired cells in a single column. I have typed the assumed target in a column displayed in the screenshot.
data Multiple operations in OO Calc
Select the data along with the adjacent column and follow the below-given steps:
Step 1: Click on Data → Multiple Operations. Multiple operations dialog box appears have look in the following screenshot.
Multiple Operations dialog box in OO Calc
This dialog box consists of three options:
Formulas: Select the cell where formula is entered.
Row input cell: Selectthe cell for the input values that are entered in rows.
Column input Cell: Selectthe cell for the input values that are entered in columns.
Step 2: As per my data, the values are stored in a column and the formula is entered in B6. For column input cell I have selected the cell B2. Observe the following screenshot.
Multiple Operations data selection
Step 3: Click on the OK button. You will get the desired result. Observe the following screenshot.
Multiple Operations result with column input cells
Similarly, you can type the values in a row and check multiple operations result accordingly.
Watch the complete video tutorial for the same. If you have any queries or doubts, please comment in the comment section.
Sharing is a wonderful thing, Especially to those you've shared with. Share this: