2e:null 2f:["$","title",null,{"children":"NBN Financials - from 2021 to Infinity and beyond... | Richard Ferrers | Observable"}] 34:T475,viewof basecase = { const pl = DOM.element('div'); var layout = { showlegend: true, title: "NBN Financials 2021 - on", barmode: 'stack', xaxis: { title: 'Year', dtick: 1 }, // x-axis yaxis: { range: [0, Math.max(cash)], title: '$B', dtick: Math.max(cash)/8 } // y-axis } var trace1 = { x: newYears, y: newRevenue, name: 'Revenue', type: 'line' }; var trace2 = { x: years, y: subPays, name: 'Subscriber Payments', type: 'bar', marker: { color: 'forestgreen' } }; var trace3 = { x: newYears, y: newOpexVar, name: 'Variable - OPEX', type: 'bar', marker: { color: 'darkorange' } }; var trace4 = { x: newYears, y: newOpexFix, name: 'Fixed - OPEX', type: 'bar', marker: { color: 'steelblue' } }; var trace5 = { x: newYears, y: cash, name: 'Cash', type: 'line', marker: { color: 'blueviolet' } }; plotly.newPlot(pl, [ trace1, trace2, trace5, trace4, trace3], layout) return pl }35:[] 32:{"id":"54d589cde6ba6970","slug":"nbn-financials-from-2021-to-infinity-and-beyond","title":"NBN Financials - from 2021 to Infinity and beyond...","thumbnail":"be65219926ce779efdefda8e3ca6684381be279801c03ac0e7262348737134b7","access_level":"public","create_time":"$D2018-03-12T02:24:22.158Z","update_time":"$D2018-03-22T01:07:17.438Z","view_time":null,"first_public_time":"$D2018-03-13T01:25:44.664Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"0018576fb2fd42a8","login":"areff2000","name":"Richard Ferrers","avatar_url":"https://avatars-next.observableusercontent.com/bd016a1821a066a393fccf09562a2562008bedb29f8c0cd19a3b83fa5bf1a3dc","tier":"starter_2024"},"version":346,"publish_version":346,"role":null,"authored":false,"authors":[{"type":"individual","id":"aeee6da9848a4b1c","login":"areff2000","name":"Richard Ferrers","avatar_url":"https://avatars-next.observableusercontent.com/bd016a1821a066a393fccf09562a2562008bedb29f8c0cd19a3b83fa5bf1a3dc","tier":null}],"members":[{"type":"individual","id":"aeee6da9848a4b1c","login":"areff2000","name":"Richard Ferrers","avatar_url":"https://avatars-next.observableusercontent.com/bd016a1821a066a393fccf09562a2562008bedb29f8c0cd19a3b83fa5bf1a3dc","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":null,"type":"notebook","body":{"title":"NBN Financials - from 2021 to Infinity and beyond...","cells":[{"id":0,"value":"md`# NBN Financials - from 2021 to Infinity and beyond...`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":313,"value":"md`This is Version 1. 13.3.18 \nSee [Version 2](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-on) which includes upgrade to gigabit services, and FTTN end of life `","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":261,"value":"md`This model is a Work in Progress; for submission to the [Enquiry](https://www.aph.gov.au/Parliamentary_Business/Committees/Joint/National_Broadband_Network) 28.3.18
\nIn response to an Enquiry by Joint Standing Committee on the NBN.
\n**Submissions Close**: 29 March 2018. See [Enquiry Home.](https://www.aph.gov.au/Parliamentary_Business/Committees/Joint/National_Broadband_Network).
\nThis analysis is licensed: [CC-BY](https://en.wikipedia.org/wiki/Creative_Commons_license).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":285,"value":"md`### NB: DISCLAIMER: Model currently ignores##\n- repayment of debt, dividends or tax payments. These are all cash withdrawls from NBN, and will be added after the addition of revenue impacting variables.\n- difference in revenue by technology type. To add with new sliders.\n- level of NBN Customer Satisfaction.\n- length of useful life of technology type eg FTTN, Wireless, FTTC.\n- discounting cash, see VERSION 2.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":292,"value":"md`**Aim:** This page projects forward NBN Financials after the Build Phase of the NBN, with three user selectable options: \n- users switching away from NBN to mobile only\n- takeup of gigabit services on FTTC, HFC and FTTP,\n- consequences of FTTN reaching end of life, and a % of users switching off.\n

The main outputs of the model, are:\n- NBN yearly sales and (EBITDA)profit ($Billion), and\n- end of year, cash on hand ($Billion).\n- value of NBN (discounted future cashflows for period of analysis). See note on NPV discounting in Disclaimer.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":11,"value":"import {viewof pic1 as nbngraph} from \"@areff2000/nbn-business-case-financials\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"viewof nbngraph","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":179,"value":"md`Figure 1. **NBN Financials during the NBN Build.** Debt $20B. Cashflow positive in 2021.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":27,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":182,"value":"md`Figure 2. **BaseCase NBN Post Build** - no change in Revenue or Operating Expenses over time.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":337,"value":"html`\n\n\n\n

p.a.	Revenue	Expenses	PROFIT(EBITDA)	VALUE NBN YR${moreYears}	Pay Debt back (Yrs)
$B	${newRevenue[0]}	${Math.round((newOpexFix[0] + newOpexVar[0])*100)/100}	${ebitda}	$B ${Math.round(cash[moreYears])}	${myRound(-cashBalanceOpening / ebitda)}

`\n// if payback > lifeFTTN, check cash until goes positive - need function","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":304,"value":"md`### First Steps beyond the Base Case\nThese sliders shift the Base Case in Figure 2.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":57,"value":"viewof moreYears = slider({\n min: 0, \n max: 40, \n step: 1, \n value: 20, \n title: \"Count of post 2020 years for NBN analysis:\", \n description: \"Integers from zero through 40\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":187,"value":"viewof moreMobile = slider({\n min: 0, \n max: 40, \n step: 1, \n value: 26, \n title: \"Non-NBN Users% (post 2020 ie mobile only):\", \n description: \"Integers from zero through 40% - est'd. 26% at 2021\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":301,"value":"md`### Moving beyond the Base Case; yet to implement\nA number of variables will be added to the Base Case, including: \n- years of analysis (post 2020) (DONE V1)\n- level of non-NBN users (ie mobile only) (DONE V1)\n- power users upgrade to latest Tech (DONE V2)\n- customer satisfaction (in progress V3)\n- length of life of various NBN technologies (FTTN - DONE V2, FTTC, HFC, FTTP)\n- when a user of a particular technology will stop using NBN, due to lack of satisfaction.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":273,"value":"viewof csat = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 50, \n title: \"NBN Customer Satisfaction:\", \n description: \"Integers from zero through 100. Low scores indicate customer/s leaving NBN. - yet to implement\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":279,"value":"html`\n

Breakdown on Users by NBN Technology; at end of build

\nSource: NBN Corporate Plan 2018-21, p.36, 39. UBB - analyst assessment.\n\n\n\n\n\n\n

	FTTN	FTTC	HFC	FTTP	Wireless	Satellite	Total
Passed (M)	4.6	1.0	3.1	2.0	0.6	0.4	11.7
Activated (M)	3.4	0.8	2.3	1.5	0.4	0.2	8.6
% Active	${Math.round(3.4*100/4.6)}%	80%	${Math.round(2.3*100/3.1)}%	75%	66%	50%	${Math.round(8.6*100/11.7)}%
% Est'd Life (years)	10	20	20	40	15	10	n/a
Ultra BB capable(gbps)	Some	Yes	Yes	Yes	No	No	n/a

\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":190,"value":"md`Total Build: 11.7M premises (p.9 NBN Corporate Plan 2018-21). Activations: 8.6M 2021 (p.32 ibid.)(ie 76%).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":97,"value":"md`Notes on Figure 2.
\nNBN pays back its $20B debt by 2030. But still has to recoup $9B acc losses (since 2016), plus pay tax (30%).\n\nNext to add; amendments to BaseCase eg declining revenue, users switch to mobile only (DONE), choice of use for cash eg upgrade FTTN, pay tax/interest/debt(DONE), pay dividends. Adjust OPEX - var for change in revenue(DONE).\n### BaseCase Data - amended by slider choices.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":318,"value":"currNet = myRound(newRevenue[0] - (newOpexFix[0] + newOpexVar[0]))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":99,"value":"newRevenue = {\nvar values = [];\n var adjMob = 11.6/8.6 * (100 - moreMobile)/100;\n var final = revenue[revenue.length-1];\n \n for (let i = 0; i <= moreYears; ++i) \n { values.push(Math.round(final * adjMob*100)/100);\n }\n \n return (values); \n// pickup add other additions.\n // $5.3B per Corp Plan 2018, adjusred for MOB %\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":198,"value":"mobRevShiftPerCent = Math.round(11.6/8.6 * 100 * (100 - moreMobile)/100)-100\n// percent change in revenue due to change in Mobile (non-NBN) use","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":241,"value":"md`(above)Non-NBN Users; PER CENT Revenue change from project NBN activated by 2021; est'd 8.6M of 11.7M passed.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":222,"value":"cashBalanceOpening = -20.3 // $B Cash on Hand 2017 Annual Report $1B, plus cashflow 2018-20.","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":239,"value":"md`CASH balance at end of NBN Build ($B)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"newYears = {\nvar values = [];\n var final = years[years.length-1];\n \n for (let i = 0; i <= moreYears; ++i) \n { values.push(final+i);\n }\n \n return (values); \n// pickup add other additions.\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":113,"value":"cash = {\n var total = [];\n var openingBalance = -20;\n total[0] = Math.round((newRevenue[0] - newOpexFix[0] - newOpexVar[0] + cashBalanceOpening)*100)/100; \n \n for (let i = 1; i <= moreYears; ++i) {\n total[i] = Math.round((total[i-1] + newRevenue[i] - newOpexFix[i] - newOpexVar[i])*100)/100;\n }\n return (total);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":254,"value":"md`CASH adjusted for opening balance, REVENUE, OPEX variable adjusted by mobile non-NBN users.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":132,"value":"newOpexFix = {\nvar values = [];\n var final = opexFix[opexFix.length-1];\n \n for (let i = 0; i <= moreYears; ++i) \n { values.push(final);\n }\n \n return (values); \n// pickup add other additions.\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":137,"value":"newOpexVar = {\nvar values = [];\n var final = opexVar[opexVar.length-1];\n \n for (let i = 0; i <= moreYears; ++i) \n { values.push(Math.round(final * (1+ mobRevShiftPerCent/100)*100)/100);\n }\n \n return (values); \n// pickup add other additions.\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":248,"value":"md`VariableOPEX adjusted by REVENUE change.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":89,"value":"html`

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"md`### Data:`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":339,"value":"ebitda = myRound(newRevenue[0] - (newOpexFix[0] + newOpexVar[0]))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":33,"value":"years = { return [ 2021]; }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":35,"value":"revenue = { return [ 5.37]; }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":47,"value":"opexFix = { return [ 2.0]; }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":49,"value":"opexVar = { return [ 0.9]; }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":55,"value":"subPays = { return [ 0.3]; }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":95,"value":"html`

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":40,"value":"md`### Setup:`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":42,"value":"plotly = require('https://cdn.plot.ly/plotly-latest.min.js')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":44,"value":"d3 = require(\"https://d3js.org/d3.v5.min.js\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":77,"value":"import {slider} from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":141,"value":"function myRound(name) {\n return Math.round(name*100)/100;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W35","stdlib":"1","titling":"auto"}} 33:"$32:body"

${newRevenue[0]}

${Math.round((newOpexFix[0] + newOpexVar[0])*100)/100}

${ebitda}

$B ${Math.round(cash[moreYears])}

${myRound(-cashBalanceOpening / ebitda)}

Breakdown on Users by NBN Technology; at end of build