Subscribe Bookmark
charles_pirrell1

Joined:

Jun 13, 2014

What-if analysis with JMP (no spreadsheets) -- Part 5

Last week, I showed how the Excel Add-In for JMP can bring more value to Excel spreadsheets for what-if analysis and optimization. Today, we’ll look at how using that same data from within JMP alone is more elegant.

First, let’s look at the Excel spreadsheet from last week's post (see below). You may recall we were exploring the question When will I have enough money to retire?

I am calculating the future value of an Opening Balance (amount saved to date) plus the future value of a stream of annual savings. Here’s what that data looks like in a JMP data table:

The first thing you may notice is that rows in Excel are columns in JMP. That’s because I am calculating my values by column (the last three columns are calculated columns). In this example, I only need two rows: a lower- and upper-limit row.

My formula for the interest on the Opening Balance is:

And the formula for the Future Value of Savings is:

I then sum the above two columns into the last column.

To display the Profiler dialog box, I click on the Graph menu and select Profiler.

The column for Y is the target or output value. I then click on the Expand Intermediate Formulas check box to get JMP to display the input variables versus the intermediate formulas, and click OK.

I now have the same analysis that I did using the Excel Add-In but, importantly, without needing the Excel spreadsheet. I also have a much cleaner, governed data table.

Will Excel ever go away? No. Do we want it to? Absolutely not! But as Excel grows in sophistication, it will most likely never add the necessary foundation in order to support more sophisticated analysis and predictive modeling  – i.e., control of the building blocks for that analysis.

If Microsoft ever added such controls and governance to Excel, it would cease to be Excel and, in my opinion, no longer attract and retain the masses of users. Its appeal has always been and will always be the ease with which users can develop spreadsheets to organize, calculate and model results. This is its underlying strength, yet as I hope I've shown in this series, also its biggest weakness. Excel is a sports car that some folks are trying to drive over mountainous terrain. They may make it to their destination, but chances are the car will break down before ever getting there.


Note: This blog post is part of a series on errors in spreadsheets. This is the final post. Thanks for reading!

2 Comments
Community Member

Adam Smith wrote:

This has been a great series of blog posts. I've learned a lot of things to do in JMP that I had not thought of before!

Charles Pirrello wrote:

Thanks Adam. I'm glad you found them worthwhile.