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.

image

 

CapacityConstraint

 

image

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

 

image

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

 

image

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

 

image

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

 

image

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

 

.image

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

 

DemandConstraint

 

image

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.

image

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.

image

 

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.

image

 

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!

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!

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: