Explore Design Sensitivity Using Excel’s Scenario

Most electronics designers have at their fingertips a useful tool for performing design calculations; they just don't know it. It is the Excel spreadsheet program, and it has many features that can be applied to circuit design. One of them — Scenario — allows you to see how your design handles change.

To investigate the Excel Scenario feature, let’s continue with the LM317 example that I presented in my earlier blog, Use Excel Goal Seek to Solve Design Equations on Excel in design. Recall that for the LM317, Vout =Vref (1+R2/R1)+Iadj *R2, where Vref was specified at 1.25V. The example had calculated values for R1 and R2 that met some design criteria, and we picked the nearest stock values for our design. But the results assume that the stock resistors have exactly their nominal value.

Let's consider the scenario that the stock resistors are not exact in their value, but only somewhere within a tolerance band. As with any scenario the purpose is to consider different aspects of the same calculations to understand the sensitivity of the model to change. I took the original model and repeated it below with the reference voltage built into the calculations as you see in figure 1. I selected the nearest standard value for R1 and manually entered it. You can see the resultant voltage in cell B12.

Figure 1

Let’s build a model to see how the circuit's output voltage may vary with component variations. I have added some additional entries in the rows below the original model, as shown in figure 2, to use in this new model.

Figure 2

I took the Iadj and reference voltage values from the LM317T data sheet. The min and max resistor values (B23 to B26) are based on the design values in B14 and B15, allowing for 1% value variations. The cells actually contain a forumla, as you can see in the formula bar (fx = ) at the top of the worksheet, so that the min/max will track any changes to R1 or R2.

Now harking back to university days we recall that to obtain the maximum value of a function that involves a ratio, we use the maximum values of the parameter in the numerator and the minimum value in the denominator. The process is inverted to obtain the minimum of the function. That is the approach we will use for this example to find out how much variation to expect in the output voltage given the uncertainty in resistor values. If your design formula makes it difficult to decide what parameter value will maximize and minimize the function, though, you could try to build other scenarios to evaluate how the function will perform.

To find the minimum and maximum output, first invoke the Scenario manager (figure 3) and then create a “Nominal” situation. You accomplisht this by first clicking on the “Add” button, entering “Nominal” as the scenario name, and then fill in the boxes as shown in figure 4 and click on “OK.” I also have enabled changes to the cells, although I am not sure that makes much difference.

Figure 3

Figure 4

Figure 5

The parameters for the Nominal scenrio default to the values originally set up on the spreadsheet, as shown in figure 5, which we will just accept with the “OK”.

Next, Add a new scenario you name “Maximum Voltage”, leave the Changing cells entry at their default values, and click on OK . Then enter the new value information to be used in this scenario. Although not readily obvious you can enter ” =” and the cell number rather than the actual value, so you can do some preparation and not have to enter the data directly into the scenario manger. However, you cannot point and click on the cell to get its cell number into the dialog box (as you can in many other Excel operations) and the scenario manager will replace the cell name with the numeric value of its contents when performing its function. It will notify you to this fact, which you can happily ignore until you come back to redo a scenario, but it means that if you want to modify a scenario you must make the change in the scenario manager, not just change the value in the target cell you originally specified.

Figure 6

Repeat the process for a Minimum Voltage scenario as seen in figure 7.

Figure 7

Once you have created all your scenarios you will have a list in the scenario manager like that shown in figure 8. Highlighting one of the scenarios and clicking on Show will result in the values in cells you have indicated are to change being updated with the values associated with the scenario, and your equations will be recalculated.

Figure 8

Click on Summary in the scenario manager to create a report of your scenario results under a new sheet in the workbook. In our example the results cell is B12 (the calculation of Vout ) as shown in figure 9. The report that appears you can see in figure 10.

Figure 9

Figure 10

There you have it — a fairly straightforward method for evaluating how a formula's result may change with some perturbations in the parameter values. It is not that sophisticated a method and in my opinion could benefit from the ability to enter cells rather than just values in the calculations, but then I am just a voice in the wilderness. You could always write a macro to automate this and bypass the restrictions.

So, now that you know about it, have you any thoughts as to how you might use the Scenario Manager?

1 comment on “Explore Design Sensitivity Using Excel’s Scenario”

1. PCR
November 10, 2013

Aubrey, many thanks for the article, it's really informative and simple to follow,

This site uses Akismet to reduce spam. Learn how your comment data is processed.