Monday, March 9, 2015

Scenario Analysis in Excel With Option Buttons and the What-If Scenario Manager

Scenario Analysis in Excel

With Option Buttons and

the What-If Scenario

Manager

Two excellent tools in Excel to conveniently perform scenario analysis are Option Buttons and the What-If Scenario Manager. Scenario analysis in Excel involves switching different sets of input values into the same set of formulas to compare the differences in outcome. The classic example is the Best Case-Expected Case-Worst Case set of scenarios. This article will demonstrate in detail how to implement this set of scenarios with both the options buttons and with the What-If Scenario Manager. Here is a brief description of each:

 

Scenario Analysis With Option Buttons – Formerly called a Radio Button, the Option Button to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are common methods employed to implement scenario selection. Both of these techniques will be demonstrated in this article. Switching between different scenarios is as simple as selecting a different option button. An example using options buttons to implement scenario selection is shown as follows:

Scenario Analysis in Excel - Option Button Scenarios
(Click On Image To See a Larger Version)

 

Scenario Analysis With the What-If Scenario Manager -

The Scenario Manager is a tool found under the What-If Analysis section which is located under the Data tab. The Scenarios are created and stored within the Scenario Manager. When an individual scenario is selected within the Scenario Manager, the value in the output cell is changed to the value specified by the scenario as follows:

Scenario Analysis in Excel - Select Scenario
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - Select Formulas
(Click On Image To See a Larger Version)

The Scenario Manager must be brought up every time that a scenario change is desired. Option buttons are always visible making them a more convenient option to switch between scenarios.

 

Turning On the Developer Tab in the Excel Ribbon

Form controls are accessed from the Developer tab. By default the Developer tab does not appear in the ribbon. The Developer tab must be configured to be one of the tabs permanently displayed in the ribbon. This can be quickly implemented with the following steps:

File / Options / Customize Ribbon

This will produce the following dialogue box. The checkbox next to Developer will initially be unchecked. Simply check that checkbox and the Developer tab will become a permanently visible part of the ribbon as long as that checkbox remains checked. This is shown in the following images.

Scenario Analysis in Excel - Form Control Options
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - Developer Tab
(Click On Image To See a Larger Version)

The Developer tab now appears as a permanent part of the ribbon as long as the checkbox next to Developer remains checked. This is shown as follows:

Scenario Analysis in Excel - Developer Form Controls
(Click On Image To See a Larger Version)

All form controls are made available clicking Insert in the Developer tab.

Scenario Analysis in Excel - Select Form Control
(Click On Image To See a Larger Version)

 

Scenario Analysis With Option Buttons

Formerly called a Radio Button, the Option Button is used to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are common methods employed to implement scenario selection. Both of these techniques will be demonstrated in this article.

The option button is creating by clicking the option button icon in the Form Controls menu as follows:

Scenario Analysis in Excel - Option Button
(Click On Image To See a Larger Version)

When the option button icon is clicked, the cursor become the tool to place the option button on the Excel worksheet. The outline of the option button is created by clicking and then dragging the cursor. The location and dimensions of the option button can be changed at any time after the initial option button has been created. The initial option button will appear as follows:

Scenario Analysis in Excel - Create Option Button
(Click On Image To See a Larger Version)

The option button must now be formatted. This is accomplished by right-clicking anywhere in the option button to bring up the following short-cut menu. Format Control is selected from the short-cut menu. This brings up the Format Control dialogue box. The cell which will hold the output of the option button must now be specified. In this case that cell is A4.

Each new option button that is created will be part of the initial group of option buttons. All option buttons in the same group will collectively send their output to the same cell. Only one option button in a group can selected at any time. The output cell, A4 in this case, will contain the number that is associated with and unique to the specific option button that has been selected.

Scenario Analysis in Excel - Format Option Button
(Click On Image To See a Larger Version)

The first completed options button and output cell appear as follows

Form_Controls_24_Option_Button_1_400
(Click On Image To See a Larger Version)

