Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your models.


 

Lockbox Location
A company is considering opening lockboxes in several cities to reduce the 'float' (lost interest) waiting for
mailed payments. In what cities should lockboxes be opened to minimize lost interest and operating cost?
Each area can send payments to only one city.The interest rate is 6%    
Areas concerned            
  Northwest North Northeast Southwest South Southeast
Daily Payments $325,000 $475,000 $300,000 $275,000 $385,000 $350,000
Cities to be considered          
  Seattle Chicago New York L.A. Dallas Miami
Operating cost $55,000 $50,000 $60,000 $53,000 $58,000 $55,000
Average number of days from mailing to clearing of payment    
  Seattle Chicago New York L.A. Dallas Miami
Northwest 2 5 5 4 6 8
North 4 2 4 6 6 6
Northeast 5 5 2 8 7 5
Southwest 4 6 8 2 4 5
South 6 6 6 4 2 5
Southeast 8 8 5 5 5 2
Assignments of areas to cities (1=yes, 0=no)        
  Seattle Chicago New York L.A. Dallas Miami Total
Northwest 0 0 0 0 0 0 0
North 0 0 0 0 0 0 0
Northeast 0 0 0 0 0 0 0
Southwest 0 0 0 0 0 0 0
South 0 0 0 0 0 0 0
Southeast 0 0 0 0 0 0 0
   
Lockbox Decision 0 0 0 0 0 0  
Lost interest Seattle Chicago New York L.A. Dallas Miami
Northwest $0 $0 $0 $0 $0 $0
North $0 $0 $0 $0 $0 $0
Northeast $0 $0 $0 $0 $0 $0
Southwest $0 $0 $0 $0 $0 $0
South $0 $0 $0 $0 $0 $0
Southeast $0 $0 $0 $0 $0 $0
Total of Lost Interest $0
Operating Cost $0
Total Cost $0
Problem              
A company wants to reduce lost interest ('float') due to mail delay, for the payments it receives every day. It is
considering opening lockboxes in 6 different cities. Each lockbox would require a certain amount of money each
year to operate. The company receives payments from the Northwest, the North, the Northeast, the Southwest, the
South and the Southeast. The amounts involved per day are known. Where should the company open lockboxes?
   
Solution  
This model differs from others in the fact that the variables do not represent amounts of money, a number of
products or other such values. This time the variables are decisions. Do we open a lockbox in this city? To what city
should an area send its payments?  
It turns out that there is an easy and elegant way to describe such variables in models. We do this by using variables
that can be either 0 or 1. Decision variables like this are often called binary variables. We assign a variable to each
decision and if the decision is yes we give the variable a value 1 and otherwise the value 0.  
On this worksheet, we have assigned such variables for the decisions to open lockboxes in the different cities. These
are defined as Lockbox_decisions found in cells B32 through G32.  
By laying out the different areas versus the cities, we can also assign 0-1 variables to the decisions whether an area
should send payments to a certain city. On the worksheet these are defined as Assignments, found in cells B25
through G30. By using the properties of the numbers 0 and 1, we can now easily formulate the model.  
   
1) The variables are the decisions where to open lockboxes and the decisions where to send the mail for each area.
These variables are defined in the worksheet as lockbox_decisions and assignments. All these variables are either
0 or 1. (They are binary variables.)  
   
2) We must tell the Solver that the variables can be only 0 or 1. This gives us:  
  assignments = binary  
  lockbox_decisions = binary  
   
If we do not open a lockbox in a city, we can not have any mail sent to it. These constraints are expressed as follows
  Chicago_boxes <= Chicago_decision  
  Dallas_boxes <= Dallas_decision  
  LA_boxes <= LA_decision  
  Miami_boxes <= Miami_decision  
  New_York_boxes <= New_York_decision  
  Seattle_boxes <= Seattle_decision  
   
Notice how the usage of 0s and 1s gives us the opportunity to write these constraints this way.  
Finally, we assume an area only sends mail to one city. Again, because of the properties of 0 and 1, we can achieve
this by requiring that the sum over the cities of the variables for an area (one row) equals 1. This gives  
  assignments_total = 1  
   
3) The objective is to minimize lost interest and operating cost. This is defined in the worksheet as Total_Cost.
This is calculated by adding the operating cost and the lost interest. The operating cost is calculated by multiplying
the decisions to open lockboxes by the cost to operate them. This is again possible because the variables are exactly
0 or 1. The lost interest is similarly calculated.  
   
Remarks  
The techniques used in this model are simple but very powerful. Questions that are answered by yes or no, open or
closed, etc. can often be solved by using binary variables.  
   
Notice that the interest in this model is very important for the solution. If interest goes up, it becomes more  
profitable to open more lockboxes. If interest is low, it could be more profitable to use fewer lockboxes and accept a
higher float. You can see how the Solver finds different answers by changing the interest rate in cell E4.  

To Learn More:

For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.
User type
Email address
Name
First Last
Company
University
Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.