2e:null 2f:["$","title",null,{"children":"NBN Financials - from 2021 on (inc. interest paid on debt).... (v5c) | Richard Ferrers | Observable"}] 34:Tc08,md`Version 5c. 27.4.18. For explanations, click Version 5 below.
This is the model **WITHOUT EXPLANATIONS**. See 5pp description 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

PICKUP: consider refactoring entire interest calculation, with a function calc.

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 | Submitted Version [v5a](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-on-v5a-verbose-off) | [v5b](https://beta.observablehq.com/@areff2000/nbn-financials-from-2021-on-v5b) | 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. Amended FTTN replace CAPEX calculation to prevent negative CAPEX. 21.4.18. Amended Debt Repayment to avoid negative repayment, due to EBITDA negative. 11.5.18.
**Interest** included, at variable rate, but only calcs on pre-FTTN cash < $0. Issue: Interest calc not taking into account, cashflow of interest paid on cash balance, since circular formula. Est. $250M-$1B impact on NBN Value. 27.4.18. FIXED 11.5.18, shown Cash Post Int as separate line.
**ISSUE**: Calc of CAPEX does not include interest in calculation of cash available to spend. Hence overspend on CAPEX by amount of interest. If add interest, then Circular Calculation Error. Since CAPEX calc includes Profit (EBITDA) plus interest. Graph shows too much spending (bar) exceeds revenue (line), when FTTN replacement.
**ISSUE**: Calc of interest is based on Cash, not Cash post FTTN, so no interest on FTTN_CAPEX (FIXED by separate Int line). So interest expense understated by amount of compounding interest, in the time between when Cash positive and Cash post FTTN positive. FIXED as separate Int line. CAPEX interest still calculated even if Cash is positive. Consider add condition to check for this.
eg FTTNup=100, DebtRepay=0, => Int=$12B, No interest Exp after Yr 13. But Cash post FTTN positive in Yr 17. Est. Understatement Interest $1.5B. FIXED - Added as separate calc, but not including compound interest.
**ISSUE** Noted potential issue where Debt repayments become negative, because EBITDA can be negative ie as result of Interest - FIXED. Debt Repay problematic because using a local calc of Interest and total repaid. Why does debt repay go to $zero? Clue: Affected by high FTTN abandon. Why debt repaid not up to total EBITDA?`35:T994,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, exc Int. Exp.', type: 'line', marker: { color: 'blueviolet' } }; var trace15 = { x: newYears, y: cashPostFTTN, name: 'Cash Post FTTN upgrade, inc. Int. Exp.', type: 'line', marker: { color: 'gold' } }; var trace25 = { x: newYears, y: netDebt, name: 'Debt Outstanding', type: 'line', marker: { color: 'pink' } }; var trace6 = { x: newYears, y: debtRepayments, name: 'Debt - REPAID', type: 'bar', marker: { color: 'red' } }; var trace66 = { x: newYears, y: interestExpComp, name: 'Int - PAID', type: 'bar', marker: { color: 'black' } }; 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, trace25, trace6, trace66, trace8, trace2, trace4, trace3], layout) return pl }36: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. `37: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 % }38: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. }39:T762,debtRepayments = { var totalPaid = 0; var debtRepaid = []; var outstandingDebt = cashBalanceOpening; debtRepaid[0] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - (DCFrate/100 * -cashBalanceOpening) - subPays[0]) * debtRepayRate / 100); // Debt Repayments now adjusted for interest paid totalPaid += debtRepaid[0]; for (let i = 1; i <= moreYears; ++i) { //TODO chase up why last payment not rounded to 2dp. if (totalPaid < -cashBalanceOpening) { // only pay back to level of debt; NB: cashBal assumed negative var thisInt = -DCFrate/100 * (cashBalanceOpening + totalPaid) // Debt Repayments now adjusted for interest paid var thisEBITDA = myRound((newRevenue[i] - newOpexFix[i] - newOpexVar[i] - thisInt)); // only repay debt if EBITDA > 0 else nothing to repay with // old: debtRepaid[i] = myRound((newRevenue[i] - newOpexFix[i] - newOpexVar[i] - thisInt) * debtRepayRate / 100); if (thisEBITDA > 0) { debtRepaid[i] = myRound(thisEBITDA * debtRepayRate / 100); } else { debtRepaid[i] = 0; } totalPaid += debtRepaid[i]; // repay debt if (totalPaid > -cashBalanceOpening) { debtRepaid[i] -= myRound(totalPaid + cashBalanceOpening); // refunded excess payment // since cashBalanceOpening is negative // last payment of diff between outstanding and } // adjust if final payment } // end calcRepayment else { debtRepaid[i] = 0; } } // end for loop return (debtRepaid); //return (totalPaid) // to see the total repayments } // check what cash balance debtRepay is based on PICKUP; ie Why does debtRepay cease? // use of own interest calculation here is problematic; remove and use interest calc elsewhere // does not include CAPEX payments in calc of repayments3a:T451,capexNew = { // Cash added to reserve to upgrade FTTN; totalCAPEX gives acc total; capex gives annual total var totalCAPEX = []; var capex = []; var outstandingDebt = cashBalanceOpening; capex[0] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - subPays[0] - debtRepayments[0]) * CAPEXupgrade / 100); if (capex[0] < 0) { capex[0] = 0 } totalCAPEX[0] = capex[0]; for (let i = 1; i <= moreYears; ++i) { // only pay back to level of debt; NB: cashBal assumed negative var thisEBITDA = myRound((newRevenue[i] - newOpexFix[i] - newOpexVar[i] - debtRepayments[i])); capex[i] = myRound((thisEBITDA) * CAPEXupgrade / 100); // if ((thisEBITDA + interestExpComp[i]) < 0) { capex[i] = 0 } // circular formula // capex[i] = myRound((newRevenue[i] - newOpexFix[i] - newOpexVar[i] - debtRepayments[i]) * CAPEXupgrade / 100); if (capex[i] < 0) { capex[i] = 0 } totalCAPEX[i] = myRound(capex[i] + totalCAPEX[i-1]); // repay debt } // end loop return (capex); // cash available for upgrading FTTN }3b: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); }3c: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); }3d: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 % }3e: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. }3f:[] 32:{"id":"90af45f5d3ddd73f","slug":"nbn-financials-from-2021-on-inc-interest-paid-on-loan-v5c","title":"NBN Financials - from 2021 on (inc. interest paid on debt).... (v5c)","thumbnail":"ccaf26736a85ff7445d9b926186b46e3f23983581c9b6b57abe2362d511771e3","access_level":"public","create_time":"$D2018-04-26T23:56:41.513Z","update_time":"$D2018-10-15T05:13:37.553Z","view_time":null,"first_public_time":"$D2018-05-11T06:47:21.624Z","has_unpublished_edits":null,"fork":{"id":"ff07a68c42a028e8","slug":"nbn-financials-from-2021-on-v5b","title":"NBN Financials - from 2021 on.... (v5b)","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":1524,"publish_version":1524,"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":0,"collection":null,"type":"notebook","body":{"title":"NBN Financials - from 2021 on (inc. interest paid on debt).... (v5c)","cells":[{"id":0,"value":"md`# NBN Financials - from 2021 on (inc. interest paid on debt).... (v5c)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1223,"value":"md`This version: Adding Interest expense calc at discount rate. Parliamentary Submission from [Dept of Comms; Sub06](https://www.aph.gov.au/Parliamentary_Business/Committees/Joint/National_Broadband_Network/Businesscase/Submissions), noted interest on NBN Debt payable at 3.96%pa. Impacts Base Case negatively by around $5B.

