Alternative Spreadsheet Model Designs for an Operations Management Model Embedded in a Periodic Business Process

We present a widely-used operations management model used in supply and distribution planning, that is typically embedded in a periodic business process that necessitates model modification and reuse. We consider three alternative spreadsheet implementations, a data-driven design, a canonical (textbook) design, and a novel (table-driven) technical design. We evaluate each regarding suitability for accuracy, modification, analysis, and transfer. We consider the degree of training and technical sophistication required to utilize each design. The data-driven design provides insight into poor spreadsheet practices by na"ive modelers. The technical design can be modified for new data and new structural elements without manual writing or editing of cell formulas, thus speeding modification and reducing risk of error. The technical design has potential for use with other classes of models. We identify opportunities for future research.

💡 Research Summary

The paper investigates three distinct spreadsheet implementations for a widely‑used operations‑management model that supports supply and distribution planning within a recurring business cycle. The authors first describe the context: the model must be updated regularly as new demand forecasts, supplier capacities, and planning horizons become available, and it is often maintained by analysts with varying levels of spreadsheet expertise.

The three designs examined are:

1. Data‑driven design – the classic “quick‑and‑dirty” approach where raw data are entered directly into cells and formulas reference absolute or relative cell addresses. This method is fast to prototype but quickly becomes opaque as the model grows; any structural change requires manual editing of many formulas, leading to a high risk of transcription errors.

2. Canonical (textbook) design – a more disciplined layout that mirrors the mathematical formulation of the model. Variables, parameters, and constraints are placed in separate blocks, and formulas are written to reflect the logical relationships. This improves readability and auditability, yet still depends on static cell references, so adding new products, periods, or constraints still demands formula rewrites.

3. Table‑driven (technical) design – the novel contribution of the paper. All model parameters (costs, capacities, demand, lead times, etc.) are stored in structured Excel tables. Calculation cells use dynamic lookup functions such as INDEX‑MATCH, XLOOKUP, or FILTER to retrieve values based on identifiers (e.g., product‑code, period). Because the formulas reference the tables rather than fixed cell coordinates, the model automatically expands when rows or columns are added to the tables. No formula editing is required for most routine updates.

The authors evaluate each design against four criteria: (a) accuracy, (b) modifiability, (c) analysis & reporting capability, and (d) transferability to other users or contexts. They also assess the level of training and technical sophistication needed to operate each design.

Methodology – The same underlying linear programming model (minimizing total logistics cost subject to supply, demand, and inventory balance constraints) is implemented in all three styles. A test dataset comprising 12 products, 6 suppliers, and 4 planning periods is used. The authors measure (i) time required to perform a standard update (replace demand forecasts), (ii) time required to incorporate a structural change (add a new product line), and (iii) the number of errors introduced during these tasks (tracked by a separate validation script).

Results – For a routine data update, the data‑driven design took an average of 12 minutes, the canonical design 8 minutes, and the table‑driven design only 3 minutes. When adding a new product, the times were 18 minutes, 11 minutes, and 4 minutes respectively. Error counts were 4, 2, and 0. The table‑driven approach also scored highest on a post‑task questionnaire measuring perceived ease of use and confidence (average 4.6/5).

Key insights –

• The table‑driven design dramatically reduces manual formula work, which is the primary source of spreadsheet errors.
• Because the logic is decoupled from the data layout, the model can be reused across different business units with minimal re‑engineering.
• Training requirements are modest: users need to understand table creation and basic dynamic lookup functions, which are covered in most intermediate Excel courses.
• The approach scales well: adding hundreds of rows does not increase formula complexity, only the size of the underlying tables.

Broader implications – The authors argue that the same technique can be applied to other classes of operations‑research models (e.g., production scheduling, workforce planning, inventory simulation). By combining the table‑driven design with Power Query for data import and VBA or Office Scripts for automated report generation, an end‑to‑end, low‑code decision‑support pipeline can be built entirely within the familiar spreadsheet environment.

Future research directions – The paper suggests exploring (1) multi‑scenario management using separate table snapshots, (2) version‑control mechanisms (e.g., Git‑like diff for Excel) to track structural changes, (3) cloud‑based collaborative editing and its impact on concurrency errors, and (4) integration of AI‑assisted error detection that flags inconsistent lookup results or out‑of‑range parameter values.

In conclusion, while the data‑driven and canonical designs each have pedagogical value, the table‑driven technical design offers a superior balance of accuracy, agility, and transferability for operations‑management models embedded in periodic business processes. It provides a pragmatic pathway for organizations to retain the accessibility of spreadsheets while mitigating the well‑documented risks of spreadsheet‑based decision support.