A second options button can now be created. This second options button will be part of the same group that contains the first options button. The Format Control dialogue box that will appear when the second dialogue box is right-clicked as follows. By default the value of the second options buttons will initially be Unchecked and the output will be linked to the same output cell of the first options box, which is A4.

Changing the output cell in the Format Control of an options button will change the location of the output cells for all options buttons in the same group.

Scenario Analysis in Excel - Format Option Button
(Click On Image To See a Larger Version)

A third options button can be added to the group as follows:

v
(Click On Image To See a Larger Version)

The labels associated with each options button can be renamed by right-clicking on the original label and then typing in the new label. The result might be as follows:

Scenario Analysis in Excel - Renaming Option Buttons
(Click On Image To See a Larger Version)

These three options buttons can be assigned to a single group by selecting the Group Box from the list of Format Controls.

Scenario Analysis in Excel - Insert Group Box
(Click On Image To See a Larger Version)

When the group box icon is clicked, the cursor become the tool to place the group box on the Excel worksheet. The outline of the group box is created by clicking and then dragging the cursor. The location and dimensions of the check box can be changed at any time after the initial check box has been created. Any options buttons that are inside the group box are part of one group. Any options buttons that are outside of the group box are part of a different group. The initial group box will appear as follows:

Scenario Analysis in Excel - 1st Group
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - New Group
(Click On Image To See a Larger Version)

The Option Button is designed to create the ability to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are straightforward methods to implement scenario selection. An example of each of the two methods are shown as follows:

Scenario Analysis in Excel - Option Button Scenario 1
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - Option Button Scenario 1 Closeup
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - Option Button Scenario 2
(Click On Image To See a Larger Version)

Conditional formatting was applied to display negative values in red instead of black as shown as follows:

Scenario Analysis in Excel - Option Button Scenario 3
(Click On Image To See a Larger Version)

 

Scenario Analysis With the What-If Analysis Scenario Manager

The Scenario Manager is a tool found under the What-If Analysis section which is located under the Data tab. The Scenarios are created and stored within the Scenario Manager. When an individual scenario is selected within the Scenario Manager, the value in the output cell is changed to the value specified by the scenario.

Creating a Scenario in the What-If Scenario Manager

The Scenario Manager is one of the tools in the drop-down menu under What-If Analysis, which is under the Data tab.

Scenario Analysis in Excel - Select What-If
(Click On Image To See a Larger Version)

The following empty dialogue box appears when Scenario Manager has been selected.

Scenario Analysis in Excel - Scenario Manager Dialogue Box
(Click On Image To See a Larger Version)

Add a scenario by selecting Add. This brings up the following Edit Scenario dialogue box, which has been filled in with the basic information about the first scenario titled Best Case. The output cells for this scenario is A4.

Scenario Analysis in Excel - 1st Scenario
(Click On Image To See a Larger Version)

The value that will appear in this output cell when the scenario is set in the Scenario Values dialogue box, which appears after OK is clicked in the Edit Scenario dialogue box.

Scenario Analysis in Excel - 1st Scenario Value
(Click On Image To See a Larger Version)

A second scenario titled Most Expected Case is created in a very similar fashion. In this case, the output cell, A4, will display a 2 when this scenario is selected.

Scenario Analysis in Excel - 2nd Scenario
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - 2nd Scenario Value
(Click On Image To See a Larger Version)

Finally a third scenario is added titled Worst Case. This will also be linked to cell A4 and will produce the value of 3.

Scenario Analysis in Excel - 3rd Scenario
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - 3rd Scenario Value
(Click On Image To See a Larger Version)

The output cell A4 can control which set of input values are applied to the analysis through the use of If-Then-Else statements or CHOOSE() commands as follows:

Scenario Analysis in Excel - Scenario Formulas
(Click On Image To See a Larger Version)

Scenarios_13_Scenario_Formulas_Closeup_600
(Click On Image To See a Larger Version)

The scenario is selected from the What-If Scenario Manager as follows:

Scenario Analysis in Excel - Select What-If
(Click On Image To See a Larger Version)

Scenario Analysis in Excel - Select Scenario 2
(Click On Image To See a Larger Version)

