MasterDebt is a national credit card company with thousands of card holders located across the United States. Every day throughout the month, MasterDebt sends out statements to different customers summarizing their charges for the previous month. Customers then have 30 days to remit a payment for their bills. MasterDebt includes a pre-addressed envelope with each statement for customers to use in making their payments. One of the critical problems facing MasterDebt involves determining what address to put on the pre-addressed envelopes sent to various parts of the country. The amount of time that elapses between when a customer writes his check and when MasterDebt receives the cash for the check is referred to as float. Checks can spend several days floating in the mail and in processing before being cashed. This float time represents lost revenue to MasterDebt because if they could receive and cash these checks immediately, they could earn additional interest on these funds. To reduce the interest being lost from floating checks, MasterDebt would like to implement a lockbox system to speed the processing of checks. Under such a system, MasterDebt might have all its customers on the West Coast send their payments to a bank in Sacramento which, for a fee, processes the checks and deposits the proceeds in a MasterDebt account. Similarly, MasterDebt might arrange for a similar service with a bank on the East Coast for its customers there. Such lockbox systems are a common method companies use to improve their cash flows. MasterDebt has identified six different cities as possible lockbox sites. The annual fixed cost of operating a lockbox in each of the possible locations is given in the following table.

An analysis was done to determine the average number of days a check floats when sent from seven different regions of the country to each of these six cities. The results of this analysis are summarized in the following table. This table indicates, for instance, that a check sent from the central region of the country to New York spends an average of three days in the mail and in processing before MasterDebt actually receives the cash for the check.

Further analysis was done to determine the average amount of payments being sent from each region of the country. These results are given next:

Thus, if payments from the Central Region are sent to New York, on any given day, there is an average of $135,000 in undeposited checks from the Central Region. Because MasterDebt can earn 15% on cash deposits, it would be losing $20,250 per year in potential interest on these checks alone.

1. Which of the six potential lockbox locations should MasterDebt use and to which lockbox location should each region be assigned?

2. How would your solution change if a maximum of four regions could be assigned to any lockbox

Please show all work in excel

Sacramento $25 Annual Lockbox Operating costs (in $1,000s) Denver Chicago Dallas New York $30 $35 $35 $30 Atlanta $35 Average Days of Float Between Regions and possible Lockbox Locations Sacramento Denver Chicago Dallas New York Atlanta Central Mid-Atlantic Midwest Northeast Northwest Southeast Southwest A Average Daily Payments (in $1,000s) by Region Payments Central $45 Mid-Atlantic $65 Midwest $50 Northeast $90 Northwest $70 Southeast Southwest A $80 $60 Sacramento $25 Annual Lockbox Operating costs (in $1,000s) Denver Chicago Dallas New York $30 $35 $35 $30 Atlanta $35 Average Days of Float Between Regions and possible Lockbox Locations Sacramento Denver Chicago Dallas New York Atlanta Central Mid-Atlantic Midwest Northeast Northwest Southeast Southwest A Average Daily Payments (in $1,000s) by Region Payments Central $45 Mid-Atlantic $65 Midwest $50 Northeast $90 Northwest $70 Southeast Southwest A $80 $60