Solver Foundation for Excel – Supply Chain Planning Part 2

In my last blog, I demonstrated how to create Sets, Parameters, Decisions and Goals in Solver Foundation Excel Add In.  In this blog, I am going to continue the demonstration and discuss the last component, Constraints.

Add Constraints

There are 7 constraints in this supply chain model and we are going to look at each one of them.

• CapacityConstraint – Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on.
• Constraint2 – Summation of units from all promotions should equal to the units shipped for each product produced in factories that will be sold in areas.
• Constraint3 – The product units sold should match the sales forecasted units.
• Constraint4 – total cost for each product involves manufacture cost and shipping cost.
• Constraint5 – Total revenue is the forecasted price times the units sold.
• Constraint6 – Total profit is the summation of each product’s revenue minus its cost.
• DemandConstraint – The units of products with promotions that will be sold in areas will be produced should be less than the units forecasted.

 

CapacityConstraint

 

The capacity required to produce the planned number of products is:

Plan[iter2, iter3, iter4, iter1] * manufactureLoads[iter2]

and iter2, iter3 and iter4 represent loops that would sum up the total capacity required per factory ({iter1, Factories}).

The total capacity required must be smaller or equal to factory capacity factoryCapacity[iter1].

Constraint2

 

Plus[-ShippingPlan[iter5, iter6, iter7], Foreach[
            {iter8, Promotions},
            Plan[iter5, iter6, iter8, iter7]
          ]] == 0

The planned number of products produced, Plan[iter5, iter6, iter8, iter7], should be equal to the number of products shipped, ShippingPlan[iter5, iter6, iter7] .

Constraint3

 

Plus[-SalesForecastUnits[iter9, iter10, iter11], Foreach[
            {iter12, Factories},
            Plan[iter9, iter10, iter11, iter12]
          ]] == 0

The planned number of products produced Plan[iter9, iter10, iter11, iter12] should be equal to the number of product sold, SalesForecastUnits[iter9, iter10, iter11].

Constraint4

 

Plus[-TotalCost[iter13, iter14], Foreach[
         {iter15, Factories},
         ShippingPlan[iter13, iter14, iter15] * (unitManufactureCost[iter13] + transport[iter13, iter14, iter15])
       ]] == 0
The total cost TotalCost[iter13, iter14] should be equal to the manufacture cost ShippingPlan[iter13, iter14, iter15] * unitManufactureCost[iter13] and shipping cost ShippingPlan[iter13, iter14, iter15] * transport[iter13, iter14, iter15].

 

Constraint5

 

Plus[-TotalRevenue[iter16, iter17], Foreach[
          {iter18, Promotions},
          SalesForecastUnits[iter16, iter17, iter18] * demandForecastPrice[iter16, iter17, iter18]
        ]] == 0

The total revenue TotalRevenue[iter16, iter17] should be equal to the multiple of number of products sold SalesForecastUnits[iter16, iter17, iter18] and price demandForecastPrice[iter16, iter17, iter18]
.

Constraint6

 

.

This one is quite simple.  Total Profit is equal to Total Revenue – Total Cost.

 

DemandConstraint

 

This is another simple one.  Sales SalesForecastUnits[iter21, iter22, iter23] must be smaller or equal to demand demandForecastUnits[iter21, iter22, iter23].

Model

Now, the model is completed and if we click on the model tag, then we can see the whole model in OML format.

Below is the full model:

