{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Points","Cash Sweep Exercise Blank","Completed Sweep Exercise","Sweep Before DSRA - Works","Sweep After DSRA - Problem","Sheet2","Sheet3"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Points","index":0,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"The main points are Using MAX adn MIN"]
[]
[null,"Use MIN in Maturities"]
[]
[null,"Include MIN AND MAX in Cash Flow Statement"]
[]
[null,"DSRA Changes and Formulas After Sweep"]

{"sheet":"Cash Sweep Exercise Blank","index":1,"n_rows":55,"n_cols":8,"rows_kept":55,"cut":false,"hidden":false,"merged":[]}
["Cash Sweep "]
[]
["Assumptions"]
[null,"Base Cash Flow",null,null,300,null,null,"DSCR"]
[null,"Year of Crash",null,null,20,null,null,"PLCR"]
[null,"Percent Reduction in Crash",null,null,0]
[]
[null,"Debt Commitment",null,null,900]
[null,"Debt Tenor",null,null,15]
[null,"Interest Rate",null,null,0.1]
[null,"Cash Sweep Percent",null,null,0]
[]
["Operating Analysis"]
[null,"Period"]
[null,"Post Crash Switch"]
[null,"Cash Flow"]
[]
["Debt Schedule"]
[null,"Opening Balance"]
[null,"Less: Scheduled Maturities - INCLUDE MIN FUNCTION"]
[null,"Less: Cash Sweep - TAKE FROM BELOW"]
[null,"Closing Balance"]
[null,"Interest"]
[]
[null,"Defaulted Debt"]
[null,null,"Opening Balance"]
[null,null,"Add: Defaulted - FROM BELOW"]
[null,null,"Less: Repayment of Default - FROM BELOW"]
[null,null,"Closing Balance"]
[null,null,"Interest "]
[]
[]
["Cash Flow Analysis"]
[null,"Cash Sweep %"]
[]
[null,"Operating Cash Flow"]
[null,"Less: Scheduled Maturities"]
[null,"Less: Interest"]
[null,null,"Cash Before Sweep"]
[null,"Less: Cash Flow Sweep - USE MAX AND MIN"]
[null,null,"Cash after Sweep"]
[null,"Add: Default on Debt - USE MAX(-CF,0)"]
[null,"Less: Repayment of Default - USE MAX AND MIN"]
[null,null,"Dividends"]
[]
["Ratios"]
[null,"DSCR"]
[null,null,"CFADS"]
[null,null,"Debt Service"]
[null,null,null,"DSCR"]
[]
[null,"PLCR"]
[null,null,"NPV of Cash Flow"]
[null,null,"Debt Issued"]
[null,null,"PLCR"]

