Using spreadsheet optimisation facilities as a decision aid within the theory of constraints framework

Abstract

This paper seeks to bridge the gap between managers and management science (MS) models by demonstrating how to use a computer spreadsheet to explore common managerial issues such as product mix and resource allocation. In the past, managers may have been put off by the complexity and user-unfriendliness of many MS models. However the advent of spreadsheets has opened up access to these models in a user-friendly way. This paper demonstrates how to set up a spreadsheet to explore issues such as how to make the most effective use of scarce resources. Using the optimisation capabilities of spreadsheets, this question can be answered in minutes. Sensitivity and answer reports are generated automatically, which give the user much information about the quality of the solution. While Excel will be used for this demonstration, the general approach is similar for other spreadsheet packages such as Quattro Pro. The paper also shows how the use of MS models can be embedded in the wider Theory of Constraints (Goldratt), for fuller benefits and understanding. This approach has been applied to a local company who have gained fresh insights into their operations.