Building Decision Support Systems in Excel for Production and Distribution Planning: A Case Study

Abstract

We develop a decision support system in Microsoft Excel that integrates production and distribution for a manufacturer of natural fiber-based products in North America. The production and distribution of the company’s products were optimized using a linear programming model, implemented in Excel. The spreadsheet dynamically adjusts the formulation to reflect the user’s current requirements, solves the optimization model in the background, and generates detailed managerial reports. In addition, it allows users to conduct what-if analyses by varying the number of plants and warehouses. It demonstrates the ability of a Linear Programming Model run on an Excel platform to provide the firm with an optimized production plan resulting in significant, cost savings since implementation.