{"sheet":"Completed Sweep Exercise","index":2,"n_rows":55,"n_cols":22,"rows_kept":55,"cut":false,"hidden":false,"merged":[]}
["Cash Sweep "]
[]
["Assumptions"]
[null,"Base Cash Flow",null,null,300,null,null,"DSCR",{"$f":"F50","v":2}]
[null,"Year of Crash",null,null,20,null,null,"PLCR",{"$f":"F55","v":2.5353598354361195}]
[null,"Percent Reduction in Crash",null,null,0]
[]
[null,"Debt Commitment",null,null,900]
[null,"Debt Tenor",null,null,15]
[null,"Interest Rate",null,null,0.1]
[null,"Cash Sweep Percent",null,null,0]
[]
["Operating Analysis"]
[null,"Period",null,null,null,null,0,{"$f":"G14+1","v":1},{"$f":"H14+1","v":2},3,4,5,6,7,8,9,10,11,12,13,14,15]
[null,"Post Crash Switch",null,null,{"$f":"E5","v":20},null,null,{"$f":"H14>=$E$15","v":false},{"$f":"I14>=$E$15","v":false},false,false,false,false,false,false,false,false,false,false,false,false,false]
[null,"Cash Flow",null,null,{"$f":"E4","v":300},{"$f":"E6","v":0},null,{"$f":"$E$16*(1-$F$16*H15)","v":300},{"$f":"$E$16*(1-$F$16*I15)","v":300},300,300,300,300,300,300,300,300,300,300,300,300,300]
[]
["Debt Schedule"]
[null,"Opening Balance",null,null,null,null,null,{"$f":"G22","v":900},{"$f":"H22","v":840},780,720,660,600,540,480,420,360,300,240,180,120,60]
[null,"Less: Scheduled Maturities",null,null,{"$f":"E9","v":15},null,null,{"$f":"MIN(($G$22/$E$20),H19)","v":60},{"$f":"MIN(($G$22/$E$20),I19)","v":60},60,60,60,60,60,60,60,60,60,60,60,60,60]
[null,"Less: Cash Sweep",null,null,null,null,null,{"$f":"H40","v":0},{"$f":"I40","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Closing Balance",null,null,{"$f":"E8","v":900},null,{"$f":"E22","v":900},{"$f":"H19-H20-H21","v":840},{"$f":"I19-I20-I21","v":780},720,660,600,540,480,420,360,300,240,180,120,60,0]
[null,"Interest",null,null,{"$f":"E10","v":0.1},null,null,{"$f":"$E$23*H19","v":90},{"$f":"$E$23*I19","v":84},78,72,66,60,54,48,42,36,30,24,18,12,6]
[]
[null,"Defaulted Debt"]
[null,null,"Opening Balance",null,null,null,null,{"$f":"G29","v":0},{"$f":"H29","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Add: Defaulted",null,null,null,null,{"$f":"H42","v":0},{"$f":"I42","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Less: Repayment of Default",null,null,null,null,{"$f":"H43","v":0},{"$f":"I43","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Closing Balance",null,null,null,null,{"$f":"H26+H27-H28","v":0},{"$f":"I26+I27-I28","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Interest ",null,{"$f":"E23","v":0.1},null,null,{"$f":"$E$30*H26","v":0},{"$f":"$E$30*I26","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[]
["Cash Flow Analysis"]
[null,"Cash Sweep %",null,null,null,null,null,{"$f":"$E$11","v":0},{"$f":"$E$11","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[null,"Operating Cash Flow",null,null,null,null,null,{"$f":"H16","v":300},{"$f":"I16","v":300},300,300,300,300,300,300,300,300,300,300,300,300,300]
[null,"Less: Scheduled Maturities",null,null,null,null,null,{"$f":"H20","v":60},{"$f":"I20","v":60},60,60,60,60,60,60,60,60,60,60,60,60,60]
[null,"Less: Interest",null,null,null,null,null,{"$f":"H23+H30","v":90},{"$f":"I23+I30","v":84},78,72,66,60,54,48,42,36,30,24,18,12,6]
[null,null,"Cash Before Sweep",null,null,null,null,{"$f":"H36-H37-H38","v":150},{"$f":"I36-I37-I38","v":156},162,168,174,180,186,192,198,204,210,216,222,228,234]
[null,"Less: Cash Flow Sweep",null,null,null,null,null,{"$f":"MIN(MAX(H39,0)*$H$34,H19-H37)","v":0},{"$f":"MIN(MAX(I39,0)*$H$34,I19-I37)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Cash after Sweep",null,null,null,null,{"$f":"H39-H40","v":150},{"$f":"I39-I40","v":156},162,168,174,180,186,192,198,204,210,216,222,228,234]
[null,"Add: Default on Debt",null,null,null,null,null,{"$f":"MAX(-H41,0)","v":0},{"$f":"MAX(-I41,0)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Less: Repayment of Default",null,null,null,null,null,{"$f":"MIN(H26,MAX(H41,0))","v":0},{"$f":"MIN(I26,MAX(I41,0))","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Dividends",null,null,null,null,{"$f":"H41+H42-H43","v":150},{"$f":"I41+I42-I43","v":156},162,168,174,180,186,192,198,204,210,216,222,228,234]
[]
["Ratios"]
[null,"DSCR"]
[null,null,"CFADS",null,null,null,null,{"$f":"H36","v":300},{"$f":"I36","v":300},300,300,300,300,300,300,300,300,300,300,300,300,300]
[null,null,"Debt Service",null,null,null,null,{"$f":"H37+H38","v":150},{"$f":"I37+I38","v":144},138,132,126,120,114,108,102,96,90,84,78,72,66]
[null,null,null,"DSCR","Min",{"$f":"MIN(H50:V50)","v":2},null,{"$f":"IF(H49>0.1,H48/H49)","v":2},{"$f":"IF(I49>0.1,I48/I49)","v":2.0833333333333335},2.1739130434782608,2.2727272727272729,2.3809523809523809,2.5,2.6315789473684212,2.7777777777777777,2.9411764705882355,3.125,3.3333333333333335,3.5714285714285716,3.8461538461538463,4.166666666666667,4.5454545454545459]
[]
[null,"PLCR"]
[null,null,"NPV of Cash Flow",null,{"$f":"E10","v":0.1},{"$f":"NPV(E53,H48:V48)","v":2281.8238518925077}]
[null,null,"Debt Issued",null,null,{"$f":"E8","v":900}]
[null,null,"PLCR",null,null,{"$f":"F53/F54","v":2.5353598354361195}]

{"sheet":"Sweep Before DSRA - Works","index":3,"n_rows":40,"n_cols":15,"rows_kept":40,"cut":false,"hidden":false,"merged":[]}
["Cash Sweep and DSRA"]
[]
[null,"DSRA Periods",null,null,1]
[null,"Cash Sweep Percent",null,null,0.5]
[]
[null,"Repayments Scheduled",null,null,null,null,100,100,100,100,100,100,100,100,100]
[null,"Debt",null,null,null,{"$f":"SUM(6:6)","v":900}]
[]
[null,"EBITDA",null,null,null,null,300,{"$f":"G9","v":300},{"$f":"H9","v":300},300,300,300,300,300,300]
[]
["Debt Schedule"]
[null,"Opening Balance",null,null,null,null,{"$f":"F15","v":900},{"$f":"G15","v":745},582.25,411.36250000000001,231.93062500000002,43.527156250000019,0,0,0]
[null,"Less: Repayments",null,null,null,null,{"$f":"MIN(G6,G12)","v":100},{"$f":"MIN(H6,H12)","v":100},100,100,100,43.527156250000019,0,0,0]
[null,"Less: Cash Sweep",null,null,null,null,{"$f":"G38","v":55},{"$f":"H38","v":62.75},70.887500000000003,79.431874999999991,88.403468750000002,0,0,0,0]
[null,"Closing Balance",null,null,null,{"$f":"F7","v":900},{"$f":"G12-G13-G14","v":745},{"$f":"H12-H13-H14","v":582.25},411.36250000000001,231.93062500000002,43.527156250000019,0,0,0,0]
[]
[null,"Interest Rate",null,null,null,0.1,{"$f":"$F$17","v":0.1},{"$f":"$F$17","v":0.1},0.1,0.1,0.1,0.1,0.1,0.1,0.1]
[null,"Interest Expense",null,null,null,null,{"$f":"G17*G12","v":90},{"$f":"H17*H12","v":74.5},58.225000000000001,41.136250000000004,23.193062500000003,4.3527156250000019,0,0,0]
[]
["DSRA"]
[null,"Debt Service",null,null,null,{"$f":"F18+F13","v":0},{"$f":"G18+G13","v":190},{"$f":"H18+H13","v":174.5},158.22499999999999,141.13625000000002,123.1930625,47.879871875000021,0,0,0]
[null,"Forward Debt Service - Offset",null,null,null,{"$f":"IFERROR(SUM(OFFSET(F21,0,1,1,$E$3)),0)","v":190},{"$f":"IFERROR(SUM(OFFSET(G21,0,1,1,$E$3)),0)","v":174.5},158.22499999999999,141.13625000000002,123.1930625,47.879871875000021,0,0,0,0]
[null,"Required Debt Service",null,null,null,{"$f":"F22","v":190},{"$f":"G22","v":174.5},{"$f":"H22","v":158.22499999999999},141.13625000000002,123.1930625,47.879871875000021,0,0,0,0]
[null,"Opening DSRA",null,null,null,{"$f":"F28","v":0},{"$f":"G28","v":190},{"$f":"H28","v":174.5},158.22499999999999,141.13625000000002,123.1930625,47.879871875000021,0,0,0]
[null,"Required Addition",null,null,null,{"$f":"F23-F24","v":190},{"$f":"G23-G24","v":-15.5},{"$f":"H23-H24","v":-16.275000000000006},-17.088749999999976,-17.943187500000022,-75.313190624999976,-47.879871875000021,0,0,0]
[]
[]
[null,"Opening DSRSA",null,null,null,{"$f":"D30","v":0},{"$f":"F30","v":190},174.5,158.22499999999999,141.13625000000002,123.1930625,47.879871875000021,0,0,0]
[null,"Add: Net Change in Required",null,null,null,{"$f":"F25","v":190},{"$f":"G25","v":-15.5},-16.275000000000006,-17.088749999999976,-17.943187500000022,-75.313190624999976,-47.879871875000021,0,0,0]
[null,"Closing Balance",null,null,null,{"$f":"SUM(F28:F29)","v":190},{"$f":"SUM(G28:G29)","v":174.5},158.22499999999999,141.13625000000002,123.1930625,47.879871875000021,0,0,0,0]
[]
["Cash Flow"]
[null,"EBITDA",null,null,null,null,{"$f":"G9","v":300},{"$f":"H9","v":300},300,300,300,300,300,300,300]
[null,"CFADS",null,null,null,null,{"$f":"G33","v":300},{"$f":"H33","v":300},300,300,300,300,300,300,300]
[null,null,"Less: Scheduled Debt Repayment",null,null,null,{"$f":"G13","v":100},100,100,100,100,43.527156250000019,0,0,0]
[null,null,"Less: Interest",null,null,null,{"$f":"G18","v":90},74.5,58.225000000000001,41.136250000000004,23.193062500000003,4.3527156250000019,0,0,0]
[null,"Cash Flow after DS",null,null,null,null,{"$f":"G34-G35-G36","v":110},{"$f":"H34-H35-H36","v":125.5},141.77500000000001,158.86374999999998,176.8069375,252.12012812499998,300,300,300]
[null,null,"Less: Sweep",null,null,{"$f":"E4","v":0.5},{"$f":"MIN($F$38*G37,G12-G13)","v":55},{"$f":"MIN($F$38*H37,H12-H13)","v":62.75},70.887500000000003,79.431874999999991,88.403468750000002,0,0,0,0]
[null,null,"Add: Reduction in DSRA AFTER",null,null,null,{"$f":"G29","v":-15.5},-16.275000000000006,-17.088749999999976,-17.943187500000022,-75.313190624999976,-47.879871875000021,0,0,0]
[null,"Dividends",null,null,null,null,{"$f":"G37-G38-G39","v":70.5},{"$f":"H37-H38-H39","v":79.025000000000006},87.976249999999979,97.375062500000013,163.71665937499998,300,300,300,300]

{"sheet":"Sweep After DSRA - Problem","index":4,"n_rows":39,"n_cols":14,"rows_kept":39,"cut":false,"hidden":false,"merged":[]}
["Cash Sweep and DSRA"]
[]
[null,"DSRA Months",null,null,1]
[null,"Cash Sweep Percent",null,null,0.5]
[]
[null,"Repayments Scheduled",null,null,null,100,100,100,100,100,100,100,100,100]
[null,"Debt",null,null,{"$f":"SUM(6:6)","v":900}]
[null,"Cash Flow",null,null,null,300,{"$f":"F8","v":300},{"$f":"G8","v":300},300,300,300,300,300,300]
[]
["Debt Schedule"]
[null,"Opening Balance",null,null,null,{"$f":"E14","v":900},{"$f":"F14","v":745},582.25,411.36250000000001,231.93062500000002,43.527156250000019,0,0,0]
[null,"Less: Repayments",null,null,null,{"$f":"F6","v":100},{"$f":"G6","v":100},100,100,100,100,100,100,100]
[null,"Less: Cash Sweep",null,null,null,{"$f":"F38","v":55},{"$f":"G38","v":62.75},70.887500000000003,79.431874999999991,88.403468750000002,-56.472843749999981,-100,-100,-100]
[null,"Closing Balance",null,null,{"$f":"E7","v":900},{"$f":"F11-F12-F13","v":745},{"$f":"G11-G12-G13","v":582.25},411.36250000000001,231.93062500000002,43.527156250000019,0,0,0,0]
[]
[null,"Interest Rate",null,null,0.1,{"$f":"$E$16","v":0.1},{"$f":"$E$16","v":0.1},0.1,0.1,0.1,0.1,0.1,0.1,0.1]
[null,"Interest Expense",null,null,null,{"$f":"F16*F11","v":90},{"$f":"G16*G11","v":74.5},58.225000000000001,41.136250000000004,23.193062500000003,4.3527156250000019,0,0,0]
[]
["DSRA"]
[null,"Debt Service",null,null,{"$f":"E17+E12","v":0},{"$f":"F17+F12","v":190},{"$f":"G17+G12","v":174.5},158.22499999999999,141.13625000000002,123.1930625,104.352715625,100,100,100]
[null,"Forward Debt Service - Offset",null,null,{"$f":"SUM(OFFSET(E20,0,1,1,$E$3))","v":190},{"$f":"SUM(OFFSET(F20,0,1,1,$E$3))","v":174.5},{"$f":"SUM(OFFSET(G20,0,1,1,$E$3))","v":158.22499999999999},141.13625000000002,123.1930625,104.352715625,100,100,100,0]
[null,"Required Debt Service",null,null,{"$f":"E21","v":190},{"$f":"F21","v":174.5},{"$f":"G21","v":158.22499999999999},141.13625000000002,123.1930625,104.352715625,100,100,100,0]
[null,"Opening DSRA",null,null,{"$f":"E27","v":0},{"$f":"F27","v":190},{"$f":"G27","v":174.5},158.22499999999999,141.13625000000002,123.1930625,104.352715625,100,100,100]
[null,"Required Addition",null,null,{"$f":"E22-E23","v":190},{"$f":"F22-F23","v":-15.5},{"$f":"G22-G23","v":-16.275000000000006},-17.088749999999976,-17.943187500000022,-18.840346874999994,-4.3527156250000019,0,0,-100]
[]
[]
[null,"Opening DSRSA",null,null,{"$f":"D29","v":0},{"$f":"E29","v":190},174.5,158.22499999999999,141.13625000000002,123.1930625,104.352715625,100,100,100]
[null,"Add: Net Change in Required",null,null,{"$f":"E24","v":190},{"$f":"F24","v":-15.5},-16.275000000000006,-17.088749999999976,-17.943187500000022,-18.840346874999994,-4.3527156250000019,0,0,-100]
[null,"Closing Balance",null,null,{"$f":"SUM(E27:E28)","v":190},{"$f":"SUM(F27:F28)","v":174.5},158.22499999999999,141.13625000000002,123.1930625,104.352715625,100,100,100,0]
[]
["Cash Flow"]
[null,"EBITDA",null,null,null,{"$f":"F8","v":300},{"$f":"G8","v":300},300,300,300,300,300,300,300]
[null,"Add: Reduction in DSRA"]
[null,"CFADS",null,null,null,{"$f":"F32-F33","v":300},{"$f":"G32-G33","v":300},300,300,300,300,300,300,300]
[null,"Less: Scheduled Debt Repayment",null,null,null,{"$f":"F12","v":100},{"$f":"G12","v":100},100,100,100,100,100,100,100]
[null,"Less: Interest",null,null,null,{"$f":"F17","v":90},{"$f":"G17","v":74.5},58.225000000000001,41.136250000000004,23.193062500000003,4.3527156250000019,0,0,0]
[null,"Cash Flow after DS",null,null,null,{"$f":"F34-F35-F36","v":110},{"$f":"G34-G35-G36","v":125.5},141.77500000000001,158.86374999999998,176.8069375,195.647284375,200,200,200]
[null,"Less: Sweep",null,null,{"$f":"E4","v":0.5},{"$f":"MIN($E$38*F37,F11-F12)","v":55},{"$f":"MIN($E$38*G37,G11-G12)","v":62.75},70.887500000000003,79.431874999999991,88.403468750000002,-56.472843749999981,-100,-100,-100]
[null,"Dividends",null,null,null,{"$f":"F37-F38","v":55},{"$f":"G37-G38","v":62.75},70.887500000000003,79.431874999999991,88.403468750000002,252.12012812499998,300,300,300]

{"sheet":"Sheet2","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sheet3","index":6,"n_rows":8,"n_cols":6,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,"www.financeenergyinstitute.com"]