2d:null 2e:["$","title",null,{"children":"NBN Financials - from 2021 (V4) on... | Richard Ferrers | Observable"}] 33:T612,viewof basecase = { let width = 400 let height = 400 //const pl = DOM.context2d(width, height); 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)/5 } // 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' } }; var trace6 = { x: newYears, y: debtRepayments, name: 'Debt - REPAID', type: 'bar', marker: { color: 'red' } }; var trace7 = { x: newYears, y: capexNew, name: 'FTTN upgrade reserve', type: 'line', marker: { color: 'green' } }; plotly.newPlot(pl, [ trace1, trace5, trace7, trace6, trace2, trace4, trace3], layout) return pl }34:T42c,html`

1.4 Impact of Customer Satisfaction on NBN.

On a five point scale, how happy are you with the NBN?
Very Unhappy | Unhappy | Neutral | Happy | Very Happy.
Relates to delay to install, service, reliability, help fixing problems, price etc.
Impact:

Lower satisfaction increases switching to mobile only, less revenue, minimising use.
Higher satisfaction increases revenue, increases power usage, decreases switching to mobile only.

	Very😡	☹️:-(	😏:-\|	😀:-)	Very😍	Status
% mobile	+20%	+10%	-	-	-10%	Yet to implement
% power Users (M)	-	-	-	+10%	+20%	Yet to implement
Revenue (M)	-50%	-20%	-10%	-	+10%	Yet to implement

Possible: levels of satisfaction: 😀😍😫☹️😡😇😏 | Possible revision: add CSat by Tech. `35:T6a9,newRevenue = { var values = []; var adjMob = 11.7/8.6 * (100 - moreMobile)/100; // csat impact on PowerUsers var csatPow = 1; // default no impact on Power Users if (csat > 60) { csatPow = 1.1; } if (csat > 80) { csatPow = 1.2; } // extra 20% Power users when csat high var powerREV = powerUsers/100 * csatPow * 50 * 12 * 4.3 / 1000; // powerUsers is a % of FTTC/FTTP/HFC who adopt Ultra-broadband // could add FTTN as an option ie 3.4M activated users, but some % could not access ie 25-50% // FTTC + FTTP + HFC = 4.3M users // $50 pm revenue generated per power user var final = revenue[revenue.length-1]; // csat impact on adjMob var csatMob = 1; // default no impact on Mobile switchers if (csat > 80) { csatMob = 1.1; } if (csat < 40) { csatMob = 0.9; } if (csat < 20) { csatMob = 0.8; } // csat impact on overall revenue var csatRev = 1; // default no impact on Mobile switchers if (csat > 80) { csatRev = 1.1; } if (csat < 60) { csatRev = 0.9; } if (csat < 40) { csatRev = 0.8; } if (csat < 20) { csatRev = 0.5; } var users = 8.6; // NBN users activated var lifeFTTN = 10; // years var usersFTTN = 3.4; // FTTN users activated var ARPUpa = revenue / users; // ARPU - assumes revenue same per technology var revFTTN = usersFTTN * ARPUpa * EOLeavers / 100; var FTTNadj = 0; for (let i = 0; i <= moreYears; ++i) { if (i > lifeFTTN) { FTTNadj = revFTTN; } // adjust revenue of FTTN for after FTTN end of life values.push(myRound((((final - FTTNadj) * csatRev) + powerREV ) * adjMob * csatMob)); } return (values); // pickup add other additions. // $5.3B per Corp Plan 2018, adjusred for MOB % }36:T543,newOpexVar = { var values = []; var final = opexVar[opexVar.length-1]; // csat impact on PowerUsers var csatPowOpex = 1; // default no impact on Power Users if (csat > 60) { csatPowOpex = 1.1; } if (csat > 80) { csatPowOpex = 1.2; } // extra 20% Power users when csat high var powerOPEX = powerUsers/100 * csatPowOpex * 20 * 12 * 4.3 / 1000; // FTTC + FTTP + HFC = 4.3 // $20 pm costs power use var users = 8.6; var lifeFTTN = 10; var usersFTTN = 3.4; var ARPUpa = opexVar / users; var opexVarFTTN = usersFTTN * ARPUpa * EOLeavers / 100; var FTTNadj = 0; // csat impact on Mobile Users var csatMobOpexAdj = 1; // default no impact on Mobile switchers if (csat > 80) { csatMobOpexAdj = 1.1; } if (csat < 40) { csatMobOpexAdj = 0.9; } if (csat < 20) { csatMobOpexAdj = 0.8; } // csat impact on overall revenue var csatOpex = 1; // default no impact on Opex if (csat > 80) { csatOpex = 1.1; } if (csat < 60) { csatOpex = 0.9; } if (csat < 40) { csatOpex = 0.8; } if (csat < 20) { csatOpex = 0.5; } for (let i = 0; i <= moreYears; ++i) { if (i > lifeFTTN) { FTTNadj = opexVarFTTN; } values.push(myRound((((final - FTTNadj) * csatOpex) + powerOPEX) * (1 + (mobRevShiftPerCent * csatMobOpexAdj)/100))); } return (values); // pickup add other additions. }37:[] 31:{"id":"8e7e38598624d8ec","slug":"nbn-financials-from-2021-v4-on","title":"NBN Financials - from 2021 (V4) on...","thumbnail":"46196013641f842d878578edd97c4bb9a93117ac1815eb2178be386a0dcae6cf","access_level":"public","create_time":"$D2018-03-19T23:59:33.328Z","update_time":"$D2018-03-24T05:45:43.377Z","view_time":null,"first_public_time":"$D2018-03-22T00:50:25.927Z","has_unpublished_edits":null,"fork":{"id":"6ab32c8c3cea72d2","slug":"nbn-financials-from-2021-test-v3-on","title":"NBN Financials - from 2021 (V3) on...","owner":{"id":"0018576fb2fd42a8","login":"areff2000","name":"Richard Ferrers"}},"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":875,"publish_version":875,"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 (V4) on...","cells":[{"id":0,"value":"md`# NBN Financials - from 2021 (V4) on...`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":281,"value":"html`

Version 4. 22.3.18, including CSAT (DONE), repay debt early (DONE), upgrade FTTN ($ allocated but not yet spent)...`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":808,"value":"md`#### Wishlist:\n- save variable settings, and export graph to print/pdf`","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":325,"value":"md`Link to [Base Case v1](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-to-infinity-and-beyond) | [Version 2](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-on) | [Version 3](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-test-v3-on) | [Version 5](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-v5-on).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":373,"value":"md`### NB: DISCLAIMER: Model currently ignores##\n- repayment of dividends or tax payments. These are all cash withdrawls from NBN, and will be added after the addition of revenue impacting variables. Debt repayments now added in v4.\n- difference in revenue by technology type. To add with new sliders (not yet implemented).\n- upgrading FTTN to FTTC or FTTP (Funds allocated in v4, spend funds v5).\n- adjusting end of life for FTTN with a slider (not yet implemented).\n- inflation, discounting future cashflows. All figures are effectively cash in current years dollars. So the value of NBN is merely the sum of cash generated over the analysis term ie closing cash balance. NB; does not imclude tax or interest payable as noted above [Discounted](https://en.wikipedia.org/wiki/Discounted_cash_flow) NPV now added with variable interest rate but since no inflation or interest earned on cash balances, [DCF](https://en.wikipedia.org/wiki/Discounted_cash_flow) is effectively double discounted).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2,"value":"md`**Aim:** This page projects forward NBN Financials after the Build Phase of the NBN, with six user selectable options: \n- users switching away from NBN to mobile only\n- takeup of gigabit services on FTTC, HFC and FTTP,\n- paying for FTTN upgrade (but not yet upgrading) by using a portion of cash generated each year\n- paying off NBN debt by using a portion of cash generated each year\n- consequences of FTTN reaching end of life, and a % of users switching off, and\n- impact of customer satisfaction; when good as returning customers, more gigabit use, and when poor, falling revenue and more leaving customers.\n

The main outputs of the model, are:\n- NBN yearly sales and (EBITDA)profit ($Billion), and\n- end of analysis and by year, cash on hand ($Billion).\n- value of NBN (and discounted future cashflows for period of analysis). See note on NPV discounting in Disclaimer.\n- total GDP impact of NBN ie Revenue plus expenses plus profit.`","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":"md`For graph of NBN Financials 2016-21, click above link.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":736,"value":"md`## 0.1 Setup.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":738,"value":"viewof moreYears = slider({\n min: 0, \n max: 40, \n step: 1, \n value: 20, \n title: \"Number 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":744,"value":"viewof DCFrate = slider({\n min: 0, \n max: 20, \n step: 1, \n value: 5, \n title: \"NBN CASHFLOW discount rate:\", \n description: \"Integers from zero through 20. Used in DCF calc.\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":766,"value":"viewof debtRepayRate = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 50, \n title: \"NBN Debt repayment rate: (% of annual cash surplus)\", \n description: \"Integers from zero through 100.\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":844,"value":"viewof CAPEXupgradeType = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 50, \n title: \"NBN/FTTN Upgrade: more FTTC or more FTTP\", \n description: \"Integers from zero (all FTTC) through 100 (all FTTP) - not yet implemented for FTTN\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":27,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":532,"value":"html`\n\n\n\n

p.a.	Revenue	Expenses	PROFIT(EBITDA)	VALUE NBN YR${moreYears}	Pay Debt back (Min.Yrs)
$B	${newRevenue[0]}	${myRound((newOpexFix[0] + newOpexVar[0]))}	${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":486,"value":"\nhtml`\n\n\n\n

p.a.	Revenue	Expenses	PROFIT(EBITDA)	DCF CASH	Financials Post FTTN (EOL)
$B	${newRevenue[lifeFTTN+1]}	${myRound((newOpexFix[lifeFTTN+1] + newOpexVar[lifeFTTN+1]))}	${ebitdaFTTNeol}	${myRound(npvCalcEBITDA,2)}	NBN GDP impact=$B ${Math.round(myRound(gdp))} FTTN Upgrade Reserve: $B ${Math.round(capexNew[moreYears])}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":434,"value":"html`

1.Active Analysis tools

\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.5, \n title: \"Non-NBN Users% (post 2020 ie mobile only):\", \n description: \"Integers from zero through 40% - est'd. 26.5% at 2021\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":687,"value":"viewof csat = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 60, \n title: \"NBN Customer Satisfaction:\", \n description: \"Integers from zero through 100. Low scores indicate customer/s leaving (x<40) or spending less (x<60) NBN. See more at Section 1.4. \"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":376,"value":"viewof powerUsers = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 0, \n title: \"NBN % adopters of Ultra-broadband (>1Gbps etc.):\", \n description: \"Integers from zero through 100. High scores indicate higher revenue for NBN. No impact on CAPEX or OPEX-fix.\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":458,"value":"viewof EOLeavers = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 100, \n title: \"NBN Users who abandon FTTN at Technology End of Life (10yrs):\", \n description: \"Integers from zero through 100. Higher scores indicate customer/s leaving NBN. - implemented for FTTN\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":811,"value":"viewof CAPEXupgrade = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 100, \n title: \"NBN/FTTN Upgrade: % free cash (after paying debt) added to FTTN upgrade reserve\", \n description: \"Integers from zero through 100 - implemented for FTTN\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":841,"value":"md`Note to Figure 1. **NBN Post Build Financials** - adjusted for non_NBN usage, power_NBN users(gbps), FTTN abandoners at End of Life (EOL). PROFIT(EBITDA) is gap between Revenue line and Operating Expense (OPEX) bar.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":390,"value":"md`NB:\n1.POWER USERS assumed to pay $100/mth (ie double ARPU) for premium speed service AVAILABLE ON FTTC, HFC, FTTP. EXTRA OPEX-var $20pm.\n\n2.FTTN abandoning is expected to only impact variable OPEX. Fixed OPEX remains unaltered. About $0.8B per annum would be saved, and $3B added to NBN value at Yr 20, if a share of Fixed OPEX, proportional to the number of FTTN users was avoided. \n\n3.Customer Satisfaction affects three things (see Section 1.4 below), level of mobile switchers (better satisfaction, fewer switchers), level of POWER USERS (better satisfaction, more power users) and level of revenue (better satisfaction, more revenue and vice versa). `","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":432,"value":"html`

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":380,"value":"md`### 1.1 Moving beyond V2, V3; yet to implement tools in V4\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 v2)\n- power users upgrade to latest Tech (DONE v2)\n- customer satisfaction (DONE v4); normal dist'n around mean (not yet implemented); though would expect it to vary by tech (FTTN vs Satellite; not implemented); could add random propensity to leave/spend more based on level of dis/satisfaction (not yet implemented)\n- length of life of various NBN technologies (FTTN - DONE, FTTC, HFC, FTTP)\n- upgrade of FTTN to FTTC/P (funds allocated v4, funds spent v5)\n- when a user of a particular technology will stop using NBN, due to lack of satisfaction (v2 DONE for FTTN).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":724,"value":"md`### 1.2 Yet to implement tools.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":323,"value":"viewof newCapex = slider({\n min: 0, \n max: 100, \n step: 1, \n value: 50, \n title: \"NBN FTTN CAPEX Upgrade:\", \n description: \"Integers from zero through 100. Low scores indicate earlier upgrade. - yet to implement\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":285,"value":"html`\n

1.3 Breakdown on Users by NBN Technology; at end of build

\nSource: NBN Corporate Plan 2018-21, p.36, 39\n\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	No	Yes	Yes	Yes	No	No	n/a

\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":494,"value":"lifeFTTN = 10","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":395,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":190,"value":"md`

*Other Notes*:
\nTotal 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%), less accumulated losses (.\n\nNext to add; amendments to BaseCase eg declining revenue(DONE for FTTN), users switch to mobile only (DONE), choice of use for cash eg upgrade FTTN (yet to implement), pay tax/interest/debt(DONE), pay dividends. Adjust OPEX - var for change in revenue(DONE).\n### 2.BaseCase Data - amended by slider choices.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":363,"value":"ebitda = myRound(newRevenue[0] - (newOpexFix[0] + newOpexVar[0]))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":505,"value":"ebitdaFTTNeol = myRound(newRevenue[lifeFTTN+1] - (newOpexFix[lifeFTTN+1] + newOpexVar[lifeFTTN+1]))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":448,"value":"md`EBITDA(cash profit) $B`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":99,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":198,"value":"mobRevShiftPerCent = Math.round(11.7/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] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - subPays[0] + cashBalanceOpening)); \n \n \n for (let i = 1; i <= moreYears; ++i) {\n total[i] = myRound((total[i-1] + newRevenue[i] - newOpexFix[i] - newOpexVar[i] ));\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 var users = 8.6;\n var lifeFTTN = 10;\n var usersFTTN = 3.4; \n var ARPUpa = opexFix / users;\n var opexFixFTTN = usersFTTN * ARPUpa * EOLeavers / 100; \n var FTTNadj = 0;\n \n for (let i = 0; i <= moreYears; ++i) \n { \n // if (i > lifeFTTN) { FTTNadj = opexFixFTTN; } // unimplemented no OPEXfix adj for EOL FTTN\n values.push(myRound(final - FTTNadj));\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":475,"value":"md`FixedOPEX adjusted by EOLife change.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":137,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":819,"value":"md`VariableOPEX adjusted by REVENUE, EOLife change, Customer Satisfaction. EXTRA for PowerUSERS`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":771,"value":"debtRepayments = {\n \n var totalPaid = 0; \n var debtRepaid = [];\n \n var outstandingDebt = cashBalanceOpening;\n debtRepaid[0] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - subPays[0]) * debtRepayRate / 100); \n totalPaid += debtRepaid[0];\n \n for (let i = 1; i <= moreYears; ++i) {\n//TODO chase up why last payment not rounded to 2dp. \n \n if (totalPaid < -cashBalanceOpening) { // only pay back to level of debt; NB: cashBal assumed negative\n debtRepaid[i] = myRound((newRevenue[i] - newOpexFix[i] - newOpexVar[i]) * debtRepayRate / 100);\n totalPaid += debtRepaid[i]; // repay debt\n \n if (totalPaid > -cashBalanceOpening) {\n debtRepaid[i] -= myRound(totalPaid + cashBalanceOpening); // refunded excess payment\n // since cashBalanceOpening is negative\n // last payment of diff between outstanding and \n } // adjust if final payment\n \n } // end calcRepayment\n \n else { debtRepaid[i] = 0; }\n } // end loop\n return (debtRepaid);\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":248,"value":"md`Debt Repayments; based on % of cash surplus selected.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":817,"value":"capexNew = {\n \n var totalCAPEX = []; \n var capex = [];\n \n var outstandingDebt = cashBalanceOpening;\n capex[0] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - subPays[0] - debtRepayments[0]) * CAPEXupgrade / 100); \n \n totalCAPEX[0] = capex[0];\n \n for (let i = 1; i <= moreYears; ++i) {\n // only pay back to level of debt; NB: cashBal assumed negative\n capex[i] = myRound((newRevenue[i] - newOpexFix[i] - newOpexVar[i] - debtRepayments[i]) * CAPEXupgrade / 100);\n totalCAPEX[i] = capex[i] + totalCAPEX[i-1]; // repay debt\n \n } // end loop\n return (totalCAPEX);\n \n}","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`### 3.BASE CASE Data:`","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`### 4.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"},{"id":555,"value":"function npv(myArray, rate)\n{\n // where rate = 10 => 10%\n \n var total = 0;\n var calc = []\n \n for (let i = 0; i < myArray.length; i++) {\n calc[i] = myArray[i] / Math.pow((1 + rate/100), i);\n // console.log(myRound(calc));\n total += calc[i];\n \n }\n \n return(total)\n\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":646,"value":"function calcGDP(myArray, rate)\n{\n // where rate = 10 => 10%\n \n var total = 0;\n var calc = []\n \n for (let i = 0; i < myArray.length; i++) {\n calc[i] = myArray[i] / Math.pow((1 + rate/100), i);\n \n // GDP is undiscounted\n \n total += calc[i];\n \n }\n \n return(total)\n\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":303,"value":"8.6/11.7","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":563,"value":"npvCalcEBITDA = {\n ///var test = [10, 10, 10];\nreturn (- npv(newOpexVar, DCFrate) - npv(newOpexFix, DCFrate) + npv(newRevenue, DCFrate) + cashBalanceOpening);\n \n // discounted to 2021\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":648,"value":"gdp = {\n ///var test = [10, 10, 10];\n var gdpEBITDA = - npv(newOpexVar, 0) - npv(newOpexFix, 0) + npv(newRevenue, 0);\n // test: var gdpEBITDA = 0;\n \nreturn ( npv(newOpexVar, 0) + npv(newOpexFix, 0) + npv(newRevenue, 0) + gdpEBITDA );\n // GDP = revenue + expenses + profit ie for customers(sales), for vendors(expenses) and for NBNCo (profit) \n \t\t// EBITDA; included as Revenue - OpexFix - OpexVar\n // + cashBalanceOpening); - no cashBalance included in GDP calc\n \n // gdp impact discounted to 2021 ie end of build at 0% discount rate\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W37","stdlib":"1","titling":"auto"}} 32:"$31:body"

1.4 Impact of Customer Satisfaction on NBN.

${newRevenue[0]}

${myRound((newOpexFix[0] + newOpexVar[0]))}

${ebitda}

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

${myRound(-cashBalanceOpening / ebitda)}

${newRevenue[lifeFTTN+1]}

${myRound((newOpexFix[lifeFTTN+1] + newOpexVar[lifeFTTN+1]))}

${ebitdaFTTNeol}

1.Active Analysis tools

1.3 Breakdown on Users by NBN Technology; at end of build