Assignment:
Write a one page memo and an excel spreadsheet table to determine the solution.
You need to decide where to locate call centers and determine the routing of calls to achieve the lowest total cost. You are considering seven cities in which to locate call centers. You know the average cost incurred if a call is made from any of these cities to any region of the country. (See first table below) You also know the hourly wage that you must pay workers in each city and the annual cost for the physical facilities (second and third tables below.) Each call center can only handle 5000 calls per day. Assume there are 250 days per year and that each call averages 4 minutes. The average number of calls made per day to each region is given in the last table. Labor and calling costs are variable costs and facility costs are fixed costs. Where do you locate call centers and how to do you route the calls to obtain a minimum cost solution? Remember, no conditional statements (if, max, min, etc.)
There is a lot of data here. To avoid making data entry errors and the time to enter all of this, look at this document on your computer and just copy the tables and paste them into Excel.
Cost per Call (dollars)
|
ew England
|
Middle Atlantic
|
Southeast
|
Southwest
|
Great
Lakes
|
Plains
|
Rocky Mountains
|
Pacific
|
|
Providence
|
1.20
|
1.20
|
1.10
|
1.50
|
2.00
|
2.20
|
2.20
|
2.50
|
|
Philadelphia
|
1.30
|
1.00
|
1.30
|
2.20
|
1.80
|
1.90
|
2.50
|
2.80
|
|
Atlanta
|
1.50
|
1.40
|
0.90
|
1.90
|
2.10
|
2.30
|
2.60
|
3.30
|
|
Fort Worth
|
2.00
|
1.80
|
1.20
|
1.50
|
1.70
|
2.20
|
2.10
|
2.70
|
|
Milwaukee
|
2.10
|
1.90
|
2.30
|
1.50
|
1.10
|
1.30
|
1.20
|
2.20
|
|
San Francisco
|
2.50
|
2.10
|
1.90
|
1.20
|
1.70
|
1.50
|
1.40
|
1.00
|
|
Kansas City
|
2.20
|
2.10
|
2.00
|
1.30
|
1.40
|
0.90
|
0.90
|
1.50
|
Labor Costs by City(per hour)
|
Providence
|
$12
|
|
Philadelphia
|
$16
|
|
Atlanta
|
$11
|
|
Fort Worth
|
$14
|
|
Milwaukee
|
$12
|
|
San Francisco
|
$18
|
|
Kansas City
|
$10
|
Annual Facility Cost
|
Providence
|
$270,000
|
|
Philadelphia
|
$320,000
|
|
Atlanta
|
$210,000
|
|
Fort Worth
|
$220,000
|
|
Milwaukee
|
$240,000
|
|
San Francisco
|
$360,000
|
|
Kansas City
|
$210,000
|
Calls Made per Day
|
New England
|
1500
|
|
Middle Atlantic
|
2000
|
|
Southeast
|
2000
|
|
Southwest
|
2500
|
|
Great Lakes
|
3000
|
|
Plains
|
1000
|
|
Rocky Mountains
|
2000
|
|
Pacific
|
3500
|
Hints:
- The minimum annual cost is $9,607,500.
- Treat labor as a variable cost, that is, we must base this on volume. Getting this to an annual cost can be a little tricky. You would calculate that as follows: calls made per day * days per year * minutes per call * hours per minute * wage dollars per hour. (My management science prof. used to call this 'dimensional analysis.' You need to be able to cancel out all of the units of measure so that in the end all you have left is $ per year. Try making the fractions: calls over day, then day over year, etc. You should find that you can cancel out everything except $ over year. Now you can impress your colleagues and tell them you can do dimensional analysis.)
- Don't forget you have two decisions to make, where to locate the call centers; that should be 8 changing cells. Then we need to know how to route the calls-that will be 56 changing cells, probably formatted in a to/from grid.
- Finally, the best model for this problem is the computer purchase problem. While there are more decisions to make in this problem, the set up is exactly the same: fixed and variable costs, with binary variables and logical constraints. However, there are three costs: facility costs, calling costs, and labor costs. Review the computer purchase problem before you begin.