Linear Programming | All things about Optimization

There are many factors to consider when building a new warehouse facility. Making the right decisions with regards to both warehousing and distribution methods could make all the difference for your firm. This can range from storage requirements, the level of automation in material handling, and workforce availability. The most important among all is the Physical Location.

Physical Location

When zeroing on which warehouse to use, choosing the one with the best physical location is important. The first question to ask yourself is, which region are you looking to serve? Ensuring your product is stored in a region near your customers is important for prompt deliveries. This also factors into considering cost. Calculating landed transportation costs to facility from manufacturing, and expected transportation costs from facility to end customer, will help decide where you can afford to keep your product.

The location’s proximity to carrier facilities should also be taken into consideration. Look for an all-encompassing solution that offers both warehousing and transportation to get the most value out of your investment, or ensure your storage facility is as close to your carrier as possible.

The general objective of transport optimization is minimizing transport cost subjected to the customer service policy. Normally, the transport solution affects inventory carrying and warehousing cost considerably.

Consider a simple scenario – A company stores all of its products in a single warehouse. All of its customers are located in the eastern sea board of the US. The management wishes to determine the best location for their warehouse in order to minimize total transportation costs. The number of shipments made to each of its customers for a period is given. The main objective is to determine the warehouse location based on its latitude and longitude coordinates.

Optimize Warehouse Location in Excel

The input for this problem is the location of each address identified by its lat-long. The user is asked to fill in the shipping addresses of the customer and the number of shipments in a given period, and the spreadsheet works tirelessly behind the scenes to determine the optimal lat-long coordinates of the warehouse to minimize transportation costs. Once you have filled out the shipping point addresses, simply click on “Geocode” command button. Columns C, D, and E are computed using Google Geo web service.

The distance formula is based on spherical law of cosines and bears more accuracy than the straightforward orthogonal distance. R is 69 and is based on earth’s curvature and is used in calculating latitude and longitude distances for cities.

d = acos(sin θ₁ * sin θ₂ + cos θ₁* cos θ₂ * cos Δλ ) * R

Geo-coding and distance computation are done behind the scenes by a free web service from Google. A Caveat – Google is nice enough to geo-code for free but they won’t put up with thousands of requests or attempts per hour from the same IP. After about 10 seconds or so, they will stop sending coordinates for a brief period. You may occasionally get “Not Found” message. The solution is to wait for few seconds and try again. The underlying code has an intentional delay of 2 seconds for every address that needs to be geo-coded. If you persistently get the “Not Found” message check to make sure that your address is as complete as it can be. Adding things like the province and the country can help Google narrow its search and provide a better result.

Setting up Solver

The decision variables are the latitude and longitude values of the warehouse location in columns H3 and I3 respectively.

Latitude and Longitude coordinates are the only constraint to this problem and they must be between 0 and 120 degrees. We only need to add the constraint that they be less than or equal to 120 and they must be non-negative.

The objective function to minimize the total distance between the warehouse and the shipment addresses is set to cell J12. It is the sum of the array product between the column of distances and the column of shipments made to each shipping addresses from the input table. The column range for shipments is “WhouseShipments” and distance as “WHouseDistance”. Thus, the formula for the objective function becomes:

=SUMPRODUCT(WhouseShipments,WHouseDistance)

The decision variables, cells H3 and I3 are to be determined by the solver. Cells H3 and I3 must be non-negative. Choose “Evolutionary” as the solving method.

Now click on Options to view the Evolutionary Solver options. Check Assume Non-Negative and set the population size and mutation rate. Initial population size in this problem is 100 and mutation rate as 25%. Once the solver finishes its operation, you can view the number of iterations of the Genetic Algorithm in the Population and Results report. The outcome of the solution is shown below in the Population report.

Based on those 24 customer shipping points in this example, the solver by evolutionary algorithm has found the coordinates of (41.74, 76.25) as optimal.

Download Optimize Warehouse Location Spreadsheet

#excel-google-geo-xml, #linear-programming, #lp-simplex, #transport-optimization

How much should my AdWords budget be?

This is a very common and an important question for advertisers. It is also one that doesn’t have a straightforward answer. If AdWords is profitable for you, then by all means your budget should be as HIGH as possible. If AdWords is not profitable for you, then your budget should be ZERO.

