Is Microsoft Excel (including VBA) a viable platform for the development of complex mathematical optimization models and algorithms?
Whenever it comes to implement business calculation models, probably everyone immediately considers using Microsoft Excel. Excel is widely spread in corporate environments and most people are familiar with using it to a greater or lesser extent. The most exciting thing about Excel, however, is its apparently unlimited flexibility. Excel does the math, imports, stores and exports data, creates visualizations, provides a predefined tabular structure, includes a built-in programming language, etc.
Sometimes this flexibility is boon and bane. The fact that it seems as if you could do anything with Microsoft Excel does not mean you should. I have seen people misusing Microsoft Excel as a word processor, a database, a project planning software or a graphical application. Most of those workbooks reminded me of the following quote by Abraham Maslow (The Psychology of Science, 1966):
“If the only tool you have is a hammer,
you tend to see every problem as a nail.”
Having said this, it is definitely appropriate to always question in advance whether the tool you are intending to use is the right one for the task you are facing.
Besides financial planning, simulation models, visualization and project management, optimization models and algorithms always played an important role in my professional life. Thus, the question whether Microsoft Excel is an appropriate option for developing optimization models and algorithms for complex combinatorial problems suggests itself. Today’s post discusses this question, including the pros and cons as well as the possible use cases.