The Excel Solver add-on in Excel can be used by any Excel user. Excel Solver allows Excel users to implement mathematical programing in Excel. In this way Excel users can solve optimization problems. In this blog post I provide 3 application examples of Excel Solver in SCM.

Prioritizing purchasing spend in Excel Solver

Imagine an oil field and that you want to facilitate crude oil production on the oil field. For this you need pumps that will then be installed on the oil field. The oil field has a limited surface area. You also have a production target specified in the amount of barrel of crude oil to be pumped every day or month. You can purchase different types of oil pumps. Pump types differ in purchasing price, surface area requirements, and maybe also maintenance expenses, other variable operational expenses and lead times.

As a purchasing manager you can formulate this problem mathematically and solve it in Excel Solver. The objective is to minimize purchasing spend (or total net present value of all costs) and the constraints are as follows:

• Ensure that production targets are achieved.
• Respect the limitation of available surface area for oil pump installation.
• Ensure promised production start date (relates to lead times).

Using Excel Solver you can solve this problem using the LP simplex algorithm. Below is a link to an exemplary oil pump capacity planning program in Excel Solver:

• Link: Oil pump capacity Excel Solver optimization

I have also shared this example in Python. See below link.

• Link: Oil pump capacity planning problem in Python

Solving blending problems in Excel Solver

Referring to above oil field planning problem you can think of another common optimization problem – a blending problem. In this case the company that owns the oil field also owns other oil fields. The crude oil from the various fields differ slightly in quality and content. Further down stream the company produces gasoline and other products from crude oil blends. For this a defined crude oil quality, specified as a spectrum, is required.

This results in a blending problem. Considering that different products, produced from crude oil, trade at different market prices, and market demand differs by product, the problem is: Maximize operational profits subject to demand constraints, available crude oil volumes and types, as well as crude oil blend quality requirements.

I have developed a Excel Solver template for this. You can find it via the link below:

• Link: Oil blend Excel Solver optimization

Transport mode planning in Excel Solver

Assuming the crude oil blends have been processed into products such as gasoline the question now is how to distribute the product to the customer. In this case, assuming all facilities are already installed and fixed (which is the case in short- and medium-term distribution planning), this problem comes down to transport mode planning.

For example, the company could use trucks, rail, air or sea vessels for transport. There could be defined amounts of trucks, ships, airplanes and rail wagons available, and their rental fees or transport fees per volume unit would differ. Lead times would also differ by transportation mode. The resulting transport mode planning program can be implemented in Excel Solver. The objective is to minimize transportation costs, while respecting supply constraints, available vehicle amounts and transport capacities. Another constraint is customer demand satisfaction.

I have developed a template for this problem. It is another good example of Excel Solver for SCM. You can find the Excel template via below link.

Link: Transport mode Excel Solver model

Linnart Felkl

Data scientist focusing on simulation, optimization and modeling in R, SQL, VBA and Python