Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

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.

The scenario manager and the target value search in Microsoft Excel

Topic Overview

Anzeige

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.

The scenario manager and the target value search in Microsoft Excel

Topic Overview

Anzeige

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)

Zielwertsuche Excel
Advertisement

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)

Zielwertsuche Excel
Advertisement

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)

Excel 2016 Zielwertsuche aufrufen
cshow
Ads

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)

Zielwertsuche ausfüllen
cshow
Ads

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)

Zielwertsuche-Ergebnis

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)

Excel 2016 Zielwertsuche aufrufen
cshow
Ads

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)

Zielwertsuche ausfüllen
cshow
Ads

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)

Zielwertsuche-Ergebnis

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)

 Szenario-Manager-aufrufen

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)

 Szenario-Manager-aufrufen

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)

Excel 2016 Szenario hinzufügen
Excel 2016 Szenario festlegen
Excel 2016 Szenario Werte festlegen

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)

Excel 2016 Szenario hinzufügen
Excel 2016 Szenario festlegen
Excel 2016 Szenario Werte festlegen

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)

 Szenarien in Excel anzeigen
Kauf auch du bei der Nummer 1!
Anzeige
cshow
Anzeige

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.

Blogverzeichnis Bloggerei.de

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)

 Szenarien in Excel anzeigen
Kauf auch du bei der Nummer 1!
Anzeige
cshow
Anzeige

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.

Blogverzeichnis Bloggerei.de

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top