What-If-Analysis - Scenario Manager
In this last session of what-if-Analysis for Excel we are going to discuss Scenario Manager. In case you missed the first two part you can use the link below to view themFor using Goal Seek in Excel use the link below -
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-1-goal.html
For using Data Tables in Excel use the link below -
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-2-data.html
Scenario Manager
Scenario Manager allow you to create a series of possible drivers to show in your model. The effect on Many variable on the model for different scenarios
Using Scenario Manager
The problem we are going to discuss is the same.
Where quantity is quantity sold , Unit Price is the cost to make each item , cost price is selling price . Revenues is Quantity * Cost price , CoGS(cost of goods sold) is -Quantity* Unit Price , fixed cost is some cost to produce items. Assuming we dont have any other expense or profit the
Net Profit=Revenues + CoGS+Fixed Cost
Now what we want to see is how would Net Profit change if we change other input variables and we want to store result of each change.The steps are as follows.
1.Click the Datatab, click What-if-Analysis, and choose Scenario Manager . The scenario Manager Dialog is displayed below
2.The Scenario Manager dialog box is shown below.
3. Click on Add to add scenarios.
4. Now in the box scenario name click give the name of the scenario , in our case we will call it BreakEven( Net Profit is 0). Next add the cells you want to change in Changing Cells you can select a full array or can give them one by one, in my case I am giving it as D2(Quantity), D4(Unit_price) and D5(Cost Price) as shown above.Once you click OK you get a window called Scenario Values which will show you the current values of the three cells you entered. Now you can change the values according to your scenarios and make as many scenarios as you want.
5. Once you have created scenarios all the scenarios will be listed one by one as shown below, you can edit , delete or view them one by one by using show.
6. Now to view all the scenarios at once and see how they are impacting revenue ,CoGS and net profit. You can click on Summary button .This button will ask you what you want to display in the summary , basically all the values you want to see the change on.Enter all the cells you want to see in summary in the Result cells as an array or separated by comma as shown below. Now select the report type ,in our case we have selected the scenario summary which gives me a plain and simple report . Click OK
7.Finally you will see a new sheet automatically created called Scenario Summary and would show the impact on all the result cells.Here is the look on our scenario summary sheet.
As you can see the Scenario Manager is really useful in seeing the impact of variables and also helps to store them in form of scenarios which can be later edited according to the users need . It is a very powerful tool and is often used in financial analysis to display different scenarios.
Hope this article was useful . That concludes our what-if-Analysis for excel. Do look out for more excel tips and also let me know if you want me to write on something specific.
Now what we want to see is how would Net Profit change if we change other input variables and we want to store result of each change.The steps are as follows.
1.Click the Datatab, click What-if-Analysis, and choose Scenario Manager . The scenario Manager Dialog is displayed below
2.The Scenario Manager dialog box is shown below.
4. Now in the box scenario name click give the name of the scenario , in our case we will call it BreakEven( Net Profit is 0). Next add the cells you want to change in Changing Cells you can select a full array or can give them one by one, in my case I am giving it as D2(Quantity), D4(Unit_price) and D5(Cost Price) as shown above.Once you click OK you get a window called Scenario Values which will show you the current values of the three cells you entered. Now you can change the values according to your scenarios and make as many scenarios as you want.
5. Once you have created scenarios all the scenarios will be listed one by one as shown below, you can edit , delete or view them one by one by using show.
6. Now to view all the scenarios at once and see how they are impacting revenue ,CoGS and net profit. You can click on Summary button .This button will ask you what you want to display in the summary , basically all the values you want to see the change on.Enter all the cells you want to see in summary in the Result cells as an array or separated by comma as shown below. Now select the report type ,in our case we have selected the scenario summary which gives me a plain and simple report . Click OK
7.Finally you will see a new sheet automatically created called Scenario Summary and would show the impact on all the result cells.Here is the look on our scenario summary sheet.
As you can see the Scenario Manager is really useful in seeing the impact of variables and also helps to store them in form of scenarios which can be later edited according to the users need . It is a very powerful tool and is often used in financial analysis to display different scenarios.
Hope this article was useful . That concludes our what-if-Analysis for excel. Do look out for more excel tips and also let me know if you want me to write on something specific.
No comments:
Post a Comment