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

TL;DR

This paper compares three spreadsheet implementation designs for an operations management model embedded in business processes, focusing on accuracy, modifiability, and ease of use, and introduces a novel table-driven approach.

Contribution

It introduces a novel table-driven spreadsheet design for operations models, demonstrating advantages over traditional data-driven and canonical designs in flexibility and error reduction.

Findings

The technical design allows easier modifications without manual formula editing.

The data-driven design highlights common poor spreadsheet practices.

The technical design reduces modification time and error risk.

Abstract

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.