This sets the value of cell A4 to 2.

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!

13 comments:

  1. Valuable information about Excel with option button sharing here .Great post. I used to be checking always this weblog and I am impressed. Very useful info, Specially the last time I take care of such information a lot. I wish to advise you some interesting things or tips. I used to be looking for this optimistic information for a long time. Thanks and good luck. I wish for to read more things about it.

    ReplyDelete
  2. Actually i don't get a thing from the article you provide here. I always have a support for such cases. If I am not sure about the content of my paper I ask https://essaysprofessor.com/movie-critique-writing-service.html for writing assistance. It always works out in the best resut.

    ReplyDelete
  3. Two Tips For Better TOEFL Essay Topics - Getting Started Writing
    TOEFL essay topics are basically short written examinations for university students. They are typically used by most colleges as a part of their admission procedure. To be an effective English student, however, it's extremely important to read https://www.the-essays.com/research-proposal
    and to know and learn the important concepts and practical application behind the essay writing process.The first tip in TOEFL essay writing is to start writing on one side only. Don't start reading your essay writing on the second side or start incorporating other text onto the first side before you've even finished the introduction or the body of your assignment. You can always change your mind about what side you're going to start writing on later, but don't do it before you've even started! It's tempting to want to rush through everything, but rushing through will just negatively affect your chances of being a better writer. Instead, pick one side to start writing, then proceed to the next.The second tip in TOEFL essay topics is to read your two essays back to back. This will allow you to familiarize yourself with the structure and flow of each one. If you have a clear idea of what the focus of one essay is, it will help you be able to tell what is going on in the other one. For instance, if the focus of your first essay is on the concept of love and relationships, but the second one you're working on is about immigration laws, chances are you'll notice a commonality between the two. If they both start out talking about how people meet, then you can probably assume that the topic of the second is about marriage. By reading your two essays back to back, you will be able to catch these subtle similarities, as well as many of the style and rules that are shared between the two.

    ReplyDelete
  4. Let's analyze the theory of synopsis. Review of the subject without detailed argumentation. I think it is convenient and necessary to review erudite people. Writing a synopsis requires a brief description of the chosen topic. All the nuances you can understand here!

    ReplyDelete
  5. เกมไลฟ์ดีลเลอร์บางเกมถูกสตรีมจากคาสิโนบนบกจริง https://bk8casino.org/ ทำให้ผู้เล่นได้รับประสบการณ์การเล่นเกมที่แท้จริงซึ่งไม่ด้อยไปกว่าเกมบนบก

    ReplyDelete
  6. Thanks for the article. The article is very informative and can teach you how to use this program. It can also be used as a sample research paper for high school students. I think this information will be needed for future students.

    ReplyDelete
  7. แฟน ๆ ของโป๊กเกอร์ชอบเล่นเกมวิดีโอโป๊กเกอร์ออนไลน์ เกมเหล่านี้น่าเล่นและเร็วกว่าเวอร์ชันอิฐและปูน https://bk8slots.com/ ด้วยวิดีโอโป๊กเกอร์ออนไลน์ คุณกำลังเล่นเกมโป๊กเกอร์สไตล์สล็อตแมชชีน เช่นเดียวกับเกมคาสิโนอื่น ๆ มีเกมให้เลือกมากมาย

    ReplyDelete
  8. ข้อได้เปรียบที่ชัดเจนของการมีตัวเลือกการชำระเงินที่มากขึ้นคือ คุณสามารถฝากเงินได้จากทุกที่ที่คุณต้องการ https://ivip9-casino.com/ แม้ว่าคุณจะกำลังเดินทาง แต่ให้แน่ใจว่าคุณให้ข้อมูลที่เป็นข้อเท็จจริงของคาสิโน เนื่องจากการถอนเงินมักจะถูกโอนเข้าบัญชีเดียวกันกับที่คุณทำการฝากเงินเท่านั้น

    ReplyDelete
  9. We have smart developers who have an intimate understanding and right knowledge of the key unique selling propositions of the product and the services

    ReplyDelete