Monday, August 8, 2016

Where is the economy growing / shrinking? #AU

The Australian Tax Office (ATO) has recently provided me with a dataset to examine innovation in Australia. I am interested in economic activity over time and by location. Previously the ATO has provided corporate data, but companies are seen only as Australian, and not having a point or multi-point location. Employees however have an address which provides a point location.

Thus the ATO has kindly provided all the salaries and wages data (for 12 years), and sole trader data, by fine detailed industry and location. Sole Trader's have 500 industries and employees have 1500 occupations. Australian locations, while requested at postcode level, were provided at a statistical area level (SA4) which splits Australia into 100 regions of similar population. Cities have many zones, while the country has large zones.

See my request for the data here.

The resulting dataset has two tables:
  • Sole Traders (100,000 rows) 2014 $96B, 2002 $61B.
  • Salary and Wages (200,000 rows) 2014 $584B, 2002 $285B.
The raw data is available here at  I have loaded the data into an online database at Nectar, which provides free cloud services for Australian Researchers. Preliminary analysis is taking place at a website set up for that purpose here. For SELECT access to the database please contact me.

Data Visualisation of the Industries of Sole Traders.

See here.

A Data Visualisation of growth by SA4 region

Figure 1: Growth by SA4 region (2006, 2010, 2014). Data

Top ten occupations (2010-2014) - growing / shrinking

Top Ten Growing Occupations**20102014Diff
Sales Assistant (General) 235,449 273,104 37,655
Corporate General Manager 162,899 198,595 35,696
Office Manager 145,522 172,883 27,361
Primary School Teacher 121,599 147,589 25,990
Child Care Worker 73,496 92,813 19,317
Aged or Disabled Carer 103,196 121,359 18,163
Labourers nec 72,007 89,238 17,231
Program or Project Administrator 81,640 94,905 13,265
Registered Nurse (Aged Care) 29,605 42,581 12,976
Machine Operators nec 18,764 31,263 12,499

Top Ten Shrinking Occupations**20102014Diff
Practice Managers nec 131,480 73,434 68,722
General Clerk 361,094 326,118 34,976
Earthmoving Labourer 33,313 13,585 19,728
Sales Representatives nec 90,840 74,679 16,161
Nurse Practitioner 35,031 21,767 13,264
Secretary (General) 46,570 33,800 12,770
Farm 33,781 21,038 12,743
Hospitality Workers nec 40,256 28,725 11,531
Clerical and Administrative Workers nec 44,280 32,874 11,406
Checkout Operator 65,242 54,294 10,948
NB**: excludes unnamed occupations. Full List. Data (1162 lines, csv).

Other sample reports

Several sample reports are now available, including;
  • List of Occupations from largest to smallest
  • List of Occupations from highest average wage to smallest
  • List of Sole Trader industries from highest sales to smallest
  • Change in one Industry over time - Hairdressing.
For more information: contact

Thursday, February 4, 2016

FTTN vs FTTP (6): the graphical version

After attending a Data Visualisation workshop at University of Melbourne this week (Resbaz 2016; D3 Visualisation workshop, workshop; Thanks Isabel and Errol), I have tried to plot graphically the FTTN vs FTTP perspectives. See my outputs here from this week's workshops.

Through playing with the model (available on figshare (v5)), the most important variables seemed to be:

- impact on GDP per household after 20 years
- discounted by an interest rate (0,3,5,10%)
- impacted by a delay in FTTP rollout (0,2,4,6,8 years)
- impacted by Household GDP growth (0,1,2%) externality

as discussed in earlier posts. FTTN (blue) vs FTTP (orange). Some assumptions support FTTN (blue). Other assumptions support FTTP (orange).

Datapoints: GDP per household at Yr 20, discounted to current $$; difference FTTN -  FTTP
eg 1. At Year 20, FTTP Delay = 8 yrs, 0% discount rate, FTTP and FTTN GDP impact is $20k per household. Difference is close to $0. A datapoint of $0 is plotted;a brown dot. Where FTTN GDP > FTTP GDP then plot is blue (positive nos). When FTTN GDP < FTTP GDP impact, then plot is orange (negative nos).
eg 2. At year 20, Delay = 6 years, interest rate 5%, household GDP impact 1% pa; FTTP GDP impact is: $13k; FTTN GDP impact is $20k. So the difference ie $7k is plotted; a light blue dot.

The dots are smoothed into colour sections. Blue sections where the assumptions favour FTTN (preferred by the Liberals Party, in conjunction with HFC, some FTTP and other wireless tech for Regions and Remote) and orange sections where the assumptions favour FTTP (preferred by Labor Party). Live version of image, with mouseover datapoints at: Figshare (v5) to download, and Github to view.

