CA Loan Bajaj Finserv
CA Final Online Classes
CA Classes

Share on Facebook

Share on Twitter

Share on LinkedIn

Share on Email

Share More

Scenario Manager in Excel- A step towards Advance Excel

CA Kumar Mukesh 
on 22 December 2014

LinkedIn


LEARNING SCENARIO MANAGER IN EXCEL "A STEP TOWARDS ADVANCE EXCEL"

Scenario Manager is a common top-down analytical approach where numerous inputs are modified at a time, consistent with a common theme, and corresponding outputs are then analyzed. 

Sometime in Excel Sheet we have to make data analysis under different conditions and criteria and then output is analyzed. For Example in budgeted profit computation, Net Profit may require to be compute under various variables like Cost of Material labour and Tax Rates etc. in that cases someone ma find certain amount of difficulties if he/she attempt by traditional methods. However by using Scenario Manager option can be prove very handy. 

How it works let see 

Suppose we have the following type of problem in which we have to calculate profit under Set-1, Set-2 and Set-3 assumptions as mentioned below*

*The profit compuated in base workings are linked to Set-1 assumptions only 

For handling such types of problems we have to follows the following steps

Step-1 Select "Data Tab" and go to "what is analysis" and select "Scenario Manager"

Step: 2 Select "Add" 

Step: 3 Give the scenario name "Set-1" and select the changing cell range "A:18 to B:25" and click "OK"

Step:4 Select OK

After the Step 4 it will be appear like that

Step:5 Copy the area of Set-2 and paste over the area of Set -1

Step:6 Go to again "Data Tab" then select "What if Analysis" and then "Scenario Manager" and click ADD

Step: 7 Give the scenario name Set-2 and select the changing cell range "A:18 to B:25" and click ok

Step 8: Click OK

After the Step 8 it will be appear like that

Step:9 Copy the area of Set-3 and paste over the area of Set -1

Step:10 Go to once again Data Tab and select whatif analysis and then Scenario Manager and click ADD

Step:11 Give the scenario name Set-3 and select the changing cell range "A:18 to B:25" and click ok

Step:12 select OK

After the Step 12 it will be appear like that

Now Select Set-1/2/3 and click

You will see that all relevant details are updated instantly.

The above excel file can also be from 

Scenario Manager File

You may also post you queries in Excel at camukeshkumar@consultant.com

Note: There may be some visuality problem, however if you following the steps only you still able understand the usage of "Scenario Manager"


Tags :



Category Info Technology
Other Articles by -
CA Kumar Mukesh 

Report Abuse

LinkedIn



Comments


update