Solver Foundation for Excel – Supply Chain Planning Part 1

Solver Foundation is a pure, managed code runtime for mathematical programming, modeling, and optimization. We can use it to develop optimization model in many areas, e.g. finance, production, supply chain, human resources… etc.

The Solver Foundation has an Excel add-in so we can build optimization model within Excel, an environment that most of us are comfortable.

Below is how the Solver Foundation Excel add-in looks like.

We are going to find out how we can define a model in the modeling editor.  The modeling editor has 8 different sections:

• Sets are used as indexes in Parameters or Decisions.
• Parameters are the “inputs” of the solver.
• Decisions are the “outputs” or results of the model being solved.
• Goals define the business objective that we are trying to achieve.
• Constraints are where we define business logic / algorithms.
• Directives allow us to control how solver should work or provide hints to solver.
• Model shows the model in OML format. 
• Log.

I am going to use an example that comes with the Solver Foundation Excel Add-in to illustrate how to develop a supply chain model that can maximize profit.  You can find the example after you download and install Solver Foundation.

Add Sets

• Under the Sets tab, click New button

• Enter Products in the Name:  box

Repeat the same procedures to add Areas, Promotions, Factories; so now we have 4 sets (indexes).

 

Add Parameters

• Under the Parameters tab, click New button

• Enter manufactureLoads in the Name:  box

• Click on the radio button to bind data to this parameter.

• Binding Editor dialogue is displayed
• Select the appropriate range, i.e. Sheet1!$B$6:$C$9

• Enable Range Includes Column Headers
• Select Products at the Set column
• Select Product at the Column Header column
• Select ManufactureLoads at the Value Field.
• Click OK button.

• Add Manufacture loads for each product types. to Description

There are 5 more parameters in this model,

• manufactureLoads – Manufacture loads for each product types.
• factoryCapacity – Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on.

• unitManufactureCost – Unit cost of each product manufactured.

• transport – Shipping the products manufactured in a specific factory to a specific geographic area for sale has certain cost.

• demandForecastPrice – A sales forecast on the price at which it will be sold of each product with each promotion type in every geographical area it is going to be sold.

• demandForecastUnits – A sales forecast on the number of units to be sold of each product with each promotion type in every geographical area it is going to be sold.

We can have multiple indexes for each parameter, e.g. demandForecastUnits has 3 indexes.

So when it binds to data, it imports 4 columns.  The last column, demandForecastUnits, is the value field.

 

Add Decisions

Decisions are the output of the model, so we can also bind Decisions to Excel or just show the results at the Solver Foundation Results.  There are 6 Decisions in this model and they all output results to Solver Foundation Results. 

• Plan – how many units of Products will be produced in Factories that will be sold in Areas with Promotions.
• ShippingPlan – how many units of Products that are manufactured in Factories will be shipped into Areas
• SalesForecastUnits – how many units of Products with Promotions that will be sold in Areas will be produced
• TotalRevenue – total revenue of selling Products in Areas
• TotalCost – total cost of selling Products in Areas
• TotalProfit – total profit of a plan

Decisions can also be indexed with Sets, e.g. Plan is a vector with 4 Sets.

Decisions can also be a single value, e.g. TotalProfit.  TotalProfit does not have any Set so it is a single value Decision.

 

Add Goals

There is only one goal for this model, i.e. to maximize the TotalProfit.

So far, we added input (Parameters), output (Decisions), and objective (Goals) to the model.  Where are the business logic / algorithms?  Most of the algorithms are defined in Constraints. We are going to discuss in my next blog.  Please stay tuned!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!