Subscribe Bookmark


Jun 13, 2014

Scenario-based planning & optimization in Excel & JMP -- Part 4

Over the past few weeks, we’ve looked at the rise of Excel’s usage and popularity, and I've noted that Excel has made forays into analytics. Today, we’ll look at one type of analysis: scenario-based planning and optimization.

Using Excel add-ins, you can create what-if analysis and scenario-based planning using Monte Carlo simulation. But creating this type of analysis is tedious, primarily because basic Excel lacks such built-in features. Additionally, the display is still in spreadsheet form rather than a graphic display. Being able to show the inputs graphically and their effects on the output, along with their interactions with one another, is extremely valuable to the viewer.

A few versions ago, we decided to add JMP analytics to Excel spreadsheets via our own Excel add-in. This extends the value of an Excel spreadsheet by incorporating JMP graphics and Monte Carlo simulation.

When can I retire – an example

Let’s look at the difference in the two approaches using a topic that is near and dear to all of us: When will I have enough money to retire? Below is a spreadsheet that I created to show potential results.

It starts with my savings to date (Opening Balance), what I plan on saving each year, how much interest I might earn from my investments and how much longer I plan on working. I then calculate the future value of my Opening Balance and the future value of the streams of savings. I’m only showing the input/output cells, but you’ll notice I’ve hidden some columns.

In order to show how the numbers have been calculated, I’ve unhidden the columns (by the way, is being able to hide stuff in a spreadsheet a good idea?). Now, we can see how I created the model.


I needed to make a calculation based on the number of years left to retirement. If that number were 10, I’d want my future value formulas on both the opening value and the savings stream to be set 10 years out. I accomplished that by creating 30 cells (assuming I have 30 or fewer years to work) each with the following formula:

The cells in column D contain =+IF($C$9>D5,D5+1,0)

The cells in column E contain =IF(D6<>0,E5,0)

Both are in relative or absolute terms as necessary. This allowed me to create an array of the needed number of years for my calculation.

Cell F10 contains =FVSCHEDULE(F6,$E$5:$E$54) and

Cell F11 contains =FV(C8,C9,F7)*-1

This may not be the best way to create this model, but it seems to calculate the results correctly -- so what does it matter if there’s a better way? I got it to work, right? (If you disagree, you are not a typical Excel user.)

I can adjust the inputs and see the outputs change. Beautiful. Yet there are potential insights that aren’t apparent within the spreadsheet. There is a graphic representation that would provide a better understanding of the model.

I won't get into the details of how Excel would perform scenario-based analysis here. You’re free to learn that from Excel. But I do want to show how JMP can provide fresh insight that Excel cannot.

You open the spreadsheet with the JMP add-in invoked, click on the JMP tab and select Create/Edit Model:

You then define the model using the Create/Edit dialog box:

During the definition phase, you click on the Choose button to select the input labels and the cell containing the input amount. You set minimum, initial and maximum values for each input cell, and then identify the Output(s) cells in the same point-and-click fashion.

After defining the model, you select Run Model from the JMP add-in tab. JMP displays the following:

JMP will graphically show the variables that contribute to the outcome (future savings). These graphs convey much more information than cells. Because JMP is graphing the minimum/maximum values for each variable, the one that has the steepest slope is the one that will affect the target variable (future savings) the most. In this case, it the number of years I am able to work in order to allow my savings to grow.

But the Profiler will also show how each variable can affect the others. I can use the mouse to select the vertical red line in the middle of each graph and drag it to the left or right, in effect raising or lowering that variable’s value. When I raise the number of years until retirement, look what happens to the Annual ROI graph:

The slope of that line increases dramatically, indicating that this variable now has a greater impact on the outcome. This makes sense -- the more time you allow your money to earn interest and grow, the better.

But the analysis doesn’t stop there. After you’ve set each variable to a realistic value, you may want to test the probability of reaching those values -- that is, what is the likelihood that I’ll save $10,000 a year on average or earn a 7 percent return on my investments? This is where simulation comes into play.

With JMP, you can define the variables upon which to run Monte Carlo simulation:

The above image shows the likelihood of meeting a goal of $1 million in future savings by randomly varying the amount saved per year, the rate of return and the number of years to retirement. A 5 percent chance of meeting my goal would not make me feel good if these were real numbers.

But the goal of this blog series is not to show how to leverage an existing spreadsheet. The fact is that if there were errors in the spreadsheet (and please don’t find any), the JMP analysis would be of little value, and possibly lead to incorrect conclusions and decisions.

Next week, we’ll look at how you could create the same model in JMP.

Note: This blog post is part of a series on errors in spreadsheets. New posts in this series appear weekly on Wednesdays.

Community Member

Bruce Janda wrote:

This is a great build on previous articles regarding Excel. I've fallen into the traps you previously described.

I'm looking forward to seeing this problem in an all JMP environment in your next post.

Community Member

Mike Clayton wrote:

Savings examples always feature what I think are very optimisitic ROI's given usual IRA type savings systems. My ROI on savings from 1994 to 2014, a 20 year period of much volatility, showed AFTER FEES ROI and BEFORE TAXES of about 4 percent. However to get that money OUT, I have to pay income taxes...which I agree I did avoid during that 20 year period of steady savings.

So what's real? How do you include tax issues in a savings plan analysis given common use of IRA's. That might be interesting model to add Monte Carlo efforts BEFORE you sign up for either a ROTH or a REGULAR IRA. Would make a nice webinar.

Charles Pirrello wrote:

Thanks Bruce,

I'm glad it's provided some insight as to the types of traps users can find themselves caught in. I hope that after next week's conclusions, readers will agree that there are more elegant, less risky solutions for these types of analyses.