2d:null 2e:["$","title",null,{"children":"NBN Financials - from 2021 on... | Richard Ferrers | Observable"}] 33:T61f,md`Version 5a. 22.3.18. For explanations, click Version 5 below.
NB: Minor calc error noted, when profit (EBITDA) negative, fixed in [V5b](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-on-v5b) (21.4.18).
Rough cut V5c with interest on debt (11.5.18)(Noted: $5B interest in Base Case @4%), and minor fix to negative debt repayments.

This is the model **WITHOUT EXPLANATIONS**. For 5pp explanation/submission to JOINT NBN Committee, see archive copy at [figshare](https://doi.org/10.6084/m9.figshare.6030926).
Any comments, tweet me [@valuemgmt](http://twitter.com/valuemgmt) | [model tweet](https://twitter.com/valuemgmt/status/979110826643660800?s=21)
NB: Best viewed on a monitor, where you can see sliders, and graph at the same time (rather than laptop). RF 19.4.18

Link to [Base Case v1](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-to-infinity-and-beyond) | [Version 5](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-v5-on) with explanations | Final Version (is this version) | Figshare DOI. | This analysis is licensed: [CC-BY](https://en.wikipedia.org/wiki/Creative_Commons_license).
Changelog: AMENDED EOL FTTN so calcs pick up global variable, show post-FTTN NBN value, debt repayment offsetting op.bal., pro rata upgrade to FTTN to 100% (check calc; add divide by zero catch) 23,27.3.18.`34:T832,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 inc FTTN 10yr life', type: 'line' }; var trace11 = { x: newYears, y: newRevenuePostFTTN, name: 'Revenue post FTTN upgrade', type: 'line' }; var trace2 = { x: years, y: subPays, name: 'Subscriber Payments', type: 'bar', marker: { color: 'yellow' } }; 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 Pre FTTN upgrade', type: 'line', marker: { color: 'blueviolet' } }; var trace15 = { x: newYears, y: cashPostFTTN, name: 'Cash Post FTTN upgrade', type: 'line', marker: { color: 'gold' } }; 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' } // now removed since replaced with actual upgrade }; var trace8 = { x: newYears, y: upgradeCAPEX, name: 'FTTN upgrade', type: 'bar', marker: { color: 'green' } }; plotly.newPlot(pl, [ trace1, trace11, trace5, trace15, trace6, trace8, trace2, trace4, trace3], layout) return pl }35:T42b,html` Sections 1.0-1.3 omitted.

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%	DONE v4
% power Users (M)	-	-	-	+10%	+20%	DONE v4
Revenue (M)	-50%	-20%	-10%	-	+10%	DONE v4

