Contains paid promotion

Advertisement

How to set the scenario manager and
the target value search in Excel 2016

With Excel, you not only have a spreadsheet with which you can perform exact 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.

Excel 2016 Zielwertsuche und Szenariomanager

In our article, 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 Excel 2016 in our article.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

How to set the scenario manager and the target value search in Excel 2016

With Excel, you not only have a spreadsheet with which you can perform exact 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.

Excel 2016 Zielwertsuche und Szenariomanager

In our article, 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 Excel 2016 in our article.

Advertisement
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1,Grau, Rot
SanDisk Ultra 64GB (SDSQUAR-064G-GN6MA) microSDXC ...*
by SanDisk

Kapazität: 64 GB, Flash Card Typ: MicroSDXC, Flash-Memory-K...

List Price: € 30.99 You Save: € 11.99 (39%) Prime Price: € 19.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 much 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)

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 much the purchase quantity has to be at least in order to reach a selling price of € 23.50 (under otherwise identical conditions).

See picture:

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)

Go to Target Find in Excel
Advertisement

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)

Complete target value search in Excel
Advertisement

After clicking on “OK“, the value in the changeable cell (in our example, the purchasing 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 such as our profit bonus in % or the flat-rate surcharges.

See picture (click to enlarge)

Target search result
Advertisement

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:

Go to Target Find in Excel
Advertisement

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:

Complete target value search in Excel
Advertisement

After clicking on “OK“, the value in the changeable cell (in our example, the purchasing 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 such as our profit bonus in % or the flat-rate surcharges.

See picture:

Target search result
Advertisement

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 up the scenario manager, click under the tab:

  • Data” – “What if analysis“.
  • And there select the item “Scenario Manager“.

See picture (click to enlarge)

Call Scenario Manager
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 up the scenario manager, click under the tab:

  • Data” – “What if analysis“.
  • And there select the item “Scenario Manager“.

See picture:

Call Scenario Manager
Advertisement
#1
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis zu 100MB/Sek
SanDisk Ultra 64GB USB-Flash-Laufwerk USB 3.0 bis ...*
by SanDisk

USB 3.0 DRIVE, SANDISK,ULTRA CRUZER,64GB--- Memory Capacity ...

List Price: € 26.99 You Save: € 11.00 (41%) Prime Price: € 15.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 12:52 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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 marking them on the worksheet.

Advertisement

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)

Add scenario in Excel
Set scenario in Excel
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 marking them on the worksheet.

Advertisement

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:

Add scenario in Excel
Set scenario in Excel
Excel 2016 Szenario Werte festlegen

5. View and view the specified scenarios.

5. View and view the specified scenarios.

To call up and display your predefined 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)

Show scenarios in Excel
Advertisement

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.

Print Friendly, PDF & Email

To call up and display your predefined 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:

Show scenarios in Excel
Advertisement

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.

Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Advertisement
#1
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterwasser Action Kamera 40M Digitale Videokamera mit 170 ° Weitwinkelobjektiv Eis Sony Sensor, Zwei verbesserten Batterien, Transportskoffer und 24 Multiples
APEMAN 4K Action Cam 20MP WIFI Wasserdichte Unterw...*
by Apeman

Das ist unser Leben,verpasse nie wieder einen wunderbaren Mo...

List Price: € 109.99 You Save: € 20.00 (18%) Prime Price: € 89.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. August 2018 um 15:32 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-08-09T13:45:24+00:00By |Categories: Excel 2016, Office 2016|
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok