[Paper Review] A Primer on Spreadsheet Analytics

Albert Thomas|arXiv (Cornell University)|Sep 21, 2008

Spreadsheets and End-User Computing2 references3 citations

TL;DR

This paper introduces a structured framework for spreadsheet analytics, guiding analysts through terminology, model preparation, and a hierarchy of techniques including sensitivity analysis, tornado charts, and backsolving using native Excel and add-ins. It demonstrates practical implementation methods and calls for empirical research on advanced add-ins for sensitivity analysis and optimization.

ABSTRACT

This paper provides guidance to an analyst who wants to extract insight from a spreadsheet model. It discusses the terminology of spreadsheet analytics, how to prepare a spreadsheet model for analysis, and a hierarchy of analytical techniques. These techniques include sensitivity analysis, tornado charts,and backsolving (or goal-seeking). This paper presents native-Excel approaches for automating these techniques, and discusses add-ins that are even more efficient. Spreadsheet optimization and spreadsheet Monte Carlo simulation are briefly discussed. The paper concludes by calling for empirical research, and describing desired features spreadsheet sensitivity analysis and spreadsheet optimization add-ins.

Motivation & Objective

• To provide a comprehensive guide for analysts seeking to extract insights from spreadsheet models.
• To standardize terminology and best practices for preparing spreadsheet models for analytical evaluation.
• To present a tiered approach to analytical techniques, from basic to advanced, within the Excel environment.
• To evaluate the efficiency of native Excel methods versus specialized add-ins for automation.
• To advocate for empirical research on the effectiveness of spreadsheet sensitivity analysis and optimization add-ins.

Proposed method

• The paper outlines a hierarchy of analytical techniques, beginning with sensitivity analysis to assess input impact on outputs.
• It describes tornado charts as visual tools to rank input variables by their influence on model outcomes.
• Backsolving (goal-seeking) is presented as a method to determine input values needed to achieve a target output.
• Native Excel functions such as Data Tables and Goal Seek are recommended for automating sensitivity and goal-seeking analyses.
• The paper evaluates third-party add-ins as more efficient alternatives for complex or repeated analyses.
• Brief overviews of spreadsheet optimization and Monte Carlo simulation are included to contextualize advanced techniques.

Experimental results

Research questions

• RQ1 How can spreadsheet models be systematically prepared to support effective analytical evaluation?
• RQ2 What is the relative effectiveness of native Excel tools versus specialized add-ins for sensitivity analysis and optimization?
• RQ3 Which analytical techniques—sensitivity analysis, tornado charts, or backsolving—are most suitable for different types of spreadsheet models?
• RQ4 What features should future add-ins for spreadsheet analytics include to improve usability and analytical depth?
• RQ5 What empirical research is needed to validate the performance and reliability of spreadsheet analytics tools?

Key findings

• Sensitivity analysis enables analysts to identify which input variables most significantly affect model outcomes.
• Tornado charts provide a clear visual ranking of input variables by their impact, enhancing interpretability.
• Backsolving offers a practical method for determining required inputs to achieve a desired output, especially useful in forecasting and planning.
• Native Excel tools like Data Tables and Goal Seek provide accessible automation for basic analytical tasks.
• Specialized add-ins are more efficient than native tools for complex or repeated analytical operations.
• The paper identifies a need for further empirical research on the design and performance of spreadsheet analytics add-ins.