What is the use of Scenario function in MS Excel?
This query is : Resolved
17 June 2008
Can any one tell me the best use of Scenario function in Ms excel?
01 October 2009
Excel's Scenario Manager is a tool that can be used to determine different projected outcomes of data by changing different cells within a Worksheet model.
01 October 2009
A scenario is a specific set of values that Excel can save for you and automatically substitute into your Worksheet. This means that you could have a spreadsheet displaying numerical data that is relevant to a certain date, month, topic or whatever and using the Scenario Manager you can enter different values into the worksheet to forecast the outcome of the data. These values (or Scenarios) can be retained for future use and are stored in a hidden part of the workbook which can be retrieved by asking the Scenario Manager to show the Scenario that uses those specific values.
Remember, all that Scenarios are are just a different version of the same worksheet with each different version using a different set of input values in the nominated cells.
Remember, all that Scenarios are are just a different version of the same worksheet with each different version using a different set of input values in the nominated cells.
01 October 2009
For Scenarios to work correctly, you should first set up a base or default Scenario, on a worksheet in Excel. It is from this default Scenario that all other Scenarios are defined.
1. Go to Tools>Scenarios to activate the Scenario Manager. You will see a message telling you "No Scenarios are defined".
2. Choose Add to add your default Scenario.
3. Give your Scenario a name, Base, Original or Default or a name you can easily identify will be fine.
4. Click in the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook will be referenced here.
5. Click the collapse dialog button to collapse this dialog box up so that you can easily select the cells from the Worksheet that you wish to reference.
6. Click on the collapse dialog button again to expand the full Scenario Manager box.
7. If you wish to type a comment in, then click into the Comment: box and do so.
1. Go to Tools>Scenarios to activate the Scenario Manager. You will see a message telling you "No Scenarios are defined".
2. Choose Add to add your default Scenario.
3. Give your Scenario a name, Base, Original or Default or a name you can easily identify will be fine.
4. Click in the next box Changing cells: You will notice here that the cell that was your active cell in the Workbook will be referenced here.
5. Click the collapse dialog button to collapse this dialog box up so that you can easily select the cells from the Worksheet that you wish to reference.
6. Click on the collapse dialog button again to expand the full Scenario Manager box.
7. If you wish to type a comment in, then click into the Comment: box and do so.
01 October 2009
There are two options at the bottom of this dialog box. They are Prevent changes and Hide, with Prevent changes being the default. If you select Prevent changes, then all your Scenarios will be locked and will be unable to be edited. It is important to note here that you MUST also protect your Worksheet via the Tools>Protection>Protect sheet option for this option to take effect. If you then with to edit your Scenarios, you must first unprotect your Worksheet, then de-select the Prevent changes option to proceed. The Hide option when selected will do exactly as said and Hide your Scenarios. This option also requires sheet protection for it to take effect.
8. Click the OK button. Once you click on the OK button the Scenario Values dialog box will appear. This is where you must enter values into the scenario cells. As the first scenario is your default Scenario, the values in the cells that we specified in the Changing Cells: box have been picked up so we need to make no changes here, we need only click the OK button. This will now take us back to the Scenario Manager and you will see the name of your Scenario.
8. Click the OK button. Once you click on the OK button the Scenario Values dialog box will appear. This is where you must enter values into the scenario cells. As the first scenario is your default Scenario, the values in the cells that we specified in the Changing Cells: box have been picked up so we need to make no changes here, we need only click the OK button. This will now take us back to the Scenario Manager and you will see the name of your Scenario.
01 October 2009
Adding Scenarios
There is no limit to the number of Scenarios that you can apply to your worksheet model. Adding a scenario is done in basically the same way as creating a default scenario.
1. Go to Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box.
2. Under Scenario name: give your new Scenario a name. You need make no changes to the Changing cells: box as the cells we nominated in your Default scenario show here.
3. Click OK. This will show the Scenario values dialog box and this time you will need to change the values.
4. Click OK.
You will now have two Scenarios available you.
There is no limit to the number of Scenarios that you can apply to your worksheet model. Adding a scenario is done in basically the same way as creating a default scenario.
1. Go to Tools>Scenarios to display the Scenario Manager dialog box and select Add to display the Add Scenario dialog box.
2. Under Scenario name: give your new Scenario a name. You need make no changes to the Changing cells: box as the cells we nominated in your Default scenario show here.
3. Click OK. This will show the Scenario values dialog box and this time you will need to change the values.
4. Click OK.
You will now have two Scenarios available you.
01 October 2009
Displaying Scenarios
Now you can display your Scenarios to show how they change the outcome of your data by asking the Scenario Manager to show a particular scenario.
1. Select Tools>Scenarios
2. Click on the Scenario name you want to see
3. Click Show.
4. Drag the dialog box out of the way and have a look at the values in the worksheet. One you have done this, click on the next Scenario name and then Show, and again peruse the values in the worksheet.
5. Click on Close when you no longer wish to view your Scenarios.
Now you can display your Scenarios to show how they change the outcome of your data by asking the Scenario Manager to show a particular scenario.
1. Select Tools>Scenarios
2. Click on the Scenario name you want to see
3. Click Show.
4. Drag the dialog box out of the way and have a look at the values in the worksheet. One you have done this, click on the next Scenario name and then Show, and again peruse the values in the worksheet.
5. Click on Close when you no longer wish to view your Scenarios.
You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now
Click here to login now
Similar Resolved Queries
Unanswered Queries
- Applicability
- CPC Processing IT Return for AY 2023-24 without recognizing the Taxation Option under New Regime
- Where to show the RCM on Rent paid to unregistered landlord
- Is Aadhar PAN linking required for opening bank account?
- Gst Sales of FY 2023-24
- RCM - commercial rent paid to URP under GST
- GSTR-3B - Do I file nil when there is no sale but I've bought raw materials
- Legal heir raised refund , but refund issued to the closed account of deceased.
- Offsetting interest income against derivative trading losses
- TDS NUMBER SURRENDER
- Residential status for filling income tax returns in India
- Payable outstanding for years disallowance
- Issue of gst e invoice
- Interchangeable words in accounts
- Adjusted profit/Loss in the case of LLP and Pvt. Ltd.company
- How to adjusted advances given or receipt
- Foreign vendor without PAN unable to to register due to non receipt of OTP on foreign number ?
- 133(6) DIN INVALID format error - ->WHERE TO REPLY 133(6) notice?
- Query for dubts
- Regarding Resignation by Auditor from Pvt. Ltd.
Trending Online Classes
-
Excel Mastery Program
CA Rishabh Pugalia
CAclubindia
