Using excel: A telecom company needs to build a set of cell towers to provide signal coverage for the inhabitants of a given city. A number of potential locations where the towers could be built have been identified. The towers have a fixed range, and due to budget constraints only a limited number of them can be built. Given these restrictions, the company wishes to provide coverage to the largest percentage of the population possible. To simplify the problem, the company has split the area it wishes to cover into a set of regions, each of which has a known population. The goal is then to choose which of the potential regions the company should build cell towers on in order to provide coverage to as many people as possible. The table below shows which of the regions are covered when a tower is build in that region. Note "-" means the tower build cannot serve the region. Region 0 Region 1 Region 2 Region 3 Region 4 Region 5 Region 0 1 1 - - - 1 Region 1 1 1 - - - - Region 2 - - 1 1 - - Region 3 - - 1 1 - 1 Region 4 - - - - 1 - Region 5 1 - - 1 - 1 The population at each region is stated in the following table. Region 0 Region 1 Region 2 Region 3 Region 4 Region 5 Population 523 690 420 1010 1200 850 The cost to build a cell tower at each location site is stated in the following table. Cost (millions of USD) Region 0 4.2 Region 1 6.1 Region 2 5.2 Region 3 5.5 Region 4 4.8 Region 5 9.2 The allocated budget is $20,000,000 Hint: Objective is to maximize the population covered. Hint 2: Model this problem as a set-covering problem. An additional constraint on the budget needs to be added to the model.
Using excel:
A telecom company needs to build a set of cell towers to provide signal coverage for the inhabitants of a given city. A number of potential locations where the towers could be built have been identified. The towers have a fixed range, and due to budget constraints only a limited number of them can be built. Given these restrictions, the company wishes to provide coverage to the largest percentage of the population possible. To simplify the problem, the company has split the area it wishes to cover into a set of regions, each of which has a known population. The goal is then to choose which of the potential regions the company should build cell towers on in order to provide coverage to as many people as possible.
The table below shows which of the regions are covered when a tower is build in that region. Note "-" means the tower build cannot serve the region.
|
Region 0 |
Region 1 |
Region 2 |
Region 3 |
Region 4 |
Region 5 |
|
|
Region 0 |
1 |
1 |
- |
- |
- |
1 |
|
Region 1 |
1 |
1 |
- |
- |
- |
- |
|
Region 2 |
- |
- |
1 |
1 |
- |
- |
|
Region 3 |
- |
- |
1 |
1 |
- |
1 |
|
Region 4 |
- |
- |
- |
- |
1 |
- |
|
Region 5 |
1 |
- |
- |
1 |
- |
1 |
The population at each region is stated in the following table.
|
Region 0 |
Region 1 |
Region 2 |
Region 3 |
Region 4 |
Region 5 |
|
|
Population |
523 |
690 |
420 |
1010 |
1200 |
850 |
The cost to build a cell tower at each location site is stated in the following table.
|
Cost (millions of USD) |
|
|
Region 0 |
4.2 |
|
Region 1 |
6.1 |
|
Region 2 |
5.2 |
|
Region 3 |
5.5 |
|
Region 4 |
4.8 |
|
Region 5 |
9.2 |
The allocated budget is $20,000,000
Hint: Objective is to maximize the population covered.
Hint 2: Model this problem as a set-covering problem. An additional constraint on the budget needs to be added to the model.
Excel is a widely used spreadsheet software developed by Microsoft. It provides a versatile platform for creating, organizing, analyzing, and presenting data in tabular form. Users can perform calculations, create charts, graphs, and pivot tables, and apply various functions to manipulate and visualize data. Excel is commonly utilized for tasks ranging from simple data entry and basic calculations to complex data analysis, financial modeling, and business reporting.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 2 images
