Thursday, January 22, 2009

Use of Excel Spreadsheets in Masters Program Projects

I've been involved with the Centre for Operations Excellence for nearly 2.5 years now. First as a masters student and later as an employee. Each masters student completes an applied Operations Research industry project in the summer as part of the 16-month professional degree in the Sauder School of Business at the University of British Columbia*. This industry project is the highlight of the program and is a consulting-style engagement. A great deal of the modeling done at the COE involves Excel spreadsheets and Visual Basic for Applications. Today I will go through the 4 industry projects from this summer that used Excel for modeling.

In two instances Excel was used as an interface for an ARENA model. ARENA is an excellent discrete event simulation platform and when developing complex scenarios for it, it's ability to interface with Excel is a powerful feature. In An Evaluation of Alternative Designs for the Surgical Suite at BC Children's Hospital, an Excel VBA-based tool was developed to configure surgery block schedule scenarios for the simulation model. In Complex Care & Assisted Living Resource Forecasting an Excel VBA interface was developed for inputs and outputs to an ARENA model for forecasting needs and queuing in an extended care system.

In Optimizing the Supply Chain for a Beverage Manufacturing and Distribution Company a spreadsheet was used to model the supply chain. This model allowed for what-if scenarios testing production schedules, delivery methods, order processing, and inventory levels.

In Evaluating Operational Capital Investments at an International Mining and Metals Company an Excel-based stochastic model was developed of the processing operation.

Obviously I haven't gone into great detail here, but there are confidentiality issues at play. Even without the details, I think the importance of the role of Excel spreadsheets here can be appreciated. All 9 of the projects that year used Excel for data analysis and I would say that 4/9 using Excel for modeling is remarkably few when compared to previous years. If you surveyed the students ahead of time, I don't imagine they would say they pursued a career in OR in order to work with spreadsheets, but in a project where the problem leads the solution, the rapid development environment and widespread platform of the spreadsheet is hard to argue with.

* also the Robert H. Lee Graduate School, but I think we're all getting a little tired of naming all the components of our post secondary institutions.

1 comment:

Anonymous said...

Great post. Rapid deployment and development is a must in our fast paced world of decision making and results-oriented organizations. Another thing to note is cost. Excel is a proprietary platform. There are non-proprietary platform or free software solutions of the spreadsheet application. One notable one is Calc. Very user friendly and even as an optimization solver feature.

I have listed other free software equivalents on my blog.