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:
(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:
(Click On Image To See a Larger Version)
(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.
(Click On Image To See a Larger Version)
(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:
(Click On Image To See a Larger Version)
All form controls are made available clicking Insert in the Developer tab.
(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:
(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:
(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.
(Click On Image To See a Larger Version)
The first completed options button and output cell appear as follows
(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.
(Click On Image To See a Larger Version)
A third options button can be added to the group as follows:
(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:
(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.
(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:
(Click On Image To See a Larger Version)
(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:
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
(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:
(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.
(Click On Image To See a Larger Version)
The following empty dialogue box appears when Scenario Manager has been selected.
(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.
(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.
(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.
(Click On Image To See a Larger Version)
(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.
(Click On Image To See a Larger Version)
(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:
(Click On Image To See a Larger Version)
(Click On Image To See a Larger Version)
The scenario is selected from the What-If Scenario Manager as follows:
(Click On Image To See a Larger Version)
(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!
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.
ReplyDeleteActually 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Đặt vé máy bay tại đại lý Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ
giá vé máy bay từ mỹ về việt nam
săn vé máy bay đi nha trang
giá vé đi phú quốc
ve may bay di Hue gia re
Two Tips For Better TOEFL Essay Topics - Getting Started Writing
ReplyDeleteTOEFL 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.
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!
ReplyDeleteYou have done a amazing job with you website
ReplyDeletedata science training in pune
I really enjoy reading and also appreciate your work.
ReplyDeletedata science courses aurangabad
เกมไลฟ์ดีลเลอร์บางเกมถูกสตรีมจากคาสิโนบนบกจริง https://bk8casino.org/ ทำให้ผู้เล่นได้รับประสบการณ์การเล่นเกมที่แท้จริงซึ่งไม่ด้อยไปกว่าเกมบนบก
ReplyDeleteThanks 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แฟน ๆ ของโป๊กเกอร์ชอบเล่นเกมวิดีโอโป๊กเกอร์ออนไลน์ เกมเหล่านี้น่าเล่นและเร็วกว่าเวอร์ชันอิฐและปูน https://bk8slots.com/ ด้วยวิดีโอโป๊กเกอร์ออนไลน์ คุณกำลังเล่นเกมโป๊กเกอร์สไตล์สล็อตแมชชีน เช่นเดียวกับเกมคาสิโนอื่น ๆ มีเกมให้เลือกมากมาย
ReplyDeleteข้อได้เปรียบที่ชัดเจนของการมีตัวเลือกการชำระเงินที่มากขึ้นคือ คุณสามารถฝากเงินได้จากทุกที่ที่คุณต้องการ https://ivip9-casino.com/ แม้ว่าคุณจะกำลังเดินทาง แต่ให้แน่ใจว่าคุณให้ข้อมูลที่เป็นข้อเท็จจริงของคาสิโน เนื่องจากการถอนเงินมักจะถูกโอนเข้าบัญชีเดียวกันกับที่คุณทำการฝากเงินเท่านั้น
ReplyDeleteWe have smart developers who have an intimate understanding and right knowledge of the key unique selling propositions of the product and the services
ReplyDeleteYou shared an informative blog!
ReplyDeleteIf you are searching for garage door openers Laurel MD area, you have come to the right place. Laurel offers a wide range of options for garage door openers, catering to the diverse needs of homeowners and businesses alike. From traditional chain-driven openers to quieter belt-driven models, Laurel provides a comprehensive selection that ensures you'll find the perfect fit for your garage door system.
ReplyDeleteWith the advancement of technology, garage door openers in Laurel now come with various convenient features that enhance functionality and improve security. These features include Wi-Fi connectivity, battery backup systems, and smart home integration, allowing you to control your garage door effortlessly from your smartphone or integrate it into your home automation system.
代写服务的使用不应成为学生逃避学术任务的借口，而应当与个人的学习计划相结合，形成一种平衡。学生在使用代写服务时，可以将其视为自己学习计划中的一部分，合理分配时间与精力。例如，在时间紧迫的情况下，可以选择将部分较为困难的作业交给代写https://www.lunwenhelp.com/服务处理，从而腾出时间专注于自己擅长的领域或其他更为重要的学术任务。这种策略不仅能减轻学生的学术压力，还能保证个人学习的连续性和全面性。通过合理安排代写服务的使用，学生能够更有效地管理时间，提高整体学习效率。
ReplyDelete