Spreadsheet: 86665d1286613789-conditional-array-and-vlookup-example-file-attached-test-2
Sheet: Hayys CAPEX

 |  |  | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020
 | Example only |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | DISTANCE ARRAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | CABLE QUANTITY ARRAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | CABLE TYPE ARRAY
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Cumulative Customer Numbers at each point in Tree Network
 | Brownfield |  |  |  |  |  | Primary Network Remediation Upgrade |  |  |  |  |  |  | Access Upgrade |  |  |  |  | Indicator |  |  | Premesis |  | Corporate |  | Total |  | Current Service Status: |  |  |  |  | Current Network Status |  |  |  |  |  | Current Civils Status |  |  |  |  | Central Office through to 2nd Cabinet |  |  |  | C1 to [NAME] (a-c) |  |  | C1/JB1(a)/JB2 (a-c) |  |  | C1/JB1(b)/JB2 (a-c) |  |  | C1/JB1(c)/JB2 (a-c) |  |  |  | Qty of derived cable types to min cost/seg |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Cable Type (fibres per cable) - achieves min cost / segment |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Fibre Filaments Required / segment
 | [NAME] | ID | Type Node | Mother PN ID | Mother CO ID |  | CO to PN | Upgrade Decision | Date (Yr.) | PN to C | Upgrade Decision | Date (Yr.) |  | Tech. | Min Service | Max Service | Date (Yr.) |  | Rev/Line | Density |  | Total -all units |  | % Total Premises |  | Area m2 |  | # Lines | # ADSL | Total Rev | xDSL Rev |  | ADSL | MSAN Y/N | MSAN Model | Ports op bal. |  |  | Direct Dig C-HH? | Copper Fix? | QoS: C to H'H | Other |  | CO to PN | PN to C1 | PN to C2 | PN to C3 | C1/JB1/(a) | C1/JB1/(b) | C1/JB1/(c) | C1/JB1(a)/JB2(a) | C1/JB1(a)/JB2(b) | C1/JB1(a)/JB2(c) | C1/JB1(b)/JB2(a) | C1/JB1(b)/JB2(b) | C1/JB1(b)/JB2(c) | C1/JB1(c)/JB2(a) | C1/JB1(c)/JB2(b) | C1/JB1(c)/JB2(c) |  | Metro side of CO | CO to PN | PN to C | C/JB1(a) | C/JB1(b) | C/JB1(c) | C/JB1(a)/JB2(a) | C/JB1(a)/JB2(b) | C/JB1(a)/JB2(c) | C/JB1(b)/JB2(a) | C/JB1(b)/JB2(b) | C/JB1(b)/JB2(c) | C/JB1(c)/JB2(a) | C/JB1(c)/JB2(b) | C/JB1(c)/JB2(c) |  | Metro side of CO | CO to PN | PN to C | C/JB1(a) | C/JB1(b) | C/JB1(c) | C/JB1(a)/JB2(a) | C/JB1(a)/JB2(b) | C/JB1(a)/JB2(c) | C/JB1(b)/JB2(a) | C/JB1(b)/JB2(b) | C/JB1(b)/JB2(c) | C/JB1(c)/JB2(a) | C/JB1(c)/JB2(b) | C/JB1(c)/JB2(c) |  | Metro side of CO | CO to PN | PN to C | C/JB1(a) | C/JB1(b) | C/JB1(c) | C/JB1(a)/JB2(a) | C/JB1(a)/JB2(b) | C/JB1(a)/JB2(c) | C/JB1(b)/JB2(a) | C/JB1(b)/JB2(b) | C/JB1(b)/JB2(c) | C/JB1(c)/JB2(a) | C/JB1(c)/JB2(b) | C/JB1(c)/JB2(c) | Cabinet | JB1(a) | JB1(b) | [NAME]) | JB1(a)/JB2(a) | JB1(a)/JB2(b) | JB1(a)/JB2(c) | JB1(b)/JB2(a) | JB1(b)/JB2(b) | JB1(b)/JB2(c) | JB1(c)/JB2(a) | JB1(c)/JB2(b) | JB1(c)/JB2(c)
 | A | 123 | C | 456 | 999 |  | C | Y | 2011 | F | Y | 2011 |  | PTP |  |  | 2016 |  |  |  |  | 500 |  | 0.8 |  | 300 |  | 1500 | 200 | 200000 | 5000 |  | No | Y | Alpha | 500 |  |  | No | No | 16 |  |  | 900 | 500 | 400 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 6300 | 2700 | 2700 | 900 | 900 | 900 | 900 | 900 | 900 | 900 | 300 | 300 | 300
 | B | 456 | PN | 456 | 999 |  | C | N | 2011 | F | Y | 2011 |  | PTP |  |  | 2012 |  |  |  |  | 800 |  | 0.2 |  | 900 |  | 500 | 50 | 25000 | 15000 |  | No | Y | Beta | 100 |  |  | Yes | No | 10 |  |  | 1000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | C | 789 | PN | 789 | 666 |  | F | Y | 2011 | C | Y | 2011 |  | VDSL |  |  | 2015 |  |  |  |  | 900 |  | 0.02 |  | 200 |  | 500 | 50 | 10000 | 8000 |  | Y | Y | Beta | 200 |  |  | No | Yes | 15 |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | D | 1011 | PN | 1011 | 999 |  | F | Y | 2012 | C | Y | 2012 |  | VDSL |  |  | 2016 |  |  |  |  | 901 |  | 0.02 |  | 200 |  | 500 | 50 | 10000 | 8000 |  | Y | Y | Beta | 201 |  |  |  |  |  |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | E | 1012 | C | 456 | 666 |  | F | Y | 2013 | C | Y | 2013 |  | GPON |  |  | 2017 |  |  |  |  | 902 |  | 0.02 |  | 200 |  | 500 | 50 | 10000 | 8000 |  | Y | Y | Beta | 202 |  |  |  |  |  |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | F | 1013 | C | 789 | 666 |  | F | Y | 2014 | C | Y | 2014 |  | VDSL |  |  | 2018 |  |  |  |  | 903 |  | 0.02 |  | 200 |  | 500 | 50 | 10000 | 8000 |  | Y | Y | Beta | 203 |  |  |  |  |  |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | G | 1014 | C | 1011 | 666 |  | F | Y | 2015 | C | Y | 2015 |  | VDSL |  |  | 2019 |  |  |  |  | 904 |  | 0.02 |  | 200 |  | 500 | 50 | 10000 | 8000 |  | Y | Y | Beta | 204 |  |  |  |  |  |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | H | 999 | CO | 999 | 999 |  | C | Y | 2016 | C | Y | 2016 |  | VDSL |  |  | 2020 |  |  |  |  | 905 |  | 0.02 |  | 200 |  | 500 | 50 | 10000 | 8000 |  | Y | Y | Beta | 205 |  |  |  |  |  |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 | I | 666 | CO | 666 | 666 |  | C | Y | 2011 | C | Y | 2011 |  | VDSL |  |  | 2015 |  |  |  |  | 500 |  | 0.02 |  | 400 |  | 500 | 50 | 50000 | 5000 |  | Y | Y | Beta | 300 |  |  | No | No | 20 |  |  | 2000 | 500 | 400 | 300 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 | 400 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8400 | 3600 | 3600 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 1200 | 400 | 400 | 400
 |  |  |  |  |  |  | [NAME] | [NAME]
 | Top-Level Dimension Assumptions |  |  |  |  |  | 2 | 32
 |  |  |  |  |  |  | Utilisation | 0.85
 |  |  |  |  |  |  | Corporates | 0.15
 | Network Deployment |  |  |  |  |  | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.7 | 0.8 | 0.9 | 1 | 1
 | Incremental
 | BROWNFIELD
 | VDSL Ports Required (customer side) |  |  |  |  |  | 1500
 |  |  |  |  |  | [NAME] explanation: Cost=function (conditional array, distance array, cable quantity array, cable type as a basis for referral to the price look-up)
 |  |  |  |  |  | The formula is intended to check the cable type in column BZ and then multiply by the quanitity of that cable type in column BJ using the VLOOKUP to check which price from the price schedule below to multiply through by to get a total cost.
 | Fibre Distance | meters
 | CO to PN (s.t Remediation) |  |  | PROBLEM
 | PN to Cabinets (s.t Remediation)
 | C/JB1(a)
 | C/JB1(b)
 | C/JB1(c)
 | UNIT PRICES |  |  |  |  | PRICE SCEHEDULE - THESE PRICES RELATE TO THE CABLE TYPE WHICH IS CALCULATED COMMENCING COLUMN EB
 | Fibre & Installation Cost/Meter
 | Do not delete this row => | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | DO NOT DELETE
 | 276 fibre pairs | 276 | 8
 | 240 fibre pairs | 240 | 7
 | 192 fibre pairs | 192 | 6
 | 144 fibre pairs | 144 | 5
 | 96 fibre pairs | 96 | 4
 | 48 fibre pairs | 48 | 3
 | 24 fibre pairs | 24 | 2
 | 12 fibre pairs | 12 | 1.4
 | 8 fibre pairs | 8 | 1.2
 | 4 fibre pairs | 4 | 1
 | 2 fibre pairs | 2 | 1
 | Price change (-ve/+ve) |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0