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