Based on the lecture “Integer Program,” work on the problem below on the Excel template provided. When you are finished, you should ONLY upload a single Excel file that contain only one workbook of your answer. Once you submit your answer, you should see the solution Excel file.
Call Center
When you lease 800 phone numbers from AT&T for telemarketing, AT&T uses an optimization model to tell you where you should locate calling centers to minimize your operating costs over a 10-year horizon. To illustrate the model, suppose you are considering seven calling center locations: Boston, New York, Charlotte, Dallas, Chicago, Los Angeles, and Omaha. You know the average cost (in dollars) incurred if a telemarketing call is made from any these cities to any region of the country. This information is listed in the attached spreadsheet.
Assume that an average call requires 4 minutes of labor. You make calls 250 days per year, and the average number of calls made per day to each region of the country is provided in the attached spreadsheet.
The cost (in millions of dollars) of building a calling center in each possible location and the hourly wage that you must pay workers in each city is listed in spreadsheet. Each calling center can make up to 5000 calls per day.
Given this information, you want to minimize the discounted cost (at 10% per year) of running the telemarketing operation for 10 years. Assume all wage and calling costs are paid at the ends of the respective years.
Question:
- Formulate the problem as an integer program and implement the model in the spreadsheet.