Analyst Role - Case Study
Analyst Role - Case Study
Analyst Role - Case Study
Overview
This workbook is for use as a case study as part of Grex Consulting's application process.
Instructions
Answer the below questions (1-6) using the data provided in tab "Extracts - 31 March 2021". Create tabs and
manipulate data as needed. Use formulas, Pivot Tables, etc. to help shape your presentation. We recommended
creating a duplicate of the data in case you need to start from scratch.
The end product of this exercise is a PowerPoint presentation, between 5 and 10 slides long. You will be presenting
this to us virtually, so please keep that in mind when creating the presentation. Be mindful of key messages, slide
overcrowding, what visuals to use (visuals should be easy to understand and should answer the question in the
simplest means possible). Slides should be succint, but should tell a clear and insightful story.
This is a test of analytical skill and ability, presentation creativity, and depth of insights.
Data Source
This data has been made publicly available by the Australian Competition & Consumer Commission, and can be
found at https://www.accc.gov.au/regulated-infrastructure/communications/national-broadband-network-nbn/nbn-
wholesale-market-indicators-report/march-quarter-2021-report
Intepretation
There are many acronyms in the telecommunications industry, and this workbook is no exception. For a guide to
interpreting the data contained in tab "Extracts - 31 March 2021", please see
https://www.accc.gov.au/system/files/NBN%20SIO%20RKR%20-%20Guide%20to%20interpreting%20NBN
%20wholesale%20market%20indicators%20report%20tables_1.pdf
Any acronyms or terms not explained can be easily found with a Google search.
QUESTIONS
Question 1
What is Telstra's FTTP footprint in Australia on a State basis?
Question 2
Provide an overview of Regional Queensland Points of Interconnect (PoIs) for TC4 AVCs.
Question 3
Provide an overview of Services in Operation (SIOs) throughout Australia, split by Access Seeker Group and by
location (Metro, Outer Metro, Regional).
Question 4
What is Aussie Broadband's total TC4 AVC footprint in Australia? Split by technology type (FTTP, FTTN, HFC, etc.).
Question 5
Show the different proportions of Access Seeker Group for SIOs by data download transfer rate.
Question 6 - Bonus
What insights and other information can be inferred from the data?
This is an open-ended bonus question with no right answer. Creativity and critical thinking are being tested.
Extracts of reports prepared under the amended Disclosure Direction provided to nbn co limited on 28 September 2020 in relation to the NBN Services in
Operation Record Keeping and Reporting Rules
The data presented in the extracts relate to ‘Network Access Services’ supplied by nbn, and do not include any services that are supplied over other access networks.
The AVC and CVC data presented in the extracts do not separately identify access services that are used by access seekers to supply retail or wholesale services. Hence the data presented in the extracts are not intended to provide
a reliable view of the share of total nbn Network Access Services, or of total services, that a provider supplies in the retail market.
Capitalised terms have the same meaning as in the NBN Services in Operation Record Keeping and Reporting Rules (NBN SIO RKR).
Table 2 – Sum of contracted CVC capacity by State or Territory and by CVC traffic class, 31 March 2021
Class
1 2 4
State Mbps Mbps Mbps
ACT 1,230 3,770 429,100
NSW 23,000 64,735 6,718,625
NT 700 3,500 148,200
QLD 15,645 37,005 4,580,550
SA 4,730 13,450 1,350,350
TAS 2,170 4,955 450,200
VIC 18,340 43,130 5,227,150
WA 7,050 24,730 2,141,700
Total 72,865 195,275 21,045,875
Listed POIs
# Access
Seeker #
Groups
0 0
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 5
12 24
13 20
14 20
15 16
16 20
17 10
18 5
19 0
20 0
21 0
22 0
23 1
24+ 0
Notes:
1. As defined in the Dislosure Direction (clause 3), 'Listed POI' means a POI that is set out in the document titled "Listed Points of Interconnection - NBN Corporation", published by the ACCC on 2 November 2012.
2. An NBN Access Seeker Group is counted as being present at a POI if it has contracted CVC capacity at that POI.
Notes:
1. As defined in the Disclosure Direction, (clause 3) 'NBN Access Seeker Group' means two or more Access Seekers which are acquiring Network Access Services and that are related to each other at the end of the relevant
calendar quarter.
2. If the count of TC-4 AVCs acquired by any NBN Access Seeker Group is less than 1% of the total TC-4 AVCs for that Network Access Service as at the end of the calendar quarter, that count is aggregated in the 'Other Access
Seekers' line item.
12 Mbps 25 Mbps 50 Mbps Wireless Plus 100 Mbps 250 Mbps 500 Mbps Home Ultrafast 1000 Mbps
Access Seeker Group SIO SIO SIO SIO SIO SIO SIO SIO SIO
Telstra Corporation Ltd 457,464 711,367 2,141,290 135,550 239,447 47,096 55 833 18
TPG Internet Pty Ltd 426,412 311,021 663,067 1,720 214,755 335,761 34 62869 6
Optus Fixed Infrastructure Pty Limited 62,906 108,030 880,880 5,520 142,066 89,369 43 1018 7
Vocus Communications 19,357 131,072 356,649 22,517 60,840 1,207 16 - -
Aussie Broadband Limited 8,334 74,664 134,859 19,849 110,122 8,963 115 10110 215
Other Access Seekers 41,705 77,622 94,965 14,004 36,238 8,693 259 8088 94
Total 1,016,178 1,413,776 4,271,710 199,160 803,468 491,089 522 82,918 340
Notes:
1. As defined in the Disclosure Direction, (clause 3) 'NBN Access Seeker Group' means two or more Access Seekers which are acquiring Network Access Services and that are related to each other at the end of the relevant
calendar quarter.
2. If the count of TC-4 AVCs acquired by any NBN Access Seeker Group is less than 1% of the total TC-4 AVCs as at the end of the calendar quarter, that count is aggregated in the 'Other Access Seekers' line item.
3. AVCs that are supplied with a data transfer rate tier that comprises a single specified download data transfer rate are aggregated with all other AVCs that are supplied with a data transfer rate tier with the same specified
download data transfer rate. A data transfer rate tier that comprises a range of download transfer rates is treated as though it comprised a single download data transfer rate that nbn offers on a data transfer rate tier, and this
is selected to be equal to the maximum of the range of download data transfer rates available on the relevant data transfer rate tier.
4. The 'Wireless Plus' TC-4 AVC relates to the Wireless bandwidth profile that aims to deliver the maximum potential wholesale download speed the Wireless network is capable of delivering to a particular premises at the time
of use. This bandwidth profile is not offered as a “range PIR” product, nor can it be characterised as a product with a uniform specified PIR, as the theoretical maximum speed will differ at each premises and may be no more
than 25/5 Mbps.
5. The Home Ultrafast AVC TC-4 bandwidth profile has a downstream PIR of '500 to ~1000 Mbps'. The range shows the maximum PIR which may be achieved at the nbn network boundary. The PIR can fall anywhere in the range
in respect of a particular AVC TC-4 used to serve a premises (they are not minimum-maximum PIR ranges). Inherent limitation of nbn Ethernet in relation to frame overhead means the effective Layer 2 PIR will be limited to,
depending on the Frame Size, up to a maximum of 990Mbps (at 2,000 Byte Frame Size). For the HFC Home Ultrafast bandwidth profile, the Layer 2 wholesale downstream service will be configured at the Layer 2 network
management to a Maximum Sustained Information Rate of 750Mbps with the potential to burst up to a maximum of 990Mbps (depending on but not limited to, the Frame Size and line speed capability) at potential burst
durations between 1 to 50 seconds at least once a day. Please see the WBA for all applicable terms and conditions.
Table 6 - Count of TC-4 AVCs by State or Territory and by NBN Access Seeker Group, 31 March 2021
Notes:
1. Excludes AVCs supplied on a Satellite Access Service.
2. As defined in the Disclosure Direction, (clause 3) 'NBN Access Seeker Group' means two or more Access Seekers which are acquiring Network Access Services and that are related to each other at the end of the relevant calendar quarter.
3. If the count of TC-4 AVCs acquired by any NBN Access Seeker Group is less than 1% of the total TC-4 AVCs as at the end of the calendar quarter, that count is aggregated in the 'Other Access Seekers' line item.
Table 7 - Count of TC-4 AVCs by metropolitan/regional designation and by NBN Access Seeker Group, 31 March 2021
Notes:
1. As defined in the Disclosure Direction (clause 3), 'NBN Access Seeker Group' means two or more Access Seekers which are acquiring Network Access Services and that are related to each other at the end of the relevant calendar quarter.
2. With the exception of Satellite Access Services, AVCs are designated as 'Metro', 'Outer Metro' or 'Regional' based upon the designation that is given to the POI to which they are connected in the document titled "Listed Points of Interconnection - NBN Corporation",
published by the ACCC on 2 November 2012.
3. For the purposes of this table, an AVC that is supplied on a Satellite Access Service is designated as 'Regional'.
4. If the count of TC-4 AVCs acquired by any NBN Access Seeker Group is less than 1% of the total TC-4 AVCs as at the end of the calendar quarter, that count is aggregated in the 'Other Access Seekers' line item.
Access Seeker
TC1 AVCs TC2 AVCs TC4 AVCs Total CVC (Mbps) Groups
POI Name State POI Location Fixed Wireless Fixed Wireless Fixed Wireless TC1 TC2 TC4 CVC
Acacia Ridge Depot QLD Metro 64,762 - 198 - 82,035 - 640 1,370 231,600 16
Albury NSW Regional 57,416 7785 506 0 72755 13477 915 3,130 187,400 15
Applecross WA Metro 38,940 0 182 0 62664 0 555 1,220 139,050 13
Aspley QLD Metro 49,325 - 231 - 63,432 - 715 1,475 185,450 14
Aspley Depot QLD Metro 51,194 185 137 - 65,193 329 490 930 186,850 15
Asquith Depot NSW Metro 34,599 - 87 - 43,073 - 345 1,115 113,500 13
Ballarat VIC Regional 54,194 5,597 348 - 73,701 9,487 930 2,390 212,050 18
Bassendean WA Metro 47,196 - 308 - 71,295 - 450 1,950 157,950 14
Bendigo VIC Regional 63,118 7,500 353 - 84,089 12,955 905 1,720 235,650 17
Bentley Depot WA Outer Metro 30,771 2,660 259 - 45,315 4,360 400 2,280 127,150 13
Berkeley Vale NSW Regional 44,131 409 236 - 56,728 705 655 1,615 170,000 16
Blacktown NSW Metro 53,504 - 275 - 66,863 - 590 2,395 198,000 13
Bundaberg QLD Regional 68,125 11,033 480 - 82,461 17,713 970 2,155 256,950 16
Bundamba QLD Metro 47,380 - 203 - 61,791 - 530 1,465 179,000 13
Caboolture QLD Outer Metro 65,067 2,018 284 - 84,739 3,094 820 1,295 261,050 13
Cairns QLD Regional 52,031 2,347 554 - 67,787 3,849 740 2,885 171,550 15
Camp Hill QLD Metro 57,602 - 314 - 73,380 - 585 2,000 212,350 15
Campbelltown NSW Outer Metro 90,478 2,171 438 - 115,939 3,663 1,095 2,730 347,850 16
Campsie NSW Metro 42,632 - 76 - 53,146 - 345 800 133,450 14
Cannington WA Metro 31,608 108 226 - 46,578 210 310 1,250 109,750 11
Carramar NSW Metro 41,668 - 218 - 49,845 - 390 1,265 149,800 13
Castle Hill NSW Metro 47,203 359 183 - 60,842 621 590 1,425 189,200 16
Caulfield VIC Metro 55,539 - 74 - 69,558 - 575 990 167,500 12
Chatswood NSW Metro 35,979 - 72 - 44,333 - 345 800 112,650 12
Cheltenham VIC Metro 48,076 - 115 - 59,294 - 485 1,125 145,350 12
City South NSW Metro 17,262 - 206 - 27,296 - 250 1,115 62,050 14
Civic ACT Regional 68,704 - 370 - 105,589 - 745 2,060 262,450 17
Coffs Harbour NSW Regional 64,741 8,435 424 - 84,749 13,728 1,080 2,980 222,300 16
Cranbourne VIC Outer Metro 68,884 3,624 284 - 94,954 7,197 930 1,715 295,150 18
Dalley NSW Metro 16,485 - 302 - 24,318 - 260 1,545 51,550 16
Dandenong VIC Metro 52,717 - 154 - 64,482 - 515 1,125 169,650 12
Darwin NT Regional 38,673 1,856 557 - 56,847 3,011 700 3,500 148,200 12
Doubleview WA Metro 40,348 - 201 - 62,532 - 385 1,245 134,950 12
Dubbo NSW Regional 62,456 7,465 620 - 79,541 12,781 1,045 3,465 222,000 15
Eastern Creek Depot NSW Metro 61,626 128 117 - 74,911 229 600 1,160 255,450 15
Edgecliff NSW Metro 35,509 - 60 - 44,786 - 360 830 107,050 14
Edwardstown SA Metro 44,198 - 87 - 55,113 - 415 810 119,500 12
Eight Mile QLD Metro 40,234 - 86 - 49,711 - 440 575 148,650 15
Elizabeth SA Outer Metro 62,150 5,269 358 - 84,053 9,363 795 2,165 254,350 14
Exhibition VIC Metro 37,907 - 475 - 60,026 - 540 2,900 122,050 17
Footscray VIC Metro 50,862 - 292 - 69,187 - 480 1,610 169,950 16
Frenchs Forest NSW Metro 46,179 - 88 - 56,648 - 430 835 150,250 14
Geelong VIC Regional 72,363 3,461 346 - 96,322 5,968 925 2,345 222,800 15
Geraldton WA Regional 32,562 503 484 - 47,757 930 530 2,530 126,350 12
Glebe NSW Metro 29,571 - 88 - 38,891 - 300 815 92,250 16
Goodna QLD Metro 70,555 2,170 425 - 92,950 3,548 945 2,370 322,650 18
Gosford NSW Regional 53,580 - 262 - 69,511 - 795 1,825 175,100 17
Grafton NSW Regional 61,332 9,470 333 - 80,785 16,257 915 2,585 216,400 17
Greenfields Depot SA Metro 44,787 - 437 - 66,023 - 455 1,815 146,200 13
Hamilton NSW Regional 89,348 - 527 - 116,709 - 1,050 2,955 289,750 16
Hawthorn VIC Metro 45,402 - 101 - 60,844 - 460 1,265 136,000 15
Hilton WA Metro 31,725 - 239 - 51,884 - 365 1,505 107,750 12
Hobart TAS Regional 58,801 5,952 460 - 92,636 10,942 1,075 2,460 239,650 16
Horsham VIC Regional 30,452 7,010 272 - 40,930 14,387 705 1,710 113,700 14
Ipswich QLD Outer Metro 28,593 2,572 170 - 37,041 3,893 425 1,055 135,850 13
Karingal VIC Metro 50,090 416 183 - 64,489 703 630 1,190 170,950 12
Katanning WA Regional 41,480 4,741 532 - 60,007 9,657 680 3,120 150,950 13
Kelmscott WA Outer Metro 52,142 1,040 202 - 77,856 1,747 550 1,295 210,100 13
Kensington NSW Metro 49,986 - 143 - 64,330 - 430 1,240 156,300 12
Kew VIC Metro 46,598 - 71 - 62,032 - 460 800 141,850 13
Keysborough VIC Outer Metro 51,282 - 254 - 64,076 - 585 1,445 184,050 15
Kings Park VIC Metro 59,544 - 124 - 73,699 - 660 1,040 219,000 12
Lakemba NSW Metro 45,576 - 100 - 53,646 - 345 900 156,750 11
Launceston TAS Regional 59,648 7,798 493 - 82,304 13,355 1,095 2,495 210,550 16
Lidcombe NSW Metro 53,758 - 240 - 69,390 - 540 1,685 176,800 15
Lilydale VIC Metro 51,294 1,059 123 - 65,220 1,859 595 975 174,700 12
Liverpool NSW Metro 50,476 - 166 - 62,490 - 560 1,125 200,950 16
Lonsdale Depot SA Metro 31,822 - 174 - 50,044 - 370 820 125,550 11
Mackay QLD Regional 39,633 6,965 431 - 49,268 10,102 785 1,960 154,250 16
Maitland NSW Regional 58,725 3,592 329 - 74,337 5,694 880 2,730 241,900 16
Mayfield NSW Regional 38,852 1,508 209 - 48,740 2,515 520 1,415 121,750 16
Merrimac QLD Regional 45,021 - 157 - 61,340 - 570 1,150 161,550 13
Miranda NSW Metro 56,558 - 195 - 69,523 - 535 1,515 179,050 12
Modbury SA Metro 41,556 - 107 - 53,063 - 425 855 129,350 13
Mosman NSW Metro 47,389 - 118 - 59,060 - 460 1,155 144,350 12
Mount Eliza VIC Metro 46,334 419 174 - 58,601 667 590 940 124,650 11
Mullaloo WA Outer Metro 47,033 76 209 - 66,360 132 545 1,475 183,600 13
Nambour QLD Regional 79,279 3,704 559 - 106,338 6,027 1,090 2,405 270,350 18
Nerang QLD Regional 51,540 1,157 230 - 71,719 1,740 655 1,220 234,150 14
Newtown NSW Metro 40,452 - 69 - 54,043 - 340 775 132,350 14
North Balwyn VIC Metro 52,246 - 81 - 62,708 - 510 895 155,000 13
Nowra-Bomaderry NSW Regional 56,909 5,458 270 - 75,427 9,944 825 2,215 186,600 16
Nunawading Depot VIC Metro 55,465 - 77 - 68,837 - 520 980 167,450 14
Parramatta NSW Metro 29,765 - 58 - 38,400 - 265 875 103,950 14
Peakhurst NSW Metro 43,346 - 99 - 52,328 - 320 685 140,600 12
Pendle Hill NSW Metro 39,939 - 58 - 47,798 - 365 670 131,800 11
Penrith NSW Metro 42,538 678 223 - 55,832 1,023 515 1,565 146,250 14
Petrie QLD Metro 60,802 - 292 - 80,519 - 805 1,275 255,200 15
Pinjarra WA Regional 48,762 2,434 325 - 69,206 4,174 790 2,280 182,100 14
Port Augusta SA Regional 27,820 4,118 411 - 39,064 8,742 475 1,900 107,000 13
Port Melbourne Depot VIC Metro 31,399 - 162 - 48,085 - 360 1,290 102,600 15
Prospect SA Metro 44,673 - 234 - 61,477 - 455 1,630 125,450 13
Queanbeyan ACT Regional 45,253 1,116 267 - 63,257 2,168 485 1,710 166,650 16
Reservoir VIC Metro 52,951 - 95 - 65,105 - 460 905 159,600 12
Ringwood VIC Metro 58,629 - 56 - 72,333 - 580 715 179,550 14
Rockdale NSW Metro 46,188 - 159 - 57,545 - 355 1,135 152,300 14
Rockhampton QLD Regional 48,676 2,032 606 - 60,595 3,201 770 2,210 176,000 16
Ryde NSW Metro 51,743 - 174 - 66,245 - 390 1,125 161,750 12
Shepparton VIC Regional 48,650 11,935 432 - 62,232 20,434 895 2,455 183,200 15
Slacks Creek QLD Metro 54,401 - 238 - 70,132 - 655 1,515 246,900 16
South Coogee WA Regional 54,531 - 267 - 82,195 - 645 1,855 237,000 12
South Morang VIC Metro 58,348 1,006 179 - 74,737 1,657 675 1,380 215,450 17
Southport QLD Regional 46,156 - 174 - 61,459 - 555 1,265 171,350 14
St Kilda VIC Metro 35,711 - 37 - 45,649 - 355 585 113,350 12
St Leonards NSW Metro 29,910 - 183 - 41,035 - 320 930 93,350 14
St Marys SA Metro 48,220 - 187 - 66,815 - 490 1,195 141,300 12
Stirling SA Regional 53,094 6,816 458 - 79,693 13,390 850 2,260 201,650 13
Subiaco WA Metro 41,773 - 239 - 58,183 - 405 1,975 120,050 12
Tamworth NSW Regional 35,578 5,419 366 - 46,653 8,660 640 2,625 135,650 17
Thornbury VIC Metro 45,300 - 151 - 64,748 - 445 1,425 147,550 14
Toowoomba QLD Regional 62,869 9,743 571 - 80,947 15,329 1,025 2,595 252,150 18
Townsville QLD Regional 62,727 4,962 605 - 81,034 8,529 1,060 2,790 231,300 15
Traralgon VIC Regional 39,067 8,250 381 - 58,943 17,425 655 2,290 158,650 14
Tullamarine VIC Regional 71,098 - 336 - 88,141 - 755 2,050 241,650 15
Wagga Wagga NSW Regional 33,205 6,532 311 - 43,035 10,635 535 1,635 125,350 17
Wangara Depot WA Metro 45,904 - 116 - 61,305 - 440 750 154,950 12
Werribee VIC Metro 68,052 - 171 - 89,384 - 745 1,565 273,350 17
Wheelers Hill VIC Metro 39,639 - 104 - 48,352 - 415 1,310 124,700 13
Windsor NSW Metro 24,376 1,519 110 - 32,639 2,381 360 825 106,850 12
Wollongong NSW Regional 70,194 - 398 - 92,987 - 1,010 2,525 245,500 17
Woolloongabba QLD Metro 35,600 - 127 - 50,281 - 375 1,045 135,400 15
Notes:
1. Excludes AVCs and CVC supplied on a Satellite Access Service.
2. An NBN Access Seeker Group with contracted CVC capacity on a Satellite Access Service only is not included in the count of NBN Access Seeker Groups with contracted CVC capacity at a Listed POI.
3. As defined in the Disclosure Direction, (clause 3) 'NBN Access Seeker Group' means two or more Access Seekers which are acquiring Network Access Services and that are related to each other at the end of the relevant calendar quarter.
4. An NBN Access Seeker Group is counted as being present at a POI if it has contracted CVC capacity at that POI.
5. A POI is designated as 'Metro', 'Outer Metro' or 'Regional' based upon the designation it is given in the document titled "Listed Points of Interconnection - NBN Corporation", published by the ACCC on 2 November 2012.
6. AVC counts under the subheading 'Fixed' include FTTP, FTTN, FTTB, FTTC and HFC access services.
7. A number of factors can influence the sum of contracted CVC capacity at a POI relative to the count of AVCs at that POI, such as POI location (i.e. Metro/Outer Metro/Regional), number of serviceable premises at the POI, and differences in demographics.