For most advertisers, the real answer lies somewhere in between. Some products and services can be profitable for you to advertise, while others will not be. Some keywords, geographic locations, mobile vs traditional platforms, and other targeting options will drive Adwords profitability.

If you expect AdWords to make profit for you from day one, you are setting yourself up for unpleasant surprises. There is often an initial investment required until you can ascertain what is working and what isn’t.

Generating optimal number of qualified leads within Adword Budget Constraints

An advertiser wants to allocate a pay-per-click marketing budget among five internet marketing vehicles – Google Display, Google Adwords, Facebook, LinkedIn and Twitter. Every single one of the pay-per-click vehicles has a different pricing and effectiveness per click. The objective is to achieve the highest number of qualified leads by dividing the advertising budget optimally among these 5 pay-per-click vehicles. The advertiser is constrained by budget limits and each pay-per-click vehicle has an advertising spending limit as well.

A Simple Adword Budget Optimization

An analyst has come up with the following details about the cost and effectiveness of each of the 5 pay-per-click advertising vehicles for a consumer electronics manufacturer:

	Average Cost Per Click	Expected No. of Clicks required To Get 1 Inquiry	Expected No. of Inquiries required To Get 1 Qualified Lead
Google Display	$0.88	7	12
Google Adwords	$0.79	9	10
Facebook	$0.81	5	11
LinkedIn	$0.94	8	21
Twitter	$0.90	7	10

“One size does not fit all” – The above inquiry and lead conversion figures are from hardware equipment/electronics sector and are pretty meaningless for a single manufacturer. Way too many variables come into picture to have a “universal lead conversion rate”. The notable ones are:

Landing Page Layout
Traffic Source
Definition of Conversion (different for B2B/B2C, non-profit, educational or full blown E-Commerce sites)
Industry
Price of Product
Ease of Checkout

They have just been used in this example to simplify the optimization problem so as the lead conversion numbers can be indexed to something realistic. Refer to MarketingSherpa’s 2012 PPC Edition chart of their Search Marketing Benchmark Reports and the conversion rates by industry.

Again, the purpose of this problem is to achieve the highest number of qualified leads by allocating the ad budget optimally among these 5 pay-per-click vehicles.

Solution Steps:

Step 1 – Setup the objective

The main task is to maximize the total number of qualified leads. Number of qualified leads depend on Ad Spend$ (column C), Average Cost per Click (Column D), Expected Clicks Required to get 1 inquiry (Column F), Expected number of Active Inquiries (Column G), Expected Inquiries required to get 1 Qualified Lead (Column H).

Step 2 – Identify the decision Variables

The key to find out the decision variables is to determine the amounts to spend on each pay-per-click vehicle to maximize the number of leads. Columns C3 through C7 serve as the decision variables or the Ad Spend $. The initial amount was set based on the constraints in the next step. You need to ensure that the sum of all Ad Spend (columns C3 through C7) must add up to $50K which is our allowed budget. The solver will optimize the spend allocation based on the objective and constraints.

Step 3 – List all constraints

As seen from below screen grab from excel, the problem is bounded by:

Total allowed budget of $50K. Total Ad spend cannot exceed this amount
Display Network has an upper limit of $12K
Ad Word Budget has an upper limit of $15K
Facebook Budget has an upper limit of $6K
LinkedIn Budget has an upper limit of $12K
Twitter Budget has an upper limit of $12K
All decision variables or Ad Spend are integer
Unconstrained variables are all non-negative

Integer constraints ensure that Ad Spend are kept in whole dollar amounts.

Step 4 – Setup Excel Solver

The objective cell is S9 which is maximization of “total expected number of qualified leads”. Since the objective function is linear first order, Simplex LP is the chosen solving method.

Note: You must have Excel Solver installed before you do this exercise. In Excel 2010, Solver can be found under Data menu. If you are unsure about it, the installation steps can be found here.

Step 5 – Interpret the Answer Report

Solver has finished its job and has optimized Ad Spend allocation among 5 pay-per-click vehicles (Column M3 through M7). The amounts are highlighted in yellow.

As you can see, LinkedIn constraint did not hit the target limit and $7000 slack is available. After running this optimization model, the objective function value (expected number of qualified leads) improved from original 495 to 528, which remained our main pursuit.