Possible: levels of satisfaction: 😀😍😫☹️😡😇😏 | Possible revision: add CSat by Tech. `36:T73e,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; // var powerREV = powerUsers/100 * csatPow * 50 * 12 * (4.3 + FTTNupgraders[yr]) / 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 + FTTNupgraders = 4.3M users (now plus upgraded users from FTTN) // $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 % }37:T546,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. }38:T7f4,upgradeFTTCUsers = { // // count of FTTC upgraded from FTTN var newFTTCusers = []; var newFTTPusers = []; var maxFTTN = 4.6 * (100 - moreMobile) / 100; // could add totalFTTN as a variable var totalUpgraders = 0; var countFTTN = 0; var flag = false; var newFTTC = 0; var newFTTP = 0; newFTTCusers[0] = myRound(capexNew[0] * 1000 * (100 - CAPEXupgradeType) / 100 / FTTCcapexPerHH); newFTTPusers[0] = myRound(capexNew[0] * 1000 * CAPEXupgradeType / 100 / FTTPcapexPerHH); totalUpgraders += newFTTCusers[0] + newFTTPusers[0]; countFTTN = maxFTTN - newFTTCusers[0] - newFTTPusers[0]; for (let i = 1; i <= moreYears; ++i) { // only pay back to level of debt; NB: cashBal assumed negative if (!flag) { newFTTCusers[i] = myRound(capexNew[i] * 1000 * (100 - CAPEXupgradeType) / 100 / FTTCcapexPerHH); newFTTPusers[i] = myRound(capexNew[i] * 1000 * CAPEXupgradeType / 100 / FTTPcapexPerHH); newFTTC = newFTTCusers[i]; newFTTP = newFTTCusers[i]; } // end only calculate new users if not exceed maxFTTN else { // add nils to array newFTTCusers[i] = 0; // this will set close to but not quite 100% newFTTPusers[i] = 0; } if ( (totalUpgraders + newFTTCusers[i] + newFTTPusers[i]) > maxFTTN) { // do nothing ?? // add proportionately TODO if ((newFTTC + newFTTP) > 0) { // check no divide by zero newFTTCusers[i] = Math.round((newFTTCusers[i] * countFTTN ) / (newFTTC + newFTTP)); // this will set close to but not quite 100% newFTTPusers[i] = Math.round((newFTTPusers[i] * countFTTN ) / (newFTTC + newFTTP)); flag = true; } else { newFTTCusers[i] = 0; // this will set close to but not quite 100% newFTTPusers[i] = 0; } // end newFTTC + new FTTP } // end Check not > maXFTTN totalUpgraders += newFTTCusers[i] + newFTTPusers[i]; countFTTN -= (newFTTCusers[i] + newFTTPusers[i]); } // end loop return (newFTTCusers); }39:T7e2,upgradeFTTPUsers = { // count of FTTP upgraded from FTTN var newFTTCusers = []; var newFTTPusers = []; var maxFTTN = 4.6 * (100 - moreMobile) / 100; // could add totalFTTN as a variable var totalUpgraders = 0; var countFTTN = 0; var flag = false; // upgraders exceed maxFTTN? var newFTTC = 0; var newFTTP = 0; newFTTCusers[0] = myRound(capexNew[0] * 1000 * (100 - CAPEXupgradeType) / 100 / FTTCcapexPerHH); newFTTPusers[0] = myRound(capexNew[0] * 1000 * CAPEXupgradeType / 100 / FTTPcapexPerHH); totalUpgraders += newFTTCusers[0] + newFTTPusers[0]; countFTTN = maxFTTN - newFTTCusers[0] - newFTTPusers[0]; for (let i = 1; i <= moreYears; ++i) { // only pay back to level of debt; NB: cashBal assumed negative if (!flag) { newFTTCusers[i] = myRound(capexNew[i] * 1000 * (100 - CAPEXupgradeType) / 100 / FTTCcapexPerHH); newFTTPusers[i] = myRound(capexNew[i] * 1000 * CAPEXupgradeType / 100 / FTTPcapexPerHH); newFTTC = newFTTCusers[i]; newFTTP = newFTTCusers[i]; } // end only calculate new users if not exceed maxFTTN else { // add nils to array newFTTCusers[i] = 0; // this will set close to but not quite 100% newFTTPusers[i] = 0; } if ((totalUpgraders + newFTTCusers[i] + newFTTPusers[i]) > maxFTTN) { // do nothing ?? // add proportionately TODO - DONE if ((newFTTC + newFTTP) > 0) { newFTTCusers[i] = Math.round((newFTTCusers[i] * countFTTN ) / (newFTTC + newFTTP)); // this will set close to but not quite 100% newFTTPusers[i] = Math.round((newFTTPusers[i] * countFTTN ) / (newFTTC + newFTTP)); flag = true; } else { newFTTCusers[i] = 0; // this will set close to but not quite 100% newFTTPusers[i] = 0; } // end newFTTC + new FTTP } totalUpgraders += newFTTCusers[i] + newFTTPusers[i]; countFTTN -= (newFTTCusers[i] + newFTTPusers[i]); } // end loop return (newFTTPusers); }3a:T8bf,newRevenuePostFTTN = { var values = []; var adjMob = 11.7/8.6 * (100 - moreMobile)/100; 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; // 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; // var powerREV = powerUsers/100 * csatPow * 50 * 12 * (4.3 + FTTNupgraders[yr]) / 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 + FTTNupgraders = 4.3M users (now plus upgraded users from FTTN) // $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; } for (let i = 0; i <= moreYears; ++i) { if (i > lifeFTTN) { FTTNadj = revFTTN; } values[i] = myRound((((final - FTTNadj) * csatRev) + powerREV) * adjMob * csatMob); if (FTTNupgraders[i] > 0) { // only include FTTN revenue when FTTN upgraded revFTTN = (usersFTTN - FTTNupgraders[i]) * ARPUpa * EOLeavers / 100; if (i > lifeFTTN) { FTTNadj = revFTTN; } // adjust revenue of FTTN for after FTTN end of life values[i] = (myRound((((final - FTTNadj) * csatRev) + (powerRevFTTN[i] * csatPow)) * adjMob * csatMob) ); } } // FTTNupgraders exist return (values); // pickup add other additions. // $5.3B per Corp Plan 2018, adjusted for MOB % }3b:T682,newOpexVarPostFTTN = { var values = []; var powerOPEXFTTN = []; 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 + FTTNupgraders[i]) / 1000; // FTTC + FTTP + HFC = 4.3 // this formula replace with one in [i] loop below to access FTTNupgraders by year // $20 pm costs power use var users = 8.6; // var lifeFTTN = 10; var usersFTTN = 3.4; var ARPUpa = opexVar / users; var opexVarFTTN = (usersFTTN - FTTNupgraders[lifeFTTN-1]) * 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; } // adjusted for upgraded FTTN users powerOPEXFTTN[i] = powerUsers/100 * csatPowOpex * 20 * 12 * (4.3 + FTTNupgraders[i]) / 1000; values.push(myRound((((final - FTTNadj) * csatOpex) + powerOPEXFTTN[i]) * (1 + (mobRevShiftPerCent * csatMobOpexAdj)/100))); } return (values); // pickup add other additions. }3c:[] 31:{"id":"694c4ceff6734119","slug":"nbn-financials-from-2021-on-v5a-verbose-off","title":"NBN Financials - from 2021 on...","thumbnail":"c946646d2a5e75b01f8cee0e86f1d39737d4fa0414068921227c0d9161bb8238","access_level":"public","create_time":"$D2018-03-22T09:27:46.126Z","update_time":"$D2018-08-28T06:51:57.549Z","view_time":null,"first_public_time":"$D2018-03-22T09:46:01.391Z","has_unpublished_edits":null,"fork":{"id":"ebcbe11ac4e3eec3","slug":"nbn-financials-from-2021-v5-on","title":"NBN Financials - from 2021 (V5) 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":1236,"publish_version":1236,"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":3,"collection":null,"type":"notebook","body":{"title":"NBN Financials - from 2021 on...","cells":[{"id":0,"value":"md`# NBN Financials - from 2021 on...`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":808,"value":"$33","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: 0, \n title: \"NBN Debt repayment rate: (% of annual cash surplus)\", \n description: \"Integers from zero through 100. Priority spend over FTTN upgrade.\"\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); only works once add to Upgrade Reserve below\"\n})","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":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	Post FTTN (End Of Life Yr ${lifeFTTN})
$B	${newRevenue[lifeFTTN+1]}	${myRound((newOpexFix[lifeFTTN+1] + newOpexVar[lifeFTTN+1]))}	${ebitdaFTTNeol}	${myRound(npvCalcEBITDA,2)}	NBN GDP impact=$B ${Math.round(myRound(gdpPreFTTNupgrade))} FTTN Upgrade Reserve: see V4

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

