Excel Scenario manager and target value search
With Excel, you not only have a spreadsheet with which you can perform accurate calculations.
But you can also present different scenarios for assumptions of a situation with the “what if” analysis, without having to enter all possible combinations on spreadsheets to map them.
In our contribution, we would like to present the “target value search” and the “scenario manager” from the “what if” analysis using small practical examples.
You can find out how to use them in Microsoft Excel in our article.
Excel Scenario manager and target value search
With Excel, you not only have a spreadsheet with which you can perform accurate calculations.
But you can also present different scenarios for assumptions of a situation with the “what if” analysis, without having to enter all possible combinations on spreadsheets to map them.
In our contribution, we would like to present the “target value search” and the “scenario manager” from the “what if” analysis using small practical examples.
You can find out how to use them in Microsoft Excel in our article.
1. The operation of the Excel target value search
1. The operation of the Excel target value search
In our example we represent the purchase of a certain amount of an article plus surcharges and the sale plus profit surcharge.
In this case, different values (for example, the selling price) should reach a certain target value, while only one other value may change as a result.
In the first example we would like to know how high the purchase quantity has to be at least in order to reach a selling price of € 23.50 (under otherwise identical conditions).
See picture: (click to enlarge)
In our example we represent the purchase of a certain amount of an article plus surcharges and the sale plus profit surcharge.
In this case, different values (for example, the selling price) should reach a certain target value, while only one other value may change as a result.
In the first example we would like to know how high the purchase quantity has to be at least in order to reach a selling price of € 23.50 (under otherwise identical conditions).
See picture: (click to enlarge)
2. Call up the target value search
2. Call up the target value search
After we have created a scenario with different values, we call the target value search via the register:
“Data” – “What if analysis” – “target value search” on.
See picture (click to enlarge)
The following windows are available in the dialog box, which we fill out as follows:
- target cell
(Here the cell is marked, in which the desired value is to be determined.) - target value
(The corresponding target value is entered here.) - Changeable cell
(This is the cell that is allowed to change to reach the desired target value.)
See picture (click to enlarge)
After clicking on “OK”, the value in the changeable cell (in our example, the purchase quantity) is changed until the desired target value has been reached.
It goes without saying that the total value of the EK as well as other values dependent on the purchasing quantity also change here.
But not static values like our profit surcharge in% or the lump sum surcharges.
See picture (click to enlarge)
After we have created a scenario with different values, we call the target value search via the register:
“Data” – “What if analysis” – “target value search” on.
See picture (click to enlarge)
The following windows are available in the dialog box, which we fill out as follows:
- target cell
(Here the cell is marked, in which the desired value is to be determined.) - target value
(The corresponding target value is entered here.) - Changeable cell
(This is the cell that is allowed to change to reach the desired target value.)
See picture (click to enlarge)
After clicking on “OK”, the value in the changeable cell (in our example, the purchase quantity) is changed until the desired target value has been reached.
It goes without saying that the total value of the EK as well as other values dependent on the purchasing quantity also change here.
But not static values like our profit surcharge in% or the lump sum surcharges.
See picture (click to enlarge)
3. The scenario manager
3. The scenario manager
With the scenario manager, you can quickly display a variety of very complex scenarios with predefined values, without having to make any further entries, or enter all the options on the worksheet.
In our very small example, we have created a fixed investment amount for which the interest income should be displayed at different interest rates.
To call the scenario manager, proceed as follows:
- Data tab – “What if analysis”.
- And select the item “scenario manager” there.
See picture (click to enlarge)
With the scenario manager, you can quickly display a variety of very complex scenarios with predefined values, without having to make any further entries, or enter all the options on the worksheet.
In our very small example, we have created a fixed investment amount for which the interest income should be displayed at different interest rates.
To call the scenario manager, proceed as follows:
- Data tab – “What if analysis”.
- And select the item “scenario manager” there.
See picture (click to enlarge)
4. Create a new scenario
4. Create a new scenario
After calling up the scenario, you can create different scenarios with different prerequisites.
To do this, click on “Add” in the scenario manager dialog box.
Then assign the scenario a comprehensible name for the scenario and set the modifiable cell (s).
In our example, we use only one changeable cell with the percentage.
Of course, you can set any number of changeable cells by selecting them on the worksheet.
By clicking on “OK” we are asked to set the values for the corresponding scenario, which can be displayed later.
You can set any number of scenarios by clicking Add after setting a scenario instead of OK, and then repeat the previous steps.
See picture: (click to enlarge)
After calling up the scenario, you can create different scenarios with different prerequisites.
To do this, click on “Add” in the scenario manager dialog box.
Then assign the scenario a comprehensible name for the scenario and set the modifiable cell (s).
In our example, we use only one changeable cell with the percentage.
Of course, you can set any number of changeable cells by selecting them on the worksheet.
By clicking on “OK” we are asked to set the values for the corresponding scenario, which can be displayed later.
You can set any number of scenarios by clicking Add after setting a scenario instead of OK, and then repeat the previous steps.
See picture: (click to enlarge)
5. View the specified scenarios
5. View the specified scenarios
To call up and display your previously defined scenarios, simply click on the tab again in the corresponding sheet:
“Data” – “What if analysis” on “Scenario Manager”.
It displays your named scenarios, which you can easily display on your worksheet by either double-clicking on the respective scenario, or highlight, and “Show” button.
See picture (click to enlarge)
You will find that this tool can be used in a very useful and, above all, space-saving way to view the data without having to click through a large number of worksheets.
To call up and display your previously defined scenarios, simply click on the tab again in the corresponding sheet:
“Data” – “What if analysis” on “Scenario Manager”.
It displays your named scenarios, which you can easily display on your worksheet by either double-clicking on the respective scenario, or highlight, and “Show” button.
See picture (click to enlarge)
You will find that this tool can be used in a very useful and, above all, space-saving way to view the data without having to click through a large number of worksheets.
Popular Posts:
Create a Word letter template with form fields
Create a professional DIN 5008 letter template with form fields and drop-down lists in Microsoft Word, and write letters as easily as you would fill out a form.
Protect Word documents from editing
Protect your documents in Microsoft Word from unauthorized editing or block access completely.
Show and remove duplicate entries in Excel | Tutorial
We explain how to remove duplicate entries from Excel. And also how you can only display duplicate entries in Excel but not delete them immediately in order to decide on a case-by-case basis.
Create vCard with Business Photo in Outlook
Whereas digital business cards used to be used primarily in e-mail programs such as Outlook, today they are increasingly being sent via e-mail, SMS or messenger services such as WhatsApp to smartphones
The Xreference function – difference to Sreference and Wreference
Pretty much everyone who uses Excel knows the Vreference and the Wreference. On the other hand, something newer is the X reference, which can be used to iron out some of the weaknesses of the functions used up to now. We explain the differences between Xreference and Sreference & Wreference.
Custom Formatting Excel – Number Format Codes Excel
We use a practical example to explain the importance and possible uses of custom formatting in Excel. Number format codes in Excel are not rocket science!
Offers 2024: Word & Excel Templates
Popular Posts:
Create a Word letter template with form fields
Create a professional DIN 5008 letter template with form fields and drop-down lists in Microsoft Word, and write letters as easily as you would fill out a form.
Protect Word documents from editing
Protect your documents in Microsoft Word from unauthorized editing or block access completely.
Show and remove duplicate entries in Excel | Tutorial
We explain how to remove duplicate entries from Excel. And also how you can only display duplicate entries in Excel but not delete them immediately in order to decide on a case-by-case basis.
Create vCard with Business Photo in Outlook
Whereas digital business cards used to be used primarily in e-mail programs such as Outlook, today they are increasingly being sent via e-mail, SMS or messenger services such as WhatsApp to smartphones
The Xreference function – difference to Sreference and Wreference
Pretty much everyone who uses Excel knows the Vreference and the Wreference. On the other hand, something newer is the X reference, which can be used to iron out some of the weaknesses of the functions used up to now. We explain the differences between Xreference and Sreference & Wreference.
Custom Formatting Excel – Number Format Codes Excel
We use a practical example to explain the importance and possible uses of custom formatting in Excel. Number format codes in Excel are not rocket science!
Offers 2024: Word & Excel Templates