\n*DISCLAIMER*: The interest calculation is problematic because it currently causes circular formula errors. So interest is calculated and reported (see Post FTTN Cash totals) but not all shown in graph. See Details and Issues noted\nbelow.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":808,"value":"$34","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 / interest 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":"$35","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])} Total Interest: Cash Pre $B ${(totIntCompoundPay(moreYears))} + Int: Cash Post $B ${calcInt(DCFrate,cashDiff)} + Int: Upgrade CAPEX $B ${calcInt(DCFrate,upgradeCAPEX)}

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

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":"$36","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":"$37","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. Interest can't fit here, because results in circular formula.`","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":"$38","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":"$39","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":"$3a","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":1238,"value":"interestExp = {\n var calcInt = []\n var totRepaid = 0\n \n calcInt[0] = myRound(- (cashBalanceOpening) * DCFrate / 100)\n \n for (let i = 1; i <= moreYears; ++i) {\n if (cash[i-1] < 0) { calcInt[i] = myRound(-(cash[i-1]) * DCFrate / 100)\n\n // simple interest only, on prior year cash balance, but not interest paid to date\n \n } else calcInt[i] = 0\n }\n \n // + totDebtRepay[i-1]\n return calcInt\n \n // TODO - not including Interest on interest. ie Int on Debt, cash bal, less debt repay, but not a/c for cash impact of interest paid. At 5%, $3.7B.\n // Impact $3.5B @ 5% for 7 years => $180M. Avg $500M interest. ie Int pa; $25M per annum.\n // when add six years int to Yr0, five years int to Yr 1 => $935M interest = $140M per annum.\n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1341,"value":"interestExpComp = {\n var calcInt = []\n calcInt[0] = interestExp[0]\n \n for (let i = 1; i <= moreYears; ++i) {\n if (interestExp[i] > 0) { calcInt[i] = myRound(interestExp[i] + (totIntPay(i-1) * DCFrate / 100))\n } else { calcInt[i] = 0 }\n // adds compounding to simple interest\n }\n \n return calcInt\n // Debt repayment now adjusted for interest payable. 27.4.18\n // ISSUE: interest calc on pre-Int cash, rather than cash including interest paid.\n // due to circular formula issue. Not sure of way to calc on CashPostFTTN which inc Interest.\n \n }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1255,"value":"function totDebtRepay(year)\n{\n var sum = 0\n for (let i = 0; i <= year; ++i) {\n sum += debtRepayments[i]\n }\n \n return myRound(sum) \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1331,"value":"function totIntPay(year)\n{\n var sum = 0\n for (let i = 0; i <= year; ++i) {\n sum += interestExp[i] // if change to interestExpComp then CIRCULAR error, since used to calc InterestExpComp\n \n }\n // need to add this figure to InterestExp but having a circular formula error.\n return myRound(sum) \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1477,"value":"function calcInt(rate, cashflow)\n{\n var sum = 0\n for (let i = 0; i <= moreYears; ++i) {\n sum += rate / 100 * cashflow[i] \n // returns interest in total for array\n \n }\n \n return myRound(sum) \n \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1387,"value":"totIntPay(moreYears)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1413,"value":"function totIntCompoundPay(year)\n{\n var sum = 0\n for (let i = 0; i <= year; ++i) {\n sum += interestExpComp[i] // if change to interestExpComp then CIRCULAR error, since used to calc InterestExpComp\n \n }\n // need to add this figure to InterestExp but having a circular formula error.\n return myRound(sum) \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1415,"value":"totIntCompoundPay(moreYears)","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":"$3b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":901,"value":"$3c","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":"$3d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1000,"value":"$3e","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 // includes interest expense to prevent circular formula in Cash\n total[0] = myRound((newRevenue[0] - newOpexFix[0] - newOpexVar[0] - subPays[0] - interestExpComp[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] - interestExpComp[i] - upgradeCAPEX[i]));\n //- debtRepayments[i]));\n \n if (FTTNupgraders[i] > 0) {\n \n total[i] = myRound((total[i-1] + newRevenuePostFTTN[i] - newOpexFix[i] - interestExpComp[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":1318,"value":"netDebt = {\n var sum = []\n sum[0] = myRound(cashBalanceOpening + debtRepayments[0])\n \n for (let i = 1; i <= moreYears; ++i) {\n sum[i] = myRound(sum[i-1] + debtRepayments[i])\n } \n \n return sum \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1427,"value":"md`:Need to add interest for (CashPostFTTN - Cash) * interestRate (compounding); ie when Cash is positive and CashPostFTTN is still negative.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1430,"value":"cashDiff = { // used to calculate extra interest on CashPostFTTN, since already have interest on Cash\n var calc = []\n \n for (let i = 0; i <= moreYears; ++i) {\n if ((cash[i] > 0) && (cashPostFTTN[i] < 0)) {\n calc[i] = -myRound(cashPostFTTN[i])\n }\n else { calc[i] = 0 }\n \n } // end for \n \n return calc\n}\n// next step; calc interest on cashDiff, then calc interest on interest, then plot interest exp, plot new cashflow line, show total interest PICKUP\n// Est'd understated interest at: FTTNup=100, DebtRepay=0, => $1.5B","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1482,"value":"calcInt(DCFrate,cashPostFTTN)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1475,"value":"interest2Exp = {\n var calcInt = []\n var totRepaid = 0\n \n calcInt[0] = myRound(- (cashBalanceOpening) * DCFrate / 100)\n \n for (let i = 1; i <= moreYears; ++i) {\n if (cash[i-1] < 0) { calcInt[i] = myRound(-(cash[i-1]) * DCFrate / 100)\n\n // simple interest only, on prior year cash balance, but not interest paid to date\n \n } else calcInt[i] = 0\n }\n \n // + totDebtRepay[i-1]\n return calcInt\n \n // TODO - not including Interest on interest. ie Int on Debt, cash bal, less debt repay, but not a/c for cash impact of interest paid. At 5%, $3.7B.\n // Impact $3.5B @ 5% for 7 years => $180M. Avg $500M interest. ie Int pa; $25M per annum.\n // when add six years int to Yr0, five years int to Yr 1 => $935M interest = $140M per annum.\n \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":"$W3f","stdlib":"1","titling":"auto"}} 33:"$32: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