p.a.	Revenue	Expenses	PROFIT(EBITDA)	DCF CASH	Post FTTN Upgrade Yr${moreYears}
$B	${newRevenuePostFTTN[moreYears-1]}	${myRound((newOpexFix[moreYears-1] + newOpexVarPostFTTN[moreYears-1]))}	${ebitdaPostFTTN}	${myRound(npvCalcEBITDAPostFTTN,2)}	NBN GDP impact=$B ${Math.round(myRound(gdpPostFTTNupgrade))} Post FTTN NBN Value: $B ${Math.round(cashPostFTTN[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: 100, \n step: 1, \n value: 26.5, \n title: \"Non-NBN Users% (post 2020 ie mobile only):\", \n description: \"Integers from zero through 100% - 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: 0, \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: 0, \n title: \"NBN/FTTN Upgrade: % free cash 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":432,"value":"html`

Useful life of FTTN; (years)`","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":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":97,"value":"md`### 2.BaseCase Data and calculations - 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":1027,"value":"ebitdaPostFTTN = myRound(newRevenuePostFTTN[moreYears] - (newOpexFix[moreYears] + newOpexVarPostFTTN[moreYears]))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":448,"value":"md`EBITDA(cash profit) $B | EBITDA (after FTTN end of life) | EBITDA (after FTTN upgrade to FTTC or FTTP)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":99,"value":"$36","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`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 in 2020 ($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] -upgradeCAPEX[0] + cashBalanceOpening)); \n \n //- debtRepayments[0]: exclude as affects OP.BAL\n \n \n for (let i = 1; i <= moreYears; ++i) {\n total[i] = myRound((total[i-1] + newRevenue[i] - newOpexFix[i] - newOpexVar[i] - upgradeCAPEX[i]));\n //- debtRepayments[i])); don't subtract from cash as improve OP.BAL.\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, customer satisfaction, FTTN end of life, adoption of Ultra-broadband (gbps), debt repayment, FTTN capex upgrades.`","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. Optional amend for FTTN abandoners at end of life (status = OFF).`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":137,"value":"$37","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 = { // Cash added to reserve to upgrade FTTN; totalCAPEX gives acc total; capex gives annual total\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] = myRound(capex[i] + totalCAPEX[i-1]); // repay debt\n \n } // end loop\n return (capex); // cash available for upgrading FTTN\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":876,"value":"md`Accumulated CAPEX funds put aside for upgrading FTTN; based on % of cash surplus selected.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1103,"value":"md`

\n### 2.1 Calculations for FTTN upgrade to FTTC or FTTP.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":882,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":901,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":927,"value":"upgradeCAPEX = { // cash spent on upgrading FTTN\n \n var capexSpend = [];\n \ncapexSpend[0] = myRound((upgradeFTTCUsers[0] * FTTCcapexPerHH / 1000) + (upgradeFTTPUsers[0] * FTTPcapexPerHH / 1000));\n \n for (let i = 1; i <= moreYears; ++i) {\n \ncapexSpend[i] = myRound((upgradeFTTCUsers[i] * FTTCcapexPerHH / 1000) + (upgradeFTTPUsers[i] * FTTPcapexPerHH / 1000));\n \n } // end loop\n return (capexSpend);\n \n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":964,"value":"FTTNupgraders = { // $B in excess revenue for gbps users\n var total = [];\n \n \n total[0] = upgradeFTTCUsers[0] + upgradeFTTPUsers[0];\n \n \n for (let i = 1; i <= moreYears; ++i) {\n total[i] = myRound(upgradeFTTCUsers[i] + upgradeFTTPUsers[i] + total[i-1]);\n \n }\n \n return (total)\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":994,"value":"powerRevFTTN = { // $B in excess revenue for gbps users\n \n var powerREV = [];\n \n powerREV[0] = myRound(powerUsers/100 * 50 * 12 * (4.3) / 1000);\n \n for (let i = 1; i <= moreYears; ++i) {\n \n powerREV[i] = myRound(powerUsers/100 * 50 * 12 * (4.3 + FTTNupgraders[i-1]) / 1000); \n // FTTN upgraders revenue delayed a year, so install Yr 0, can generate revenue in Yr 1\n }\n \n return (powerREV)\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":984,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1000,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1008,"value":"cashPostFTTN = {\n var total = [];\n // var openingBalance = -20;\n total[0] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - subPays[0] -upgradeCAPEX[0] + cashBalanceOpening)); \n //- debtRepayments[0]\n \n for (let i = 1; i <= moreYears; ++i) {\n \n total[i] = myRound((total[i-1] + newRevenue[i] - newOpexFix[i] - newOpexVar[i] - upgradeCAPEX[i]));\n //- debtRepayments[i]));\n \n if (FTTNupgraders[i] > 0) {\n \n total[i] = myRound((total[i-1] + newRevenuePostFTTN[i] - newOpexFix[i] - newOpexVarPostFTTN[i] - upgradeCAPEX[i]));\n //- debtRepayments[i])); repayments offset op.bal.\n }\n \n } // end loop\n \n return (total);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":905,"value":"md`Testing FTTN upgrade formula`\n// viewof CAPEXupgradeType2 = slider({ \n// to enable seeing changes in FTTNupgraders through choosing FTTC or FTTP\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)\"\n}) */","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":31,"value":"md`

\n### 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":885,"value":"FTTCcapexPerHH = 2900 ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":892,"value":"FTTPcapexPerHH = 4400","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":"533/537","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1114,"value":"md`

\n#### 4.1 Calc NPV / GDP`","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":1030,"value":"npvCalcEBITDAPostFTTN = {\n ///var test = [10, 10, 10];\nreturn (- npv(newOpexVarPostFTTN, DCFrate) - npv(newOpexFix, DCFrate) + npv(newRevenuePostFTTN, 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":"gdpPreFTTNupgrade = {\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"},{"id":1041,"value":"gdpPostFTTNupgrade = {\n ///var test = [10, 10, 10];\n var gdpEBITDA = - npv(newOpexVarPostFTTN, 0) - npv(newOpexFix, 0) + npv(newRevenuePostFTTN, 0);\n // test: var gdpEBITDA = 0;\n \nreturn ( npv(newOpexVarPostFTTN, 0) + npv(newOpexFix, 0) + npv(newRevenuePostFTTN, 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":"$W3c","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}

${newRevenuePostFTTN[moreYears-1]}

${myRound((newOpexFix[moreYears-1] + newOpexVarPostFTTN[moreYears-1]))}

${ebitdaPostFTTN}

1.Active Analysis tools