Model[
  Parameters[
    Sets[Any],
    Products,
    Factories,
    Areas,
    Promotions
  ],
  Parameters[
    Reals[-Infinity, Infinity],
    Annotation[manufactureLoads[Products], "description", "Manufacture loads for each product types."],
    Annotation[factoryCapacity[Factories], "description", "Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on."],
    Annotation[unitManufactureCost[Products], "description", "Unit cost of each product manufactured."],
    Annotation[transport[Products, Areas, Factories], "description", "Shipping the products manufactured in a specific factory to a specific geographic area for sale has certain cost."],
    Annotation[demandForecastPrice[Products, Areas, Promotions], "description", "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."],
    Annotation[demandForecastUnits[Products, Areas, Promotions], "description", "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."]
  ],
  Decisions[
    Reals[0, Infinity],
    Annotation[Plan[Products, Areas, Promotions, Factories], "description", "how many units of Products will be produced in Factories that will be sold in Areas with Promotions."],
    Annotation[ShippingPlan[Products, Areas, Factories], "description", "how many units of Products that are manufactured in Factories will be shipped into Areas"],
    Annotation[SalesForecastUnits[Products, Areas, Promotions], "description", "how many units of Products with Promotions that will be sold in Areas will be produced"],
    Annotation[TotalRevenue[Products, Areas], "description", "total revenue of selling Products in Areas"],
    Annotation[TotalCost[Products, Areas], "description", "total cost of selling Products in Areas"],
    Annotation[TotalProfit, "description", "total profit of a plan"]
  ],
  Constraints[
    CapacityConstraint -> Annotation[Foreach[
      {iter1, Factories},
      Sum[
        {iter2, Products},
        Foreach[
          {iter3, Areas},
          Foreach[
            {iter4, Promotions},
            Plan[iter2, iter3, iter4, iter1] * manufactureLoads[iter2]
          ]
        ]
      ] <= factoryCapacity[iter1]
    ], "description", "Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on."],
    Constraint2 -> Annotation[Foreach[
      {iter5, Products},
      Foreach[
        {iter6, Areas},
        Foreach[
          {iter7, Factories},
          Plus[-ShippingPlan[iter5, iter6, iter7], Foreach[
            {iter8, Promotions},
            Plan[iter5, iter6, iter8, iter7]
          ]] == 0
        ]
      ]
    ], "description", "Summation of units from all promotions should equal to the units shipped for each product produced in factories that will be sold in areas."],
    Constraint3 -> Annotation[Foreach[
      {iter9, Products},
      Foreach[
        {iter10, Areas},
        Foreach[
          {iter11, Promotions},
          Plus[-SalesForecastUnits[iter9, iter10, iter11], Foreach[
            {iter12, Factories},
            Plan[iter9, iter10, iter11, iter12]
          ]] == 0
        ]
      ]
    ], "description", "The product units sold should match the sales forecasted units."],
    Constraint4 -> Annotation[Foreach[
      {iter13, Products},
      Foreach[
        {iter14, Areas},
        Plus[-TotalCost[iter13, iter14], Foreach[
          {iter15, Factories},
          ShippingPlan[iter13, iter14, iter15] * (unitManufactureCost[iter13] + transport[iter13, iter14, iter15])
        ]] == 0
      ]
    ], "description", "total cost for each product involves manufacture cost and shipping cost."],
    Constraint5 -> Annotation[Foreach[
      {iter16, Products},
      Foreach[
        {iter17, Areas},
        Plus[-TotalRevenue[iter16, iter17], Foreach[
          {iter18, Promotions},
          SalesForecastUnits[iter16, iter17, iter18] * demandForecastPrice[iter16, iter17, iter18]
        ]] == 0
      ]
    ], "description", "Total revenue is the forecasted price times the units sold."],
    Constraint6 -> Annotation[Plus[-TotalProfit, Foreach[
      {iter19, Products},
      Foreach[
        {iter20, Areas},
        TotalRevenue[iter19, iter20] – TotalCost[iter19, iter20]
      ]
    ]] == 0, "description", "Total profit is the summation of each product’s revenue minus its cost."],
    DemandConstraint -> Annotation[Foreach[
      {iter21, Products},
      Foreach[
        {iter22, Areas},
        Foreach[
          {iter23, Promotions},
          SalesForecastUnits[iter21, iter22, iter23] <= demandForecastUnits[iter21, iter22, iter23]
        ]
      ]
    ], "description", "The units of products with promotions that will be sold in areas will be produced should be less than the units forecasted."]
  ],
  Goals[
    Maximize[
      Profit -> Annotation[Annotation[TotalProfit, "order", 0], "description", "Total profit is maximized."]
    ]
  ]
]

Solve the Model

The model is ready and we can click the Solve button on Solver Foundation tag to start the process.

 

Log

We can review the log when the process is completed.  It took 2 ms to solve the model and the total profit is 9,158,191.67.

 

Solver Foundation Results

We can find all the details results (Decisions) in the Solver Foundation Results that is stored in a new Excel worksheet, e.g. factory at China would produce 7,000 deluxe products to ship to Asia that would sell at discount.

