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.

image

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

image

  • 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

image

  • Enter Products in the Name:  box

image

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

image

 

Add Parameters

  • Under the Parameters tab, click New button

image

  • Enter manufactureLoads in the Name:  box

image

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

image

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

image

  • 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.

image

  • Add Manufacture loads for each product types. to Description

image

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.

image

  • unitManufactureCost – Unit cost of each product manufactured.

image

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

image

  • 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.

image

  • 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.

image

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

image

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

 

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

image

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

image

Decisions can also be a single value, e.g. TotalProfitTotalProfit 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.

image

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!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

One Response to Solver Foundation for Excel – Supply Chain Planning Part 1

  1. Richard says:

    This is an outstanding tool and the applications are so varied, a great tool.

    I will use it

    Richard
    http://productivitymetrics.biz/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: