Subscribe Bookmark



Aug 5, 2014

Beyond Spreadsheets: Ken Franklin, Oregon Department of Transportation

“Spreadsheets are familiar tools, which are relatively simple to use. However, the downside is that they result in fragmented thinking.”

-- Ken Franklin, Performance Measurement Program Manager, Highway Division of Oregon Department of Transportation (ODOT)

Ken HeadshotJMP customers use an array of tools and processes for exploratory data analysis to make breakthrough discoveries. In the coming weeks in a series of blog posts we are calling Beyond Spreadsheets, you can read interviews with JMP users to learn more about how and why their organizations bring Excel data into JMP to create visualizations and explore what-if scenarios.

Our first interview is with Ken Franklin, JMP advocate and provider of key support services to the Highway Division of ODOT including performance measurement, process improvement, leadership team facilitation, skill development, communications/publications, event planning, data systems maintenance, budget preparation and cash flow management.

Fast Facts

  • JMP user since 2014
  • Favorite JMP features: Graph Builder, Distribution platform, Control Chart platforms and interactive HTML files
  • Proudest professional accomplishment: "I discovered that a key agency performance measure (KPM), in place for at least eight years, had been incorrectly calculated right from the beginning, rendering it wholly invalid. JMP was instrumental in analyzing various data sets and displaying critical data visualizations that made the measure’s problem obvious."
  • Tell us a bit about the function of your department and how it contributes to your organization’s mission?

    I’m in the Strategic Business Services Branch of the Highway Division of the Oregon Department of Transportation (ODOT). We are a relatively new branch that combined some disparate units together, so a shared identity is still a work in progress. We provide support functions to the “mission pipes” of the Highway Division, which are transportation project planning, development, construction and maintenance. The support functions provided include performance measurement and reporting, process improvement, leadership team facilitation, skill development, communications/publications, event planning, data systems maintenance and development, budget preparation and reporting, and cash flow management.

    My role is performance measurement and reporting for the Highway Division. I inherited a very large quarterly business report (QBR) consisting of far too many static PowerPoint slides and too much information without clear purpose. These reports have been very labor-intensive to produce with too little resulting value when reviewed by executive management. JMP is helping me transition the QBR from an assortment of PDFs, Word documents, Excel spreadsheets, and separate and static graphics to a more useful quarterly report based more on data visualization and statistical analyses. The QBR is a challenging work-in-progress that is just one component of a performance measurement program that needs improvement from A to Z.

    What do you like most about the type of work you do? 

    What I like most is the opportunity to learn many different subjects and apply what I learn in a generally supportive environment. I’m in a unit that’s very much about continual improvement, overseen by a manager who is very generous in providing the necessary training and tools I need to increase my level of mastery in the many dimensions required. Of course, there’s always too much to learn – performance measurement process, strategic thinking, project portfolio management, data visualization, statistical analysis, JMP (!), ODOT processes, etc. – while still getting my operational work done.

    What is a professional accomplishment of which you are most proud?

    Most recently, I discovered that a key agency performance measure (KPM), in place for at least eight years, was incorrectly calculated right from the beginning, rendering it wholly invalid. However, none of the managers noticed because “analysis” stayed at an overly aggregated quarterly/yearly level. Only by “going under the hood” did we discover the error.

    An additional problem we discovered is that the data table for the measure (as well as all the other measures) is too narrow, limited to only process result data, with little to no upstream process data to provide causal insights on the resulting process performance. JMP was instrumental in analyzing various data sets and showing critical data visualizations that made the measure’s problem obvious.

    I also used JMP to show how our regions (five of them across the state) were manipulating an on-time delivery measure (common but unstated knowledge). Using Graph Builder, I developed a trellis display scatterplot that our state engineer saved to his smart phone. He uses it to demonstrate why the measure and forecasting process needs improvement.

    What do you like most about using JMP?

    • The ease of dragging and dropping variables into Graph Builder and changing the representations on the fly. I find trellis displays quite useful and have never seen one via Excel. I agree with Stephen Few’s observation that most business process/quantitative problems can be addressed via good data visualizations, without needing to employ the more technical statistical analyses available in JMP. However, that statistical power is available when needed.
    • The Distribution platform is great for dropping in a set of variables and looking for any obvious correlations.
    • The Quality and Process platform, especially the control chart options, is proving quite useful in introducing statistical control process concepts to managers where measurement of business processes and systems have for too long been limited to point-to-point comparisons, ignoring the inherent variation in measurement data.
    • The interactive HTML file capability is very useful for emailing others who don’t have a JMP license, allowing me to introduce them to more interactive data exploration.
    • I’m also looking forward to using Query Builder in JMP 12 to automate as much of my performance reporting as possible.

      How is using JMP different from using spreadsheets?

      Spreadsheets, via Excel or Access, are the dominant tool for managing processes and generating reports and graphics at ODOT. Pivot tables and, to a limited degree, Power Pivot are also used (mostly by financial analysts).

      Spreadsheets are familiar tools and relatively simple to use. The downside is that they result in fragmented thinking. I see that phenomenon here and in other agencies that use balanced scorecard measurement approaches, with goals, processes, measures, etc., spread out across conference room walls in a big spreadsheet. Data analyses are typically separate graphs or, worse, tables of numbers. The big downside of this approach is that it fails to explore and reveal the interrelationships among variables; key patterns and insights are locked away in their respective cages.

      JMP and other data visualization tools can provide the capacity to explore data interrelationships with a degree of ease not available with the older spreadsheet tools. However, there are downsides. For most staff and managers, the learning curve for new tools is steep, so it is a big obstacle to carve out the time to learn something new. Another obstacle I’ve seen is the cost of purchasing licenses in strapped budget times, which comes down to staff making a sales pitch to management unfamiliar with the newer tools. Making the case for a high enough ROI to justify a purchase can be an impediment for those who are just learning the tool. For newbies, me included, the ease of analyzing data and displaying graphical results can jump (no pun intended!) ahead of one’s knowledge of when and how to best use a particular tool and how to best communicate results, which can lead to reports that contain more statistical analysis than their audiences can currently absorb and understand.

      Of course, no matter the tool used, “garbage in, garbage out” always rules. But that’s another problem.

      What advice or best practices would you give to other companies that are currently relying on spreadsheet tools to conduct statistical analyses? 

      Explore the alternatives! Better data visualization and analysis with far less effort and cost is available. (Yes, a software license costs money, but we aren’t accounting for all the wasted costs of paying analysts to do a lot clerical-level data-entry work instead of real data analysis.)

      Ready to go beyond spreadsheets, too? Visit to learn how to build compelling reports and dynamic dashboards that you can easily share with others.