Are you a decision maker who often do planning, scheduling and optimization?

Our business world is extremely complicated and anyone who did modeling before, would tell you that even the smallest model can have up to 100s variables and constraints; medium size models is in the range of 10,00s to 100,000s.  This is just too complex for “pencil and paper” and manual methods.  Fortunately, Microsoft Excel has offered a powerful optimization tool since 1991, Solver add in.  It has helped many decision makers to do business planning, project scheduling and resources optimization.  If you haven’t heard about Microsoft Excel Solver add in, you can find out more from my previous blog.

It is easy to learn Microsoft Excel Solver since it is tightly integrated with Excel and it uses Excel structures, e.g. cells and cell references that you are familiar to formulate and implement optimization models.  I managed to coach a lot of analysts to build their first model within couple hours.  It is also very powerful and flexible that you can use it to handle financial model, supply chain model and even project scheduling.

However, spreadsheet model has a few disadvantages.  It is difficult to integrate with existing legacy systems, implement version control and identify where the changes are, document and debug the model.  Excel Solver is a great tool for ad hoc modeling; however if we want to migrate it to the production environment, there can be many challenges that we have to resolve.  How can we share our model with other colleagues and not let them accidentally change it?  Can we automatically feed the results to our finance / supply chain system?  How can we document the model so that other colleagues understand our works?

Microsoft Solver Foundation (MSF) is the answer that we are looking for.  Key features include:

• Excel add in – a complete modeling environment that allow us to develop models.
• Application Programming Interface (API) – allows us to integrate our models into the legacy systems.
• Optimization Modeling Language (OML) – an equation based modeling language.
• Solver plug-in – we can develop or purchase external solvers that can hook up to MSF.
• Parallelism – MSF will manage all threading, many-core, synchronization, and scheduling around the solvers.
• MSF has a collection of solvers and it would automatically determine the best solver for us.
• Simulation – We can use stochastic programming to run Monte Carlo simulation.

In this blog, I would only focus on Excel add in which includes:

• Modeling Editor – simplify the modeling experience
• Excel Call Binding – bind data to and from the model
• Deployment – once we develop and test the model in Excel, we can deploy it directly into C# application

I am going to use an airline route allocation simulation model to demonstrate how MSF Excel add in works.  The objective of this model is to minimize operational cost.

It is a small model, so not a lot of data.

• First, we bind data from an Excel sheet to the model through the Parameters tab.  You may notice that we can now specify description and the type of parameters, e.g. Integer, Real, InterNonnegative.

• Then we bind the results of the model being solved to Decisions tab.

• So far we don’t need to learn anything new to define Parameters and Decisions.  But we now need to learn how to use Optimization Modeling Language (OML) when we define the business goals in Goals tab.  OML is designed exclusively for modeling; it includes identifiers, comments, string literals, Boolean constants, and arbitrary numeric literals.
• Our goal is to minimize operational cost which includes 2 components, shipping cost and refund.

• We add restrictions to the decisions in the Constraints tab.

• As we defined the model through various tabs, MSF Excel add in would automatically build the model for us and we can review the entire model in OML format at Model tab.  We can add extra descriptions to the model so other users can understand how it works.

• Once the model is ready, we can click the Solve button to start the solver.

• And the results publish to the specified range.

• We can also review the output log.

• We can deploy the model to production environment and integrate with other legacy systems after we fine tune it.

Microsoft Solver Foundation is a great product; we can integrate our models into legacy systems and take advantage of our multi core CPU.  We have to learn OML; however it is a simple modeling language and quite straight forward to manipulate.  So if you often need to build business models to optimize profit, schedule and other resources; you should download its express edition and see what it can offer you.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!