Title: Development of an operational and tactical decision support tool for a Canadian beverage firm: a case study

Authors: Mehmet A. Begen; Martin L. Puterman; Hongtu Wu

Addresses: Ivey Business School, Western University, 1255 Western Road, London, Ontario N6G0N1, Canada ' Sauder School of Business, University of British Columbia, 2053 Main Mall, Vancouver, BC V6T1Z2, Canada ' Department of Decision Support, Vancouver Coastal Health, 520 West 6th Avenue, Vancouver, BC V5Z 4H5, Canada

Abstract: This paper describes a logistics optimisation case study for a Canadian beverage manufacturer and distributor. The goal was to determine production, distribution and inventory plans for a given product line to help the company with its challenges due to production shortages, stock-outs and high transportation costs in a new and highly competitive market. We built and implemented an optimisation model in Excel with VBA as a customised planning tool. Although we originally designed the tool for operational planning, the beverage company first used it for tactical planning (in price negotiations with the firm's subcontractors, deciding whether to buy a bankrupt subcontractor production site, and quantification of carrying extra inventory). The tool has changed the way the company conducts its business planning by evaluating 'what if' scenarios, finding an optimal operational plan, and forcing the company to think more strategically and for longer horizons. [Received: 21 January 2017; Revised: 30 March 2018; Revised: 30 September 2018; Accepted: 28 October 2018]

Keywords: operations research practice; decision support systems; optimisation; operational and tactical planning; Excel Solver; visual basic for applications; VBA.

DOI: 10.1504/EJIE.2019.098515

European Journal of Industrial Engineering, 2019 Vol.13 No.2, pp.245 - 263

Published online: 20 Mar 2019 *

Full-text access for editors Access for subscribers Purchase this article Comment on this article