Solver Foundation Results
Name	Value
Solution Type	Optimal
Profit	9158191.667
Plan["Deluxe", "Asia", "discount", "China"]	7000
Plan["Deluxe", "Asia", "discount", "Hungary"]	0
Plan["Deluxe", "Asia", "discount", "Mexico"]	0
Plan["Deluxe", "Asia", "none", "China"]	5000
Plan["Deluxe", "Asia", "none", "Hungary"]	0
Plan["Deluxe", "Asia", "none", "Mexico"]	0
Plan["Deluxe", "Asia", "premium", "China"]	5000
Plan["Deluxe", "Asia", "premium", "Hungary"]	0
Plan["Deluxe", "Asia", "premium", "Mexico"]	0
Plan["Deluxe", "Europe", "discount", "China"]	7000
Plan["Deluxe", "Europe", "discount", "Hungary"]	0
Plan["Deluxe", "Europe", "discount", "Mexico"]	0
Plan["Deluxe", "Europe", "none", "China"]	2686.666667
Plan["Deluxe", "Europe", "none", "Hungary"]	0
Plan["Deluxe", "Europe", "none", "Mexico"]	2313.333333
Plan["Deluxe", "Europe", "premium", "China"]	5000
Plan["Deluxe", "Europe", "premium", "Hungary"]	0
Plan["Deluxe", "Europe", "premium", "Mexico"]	0
Plan["Deluxe", "NA", "discount", "China"]	0
Plan["Deluxe", "NA", "discount", "Hungary"]	0
Plan["Deluxe", "NA", "discount", "Mexico"]	2000
Plan["Deluxe", "NA", "none", "China"]	0
Plan["Deluxe", "NA", "none", "Hungary"]	0
Plan["Deluxe", "NA", "none", "Mexico"]	1000
Plan["Deluxe", "NA", "premium", "China"]	0
Plan["Deluxe", "NA", "premium", "Hungary"]	0
Plan["Deluxe", "NA", "premium", "Mexico"]	100
Plan["Luxury", "Asia", "discount", "China"]	0
Plan["Luxury", "Asia", "discount", "Hungary"]	0
Plan["Luxury", "Asia", "discount", "Mexico"]	0
Plan["Luxury", "Asia", "none", "China"]	2058.333333
Plan["Luxury", "Asia", "none", "Hungary"]	0
Plan["Luxury", "Asia", "none", "Mexico"]	0
Plan["Luxury", "Asia", "premium", "China"]	0
Plan["Luxury", "Asia", "premium", "Hungary"]	0
Plan["Luxury", "Asia", "premium", "Mexico"]	0
Plan["Luxury", "Europe", "discount", "China"]	0
Plan["Luxury", "Europe", "discount", "Hungary"]	7000
Plan["Luxury", "Europe", "discount", "Mexico"]	0
Plan["Luxury", "Europe", "none", "China"]	0
Plan["Luxury", "Europe", "none", "Hungary"]	3833.333333
Plan["Luxury", "Europe", "none", "Mexico"]	1666.666667
Plan["Luxury", "Europe", "premium", "China"]	0
Plan["Luxury", "Europe", "premium", "Hungary"]	10000
Plan["Luxury", "Europe", "premium", "Mexico"]	0
Plan["Luxury", "NA", "discount", "China"]	0
Plan["Luxury", "NA", "discount", "Hungary"]	0
Plan["Luxury", "NA", "discount", "Mexico"]	8000
Plan["Luxury", "NA", "none", "China"]	0
Plan["Luxury", "NA", "none", "Hungary"]	0
Plan["Luxury", "NA", "none", "Mexico"]	4000
Plan["Luxury", "NA", "premium", "China"]	0
Plan["Luxury", "NA", "premium", "Hungary"]	0
Plan["Luxury", "NA", "premium", "Mexico"]	400
Plan["Standard", "Asia", "discount", "China"]	0
Plan["Standard", "Asia", "discount", "Hungary"]	0
Plan["Standard", "Asia", "discount", "Mexico"]	0
Plan["Standard", "Asia", "none", "China"]	0
Plan["Standard", "Asia", "none", "Hungary"]	0
Plan["Standard", "Asia", "none", "Mexico"]	0
Plan["Standard", "Asia", "premium", "China"]	0
Plan["Standard", "Asia", "premium", "Hungary"]	0
Plan["Standard", "Asia", "premium", "Mexico"]	0
Plan["Standard", "Europe", "discount", "China"]	0
Plan["Standard", "Europe", "discount", "Hungary"]	0
Plan["Standard", "Europe", "discount", "Mexico"]	0
Plan["Standard", "Europe", "none", "China"]	0
Plan["Standard", "Europe", "none", "Hungary"]	0
Plan["Standard", "Europe", "none", "Mexico"]	0
Plan["Standard", "Europe", "premium", "China"]	0
Plan["Standard", "Europe", "premium", "Hungary"]	0
Plan["Standard", "Europe", "premium", "Mexico"]	0
Plan["Standard", "NA", "discount", "China"]	0
Plan["Standard", "NA", "discount", "Hungary"]	0
Plan["Standard", "NA", "discount", "Mexico"]	0
Plan["Standard", "NA", "none", "China"]	0
Plan["Standard", "NA", "none", "Hungary"]	0
Plan["Standard", "NA", "none", "Mexico"]	0
Plan["Standard", "NA", "premium", "China"]	0
Plan["Standard", "NA", "premium", "Hungary"]	0
Plan["Standard", "NA", "premium", "Mexico"]	0
ShippingPlan["Deluxe", "Asia", "China"]	17000
ShippingPlan["Deluxe", "Asia", "Hungary"]	0
ShippingPlan["Deluxe", "Asia", "Mexico"]	0
ShippingPlan["Deluxe", "Europe", "China"]	14686.66667
ShippingPlan["Deluxe", "Europe", "Hungary"]	0
ShippingPlan["Deluxe", "Europe", "Mexico"]	2313.333333
ShippingPlan["Deluxe", "NA", "China"]	0
ShippingPlan["Deluxe", "NA", "Hungary"]	0
ShippingPlan["Deluxe", "NA", "Mexico"]	3100
ShippingPlan["Luxury", "Asia", "China"]	2058.333333
ShippingPlan["Luxury", "Asia", "Hungary"]	0
ShippingPlan["Luxury", "Asia", "Mexico"]	0
ShippingPlan["Luxury", "Europe", "China"]	0
ShippingPlan["Luxury", "Europe", "Hungary"]	20833.33333
ShippingPlan["Luxury", "Europe", "Mexico"]	1666.666667
ShippingPlan["Luxury", "NA", "China"]	0
ShippingPlan["Luxury", "NA", "Hungary"]	0
ShippingPlan["Luxury", "NA", "Mexico"]	12400
ShippingPlan["Standard", "Asia", "China"]	0
ShippingPlan["Standard", "Asia", "Hungary"]	0
ShippingPlan["Standard", "Asia", "Mexico"]	0
ShippingPlan["Standard", "Europe", "China"]	0
ShippingPlan["Standard", "Europe", "Hungary"]	0
ShippingPlan["Standard", "Europe", "Mexico"]	0
ShippingPlan["Standard", "NA", "China"]	0
ShippingPlan["Standard", "NA", "Hungary"]	0
ShippingPlan["Standard", "NA", "Mexico"]	0
SalesForecastUnits["Deluxe", "Asia", "discount"]	7000
SalesForecastUnits["Deluxe", "Asia", "none"]	5000
SalesForecastUnits["Deluxe", "Asia", "premium"]	5000
SalesForecastUnits["Deluxe", "Europe", "discount"]	7000
SalesForecastUnits["Deluxe", "Europe", "none"]	5000
SalesForecastUnits["Deluxe", "Europe", "premium"]	5000
SalesForecastUnits["Deluxe", "NA", "discount"]	2000
SalesForecastUnits["Deluxe", "NA", "none"]	1000
SalesForecastUnits["Deluxe", "NA", "premium"]	100
SalesForecastUnits["Luxury", "Asia", "discount"]	0
SalesForecastUnits["Luxury", "Asia", "none"]	2058.333333
SalesForecastUnits["Luxury", "Asia", "premium"]	0
SalesForecastUnits["Luxury", "Europe", "discount"]	7000
SalesForecastUnits["Luxury", "Europe", "none"]	5500
SalesForecastUnits["Luxury", "Europe", "premium"]	10000
SalesForecastUnits["Luxury", "NA", "discount"]	8000
SalesForecastUnits["Luxury", "NA", "none"]	4000
SalesForecastUnits["Luxury", "NA", "premium"]	400
SalesForecastUnits["Standard", "Asia", "discount"]	0
SalesForecastUnits["Standard", "Asia", "none"]	0
SalesForecastUnits["Standard", "Asia", "premium"]	0
SalesForecastUnits["Standard", "Europe", "discount"]	0
SalesForecastUnits["Standard", "Europe", "none"]	0
SalesForecastUnits["Standard", "Europe", "premium"]	0
SalesForecastUnits["Standard", "NA", "discount"]	0
SalesForecastUnits["Standard", "NA", "none"]	0
SalesForecastUnits["Standard", "NA", "premium"]	0
TotalRevenue["Deluxe", "Asia"]	5083000
TotalRevenue["Deluxe", "Europe"]	5083000
TotalRevenue["Deluxe", "NA"]	926900
TotalRevenue["Luxury", "Asia"]	347858.3333
TotalRevenue["Luxury", "Europe"]	3802500
TotalRevenue["Luxury", "NA"]	2095600
TotalRevenue["Standard", "Asia"]	0
TotalRevenue["Standard", "Europe"]	0
TotalRevenue["Standard", "NA"]	0
TotalCost["Deluxe", "Asia"]	1700000
TotalCost["Deluxe", "Europe"]	1683000
TotalCost["Deluxe", "NA"]	303800
TotalCost["Luxury", "Asia"]	255233.3333
TotalCost["Luxury", "Europe"]	2725833.333
TotalCost["Luxury", "NA"]	1512800
TotalCost["Standard", "Asia"]	0
TotalCost["Standard", "Europe"]	0
TotalCost["Standard", "NA"]	0
TotalProfit	9158191.667

 

Conclusion

It may take us a long while to find out the optimal solution if we are using pencil and paper and we may never be sure that we find the optimal solution.  With Solver Foundation Excel Add-in, we can optimize solutions in many different areas, e.g. finance, operation, investment, risk management, production…etc.

Microsoft Solver Foundation is a tool that every business decision maker would love when they do their business planning.  Not more guessing; not more trial and error.  We have the solution at our finger tips.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!