Operations Research and Excel Supply Chain Optimisation

Designing supply chains, and managing cost and service within the supply chain, is more often than not a case of solving a series of complex optimisation problems. However, thanks to Operations Research many of our supply chain optimisation problems can be answered, and thanks to Microsoft Excel the answers may be at your fingertips.

What is Operations Research?

Optimisation problems fall into a discipline of mathematics called Operations Research (OR). The discipline of OR is aimed at modelling complex decision-making problems and arriving at near-optimal or optimal solutions. OR, strictly speaking, is not a science in itself, but rather a consolidation of problem solving techniques from other mathematical sciences, such as statistical analysis.

History of Operations Research

OR originated in the UK from military planning in WWII, with strands of evolution going right back to Charles Babbage who, in 1822, designed what can arguably be credited as the first computer with his mechanical ‘Decision Engine’ design.

Following WWII, the use of OR started to get traction outside of military planning, especially within management and industry. As the OR problem solving models were developed, the value of their ability to identify optimal answers within manufacturing, purchasing and supply chain were also recognised.

Operations Research and Supply Chain Optimisation

The migration was logical. If an OR model can calculate the optimal location for a ballistic missile, ensuring that the distance to all potential target points is minimised, then of course it can also calculate the optimal position for a distribution centre, ensuring the distance to all customers is minimised.

This logical migration into industry was then super-charged during the 60’s and 70’s as technology facilitated the computerisation of iterative calculations and the ability to store and access large volumes of data. Since this time, OR has increasingly gained in popularity throughout management science, but especially within the field of supply chain management where there are often many conflicting objectives in optimisation problems.

Supply Chain Optimisation Systems

Thanks to the migration of OR into the supply chain we now have a host of excellent supply chain decision tools in the market that utilise mathematical programming techniques from the OR discipline. These tools are able to model and optimise transport routes, fleet profiles, inventory allocation, warehouse location and manufacturing schedules, amongst many other supply chain optimisation issues.

There is however, one problem – cost. Many of these decision tools have been developed towards multinational, large-scale corporations with significant budgets for ‘optimisation’ reviews. However, it is rarely the case that these systems are using proprietary algorithms and quite often they will be based upon well documented linear, non-linear and integer programming techniques. Their value does not necessarily come from the optimisation technique, but rather the user-interface and scalability of their platform.

Meet Microsoft Excel’s Solver

So, where does this leave you if you’re an SME with a series of supply chain optimisation problems, but not the budget to invest in major decision making systems, or even the budget to train and retain resources to use these systems?

As you’re reading this, it’s highly probable that you are one click away from Microsoft Excel, and consequently two clicks away from Microsoft Excel’s Solver Engine. Solver is a Microsoft Excel ‘add-in’ that is shipped with every license of Excel and is capable of solving linear, non-linear and integer optimisation problems within the supply chain. Better still, Solver is completely free.

Solver is a very capable decision tool, and whilst it does have limitations compared to some supply chain optimisation software (it’s limited to 200 iterative calculations) it is more than capable of solving many of today’s supply chain problems. If you haven’t familiarised yourself with this excellent tool, we highly recommend you do.

If you want support in any aspect of supply chain optimisation, or logistics planning, please contact our team of supply chain experts today.

Share this post


Need consultancy support?
Get in touch!

Tamsin Giles, Client Service Coordinator, contact photo

Hello! I’m Tamsin, Client Services Coordinator at Paul Trudgian. Please get in touch by phone, email or the contact form and I’ll make sure your enquiry is dealt with promptly and passed to the right member of the consulting team. We look forward to hearing from you!