Click pic to enlarge

Figure 1,2,3: Stronger colours indicate more value. FTTN vs FTTP GDP per household impact at Year 20, discounted by interest rate; Y axis shows interest rate (discount rate). X axis shows years delay until FTTP installed. Orange zones indicate FTTP preferred. Blue zones indicate FTTN preferred, per model (linked above). Heatmap coded added to Figshare model (v5). Html version shows GDP values at each point. Blogger unfortunately doesn't run javascript. See live heatmap version here.

var data = [{
  z: [[-12, -9,-6,-3,0],
       [-9,-6, -3,0, 2],
       [-7,-3.5,-1,1.5,3]]; // GDP diff FTTN - FTTP $k per HH, 0% HH GDP imapct

  x: [0,2,4,6,8], // Delay years
  y: [0,3,5,10]}; // Discount rate
z: [[-12, -6.5,2,5,11],
       [-9,-3, 2,7, 11],
       [-7,-1,4,7.5,11]], // GDP diff FTTN - FTTP $k per HH, 1% HH GDP impact
z: [[-12,-4, 5,14,22.5],
       [-9, 0,7,14, 20.5],
       [-7,1,8,14,18]], // GDP diff FTTN - FTTP $k per HH, 2% HH GDP impact
Source: Figshare model (manually entered from model into heatmap html);
view source on Github page.
What the above figure shows is:
  • at 2% impact on household (HH) GDP ($70k household) then FTTN is highly favourable, even if only short two year delay. The HH GDP annual impact $1,400pa is much larger than around $2,000 difference between FTTN and FTTP capex and dwarfs OPEX and revenue differences.
  • at 1% impact on HH GDP, a four year delay in FTTP installation is sufficient to favour FTTN over FTTP.
  • at 0% impact on HH GDP, then FTTP is favourable even with long delays to FTTP. At higher interest rates, then a shorter delay for FTTP is equivalent to FTTN.
In previous cost benefit analyses of NBN, a 0.5 - 1.0% impact on GDP was sufficient to make the NBN NPV positive - ie benefits exceed costs, accounting for time impact of cashflows. At these lower household impacts, FTTP is preferred, but where there are immediate significant household GDP impacts ( ie more than 1%) then FTTN is preferred. Long delays in FTTP encourage more use of FTTN, as do higher interest rates.

NB: outstanding and unaccounted for issues were described in the last post, eg cost of replacing FTTN at the end of its perhaps 10 year life; the excess benefit of FTTP over FTTN, and so on.

FTTN and FTTP are only as good as the assumptions that assert their case.  There are many cases, listed above where FTTN is better than FTTP for GDP over 20 years. Where the NBN impact on household GDP is significant, this prompts an earlier install of FTTN.

Sunday, January 24, 2016

FTTN vs FTTP (5): cheaper now or faster later; a value clash of assumptions

[NB: Best viewed for iPhone in Reader view.]
This is likely the last post in this series, and adds another element to the value assessment.
So far, I compared FTTN and FTTP based on CAPEX, OPEX and Revenue. I aggregated those costs to get a GDP impact. I applied an interest rate to compare cashflows over time. In this post, I add an external GDP impact (an externality) to a household with NBN. The Prime Minister argues for FTTN now, rather than the delay of FTTP. He sees a benefit now, rather than waiting. The analysis so far shows over the longer term, except in an extreme delay (eg 8 years), FTTP is preferred.
Source: wikimedia

However, if an extra boost to GDP comes from getting FTTN early, how much does that change the picture? I modelled a 1% and 2% increase in household GDP for the years between FTTN and FTTP.
That is, if a $70k household generates $700 extra benefit (1%) from using FTTN does that justify FTTN over FTTP? I updated the model to add a household (HH) GDP impact, then tried to find what HH GDP impact makes the GDP impact of FTTN the same as for FTTP. I used both 0% and 10% discount rates. An update to the model  (v4) is now provided which included a GDP boost (variable) for FTTN in the years pending FTTP delay.

Where you end up with is a clash of assumptions. One set of assumptions favour FTTN. Another set of assumptions favour FTTP. I tried to find the line, where the assumptions balance out. Therefore, increasing or decreasing an assumption will point to either FTTN or FTTP. Then the argument becomes which assumptions (or set of assumptions) do I believe/trust/support.

In short, FTTN vs FTTP is a marshmallow test. The marshmallow test is a classic child test. Do you want one marshmallow now, or wait to get two marshmallows? Do you want FTTN now, or wait to get FTTP?

Figure 1: Children wrestle with the Marshmallow test. Some (FTTN) now or more (FTTP) later.

Let's see how the numbers change, when you add some household boost to GDP, on top of revenue paid to NBN Co for FTTN.

I use two interest rates for the assessment of extra HH GDP; 0% and 10%. Later I will add in 3% and 5% in a summary. The first two Tables show interest at 0%. FTTP  figures do not change, since the HH GDP is increased only for the time until FTTP is available. I assume no extra benefit for FTTP, since I have no basis for those figures. The GDP impact benefits come from earlier NBN Cost benefit analysis I have done. They showed a 0.5-1% increase in GDP was needed to make NBN cost benefit analysis positive (DCF basis). See links to Whirlpool NBN Costs Benefit summary.

GDPDelay = 0yrs2 468
Table 1: Year 10: GDP: with GDP boost to FTTN: 0% Un-discounted cash generated by one FTTN or FTTP household at Yr 10, given delay of installing FTTP. Shaded column indicates better outcome.

GDPDelay = 0yrs2 468
Table 2: Year 20: GDP: with GDP boost to FTTN: 0% Un-discounted cash generated by one FTTN or FTTP household at Yr 20, given delay of installing FTTP. Shaded column indicates better outcome.

What these tables show is that extra GDP significantly improves the FTTN proposition.
The 0% is the base case, and the 1% and 2% show major financial gains, the greater the longer the delay in FTTP. Where previously at Yr 20, FTTP was always better except where the delay was 8 years then FTTP and FTTN were the same. With extra GDP, even a four year delay can be enough at high extra GDP for FTTN to be more attractive.

Let's look at how the numbers change if we charge a high interest rate of 10% on each years cashflows. Now all the GDP figures are lower, since later cashflows are worth a lot less. High extra GDP will make a large difference, since the cashflows are early. The longer the FTTP delay, the less GDP (discounted at 10%) generated by FTTP.

GDPDelay = 0yrs2 468
Table 3: Year 10: GDP: with GDP boost to FTTN: 10% Discounted cash generated by one FTTN or FTTP household at Yr 10, given delay of installing FTTP. Shaded column indicates better outcome.

GDPDelay = 0yrs2 468
Table 4: Year 20: GDP: with GDP boost to FTTN: 10% Discounted cash generated by one FTTN or FTTP household at Yr 20, given delay of installing FTTP. Shaded column indicates better outcome.

What Tables 3 and 4 show is a much improved FTTN proposition at 10% interest rate. FTTN is now preferred at Delay of 4, 6, and 8 years, and even a two year delay if the GDP boost of FTTN is high.

This raises the question - where to draw the line between FTTN and FTTP? On reflection and through playing with the model I can show the assumptions where the GDP (discounted) for FTTN becomes the same for FTTP.

GDPDelay = 0yrs2 468

Yr 10i=10%i=5%i=3%i=0%
Yr 20i=10%i=5%i=3%i=0%
Table 5: When does GDP (FTTN = FTTP)? What interest rate (discount) equates to years delay to FTTP?

What I tried to show in this table is what interest rate charged on cashflows make the GDP impact  of FTTN = FTTP.  Thus at Yr 20, with no interest FTTN and FTTP have the same GDP impact, when FTTP is delayed eight years. When a high interest rate is used on the cashflows (10%), then FTTN is the same GDP effect as FTTP, when the delay is only four years.

In the next table, I compare how much extra GDP boost would FTTN require for the GDP impact to be the same as FTTP.

GDPDelay = 0yrs2 468

Yr 10i=10%FTTP > FTTN1-2% (High)0%(Low)
i=0%FTTP >2-3%(High)0-1%(Med)
Yr 20i=10%FTTP >1-2%(High)0-1%(Med)
i=0%FTTP >3-4% (High)1-2%(High)0-1%(Med)0%(Low)
Table 6: When does GDP (FTTN = FTTP)? What GDP boost to FTTN equates to years delay to FTTP?

What Table 6 shows is that FTTP and FTTN are favourable based on different assumptions.
FTTP is favoured when there is no delay and when interest rates are low. When only a two year delay to FTTP, then FTTN needs high GDP boost to be equivalent. When delay is four years, GDP is roughly equivalent at high interest rate, but requires medium GDP boost at low interest after ten years. After 20 years, FTTN needs medium or high boosts to be equivalent to FTTP.

FTTN is favoured when delay is six or eight years, when interest rates are high (10%), or when boosts to GDP are high.  Previous cost benefit analysis showed a 0.5-1% boost in GDP was needed for NBN to be successful (ie DCF positive).

FTTN as an alternative to FTTP is significantly boosted by including a 1 or 2% GDP boost from FTTN now up until a delayed FTTP rollout. A high interest rate (10%) also makes FTTN more attractive if delays to FTTP reach four years.

So, cheaper now(FTTN) or faster later (FTTP), is complex. The model now accounts for:
  1. OPEX, CAPEX and Revenue
  2. Delay in FTTP install
  3. Life of assets (but not replacement of FTTN)
  4. Interest rate (ie DCF discounted cashflows_)
  5. Impact on GDP based on OPEX, CAPEX and Revenue
  6. a GDP boost from NBN
Like the marshmallow test, less now (FTTN) and more later (FTTP) is a value tradeoff, which is personal, subjective and complex. Accounting for a short term GDP boost from FTTN makes a significant difference.

What is not yet counted? More factors can be taken into account. Some hard. Some very hard to assess or estimate.

Hard: what cost to replace FTTN with FTTP and when? eg $4k FTTP CAPEX inflated at 1,3,5%pa, discounted at 0,1,3,5,10%, at Yr 10, 15, 20.
Too Hard: how much more GDP impact will FTTP have over FTTN?
Too Hard: how much customer satisfaction will FTTP and FTTN create?
Too Hard: how to compare customer satisfaction with $$?

Sunday, January 10, 2016

FTTN vs FTTP (4): cheaper and now, or faster, more $$ and later

[NB: Best viewed for iPhone in Reader view.]
In the last post, I mentioned comparing $$ now and $$ later. Which is better: to receive $2100 now or $4400 later? This depends on when, and how much interest is worth to you each year. This is called the discount rate or cost of capital for businesses.

I analysed the NBN CAPEX, OPEX and revenue for FTTN and FTTP over ten and twenty years, and calculated the impact on GDP. A model, I created, showed the time to breakeven (revenue - OPEX divided by CAPEX). The model also adjusted for delays in rolling out FTTP. When FTTP is delayed more than four years, then FTTN is better in the short term, but FTTP always worked out better in the long term (t=20).

An update to the model (V2; the DCF model), included an interest rate, to account for the 'time value of money'.
I used several rates; 0%, 1%, 3%, 5% and 10%. Businesses usually use closer to 10%, while Government might use closer to 3%. The 0% is the same as the cash figures, I showed in the last post.

Below I show the impact on GDP of FTTN, FTTP, delay to rollout FTTP, and interest rate for the 'time value of money'. The results are a little surprising. The later FTTP becomes, the cheaper it becomes in current $, as the interest rate gets higher. At high interest rates, later becomes better. Again, I shade the option which gives the better financial outcome.

The PM states earlier is better. Let's see if he is right. Any positive figures mean the project is worth pursuing at that interest rate. For two projects, the better project is the one with the higher figure. Negatives are shown in brackets. Figures rounded in most cases.

GDPDelay = 0yrs2 468
Figure 1: Discounted cash generated by one FTTN or FTTP household at Yr 10, given delay of installing FTTP. Shaded column indicates better outcome.
[Oops, I will amend the % order of this table to match my preferred order below.]

Some points worth noting:
- FTTN and FTTP are always positive, so on their own are worth pursuing
- FTTP is better when the delay is no more than four years at all interest rates
- FTTN is better when the delay is six or eight years at Yr 10, at all interest rates.

GDPDelay = 0yrs2 468

Figure 2: Discounted cash generated by one FTTN or FTTP household at Yr 20, given delay of installing FTTP. Shaded column indicates better outcome.

What this shows is:
- At Yr 20, FTTP is superior to FTTN except where the delay is eight years, at all interest rates, or at Delay = 6 and Interest = 10%. This differs from the 0% rate , which shows FTTP superior regardless of delay.
- Both projects are viable in their own right
- High interest rates substantially lower the project benefits

NB: However, FTTN does not include replacement after its end of useful life, which could be as soon as Yr 10. That expense would have to be factored into a cost benefit analysis, but I have no data on replacement cost of FTTN or when that would occur.

The interest rates do little to change the pattern of FTTP vs FTTN. Where the delays is less than six years, then FTTP is superior, except at the highest interest rate (10%). Even with an eight year delay, if interest is not counted for then the benefits are similar between FTTP and FTTN. Highest interest rates are better for FTTN when FTTP is delayed six or eight years.

So is earlier better? FTTP is still superior to FTTN even if delayed six years for interest rates up to 5% but not at 10%. At Yr 10, FTTP is only superior if the FTTP delay is four years or less.

Next: I will reconsider what if we add the usage benefits (externalities) of NBN into the calcs, using a 1.5% annual impact on GDP. I will refer the impact to my previous cost benefit models of NBN impact on this blog. Will that be enough to justify an earlier, slower, more expensive to run FTTN over FTTP? I will use household average income as GDP for the household. Say 1.5% of $70,000 => say $1,000 annual benefit. See you next time. comments welcome.

Wednesday, January 6, 2016

FTTN vs FTTP (3): is FTTN now better value than FTTP later?

[Best viewed for iPhone in Reader view.]
NBN Co. kindly responded to my analysis, after a Gizmodo request, saying "speed of install and deployment being of greatest importance":

“Our priority is to provide access to the NBN to all Australians as soon as possible in the most cost-effective way with an upgrade path to meet future demand. A faster rollout of the network leads to earlier activations and revenue opportunity.”

“A full Fibre-to-the-Premises rollout will take significantly longer to complete than the Multi-Technology approach. This means delayed revenue opportunity and an inability to take advantage of a ubiquitous network in the next four years.”

Competing Value Demands in the NBN
There are competing value demands in these statements. Let me untangle them. Value types include:
1. Access/Community: to all Australians.
2. Time: as soon as possible
3. Cost: in the most cost-effective way
4. Flexibility: with an upgrade path to meet future demand.
5. Cost/Time: A faster rollout means earlier activations and revenue
6. Time: FTTP is slower to roll out.
7. Time/Cost: Delay in FTTP means delayed revenue
8. Community/Time: lose advantage of a ubiquitous network in four years.

NBN is complex because of these competing value dimensions. A few I can ignore because there is no data to analyse, and estimates are likely to vary substantially. For instance, what is a ubiquitous network worth? How much does a flexible upgrade path cost, both in time, $$ and complexity of systems? What a Government does is set priorities for which type of value is most important. The instructions to NBN Co from the Government set these priorities.

It is possible for me to analyse the Time vs Cost aspects and comment on the "most cost-effective" statement. Two ways of assessing Time vs Cost are: (1) Peak Cash, how much will the Govt have to pay to build and run the network, and (2) Time value of money, that is $100 paid today is not the same as $100 paid in six years time, if you take interest into account you could earn on the money elsewhere.

My previous posts already tell us about Peak Cash. On the figures previously provided FTTP generates more cash than FTTN after six years, if they are installed at the same time.

NB: Also amended figures in first post for a calc error. See original post for details. Overall outcome $ per HH pa appears about the same. There is however a substantial negative effect on FTTN GDP, since both OPEX and Revenue are reduced, doubling the impact on GDP.

1.Peak Cash
From the last post, you can show how much cash is generated by FTTN, FTTP at Yr 10, 20 and based on how much FTTP delay there is (0,2,4,6,8). You can display this graphically to show in which instances FTTP or FTTN is superior.

Negatives in brackets. Rounding. Figures are in $thousands; (Rev - OPEX - CAPEX) total $$ accumulated up until that year. Depreciation excluded. Shaded colour the best performer for Peak Cash.

Delay = 0yrs2 468
Yr 10(1.4)0(1.4)(0.9)(1.4)(1.8)(1.4)(2.7)(1.4)(3.5)
Yr 20(0.7)4.3(0.7)3.4(0.7)2.5(0.7)1.7(0.7)0.8
Figure 1. Cash created(consumed) by FTTP, FTTN at different FTTP delays. Superior outcome shaded. Outcome for one household eg 4.3 means $4,300 cash generated for NBN Co from NBN services (Revenue less Opex less Capex). A 0 means all CAPEX paid back with Revenue at Yr 10 - the breakeven point.

The table shows, using NBN cash as a proxy for benefit:
- FTTP is superior regardless of delay once reach Yr20.
- FTTN is superior at Year 10 so long as there is four years or more delay to install FTTP
- overall FTTN wins in three scenarios, while FTTP wins in seven.

The same analysis can be done looking at GDP. GDP calculate as Rev + OPEX + CAPEX plus Profit/loss of NBN Co. This reflects total economic activity generated by the NBN. This does not account for multiplier effect (extra benefit through more people using NBN. I have previously estimated that benefit at 1.5% GDP per year. This multiplier is likely to make only a small difference in this case.

Delay = 0yrs2 468

Yr 1012.8 / 10**1812.8 / 10**1512.8 / 10**1212.89 12.8 / 10**6
Yr 2026 / 20**3226 / 20**2926 / 20**2626 / 20**2326 / 20**20
Figure 2. GDP compared by FTTP, FTTN at different FTTP delays. Superior outcome shaded.

Note: ** indicates revised GDP from prior post for FTTN OPEX and Revenue over 1.3, rather than pa. There is a significant impact on GDP, since both Revenue and OPEX are reduced having a double impact.

The table shows, using GDP as a proxy for benefit (undiscounted)(using revised figures):
- FTTP is superior regardless of up to eight year delay, by Yr20
- FTTP is superior at Yr10, so long as delay is no longer than four years.
- FTTN is superior when delay is six or eight years to install FTTP.

2.Time Value of Money
I have updated the model to include a reduction in value of cash the later it is received. So $100 received in ten years is worth less that $100 received today. The difference is the interest rate or discount rate for receiving the money later. I redid the above cash numbers using various discount rates - 0% [same as above], 1%, 3%, 5% and 10%. What rate should be used depends on what the NBN pays to borrow money. Government usually has a lower rate than business. Business could use a 10% rate, while Government might use a 3% rate.

If you discount all the cashflows, a positive cashflow indicates a project worth doing, while a negative would not be. When comparing two projects, you prefer the project with the higher discounted cashflow. I have compared FTTN and FTTP on this basis, and updated the model to include these calculations. This is a bit complex, so there is some chance for error. All errors are mine.

The below tables are results from the model, publically available at: The Time Value of Money calcs were added in Version 2 of the model.

Delay = 0yrs2 468
Figure 3: Discounted cash generated by one FTTN or FTTP household at Yr 10, given delay of installing FTTP. Shaded column indicates better outcome.

From this table you can see, at Yr 10:
- FTTP is superior while delay is less than four years
- FTTN is superior when delay to get FTTN is four years or more.

Delay = 0yrs2 468
Figure 4:  Discounted cash generated by one FTTN or FTTP household at Yr 20, given various delay of installing FTTP. Note: FTTN same for any delay to FTTP.

From this table you can see:
- in Yr 20 FTTP is superior regardless of delay, and regardless of discount rate.

GDP Calcs (adjusted for time value of money) to follow.... in the next post...

Monday, December 14, 2015

FTTN vs FTTP (2): Fast, cheaper, sooner vs Do it once. Do it right.

NB: Update 7, 8 Jan 16
There is a calc error** that I have amended for in the below figures. See more here. Revisions indicated with **.
==Original post
Last week,  said on 7.30 Report: Leigh Sales posed an NBN question
Leigh Sales and Malcolm Turnbull, PM"So why then do you continue to back a broadband network that relies on a decrepit copper network?" ie FTTN

The Australian PM, Malcolm Turnbull in response said "under the approach we are taking to the NBN, we will get the network completed six to eight years sooner than it would be under Labor's proposed method and $30 billion cheaper or at less expense to the Government, which makes broadband more affordable."

In a previous post, I compared FTTP and FTTN assuming the networks were available at the same time. Here I would like to compare where there is a delay in the availability of FTTP at a household.

I created a model to compare the costs and benefits of FTTP and FTTN, and accounted for the delay.
The CAPEX, OPEX and Revenue come from recently leaked NBN Co. estimates to replace ailing Optus HFC cable with either FTTN or FTTP. All these figures are generated for a single household. To get to a national perspective, multiply the outcome by 2.5M households ie 10M total households by 25% FTTN rollout.

I use four comparisons:
  • sales generated over time
  • cash generated / expended over time
  • profit or loss generated by NBN Co over time, and
  • GDP impact ie Sales (customer benefit), OPEX (supplier benefit) and NBN Co profit/loss (shareholder benefit).
The results of the model, show two time frames, after Year 10, and Year 20 for a variety of delays between arrival of FTTP - Yr 0 (at the same time), Yr2 , Yr 4, Yr 6, Yr 8. The last two scenarios being the ones suggested by the PM. The Year 10 and 20 scenarios assume that revenue, and OPEX are consistent over the 20 year timeframe.

FTTP Revenue: $785pa OPEX: $350pa, CAPEX: $4,400
FTTN Revenue: $640pa OPEX: $570pa. CAPEX: $2,100
(NB:** Now revised to FTTN OPEX $440pa Revenue $490pa - amended figures below.)
These figures come from the NBN Co, Optus HFC replacement slides from Delimiter, as discussed in the earlier post.

Output of the Model
All figures in thousands of dollars. Negatives/losses in brackets. Some rounding to whole numbers.
Figures derived from model linked above. Model is in OSX Numbers, and translated in xls.
Revised** numbers only affect FTTN figures. FTTN Revenue / Opex down $150pa, $130pa. But some rounding errors when adding together.

FTTP commences at Yr 8: (Old/Revised**: not amended if diff < 10%;^)
GDP12.8 / 10**6GDP26 / 20**20
Sales6.4 / 5**1.5Sales13 / 10**9
Cash(1.4)^(3.5)Cash(0.7) / (1.0)**0.8
Profit/(Loss)(1.4)^(1)Profit/(Loss)(0.7) / (1.0)**1.7

FTTP commences at Yr 6: (Old/Revised**)
GDP12.8 / 10**9GDP26 / 20**23
Sales6.4 / 5**3Sales13 / 10**11
Cash(1.4)^ (2.7)Cash(0.7) / (1.0)**1.7
Profit/(Loss)(1.4)^(0.5)Profit/(Loss)(0.7) / (1.0)**2.5

FTTP commences at Yr 4: (Old/Revised**)
GDP12.8/ 10**12GDP26 / 20**26
Sales6.4 / 5**5Sales13 / 10**12.5
Cash(1.4)^ (1.8)Cash(0.7) / (1.0)**2.5
Profit/(Loss)(1.4)^1Profit/(Loss)(0.7) / (1.0)**3.4

FTTP commences at Yr 2: (Old/Revised**)
GDP12.8/ 10**15.2GDP26 / 20**29.1
Sales6.4 / 5**6.3Sales13 / 10**14.1
Cash(1.4)^(0.9)Cash(0.7) / (1.0)**3.4
Profit/(Loss)(1.4)^1.7Profit/(Loss)(0.7) / (1.0)**4.3

FTTP commences at Yr 0: (Old/Revised**)
GDP12.8/ 10**18GDP26 / 20**32.2
Sales6.4 / 5**7.8Sales13 / 10**15.7
Cash(1.4)^ 0Cash(0.7) / (1.0)**4.3
Profit/(Loss)(1.4)^2.6Profit/(Loss)(0.7) / (1.0)**5.2

What these figures show, is that:
- FTTP is much more profitable for NBN Co. than FTTN, even when running eight years later. Profit in this case is Revenue less OPEX less Depreciation, where FTTN is depreciated over ten years, and FTTP is depreciated over 25 years. FTTN never makes a profit, nor generates positive cashflow even after 20 years. There is a cash benefit relative to FTTP though. Roughly $2100^ is saved in the case where FTTP starts eight years later than FTTN. But by Yr 20, FTTP has generated $1500 ($1800**) more cash, for the Yr 8 scenario. (Unchanged by Revision**).
- FTTP generates as much GDP as FTTN, so long as FTTP is no more than four (eight**) years later [Revised **]
- the PM is correct. when FTTP is six or eight years later than FTTN, greater customers sales and GDP is generated by FTTN. But a four year delay, makes the outcome by Yr 10 and thereafter almost identical. (Revised**: FTTP generates as much GDP as FTTN, even if eight years late at Yr 20).
- FTTP is generally superior to FTTN whenever the gap between availability is less than four years.
- none of the FTTN scenarios is profitable for NBN Co.

The Yr 10 - 20 figures must be more suspect for FTTN revenue, since the likelihood of revenue stability after the ten year useful life of the FTTN network (especially relative to the FTTP network) must be suspect. The FTTP is upgradeable for the foreseeable future, and likely to Yr 20.

The next step is to compare the $30B statement. Can the PM's statement that $30B is saved by using his MTM (ie FTTN, HFC) rather than FTTP really amount to a $30B saving? That will have to be addressed in the next post.

Sunday, December 6, 2015

FTTP vs FTTN; when is spending $4,400 better value than spending $2,100?

NB: Update: 7 Jan 16. 
The Delimiter comment picked up a calc error** in the below post.
I did allocate the FTTN costs/revenue over 1 year not 1.3 years, which inflates the OPEX pa and the Revenue pa for FTTN. The total diff FTTN vs FTTP being about the same, as the Rev and OPEX changes offset. Makes the OPEX diff $5 per month, and the Revenue $25 per month in favour of FTTP over FTTN. So about the same difference overall. Great to see Delimiter readers paying so much attention to the detail. Revisions identified below with **.
Thanks to david 04/01/2016 at 3:55 pm at the Delimiter comments for the catch.
== Original post

The National Broadband Network (NBN) is Australia's plan to connect all Australians to faster broadband. The current Liberal/National government plans to connect about 25% of premises to cheaper to install ($2,100 per household), faster to roll out FTTN. The previous Labor government aimed to connect 93% of premises to more expensive to install ($4,400 per household) and slower to roll-out FTTP.

It costs $4,400 to install fibre to the premises (FTTP) but only $2,100 to install fibre to the node (FTTN). Recent figures from NBN Co suggest that when the OPEX (Operating Expenses) paid per year to run these services and Revenue generated from these services is taken into account, then FTTP may be better value than FTTN in as short a period as 6 1/2 years. See the Graph below, which shows Cash generated by FTTP exceeds that generated by FTTN after 6 1/2 years. The graph shows total costs for FTTP and FTTN (including CAPEX and OPEX) become equivalent ten years after installation. Coincidentally, the graph shows FTTP breaks even (total revenue exceeds total costs, including CAPEX), at the same time, after ten years. The equivalent breakeven time for FTTN is 30 years, using the numbers below. This graph does not take into account build time, which I will deal with in a second post. **Cash impact of graph unchanged.
Model online to download at DOI:

I did some analysis on the recently published HFC slides (pdf; from Delimiter's 25.11.15 page) which compare FTTP and FTTN Capex, Opex and Revenue. I tweeted them recently (on 2 Dec; first, second, third).

Click on image to enlarge

The HFC slide analysis indicates:  FTTP $20pm (per household) less OPEX; $10pm more REV (per household) than FTTN. With a $30 per month financial benefit, a $2,300 CAPEX diff pays for itself in 76mths (6 1/2 years). The  $2,300 CAPEX difference is FTTP $4,400 CAPEX/hh, less FTTN $2100 CAPEX/hh (per household). (NB: Calc error Revision**: FTTP $90pa per houshold less OPEX, $300pa per household more Revenue than FTTN).

The analysis is based on: 350k households in HFC slide (see image)
FTTP CAPEX $4400/hh | OPEX $125M pa | REV $275M pa
FTTN CAPEX $2100/hh | OPEX $200M pa | REV $225M pa (NB:** This should be over 1.3yrs.)

If you work out these total costs and revenues per household, that equates to:
FTTP CAPEX $4400 | OPEX $350 pa / hh | REV $785 pa / hh
FTTN CAPEX $2100 | OPEX $570 pa / hh | REV $640 pa / hh  (NB:** Opex $440 pa/hh Rev $490 pa/hh)

This data suggests FTTP is better value than FTTN when you take into account OPEX and Revenue after 6 1/2 years. FTTP is not only cheaper to run per annum (OPEX $220 less for FTTP; $570 vs $350 i.e. close to $20 per month), but generates more average revenue ($785 vs $640, close to $10 per month). This difference totals about $30 per month FTTP > FTTN greater margin per household for NBN Co. This is a total net benefit of $360 per household per year for using FTTP over using FTTN. The CAPEX difference of $2300 ($4400 -  $2100) would be earned back in 76 months i.e. $2300 / $30. Over 25% of say 10M households, that works out in total to be a benefit of $900M per year ($360pa * 10M * 25%). So over the say ten year life of FTTN, the benefit is approaching $9B total. The longer FTTN remains in place, the greater the foregone benefit for not switching to FTTP. (NB:** OPEX $90 less for FTTP; Revenue about $300 more for FTTP; diff about $30 per mth).

This annual and total benefit does not take into account extra CAPEX expense to replace the FTTN with FTTP. This would be an extra difference between FTTN and FTTP.

Thus, assuming these figures are correct FTTP would pay for itself, compared to FTTN within 6.5 years, according to NBN Co's numbers on the slide linked above, based on differences in Opex and Revenue, regardless of nearly 100% greater CAPEX on FTTP.

But this is the first time the HFC slide has brought all those numbers easily together. Such information could have been presented in the cost benefit analysis but these numbers tell such an interesting story, that surely I would have remembered that.

Summary: It makes sense to spend $4,400 CAPEX on FTTP (rather than $2,100 on FTTN CAPEX) when the OPEX and Revenue make the margins much more favourable for FTTP than for FTTN. The OPEX and Revenue figures from the HFC slide indicate about $30 per month margin benefit (OPEX plus revenue difference) for FTTP over FTTN or $385 per annum. This margin benefit makes FTTP more attractive than FTTN after 6 1/2 years of installation.

old/Revised **FTTNFTTP
CAPEX per HH$2,100$4,400
OPEX TOTAL pa$200M$125M
OPEX per HH pa$570/$440**$350
Rev TOTAL pa$225M$275M
Rev per HH pa$640/$490**$785
Margin per HH pa$70/$50**$435